读书笔记-Effective MySQL Optimizing SQL - Ronald Bradford
第一章 DBA五分钟速成
-
在决定添加索引之前,通常应该至少做两项检查
-
首先验证表现有的结构
1
SHOW CREATE TABLE table_name\G
-
然后确认表的大小
1
SHOW TABLE STATUS LIKE 'table_name'\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21mysql SHOW TABLE STATUS LIKE 'user'\G
*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 11
Create_time: 2023-06-06 02:37:11
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
-
第二章 基本的分析命令
-
EXPLAIN
命令 -
SHOW CREATE TABLE
命令SHOW CREATE TABLE FROM table_name
- 该命令以一种便于阅读和操作的格式向用户呈现基本表中的当前列和索引定义的全部细节.
-
SHOW INDEXES
命令SHOW INDEXES FROM table_name
-
SHOW TABLE STATUS
命令- 使用该命令可以查看数据表的底层大小以及表结构,包括存储引擎类型,版本,数据和索引大小,行的平均长度以及行数.
-
SHOW [GLOBAL|SESSION] STATUS
命令- 默认
SESSION
- 该命令可以查看MySQL服务器的当前内部状态信息
- 默认
-
SHOW [GLOBAL|SESSION] VARIABLES
命令-
该命令可以用来查看MySQL系统变量的当前值
-
MySQL 8.0中对比SESSION和GLOBAL变量值的差异
1
2
3
4
5
6
7SELECT 'SESSION' AS scope, variable_name, variable_value
FROM performance_schema.session_variables
WHERE variable_name IN ('tmp_table_size', 'max_heap_table_size')
UNION
SELECT 'GLOBAL' AS scope, variable_name, variable_value
FROM performance_schema.global_variables
WHERE variable_name IN ('tmp_table_size', 'max_heap_table_size');
-
第三章 深入了解MySQL的索引
第四章 创建MySQL索引
第五章 创建更好的MySQL索引
第六章 MySQL配置选项
- 在MySQL中,引用会话其实与一个给定的连接有关.默认情况下,一个连接也就是一个线程,即一个连接有且只有一个线程.
第七章 SQL的生命周期
-
截取SQL语句,可以通过一下方式进行截取
-
全面查询日志
1
2
3
4[mysqld]
general_log=1
general_log_file=/path/to/file
log_output=FILE- 或者
1
2SET GLOBAL general_log=1;
SET GLOBAL log_output=TABLE; -
慢查询日志
1
2
3
4
5[mysqld]
slow_query_log=1
slow_query_log_file=/path/to/file
long_query_time=0.2
log_output=FILElong_query_time
以秒为单位
-
二进制日志
1
2[mysqld]
log-bin=/path/to/file -
进程列表
1
SHOW FULL PROCESSLIST
-
引擎状态
1
SHOW ENGINE [引擎] STATUS
-
MySQL连接器
-
应用程序代码
-
INFORMATION_SCHEMA
-
PERFORMANCE_SCHEMA
-
SQL语句统计信息插件
-
MySQL代理
-
TCP/IP
-
第八章 性能优化之隐藏秘籍
索引列的改进
数据类型
BIGINT和INT
- 一个
INT UNSIGNED AUTO_INCREMENT
数据类型能够支持的最大值是43亿. - 把主码列的数据类型从
BIGINT
改为INT
的影响是此列每行的容量减小50%,即从8字节减少到4字节.这个改动不仅仅针对主码,所有定义为BIGINT
的外键都可以改成INT
.
- 一个
DATETIME和TIMESTAMP
- 一个
DATETIME
数据类型占用8字节 - 一个
TIMESTAMP
数据类型占用4字节- 使用
TIMESTAMP
类型的缺点是这种类型隐含的默认值为0,这和任何与SQL_MODE
相关的禁用0日期设置相违背. TIMESTAMP
数据类型也不支持NULL
值.- 这个列类型适合存储那些总是有值且存在的时间或日期.
- 使用
- 一个
- 隐含的变换
- 当你为表连接选择一个索引数据类型时,一定要确保这个数据类型是相同的.隐含的类型转换将带来不必要的开销.
- 对于整数类型的列,要确保
UNSIGNED
和SIGNED
类型是统一的. - 对于可变的数据类型,会出现字符集和"逻辑与"运算的复杂情况.
减少SQL语句
- 删除内容重复的SQL语句
- 删除重复执行的SQL语句
- 删除不必要的SQL语句
- 缓存SQL语句的返回结果
删除重复执行的SQL语句
-
使用分析功能可以看出这类SQL执行的总开销
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46SET PROFILING = 1;
select * from user where id =3;
select * from user where id =4;
select * from user where id =5;
select * from user where id =6;
select * from user where id =7;
select * from user where id =8;
select * from user where id =9;
select * from user where id =10;
select * from user where id =11;
select * from user where id =12;
select * from user where id =13;
select * from user where id in (3,4,5,6,7,8,9,10,11,12,13);
SHOW PROFILES;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------+
| 1 | 0.00021900 | select * from user where id =3 |
| 2 | 0.00014175 | select * from user where id =4 |
| 3 | 0.00012300 | select * from user where id =5 |
| 4 | 0.00008925 | select * from user where id =6 |
| 5 | 0.00012050 | select * from user where id =7 |
| 6 | 0.00009000 | select * from user where id =8 |
| 7 | 0.00011800 | select * from user where id =9 |
| 8 | 0.00011925 | select * from user where id =10 |
| 9 | 0.00010200 | select * from user where id =11 |
| 10 | 0.00014300 | select * from user where id =12 |
| 11 | 0.00013900 | select * from user where id =13 |
| 12 | 0.00014475 | select * from user where id in (3,4,5,6,7,8,9,10,11,12,13) |
+----------+------------+------------------------------------------------------------+
SELECT 'Sum Individual Queries' AS txt,SUM(DURATION) AS total_time
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID BETWEEN 1 AND 11
UNION
SELECT 'Combined QUERY',SUM(DURATION)
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 12;
+------------------------+------------+
| txt | total_time |
+------------------------+------------+
| Sum Individual Queries | 0.001429 |
| Combined QUERY | 0.000145 |
+------------------------+------------+
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 HoleLin's Blog!