MySQL优化

9 MySQL优化

9.1 索引优化

索引的分类

回表
假设我们执行一条查询语句
select * from person where ID=6,因为直接使用的是主键ID查询，所以就会主键索引，由于主键索引直接关联了整行所有的数据，所以，引擎只要执行一次就能查询出结果。

如果执行的sql语句是非主键索引

select * from person where age=18

上述语句会走age的普通索引，索引先根据age搜索等于索引记录，找到ID=10的记录，然后再找到主键索引搜索一次，然后拿出需要查询的数据。

从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

覆盖索引
我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做‘覆盖索引’。

建表SQL

create table staffs(
    id int primary key auto_increment,
    name varchar(20) not null default,
    age int bot null default 0,
    pos varchar(20) not null default
    add_timestamp not null default current_timestamp
 )engine=innodb default charset=utf8;

create table staffs(
    id int primary key auto_increment,
    name varchar(20) not null default '',
    age int not null default 0,
    email varchar(20) default null
 )engine=innodb default charset=utf8;

插入数据

insert into staffs (`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());

insert into staffs(`name`,`age`,`pos`,`add_time`) values('july',23 ,'dev',now());

insert into staffs(`name`,`age`,`pos`,`add_time`) values(2000,23 ,'dev',now());

insert into user(name,age,email) values ('1aa1',21,'b@163.com');

insert into user(name,age,email) values ('2aa2',22,'a@163.com');

insert into user(name,age,email) values ('3aa3',23,'c@163.com');

insert into user(name,age,email) values ('4aa4',24,'b@163.com');

建立复合索引

create index idx_staffs_nameAgePos on staffs(name,age,pos);

口诀

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右，覆盖索引不写里
不等空值还有or，索引失效要少用
varchar引号不可丢，SQL高级也不难

9.2 索引优化案列

单表优化

建表

create table article(
    id int unsigned not null primary key auto_increment,
    author_id int unsigned not null,
    category_id int unsigned not null,
    views int unsigned not null,
    comments int unsiggned not null,
    title varchar(255) not null,
    content text not null
 );

插入数据

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

需求：查询category_id 为1 且 comments 大于1 的情况下，views 最多的 article_id。

双表优化

建表

商品类别表

create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
);

图书表

create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
);

驱动表的概念，mysql 中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。

9.3 join 语句优化

我们在使用数据库查询数据时，有时一张表并不能满足我们的需求，很多时候都涉及到多张表的连接查询。今天，我们就一起研究关联查询的一些优化技巧。在说关联查询优化之前，我们先看下跟关联查询有关的几个算法：

为了方便理解，首先创建测试表并写入测试数据，语句如下：

CREATE TABLE `test_join` ( /* 创建表t1 */
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

关联查询的算法

• Nested-Loop Join 算法
• Block Nested-Loop Join 算法

Nested-Loop Join 算法

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

我们试想一下，如果在被驱动表中这个关联字段没有索引，那么每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会对被驱动表做一次全表扫描，成本是非常高的（比如驱动表数据量是m，被驱动表数据量是 n,则扫描行数为 m*n)。

好在MySQL 在关联字段有索引时，才会使用NLJ，如果没索引，就会使用 Block Nested-Loop Join。我们先来看下在有索引情况的情况下，使用 Nested-Loop Join 的场景 （称为：index Nested-Loop Join）。

因为MySQL 在关联字段有索引时，才会使用NLJ，因此本节后面的内容所用到的 NLJ 都表示 index Nested-Loop Join。

select * from t1 inner join t2 on t1.a=t2.a;

怎么确定这条SQL使用的是NLJ算法？

从执行计划中可以看到这些信息：

• 驱动表是t2,被驱动表是t1。原因是：explain分析join语句时，在第一行的就是驱动表；选择t2座驱动表的原因：如果没固定连接方式优化器会优先选择小表左驱动表。所以使用 inner join 时，前面的表并不一定就是驱动表。
• 使用NLJ。原因是：一般join 语句中国，如果执行计划Extra 中未出现 Using join buffer （***）；则表示使用的join 算法是 NLJ。

sql 1 的大致流程如下：

• 从表t2 中读取一行数据；
• 从表 1 步的数据中，取出关联字段a，到表t1中查找；
• 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客服端；
• 重复上面 3 步。

在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行。

在前面，我们有说到：如果被驱动表的关联字段没索引，就会使用 Block Nested-Loop Join (简称：BNL），为什么会选择使用 BNL 算法而不继续使用 Nested-Loop Join 呢？

Block Nested-Loop Join 算法

Block Nested-Loop Join (BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客服端。

我们一起看看下面这条 SQL 语句：

select * from t1 inner join t2 on t1.b=t2.b

在 Extra 发现 Using join buffer (Block Nested Loop），这个就说明该关联查询使用的是 BNL 算法。

我们在看下 sql 2 的执行流程：

• 把 t2 的所有数据放入到 join_buffer 中
• 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
• 返回满足 join 条件的数据

在这个过程中，对表 t1 和 t2都做了一次全表 扫描，因此扫描 的总行数为 10000（表 t1
的数据总量）+ 100（表 t2 的数据总量）=1010。并且 join_buffer 里的数据时无序的，因此对表 t1 中的每一行，都要做100次判断，所有内存中的判断次数是 100*10000=100万次。

下面我们来回答上面提出的一个问题：

如果驱动表的关联字段没索引，为什么会选择使用 BNL 算法而不继续使用 Nested-Loop Join呢？
在被驱动表的关联字段，没索引的情况下，比如 sql 2：

如果使用 Nested-Loop Join，那么扫描次数行数为 100*10000=100万次，这个是磁盘扫描。

如果使用 BNL，那么磁盘扫描是 100+10000=10100次，在内存中判断 100*10000=100万次。

显然后者磁盘扫描的次数少很多，因此是更优的选择。因此对于MySQL 的关联查询，如果被驱动表的关联字段没有索引，会使用 BNL 算法。

优化关联查询

关联字段添加索引

通过上面的内容，我们知道了 BNL、NLJ的原理，因此让BNL变成NLJ，可以提高join的效率。我们来看下面的例子

我们构造出两个算法对应的例子：

Block Nested-Loop Join 的例子：

select * from t1 join t2 on t1.b=t2.b;

index Nested-Loop Join 的例子：

select * from t1 join t2 on t1.a=t2.a;

对比一下两条 SQL的执行计划：

小表做驱动
前面说到，index Nested- Loop Join 算法会读取驱动表所有的数据，首先扫描的行数是驱动表的总行数（假设为n),然后遍历这n行数据中关联字段的值，根据驱动表中关联字段的值索引扫描被驱动表中的对应行，这里又会扫描 n 行，因此整个过程扫描了 2n 行。当使用 index Nested-Loop Join 算法时，扫描行数跟驱动表的数据量成正比。所以在写 SQL 时，如果确定被关联字段有索引的情况下，建议用小表做驱动表。

我们来看下以 t2 为驱动表的 SQL:

select * from t2 straight_join t1 on t2.a=t1.a;

这里使用 steaight_join 可以固定连接方式，让前面的表为驱动表。

在看下以 t1 为驱动表的 SQL：

select * from t1 straight_join t2 an t1.a=t2.a;

我们对比下两条 SQL的执行计划：

明显前者扫描的行数少（注意关注explain结果的rows列），所以建议小表驱动大表。

临时表

多数情况我们可以通过在被驱动表的关联字段加上索引来让 join 使用NLJ或者BKA，但有时因为某条关联查询只是临时查一次，如果再去添加索引可能会浪费资源，那么有什么办法优化呢？

这路提供一种创建临时表的方法。

我们一起测试下：

比如下面这条关联查询：

select * from t1 join t2 on t1.b=t2.b;

我们看下执行计划：

由于表 t1 和表 t2 的字段 b都没有索引，因此使用的是效率比较低的 BNL算法。

我们用临时表的方法对这条SQL进行优化：

首先创建临时表 t1_tmp，表结构与表 t1 一致，只是在关联字段 b 上添加了索引。

CREATE TEMPORARY TABLE `t1_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (b)
) ENGINE=InnoDB ;

