浅谈MySQL索引
作为后端java小白，mysql查询优化是不可避免碰到的，尤其是索引方面，分享下我初步研究成果
索引基础
在MySQL中，索引是在存储引擎层而不是服务器层实现的，并没有统一的索引标准，不同的存储引擎索引工作的方式是不一样的。
索引类型
1.B-Tree索引 意味着所有的索引都是按顺序存储的，并且每一个叶子页到根的距离相同。 B-Tree索引可应用于如下类型的查询：
（1）全值匹配：和索引中的所有列进行匹配；
（2）匹配最左前缀：如当索引有多列时，只使用索引的第一列；
（3）匹配列前缀：只使用索引第一列的值的开头部分；
（4）匹配范围值：只使用索引的第一列；
（5）精确匹配某一列，并范围匹配另一列：只使用索引的第一列及第二列的开头部分；
（6）只访问索引的查询：查询只需要访问索引，而无须访问数据行，即覆盖索引；
（7）可用于ORDER BY查询；
B-Tree索引的限制：
（1）如果不是按照索引的最左列开始查找，则无法使用索引；
（2）不能跳过索引中的列；
（3）如果查询中有某个列的范围查询（包括LIKE等），则其右边所有列都无法使用索引优化查找；
2.哈希索引 基于哈希表实现，对于每一行数据存储引擎都会对所有的索引列计算一个哈希码，只有精确匹配索引所有列的查询才有效。 只有Memory引擎显式支持哈希索引。
InnoDB有一个特殊的功能：自适应哈希索引，当InnoDB注意到某些索引值被使用得非常频繁时，就会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。
3.空间数据索引（R-Tree） MyISAM表支持空间索引，可以用作地理数据存储。
4.全文索引 查找的是文本中的关键词，而不是直接比较索引中的值。适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。
建索引
1.索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能
2.避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引
3.数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
4.在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度
5.在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。
索引的优点
减少服务器需要扫描的数据量；
避免排序和临时表；
将随机I/O变为顺序I/O；
索引策略
1.如果查询中的列不是独立的，而是表达式的一部分，MySQL将不会使用索引；如下情况下不会使用索引 SELECT id FROM table WHERE id + 1 = 5;
2.索引的选择性越高则查询的效率越高；（索引选择性 = 列取值基数/表记录总数）;
3.在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能： （1）当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的多列索引； （2）当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要消耗大量的CPU和内存资源用在缓存、排序和合并操作上。而且优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取，这会导致查询的成本被低估，甚至执行该计划还不如全表扫描。这样的查询通常通过改写成UNION查询来优化;
4.多列索引的列顺序非常重要，在一个多列的B-Tree索引中，索引总是先按照最左列进行排序，其次是第二列，等等。 经验：一般的选择是将选择性最高的列放到索引最前列，但是避免随机IO和排序通常更为重要，所以要优先结合值的分布情况及频繁执行的查询场景来考虑。
常见的导致查询过慢的原因
- 查询不需要的记录；如误以为MySQL会只返回需要的数据，实际上却是先返回全部结果集，再进行计算。
- 多表关联时返回全部列
3.总是取出全部列：SELECT * FROM ...
4.重复查询相同的数据，而不是使用缓存；
优化依据
MySQL使用WHERE条件的不同方式 从好到坏依次为： 1. 在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎层完成的； 2. 使用索引覆盖扫描（Using index）返回记录，直接从索引中过滤不需要的记录并返回命中的结果，在MySQL服务器层完成，无需再回表查询记录； 3. 从数据表中返回数据，然后过滤不满足条件的记录（Using Where），MySQL需要先从数据表读出记录然后过滤；
优化关联查询
- 确保ON或者USING子句中的列上有索引；
- 一般只需要在关联顺序中的第二个表的相应列上创建索引；
- 确保GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程；
优化子查询
尽可能使用关联查询代替子查询。
优化GROUP BY和DISTINCT
MySQL使用同样的方式优化这两种查询，在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化。 当无法使用索引时，GROUP BY使用两种策略来完成：临时表或者文件排序。 通常使用查找表的标识列分组的效率会比其他列更高。 尽可能将WITH ROLLUP功能转移到应用程序中处理。
