可能出现全表扫描场景

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 * from employees limit 90000,5;
-- 优化为
mysql> select * from employees where id > 90000 limit 5;

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

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

mysql> select * from employees ORDER BY name limit 90000,5;
--优化为
mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.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使用，用完之后立即关闭）

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
set session optimizer_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%”，一般情况下会使用索引下推

EXPLAIN SELECT * FROM employees WHERE name like  'LiLei%';
EXPLAIN SELECT * FROM employees WHERE name like  'Li%' and age = 30 AND position = 'dev';

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

EXPLAIN SELECT * FROM employees WHERE name > 'L' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

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

EXPLAIN SELECT * FROM employees WHERE name > 'A' AND age = 22 AND position ='manager'; -- 造成全表扫描

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

线上慢sql如何优化

1：查询慢查询日志，确定线上哪条sql慢

• 执行时间queryTime：查看sql执行了多长时间
• 扫描行数rows_examined:查看扫描了多少行数据
• 返回行数rows_sent:查看返回了多少行数据
• 是否存在排序，临时表，锁等待等额外开销
  如果扫描行数百万级行，只返回几十行，说明过滤条件没有索引。
  如果扫描行数不多，执行时间确长，可能是在等锁或者数据库资源不足

2：查看执行计划
访问类型type ：走索引(ref/range)还是全表扫描(All)
实际使用索引key： 有时候实际建了索引但数据库执行时没有用上。 （possible_keys）
预估扫描行数rows: 扫描行数越多花费成本越高
额外操作Extra：是否存在文件排序，临时表，大量回表

3：排查索引是否失效
对查询条件做函数处理，比如原本用时间查询，sql却对条件套用YEAR函数，数据库会丢弃索引，正确做法改成时间范围查询
模糊查询时关键字带了%
是否存在隐式类型转换，如字段本是字符串，查询时当数字来查，数据库可能先做类型转化。
联合索引不符合最左前缀原则
回表是否太多，优化：减少查询字段，设计覆盖索引
排序：如果排序字段没有合适索引，数据库会把大量数据查出来单独排序，非常耗资源
深分页：数据库需要把前面数据扫出来在丢掉只返回需要数据，页数越深性能越差。优化：游标分页，记录上一次分页的游标位置，下次直接从这个位置往后查。

4：排查sql是否存在锁等待
有些sql本身很简单，执行时间却很长，可能在等锁。
排查：有没有长事务，有没有锁等待，有没有死锁。

5：数据库整体资源
sql平时很快，突然很慢，可能是数据库整体压力上来了。如，cpu打满，磁盘读写很高，连接数耗尽，大查询大批量更新。
所以线上查询一定要结合监控，看cpu，内存，磁盘，连接，慢查询数量，锁等待数量，只有把sql和数据库状态放在一起查看才能判断sql是真慢还是被系统资源拖慢。