性能调优-Mysql-思维导图

目录

• • 索引底层数据结构与算法
    • 索引是帮助MySQL高效获取数据的排好序的数据结构
  • Explain详解与索引最佳实践
    • 索引最佳实践
    • 索引类型
  • 一条SQL在MySQL中是如何执行的
    • MySQL的内部组件结构
  • Mysql索引优化实战
    • 索引下推
  • 深入理解Mysql事务隔离级别与锁机制
    • 锁分类
    • 锁的作用范围分类
    • 锁的模式分类
    • 锁的类型分类
    • 加锁原则
  • 深入理解MVCC和BufferPool缓存机制
    • Innodb引擎的三大特性
  • MySQL日志
  • MySQL主从架构及读写分离
• 索引优化总结
  • 索引失效
  • Order by与Group by优化
  • 分页查询优化
  • join语句怎么优化
  • in和exsits优化
• SQL注意点
• 高性能表结构及索引设计最佳实践
  • 数据库表设计
  • 高性能的索引创建策略
• 从架构师角度全局理解Mysql性能优化
  • 优化SQL查询方法论
• Mysql执行原理之索引合并详解
• Mysql内核查询优化规则详解

Mysql Xmind 小结

索引底层数据结构与算法

索引是帮助MySQL高效获取数据的排好序的数据结构

1. 索引使用B+树，不使用B树作为数据结构的原因？

B树非叶子节点也会存储数据，导致树的高度很高，查磁盘次数增加；
B+树非叶子节点不存储数据只存储索引，故而可以放更多的索引，举例：mysql页大小/节点大小为16k，一颗高度为3的B+树能存储的数据为1170117016= 2千万记录；（16KB/8B+6B = 1170、16KB/1KB=16：主键一般是bigint类型占用8B、存储下一层索引的地址大小占用6B、叶子节点按照1KB计算）
B+树的叶子节点间用指针相连，从而提高区间访问的性能；
Hash索引仅能满足 “=”，“IN”，不支持范围查询，且有hash冲突问题

2. 存储引擎

MyISAM索引文件和数据文件是分离的(非聚集)；InnoDB存储引擎(聚集)

3. 联合索引的底层存储结构长什么样？

B+树，符合索引最左前缀原理

图源：mysql一张表到底能存多少数据

Explain详解与索引最佳实践

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL  https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

1. select_type列   表示对应行是简单还是复杂的查询。

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
primary(复杂查询中最外层的select)  subquery(select子查询不在from子句中)  derived(子查询包含在from子句中)

2. type列    表示关联类型

依次从最优到最差分别为：system > const > eq_ref > ref > range(保证达到) > index > ALL

## 举例 
有  tb_order 表：PRIMARY KEY   (`id`), KEY   `key_org_id` (`org_id`)
和  tb_order_material  表：PRIMARY KEY   (`id`)
和  tb_org  表：PRIMARY KEY   (`id`)
和  tb_org  表：PRIMARY KEY   (`id`)

# NULL 
explain select min(id) from tb_order;

# const, system 表最多有一个匹配行, 只读取1次, system是 const的特例
explain select * from tb_order where id = 123;
explain select * from (select * from tb_order where id = 123) temp;

# eq_ref  主键/唯一索引的所有部分被连接使用 被关联的表(右表)只有一行满足JOIN条件
explain select * from tb_order_material m LEFT JOIN tb_order o on o.id = m.order_id
explain select * from tb_order o LEFT JOIN tb_org r on o.org_id = r.id

# ref 普通索引匹配多行 / 联接仅使用键的最左前缀
explain select * from tb_order where org_id = 1;
explain select * from tb_order_material m RIGNT JOIN tb_order o on o.id = m.order_id

# range 索引的范围查询
explain select * from tb_order where id > 1000;
explain select * from tb_order where org_id > 1;

# index 索引全扫描
explain select org_id from tb_order;

# ALL
explain select * from tb_order where user_id = 1;

3. Extra列

## Extra列 
举例 有 tb_express_info 表： PRIMARY KEY (`id`), 
KEY `key_org_office_id` (`org_id`,`org_office_id`) USING BTREE

tb_express_management 表： PRIMARY KEY (`id`)

tb_express_info 表： PRIMARY KEY (`id`),
  KEY `key_org_office_id` (`org_id`,`org_office_id`) USING BTREE,
  KEY `key_m_sr` (`express_management_id`,`express_sender_receiver_id`) USING BTREE,
  KEY `key_o_p` (`order_num`,`pay_method`,`create_time`) USING BTREE

# Using index 只使用到索引部分，整个查询结果只通过辅助索引就能拿到结果，不用回表
# 不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
explain select org_id from tb_express_info;

