MySQL性能优化详解【精华篇】
0. 前置说明
-
本文使用的数据库存储200万行的记录
-
全文词数:6169
-
sql文件下载地址:https://gitee.com/alizipeng/the-way-of-programming/blob/master/09-%E6%95%B0%E6%8D%AE%E5%BA%93/user.sql
-
若希望看到其他技术笔记欢迎来访我的技术笔记项目:https://gitee.com/alizipeng/the-way-of-programming
mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (0.14 sec) mysql> select * from user limit 10; +----+----------------------+------+-----------+---------------------+ | id | name | age | address | birthday | +----+----------------------+------+-----------+---------------------+ | 1 | Z7zEgyrL3PJq86sfhas3 | 26 | 云浮市 | 2019-01-13 10:04:55 | | 2 | MrldItwPeb6VDk7zCOO1 | 2 | 汕尾市 | 2018-10-18 09:41:51 | | 3 | 5ag8vXuMnV0JRBH2PKkG | 72 | 梅州市 | 2021-06-16 06:22:28 | | 4 | SccWwgENpikMyriAdJ3V | 12 | 江门市 | 2019-01-17 15:21:14 | | 5 | 96jgKGorO4IQAFg6kIKN | 63 | 深圳市 | 2020-02-25 14:35:45 | | 6 | l8ZJtMI90mfHGTSMcZGm | 15 | 珠海市 | 2019-07-05 12:25:37 | | 7 | ZUu15AfviMv64bI9JGVk | 21 | 东莞 | 2018-12-04 17:18:22 | | 8 | CVXyTqfPgOVPrq2UDPol | 37 | 梅州市 | 2021-03-24 11:51:06 | | 9 | 2sLdyXj6ihbQ2eXUc8n0 | 35 | 河源市 | 2020-11-16 16:39:59 | | 10 | pyM43aO6C24upRSDSMYr | 37 | 湛江市 | 2020-07-27 22:11:45 | +----+----------------------+------+-----------+---------------------+ 10 rows in set (0.00 sec)
1. 索引优化
索引是优化查询最有效的方式之一
1.1 是否使用索引的区别
- 不使用索引
mysql> select * from user where name = 'Z7zEgyrL3PJq86sfhas3';
+----+----------------------+------+-----------+---------------------+
| id | name | age | address | birthday |
+----+----------------------+------+-----------+---------------------+
| 1 | Z7zEgyrL3PJq86sfhas3 | 26 | 云浮市 | 2019-01-13 10:04:55 |
+----+----------------------+------+-----------+---------------------+
1 row in set (0.55 sec)
- 使用索引
mysql> create index idx_user_name on user(name);
Query OK, 0 rows affected (20.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from user where name = 'Z7zEgyrL3PJq86sfhas3';
+----+----------------------+------+-----------+---------------------+
| id | name | age | address | birthday |
+----+----------------------+------+-----------+---------------------+
| 1 | Z7zEgyrL3PJq86sfhas3 | 26 | 云浮市 | 2019-01-13 10:04:55 |
+----+----------------------+------+-----------+---------------------+
1 row in set (0.00 sec)
1.2 索引的使用
1.2.1 避免索引失效
1.2.1.1 建立复合索引
mysql> create index idx_user_name_age_address on user(name, age, address);
Query OK, 0 rows affected (29.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
1.2.1.2 全值匹配
对索引中所有列都指定具体值,索引生效
explain select * from user where name='Z7zEgyrL3PJq86sfhas3' and age = 26 and address='云浮市';
1.2.1.3 最左前缀法则
如果索引多列,需要遵循最左前缀法则。指查询从索引的最左列顺序开始,不能跳过中间的列
索引生效的情况:
索引失效的情况:
1.2.1.4 范围查询
范围查询右边的列不能使用索引
1.2.1.5 运算操作
在索引列上使用运算操作,索引失效
1.2.1.6 字符串字段
不加单引号,索引失效【该执行结果使用的是另外的表】
1.2.1.7 覆盖索引
尽量使用覆盖索引【索引包含查询列】,减少使用select *
Extra字段:
using index:使用覆盖索引
using where:使用索引,但需要回表查询
using index condition:使用索引,但需要回表查询
using index; using where:使用索引,需要的字段都在索引中找到,不需要回表查询
1.2.1.8 OR条件查询
若 or 前的条件有索引,后面的列没有索引,则索引全部失效
explain select * from user where name = 'Z7zEgyrL3PJq86sfhas3' or birthday='2019-01-13 10:04:55';
1.2.1.9 Like模糊查询
以%
开头的Like模糊查询,索引失效
但可以通过覆盖索引解决
1.2.1.10 全表扫描
若MySQL判定全表扫描将比使用索引更快,则不使用索引
1.2.1.11 is [not] null
有时索引失效【根据该字段为null的比例判断】
1.2.1.12 [not] in
// todo
1.2.1.13 单列索引和复合索引
尽量使用复合索引,少使用单列索引
复合索引idx_user_name_age_address
相当于创建了三个索引:
name
name + age
name + age + address
当创建多个单列索引时,数据库会选择一个最优的索引【辨识度最高】,并不会使用全部索引
1.2.2 查看索引使用情况
show [session|global]status like 'Handler_read%';
mysql> show global status like 'Handler_read%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Handler_read_first | 194 |
| Handler_read_key | 6004303 |
| Handler_read_last | 0 |
| Handler_read_next | 6007205 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 330 |
| Handler_read_rnd_next | 33054118 |
+-----------------------+----------+
7 rows in set (0.00 sec)
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 17 |
| Handler_read_last | 0 |
| Handler_read_next | 16 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4 |
|