MySQL性能优化详解【精华篇】

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