把 t1 表中的数据写入临时表 t1_tmp中：

insert into t1_tmp select * from t1;

执行语句join语句：

select * from t1_tmp join t2 on t1_tmp.b=t2.b;

我们再看下执行计划：

Extra 没出现 ‘Block Nrested Loop‘，说明使用的是index Nested-Loop Join，并且扫描行数大大降低了。

所以当遇到 BNL 的 join 语句，如果不方便在关联字段上添加索引，不妨尝试创建临时表，然后在临时表中的关联字段上添加索引，然后通过临时表来做关联查询。

9.4 排序优化

永远小表驱动大表

for i in range(5):
    for j in range(1000):
        pass

for i in range(1000):
    for j in range(5):
        pass

order by 优化

order by 子句，尽量使用index方式排序，避免使用 filesort方式排序

建表，插入测试数据

create table tbla(
    age int,
    birth timestamp not null
);

插入数据

insert into tbla(age,birth) values(22,now());
insert into tbla(age,birth) values(23,now());
insert into tbla(age,birth) values(24,now());

建立索引

create index idx_tbla_agebirth on tbla(age,birth);

分析
MySQL 支持两种方式的排序，filesort 和index 效率高 MySQL扫描索引本身完成排序。filesort方式效率较低。

order by 满足两种情况下，会使用index方式排序