# Using index condition 搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
# 查询的列不完全被索引覆盖，需要回表，效率低于Using where?和Using index
# 跟 Using index 的区别在于查询出来的字段包括不在索引里的
# 如果出现了using index condition，是使用了index condition pushdown索引下推
explain select * from tb_express_info WHERE org_id > 3;

# Using where 查询的列未被索引覆盖，where筛选条件非索引的前导列/非索引列，走全表
# operating_user_id 字段没有建索引、org_id建立索引但效率还不如全表
# 不管有没有通过索引查找，只要加载了数据到内存进行where条件筛选，都是Using where
explain select * from tb_express_info WHERE org_id > 1;
explain select * from tb_express_info WHERE user_id > 1  

# Using index for group-by
explain select DISTINCT org_id from tb_express_info   

# Using temporary 需要创建一张临时表来处理查询，可优化，查询字段没有索引；
# 对dossier_num加上索引后查询时extra是using index
explain select DISTINCT dossier_num from tb_express_info 

# Using filesort 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
# 可优化，ORDER BY字段没有索引，对dossier_num加上索引后查询时extra是using index
explain select DISTINCT id from tb_express_info ORDER BY dossier_num

4. key_len列，
   varchar(n)：如果存汉字则长度是3n+2字节；
   timestamp：4字节；
   datetime：5字节（5.6之后）；
   tinyint：1字节；
   smallint：2字节 ；
   int：4字节；
   bigint：8字节；
   如果字段允许为 NULL，需要1字节记录是否为 NULL

# key_len = 8
explain select * from tb_express_info WHERE org_id = 1;
explain select * from tb_express_info WHERE org_id > 3; # 索引字段用范围可能走索引可能不走
explain select * from tb_express_info WHERE org_id > 0; # 走全表
explain select * from tb_express_info WHERE org_id = 1 and org_office_id like '11%';

# key_len = 17 
# 走全表 范围过大全表扫描
explain select * from tb_express_info WHERE org_id = 1 and org_office_id > 1; 
# 第二个索引字段范围小 可能走索引
explain select * from tb_express_info WHERE org_id = 1 and org_office_id < 3;
explain select * from tb_express_info WHERE org_id = 1 and org_office_id = 3;

# key_len = 63  Using index condition
explain select * from tb_express_info WHERE order_num like 'EX%';
# key_len = 65  Using index condition
explain select * from tb_express_info WHERE order_num like 'EX%' and pay_method = 1;
# key_len = 71  Using index condition
explain select * from tb_express_info WHERE order_num like 'EX%' and pay_method = 1 and create_time = '2021-06-05 14:17:07';

# key_len = 63  索引断了
explain select * from tb_express_info WHERE order_num = 'EX';
# key_len = 63  Using index condition
explain select * from tb_express_info WHERE order_num = 'EX' and create_time = '2021-06-17 17:54:55';
# key_len = 63  Using index condition
explain select * from tb_express_info WHERE order_num = 'EX' and create_time = '2021-06-17 17:54:55' order by pay_method;

索引最佳实践

覆盖索引满足最左前缀法则：如果索引了多列，要遵守最左前缀法则，查询从索引的最左前列开始并且不跳过索引中的列，遇到范围查询(>、<、between、like)会停止匹配；

尽量使用覆盖索引，只访问索引的查询, 减少 select * 语句;

不在索引列上做任何操作（计算、函数）;

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描；
< 小于、 > 大于、 <=、>=、in 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引；is null，is not null 一般情况下也无法使用索引；

like KK%相当于常量，%KK和%KK% 相当于范围；like KK%一般情况都会走索引, 模糊查询会用到下推;

不要跳过/颠倒索引字段顺序;

索引 where 与 order by冲突时优先where;

联合索引范围查询后的条件不能用索引 范围查询字段可能用到索引可能没用到;

索引类型

mysql索引类型：FULLTEXT、NORMAL、SPATIAL、UNIQUE的详细介绍

• Normal 普通索引

表示普通索引，大多数情况下都可以使用

• Unique 唯一索引

表示唯一的，不允许重复的索引，索引列的值必须唯一允许有空值，如果是组合索引列值的组合必须唯一。
Unique(要求列唯一)
Primary Key(primary key = unique + not null 列唯一)
Primary Key是拥有自动定义的Unique约束，每个表中可以有多个Unique约束，但是只能有一个Primary Key约束

• Full Text 全文索引

表示全文收索，在检索长文本的时候，效果最好，短文本建议使用Index,但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多

• SPATIAL 空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

一条SQL在MySQL中是如何执行的

MySQL的内部组件结构

MySQL 大体可以分为 Server 层和存储引擎层两部分

Server层主要包括连接器、词法分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

Store层存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。

分析器：词法分析识别字符串含义，语法分析会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

优化器：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，

Mysql索引优化实战

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

