1 索引基本概念
1.1 什么是索引
索引是一种存储结构，是数据库表中一列或若干列值的集合，或者是指向表中数据页物理标识的逻辑指针清单。在日常生活中索引就像是一本书的目录，一个商品的使用说明书，我们可以跳过大部分不关心的内容，直奔我们需要知道的主题。目录和使用说明书可以节省我们很多时间，索引也同样用来提高数据库的检索速度，提高数据库性能。
例如：有一张 student 表，其中有 10w 条记录，记录这 10w 个学生的信息。其中有一个 sno 的字段记录每个学生的学号，现在想要查询出学号为 XXXX 的人的信息。
在没有索引的情况下，我们需要一条一条的遍历表中的数据。在查询到匹配的数据后，先将该数据放入到结果集中，再继续查询直到将全部的数据查询完毕。
而在有索引的情况下， 可以将 sno 的 key 的值放在一个 n 叉树上（ BTree ）。查询数据时，会在 B 树上根据 sno 的值进行条件查询， 而不需要全局遍历。
1.2 索引的优点
a.索引的最明显的优点就是减少查询的速度，提高数据库的效率。
- 通过索引查询可以缩短数据检索的时间
- 通过索引可以加快表与表之间的关联查询时间
b.为排序或者分组的字段增加索引，可以提升排序和分组的效率。
1.3 索引的缺点
a.创建索引的时间成本会随着数据量的增大而增大。
b.索引创建之后，在对数据库表中的数据进行增、删、改等操作之后，相应的索引也需要进行维护，降低了数据维护的速度。
c.从本质上来讲，索引是通过空间来换取时间。也就是说，在我们缩短查询的时间成本的同时，势必要牺牲磁盘空间成本来存储索引。
1.4 索引的应用
根据上面所讲的优缺点我们知道，索引是需要在一定的场景下使用才能真正的提高效率的，需要根据具体情况进行分析决定是否使用索引。
a.表数据量较大且响应时间不能满足需求的时候，可以考虑使用索引。
b.索引建议创建在变动频率较小，且值内容较多的字段上。
2 索引实现方式
2.1 创建索引
2.1.1 创建表同时创建索引
CREATE TABLE table_name [col_name data type]
[unique:fulltext:spatial]
[index:key]
[index_name]
(col_name[length])
[asc:desc][unique : fulltext : spatial] ：指定索引类型，唯一索引：全文索引：空间索引。 [index : key] ：index 和 key 都可以用来指定索引，区别是 index 可以给索引命名， key 则默认字段名作为索引名称。 [indexname] ：索引的名称，若不指定默认为 colname 的值，由前一个参数是 index 还是 key 决定。 (colname[length]) ：需要创建索引的字段，不需是表中定义的列， length 为可选参数，只有在 colname 是字符串串类型的时候才能指定长度。
- 创建普通索引
CREATE TABLE order id INT NOT NULL AUTO_INCREMENT,
order_code VARCHAR(255) NOT NULL,
price BIGDECIMAL NOT NULL,
state INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
pro_code VARCHAR(255) NOT NULL,
extension VARCHAR(255),
INDEX (order_code));
- 创建复合索引
CREATE TABLE order (id INT NOT NULL AUTO_INCREMENT,
order_code VARCHAR(255) NOT NULL,
price BIGDECIMAL NOT NULL,
state INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
pro_code VARCHAR(255) NOT NULL,
extension VARCHAR(255),
INDEX (id, order_code, state);
- 创建唯一索引
CREATE TABLE order (id INT NOT NULL AUTO_INCREMENT,
order_code VARCHAR(255) NOT NULL,
price BIGDECIMAL NOT NULL,
state INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
pro_code VARCHAR(255) NOT NULL,
extension VARCHAR(255),
UNIQUE INDEX (order_code));
- 创建主键索引
CREATE TABLE order (id INT NOT NULL AUTO_INCREMENT,
order_code VARCHAR(255) NOT NULL,
price BIGDECIMAL NOT NULL,
state INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
pro_code VARCHAR(255) NOT NULL,
extension VARCHAR(255),
PRIMARY KEY(id));
- 创建全文索引
CREATE TABLE order (id INT NOT NULL AUTO_INCREMENT,
order_code VARCHAR(255) NOT NULL,
price BIGDECIMAL NOT NULL,
state INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
pro_code VARCHAR(255) NOT NULL,
extension VARCHAR(255),
FULLTEXT INDEX (order_code))
ENGINE=MyISAM;
2.1.2 在已有表上创建索引
ALTER TABLE table_name ADD
[unique:fulltext:spatial]
[index:key]
[index_name]
col_name[length])
[asc:desc]- 修改普通索引
ALTER TABLE order ADD INDEX (order_code(255));
CREATE INDEX order_code on order(order_code);
- 创建复合索引
ALTER TABLE order ADD INDEX (id, order_code(255), state);
CREATE INDEX order_code on order(id, order_code， state);
- 创建唯一索引
ALTER TABLE order ADD UNIQUE INDEX (order_code);
CREATE UNIQUE order_code on order(order_code);
- 创建主键索引
ALTER TABLE order ADD PRIMARY KEY(id));
- 创建全文索引
ALTER TABLE order ADD FULLTEXT INDEX (order_code(255));
2.2 查询 或 删除索引
2.2.1 查询索引
SHOW INDEX FROM table_name;
SHOW INDEX FROM order;
2.2.2 删除索引
DROP INDEX index_name on table_name;
DROP INDEX order_code ON order;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE order DROP PRIMARY KEY;
ALTER TABLE order DROP INDEX order_code;
3 索引的问题及优化方案
3.1.1 前导模糊查询利用不到索引。
例如： select * from order where extension like '%XXX' ;
该 SQL 在查询索引字段的时候，由于查询条件开始是模糊的，会导致索引失效，会导致查询全局扫描或者全索引扫描。因此在页面严禁做模糊或者全模糊搜索，如果需要可以通过使用搜索引擎来解决。
3.1.2 union、in、or 都能够命中索引，建议使用 in。
select * from order where id = 1
union all
select * from doc where id = 2;
使用 union 可以命中索引，消耗 CPU 也是最少的，但是一般不这么写 SQL。
select * from order where id in (1, 2) ;
in 同样可以命中索引，查询时消耗的 CPU 比 union all 要多一些，但是通常情况下可以忽略不计，建议使用这种方式
select * from order where id = 1 or id = 2 ;
在新版的 MySQL 中 or 可以命中索引，但是查询时消耗 CPU 比 in 还要多，因此不建议频繁使用 or。
3.1.3 负向条件查询不能使用索引，可以优化为 in 查询。
负向条件包含 ：<>（!=）、not in、 not like、 not exists等 例如：
select * from order where id not in (1, 2)；
可以优化为 in 查询：
select * from order where id in （3, 4, 5) ;
3.1.4 创建索引时避免以下错误观念
- 索引多多益善，过多的索引会占用更多的系统空间，而且维护起来难度也会相应增高；
- 索引宁缺毋滥，认为索引会消耗空间，降低新增和更新的速度；
- 抵制唯一索引，在应用层面通过“先查后插”来对唯一性进行控制；
- 优化索引的时间不正确，过早优化索引，可能会因为不了解系统而优化不完全；过晚的优化索引又可能增加修改索引的工作量。
索引多多益善和索引宁缺毋滥是使用索引的两种极端表现，还是需要根据具体的情况分析如何使用索引，才能提高数据库的使用效率。
3.1.5 超过三个表最好不要 join。
- join 字段的数据类型，要求必须一致；
- 多表关联查询时，被关联的字段必须有索引。
3.1.6 如果明确知道只有一条结果返回，limit 1 能够提高效率。
在知道只有一条结果的时候，我们需要明确的告诉数据库我们只要查这一条结果，让数据库停止继续查询。
select * from order where user_id = ‘XXX’ limit 1 ;
3.1.7 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
根据墨菲定律，如果在数据库中没有创建唯一索引，即使在应用层做了非常完善的数据校验和控制，也会有脏数据产生；而且与明显提高查询的速度来比较，在给有唯一索引的数据库添加数据时降低的速度几乎可以忽略不计。
3.1.8 联合索引最左前缀原则（又叫最左侧查询）
如果在(a,b,c)三个字段上建立联合索引，那么它能够在 a | (a,b) | (a,b,c) 三个地方生效。 例如：
select * from order where order_code = 'XXXX' and state = XX ;
可以建立 (ordercode, state) 的联合索引。 在业务上根据订单的编号作为查询条件的需求有很多，但是几乎没有仅用订单状态来查询的需求，因此是创建 (order_code, state) 的联合索引，而不是 (state, ordercode) 的联合索引。
建联合索引的时候，区分度最高的字段在最左边。 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a > XX and b = XX的时候，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。 最左侧查询，并不是指 SQL 语句的 where 查询条件顺序要和联合索引顺讯一致。 下面的 SQL 语句也可以命中 (order_code， state) 这个联合索引。
select * from order where state = XX and order_code = 'XXXX' ;
但我们还是建议 where 后查询条件的顺序与联合索引的顺序保持一致。 如果建立了 (a,b) 联合索引，就不必再单独建立 a 索引，如果建立了 (a,b,c) 联合索引，就不必再单独建立 a、(a,b) 索引。
3.1.9 范围列可以用到索引（联合索引必须是最左前缀）。
范围列可以命中索引；在联合索引中，如果存在两个或两个以个上范围列，则最多只有一个可以命中索引（命中原则为：最左前缀）。 范围条件包括：>、>=、<=、<、between 等。 例如有 (id，ordercode，enddate) ，那么下面的 SQL 中 id 可以命中索引，而 price 和 from_date 则使用不到索引。
select * from order where id < 10300 and order_code = 'XXXX' and end_date between '2019-01-01' and '2019-12-31' ;
3.1.10 把计算放到业务层而不是数据库层。
select * from order where price = (amount * unit_price) ;
在上面的 SQL中，即使 price 上创建了索引，也会全表扫描。这是因为在字段上计算是不能够命中索引的。 可以把计算放到业务层，这样做既节省数据库的对 CPU 的占用，还可以优化查询缓存。
3.1.11 强制类型转换会全表扫描
如果 varchar 类型在查询的时候不加引号，该值会被强制转成 int 类型，而强转之后的字段不能命中索引。 例如：
select * from order_code = XXXX ; //不能命中索引select * from order_code = 'XXXX' ; //可以命中索引
3.1.12 更新十分频繁、数据区分度不高的字段上不宜建立索引。
MySQL目前主要有以下几种索引方法： B-Tree，Hash，R-Tree。不论用那种索引方法，更新频繁都会大大降低数据库的性能。
一般字段区分度在 80% 以上的时候就可以在该字段上建立索引了，区分度可以使用 count(distinct( 字段名称 )/ count( * )) 来计算。像是“性别”这种区分度很低的字段，建立索引的意义也是非常小的。
3.1.13 MySQL 主要提供 2 种方式的索引： B-Tree 索引， Hash 索引
B 树索引具有范围查找和前缀查找的能力，对于有 N 节点的 B 树，检索一条记录的复杂度为 O(LogN)。与二分查找相同。
哈希索引只能做等于查找，但是无论多大的 Hash 表，查找复杂度都是 O(1)。
显然，如果值的差异性大，并且以等值查找 (=、 <、>、in) 为主，Hash 索引是更高效的选择，它有 O(1) 的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B 树是更好的选择，它支持范围查找。
3.1.14 建立索引的列，不允许为 null。
a.单列索引无法储 null 值，复合索引无法储全为 null 的值。需要使用 not null 约束以及默认值。 b.查询时，采用 is null 条件时，不能命中索引，只能全表扫描。
索引无法存储 null 值的原因如下： a. 索引是有序的。null 值进入索引时，无法确定其应该放在哪里。 (null 值不能进行比较，无法确定 null 出现在索引树的叶子位置的节点位置。) b. 如果需要把空值存入索引，方法有二：其一，把 null 值转为一个特定的值，在 where 中检索时，用该特定值查找。其二，建立一个复合索引。
3.1.15 单索引字段数不允许超过 5 个。
当单索引的字段数超过 5 个的时候，就已经没有过滤数据的效果了。
3.1.16 使用短索引（又叫前缀索引）来优化索引。
前缀索引 对文本的前一部分建立索引，这样可以节约空间，也可以优化查询效率。可以使用 count(distinct left ( 列名, 索引长度 )) / count( * ) 来计算前缀索引的区分度。 其缺点是不能使用 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引，全字段建立索引有时候没必要，就可以根据实际文本区分度决定索引长度即可。 例如：
select * from order where order_code = 'XXXX' and extension = '110108XXXXXXXXXXXX'；
就可以创建索引：(order_code， extension(6))
3.1.17 SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
- all：表示全表扫描
- index ：扫描顺序是按照索引的顺序的全表全扫，速度非常慢。
- consts ：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
- ref ：查找条件列使用了索引而且不为主键和 unique。使用普通的索引 (Normal Index)
- range ：对索引进行范围检索。
补充：
char 和 varchar 的区别：
- char(M) 类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M ， MySQL 就会在它的右边用空格字符补足．
- varchar(M) 类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为 L+1 字节）
未经允许不得转载：极客萌动 » 浅谈 MySQL 数据库索引与优化