MySQL教程（6）MySQL索引与执行计划

一、MySQL索引

1、索引分类

MySQL官方对索引的定义是"帮助MySQL高效获取数据的数据结构"，通俗来讲索引相当于字典的音序表或书籍的目录，通过将索引包含的字段进行排序（默认升序）可以让存储引擎更快速的查询到需要的数据。比如将1-10000进行排序然后再查找指定的数字，肯定要比乱序更好找到。当一个表没有索引时，进行查询操作就会产生全表扫描、额外排序、临时表等现象发生，甚至还会因为一个DML操作锁住整张表。在MySQL中索引是在存储引擎层实现，而非服务器层，所以不同存储引擎具有不同的索引类型和特性。

1.1、按数据结构分类

· 二叉树\红黑树

二叉树的每个节点最多只有两个子节点，并且左边的子节点比当前节点小，右边的节点比当前节点大。但是如果每次插入的数据都比之前的数据更大，那么整个二叉树就会越来越高，出现数据倾斜的问题。而类似红黑树这样的平衡二叉树在二叉树的基础上增加了自平衡的操作，确保每个节点的左子树和右子树的高度差不能超过 1，但是随着数据变多会出现整个树过高的问题，究其原因就是它们都是二叉树，每个节点只能保存 2 个子节点。

· B树\B+树

B树\B+树的出现解决了二叉树的高度问题，它们不再限制一个节点只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度，所以二者也叫做多路搜索树（多叉树）。它们的区别在于B树的叶子节点和非叶子节点都有可能存放数据，而B+树将所有数据都保存在了叶子节点，这些叶子节点里的数据是有序存放的，这样在进行范围查找时性能更好。

它们会先申请一块磁盘空间（默认16K，由innodb_page_size控制）作为索引页用于存放索引元素。假设存放的是占用磁盘空间为8B的BIGINT数据类型，加上占用磁盘空间6B的下一级索引磁盘地址信息数据，那么一个16K的索引节点可以存放16K/(8B+6B)=1170个索引元素。当B+树高度为3时可以存放的数据已经可以达到2000多万（1170*1170*1170），也就是说经过3次I/O操作就可以从2000多万数据中找到自己需要的那行（这也是为什么常说MySQL单表数据量最好不要超过2000万行的一个原因）

· 哈希索引

哈希索引适用于在大量数据中进行=或者IN这样的等值精确查询，由于会对字段进行哈希运算，所以每个字段有一个唯一的哈希值，这样查询的性能就会非常高效。但是哈希索引无法进行排序和范围查询等模糊查询，Hash索引还有一个缺陷是存储的数据是无序的，在ORDER BY时还需要对数据重新排序。InnoDB引擎本身不支持HASH索引，但是提供了一个特殊的自适应哈希索引机制（Adaptive Hash Index），当InnoDB发现某些索引值被使用非常频繁时会自动在内存中基于BTree索引创建一个哈希索引，使得BTree索引也具有哈希索引的优点。由于这是一个全自动的内部行为，用户无法进行控制或者配置，只能通过innodb_adaptive_hash_index选项来决定是否开启，默认为ON

1.2、按功能逻辑分类

· 主键索引

主键索引也叫Primary Key，它属于聚簇索引，也是一种特殊的唯一索引。通过主键索引进行查询时不会有回表行为，性能更好。好比从字典中查找“数”这个字，只要找到shu的拼音就可以直接找到对应的数据行，不需要再二次查询。

在开发规范中，通常会要求每张表必须创建主键索引（一张表中也只能定义1个主键索引）。在MySQL中没有直接的语法来创建主键索引，而是通过定义主键约束来隐式创建。定义主键约束时MySQL会同时创建主键索引来保证唯一性和非空性。

对于非核心业务可以使用自增ID列作为主键。对于核心业务不建议使用类似UUID这种太过离散的数据作为主键。一是UUID占用的空间较大，另外UUID的数据不是有序的，这样在插入数据的时候会导致树的分裂频繁，排序性能差。可以通过有序UUID或者其他方式来构建一个全局唯一且单调递增的字段。

