1 Mysql 分页查询sql 执行原理？

2，千万级数据mysql 分页查询如何优化

3，Mysql表连接底层实现原理
·

4，nested_Loop Join(NLJ)与Block Nested-Loop(BNL)连表算法的区别

5，in/exist/count(*)count(1) count(列名)查询优化

6，EXPLAIN 分析sql语句 type 最低满足什么级别

7，为什么阿里巴巴官方手册不推荐使用存储过程。

8，阿里官方手册mysql 索引优化总结》

分页查询优化：

select * from employees limit 10000,10;
select * from employees limit 100000,10;
select * from employees limit 1000000,10;
select * from employees limit 3000000,10;

select * from employees limit 3000000,10;

image.png

从表中只查询10条数据，实际上底层读取了300000条数据，然后读取最后10条
这种分页查询采用全表扫描的方式，查询效率极低。
1，根据主键且连续的主键排序的分页查询：

EXPLAIN select * from employees where id>3000003 limit 10;

原理： 根据主键索引id排除< 3000003 ，取后10条数据避免全表扫描
缺点： 如果主键id不连续 ，可能无法实现效果。
2，非主键索引方式分页优化：

select * from employees order by id limit 100000,10;

SELECT * from employees  e  inner join (

select id from employees a order by a.id limit 100000,10 ) ed on e.id=ed.id

使用复合索引嵌套子查询，效率可以提高一半。

阿里巴巴手册：

image.png

数据量超过500万或者大于2g的时候建议分表

count(*) 优化：

临时关闭mysql 的查询缓存，为了查看sql多次执行的真实时间。

set global query_cache_size=0;
set global query_cache_type=0;
1,count(field) 不包含字段值为null 的值；
2，count() 包含字段为null 的值；
3，select() 与select(1) 在InnnDB 中性能没有任何区别，处理方式相同。

5.6版本：
1, select count(1) from employees; 使用辅助联合索引计数
2， select count(id) from employees; 使用辅助索引计数
3， select count(name) from employees; 使用辅助索引计数
4， select count（） from employees; 使用辅助索引计数
5， select count(id) from employees force index (PRIMARY) 使用主键索引；
从效率上看： count(1) ==count()>count(name)>count(id)
因为主键索引的id 对应的叶子节点中存放data 数据，加载内存中计数的时候比较慢。 推荐使用 count(*)；

在mysql 5.7 count(*) 会选择聚集索引，进行一次内部handler函数调用，即可快速获得该表总数，执行计划Extra 显示 select tables optimized away SELECT 操作已经优化到不能再优化
如果聚集索引比较大(或者说表数据量比较大)。没有完全加载到buffer pool 中的话,MYSQL5.7的查询方式有可能反而更慢， 还不如原先放方式 MYSQL5.6

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
EXPLAIN select count(id) from employees force index(PRIMARY) 强制使用聚集索引

聚集索引和非聚集索引的区别：
聚集索引： 就是主键id 非聚集索引就是自定义的userName 字段
其中聚集索引 clustered index(id) , 非聚集索引index (UserName)

InnoDB 表中在没有默认主键的情况下会生成一个6 byte 空间的自增长主键，可以用
select _rowid from table 查询的是对应的主键值 select _rowid from employees

常见的优化方案：
1，对于 myisam 存储引擎的表做不带where 条件的count 查询性能是很高的，因为mysiam 存储的表总行会被mysql 存储在磁盘上，查询不需要操作
2 show table status
3, 使用Redis set key 记录表总数。

Join 关联表查询

EXPLAIN select * from mayikt_1 left join mayikt_2 on mayikt_1.t1= mayikt_2.t1;
EXPLAIN select * from mayikt_2 left join mayikt_1 on mayikt_1.t1= mayikt_2.t1;
EXPLAIN select * from mayikt_2 right join mayikt_1 on mayikt_1.t1= mayikt_2.t1;

Nested-Loop Join(NLJ) 嵌套循环连接 算法
Block Nested-Loop Join (BNL) 基于块的嵌套循环连接 算法

Nested-Loop Join 嵌套循环连接 算法（主键关联查询）
EXPLAIN select * from mayikt_1 inner join mayikt_2 on mayikt_1.t1= mayikt_2.t1;

image.png

全表扫描查询到mayikt_2 表中索引的数据，在根据 mayikt2 中的t1 索引字段数据查询mayikt1 索引字段中的数据，总共扫描2000行
驱动表 mayikt2(小表)被驱动表： mayikt_1(大表)
优化器一般会选择小表做驱动表，所以使用 inner join 时 ，排在后面的表并不一定

Block Nested-Loop Join 基于块的嵌套循环连接 算法（非索引关联查询）
关联查询使用的是 BNL 算法：
EXPLAIN select * from mayikt_2 straight_join mayikt_1 on mayikt_1.t2= mayikt_2.t2;

image.png

原理：
1.将mayikt2表中所有的数据放入到join_buffer 中
2.在读取mayikt1表中每行数据与join_buffer中数据实现匹配关联
3.最后在返回查询的数据
1000*32869次 查询效率极低。

有索引是情况下 NLJ 算法比BNL 算法性能要高；
对于关联sql的优化：
1， 超过三个表禁止join, 需要join 的字段 ，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引，尽量使用NLJ 算法；
2， 小表驱动大表 ，写多表连接sql 时如果明确知道那张表是小表可以用 straight_join 写法固定方式 ，省去mysql 优化器自己判断时间。

EXPLAIN select * from mayikt_2 straight_join mayikt_1 on mayikt_1.t2= mayikt_2.t2;

in 和exist 优化：
也是需要遵循原则；
原则： 小表驱动大表，即小的数据集驱动大的数据集

如果 mayikt_b 中的数据小于mayikt_a 的数据可以使用in

select * from mayikt_a where id in (select id from mayikt_b)

底层类似于这样实现
for(select id from mayikt_B){
     select * from mayikt_a where mayikt_a.id = mayikt_b.id
 }
如果mayikt_a中的数据小于mayikt_b数据可以使用exists 
select * from mayikt_a where exists (select id from mayikt_b where mayikt_b.id = mayikt_a.id)
 for(select * from mayikt_a){
      select * from mayikt_a where mayikt_b.id = mayikt_a.id
 }

1 mysql 索引优化原则：
2 核心点： B+树，优化原则： 常见方式，分页，排序，连表， count(*)
3 MYSQL 索引底层采用B+树； 减少磁盘IO操作，支持高效的范围查询：

4 优化原则：
核心： 先定位慢查询，在通过慢查询日志文件 分析sql 语句；
分析sql 语句工具： ExPLAIN/trace 工具
EXPLAIN type 满足级别： type最低 满足 range 范围查询级别
防止索引失效，避免全表扫描；

5， 优化方案：
常见方案：
常见方式，分页，排序，连表，count(*)
1，必须遵循最佳左前缀原则 防止索引失效
2，尽量使用覆盖索引 查询列都是加上索引 ，减少select *
3，is null ，is not null 索引会失效，空值用专门特定的常量值定义； dfe
4， like 模糊查询遵循最佳左前缀原则 ，使用复合索引模糊查询
5，排序相关： 最佳左前缀原则，避免 filesort

6, 分页查询 ， 根据 where id 条件过滤 offset 或者使用子查询先定位id， 在查询效率可以提高一半，但是如果数据量大于500万的情况下建议使用分表。
7，连表查询 ： 小表驱动大表数据避免全表扫描，超过三张表禁止使用join

8，count(*) 查询优化 ，辅助索引count 比主键索引count
效率高； mysql5.7 主键id count mysql5.6 辅助索引 count