mysql索引及其常见各种优化方式

Mysql总结（本次使用myslq的版本是5.7）

一. 索引

1.索引是什么

索引是一种经过整理的数据结构， 索引类似大学图书馆建书目索引，分类排好序，可以提高数据检索的效率；

索引已经成为关系型数据库中非常重要的组成部分，可降低数据库的IO成本；

索引可以包含一个或多个列的值，如果索引包含多个列的值，则列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列，mysql优化器也会根据这个原则去进行优化；

2.mysql索引类型

1.normal：普通索引

2.unique：唯一索引，不允许重复

3.spatial：空间索引，对GIS空间数据的支持，在最新发布的MySQL 5.7.4实验室版本中，InnoDB存储引擎新增了对于几何数据空间索引的支持，通过R树来实现，使得空间搜索变得高效。

4.full text：全文搜索的引擎，全文索引时将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的技术，从INNODB1.2.x版本开始，INNODB存储引擎开始支持全文索引，其支持myisam的全部功能，并且还支持其他的一些特性。

innodb存储引擎的全文索引还存在以下限制：

• 每张表只能有一个全文索引。
• 由多列组合而成的全文索引必须使用相同的字符集与排序规则。
• 不支持没有单词界定符的语言，如中文，日语，韩语等。

索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

3.mysql索引方法

1.b+tree

​ B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等，且叶节点之间有指针。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）

B-Tree在MyISAM里的形式和Innodb稍有不同：

​ InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录（聚簇索引）

​ MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址（非聚簇索引）

​ 以下是b+tree在线演示的地址，帮助你更形象地理解B+Tree

url：B+tree在线演示链接

演示效果如下图

首先，得先好好理解什么是B+树！看单独介绍B树、B+树的文章，结合上文提到的网站动态演示效果去理解。

简单的说，是使用B+树存储数据可以让一个查询尽量少的读磁盘，从而减少查询时磁盘I/O的时间。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

2.hash

哈希表是一种以键-值（key-value）的方式存储数据的结构，通过输入待查找的值（即key），即找到其对应的值（即Value）。哈希的思路很简单（数组+链表，类似hashmap的处理方式），把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置，即idx = Hash(key)。如果出现哈希冲突，就采用拉链法解决。

因为哈希表中存放的数据不是有序的，因此不适合做区间查询，适用于只有等值查询,字典表的设计可采用hash的索引方式的场景。

4.索引常见的名词是什么

4.1最左前缀

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
比如需要建立索引的字段有3列，分别是a,b,c，建立索引：idx_a_b_c
则生效的查询条件有：

a =x
a =x and b=y 或者（b =y and a=x，即顺序不影响，SQL优化器会自动处理，以下同理）
a =x and c =y
a=x and b=y and c=z
即保持abc的顺序有多少种排列组合

4.2联合索引

联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引；

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

注意事项：只要列中包含有NULL值都将不会被包含在索引中**，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的，所以我们在数据库设计时尽可能不要让字段的默认值为NULL。

4.3覆盖索引

如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引，即where后面的查询条件字段正好和select后面的查询字段一样，这种索引叫做覆盖索引；

注意，不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+Tree索引做覆盖索引

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点
1、索引项通常比记录要小，所以MySQL访问更少的数据
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O（顺序读）
3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引（索引文件和数据是分开的）
4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

4.4索引下推

索引条件下推（ICP）是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP，存储引擎会遍历索引以查找基表中的行，并将它们返回给MySQL服务器，该服务器会评估WHERE行的条件。启用ICP后，如果WHERE只使用索引中的列来评估部分 条件，MySQL服务器会推送这部分内容。WHERE条件下到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且仅当满足该条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

指数条件下推优化的适用性受以下条件限制：

• ICP用于 range， ref， eq_ref，和 ref_or_null访问方法时，有必要访问完整的表行。
• ICP可用于表InnoDB 和MyISAM表，包括分区InnoDB和 MyISAM表。
• 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取的数量，从而减少I / O操作。对于 InnoDB聚簇索引，已将完整记录读入InnoDB 缓冲区。在这种情况下使用ICP不会降低I / O.
• 在虚拟生成列上创建的二级索引不支持ICP。InnoDB 支持虚拟生成列上的二级索引。
• 引用子查询的条件无法下推。
• 引用存储函数的条件无法下推。存储引擎无法调用存储的函数。
• 触发条件无法下推。

索引下推经常使用的场景：

