MYSQL 索引的设计——注意事项

关于Innodb的primary key 的建议

我们建表使用一个和存储数据无关的自增ID作为primary key，而主键是unique key；原因如下：

1. 自增id作为primary key，数据插入的时候是循序插入的，插入快，而且是聚簇索引，避免空间的浪费；如果主键作为primary key，一般主键都是长的字符串，插入是随机的，这样调整索引树的结构会很消耗资源；而且局促索引，导致叶节点分裂严重，浪费空间；
2. 其他索引的叶节点除了存储索引数据，还要存储primary key（用于访问数据项来获取索引以外的其他字段），自增id定义为bigint才占用8字节；然而，主键一般是字符串，会很占据很大空间，浪费资源。

---

什么列上适合建索引,什么列上不适合建索引

索引一定有益吗？

答案是否定的，因为索引是有代价的
索引就是把数据库的列数据进行特殊数据接口的排序，达到根据改列字段快速查找的目的；所以每次写入数据，索引数据要重新调整，索引需要维护，所以大量的索引必然降低写性能，无用和冗余的索引一定要删除，否则它只会带来维护开销，没有收益；

索引一定比扫描全表快吗？

答案是否定的

mysql> select sql_no_cache count(1) from (select * from novel_agg_info where public_status = 0) tmp;
+----------+
| count(1) |
+----------+
|  3511945 |
+----------+
1 row in set (11.59 sec)
  
mysql> select sql_no_cache count(1) from (select * from novel_agg_info ignore index(idx_public_status)  where public_status = 0) tmp;
+----------+
| count(1) |
+----------+
|  3511945 |
+----------+
1 row in set (8.46 sec)

第二条语句忽略索引，全表扫描，比使用索引快；因为索引回访表的操作，因为符合条件的数据量特别大，所以大量回放表操作导致大量的IO，性能下降；全表扫描虽说表空间比索引空间大，但是使用多块读写性，一定程度比索引快；

---

where条件中不要在索引字段侧进行任何运算(包括隐式运算),否则会导致索引不可用,导致全表扫描

select * from tab where id + 1 = 1000; 会导致全表扫描,应该修改为select * from tab where id = 1000 -1; 才可以高效返回.
这里的运算也包括隐式的运算,比如说隐式的类型转换…业务上经常有类型不匹配导致隐式的类型转换的情况.这里经常出现的情况是字符串和整型比较.
比如说表定义字段类型为BIGINT,但业务上传进来一个字符串的; 或者是表定义字段类型为varchar,但业务上传进来一个整型的.这个字段上存在索引时,索引也许是不可用的.

为什么说也许呢？这取决于这种隐式的类型转换发生在了哪侧?是表字段侧,还是业务传入数据侧?

整型和字符串比较,DB中和许多程序语言中的处理方式是一样的,都是字符串转换为整型后和整型比较.

所以表定义字段类型为BIGINT,但业务上传进来一个字符串,字段上的索引依然可用,因为隐式的类型转换发生在业务传入数据侧(这只能说是索引依然可用,没有大的性能影响,但隐式的类型转换照样是有性能损耗的,所以还是一致的好)。

表定义字段类型为varchar,但业务上传进来一个整型,会导致索引不可用,全表扫描.因为隐式的类型转换发生在表字段侧。

建议可以使用INT/BIGINT存储的,尽量定义为INT/BIGINT,这样相对于长的纯数字字符串的VARCHAR定义,INT/BIGINT不仅更节省空间(INT 4个字节,BIGINT 8个字节),性能更好;而且即使类型不匹配了,也不会导致索引不可用的问题.

---

不要使用%xxx%这种模糊匹配,会导致全表扫描/索引全扫描

where name like ‘%zhao%’ 这种前后统配的模糊查询,会导致索引不可用,全表扫描,稍好的情况是,能使用覆盖索引的话,是索引全扫描,但也高效不了.

如果确实存在这样高频执行的模糊匹配的业务需求,建议走全文检索系统,不要使用MySQL来做这个事情.

但其实很多业务,使用模糊匹配是带有很大的随意性的,完全可以改为精确匹配,从而使用字段上的索引快速定位数据的。

前缀索引和冗余索引

索引的最左原则

index(a,b,c)能同时优化一下查询：
where a=? and b=? and c=?
where a=? and b=?
where a=?
但不能优化 where b=? and c=? 因为索引定义index(a,b,c) 的前缀列a没有出现在where条件中。

