在MySQL中，有时候一个好的索引就能解决一个系统性能问题，索引的确是个好东西。所以有些人只要遇到查询，就往查询字段上加索引。
但是索引是越多越好吗？索引在innodb与myisam中是一样的吗？任何列都适合加索引吗？
在另一篇文章中，详细介绍了innodb与myisam中索引的区别，详见：https://www.jianshu.com/p/c3fb0b01c44d，今天这里只浅入浅出地讨论使用索引的一些经验。
一般创建索引的几条经验：
-
innodb存储引擎的主键是聚集存储的，一般创建一个自增列作为主键。如果主键是采用业务字段，或者类似uuid32这样非单调的、且占用较多存储空间的字段，这是非常不合适的。
一来在insert时，为了维护索引树的特性而要分裂、调整节点，十分低效！另外uuid32太大，致使辅助索引占用更多的空间，因为辅助索引保存主键的值。 -
复合索引遵循最左前缀原则，要把最常使用的字段或选择性最好的字段放在最前面。
-
对经常修改的字段不要创建索引，维护的成本大。
-
索引不是越多越好，过多的索引，会造成数据更新时的低效，因为要维护很多索引树，过多的索引还占用太多空间。
最左前缀原则
指的是在sql where 中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序、断层都无法利用到多列索引。
举例：index (username, password, time)，
可以利用到索引的where情况：（username）、（username，password）、（username，password，time）、（username，time）
or
如果or前后两个条件列都有索引，查询将使用索引，如果前后有一个条件列没有索引，则查询不使用索引。
EXPLAIN SELECT * FROM test t WHERE t.name LIKE 'tian7%' OR t.id = 1
MySQL索引使用经验
将name上的索引去掉，这时type = ALL
MySQL索引使用经验
（!=、>、<、<=、>=）
单独使用比较列无法利用多列索引，放在其它位置可以利用。不过也要看情况，一般主键可以，如 id != 123，这是可以利用索引的，type=range。其实还是要看数据集的大小，就算有索引，如果结果太大，type还是为ALL。
Cardinality
索引基数或者叫区分度，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引。这个参数是数据库引擎是否选择索引的决定性因素。如上面提到的比较列（!=、>、<、<=、>=）是否能利用索引具体要看情况，看情况就是看Cardinality，如果索引的区分度不够高，那么就不会走索引，直接全表扫描了（ALL）。可以通过show index from table查看Cardinality信息，一般主键和唯一索引的Cardinality值最大。
索引长度
MyISAM对索引的长度限制是1000字节，InnoDB是767字节，如果创建索引的字段超过指定长度（与字符集有关），则取前缀索引，如InnoDB、utf8下，如果varchar(300)，则创建的索引长度为255
InnoDB存储引擎的表索引的前缀长度最长是767字节(bytes)，起因是2^8×3-1。767表示3个字符最大占用空间（utf8)。
所以,如果需要建索引，就不能超过 767 bytes；utf8编码时 255*3=765bytes ,恰恰是能建索引情况下的最大值。
在5.5以后，开始支持4个字节的utf8(utf8mb4)。255×4>767, 于是增加了一个参数叫做innodb_large_prefix。这个参数默认值是OFF(5.7默认为ON)。当改为ON时，允许列索引最大达到3072。
like
'XXX%'能利用索引，'%XXXX%'不能。
网友评论