-- 插入一些示例数据
drop procedure if exists insert_emp; 
delimiter ;;
create procedure insert_emp()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100000)do                 
    insert into employees(name,age,position) values(CONCAT('luge',i),i,'dev');  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_emp();

# 覆盖索引优化
in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

# Order by与 Group by优化 len=74 78 140 74
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 12;
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 12 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'manager';
# key_len = 74
# age索引列用在排序过程中 因为Extra字段里没有using filesort(Using index condition)
# 用到了索引下推
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'manager' ORDER BY age; 

# 由于用了position进行排序，跳过了age，出现了Using filesort
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;

# 只用到索引name，age和position用于排序，无Using filesort
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age, position;

# 出现了Using filesort，因为索引顺序颠倒了
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position, age;

# Using index condition; Using filesort 对于排序来说，多个相等条件也是范围查询
EXPLAIN SELECT * FROM employees WHERE name in( 'LiLei', 'sad') ORDER BY age, position;


优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。
index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
  1) order by语句使用索引最左前列。
  2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。
对于group by的优化如果不需要排序的可以加上order by null禁止排序。
注意，where高于having，能写在where中的限定条件就不要去having限定了。

# 分页查询优化
EXPLAIN select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees e inner join (select id from employees order by name limit 70000,5) ed on e.id = ed.id;

# Join关联优化优化
# Nested-Loop Join (Extra未出现 Using join buffer) 有索引性能更高
EXPLAIN select * from employees e inner join employees ed on e.id = ed.id;
# Block Nested-Loop Join --> hash join  被驱动表关联字段没索引的关联查询
EXPLAIN select * from employees e inner join employees ed on e.age = ed.age; 

# https://www.cnblogs.com/clarke157/p/7912871.html
# in 和 exsits 优化  原则：小表驱动大表，即小的数据集驱动大的数据集
# in：当B表的数据集小于A表的数据集时，in优于exists ( 外查询 > 子查询 )
 select * from A where id in (select id from B)
# exists：当A表的数据集小于B表的数据集时，exists优于in  ( 外查询 < 子查询 )
 select * from A where exists (select 1 from B where B.id = A.id)
# in()只执行1次,exists()会执行A.length次

# count(*)查询优化
EXPLAIN select count(1) from employees;
EXPLAIN select count(*) from employees;
SHOW TABLE STATUS like 'employees'

索引下推

联合索引上的优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，提升了存储引擎层查询的效率、减轻server层压力（下推，即Server层将某些索引列的判断条件下推到存储引擎层查）。索引下推（Index Condition Pushdown ICP）是5.6版本中新特性，like KK%其实就是用到了索引下推优化。

explain select * from tb_express_info where order_num like 'EX%' and pay_method = 1and create_time = '2021-06-05 14:17:07; 根据最左前缀，这里不仅使用联合索引的模糊查询字段查询(order_num)，也使用到后面字段（pay_method, create_time）过滤从而减少回表次数(不是直接回表，把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断)

深入理解Mysql事务隔离级别与锁机制

1.Mysql事务及ACID特性详解
2.Mysql事务隔离级别详解
3.Mysql锁机制详解
4.Mysql锁优化详解

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据库操作的类型（锁模式）分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

• 从对数据操作的粒度分，分为表锁和行锁
• 从锁类型上分，分为记录锁、间隙锁、临键锁、意向锁、插入意向锁

锁模式可以和锁类型任意组合，如：
locks gap before rec，表示为gap锁：lock->type_mode & LOCK_GAP
locks rec but not gap，表示为记录锁，非gap锁：lock->type_mode & LOCK_REC_NOT_GAP
insert intention，表示为插入意向锁：lock->type_mode & LOCK_INSERT_INTENTION
waiting，表示锁等待：lock->type_mode & LOCK_WAIT

锁的作用范围分类

• 表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。

• 行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

• MYISAM只支持表锁，InnoDB还支持行锁；表锁和行锁有各自的读写锁实现

锁的模式分类

• 读锁与写锁

1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
3、InnoDB，行锁的一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞
4、InnoDB加锁方式：

1. 普通 select… 查询 (不加锁)
2. 普通 insert、update、delete… (隐式加写锁)
3. select…lock in share mode / select…for share (加读锁)
4. select…for update (加写锁)

• 总结：

MyISAM 在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。 （表锁共享锁、表锁排他锁）
InnoDB 在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

• 意向锁

加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行；为了解决加了行锁后、再加表锁的问题（发生冲突、需要遍历整张表判断是否加了行锁、从而实现表锁快速失败机制）；在意向锁存在的情况下，加行锁：必须先申请表的意向共享锁，成功后再申请一行的行锁。
意向共享锁（IS）：事务在给数据行加行级共享锁之前，必须先取得该表的 IS 锁。
意向排他锁（IX）：事务在给数据行加行级排他锁之前，必须先取得该表的 IX 锁。