• 1.order by 语句使用索引最左前列
• 2.使用where 子句与 order by 子句条件组合满足索引最左前列。

Filesort 是在内存中还是在磁盘中完成排序的？

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和sort_buffer_size配置的大小。

• 如果’排序的数据大小‘ < sort_bufffer_size:内存排序。
• 如果’排序的数据大小‘ > sort_buffer_size:磁盘排序。

filesort 有两种算法-双路排序和单路排序

双路排序，MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从表中读取对应的数据输出。

单路排序，从磁盘读取查询需要的所有列，按照order by 列在 buffer 对他们进行排序，然后扫描排序进后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间。

优化策略调整 MySQL参数

增加 sort_buffer_size 参数设置
增大 max_lenght_for _sort_data 参数的设置

提高 order by 的速度

• order by 时 select * 时一个大忌，只需要的字段
  • 当查询的字段大小总和小于
    max_lenght_for_sort_data 而且排序字段不是 text|blob 类型时，会用改进后的算法–单路排序。
  • 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建tmp 文件进行合并排序，导致多次 I/O
  • 尝试提高 sort_buffer_size
  • 尝试提高 max_length_for_sort_data。

9.5分页查询优化

很多时候，业务上会有分页操作的需求，对应的SQL 类似下面这条：

select a,b,c from t1 limit 10000,10;

表示从表 t1 中取出从 10001 行开始的10行记录。看似只查询了 10条记录，实际上这条 SQL 是先读取 10010条记录，然后抛弃前 10000条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较看后的数据，执行效率是非常低的。

根据两种情况的分页查询

• 根据自增连续主键排序的分页查询
• 查询根据非主键字段排序的分页查询

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

select * from t1 limit 99000,2;

该SQL表示查询从第99001开始的两行数据，没添加单独 order by，表示通过主键排序。我们再看表 t1，因为主键是自增并且连续的，所以可以改写成按照主键查询从第99001开始两行数据，如下：

select * from t1 where id>99000 limit 2;

查询的结果是一致的。我们再对比一下执行计划：

原SQL中 key 字段为null,表示未走索引，rows显示 99965，表示扫描的行数 99965行；
改写后的SQL key 字段为 PRIMARY,表示走了主键索引，扫描了1000行。

显然改写后的SQL执行效率更高。

但是，这条SQL在很多场景并不适用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图的实验（整个实验过程为：先删除一条前面的记录，然后在测试原SQL和优化的SQL)：

可以发现两条 SQL的结果并不一致，因此，如果主键不连续，不能使用上面描述的优化方法。
另外如果原SQL是order by 非主键的字段，按照上面说的方法改写会导致两条SQL的结果不一致。所以这种改写得满足以下两个条件。

• 主键自增且连续
• 结果是按照主键排序的

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

再看一个根据非主键字段排序的分页查询，SQL如下：

select * from t1 order by a limit 99000,2;

查询时间是 0.08秒。

我们来看下这条 SQL的执行计划：

其实关键是让排序时返回的字段尽可能少，所以可以排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下：

select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id=g.id;

需要的结果与原 SQL一致，执行时间0.02秒，是原SQL执行时间的四分之一，我们再对比优化前后的执行计划：

对于其它一些复杂的分页查询，也基本可也按照这两个思路去优化，尤其是第二种优化方式。第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键id。

9.6 MySQL整体优化思路

硬件相关优化

在MySQL整体的优化环节中，硬件相关的优化必不可少，因此首先来聊聊这一方面的优化策略：