#假设ID列为主键索引，该语句使用主键查询，只需要搜索ID列这棵B+树
select * from T where ID=500  

#假设name列为普通索引，该语句需要先搜索name索引树得到ID，然后再到ID索引树搜索，这个过程称为回表
select * from T where name='tanglu'

· 普通索引

普通索也可以称为辅助索引、二级索引，是最常用的索引类型，属于非聚簇索引。普通索引所在的字段的值允许重复，这里还以查找“数”字为例，如果按照部首查找的方式就需要先找到“数”字的偏旁部首，然后通过目录知道“数”字存放到第多少页，然后再去指定的页码找这个字。由于非聚簇索引的叶子节点存储的是主键的值，所以最终要还要再通过拿到的主键索引进行二次查询，有多少条数据就会查询多少次，称为回表。普通索引可以使用change buffer特性。change buffer会使用buffer pool里的内存，通过参数 innodb_change_buffer_max_size来动态设置，比如该参数设置为50表示change buffer大小最多只能占用 buffer pool 的 50%。对于写多读少的业务场景（比如账单类、日志类系统），由于页面在写完以后马上被访问到的概率比较小，此时change buffer就可以存放很多数据，减少磁盘IO，提升性能更明显。而如果业务在写入之后马上会做查询，那么change buffer中的数据会被merge到磁盘，反而增加了change buffer的维护代价。change buffer和redolog比较容易混淆，简单来说redo log主要节省随机写磁盘的IO消耗（转成顺序写），change buffer主要节省的则是随机读磁盘的 IO 消耗。

· UNIQUE（唯一索引）

添加唯一约束会自动创建唯一索引，唯一索引上的字段不允许重复，和主键索引的区别在于允许字段的值为NULL，一张表可以创建多个唯一索引。对于唯一索引来说，由于字段有唯一性，如果一个SQL已经满足查询条件就会停止继续检索。但是也由于判断SQL是否违反唯一约束的过程需要将数据页读入内存进行，不能使用change buffer特性，所以效率并不会比普通索引高。

· 全文索引

MySQL 5.7之前只有MyISAM引擎支持全文索引，现在基本使用ES解决全文检索问题，不会交给MySQL

1.3、按物理实现方式分类

按照物理实现方式可以将索引可以分为聚簇索引和非聚簇索引。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引控制数据的物理存储顺序，而二级索引只是帮助加速查询的额外结构，聚簇索引的非叶子节点存放的都是索引信息，叶子节点才存放数据本身。

聚簇索引由InnoDB存储引擎自动创建，并不需要显式创建。MySQL会优先把显式创建的主键索引自动作为聚簇索引，如果没有为表定义主键索引，则选择第一个唯一索引作为聚簇索引。如果表中没有这样的唯一索引则会基于全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引，由于这种索引属于整个实例级别，所以存在性能问题。

1.4、按作用字段个数分类

按照索引所作用的字段个数来分类，可以将索引分为单列索引和联合索引。

联合索引也称为组合索引，是指一个索引中同时包含多个列，在多个字段都要创建索引的情况下创建联合索引优于单列索引。比如已经有了 (a,b) 这个联合索引后就不需要单独在a上建立索引了；又比如一个查询语句同时使用了group by和order by子句，那么就可以为group by和order by的字段创建联合索引，在创建这样的联合索引时需要把group by写在前面。

联合索引在排序时会在完成第一个字段排序的基础上对第二个字段进行排序，依次类推。关于联合索引有一句顺口溜“带头大哥不能死，中间兄弟不能丢”，也就是说使用联合索引时需要遵循最左匹配原则，在进行查询时必须从联合索引最左侧的字段开始（查询条件中的字段书写顺序可以不和索引建立时一致，优化器会帮忙进行排序，但是必须包含最左侧的字段），因为脱离了最左侧的字段会让整个排序动作丢失。另外如果在联合索引中使用了范围查询，那么范围查询之后的字段即便有索引也会失效。通常建议将WHERE条件中经常同时出现的列放在联合索引中。最左前缀可以是联合索引的最左N个字段，也可以是具体字符串的最左N个字符。联合索引也可以用来优化where ... order by语句，比如 where a=? order by b,c，就可以创建一个索引 (a,b,c)。按照联合索引的字段顺序去进行order by排序，可以利用联合索引树里的数据有序性。