加入意向锁后，锁的兼容性分析如下表：
横轴表示已持有的锁，纵轴表示尝试获取的锁。1表示成功（即兼容，表现为正常进行下一步操作），0表示失败（即冲突，表现为阻塞住当前操作）

兼容性	IX	IS	X	S
IX	1	1	0	0
IS	1	1	0	1
X	0	0	0	0
S	0	1	0	1

锁的类型分类

在可重复读（RR）的隔离级别下，select操作不会更新版本号，是快照读、一致性读（历史版本），基于MVCC(multi-version concurrency control)机制和UndoLog实现；
insert、update、delete、lock in share mode(共享锁) 和for update(排它锁)会更新版本号，是当前读（当前版本），基于Next-Key Locks实现（记录锁+间隙锁）实现。
总结：提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。

• 间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。

间隙锁简单案例步骤	事务1	事务2
T1	begin;update account set name ='zhuge' where id > 8 and id <18;或者 select * from account where id > 8 AND id <18 for update;	-
T2	-	insert into account values(12,'luke',10);Blocked
T3	commit;	-

间隙锁死锁案例	事务1	事务2	-
T1	begin;	begin;
T2	select * from account where id > 8 AND id <18 for update;	select * from account where id > 5 AND id <8 for update;	lock_mode X locks gap
T3	insert into account values(6,'luke',10);
T4	Blocked	insert into account values(12,'luke',10);ErrorInnoDB存储引擎检查出死锁，本事务被回滚	insert intention waiting
T4	由于事务2被回滚，insert语句继续执行
T5	commit;	事务1提交，事务2回滚

• 临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

• 记录锁(Record Locks)

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT id FROM t WHERE id=1 FOR UPDATE
记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。会阻塞其他事务对其插入、更新、删除 ;

无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)
session1 执行：update account set balance = 800 where name = 'lilei';
session2 对该表任一行操作都会阻塞住。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：
select * from tb_test where lock_key = 1 lock in share mode;
select * from tb_test where lock_key = 1 and lock_biz = 'user' for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交；当执行这条语句时候，因为查询字段多余覆盖索引字段，会出现会表操作补齐其它字段，此时lock_key、lock_biz、id均被锁住

• 插入意向锁（Insert Intention）

顾明思义，该锁只会出现在insert操作执行前（并不是所有insert操作都会出现），目的是为了提高并发插入能力，注意虽有意向二字，但插入意向锁是行锁。

1. 插入意向锁是在插入一行记录操作之前设置的一种特殊的间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。
   普通的Gap Lock 不容许 在 （上一条记录，本记录） 范围内插入数据
   插入意向锁Gap Lock 容许 在 （上一条记录，本记录） 范围内插入数据
2. 假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方不冲突的插入行。

兼容性	Gap	Insert Intention	Record	Next-Key
Gap	1	1	1	1
Insert Intention	0	1	1	0
Record	1	1	0	0
Next-Key	1	1	0	0

http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

insert N的流程(有唯一索引的情况)：

1. 找到大于N的第一条记录M，以及前一条记录P
2. 如果M上面没有gap/next-key lock的话，进入3，否则等待、
3. 检查P: 判断P是否等于N：
  如果不等，则完成插入（结束），插入会在插入的行加X-lock（记录锁）
  如果相等，再判断P是否有锁， 
        如果没有锁: 判断该记录是否被标记为删除
              如果标记为删除，说明事务已经提交，还没来得及 purge，加 S 锁等待；
              如果没有标记删除，则报 duplicate key 错误；
        如果有锁: 被X锁阻塞，锁等待，申请S-锁，如果出现1062错误（duplicate key）则加S-lock，这个S-lock 在并发场景下会死锁

事务23发生死锁：事务1插入记录，事务23插入相同记录阻塞，事务1回滚，事务2(S GAP)<—事务3(S GAP)<—事务2(插入意向锁)<–事务3(插入意向锁)

加锁原则

https://mp.weixin.qq.com/s?__biz=MzI3NzE0NjcwMg==&mid=2650167577&idx=1&sn=50145801b11de3dae37e5eabbc1205e2&chksm=f3684438c41fcd2e9a248aa464ee589689469a22009af0f69e57063a8db796be46b6aecf008e&scene=21#wechat_redirect

InnoDB的RR级别中，加锁的基本单位是 next-key lock，只要扫描到的数据都会加锁。唯一索引上的范围查询会访问到不满足条件的第一个值为止。
同时，为了提升性能和并发度，也有两个优化点：

• 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

• 误区

innodb 的排它锁（for update）锁住一行记录后，其他事务就不能读取和修改该行数据（X）
innodb 的普通查询 select不会加锁，排它锁阻塞读锁写锁，普通查询不会被阻塞