CPU相关
A.关闭CPU节能，设定为最大性能模式。

原因是：考虑到在高并发之前没有任何连接的情况，机器可能会处于节电模式，高并发场景来临时可能导致处理不过来新的请求。

B.配置合理的 CPU 核数和选择合适的 CPU主频。

原因是：
CPU 核数越多，支持的并发也越高；
CPU 主频越高，处理任务的速度越快。

内存相关
内存对 MySQL数据库影响是非常大的。InnoDB 使用 InnoDB buffer pool 缓存数据、索引等内容，从而加快访问速度。因此My
SQL运行的物理机上，内存配置也是比较重要的。应用数据库实例前，应该预估活跃的数据大小，然后根据合理配置数据库服务器内存的大小。

磁盘相关
对于 OLTP的数据库，一般场景是 IO 密集型的操作。因此，对于这类情况，应该把更多的注意力放在提高磁盘 IO上。

对于磁盘相关的优化，这里聊聊几种方法：
A、使用 SSD（固态硬盘）或者 Pcle SSD设备；

为什么 SSD比传统机械硬盘快
而传统的机械硬盘需要消耗长时间的磁头旋转和定位来查找数据。
而SSD 其内部是由闪存组成的。闪存延迟低、功耗低。
因此SSD比传统机械硬盘更快。

系统层面优化
当硬件层优化的差不多了之后，系统部分配置也应该去做一些优化。这里就来介绍部分系统层面的优化方法：

调整 I/O 调度算法
MySQL 运行的物理机上，I/O调度算法建议使用：deadline/noop,尽量不使用 CFD。

原因是 CFQ 把 I/O 请求按照进程分别放入进程对应的队列中。CFQ的公平是针对进程而言，每一个提交I/O请求的进程都会有自己的 I/O 队列。以时间算法为前提，轮转调动队列，默认从当前队列中出去4个请求处理，然后处理下一个队列的4个请求，确保每个进程享有的 I/O 资源是均衡的。因此高并发场景，CEQ 很可能会导致 I/O 的响应缓慢。

** 文件系统选择**
优先选用 xfs 或 ext4，坚决不用 ext3。

原因是：ext3在fsck 时需要消耗大量时间，文件越多，时间越长。

调整内核参数

vm.swappiness<=10

降低使用 swap 的概率，但是尽量不要设置为0，可能引起 OOM。

vm。dirty_ratio <=5

这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转为处理文件 IO 而阻塞。

vm.dirty_background_ratio <=10

避免因为 IO 压力瞬间飙升导致内核进程卡死，操作系统 hung住。这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少，就会触发。
pdflush/flush/kdmflush 等后台回写进程运行，将一定缓存的脏页步地刷入外存。

** 参数优化**
在启动MySQL 之前，一些参数合理的设置，可以大大提升 MySQL 的性能。这里就来介绍一部分相对比较重要的参数：

innodb_buffer_pool_size

该参数控制 Innodb 缓存表和索引数据的内存区域大小。对性能影响非常大，建议设置为机器内存的 50-80%。

innodb_flush_log_at_trx_commit

InnoDB 的 read 日志刷新方式，对 InnoDB 的影响会很大。

控制累积多少个事物后才将二进制日志 Fsync 到磁盘。

innodb_file_per_table

开启独立表空间。

max_connection

最大连接数。不能设置的大小，防止客服端连接失败；也不能设置的过大，防止数据库内存资源过多消耗。

long_query_time

慢查询时间阈值。

query_cache_type
query_cache_size

建议这两个参数都设置为0。

MySQL 设计优化
A.使用InnoDB 存储引擎，不建议使用 MyISAM存储引擎；
B.预估表数据量和访问量，如果数据量或者访问量比较大，则需要提前考虑分开分表。
C.指定合适的数据库规范，在设计表、执行 SQL 语句时按照数据库规范来进行。

总结
具体从三个大方面聊到了优化方式：硬件、操作系统、MySQL server。

硬件方面需要考虑的优化内容是：

• CPU
• 内存
• 磁盘

操作系统层面需要考虑的优化为：

• 调整合适的 I/O 调度算法
• 选择合适的文件系统
• 调整部分会影响 MySQL 的内核参数。

在MySQL层

• 调整部分会影响性能的参数
• MySQL设计时的优化。