一、索引类型

1、聚集索引

一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。查询速度快，聚集索引的叶子节点上是该行的所有数据，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致)。主键!=聚集索引。

注：mysql数据库innodb引擎里面，主键的确就是聚集索引。但是myisam引擎里面主键也不是聚集索引

解释一：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据

2、辅助索引(非聚集索引)

一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个'书签'，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据。

聚集索引与辅助索引的区别：叶子节点是否存放的为一整行数据

覆盖索引：

指从辅助索引中就能获取到需要的记录，而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

1）select * from table limit 9000000,100; -深分页，耗时比较长

2）select * from test where id >= (select id from test limit 9000000,1) limit 0,100 -利用主键聚簇索引

二、索引失效

优化器：当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效一、单索引时效的情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

1）explain select * from t_user where name like 'name1%' -走索引

2） explain select * from t_user where name like '%name1' -索引失效

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

1） explain select * from t_user where name = 'name1' or name='name2' -走索引

2） explain select * from t_user where name = 'name1' or age=1 -索引失效(age无索引)

3、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

1） explain select * from t_user where name = 'name1' -走索引

2） explain select * from t_user where name = 1 -不走索引

4、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引不一定失效，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小

explain select * from t_emp where ename is null -走索引

原理参考文档：mp.weixin.qq.com/s/CEJFsDBiz…

**5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 **

1） explain select * from t_user where name !='name1' -不走索引

6、对索引字段进行计算操作、字段上使用函数

1） explain select * from t_user where left(name,2)='na' -不走索引

7、查询的数量是大表的大部分，通常30％以上，也有其它因素的干扰

t_user ：10条不同日期的记录

1）explain select * from t_user where create_time > '2021-11-01' -不走索引

2）explain select * from t_user where create_time > '2021-11-06' -不走索引

3）explain select * from t_user where create_time > '2021-11-07' -走索引

优化：

1）可以使用force index 强行走索引

explain select * from t_user force index(idx_create_time) where create_time > '2021-11-01' -走索引

问题：

为什么优化器查表为什么不走索引呢？ 1)、优化器会根据检索比例、表大小、IO大小进行评估是否走索引，比如单次查询数据量过大

2）是否回表查询

explain select * from t_user where id >1 -走索引，主键不用回表，IO次数减少，性能将提升不少

二、联合索引失效的情况

备注：name和sex为联合索引

1、联合索引所有成员均以and 出现时与顺序无关，都能生效

explain select * from t_student where sex = 0 and name ='张三 -走索引 2 、条件语句中出现or 则联合索引无效

explain select * from t_student where sex = 0 or name ='张三' -索引无效 3、条件语句中缺少排在第一位的索引字段，整个联合索引失效

explain select * from t_student where sex = 0 -不走索引

4、欲使联合索引要生效，排在第一位的索引为必须出现，位置不限

explain select * from t_student where age > 18 and name ='name1' -走索引 5、Mysql优化器选择

三：什么情况下不建议使用索引

1、唯一性差(特殊情况排除);

2、频繁更新的字段不用（更新索引消耗）;

3、where中不用的字段;

四、表优化

1、optimize

MySQL执行命令delete语句时，如果包括where条件，并不会真正的把数据从表中删除，而是将数据转换成了碎片，但过多的碎片，对数据的插入操作是有一定影响的。

针对MySQL的不同数据库存储引擎，在optimize使用清除碎片，回收闲置的数据库空间，把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。

通过下面的命令可以查看表中的碎片数量和索引等信息：

mysql > show table status like 'table_name'

查询结果中：

Index_length 代表索引的数量

Data_free 代表碎片数量，data_free选项代表数据碎片。

注意事项：

由于命令optimize会进行锁表操作，所以进行优化时要避开表数据操作时间，避免影响正常业务的进行。

2、Analyze

query执行流程：

词法&语法解析

按照约定的SQL语句规则，把输入的SQL语句从字符串转化为格式化结构(Stmt)，如果SQL语句存在语法错误，都会在这个环节报错。

语义解析

语义解析类似一个翻译器，把外部输入的可视化的对象翻译为数据库内部可识别的对象（比如把Stmt中以字符串记录的表名称转化为数据库内部可识别的oid），如果语句存在语义错误(比如查询的表对象不存在)，数据库会在这个环节报错。

查询重写

根据规则将“语义解析”的输出等价转化为执行上更为优化的结构，比如把查询语句中的视图逐层展开至最低层的表查询。

查询优化

数据库确认SQL执行方式、生成执行计划的过程

查询执行

根据执行计划执行SQL并输出结果的过程

数据库查询优化器分为两类：基于规则的优化器(Rule-Based Optimizer，RBO) 和基于代价的优化器(Cost-Based Optimizer，CBO)。

1、RBO是一种基于规则的优化，对于指定的场景采用指定的执行方式，这种优化模型对数据不敏感；

3、SQL的写法往往会影响执行计划，不了解RBO的细则的人员开发的SQL性能不可控，因此RBO逐渐被抛弃，目前GaussDB等数据库厂商的优化器都是CBO模型。CBO模型是根据SQL语句生成一组可能被使用的执行计划，并估算出每种执行计划的代价，最终选择选择一个代价最小的执行方式。

3、大规模数据导入/UPDATE/DELETE等操作，会导致表数据行数变化，新增的大量数据也会导致数据特征发生大的变化，此时需要对表重新收集统计信息

Analyze Table 分析表:

需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

生产上操作的风险： 1、analyze table的需要扫描的page代价粗略估算公式：sample_pages * 索引数 * 表分区数。 2、因此，索引数量较多，或者表分区数量较多时，执行analyze table可能会比较费时，要自己评估代价，并默认只在负载低谷时执行。 3、特别提醒，如果某个表上当前有慢SQL，此时该表又执行analyze table，则该表后续的查询均会处于waiting for table flush的状态，严重的话会影响业务，因此执行前必须先检查有无慢查询。

Mysql索引优化汇总