2、MySQL索引特性

2.1、覆盖索引

当一个查询语句中需要查询的字段或者过滤条件都是索引列（反过来说就是一个索引中包含了所有需要查询的字段或者过滤条件）就属于覆盖索引。覆盖索引无需回表，可以大大提升查询效率，是一个SQL优化的常用方法（由于SELECT * FROM 这样的语句没有明确字段，无法使用覆盖索引）。在对联合索引进行变更的时候需要注意，如果本身用到了覆盖索引的SQL可能会因为索引变更导致失效，因为二级索引的叶子节点都包含了主键值，假设ID为主键索引，那么col(A)实际等同于col(A,ID)这样一个联合索引，如果将col(A)扩展为col(A,B)，那么会导致WHERE A = 5 ORDER BY ID这样的查询无法再用到索引

#比如worker表有索引(type,salary)，以下查询就会使用到覆盖索引特性，不用回表
select type,salary from worker where type='b'

#如果一个有普通索引的字段结合主键字段进行查询，也可以实现覆盖索引。比如下表中id是主键，last_name字段有普通索引
SELECT id,last_name FROM students WHERE last_name='tanglu'

2.2、前缀索引

索引在默认情况下是将整个字段的长度进行包含，而前缀索引则是将字段指定长度作为索引，通常用于TEXT或者很长的VARCHAR字段上设置前缀索引。使用前缀索引可以让索引长度变短以达到减少树的高度的作用，占用空间也会更小。在定义好合理的长度前提下可以做到既节省空间又不用额外增加太多的查询成本（可以通过计算字段区别度来设置合理的长度，越接近1越好）。另外使用前缀索引就用不上覆盖索引对查询性能的优化了，因为InnoDB最终都要回表进行查询

mysql> alter table user add index index1(city);
mysql> alter table user add index index2(city(4)); #4个字符的前缀索引
insert into user values(beijing)  #匹配
insert into user values(beiping)  #不匹配
insert into user values(beijing)  #匹配

#计算字段区分度
SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS sub10 ,       --截取前10个字符的选择度
COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS sub11         --截取前15个字符的选择度
FROM test_table

2.3、索引下推

索引下推优化特性简称ICP，在执行计划中表示为Using index condition，MySQL 5.6以后开始支持，该特性只能选择开启或者关闭，没有其他额外配置参数。在没有索引下推特性之前，如果查询条件非主键索引，存储引擎会把每一条查询结果都进行回表操作。使用索引下推特性后，存储引擎会先把查询出来的数据进行一次过滤，只拿这些符合条件的数据进行回表查询，这样可以提高查询效率。索引下推特性主要在进行多条件查询时出现，当查询条件的左侧条件基于索引进行了范围查找，而后续字段如果无法使用索引时，MySQL为了减少回表次数，在查询出符合左侧条件的数据以后，会下推给后面的查询条件，等完成后续条件过滤以后再进行回表。

#id索引下推给name字段
SLECT * FROM t1 where id=00001 and name like '%唐%'

3、索引创建原则

· 索引并不是越多越好

由于索引会不断的对字段进行排序，对数据的插入或者更新会带来一定的性能影响（这也是为什么导入大量数据时，先导入数据再建立索引比先创建索引再导入数据更快的原因）。除此之外，每个索引都会占据额外的存储空间。所以索引创建不能过度，通常单表不应该超过5个。否则就应该考虑表设计的合理性。如果某个字段存在多个索引，SQL优化器会分析所有可能的执行计划，然后选择一个成本（cost）最低的进行操作，这种机制叫做CBO（Cost-based Optimizer，基于成本的优化器）

· 高选择性字段适合建立索引

高选择性字段是指该字段内很少出现重复值，即尽量选择有唯一值的字段来创建索引，比如ID、身份证号、电话号码、学号等。类似性别这样的字段没有创建索引的必要。下面的语句可以计算出指定列的选择性

#值越接近1，说明在整张表里的重复性越低，选择性就越高，值低则相反
select count(distinct test_column)/count(*) from test_table;