深入理解MVCC和BufferPool缓存机制

1.Undo日志版本链与Read View机制详解
2.MVCC多版本并发控制机制详解
3.Innodb引擎BufferPool缓存机制详解
4.Redo与Undo日志详解

https://note.youdao.com/ynoteshare/index.html?id=b36b975188fadf7bfbfd75c0d2d6b834

MVCC多版本并发控制机制
Mysql在可重复读隔离级别下保证了事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。
这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undo日志版本链与read view机制详解
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。
trx_id：表示这条事务最后一次被更改的事务id号
roll_pointer：回滚指针，指向undo

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。read-view：[min_id, ... , ... ]，max_id

版本链比对规则：

1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
       a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id l就是当前自己的事务是可见的)；
       b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

Innodb引擎SQL执行的BufferPool缓存机制

binlog 属于Servie 层，是所有存储引擎都有的；undolog、redolog 是innodb存储引擎特有的
undolog：当事务提交失败，可以用undo日志里的数据恢复Buffer Pool里的缓存数据。MySQL实现回滚操作完全依赖于undo log。
redolog ：当Buffer Pool缓存池里的数据还没来得及刷磁盘，万一数据丢失（数据库宕机），当下次数据库重启的时候，后台IO线程重做、恢复、刷新Buffer Pool里的数据。

eg: update t set a = 1 where id = 1
第1步：从磁盘中加载id=1的数据所在的整页文件到BufferPool、增删查改都在BufferPool里操作
第2步：写入undolog、便于回滚
第3步：更新内存数据
第4步：写入redolog
第5步：准备提交事务、redolog写入磁盘
第6步：准备提交事务、binlog写入磁盘
第7步：写入commit标记到redolog，提交事务完成，客户端认为事务已经提交
第8步：以页为单位，随机写入磁盘；
BufferPool更新内存、写日志文件（顺序IO）的性能很高；写磁盘文件（随机IO）的性能低

Innodb引擎的三大特性

自适应哈希索引、bufferPool、双写缓冲区
https://blog.csdn.net/qq_37143673/article/details/117113366

自适应哈希索引

自适应哈希索引是Innodb引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree索引之上再创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动的内部行为，用户无法控制或配置，但如果有必要刻意关闭该功能。

例如只需要很小的索引就可以为超长的键创建索引。
思路：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。需要做的就是在查询的where 子句中手动指定使用哈希函数。

• Mysql索引页结构

查询操作 索引页 槽 行记录

1. InnoDb通过B+Tree聚集索引搜索时，只能找到该记录所在的索引页(index page)，而不能到具体的行记录。
2. 找到该索引页(index page)后将该页加载入内存。
3. 通过key在索引页(index page)的directory slots中进行二分查找（binary search），找到key对应的slot。
4. 因为slot是管理多条记录，普通的slot最少管辖4条，最多管辖8条，所以会再根据KEY在对应的slot管理的记录中顺序（linear search）查找，找到最终结果

• 一个表最大是多少?

一个表空间最大数据为64TB（页号只有4KB，即可以存放232 页;页大小为16KB;232*234）

• 表空间结构页、区、段是什么?

一个页: 基本单位, 16KB
一个区 = 64个页 = 1M 区的作用: 叶子节点逻辑相连的也保证物理区域之间相连（顺序IO）
一个组 = 256个区
叶子节点和非叶子节点分开存放（叶子节点段、非叶子节点段）
一个双写缓冲区大小为两个区 = 2M

• 如何解决部分页写入问题? / 为什么要用双写缓冲区？

1. 从应用程序包括用户界面的角度来看，存取信息的最小单位是Byte（字节）；
2. 从磁盘的物理结构来看存取信息的最小单位是扇区，一个扇区（512B) ；扇区是磁盘最小的物理存储单元；
3. 从操作系统对硬盘的存取管理来看，存取信息的最小单位是簇，簇是一个逻辑概念，一个簇可以是2、4、8、16、32或64个连续的扇区。一个簇只能被一个文件占用，哪怕是只有1个字节的文件，在磁盘上存储时也要占用一个簇，这个簇里剩下的扇区是无用的。例如用NTFS文件系统格式化的时候默认是8个扇区组成一个簇，即4096字节。所以你如果保存了一个只有1字节的文件（例如字母N），它在磁盘上实际也要占用4096字节（4K），所以“簇”也可以理解为操作系统存取信息的最小单位。簇是操作系统读写文件的基本单位。
4. 文件的最小访问单位是4KB，数据库为一个1至多个文件最小访问单位
5. InnoDB的页大小一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以页为单位进行操作的。而操作系统写文件是以4KB作为单位的，那么每写一个InnoDB的页到磁盘上，操作系统需要写4个块；而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子 性，16K的数据，写入4K时，发生了系统断电或系统崩溃，只有一部分写是成功的，这种 情况下会产生partial page write（部分页写入）问题。这时页数据出现不一样的情形， 从而形成一个"断裂"的页，使数据产生混乱。在InnoDB存储引擎未使用doublewrite技术 前，曾经出现过因为部分写失效而导致数据丢失的情况。在数据持久化过程断电可能页面损坏、丢失数据，解决方式：双写缓冲区。
6. 实现 DoubleWrite Buffer 之后: 数据 -> 内存 -> 磁盘双写缓冲区 -> 磁盘中对应数据页（与不用相比性能降低约 10%，从库可关闭DoubleWrite Buffer）
7. 一个字母、一个数字、一个英文字符占一个字节；一个中文、一个中文字符占三个字节；4097个字母占8KB（4K对齐）

