sql优化

SQL优化

一、SQL执行频率

1、查看当前会话的 SQL 命令执行统计信息

show session status like 'Com%'; -- 查看当前会话统计结果
show global status like 'Com%'; -- 查看自数据库上次启动至今统计结果
show status like 'Innodb_rows_%'; -- 查看针对Innodb引擎的统计结果

2、慢查询日志

慢查询日志（slow_query_log）可以记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，可以间接了解 SQL 的执行频率。

步骤：

1. 启用慢查询日志：

   SQLSET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1; -- 设置慢查询阈值（单位：秒）

2. 查看慢查询日志文件路径：

   SHOW VARIABLES LIKE 'slow_query_log_file';

3. 分析慢查询日志文件，查看 SQL 执行频率。

   show variables like 'long_query_time%' -- 查看慢日志记录sql的最低阈值时间
   set global long_query_time = 4; -- 设置最低时间

4. 定位低效率执行SQL

   show processlist;

   执行结果：

   Id	User	Host	db	Command	Time	State	Info
   5	event_scheduler	localhost	null	Daemon	2550	Waiting on empty queue	null
   8	root	172.17.0.1:54214	Scores	Query	0	init	show processlist

   各列含义：

   • Id列，用户登录mysql时，系统分配的connection_id，可以使用函数connection_id来查看。

   • User列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的SQL语句

   • Host列，显示这个语句是从哪个ip和端口上发的，可以用来跟踪出现问题语句的用户

   • db列，显示这个进程目前连接的是哪个数据库

   • Commandl列，显示当前连接的执行的命令，一般取值为休眠（Sleep）,查询（Query）,连接（connent）等.

   • Time列，显示这个状态持续的时间，单位（秒）

   • State列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table，sorting result，sending data等状态才可以完成

   • info列，显示这个sql语句，是判断问题语句的一个重要依据。

二、Explain分析执行计划

基本使用

句式：

Explain + SQL语句

示例：

EXPLAIN SELECT
    salary,
    sum(salary) over (order by id desc) as `total`
FROM user
ORDER BY id DESC;

结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user		ALL					6	100.00	Using filesort

列表含义：

字段	含义
Id	select查询的序列号，是一组数字，表示查询中执行select子句或者操作表的顺序
select_type	select类型，常见的取值SIMPLE(简单表，不使用表连接或子查询)，PRIMARY(主查询，即外层的查询)，UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等
table	输出结果集的表
partitions	显示查询将访问哪些分区（如果表使用了分区）。【分区的介绍和用法将单独写一篇文章】
type	表示表的连接类型，性能由好到差的连接类型为（system >> const >> eq_ref >> ref >> ref_or_null >> index_merge >> index_subquery >> range >> index >> all）mysql5.7及以上对系统查表，返回的是all，而不是system
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	显示在连接条件或索引查找中，哪些列或常量被用于与索引进行比较。
rows	扫描行的数量
filtered	显示查询优化器估算的、经过条件过滤后剩余的行数占扫描行数的百分比。 范围是 0 到 100。 100 表示没有进行过滤。
Extra	执行情况的说明和描述

三、Show profile分析sql

MySQL5.0.37开始增加对show profiles 和 show profile 语句的支持。

这能帮助我们时间耗费到哪里去了。

SELECT @@have_profiling; -- 检查是否支持profile
set profiling=1; -- 开启profiling 开关

show profiles; -- 查看所有的sql耗时（显示每个sql的宏观耗时）
show profile for query 8;  -- 查看单个sql的微观耗时

四、Trace分析优化器执行计划

-- 开启trace追踪,在生成的 JSON 格式输出中添加结束标记
set optimizer_trace="enabled=on",end_markers_in_json=on;
-- 设置 MySQL 优化器跟踪信息的最大内存占用大小
set optimizer_trace_max_mem_size=1000000;
-- 查看 MySQL 优化器的执行计划生成过程（这命令需要在命令行查看）
select * from information_schema.optimizer_trace\G;

完整过程：

mysql> use Scores
Database changed
mysql> select * from user;
+----+--------+-------+--------+
| id | dname  | ename | salary |
+----+--------+-------+--------+
|  1 | 研发部 | 张三  |   5000 |
|  2 | 研发部 | 李四  |   6000 |
|  3 | 研发部 | 王五  |   5000 |
|  4 | 研发部 | 赵四  |   8000 |
|  5 | 销售部 | 卡尔  |   3000 |
|  6 | 销售部 | 可可  |   3000 |
|  7 | 销售部 | 丽丽  |   4000 |
|  8 | 销售部 | 楠楠  |   3000 |
+----+--------+-------+--------+
8 rows in set (0.00 sec)

mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: select * from user
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`dname` AS `dname`,`user`.`ename` AS `ename`,`user`.`salary` AS `salary` from `user`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`user`",
                "table_scan": {
                  "rows": 6,
                  "cost": 0.25
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 6,
                      "access_type": "scan",
                      "resulting_rows": 6,
                      "cost": 0.85,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 6,
                "cost_for_plan": 0.85,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`user`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`user`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

输出内容

• QUERY: 被跟踪的查询。
• TRACE: 优化器的详细决策过程，包括解析、优化和执行阶段。
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 如果跟踪信息超出最大内存限制，显示缺失的字节数。
• INSUFFICIENT_PRIVILEGES: 是否有权限不足的问题。

五、索引优化

索引生效会使得执行效率提高！

-- 创建组合索引
create index index_name on user tb_1(id,dname,salary);
-- 全值匹配（索引有几个字段where过滤就要匹配几个字段）
explain select * from user where id=1 and dname="研发部"
-- 最左前缀（至少使用最前面的索引字段）
explain select * from user where id=1 

其他匹配原则

-- 范围查询右边的列，不能使用索引
explain select * from user where salary=1 and dname="研发部"
-- 在索引列上进行运算操作，会造成索引失效
explain select * from user where substring(dname,1,2) = "研发"
-- where过滤条件中，字符串不加单引号，会造成索引失效
explain select * from user where id=1 
-- 尽量使用覆盖索引，避免使用*(查询字段需是有索引的列),覆盖索引即select的字段都存在索引
explain select dname from user where id=1 
-- 用or分割查询，索引无效
explain select dname from user where id=1 or id =3
-- 以%开头的like模糊查询，索引无效
explain select * from user where dname like ="三%"  -- 这个索引有效
explain select * from user where dname like ="%三" -- 这个索引无效
explain select * from user where dname like ="%三%" -- 这个索引无效
-- 不用*即可使用索引
explain select dname from user where dname like ="%三"

-- 若mysql评估使用索引比全表更慢，则不使用索引（高重复的数据不应该建立索引）
CREATE index username_index ON message_record(user_name);
-- 此表中，user_name列，大部分都是admin,只有一个111
explain select * FROM message_record where user_name = "111"; -- 索引生效 ref

explain select * FROM message_record where user_name = "admin"; -- 索引未生效 all
-- is NULL ,is NOT NULL 索引有时有效，有时无效
explain select * FROM message_record where user_name is NULL; -- 索引生效 ref
explain select * FROM message_record where user_name is not NULL; -- 索引未生效 all

-- in 索引有效，not in 索引失效
explain select * FROM user where id in ("1","2"); -- 普通和主键索引生效 range
explain select * FROM user where id not in ("1","2"); -- 主键索引生效 range,普通索引失效
-- 单列索引和复合索引，尽量使用复合索引
-- 若查询条件中的多个列都有索引，mysql会找一个最优的索引使用
explain select * FROM message_record where user_name = "111" and id = 1;

以上都加了explain 在查询效率的结果中，Extra列：

• using index：使用覆盖索引会出现
• using where ：使用索引时需要回表查询时会出现
• using index condition：使用索引时需要回表查询时会出现
• using index; using where: 使用索引时，数据都能在索引中找到，不需要回表查询时会出现

六、SQL优化

大批量数据加载优化

6.1、主键顺序插入

检查全局系统变量local_infile的状态

show global variables like 'local_infile'; -- 通常为OFF
set global local_infile=1; -- 修改为on ，开启local_infile

加载数据

通过load向表加载数据时，保证文件中数据主键有序可提高效率！

-- 主键有序 100万行数据(22.671s)
load data local infile 'sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

-- 清空数据
truncate table tb_user;

-- 主键无序 100万行数据(81.739s)
load data local infile 'sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';

6.2、关闭唯一性校验

这里是当表中存在UNIQUE约束时，大批量加载数据会每次都进行唯一性校验，这将显著降低加载效率！

通常插入的这种大批量的表是不需要再校验的，所以需要关闭后再加载，之后再开启就好了。

set UNIQUE_CHECKS=0 -- 默认是1开启的

-- 清空数据
truncate table tb_user;

-- 主键有序 + 关闭唯一性校验 100万行数据(21.734s)
load data local infile 'sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

set UNIQUE_CHECKS=1 -- 恢复开启唯一性校验

优化insert语句

多值表的insert语句

-- 多行数据插入，用多值表的insert语句,这可以减少客户端与数据库的连接与关闭的消耗

-- 优化前
insert into user value(1,'Tom');
insert into user value(2,'Cat');
insert into user value(3,'Jerry');

-- 优化后
insert into user value(1,'Tom'),(2,'Cat'),(3,'Jerry');

手动事务提交

begin;
insert into user value(1,'Tom');
insert into user value(2,'Cat');
insert into user value(3,'Jerry');
commit;

插入的数据尽量有序，避免无序

-- 优化前:索引列无序
insert into user value(1,'Tom');
insert into user value(3,'Jerry');
insert into user value(2,'Cat');

-- 优化后：:索引列有序
insert into user value(1,'Tom');
insert into user value(2,'Cat');
insert into user value(3,'Jerry');

优化 order by 语句

两种排序方式

filesort：非索引排序就是filesort

index：用索引排序

-- 创建表
create table `emp`(
	`id` int(11) not null auto_increment,
    `name` varchar(100) not null,
    `age` int(3) not null,
    `salary` int(11) default null,
    primary key (`id`)
);

begin;
insert into `emp`(`id`,`name`,`age`,`salary`) values('1','Tom','18','2300');
insert into `emp`(`id`,`name`,`age`,`salary`) values('2','Jerry','21','3500');
insert into `emp`(`id`,`name`,`age`,`salary`) values('3','Alex','22','3600');
insert into `emp`(`id`,`name`,`age`,`salary`) values('4','Sam','23','3600');
insert into `emp`(`id`,`name`,`age`,`salary`) values('5','july','22','2300');
insert into `emp`(`id`,`name`,`age`,`salary`) values('6','miki','30','6000');
insert into `emp`(`id`,`name`,`age`,`salary`) values('7','bili','27','4100');
insert into `emp`(`id`,`name`,`age`,`salary`) values('8','Tomas','30','5000');
insert into `emp`(`id`,`name`,`age`,`salary`) values('9','peter','25','8000');
commit;


-- 创建组合索引
create index idx_emp_age_salary on emp(age,salary);
-- 排序，order by 
explain select * from emp order by age;  -- using filesort
explain select * from emp order by age,salary; -- using filesort

explain select id from emp order by age;  -- using index
explain select id,age from emp order by age; -- using index
explain select id,age,salary,name from emp order by age;  -- using filesort

-- order by 后面的多个排序字段要求尽量排序方式相同
explain select id,age from emp order by age asc,salary desc;  -- using index;using filesort
explain select id,age from emp order by age desc,salary desc;  -- backward index scan ;using index
-- order by 后面的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from emp order by age,salary; -- using index;using filesort

FileSort优化

两次扫描算法：mysql4.1之前，使用该方式排序。首先取出排序字段和行指针信息，然后在排序区sort buff 中排序，如果sort buff不够，则在临时表中存储排序结果。完成排序后再回表读取记录，期间会导致大量随机I/O操作。

**一次扫描算法：**一次性取出满足条件的所有字段，然后在排序区排序后直接输出结果集。此方法在排序时内存开销大，但是效率比两次扫描高。

mysql通过系统变量max_length_for_sort_data的大小和Query语句取出字段总大小，来判定用哪种算法。如果系统变量max_length_for_sort_data更大，则用第二种，否则用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率。

show variables like "max_length_for_sort_data"; -- 4096
show variables like "sort_buffer_size"; -- 262144

优化子查询

能用连接查询就尽量不要用子查询，子查询需要建立临时表后再进行查询，这两步操作会使得效率低于连接查询。

Limit优化

优化思路一

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容

select * from tb_user limit 900000,10; -- 0.643s
select * from tb_user a,(select id from tb_user order by id limit 900000,10) b where a.id = b.id; -- 0.486s

优化思路二

适用于主键自增的表，可以把limit查询转换成某个位置的查询。

select * from tb_user id > 900000 limit 10; -- 0s