读书笔记-Effective MySQL Optimizing SQL - Ronald Bradford

第一章 DBA五分钟速成

在决定添加索引之前,通常应该至少做两项检查

首先验证表现有的结构
1
SHOW CREATE TABLE table_name\G

然后确认表的大小

1	SHOW TABLE STATUS LIKE 'table_name'\G

mysql 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变量值的差异

SELECT '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语句,可以通过一下方式进行截取

全面查询日志

[mysqld]
general_log=1
general_log_file=/path/to/file
log_output=FILE

或者

1 2	SET GLOBAL general_log=1; SET GLOBAL log_output=TABLE;

慢查询日志

[mysqld]
slow_query_log=1
slow_query_log_file=/path/to/file
long_query_time=0.2
log_output=FILE

long_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执行的总开销

SET 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 |
+------------------------+------------+

第一章 DBA五分钟速成

第二章 基本的分析命令

第三章 深入了解MySQL的索引

第四章 创建MySQL索引

第五章 创建更好的MySQL索引

第六章 MySQL配置选项

第七章 SQL的生命周期

第八章 性能优化之隐藏秘籍

索引列的改进

数据类型

减少SQL语句

删除重复执行的SQL语句

第二章基本的分析命令

第三章深入了解MySQL的索引

第四章创建MySQL索引

第五章创建更好的MySQL索引

第八章性能优化之隐藏秘籍