mysql-索引优化

可能出现全表扫描场景

1.索引列上操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 2.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 3.< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引 4.is null,is not null 一般情况下也无法使用索引 5.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作 6.字符串不加单引号索引失效 7.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描 8.联合索引第一个字段用范围可能不会走索引：mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高。如果范围小也可能走索引

索引优化

1.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句. 2.范围查询查询时可以将大的范围拆分成多个小范围。 3.like以通配符开头（'$abc...'） -使用覆盖索引，查询字段必须是建立覆盖索引字段 -如果不能使用覆盖索引则可能需要借助搜索引擎

Order by与Group by优化

排序方式

Using filesort 功能：使用文件排序 使用条件：未使用索引 Using index 功能： 扫描索引本身完成排序 使用条件： order by语句使用索引最左前列 使用where子句与order by子句条件列组合满足索引最左前列。

优化建议

-尽量使用索引排序，遵循索引建立（索引创建的顺序）时的最左前缀法则 -尽量用覆盖索引 -group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。 -groupby的优化如果不需要排序的可以加上order by null禁止排序 -where高于having，能写在where中的限定条件不要在having限定

分页查询优化

1、根据自增且连续的主键排序的分页查询

mysql>select*fromemployeeslimit90000,5;-- 优化为mysql>select*fromemployeeswhereid>90000limit5;

问题：某些记录被删后，主键空缺，导致结果不一致 此方式必须要满足以下条件： -主键自增且连续 -结果是按照主键排序的

2、根据非主键字段排序的分页查询

mysql>select*fromemployeesORDERBYnamelimit90000,5;--优化为mysql>select*fromemployees einnerjoin(selectidfromemployeesorderbynamelimit90000,5)edone.id=ed.id

优化关键要点：让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

Join关联查询优化

-关联字段加索引，让mysql做join操作时尽量选择NLJ算法 -小表驱动大表，表关联是如果明确知道哪张表是小表，可以用straight_join写法固定连接驱动方式。（慎用）

小表定义：两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

in和exsits优化

in ： 先执行子查询，结果存入物化表（临时表）,在执行主查询 exsits : 先执行主查询，拿出一行数据与子查询判断是否匹配，循环处理。

优化原则：小表驱动大表，即小的数据集驱动大的数据集。（in 与exists 保证先执行的查询结果集小） IN 适合子查询结果集小（先查后判断）， EXISTS 适合主查询结果集小（边查边判断）

count查询优化

count(*) :不取值按行累加，最优 count(1) :不需要取出字段统计，就用常量1做统计,扫描到数据直接在内存+1，最后统计1的数量 count(field)：将字段从索引树拿出，在统计字段数量。不会统计字段为null值的数据行 count(id) ：只统计 id 列值不为 NULL 的行数，因为 id 通常是主键（主键不允许 NULL），COUNT(id) 实际上等于 COUNT(*)（但可能走主键索引）

优化方式： 1，查询mysql自己维护的总行数，myisam支持，innodb不支持 2，使用预估值：表总行数的估计值查看show table status like 'tableName'（innodb） 3，将总数维护到Redis里，这种方式可能不准，很难保证表操作和redis操作的事务一致性 4，增加数据库计数表，插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

–

为什么myisam支持查询mysql自己维护的总行数，innodb不支持？ InnoDB 必须支持事务和多版本并发控制（MVCC），不同事务在同一时刻看到的行数可能不同，无法提前缓存一个精确的总行数。所以，InnoDB 必须实时扫描，根据当前事务的 Read View 来判断哪些行可见。对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算。

–

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少检索性能应该更高，所以count(字段)>count(主键 id) 字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

索引设计原则

-代码先行，索引后上 -联合索引尽量覆盖条件 -不要在小基数字段上建立索引 -长字符串我们可以采用前缀索引 -where与order by冲突时优先where -基于慢sql查询做优化

mysql是如何选择索引的

MySQL 选择索引不是简单地看有没有索引，而是基于统计信息，通过成本模型计算各种执行方案的 I/O、CPU、内存开销，选择**预估成本最低**的方案。这个决策受索引基数、查询选择性、回表代价、表大小等多个因素影响。 可以使用trace工具查看如何选择索引，查看trace字段可知使用索引扫描的成本，最终mysql根据扫描成本确定查询方式（开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭）

setsessionoptimizer_trace="enabled=on",end_markers_in_json=on;‐‐开启traceselect*fromemployeeswherename>'a'orderbyposition;SELECT*FROMinformation_schema.OPTIMIZER_TRACE;setsessionoptimizer_trace="enabled=off";‐‐关闭trace

一些字段解释

explain