• 只能用于二级索引

  innodb的主键索引树叶子结点上保存的是全行数据，所以索引下推并不会起到减少查询全行数据的效果。

• select的列不使用覆盖索引

• 多条件查询（where中多条件，where + order by） + 联合索引

  索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

  假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率
  select * from t where a > 2 and b > 10;

二. 索引优化

1.表及索引准备

建表语句如下：

drop table if exists test_index;

create table test_index(
	a int primary key,
	b int,
	c int,
	d datetime DEFAULT CURRENT_TIMESTAMP,
	e varchar(16)
) ENGINE=INNODB

INSERT INTO test_index(a,b,c,e) VALUES(4,3,3,'a');
INSERT INTO test_index(a,b,c,e)  VALUES(3,6,4,'b');
INSERT INTO test_index(a,b,c,e)  VALUES(5,76,56,'d');
INSERT INTO test_index(a,b,c,e)  VALUES(1,34,74,'c');
INSERT INTO test_index(a,b,c,e)  VALUES(2,6,4,'e');

接下来我们针对不同sql语句进行优化

2.引言

当创建完数据库表和索引之后，自己应该考虑以下几个问题：

• 创建的索引是否能够满足绝大多数的查询？

• 索引的区分度是否足够大？

• 组合索引的字段是否过多？是否存在区分度不高的字段？

• 索引有没有提高效率的空间？
  前三个问题，一般凭借DBA或者开发人员的经验来进行甄别，或者在实际测试或者使用中进行优化。
  但是***索引有没有提高效率的空间？***这个问题是可以通过MySQL的explain命令来进行优化指导的。

3.explain分析mysql执行计划

mysql> explain select * from test_index;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.08 sec)

• id

  id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。
  id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

  下面这个就有多条

  mysql> explain select (select 1 from test_index where b = 1) from (select * from test_index
  where a = 1) t;
  +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  |  1 | PRIMARY     | test_index | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
  |  2 | SUBQUERY    | test_index | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    5 |    20.00 | Using where |
  +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  2 rows in set (0.08 sec)
  

