sql性能分析和sql优化

在介绍常见的sql优化前，我们先了解一下sql性能优化的相关知识。

一. 查看数据库的sql执行频率

MySQL 客户端连接成功后，通过

show [session|global] status

命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次：

SHOW GLOBAL STATUS LIKE 'Com_______%';

select权重占比高的则需要考虑进行sql优化

二. 慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志。
MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息：

-- 开启MySQL慢日志查询开关 slow_query_log=1 -- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2

三. profile

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling;

默认 profiling 是关闭的，可以通过 set 语句在 session/global 级别开启 profiling：

SET profiling = 1;

执行一系列的业务 SQL 的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况 show profiles; -- 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; -- 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;

四. explain关键字

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：

-- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

explain执行计划各字段含义：

Id：select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序（id 相同，执行顺序从上到下；id 不同，值越大，越先执行）。
select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE 之后包含了子查询）等。
type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all。
possible_key显示可能应用在这张表上的索引，一个或多个。
Key：实际使用的索引，如果为 NULL，则没有使用索引。
Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rowsMySQL：认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
Extra: 这一行展示的是额外信息，常见的重要值有.
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
Using where：需要回表再过滤。
Using index condition：MySQL 在存储引擎层利用索引中的列先过滤一部分数据，再决定是否回表
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

主要关注type possible_keys key ken_len rows Extra

五. sql优化

5.1 插入优化

I.批量插入

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 手动提交事务 start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;

主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89

II. 大批量插入数据操作
如果一次性需要插入大批量数据，使用Insert语句插入性能较低，此时可以使用MYSQL数据库提供的load指令进行插入。操作如下

MySQL 本地数据导入配置

-- 客户端连接服务端时，加上参数 --local-infile mysql --local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile=1; -- 执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

关键参数说明
–local-infile：客户端连接参数，允许从本地文件系统加载数据文件。
local_infile=1：MySQL 全局开关，控制是否允许本地数据文件导入。
fields terminated by ‘,’：指定字段分隔符为逗号。
lines terminated by ‘\n’：指定行分隔符为换行符。
local infile：关键字，声明从本地文件导入数据。

5.2 主键优化

主键设计原则

· 满足业务需求的情况下，尽量降低主键的长度。
· 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。
· 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。
· 业务操作时，避免对主键的修改

主键尽量选择顺序插入，主键乱序插入时，可能会导致页分裂

什么是页分裂？ 当向页中插入数据时，如果页空间不足，Mysql就会创建一个新的页，把原页中的部分数据移动到新页。这个过程就叫页分裂。页分裂会带来额外的IO，需要写新页，移动数据，更新索引。并且可能导致B+Tree的结构调整，从而导致树高度增加。而且页分裂会产生数据碎片，页不再连续，磁盘的随机IO增加。

在这里介绍一下页分裂的反过程，页合并

合并页的阈值可以通过修改MERGE_THRESHOLD 来修改，默认为50%。

5.3 order by优化
mysql中order by 优化的核心目标主要是为了避免Using filesort。可以通过explain关键字的extra列返回的信息，判断具体是哪一种，如果是desc倒序，会出现Backward index scan（倒序扫描索引）。

order by优化时主要注意下面几点：
· 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
· 尽量使用覆盖索引
· 避免在order by中使用函数或表达式，这样索引会失效。
· where 和 order by尽量使用同一个联合索引
· 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
· 如果不可避免的出现filesort,大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

例如：

-- 根据age, phone进行降序一个升序，一个降序 explain select id,age,phone from tb_user order by age asc, phone desc; -- 创建索引 create index idx_user_age_phone_ad on tb_user(age asc, phone desc); -- 根据age, phone进行降序一个升序，一个降序 explain select id,age,phone from tb_user order by age asc, phone desc;

5.4 group by优化

-- 删除掉目前的联合索引 idx_user_pro_age_sta drop index idx_user_pro_age_sta on tb_user; -- 执行分组操作，根据profession字段分组 explain select profession,count(*) from tb_user group by profession; -- 创建索引 Create index idx_user_pro_age_sta on tb_user(profession, age, status); -- 执行分组操作，根据profession字段分组 explain select profession,count(*) from tb_user group by profession; -- 执行分组操作，根据profession字段分组 explain select profession,count(*) from tb_user group by profession, age;

核心优化原则
在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也满足最左前缀法则。

5.5 limit优化

limit优化主要解决深分页问题。

优化策略一：使用索引+order by
eg：

SELECT * FROM orders ORDER BY id LIMIT 10;

此时如果id是主键索引，B+Tree索引顺序扫描，读取十条结束，速度非常快。

优化策略二：延迟关联

错误写法：

SELECT * FROM orders ORDER BY id LIMIT 100000,10;

此时需要回表100000 IO非常大
优化：

SELECT id FROM orders ORDER BY id LIMIT 100000,10;

再回表

SELECT * FROM orders WHERE id IN ( SELECT id FROM orders ORDER BY id LIMIT 100000,10 )

优化策略三：使用覆盖索引+子查询的形式优化

解决深分页limit优化的经典策略

SELECT * FROM orders WHERE id IN ( SELECT id FROM orders ORDER BY create_time LIMIT 1000000,10 );

创建合适的覆盖索引

CREATE INDEX idx_create_time_id ON orders(create_time, id);

5.6 count优化

count 的几种用法

count (主键)
InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null）。

count (字段)
没有 not null 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null 则计数累加。
有 not null 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count(1)
InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 “1” 进去，直接按行进行累加。

count(*)
InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
效率排序：count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

5.7 update语句优化

在进行更新操作时，一定要根据索引字段进行更新操作，否则会将行锁升级成为表锁，降低并发性能。InnoDB的行锁是针对索引加的锁，并且该索引不能失效，否则会从行锁升级成表锁。

5.8 小表驱动大表

SELECT * FROM t_user u WHERE EXISTS (SELECT 1 FROM t_order o WHERE o.user_id = u.id);

执行逻辑

IN 关键字：优先执行子查询（IN 内部语句），再执行外部查询。若子查询数据量少，条件查询速度更快。
EXISTS 关键字：优先执行主查询（EXISTS 左侧语句），将结果作为条件与右侧子查询匹配，匹配成功则保留数据，否则过滤。
场景示例
order 表（10000 条，大表），user 表（100 条，小表）：
若 order 表在左侧，使用 IN 性能更好。
总结
IN：适用于左边大表，右边小表的场景。
EXISTS：适用于左边小表，右边大表的场景。