在正常的情况下, MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是写到真正的数据文件中。
Mysql启动前会进行页面检查：
如果页面丢失损坏, 双写缓冲区写成功了, 用双写缓冲区写好的数据直接覆盖（页面拷贝）
如果页面丢失损坏, 双写缓冲区写没成功, 用原始页面 + RedoLog重做
只有RedoLog刷盘成功，事务才算成功，因此不存在写 RedoLog失败的情况
总结：2种方式，数据库突然挂了用双写缓冲区恢复数据、写双写缓冲区挂了RedoLog恢复数据

• 什么是双写缓冲区

双写缓冲区在系统表空间里，一块预分配的磁盘位置，它的写入使用顺序写，效率比随机写高，出现问题概率小

redolog格式：该条 redo 日志的类型、表空间 ID、页号、 日志的具体内容
redolog刷盘时机：log buffer 快满了的时候、事务提交的时候、后台定时线程、服务器关闭
redolog刷盘去向 从log buffer 到磁盘 ：是追加日志、是顺序写

MySQL日志

Mysql redo、undo、bin、relay log 区别
https://blog.csdn.net/qq_17612199/article/details/80284080
https://www.zhihu.com/question/486105337/answer/2269843039

原子性由undo log日志来保证，因为undo log记载着数据修改前的信息；
持久性由redo log 日志来保证，一旦提交事务数据是永久记录的；

• binlog

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。mysql主从复制中就是依靠的binlog。

• binlog格式

binlog有三种格式：Statement、Row以及Mixed。
–基于SQL语句的复制(statement-based replication,SBR)，
–基于行的复制(row-based replication,RBR)，
–混合模式复制(mixed-based replication,MBR)。
https://blog.csdn.net/ouyang111222/article/details/50300851

Binlog有以下几个特点：
1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的

• 慢sql / 慢查询日志

记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询

1. vim /etc/my.cnf
#查看是否开启慢查询日志
show VARIABLES like 'slow_query_log';
slow_query_log=ON
#开启慢查询
set GLOBAL slow_query_log=1;

#查看日志存放地址
show VARIABLES like 'slow_query_log_file';
slow_query_log_file=/var/lib/mysql/mysql-slow.log

#查看慢查询时间(s)
show VARIABLES like '%long_query_time%';
long_query_time=0.1
#修改慢查询时间，这里为了测试所以设置比较小
set global long_query_time=0.1;

2.service mysqld restart
登录数据库查询配置信息是否生效
show VARIABLES like '%slow%';
show VARIABLES like 'long_query_time';
show VARIABLES like '%log_queries_not_using_indexes%'; 记录所有没有利用索引的查询
3.慢查询辅助工具 mysqldumpslow
语法： mysqldumpslow -s r -t 10 slow-mysql.log
-s order (c,t,l,r,at,al,ar) 
  c:总次数 t:总时间 l:锁的时间 r:获得的结果行数 
  at,al,ar :指t,l,r平均数 【例如：at = 总时间/总次数】 
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
-t NUM just show the top n queries：仅显示前n条查询 
-g PATTERN 正则匹配

4.测试
mysqldumpslow -s t -t 10 /var/lib/mysql/3c86be2ff75f-slow.log

Reading mysql slow query log from /var/lib/mysql/3c86be2ff75f-slow.log
Count: 2  Time=1.02s (2s)  Lock=0.00s (0s)  Rows=5046.0 (10092), root[root]@[113.110.222.149]
  SELECT * FROM tb_evidence e LEFT JOIN tb_evid_mail m 
  on e.id = m.evid_id

mysql查看正在执行的sql语句和查看已经执行的历史sql语句

show processlist;


-- set global general_log=on; 
-- set global general_log=off; 
-- show variables like 'log_output';
-- set global log_output='table';
-- set global log_output='FILE';

SELECT CONVERT(argument USING utf8mb4) from mysql.general_log ORDER BY event_time DESC;
 