• select_type

  select子句类型

  (1) SIMPLE(简单SELECT,不使用UNION或子查询等)

  (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

  (3) UNION(UNION中的第二个或后面的SELECT语句)

  (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

  (5) UNION RESULT(UNION的结果)

  (6) SUBQUERY(子查询中的第一个SELECT)

  (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

  (8) DERIVED(派生表的SELECT, FROM子句的子查询)

  (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• table

  显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derived+x(x是个数字,应该是第几步执行的结果集) derive:派生的意思

• partitions

  使用的哪个分区，需要结合表分区才可以看到

• type

  访问类型

  常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

  ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  index: Full Index Scan，index与ALL区别为index类型只遍历索引树

  range:只检索给定范围的行，使用一个索引来选择行

  ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

  NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

• possible_keys

  可能使用的键或索引

  该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
  如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

• key

  实际使用的键或索引

  如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

• key_len

  索引中使用的字节数

  可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

  不损失精确性的情况下，长度越短越好

• ref

  引用到的上一个表的列

• rows

  表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

• filtered

  表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

• Extra

  额外信息，即附加的详细信息,主要有以下几种情况：

  1.Using where:

  ​ 使用了where筛选条件

  mysql> explain select * from test_index where c=1;
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | test_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1 row in set (0.02 sec)
  

  2.Using index:

  ​ 表示在查询中使用了覆盖索引，避免了扫描表的数据行。

  +----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  | id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | test_index | NULL       | index | NULL          | idx_b | 5       | NULL |    5 |   100.00 | Using index |
  +----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  1 row in set (0.03 sec)
  
  

  3.Using filesort:

  ​ Using filesort通常出现在order by，当试图对一个不是索引的字段进行排序时，mysql就会自动对该字段进行排序，这个过程就称为“文件排序”

  mysql> explain select * from test_index order by c;
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
  | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
  |  1 | SIMPLE      | test_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
  1 row in set (0.03 sec)
  

  mysql> explain select * from test_index order by a;
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  |  1 | SIMPLE      | test_index | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | NULL  |
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  1 row in set (0.04 sec)
  

  由上面对比可看出：由于a是主键有索引，因此没有用到filesort; 而c没有索引所以用了filesort;

  Using filesort出现的情况：排序时无法根据索引进行排序，mysql优化器只能自己进行排序，这种情况会大大降低性能，不可取。

  4.Using temporary:

  ​ 表示在查询过程中产生了临时表用于保存中间结果。mysql在对查询结果进行排序时会使用临时表，常见于group by。group by的实质是先排序后分组，同order by一样，group by和索引息息相关。

  试图对一个没有索引的字段进行分组，会产生临时表：

  mysql> explain select * from test_index group by b;
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  |  1 | SIMPLE      | test_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using temporary; Using filesort |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  1 row in set (0.03 sec)
  

  而如果用有索引的字段进行分组的话

  mysql> explain select * from test_index group by a;
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  |  1 | SIMPLE      | test_index | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    5 |   100.00 | NULL  |
  +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
  1 row in set (0.04 sec)
  

  当时用left join时，若order by子句和group by子句都来自于从表时会产生临时表：

  出现Using temporary意味着产生了临时表存储中间结果并且最后删掉了该临时表，这个过程很消耗性能。

三. 慢查询优化步骤

1.第一步.开启mysql慢查询

​ （1）修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

# General and Slow logging.
log-output=FILE

general-log=0

general_log_file="LAPTOP-4O9M8OIK.log"

slow-query-log=1

slow_query_log_file="LAPTOP-4O9M8OIK-slow.log"

long_query_time=10    默认10s

​ （2）通过MySQL数据库开启慢查询

mysql> show variables like 'slow_query%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | LAPTOP-4O9M8OIK-slow.log |
+---------------------+--------------------------+

将OFF改为ON开启

mysql> set global slow_query_log='OFF';
Query OK, 0 rows affected (0.01 sec)

2.分析慢查询日志

​ 直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

3.常见的慢查询优化

（1）索引没起作用的情况

1. 条件是or，如果还想让or条件⽣效，给or每个字段加个索引
2. like开头%
3. 列类型是字符串，在条件中将数据未使⽤引号引⽤起来
4. where中索引列使⽤了函数或有运算
5. 使用多列索引的查询语句，MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

（2）优化数据库结构

	合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。需要考虑数据冗余、查询和更新的效率、字段的数据类型和大小是否合理等诸多因素。

1. 将字段很多的表分解成多个表

   字段比较多的表中如果存在很多使用频率很低的字段，会拖慢整个表，在数据量大的时候尤为明显；

2. 增加中间表

​ 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

​ 不过不当的增加中间表的话，会增加维持中间表与关联的表的一致性的问题，多出了事务问题。

3.分解关联查询

​ 将一个大的查询分解为多个小查询是很有必要的。

​ 很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：

SELECT * FROM test_index  t
        JOIN test_index1 m ON t.a = m.a
        JOIN test_index2 n ON t.b = n.b
        WHERE t.a = 'mysql';
  
        分解为：
  
        SELECT * FROM test_index WHERE a= 'mysql';
        SELECT * FROM test_index1 WHERE m = 1234;
        SELECT * FROM test_index2 WHERE b in (123,456,567);　

(3)优化LIMIT分页

​ 在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

​ 一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

​ 优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

​ 对于下面的查询：

​ select a,b from test_index limit 90000,10;

​ 该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，

​ 之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。

​ 如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，

​ 找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询

​ 能直接从该位置 开始，这样就不必每次 查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

方法一：筛选字段（b）上加索引

​ 使用覆盖索引的方式 ，title字段加索引

方法二：先查询出主键a的值

​ select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

​ 原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三： 延迟关联(deferred join)

select * from table where xxx limit a,b;

select * from table where id in (select id from table where xxx limit a,b);

在覆盖索引的场景下，第一条的执行逻辑是

​ 1.通过索引找到(a+b)条符合查询条件的记录id
​ 2.再通过(a+b)个id回表查询这(a+b)条记录
​ 3.最后按分页条件给用户返回b条记录

而第二条SQL的执行逻辑则是

​ 1.通过索引找到(a+b)条符合查询条件的记录id
​ 2.按分页条件取b个记录id，然后回表查询这b条记录
​ 3.最后给用户返回b条记录
不难看出，第二条SQL在覆盖索引的场景下，减少了大量的回表执行次数，从而提高了执行效率。而在非索引覆盖的场景下，延时关联失效，两种SQL的执行速度没有多少区别。

方法四：建立复合索引 b 和d(datetime)

​ select * from test_index where b order by d desc limit 0,10

​ 注意：order by 引起的文件排序filesort会严重拖慢sql查询速度