· 筛选或排序字段要建立索引

经常作为WHERE条件或者GROUP BY、ORDER BY进行分组排序的字段需要建立索引，最好是包含了WHERE条件需要的所有列。如果同时出现GROUP BY和ORDER BY操作，需要建立联合索引，GROUP BY在前，ORDER BY在后。由于排序操作都是在WHERE条件过滤后再执行的，所以如果WHERE条件能过滤大部分数据，那么剩下的少量数据出现了排序影响也不大。当范围条件和GROUP BY、ORDER BY字段出现二选一时，可以先看看条件字段所过滤的数据是否足够多并且排序的数据比较少，这样就可以优先在范围字段上加索引。对于UPDATE或者DELETE操作，如果WHERE字段有索引一样可以大幅提高效率

#同时出现GROUP BY和ORDER BY操作
SELECT id,COUNT(*) FROM student_info GROUP BY id ORDER BY create_time limit 100

#建立联合索引
ALTER TABLE student_info ADD INDEX idx_id_ctime(id,create_time)

· 无需创建索引的情况

当表中数据量较少时无需建立索引，比如几万、几十万行的小表在进行全表扫描时可能会更快

· 索引变慢的可能性

很少变动的表可以多创建索引，经常变动的表有索引但有可能出现索引丢失问题。当出现本来很快的查询语句突然变慢了，就要考虑这个问题，进行索引重建

4、MySQL索引日常维护命令

4.1、创建索引

MySQL支持使用多种方法创建索引，比如使用CREATE TABLE语句在创建表的时候同时创建索引、ALTER TABLE语句修改表结构的时候创建索引以及CREATE INDEX为已经存在的表创建索引

· 创建普通索引（MySQL的InnoDB表加普通索引和唯一索引的时候，支持在线加索引，不会阻塞线上的读写操作）

#通过CREATE INDEX语句增加索引
CREATE INDEX idx_name ON student(name)  #为student表的name列创建名为idx_name的索引

#通过ALTER TABLE语句增加索引
ALTER TABLE student ADD idx_name(name)

#通过CREATE TABLE语句在创建表的同时创建索引
CREATE TABLE table1 (id int,name varchar(10),age int,INDEX idx_name ( name(10))); #创建table1这张表时给name列建立普通索引，长度为10，名为name_index 
CREATE TABLE table2 (id int,name varchar(10),age int,unique index id_index (id)); #给ID列创建一个名为id_index的唯一索引

· 创建唯一索引

ALTER TABLE books ADD UNIQUE uk_idx_book_name(book_name)

· 创建组合索引

CREATE TABLE table3 (id int , name varchar(10) , age int , index zuhe_index (id,name,age) );  #组合索引遵循从左匹配原则，必须要带有最左边ID列的查询才会使用到索引

# 组合索引最左匹配使用效果
EXPLAIN select name,age from table3 where id<3 \G 
EXPLAIN select name,age from table3 where id<3 and age <50 \G 
EXPLAIN select name,age from table3 where  age <50 \G  #possible_keys是null，说明没有匹配到索引

· 创建全文索引