列表字段解释 1. id列 ： id时select的编号，d列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行 2. select_type：查询类型simple,primary,subquery,derived 3. table: explain这一列访问的表名称 4. partition：基于分区表使用时，显示访问的分区 5. type：关联类型/访问类型，确定如何查询表、查询表的大概范围。 从最优到最差 ：system>const>eq_ref>ref>range>index>all，得保证查询达到range级别，最好达到ref - 空值: 直接通过索引获取SQL执行结果，不需要访问表 - system: const 的特例，表里只有一条元组匹配 - const：用主键或唯一索引进行等值查询，优化器在编译阶段就能确定返回一行记录。如 “WHERE id = 100” - eq_ref: 用主键或唯一索引做等值关联，并且最多只返回一条匹配记录。常用于join. 如“JOIN ... ON t1.id = t2.ref_id” - ref:用普通索引或唯一索引的部分前缀进行等值查询，可能返回多条记录 - range:使用索引进行范围扫描 - index:扫描整个索引树, - all:全表扫描，扫描聚簇索引所有叶子节点 6. prossible_key ：可能使用哪些索引来查找 7. key ：实际采用的索引 8. ken_len：mysql在索引里使用的字节数 计算规则： - 字符串，utf-8，一个数字或字母占1个字节，一个汉字占3个字节 char(n)：如果存汉字长度就是 3n 字节 varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串。 - 数值类型 - tinyint：1字节 - smallint：2字节 - int：4字节 - bigint：8字节 - 时间类型 - date：3字节 - timestamp：4字节 - datetime：8字节 -如果字段允许为 NULL，需要1字节记录是否为 NULL 9. ref：表示key 列使用的索引在查找时所比较的值（是关联表的列还是常量） 10. row：预计扫描的行数 11. filtered：估算出将剩余需要检查的表行数，最终返回的行数=rows × filtered / 100 公式。 假如：row为1000，filtered为10，最终返回的行数可能为 1000*10%=100。 filtered是索引找到的行中，真正满足 WHERE 条件的百分比。值越低说明索引过滤效果越差，大量回表后被过滤掉，需要优化索引设计 13. extra ：额外信息 Using index：使用覆盖索引 Using where：使用 where 语句来处理结果 Using index condition：使用索引下推-使用索引进行条件过滤，过滤完索引后找到所有符合索引条件的数据行随后用 WHERE 子句中的其他条件继续过滤 Using temporary：mysql需要创建一张临时表来处理查询 Using filesort：使用文件排序 Using index for skip scan ：使用索引跳跃

type类型场景

const :通过主键或唯一索引进行等值查询（不允许 NULL），查询优化器在编译阶段就能确定只返回最多一行记录，因此将这一行的列值当作常量处理。场景：主键等值查询，唯一索引等值查询，联合唯一索引所有列都用上 eq_ref:使用唯一索引或主键索引进行等值查询，并且最多只返回一条匹配记录。场景:主键等值 JOIN ,唯一索引等值 JOIN,子查询中的唯一匹配 ref：使用普通索引或唯一索引的部分前缀进行等值查询，可能返回多条记录。场景：普通索引等值查询，唯一索引但值为 NULL，普通索引的 JOIN 查询 range：使用索引进行范围扫描，只扫描索引中某个范围内的部分数据，而不是全部。场景:范围运算符（最常见）,前缀模糊查询（LIKE '张%'） index:扫描整个索引树（从根节点到尾节点），不进行范围筛选，通常发生在查询需要排序且排序字段有索引、查询的所有字段都在索引中（覆盖索引）且需要遍历。场景：索引排序（无 WHERE 条件），覆盖索引 + 无筛选条件，聚合函数 + 索引列 all:扫描整个数据表（聚簇索引/堆表）的所有行，从第一行扫到最后一行

覆盖索引：

索引列包含SQL查询时所有的字段，此时不需要回表查询

索引下推（MySQL 5.6）

将 WHERE 条件中可以用到索引的部分，下推到存储引擎层进行过滤，减少回表次数。

索引跳跃（MySQL8.0）

当联合索引某最左列的唯一值极少，区分度不高时，myslq8.0可能会使用索引跳跃扫描（打破最左前缀法则）。

文件排序

单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序 双路排序（回表排序模式）：根据条件取出相应的排序字段和可以直接定位行数据的行 ID，在 sort buffer 中进行排序，排序完后需要再次通过id回表取回所需字段。 MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式 -字段的总长度小于max_length_for_sort_data 使用单路排序 -字段的总长度大于max_length_for_sort_data使用双路排序

表关联算法

NLJ算法：嵌套循环连接 Nested-Loop Join算法 （关联字段有索引时使用） 过程：循环读取驱动表每一行，通过关联字段定位被驱动表满足条件的行，最后取出两张表结果合集。 BNL算法：基于块的嵌套循环连接 Block Nested-Loop Join算法（关联字段无索引使用） 过程：驱动表数据读入join Buffer,扫描被驱动表，将被驱动表每行数据依次与join Buffer驱动表每行数据作对比，返回满足条件结果集。 备注：join buffer 一次放不下驱动表数据，可以分段进行存放，分段段数=全表扫描被驱动表次数

测试

1：like “aa%”，一般情况下会使用索引下推

EXPLAINSELECT*FROMemployeesWHEREnamelike'LiLei%';EXPLAINSELECT*FROMemployeesWHEREnamelike'Li%'andage=30ANDposition='dev';

2 ：存储引擎不能使用索引中范围条件右边的列

EXPLAINSELECT*FROMemployeesWHEREname>'L'ANDage=22ANDposition='manager';EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage>22ANDposition='manager';

using index condition使用到索引下推：扫描所有 name > ‘L’ 的行，在索引层判断 age 和 position（不满足的跳过），只回表满足条件的行。

EXPLAINSELECT*FROMemployeesWHEREname>'A'ANDage=22ANDposition='manager';-- 造成全表扫描

3.范围查询查询时可以将大的范围拆分成多个小范围。