SELECT m.*, CONVERT(argument USING utf8mb4) as res from mysql.general_log m
WHERE  event_time > '2022-04-27 07:00:17.832640' and thread_id = 729448
-- and CONVERT(argument USING utf8mb4) like '%SELECT id,org_id%'
ORDER BY event_time ASC;
 
 

MySQL主从架构及读写分离

https://note.youdao.com/ynoteshare/index.html?id=64e8c9ae0f01a59ca9b7f967e771a73b&type=note&_time=1648716064257

• 主从同步原理

即在主库上打开Binlog日志，记录对数据的每一步操作。然后在从库上打开RelayLog日志，用来记录跟主库一样的Binlog日志，并将RelayLog中的操作日志在自己数据库中进行重演。这样就能够更加实时的保证主库与从库的数据一致。
它的实现过程是在从库上启动一系列IO线程，负责与主库建立TCP连接，请求主库在写入Binlog日志时，也往从库传输一份。这时，主库上会有一个IO Dump线程，负责将Binlog日志通过这些TCP连接传输给从库的IO线程。而从库为了保证日志接收的稳定性，并不会立即重演Binlog数据操作，而是先将接收到的Binlog日志写入到自己的RelayLog日志当中。然后再异步的重演RelayLog中的数据操作。

• 半同步复制

MySQL主从集群默认采用的是一种异步复制的机制。主服务在执行用户提交的事务后，写入binlog日志，然后就给客户端返回一个成功的响应了。而binlog会由一个dump线程异步发送给Slave从服务。由于这个发送binlog的过程是异步的。主服务在向客户端反馈执行结果时，是不知道binlog是否同步成功了的。这时候如果主服务宕机了，而从服务还没有备份到新执行的binlog，那就有可能会丢数据。

靠MySQL的半同步复制机制来保证数据安全：半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

索引优化总结

覆盖索引，索引上的信息足够满足查询请求，不需要回表。（减少 select * 语句）

联合索引，要符合最左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列，遇到范围查询(>、<、between、like)会停止匹配。
eg：范围查询后的条件不能用索引，范围查询字段可能用到索引可能没用到；like KK%相当于常量，%KK和%KK% 相当于范围

索引下推，是联合索引上的优化。在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数（如果索引中包含了该字段信息会直接进行过滤不会再回表比对）
eg：联合索引不仅用到模糊查询字段，也使用到后面所有索引字段过滤，从而减少回表次数

索引失效

不在索引列上做任何操作（计算、函数）
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引，但用order by会失效
is not null 和 不等于 不会使用索引
or查询条件每一列都要建索引

Order by与Group by优化

MySQL支持两种方式的排序，filesort和index
Using index是MySQL扫描索引本身完成排序，index效率高，filesort效率低。
order by语句使用索引最左前列或where与order by组合满足使用索引最左前列，使用Using index排序。
如果order by的条件不在索引列上，就会产生Using filesort。

filesort文件排序方式
● 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
● 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

分页查询优化

● 先用索引把id查出来过滤

1. EXPLAIN select * from employees ORDER BY name limit 90000,5;
2. EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

1按道理可以走索引，结果没走索引，走全表效率更高（查找结果集太多，先用辅助索引再回表查聚簇索引，效率低），还用到了文件排序（）
2用覆盖索引优化，查找结果集只在覆盖索引里，分页查询查找的id再去关联主表（只关联5条），用到了索引排序

join语句怎么优化

mysql的表关联常见有两种算法 Nested-Loop Join 算法、 Block Nested-Loop Join 算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

eg：t1 10000行记录、t2 100行记录、两张表a字段有索引。最后扫描行 = 200 行。

• 从t2表一行一行地拿记录用关联字段去t1表过滤，t2表全表扫描、t1表走索引了

索引扫描可以认为没有扫描行，只是拿出记录来扫描了1次：走索引树，拿最后那个叶子结点的时候，从磁盘拿出来一行记录，非叶子结点一般保存在内存里不需要磁盘操作

2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer（连接缓冲区） 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比（嵌套循环）。

eg：t1 10000行记录、t2 100行记录、两张表b字段没有索引。最后扫描行 = 10100 行，内存比对100 * 10000 次（也是如果走第一种算法的扫描磁盘次数）

• t2表所有数据放到buffer里，从t1表一行一行取记录与内存里比对（内存里t2表数据是无序的）

对于关联sql的优化

• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引；被驱动表大表的关联字段加索引
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

left join的优化
选择小表作为驱动表；如果左表比较大，并且业务要求驱动表必须是左表，那么我们可以通过where条件语句，使得左表被过滤的小一些；关联字段给索引

Multi-Range Read 优化

• MRR目的是为了减少磁盘的随机访问，将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销

