实践（2）--MySQL机能优化

分页查询优化

在这我们先往 employess 插入一些测试数据

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('hjh',i),i,'dev');        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp();复制代码

许多时候我们业务系统实现分页功效大概会用如下SQL实现

select * from employees limit 10000,10;复制代码

表示从表 employees 中取出从 10001 行开端的 10 行记载。看似只查询了 10 笔记录，实际这条 SQL 是先读取 10010 笔记录，然后丢弃前 10000 笔记录，然后读到后面 10 条想要的数据。因此要查询一张大表比力靠后的数据，施行效力是非常低的。

常见的分页场景优化技巧

1. 按照自增且持续的主键排序的分页查询
2. 按照非主键字段排序的分页查询

案例1: 按照自增且持续的主键排序的分页查询

第一来看一个按照自增且持续主键排序的分页查询的例子：

select * from employees limit 9000,5;复制代码

该 SQL 表示查询从第 9001开端的五行数据，没增加独自 order by，表示通过主键排序。我们再看表 employees ，由于主键是自增并且持续的，所以可以改写成依照主键去查询从第 9001开端的五行数据，如下:

select * from employees where id > 9000 limit 5;复制代码

查询结果是一致的，我们再对照一下施行方案：

EXPLAIN select * from employees limit 9000,5;复制代码

EXPLAIN select * from employees where id > 9000 limit 5;复制代码

明显改写后的 SQL 走了索引，并且扫描的行数大大减少，施行效力更高。 但是，这条改写的 SQL 在许多场景并不有用，由于表中大概某些记载被删后，主键空缺，致使结果不一致，如下图试验所示(先删除一条前面的记载，然后再测试原 SQL 和优化后的 SQL):

两条 SQL 的结果并不一样，因此，假如主键不持续，不克不及使用上面描写的优化办法。

别的假如原SQL是order by 非主键的字段，依照上面说饿的办法改写会致使两条SQL的结果不一致。所以这种改写得知足以下两个前提：

• 主键自增且持续
• 结果是依照主键排序的

案例2: 按照非主键字段排序的分页查询

再看一个按照非主键字段排序的分页查询，SQL 如下:

select * from employees ORDER BY name limit 9000,5;复制代码

 EXPLAIN select * from employees ORDER BY name limit 90000,5;复制代码