在存在索引index(a,b,c)的情况下,绝大多数情况下,下面的这些索引就冗余了,可以DROP掉的:
index(a)
index(a,b)
上面提到了,这2个索引能优化的查询,index(a,b,c)绝大多数情况下也都能优化,所以它们就冗余了,本着索引越少越好的原则,都可以DROP掉的.
上面提到了绝大多数情况下,冗余了,可以DROP了,但也存在例外的情况,它们的存在还是必要的:

那就是存在下面的查询:
where a=? order by id [limit] (这里id是表的primary key)
这里index(a) ( 实际为index(a,id) ) 可以优化上面的查询,通过使用这个索引,避免物理排序而达到排序的实际效果.
但index(a,b,c) ( 实际为index(a,b,c,id) ) 和index(a,b) (实际为index(a,b,id)) 却达不到这样的效果.
这种情况下,存在index(a,b,c)的情况下,index(a) 是不冗余的,是需要保留的.
如果不存在这种情况,存在index(a,b,c)的情况下,index(a) ,index(a,b) 都是冗余的,建议drop掉.

---

关于索引中的字段顺序

建议where条件中等值匹配的字段放到索引定义的前部,范围匹配的字段(> < between in等为范围匹配)放到索引定义的后面.

因为前缀索引字段使用了范围匹配后,会导致后续的索引字段不能高效的用于优化查询.

mysql> select count(1) from opLog  where opName='zhangyu21' and createTime between 1492876800 and 1495468800;

索引1. index(opName,createTime)
索引2. index(createTime,opName)

索引1 首先匹配等于opName=‘zhangyu21’,createTime =1492876800 条目,向后扫描,直至opName=‘zhangyu21’,createTime>1495468800或者opName!='zhangyu21’为止，它是相当高效的,扫描的条目就是返回的条目.
索引2，因为在前缀索引字段createTime上使用了范围匹配,所以导致索引定义中后面的字段opName不能作为高效的检索字段(Access),只能作为低效的过滤字段(Filter)了.

---

sql语句查询优化

使用覆盖索引

index(a,b) 逻辑上是有序的,所以可以用于优化 where a=? order by b desc limit N; 特别是对这种topN操作的优化效果非常好。

等值匹配

index(opName, listType, createTime)

mysql> explain select * from opLog where opName='' and listType in ('cronJob','cronJobNew') order by createTime desc limit 1;
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+-------+---------+----------------------------------------------------+
| id | select_type | table | type | possible_keys                  | key                            | key_len | ref   | rows    | Extra                                              |
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+-------+---------+----------------------------------------------------+
|  1 | SIMPLE      | opLog | ref  | idx_opName_listType_createTime | idx_opName_listType_createTime | 767     | const | 1252640 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+-------+---------+----------------------------------------------------+

执行计划来看,还是有Using filesort,还是需要物理排序的. 为什么不能通过这个索引避免物理排序,快速的返回结果呢?
原因就在于listType in (‘cronJob’,‘cronJobNew’) 在这个索引字段上使用了范围匹配,从而导致索引层面上整体不再有序了。
这样索引只把所有符合条件的筛选出来再进行物理排序。
在排序字段前的所有索引字段上都必须是等值匹配,才能通过索引保证有序性,才能通过索引避免物理排序,快速的返回结果.
避免物理操作：

select *
from
(
     select * from
     (
           select * from opLog where opName='' and listType = 'cronJob' order by createTime desc limit 1
     ) tmp_1
    union all
    select * from
    (
          select * from opLog where opName='' and listType = 'cronJobNew' order by createTime desc limit 1
     ) tmp_2
) tmp
order by createTime desc limit 1;

经过改造为等效的等值匹配,使用索引避免了大的物理排序操作,快速的返回了结果。

sql优化不一定合理

说到通过索引优化排序查询,特别是TOPN操作,必须说一下MySQL在优化器层面的一个问题:
就是说在遇到order by时,myql会优先选择一个可以避免物理排序的索引来优化这个查询,有时候,这种优先选择是不合理的,会导致性能很差.
(特别在涉及到order by id limit N, 这里id是primary key,优化器选择使用PRIMARY KEY来避免物理排序时尤其要注意是否合理了)

index(app_id)，id是primary key

SELECT * FROM `layer`  WHERE (app_id = 2183) ORDER BY `layer`.`id` ASC LIMIT 1;

上面查询语句sql为了避免物理排序，不会使用index(app_id,id)的索引，而是直接扫描主键id，直到app_id = 2183，所以数据的多少取决于app_id在主键索引的位置，如果app_id不存在，那么就会扫描整张表。

解决方案：强制使用index(app_id,id)组合索引

SELECT * FROM `layer` force index(idx_app_id) WHERE (app_id = 1241) ORDER BY `layer`.`id` ASC LIMIT 1;