MySql索引优化及Explain命令

导读：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

索引分类1：

一、聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的 逻辑顺序相同，一个表中只能拥有一个聚集索引。SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引

二、非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据；不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引(主键)是通往真实数据所在的唯一路径。然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。

聚集索引和非聚集索引的区别底层_一篇读懂聚集索引、非聚集索引、覆盖索引的工作原理..._weixin_39843738的博客-CSDN博客

理解：聚集索引一般就是主键，非聚集索引就是其他的索引。

索引分类2：

Mysql的索引我们分为三大类：

1、单列索引(普通索引，唯一索引，主键索引)

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：

  1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

  2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

  3. 唯一性索引列允许空值，而主键列不允许为空值。

  4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。

  5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

  6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

  7. 主键可以被其他表引用为外键，而唯一索引不能。
 

2、组合索引

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

a，ab，abc      用到索引

bc， b， c        没有从最左边开始

ac                     不连续，如果不连续时，只用到了a列的索引，b列和c列都没有用到 

复合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度。

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
Mysql最左匹配原则_Summersadness8的博客-CSDN博客_mysql最左原则

3、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。
 

创建索引的语句

1：直接创建索引：

CREATE INDEX index_name ON table(column(length));  创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); 全文索引

2：修改表结构的方式添加索引：

ALTER TABLE table_name ADD INDEX index_name ON (column(length));  创建普通索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)); 创建唯一索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);  组合索引
ALTER TABLE article ADD FULLTEXT index_content(content); 全文索引

3：创建表的时候创建索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
);  普通索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);  唯一索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);  主键索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);  全文索引

4：删除索引：

DROP INDEX index_name ON table;

5：查看索引：

show index from table_name;

注意：阿里编码规范手册中要求：

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可。 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度 来确定。

---------------------------------------------------------------------------------------------------------------------------------

Explain查看SQL执行计划

explain | 索引优化的这把绝世好剑，你真的会用吗？（优质博客）

 在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，查看该SQL语句有没有使用上了索引，有没有做全表扫描。

所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，运行SQL语句时哪种策略预计会被优化器采用，分析查询语句或者表结构的性能瓶颈。

作用

　　1、表的读取顺序
　　2、数据读取操作的操作类型
　　3、哪些索引可以使用
　　4、哪些索引被实际使用
　　5、表之间的引用
　　6、每张表有多少行被优化器查询

Explain 用法

使用方法：explain + sql 语句。

-- 实际SQL，查找id > 1123598815738675259 的名字
select name from blade_menu where id > 1123598815738675259;
-- 查看SQL是否使用索引，前面加上Explain即可
Explain select name from blade_meun where id > 1123598815738675259;

包含的字段如下:

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 **两种情况** id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量。 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行。 注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

SQL执行顺序

从这个顺序中我们可以发现，所有的查询语句都是从 FROM 开始执行的。在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。​​​​​​​

 

extended关键字

extended关键字：仅对select语句有效，在Explain后使用extended关键字，可以显示filtered列显示了通过条件过滤出的行数的百分比估计值。

filtered列给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和explain中的前一个表进行连接的行的数目。前一个表就是指explain 的 id列的值比当前表的id小的表。

SQL优化经验

(十七)SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！ - 掘金（牛逼博客）

0.不要使用select *，

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
或
select id from t where num=10
union all
select id from t where num=20

参考：https://www.cnblogs.com/xiangxinhouse/p/6053134.html

4.不要使用like左模糊和全模糊查询,如若like关键字以%号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免%xxx、%xxx%这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

5. !=、!<>、not in、not like、or...要慎用

6.尽量避免在where条件中，不要在列上进行运算，对字段进行函数操作和加减乘除等表达式操作，
将导致索引失效而进行全表扫描.不要在条件查询=前对字段做任何运算

7.COUNT() 函数返回匹配指定条件的行数

7.1 count(*)和count(1)有什么区别
count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

7.2 （阿里java编程规范）不要使用 count(列名)或 count(常量)来替代 count(*)，
count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

8.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 、delete、update 的效率