每次从二级索引中读取一条记录，就会根据该记录的主键值进行回表操作；而在某个扫描区间的二级索引记录的
主键值是无序的，也就是说二级索引记录对应的聚簇索引的页面的页号也是无序的；这样的随机IO开销比较大；
MRR：先读取一部分二级索引记录，将它们的主键值排好序后再同一进行回表操作

in和exsits优化

SQL注意点

left join注意点
https://segmentfault.com/a/1190000020458807
对左表进行过滤必须用where；对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失

高性能表结构及索引设计最佳实践

数据库表设计

• 满足三大范式
• 实际工作中的反范式实现

性能提升-缓存和汇总

有需要时创建一张完全独立的汇总表或缓存表也是提升性能的好办法。“缓存表”来表示存储那些可以比较简单地从其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据)。而“汇总表”时,则保存的是使用GROUP BY语句聚合数据的表。
在使用缓存表和汇总表时，有个关键点是如何维护缓存表和汇总表中的数据，常用的有两种方式，实时维护数据和定期重建，这个取决于应用程序，不过一般来说，缓存表用实时维护数据更多点，往往在一个事务中同时更新数据本表和缓存表，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

性能提升-计数器表：

计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快，并且可以使用一些更高级的技巧：可以将计数器保存在多行中，每次随机选择一行进行更新。在具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加100行或者更多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可 ，这种解决思路其实就是写热点的分散

反范式设计-分库分表中的查询

高性能的索引创建策略

索引列的类型尽量小
利用索引选择性和前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引。
只为用于搜索、排序或分组的列创建索引
合理设计多列索引
尽可能设计三星索引
主键尽量是很少改变的列
处理冗余和重复索引
删除未使用的索引

从架构师角度全局理解Mysql性能优化

• 通信协议: 半双工机制

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

优化SQL查询方法论

业务层-请求了不需要的数据

1. 查询不需要的记录
2. 总是取出全部列（select *）
3. 重复查询相同的数据（改为缓存）

执行层-是否在扫描额外的记录
在确定查询只返回需要的数据以后， 接下来应该看看查询为了返回结果是否扫描了过多 的数据。 对于MySQL， 最简单的衡量查询开销的三个指标如下: 响应时间、 扫描的行数、 返回的行数 这三个指标都会记录到MySQL的慢日志中， 所以检查慢日志记录是找出扫描行数过多的 查询的好办法。

1. 响应时间

响应时间是两个部分之和:服务时间和排队时间。
服务时间是指数据库处理这个查询真正花了多长时间。
排队时间是指服务器因为等待某些资源而没有真正执行查询的时间—-可能是等I/O操作完成，也可能是等待行锁，等等。

2. 扫描的行数和返回的行数

例如不正确的使用Limit，在系统中需要进行分页操作的时候，我们通常会使用 LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。 在偏移量非常大的时候，SQL语句就变成了类似select * from order_exp limit 10000,10; 这样的查询，这时MySQL需要查询10010条记录然后只返回最后10条，前面10 000条 记录都将被抛弃，这样的代价非常高。

3. 扫描的行数和访问类型

对于我们在SQL语句中常见的WHERE条件， 一般 MySQL能够使用如下三种方式应用WHERE 条件， 从效率和扫描行数多少来评价的话， 从好到坏依次为: 1 、 在索引中使用WHERE条件来过滤不匹配的记录。 这是在存储引擎层完成的。 2、 使用索引覆盖扫描（在Extra列中出现了Using index） 来返回记录， 直接从索引中 过滤不需要的记录并返回命中的结果。这是在 MySQL服务器层完成的， 但无须再回表查 询记录。 3、 从数据表中返回数据， 然后过滤不满足条件的记录（在Extra列中出现Using Where) 。 这在 MySQL服务器层完成， MySQL需要先从数据表读出记录然后过滤。

Mysql执行原理之索引合并详解

Mysql内核查询优化规则详解

条件化简

移除不必要的括号、常量传递、移除没用的条件、表达式计算

外连接消除

在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

MySQL对IN子查询的优化

首先判断满不满足转为半连接，符合则转为半连接；无法转为半连接的进行物化、或转为exists子查询

1. 物化表

物化：把记录从表中找出来之后，内存中或磁盘建立一张临时表，为临时表建立索引去重，与外层查询建立连接查询
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。
MySQL把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

2. 物化表转连接

把子查询进行物化之后，mysql可以自己选择子查询物化表和外层查询哪张表是驱动表，再计算两张表内连接成本

3. 将子查询转换为semi-join

将子查询进行物化之后再执行查询都会有建立临时表的成本，我们能不进行物化操作直接把子查询转换为连接。

4. 不能转为semi-join查询的子查询优化

并不是所有包含IN子查询的查询语句都可以转换为semi-join，对于不能转换的，于不相关子查询来说，会尝试把它们物化之后再参与查询，不管子查询是相关的还是不相关的，都可以把IN子查询尝试转为EXISTS子查询