索引建立场景

一、建立场景
既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？
答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

二、不建议建索引的情况
1、表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。（经验值2000）
2、索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T

三、索引建立技巧
1、选择性
选择性越高的索引价值越大（这是由B+Tree的性质决定的）
2、索引长度
索引key变短而减少了索引文件的大小和维护开销
案例：
以employees.employees表为例，表中已创建一个<emp_no>主键索引，那么如果我们想按名字搜索一个人，就只能全表扫描，如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。
有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

first_name选择性.png first_name, last_name选择性.png

问题：
<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？
方案：
用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name,n)>，看看其选择性:

left(last_name, 2).png left(last_name, 3).png left(last_name, 4).png

综上所述：first_name, left(last_name, 4)作为前缀索引可以实现选择性高、索引长度适中的目标，这个前缀索引建上：
ALTER TABLE employees.employees ADD INDEX 'first_name_last_name4' (first_name, last_name(4));
此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

优化前.png
优化后.png

性能的提升是显著的，前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和 GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。