第十章：第9节 MySQL进阶篇——哪些情况下不建议用索引
更新于：2017-08-23 14:04:23
前面我们一直在讲索引建好了怎么让select语句能用到索引，或者为了让select语句提高效率而去建相关索引。这节我们讨论下一个索引有没有必要被建立。
索引有助于查询数据时提高效率，凡是有利必有弊。查询数据时方便了，可当增删改数据时，索引反而成了累赘。当对数据做改动操作时，MySQL会相应地修改索引文件，假如一个表索引太多了，势必会影响操作效率。索引文件本身要消耗存储空间，MySQL在运行时也要消耗资源维护索引。
如果一个表只做数据查询，很少改动，不介意索引文件太大，我们可以为这个表建很多个索引都没问题（一个表最多可有16个索引，最大索引长度是256个字节）。可实际应用中，只有极个别的数据表不做改动（类似中国省市区表），绝大数表都要做改动的。为了提高一点查询效率，搞垮了数据改动效率，所以在建索引时需要去多方面衡量下有没有这个必要。
更新表的同时也是在更新索引，索引不是越多越好，哪些情况下不建议用索引：
1、表记录比较少的时候不建议建索引。
数据小的时候，全表扫描也是很快的，即使建立了索引，其性能也不会得到很大的改善。相反索引建立的开销，如维护成本等等，要比这个要大。也就是说，付出的要比得到的多，显然违反常理。
比方一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下MySQL至少要往返读取数据块两次。而不用索引的情况下MySQL会将所有的数据一次读出，处理速度显然会比用索引快。
至于多少条记录才算多，有人建议是以2000作为分界线，记录数不超过 2000可以暂不建索引，超过2000条可酌情考虑索引。具体的可以通过explain测试分析。
反正楠神在开发项目时，也懒得后期优化了，设计数据库时会把可能用到的索引都先加上，就算耗费资源又能耗费多少。
2、where子句使用频率低的字段不建议建索引。
这样的索引被使用到的频率低，索引带来的好处远远低于坏处，就没有必要建立了。也有可能select语句写得不好，导致了索引没有用到，那就需要优化select语句或者修改索引了。只有经常当做where条件的字段适合建索引。
3、经常插入、删除、修改的表建议少建索引。
对一些经常处理的业务表应在查询允许的情况下尽量减少索引。
4、建议不要在很长的字符串字段上建索引。
字符串字段建索引，很容易使索引文件变得很庞大，占用磁盘空间，也不利于使用索引。尤其不建议为text字段建索引。
5、（重点）不要在重复值特别多且分布平均的表字段上建索引。
不要在像“性别”这类字段上建索引，它总共就两个值“男、女”供选择。在表中的定位性比较差，选择性低，使用索引查找还不如遍历表的效率高，即使加了索引MySQL也不会使用该索引查找。
为什么遍历全表比使用索引效率高？和“表记录比较少的时候不建议建索引”中的示例一样的道理，使用索引会极大的增加IO开销。
说下索引的选择性：
所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0, 1]，选择性越高索引价值越大，这是由B+Tree的性质决定的。
我们来看下“test_tab”里的“invest_id”索引，它的选择性：
由于表中数据不是很多，选择性不是特别高。
通过上面的公式，我们应该很容易就能猜到，性别的选择性是非常的低。假如一张会员表有200000行记录，在性别字段加上索引，那这个索引的选择性是 2/200000 = 0.00001，选择性如此低，实在没有什么必要为其单独建索引。
主键索引和唯一索引，因为有唯一约束的原因，它的选择性是1，所以需要保持数据唯一的字段很适合建一个唯一索引。
前缀索引(短索引)：
有一种与索引选择性有关的索引优化策略叫做前缀索引或短索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
这里有张表：
我们想建一个索引，可以把“nickname”建一个索引，或者把“nickname、username”建一个组合索引。
“nickname”选择性偏低，“nickname、username”选择性好，可“nickname、username”组合索引长度就太长了，起码150多个字节，这样会造成索引文件特别庞大。
有没有兼顾长度和选择性的办法？
可以只用“username”列前几个字符做索引。
“username”只取前4个字符时，选择性是0.5023；只取前5个字符时，选择性是0.7014；只取前6个字符时，选择性是0.8274。
当只取前7个字符时，这个选择性已是非常理想，而索引的长度会大大减少23（30-7）个字符的长度。
看图示：
添加组合前缀索引“nickname”，索引的长度为86。
删掉组合前缀索引“nickname”，添加完整的组合索引“nickname2”。
索引的长度是155，(155-86)/3 = 23。
前缀索引总结：
前缀索引兼顾索引大小和查询速度，它只适用于字符串字段建索引，不适用于数值型字段建索引。
前缀索引的缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。