CREATE TABLE test4(
id INT NOT NUll,
name VARCHAR(20),
info VARCHAR(255),
FULLTEXT INDEX full_idx_info(info(50))

4.2、查询索引

mysql > SELECT * FROM informaton_schema.statistics WHERE table_schema='database_test';  #查询指定库中的所有索引
mysql > SHOW CREATE TABLE table_name;  #通过查看表结构的方式查看索引
mysql > SHOW INDEX FROM table_name;    #查询表中存在的索引

4.3、删除索引

删除表中的列时，如果该列是索引的组成部分（比如name列是name,age列的联合索引，那么删除name列后联合索引就会剔除name列），那么该列会自动从索引中被删除。如果组成索引的列都被删除，那么索引也会自动被删除

#方法1：drop index 索引名 on 表名
DROP INDEX idx_name ON student

#方法2：alter table 表名 drop index 索引名
ALTER TABLE test2 DROP INDEX idx_name

5、索引失效原因

在SQL查询中有很多种原因都会导致索引失效，下面是一些常见的原因（这些原因跟数据库版本、查询数据量、数据选择度等都有关系，优化器会基于查询成本进行考虑，不是说下列情况一定会导致索引失效）

· 左模糊或者全模糊查询

如果使用like子句进行全模糊查询或左模糊查询（%在左边）将导致索引失效（在有覆盖索引的时候使用%开头进行模糊查询是有可能使用到索引的）

SELECT * FROM `user` WHERE `name` LIKE '%linux';

· 数据类型错误

当使用了错误的数据类型，导致数据库发生隐式转换时会导致索引失效，可以使用一些内置函数先进行类型转换

SELECT * FROM `user` WHERE height='180';  #如字段类型本身为int，where条件用了字符串类型
SELECT name,hire_date,department_id FROM employees WHERE department_id IN(80,90,100) AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');  #使用函数将字符串转换为日期类型

· 使用了函数计算

在SQL语句中如果在不恰当的位置使用了函数运算会导致索引失效。通常SELECT后面的函数不影响索引、WHERE条件表达式右边不影响索引，但是WHERE条件表达式左边一定会导致索引失效而全表扫描，这个时候要想办法将函数运算改为范围查找

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';  #即便create_time字段有索引也会因为函数而失效
SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'  #即便register_date有索引，但是索引的本质是排序，所以只会对register_date字段排序，不会对DATE_FORMAT(register_date) 排序

· 包含了空字符串

索引不存储空值，如果没有对索引列设置非空约束（NOT NULL），数据库会认为索引列可能存在空值导致失效。所以建议在进行表结构设计的时候最好为每个字段设置为NOT NULL，可以将默认值设置为空字符串

SELECT * FROM `user` WHERE address IS NULL  #不走索引
SELECT * FROM `user` WHERE address IS NOT NULL;  #走索引

· 进行数学运算

对索引列进行运算会导致索引失效，包含+、-、*、/、!=、 <>、IS NOT NULL。因为索引本身是排序的，但是加入运算或者不等判断后会，数据库需要对每一行进行判断处理，这样就让排序失去了意义。但是这里并不绝对，在有覆盖索引的时候使用 != 有可能使用到索引；当一个NULL值比例较高的行上进行IS NOT NULL判断的时候也有可能使用索引

SELECT * FROM `user` WHERE age - 1 = 20;

· 不符合最左匹配原则

在复合索引中索引列的顺序至关重要，如果不是按照索引的最左列开始查找，则无法使用索引。但是如果一个联合索引包含了A,B,C三列，在查询的时候只要同时使用了A,B,C三列，那优化器会自动对顺序进行调整保证能使用上索引，比如A,C,B。但是如果缺少了某个字段就必须符合最左匹配原则了。另外如果在联合索引中使用了范围查询也会导致最左匹配原则的停止

select * from linuxe where a=1 and c=3 and b=2  #如果是全等值查询，顺序即便和符合索引中不一致也是可以用到索引的

· 全表扫描或许更快

在MySQL的设计中，当查询结果达到了原表中的一定比例（大概30%左右），MySQL优化器会认为没有必要再使用索引，而直接采用全表扫描。因为全表扫描是顺序IO，而使用二级索引会有回表行为，这是随机IO。比如对于连续的数值使用BETWEEN来代替IN，因为IN和NOT IN所定义的值是不确定的，IN在部分情况下可以用到索引，NOT IN不会用到索引，而BETWEEN定义的是一个连续的区间可以用到索引

# IN不走索引
SELECT id, name, salary FROM worker WHERE salary IN (1, 2, 3); 

# BETWEEN走索引
SELECT id, name, salary FROM worker WHERE salary BETWEEN 1 AND 3;

· 隐式转换

多表进行关联查询时如果关联条件数据类型或字符集不同会产生隐式转换导致索引失效

· 多表关联查询

多表关联查询时，如果排序列不属于驱动表索引会失效。对于内连接来说，优化器可以自行选择驱动表，如果表的连接条件中只有其中一个字段有索引，则索引字段所在的表会被作为驱动表；如果两个字段都存在索引，优化器会选择数据量小的表作为驱动表

· OR操作符

当OR操作符左右条件存在没有索引的列时会导致整个查询的索引失效，因为某字段没有索引代表该列必须全表扫描，所以其它列上即便有索引也会失效

· 范围查询

如果有使用联合索引中的某个字段进行范围查找，需要把范围查找列放在索引的最右边

CREATE INDEX idx_age_name_cid ON student(age,name,classid)    #如果classid列不在索引的最右边，那么下面的语句就无法完美使用到索引
SELECT * FROM student WHERE age=30 AND name='tang%' AND classid>20;

二、执行计划（EXPLAIN）

MySQL的执行计划可以用于计算出数据库自身优化器在处理SQL时的资源成本，对于SQL优化起到关键作用。在MySQL中使用执行计划的命令是EXPLAIN（也可以写为desc、describe），通过该命令可以查看SQL在真正执行时所需要经历的一系列过程，通过执行计划的结果可以分析出一条SQL在进行查询时的查找顺序、是否有使用索引等有用信息。

从5.6.3版本之后，执行计划不光可以对SELECT语句生效，对于UPDATE、DELETE语句也是可以生效的。在使用执行计划对SQL进行分析时其SQL并不会真正执行，所以不用担心占用系统资源或者修改了数据。MySQL 8.0新增 explain analyze 执行计划，区别在于会真实进行SQL的执行，只是不返回查询结果，而是一个执行计划，这样可以得到更真实的执行流程。

1、MySQL执行计划语法

执行计划分为了传统格式、TREE格式和JSON格式，虽然最常用的是传统格式，但是相比内容最齐全的JSON格式来说，缺少了重要的成本指标

#使用默认的传统格式查看执行计划，缺少SQL成本信息
mysql > EXPLAIN SELECT id,name FROM table;

#以JSON格式进行输出，信息比默认的传统模式更详细，增加了query_cost查看SQL成本信息 
mysql > EXPLAIN FORMAT=JSON SELECT id,name FROM table;

#8.0.16开始支持的tree模式，层次结构稍有变化
mysql > EXPLAIN FORMAT=TREE SELECT id,name FROM table;

2、MySQL执行计划说明

下图是一个执行计划语句执行后的返回结果，可以看到包含了多列信息，每一列信息所代表的含义各不相同

· id列

每个ID表示一个独立的查询，通常有多少个SELECT关键字就会有多少个ID（部分情况下优化器会自动进行改写，存在ID数比SELECT关键词的数量少的情况），所以ID数越少越好。ID大的SQL语句会优先执行，ID值相同的为同组SQL按照从上往下的顺序执行。如果有使用临时表（比如UNION操作）等情况会出现ID为空的记录，这些语句会最后执行。排在前面的为驱动表，后面的是被驱动表。

· select_type列

在id列中每个id都代表了一个SELECT的出现，而在select_type列中则说明了每一个SELECT语句的查询类型

· table列

SQL所涉及到的表，可以看这个分析具体是查哪张表有问题。如果一个SELECT关键词涉及多张表，那么在该关键词所属的ID下就会出现多个表的情况。如果table列显示为derived表示该表是来自于一个查询的结果

· type列

执行计划最关键的列，记录了SQL运行时对某个表数据的查找方式，比如是否有全表扫描。不同的查找方式性能不同，按照性能从高到底排序为system-->const-->eq_ref-->ref-->range-->index-->all，通常需要对ALL或者INDEX类型做优化，至少达到range级别

· possible_keys与key列

分别显示了可能会用到的索引和实际用到的索引。如果possible_keys非空而key为空，则要检查语句是否存在索引失效的情况

· key_len列

计算索引长度，对单列索引没有意义，主要用于计算联合索引命中情况。比如一个包含4个字段的联合索引，可以通过key_len计算具体哪些列的索引有被使用。比如字段'user_name char(20) NOT NULL DEFAULT'，由于char(20)且非空，utf8字符集占用3个字节，那么最终长度就是20*3=60。如果允许NULL需要额外+1字节，VARCHAR变长额外+2字节。又假设字段类型为varchr(10)、允许NULL、字符集为UTF8，那么索引长度就是10 * 3 +1（NULL）+2（变长字段）；有字段为varchr(10)且不允许NULL，那么长度就是10 * 3 +2（变长字段）

CREATE TABLE `t1` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`name` char(20) NOT NULL DEFAULT '', 
`name1` char(20) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY idx_key_name_name1 (`name`,`name1`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 

EXPLAIN select * from t1 where name='atlas' and name1='yayun'; # explain中key_len=121，计算方式为20 * 3 + (20 * 3 +1)=121

· ref列

多表查询时表之间字段引用关系，显示索引哪一列被使用了

· rows列

预估需要扫描的数据行数，也基本是回表的次数，值越小越好。如果表的.ibd文件存在大量碎片空洞，需要使用optimize回收空间后这个预估值才会更准确

· filtered列

经过rows列扫描后满足查询条件的实际行数百分比，这个比例越高代表无效扫描的次数越少。如rows列显示扫描10000行，filterd列显示为50，代表有50%的行是有效的，即有5000行满足查询要求。该列对于多表连接的意义更大，比如t1表是驱动表、t2表是被驱动表，t1表中rows列为9688，filtered为10，代表着驱动表t1需要进行9688*0.1=968次的查询

· extra列

额外信息列
· Using temporary：表示需要使用临时表来存储查询结果，通常是执行DISTINCT去重或者GROUP BY等操作但是缺少索引导致
· Using filesort：查询语句中使用到了排序语句，但是需要进行排序的字段上没有索引，建议where哪些字段就order by哪些字段。但是需要注意的是所有的排序操作都是在WHERE条件过滤后再执行的，所以如果WHERE条件能过滤大部分数据，那么剩下的少量数据出现了排序影响也不大

· Using index：使用了覆盖索引，无需回表，该查询语句的性能非常好
· Using join buffer：代表多表 JOIN 连接没有走索引
· Using where：在查询语句中使用到了where子句，但是该字段不存在索引，可考虑为该字段增加索引进行优化
· Using index condition：使用了索引下推特性

# 创建一个复合索引 
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ; 

# using filesort 
explain select *from test02 where a1='' order by a3 ; 
explain select *from test02 where a2='' order by a3 ; 

# 无using filesort 
explain select *from test02 where a1='' order by a2 ; 

# using temporary explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; 

# 无using temporary 
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ; 

# where后面是a2和a4，按照SQL执行顺序先查出了a2和a4然后再对其进行分组，所以不需要temporary 
explain select * from test03 where a2=2 and a4=4 group by a2,a4; 

# where后面是a2和a4，但是最后分组是a3字段，此时确实相关数据，因此需要临时表，出现using temporary 
explain select * from test03 where a2=2 and a4=4 group by a3;

3、索引成本与索引选择问题

在执行SQL前优化器会分析所有可能的执行计划，然后根据CBO（基于成本的优化器）方法选择一个它认为成本最低的方法去执行，如下语句可以查看表中索引的基数，其中cardinality列就是语句根据索引可能使用到的查询可能性，数值越大越接近表的行数，说明区分度越高

#查看表中索引的基数 
show index from table_name;

如果explain预估的rows值跟实际情况差距比较大，或者原本应该选择索引的语句最终放弃了索引，一般是因为索引统计不准确导致的。还有一种情况就是如果全表扫描的成本比二级索引成本低，就会存在有索引但是不使用的问题（为什么全表扫描比二级索引查询快？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要慢，因此这取决于回表的记录数）

# 查看成本方法示例
EXPLAIN FORMAT=json SELECT o_custkey,SUM(o_totalprice) FROM orders GROUP BY o_custkey

在有些时候会因为索引统计信息不同导致同样的语句出现了不同的执行计划，这个时候可以使用analyze table重新统计索引信息，该操作会重新计算索引基数，解决部分场景下索引预估出现了明显问题的情况

#重新统计索引信息
analyze table test_table

大部分情况可以通过analyze命令解决，或者强制指定索引进行查询。甚至还可以通过删掉不必要的索引来规避优化器选错索引的问题

select * from tast_table force index(a) where a between 1 and 1000 and b between 50000 and 100000 order by b limit 1;

数据库mysql索引