9、索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
查询时尽量不要对字段做空值判断,一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。

10、MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

11、MySQL只针对以下的操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的

12.连表查询时尽量不要关联太多表,一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在16~18张表之内（阿里开发规范中要求控制在3张表以内）

13.多表查询时一定要以小驱大,可以把SQL当成一个链式处理器，每一次新的子查询、关联查询、条件处理....等情况时，都可以看成一道道的工序，我们在写SQL时要注意的是：在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。

14.必要情况下可以强制指定索引

15.避免频繁创建、销毁临时表

16.尽量将大事务拆分为小事务执行

17.从业务设计层面减少大量数据返回的情况

18.尽量避免深分页的情况出现

19.SQL务必要写完整，不要使用缩写法

20.基于联合索引查询时请务必确保字段的顺序性,最左匹配原则

21.客户端的一些操作可以直接写sql，使用insert ... values（...）批量化完成

22.明确仅返回一条数据的语句可以使用limit 1

23.
①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

0.不要使用select *，

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
或
select id from t where num=10
union all
select id from t where num=20

参考：https://www.cnblogs.com/xiangxinhouse/p/6053134.html

4.不要使用like左模糊和全模糊查询,如若like关键字以%号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免%xxx、%xxx%这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

5. !=、!<>、not in、not like、or...要慎用

6.尽量避免在where条件中，不要在列上进行运算，对字段进行函数操作和加减乘除等表达式操作，
将导致索引失效而进行全表扫描.不要在条件查询=前对字段做任何运算

7.COUNT() 函数返回匹配指定条件的行数

7.1 count(*)和count(1)有什么区别
count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

7.2 （阿里java编程规范）不要使用 count(列名)或 count(常量)来替代 count(*)，
count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

8.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 、delete、update 的效率

9、索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
查询时尽量不要对字段做空值判断,一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。

10、MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

11、MySQL只针对以下的操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的

12.连表查询时尽量不要关联太多表,一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在16~18张表之内（阿里开发规范中要求控制在3张表以内）

13.多表查询时一定要以小驱大,可以把SQL当成一个链式处理器，每一次新的子查询、关联查询、条件处理....等情况时，都可以看成一道道的工序，我们在写SQL时要注意的是：在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。

14.必要情况下可以强制指定索引

15.避免频繁创建、销毁临时表

16.尽量将大事务拆分为小事务执行

17.从业务设计层面减少大量数据返回的情况

18.尽量避免深分页的情况出现

19.SQL务必要写完整，不要使用缩写法

20.基于联合索引查询时请务必确保字段的顺序性,最左匹配原则

21.客户端的一些操作可以直接写sql，使用insert ... values（...）批量化完成

22.明确仅返回一条数据的语句可以使用limit 1

23.
①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

7、Explain

  

 

优质博客：

图解｜索引覆盖、索引下推以及如何避免索引失效 - 知乎

8、【参考】创建索引时避免有如下极端误解：

1）宁滥勿缺。认为一个查询就需要建一个索引。

2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。

3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

9、explain | 索引优化的这把绝世好剑，你真的会用吗？

explain | 索引优化的这把绝世好剑，你真的会用吗？

索引优化的过程：

1.先用慢查询日志定位具体需要优化的sql

2.使用explain执行计划查看索引使用情况

3.重点关注：

key（查看有没有使用索引）

key_len（查看索引使用是否充分） 个人理解：联合索引时，没有使用到所有的索引

type（查看索引类型）

Extra（查看附加信息：排序、临时表、where条件为false等）

一般情况下根据这4列就能找到索引问题。

4.根据上1步找出的索引问题优化sql

5.再回到第2步

10、MySQL排查篇：该如何定位并解决线上突发的Bug与疑难杂症？

开发中的Bug、慢查询的排查、MySQL的故障这三个方向：

(十八)MySQL排查篇：该如何定位并解决线上突发的Bug与疑难杂症？ - 掘金

(十一)MySQL日志篇之undo-log、redo-log、bin-log.....傻傻分不清！ - 掘金