引言
MySQL 性能优化包括很多方面，比如数据库设计、参数配置(软&硬)、sql语句、读写分离、分表技术（水平拆分、垂直拆分）等，完整的 MySQL 优化需要很深的功底，掌握这些知识不是一朝一夕可以完成的事情。本文章主要针对 sql 语句优化中的索引优化开展。
简介
什么是索引？
MySQL 官方对索引的定义：索引是帮助 MySQL 高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。我们可以理解索引就是一本书的目录，它会让你更快的找到需要的内容。
为什么要用索引？
在无索引的情况下，MySQL 会扫描整张表来查找符合 sql 条件的记录，其时间开销与表中数据量呈正相关，使用索引主要为了加快查询速度和保证数据的唯一性。
索引种类
从数据结构角度：
- B-Tree树索引(O(log(n)))
- hash索引O(1)
- fulltext索引
- R-Tree索引
从物理存储角度：
- 聚集索引（clustered index）
- 非聚集索引（non-clustered index）
从逻辑角度：
- 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
- 普通索引或者单列索引
- 多列索引（复合索引）：复合索引指多个字段上创建的索引
- 唯一索引或者非唯一索引
- 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL 中的空间数据类型有4种，分别是 GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL 使用 SPATIAL 关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为 NOT NULL
优缺点:
优点：
- 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
- 建立索引可以大大提高检索的数据,以及减少表的检索行数
- 在表连接的连接条件可以加速表与表直接的相连 4.减少查询中分组和排序的时间 5.建立索引,在查询中使用索引可以提高性能
缺点：
- 在创建索引和维护索引会耗费时间,随着数据量的增加而增加
- 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
原理
此处不进行描述，具体参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
使用
使用场景
| 场景 | 描述 |
|---|---|
| 全值匹配 | 查询条件和索引列完全匹配，如：联合索引(a,b),查询 a=? and b=? |
| 匹配最左前缀 | 查询条件中的所有字段需要从左边起按顺序出现在多列索引中，如：联合索引(a,b),查询 a=? |
| 匹配列前缀 | 查询条件只匹配某一索引列的开头部分，如：联合索引(a,b),查询 a like ’?%’ |
| 匹配范围值 | 查询条件匹配索引列的范围，如：联合索引(a,b),查询 a > ? |
| 精确匹配某一列并范围匹配另外一列 | 如：联合索引(a,b),查询 a =? and b>? |
| 多表做join操作时 | 如：表t1,索引(a),表t2,索引(b),查询 t1 left join t2 on t2.b = t1.a |
| order by操作时 | 如：联合索引(a,b),查询 a = ? order by b desc |
| group by操作时 | 如：联合索引(a,b),查询 a = ? group by b desc |
explain介绍
和大家普及一下如何查看sql的索引使用情况，即sql的执行计划： Explain详解
| 属性名称 | 说明 |
|---|---|
| id | SELECT识别符。这是SELECT的查询序列号 |
| select_type | SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询) |
| table | 输出的行所引用的表 |
| type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。 |
| possible_keys | 查询可能使用的索引 |
| key | 查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个 |
| key_len | 用于处理查询的索引长度 |
| ref | 显示使用哪个列或常数与key一起从表中选择行 |
| rows | 查询中应该检索的记录数 |
| extra | 该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。 |
注意事项
一般情况：一次查询至多使用一个索引，即每次查询时，不管建立了多少个索引，最多只会用到其中一个，如：
单列索引(a),(b),©,查询 a=? and b=? and c=?，只会用到单列索引(a)。特殊情况：索引合并会使用多个索引，如果执行计划中type列显示index_merge,则说明出现了索引合并；
建立索引的列，索引的选择性要高，即指索引中不重复的值的数目（也称基数，a）与整个表该列记录总数（b）的比值，比如一个列表（1,2,2,3），总数是4，不重复值数目为3，选择性为3/4，选择性的取值范围为(0, 1]，这个值越大，表示列中不重复值越多，越适合作为前缀索引，唯一索引（UNIQUE KEY）的选择性是1;
sql查询时一定不要出现隐式转换，当产生隐式转换时，查询不走索引，所谓隐式转换，即指where条件语句里，字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换,如：单列索引(a),字符类型，查询 a=1 ,这样的查询会把表中的a字段全部转换成整型，并且无法使用索引(a);
使用索引需要满足最左前缀原则，如：联合索引(a,b,c),查询 a=?或者a=? and b=? 或者a=? and b=? and c=?可以使用联合索引(a,b,c) ，但是b=?或者c=?或者b=? and c=?不能使用联合索引(a,b,c) ;
当查询条件是范围时，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引，如：联合索引(a,b),查询 a>? and b>?，这样的查询可以用到联合索引中的a部分，b部分没有使用；
当查询条件是范围时，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引，如：联合索引(a,b),查询 a>? and b>?，这样的查询可以用到联合索引中的a部分，b部分没有使用；
使用order by进行查询排序时，如果排序字段非索引列，执行计划的extra列会是“Using filesort”；
使用group by进行分组查询时，一般是先根据分组字段排序再进行分组，如果分组字段非索引列，执行计划的extra列会是“Using temporary; Using filesort”；
查询时优先使用覆盖索引，何为覆盖索引？即指所有数据都可以从索引中得到，而不需要去读物理记录。例如某个联合索引(a,b,c)建立在表tb1 的 a、b、c 列上，那么对于如下的sql 语句select a,b from tb1 where a = ? and b = ? and c =?,mysql可以直接从索引(a,b,c)中获取数据。使用explain 命令输出查询计划，如果extra列是“using index ”那就表示使用的是覆盖索引;
查询条件中含有函数或者表达式，mysql不会使用索引，如：索引(a),查询 left(a,6)=?, 同时 !=、<>不走索引，or前后的条件都要有索引整个SQL才会使用索引，只要有一个条件没索引那么整个SQL都不使用索引；
每个查询是否使用索引，不仅取决于索引建立是否合适，同时也取决于查询筛选记录占全表记录的大小，优化器会判断利用索引筛选出来的记录是否小于全表记录的30%，如果小于，使用索引，反之不会；
mysql不能使用索引中范围条件右边的列，如：联合索引(a,b,c),查询 a=? and b>? and c=?,其中索引的a,b列可以用到，但是c列用不到；
索引数据类型选择通常遵循以下指导原则： 1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。 2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间； 3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂，同时NULL存储也是需要占用空间的。应该用0、一个特殊的值或者一个空串代替空值。
案例
- 联合索引(a,b),查询 a=? and b=? 和b=? and a=?,哪个会命中索引？查询a=?和b=?，哪个会使用索引？
- 联合索引(a,b),查询 a>? and b>? 和b>? and a>?, 索引是如何使用的？
- 联合索引(end_time,user_id)，查询select type from um_user_vip_record where user_id =? and end_time > now() order by end_time limit 1，索引的使用情况？
- 以下sql如何优化：
- 联合索引(a,b,c,d),以下查询的索引使用情况 A、where a=? and b=? and d>? and c=? B、where a=? and b=? and d=? order by c C、where a=? and d=? group by c,b D、where a=? and e=? order by b,c E、where a=? and b=? and e=? order by b,c
注：如何知道具体使用了索引的哪一列，可以根据执行计划的key_len得出！