发明并没有使用 name 字段的索引(key 字段对应的值为 null)，详细缘由上前面讲过 : 扫描整个索引并查寻到没索引的行(大概要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器舍弃使用索引。 知道不走索引的缘由，那么如何优化呢? 其实关键是让排序时返回的字段尽大概少，所以可以让排序和分页操纵先查出主键，然后按照主键查到对应的记载，SQL 改写如下：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码

需要的结果与原 SQL 一致，施行时间减少了一半以上，我们再对照优化前后sql的施行方案:

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

Join关联查询优化

#示例表CREATE TABLE `t1` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `a` INT (11) DEFAULT NULL,    `b` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`),    KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;CREATE TABLE t2 LIKE t1;复制代码

往t1表插入1万行记载，往t2表插入100行记载

#t1 1万笔记录drop procedure if exists insert_emp_t1; 
delimiter ;; 
create procedure insert_emp_t1()begin
    declare i int; 
    set i=1; 
    while(i<=10000) do
        insert into t1(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t1();

#t2 100笔记录drop procedure if exists insert_emp_t2; 
delimiter ;; 
create procedure insert_emp_t2()begin
    declare i int; 
    set i=1; 
    while(i<=100) do
        insert into t2(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t2();复制代码

MySQL 的表关联常见有两种算法

1. Nested-Loop Join 算法
2. Block Nested-Loop Join 算法

案例1：嵌套轮回连接 Nested-Loop Join（NLJ）算法

一次一行轮回地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，按照关联字段在另一张表（被驱动表）里取出知足前提的行，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;复制代码

从施行方案中可以看到这些信息:

• 驱动表是 t2，被驱动表是 t1。先施行的就是驱动表(施行方案结果的id假如一样则按从上到下次序施行sql);优化器一样会优先选中小表做驱动表。所以使用 inner join 时，排在前面的表并不必然就是驱动表。
• 使用了 NLJ 算法。一样 join 语句中，假如施行方案 Extra 中未显现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面SQL的大致流程如下：

1. 从表 t2 中读取一行数据；
2. 从第1步的数据中，取出关键字字段 a，到表 t1 中查寻；
3. 取出表 t1 中知足前提的行，跟 t2 中猎取到的结果合并，作为结果返回给客户端；
4. 反复上面 3 步。

整个历程会读取 t2 表的所有数据（扫描100行），然后遍历这每行数据中字段 a 的值，按照 t2 表中的 a 的值索引扫描 t1 表中对应的行（扫描 100次 t1 表的索引，1次扫描可以认为终究只扫描 t1 表一行完全数据，也就是总共 t1 表也扫描了100行）。因此整个历程扫描了 200 行。

假如被驱动表的关联字段没有索引，使用NLJ算法机能会比力低（下面有具体说明），MySQL 会选中 Block Nested-Loop Join 算法。

案例2：基于块的嵌套轮回连接 Block Nested-Loop Join（BNL）算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对照。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;复制代码

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下:

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对照
3. 返回知足 join 前提的数据

整个历程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次推断，所之内存中的推断次数是 100 * 10000= 100 万次。

被驱动表的关联字段没索引为什么要选中使用 BNL 算法而不使用 Nested-Loop Join 呢?

假如上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

很明显，用BNL磁盘扫描次数少许多，比拟于磁盘扫描，BNJ 的内存运算会快得多。

因此MySQL关于被驱动表的关联字段没索引的关联查询，一样都会使用 BNL 算法。假如有索引一样选中 NLJ 算法，有索引的状况下 NLJ 算法比 BNL算法机能更高。

关于关联SQL的优化

• 关联字段加索引，让mysql做join操纵时尽量选中NLJ算法
• 小表驱动大表，写多表连接sql时假如明白知道哪张表是小表可以用straight_join写法牢固连接驱动方式，省略mysql优化器本人推断的时间

straight_join说明

straight_join功效同join相似，但能让左边的表来驱动右侧的表，能改动优化器关于联表查询的施行次序。

比方 : select * from t2 straight_join t1 on t2.a = t1.a; 代表拟定mysql选中 t2 表作为驱动表。

• straight_join只适用于inner join，并不适用于left join，right join。(由于left join，right join已经代表指 定了表的施行次序)
• 尽大概让优化器去推断，由于大部分状况下mysql优化器是比人要聪慧的。使用straight_join必然要稳重，因 为部分状况下人为指定的施行次序并不必然会比优化引擎要靠谱。

in 和 exsits 优化

原则：小表驱动大表，即小的数据集驱动大的数据集。

in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in(select id from B) 
#等价于:for(select id from B){    select * from A where A.id = B.id
}复制代码

exists：当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做前提验证，按照验证结果(true或false)来决议主查询的数据可否保存

select * from A where exists (select 1 from B whereB.id=A.id) 
#等价于:for(select * from A){    select * from B where B.id = A.id
}

#A表与B表的ID字段应创立索引复制代码

1. EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1更换,官方说法是实际施行时会 忽略SELECT清单,因此没有不同；
2. EXISTS子查询的实际施行历程大概经过了优化而不是我们懂得上的逐条对照；
3. EXISTS子查询往往也可以用JOIN来代替，何种最优需要详细问题详细剖析；

Count(*) 查询优化

暂时关闭mysql查询缓存，为了查看sql屡次施行的真实时间。

set global query_cache_size=0;set global query_cache_type=0;复制代码

EXPLAIN select count(1) from employees; 
EXPLAIN select count(id) from employees;EXPLAIN select count(name) from employees; 
EXPLAIN select count(*) from employees;复制代码

四个sql的施行方案一样，说明这四个sql施行效力应当差不多，不同在于按照某个字段count不会统计字段为null值的数据行。

为什么mysql终究选中辅助索引而不是主键汇集索引?

由于二级索引相对主键索引储备数据更少，检干脆能应当更高

常见的优化办法如下：

• 查询MySQL本人保护的总行数
• show table status
• 将总数保护到Redis里
• 增添计数表

查询MySQL本人保护的总行数

关于myisam储备引擎的表做不带where前提的count查询机能是很高的，由于myisam储备引擎的表的总行数会被 mysql储备在磁盘上，查询不需要运算。

关于innodb储备引擎的表mysql不会储备表的总记载行数，查询count需要实时运算。

show table status

假如只需要知道表总行数的估量值可以用如下sql查询，机能很高

将总数保护到Redis里

插入或删除表数据行的时候同时保护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式大概不准，很难包管表操纵和redis操纵的事务一致性。

增添计数表

插入或删除表数据行的时候同时保护计数表，让他们在统一个事务里操纵。