记一次MySQL SQL优化-Sherlock's Blog

1.记录一次优化过程

1.1 先看表结构和模拟数据

原表结构大致如下：

CREATE TABLE IF NOT EXISTS `test`(  
  `id` INT(8) NOT NULL AUTO_INCREMENT,
  `v1` VARCHAR(32) DEFAULT NULL,
  `v2` VARCHAR(32) DEFAULT NULL,
  `v3` VARCHAR(32) DEFAULT NULL,
  `v4` VARCHAR(32) DEFAULT NULL,
  `v5` VARCHAR(32) DEFAULT NULL,
  `v6` VARCHAR(32) DEFAULT NULL,
  `v7` VARCHAR(32) DEFAULT NULL,
  `v8` VARCHAR(32) DEFAULT NULL,
  `v9` VARCHAR(32) DEFAULT NULL,
  `v10` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_v1` (`v1`)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

我们利用存储过程和函数插入400W条数据

## 开启binlog后需先执行一下以下sql
#SET GLOBAL log_bin_trust_function_creators = 1;
## 创建一个函数 随机生成字符串
DELIMITER $$

DROP FUNCTION IF EXISTS `rand_string`$$

CREATE FUNCTION `rand_string`(n INT) RETURNS VARCHAR(32) CHARSET utf8mb4 
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str VARCHAR(32) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END$$

DELIMITER ;

## 创建一个存储过程
DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)

DROP PROCEDURE IF EXISTS `add_vv`$$

CREATE PROCEDURE `add_vv`(IN n INT)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT INTO test (v1,v2,v3,v4,v5,v6,v7,v8,v9,v10) 
	VALUES 
	(rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32)),
        (rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32),rand_string(32))
	;
	SET i=i+1;
    END WHILE;
END$$

DELIMITER ;

## 调用存储过程
CALL add_vv(200000);

1.2 优化前查询

SELECT COUNT(v2) FROM test WHERE v1 LIKE '23%';

EXPLAIN SELECT COUNT(v2) FROM test WHERE v1 LIKE '23%';

# explain 后 Extra 列显示：Using index condition
这表示：有些搜索条件中虽然出现了索引列，但却不能使用到索引。

1.3 查询执行代价

SHOW VARIABLES LIKE 'optimizer_trace';
SET optimizer_trace="enabled=on ";

SELECT COUNT(v2) FROM test WHERE v1 LIKE '23%';

SHOW STATUS LIKE 'Last_query_cost';
SELECT * FROM information_schema.optimizer_trace;

1.4 优化后查询

SELECT COUNT(v2) FROM test a, (SELECT id FROM  test WHERE v1 LIKE '23%') b WHERE a.id=b.id;

EXPLAIN SELECT COUNT(v2) FROM test a, (SELECT id FROM  test WHERE v1 LIKE '23%') b WHERE a.id=b.id;

# 此时，explain 后 Extra 显示：Using where; Using index

1.5 profiling 分析

SHOW PROFILES;

SHOW PROFILE cpu,block io,`memory`,swaps for query 1;

show profile 在 MySQL5.7 之后就开始是一个过时的选项，不过MySQL 5.7尚且支持，不过已不建议使用。推荐使用 performance schema，并且是默认开启的，可通过SHOW VARIABLES LIKE 'performance_schema';查询状态。

1.6 performance_schema

performance_schema 库下的表可以按照监视维度的不同分组：

--语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)
show tables like '%statement%';

--等待事件记录表，与语句事件类型的相关记录表类似：
show tables like '%wait%';

--阶段事件记录表，记录语句执行的阶段事件的表
show tables like '%stage%';

--事务事件记录表，记录事务相关的事件的表
show tables like '%transaction%';

--监控文件系统层调用的表
show tables like '%file%';

--监视内存使用的表
show tables like '%memory%';

--动态对performance_schema进行配置的配置表
show tables like '%setup%';

performance_schema实践操作：

--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

1.7 show process

2.MySQL SQL优化基本步骤

1.查看执行计划 explain
2.如果有告警信息，查看告警信息show warnings;
3.查看SQL涉及的表结构、索引信息、统计信息
4.根据执行计划，思考可能的优化点

调优手段：explain profiling trace
5.按照可能的优化点执行表结构变更、增加索引、 SQL改写等操作
需要注意：
- 条件字段函数
- 字段隐式转换
- 隐式字符编码转换
6.查看优化后的执行时间和执行计划
7.如果优化效果不明显，重复第四步操作

3.MySQL-逻辑优化总结

3.1 一定能带来优化效果的

主要包括:

1.优先做选择和投影(选择条件在查询树上下推)
2.子查询的消除（ where、 from、 in）
3.嵌套连接的消除
4.外连接消除
5.连接的消除
6.使用等价谓词重写，对条件化简
7.语义优化
8.剪掉冗余操作(一些剪枝优化技术)、最小化
查询块。

3.2 变换未必会带来性能的提高

需根据代价选择，主要包括:

1.分组的合并
2.借用索引优化分组、排序、 DISTINCT等操作
3.对视图的查询变为基于表的查询
4.连接条件的下推
5.分组的下推
6.连接提取公共表达式
7.谓词的上拉
8.用连接取代集合操作
9.用UNIONALL取代OR操作

目录CONTENT

记一次MySQL SQL优化

1.记录一次优化过程

1.1 先看表结构和模拟数据

1.2 优化前查询

1.3 查询执行代价

1.4 优化后查询

1.5 profiling 分析

1.6 performance_schema

1.7 show process

2.MySQL SQL优化基本步骤

3.MySQL-逻辑优化总结

3.1 一定能带来优化效果的

3.2 变换未必会带来性能的提高

评论区