MySQL优化
体系结构
MySQL Server由以下组成：Connection Pool（连接池组件）；Management Service & Utilities（管理服务和工具组件）；SQL Interface；Optimizer；Caches & Buffers；Pluggable Storage Engines（索引依靠于存储引擎）；File System；
索引
索引：帮助MySQL高校获取数据的数据结构（有序）。
索引设计原则
以下情况推荐建立索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，高并发下倾向创建组合索引
- 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
- 查询中统计或分组字段
同时，设计索引时可以遵循一些原则：
- 对查询频次较高，且数据量比较大的表建立索引；
- 索引字段的选择，最佳候选应当从where子句中的条件中提取；
- 使用唯一索引，区分度越高，使用索引的效率越高；
- 索引越多越不易维护；
- 使用短索引，索引创建后使用硬盘存储，短索引可以提升索引访问的I/O效率，可以提升总体的访问效率；
- 利用符合索引，对N个列组合而成的组合索引，就相当于创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
SQL性能监测
查看SQL执行频率
以下命令显示当前session中所有统计参数的值：
show status like 'Com_______';
可以查看当前数据库以什么操作为主：
mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 10 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.10 sec)
explain分析执行计划
explain select * from cnarea_2019 where id = 1;
image-20210305105526395
关于参数的初步解释可以查看参考一。
慢查询日志
默认关闭。
show variables like 'slow_query%';
show variables like 'long_query%';
# 临时开启
set global slow_query_log_file='/var/lib/mysql/tmp_slow.log';
set global long_query_time=1;
set global slow_query_log='ON';
image-20210305132149856
生成的文件格式如下：
image-20210305132605864
包含用户、数据库地址以及详细的SQL语句。
索引的使用
索引验证
在测试索引之前，我们先准备一张500万左右的数据表：
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 4999865 |
+----------+
1 row in set (2.51 sec)
mysql> select * from user limit 3;
+----+------------+------+------------+------------+
| id | username | age | address | company |
+----+------------+------+------------+------------+
| 1 | 88094c37-b | 54 | ecf48a5c-9 | e244831c-6 |
| 2 | 79c2f062-c | 71 | a03f8695-d | bd040bfb-1 |
| 3 | 18dd03ab-9 | 51 | 3332d698-a | 4b4fc273-a |
+----+------------+------+------------+------------+
3 rows in set (0.01 sec)
存储一些随机生成的数据。
当前表除主键外并没有索引。
mysql> select * from user where id = 3000000;
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 3000000 | 3b7df0e5-d | 92 | 425d44f5-a | 2b508d46-0 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from user where username = '3b7df0e5-d';
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 3000000 | 3b7df0e5-d | 92 | 425d44f5-a | 2b508d46-0 |
+---------+------------+------+------------+------------+
1 row in set (2.93 sec)
可以看到，通过主键ID进行查询快于使用username查询，接下来我们为username添加索引。
mysql> create index ind_username on user(username);
Query OK, 0 rows affected (32.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
再进行测试：
mysql> select * from user where id = 4320000;
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 4320000 | dce56995-b | 26 | bc64326a-e | b01d5ba2-8 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from user where username = "dce56995-b";
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 4320000 | dce56995-b | 26 | bc64326a-e | b01d5ba2-8 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)
建立索引后数据查询效率与主键查询速度相当。
索引使用
删除之前的索引
mysql> drop index ind_username on user;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
为username、age、addresss创建复合索引：
mysql> create index ind_username_age_address on user(username,age,address);
Query OK, 0 rows affected (46.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
先使用explain观察SQL执行计划
mysql> explain select * from user where username = "dce56995-b" and age = 26 and company = "b01d5ba2-8"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ind_username_age_address
key: ind_username_age_address
key_len: 88
ref: const,const
rows: 1
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from user where username = "dce56995-b" and age = 26\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ind_username_age_address
key: ind_username_age_address
key_len: 88
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from user where username = "dce56995-b"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ind_username_age_address
key: ind_username_age_address
key_len: 83
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
可以看到以上三个查询条件都可以走ind_username_age_address索引。
再看以下例子
mysql> explain select * from user where age = 26 and username = "dce56995-b"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ind_username_age_address
key: ind_username_age_address
key_len: 88
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
交换and左右条件该走索引依然会走。
但是如果查询未经过最左边的列时便不会走索引
mysql> explain select * from user where age = 26 and address = "dce56995-b"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4981012
filtered: 1.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
下面总结了索引失效的可能原因。
索引失效
-
不符合最左匹配原则
mysql> explain select * from user where age = 26 and company = "b01d5ba2-8"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4981012 filtered: 1.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified -
复合索引中范围查询右边的列
MySQL8中测试仍会走索引
mysql> explain select * from user where username = "adb-5" and age > 64 and company = "e938fd76-e"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: range possible_keys: ind_username_age_address key: ind_username_age_address key_len: 88 ref: NULL rows: 1 filtered: 10.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified mysql> explain select * from user where username = "adb-5" and age = 64 and company = "e938fd76-e"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: ind_username_age_address key: ind_username_age_address key_len: 88 ref: const,const rows: 1 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified只是type发生了变化。
-
like查询前有‘%’，eg: "%Hello"
like使用索引如何避免失效我测试的时候发现都会走索引。
mysql> explain select * from user where username = "%adb-5" and age = 64 and company = "e938fd76-e"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: ind_username_age_address key: ind_username_age_address key_len: 88 ref: const,const rows: 1 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified mysql> explain select * from user where username = "adb-5" and age = 64 and company = "%e938fd76-e"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: ind_username_age_address key: ind_username_age_address key_len: 88 ref: const,const rows: 1 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified猜测：MySQL8对like模糊查询进行了优化。
-
or连接的条件，一个有索引，一个没有，则整个索引都失效
mysql> explain select * from user where username = "adb-5" or company = "Hello"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALL possible_keys: ind_username_age_address key: NULL key_len: NULL ref: NULL rows: 4981012 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified -
字符串未加单引号，存在隐式数据转换可能导致索引失效
mysql> explain select * from user where username = 13\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALL possible_keys: ind_username_age_address key: NULL key_len: NULL ref: NULL rows: 4981012 filtered: 10.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec) ERROR: No query specified -
在索引列上进行运算操作
mysql> explain select * from user where substring(username,2,3) = "abc"\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4981012 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ERROR: No query specified -
MySQL内部优化（MySQL觉得全表扫描会更快）
回表：简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。
常见SQL优化
主键优化
没有特别的需要的话尽量使用一个与业务无关的自增字段作为主键。
原因：1.占用空间小；2.插入搜索快；3.检索搜索快
优化insert语句
1.如果需要同时对一张表插入很多数据时，尽量使用多个值表的insert语句。
insert into user(username,age) values("Hello",3),("World",4);
2.在事务中进行数据插入。
start transaction;
insert into user(username,age) values("Hello",3);
commit;
3.主键有序插入。
优化order by语句
mysql> explain select age,username from user order by age limit 10\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: ind_username_age_address
key_len: 1111
ref: NULL
rows: 4981014
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select age,username,company from user order by age limit 10\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4981014
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
索引覆盖：所要查询的列已包含在索引中，不需要再查询主表。
Extra中为Using Index
第一条语句中的age，username都有对应的索引，而第二条语句中的company没有对应的索引。所以第一条语句做到了索引覆盖，查询效率更优。
MySQL5.x分组后会进行排序，而8则不会。要禁用排序可以用 order by null
优化嵌套查询
MySQL 4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果， 然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接(JOIN) 替代。
示例，查找有角色的所有的用户信息（子查询）:
select * from t_user where id in (select user_id from user_role);
优化后（多表联查）：
select * from t_user u, user_role ur where u.id = ur.user_id;
优化分页
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 4999867 |
+----------+
1 row in set (3.03 sec)
mysql> select * from user limit 4000000,10;
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 4000001 | 273b8cfa-8 | 14 | 8ac53fae-8 | 58682f84-1 |
| 4000002 | e15525b4-7 | 84 | 6c462c67-5 | 86d91e7c-3 |
| 4000003 | 96db55fb-f | 15 | d64c3ca8-4 | 83c58363-1 |
| 4000004 | ed3d258a-a | 76 | d7ef91ef-9 | 1179e728-3 |
| 4000005 | c03a8321-b | 15 | 176320ef-6 | 109928fe-8 |
| 4000006 | 5ca5eba0-2 | 97 | 06d920cd-7 | 5f37f70e-a |
| 4000007 | ed5500ac-4 | 17 | a49b060b-4 | b92af423-a |
| 4000008 | 659d36ca-d | 57 | 3ec9420a-7 | 048298c2-5 |
| 4000009 | 40e965ba-1 | 96 | 778e34c3-4 | de69750e-d |
| 4000010 | 3d9146e1-0 | 1 | acc5050f-b | 59a0e1a9-6 |
+---------+------------+------+------------+------------+
10 rows in set (2.41 sec)
分页查询很慢，可以进行如下优化(多表联查)
mysql> select * from user u, (select id from user order by id limit 4000000,10) a where u.id = a.id;
+---------+------------+------+------------+------------+---------+
| id | username | age | address | company | id |
+---------+------------+------+------------+------------+---------+
| 4000001 | 273b8cfa-8 | 14 | 8ac53fae-8 | 58682f84-1 | 4000001 |
| 4000002 | e15525b4-7 | 84 | 6c462c67-5 | 86d91e7c-3 | 4000002 |
| 4000003 | 96db55fb-f | 15 | d64c3ca8-4 | 83c58363-1 | 4000003 |
| 4000004 | ed3d258a-a | 76 | d7ef91ef-9 | 1179e728-3 | 4000004 |
| 4000005 | c03a8321-b | 15 | 176320ef-6 | 109928fe-8 | 4000005 |
| 4000006 | 5ca5eba0-2 | 97 | 06d920cd-7 | 5f37f70e-a | 4000006 |
| 4000007 | ed5500ac-4 | 17 | a49b060b-4 | b92af423-a | 4000007 |
| 4000008 | 659d36ca-d | 57 | 3ec9420a-7 | 048298c2-5 | 4000008 |
| 4000009 | 40e965ba-1 | 96 | 778e34c3-4 | de69750e-d | 4000009 |
| 4000010 | 3d9146e1-0 | 1 | acc5050f-b | 59a0e1a9-6 | 4000010 |
+---------+------------+------+------------+------------+---------+
10 rows in set (2.24 sec)
也可以用如下方式
mysql> select * from user where id > 4000000 limit 10;
+---------+------------+------+------------+------------+
| id | username | age | address | company |
+---------+------------+------+------------+------------+
| 4000001 | 273b8cfa-8 | 14 | 8ac53fae-8 | 58682f84-1 |
| 4000002 | e15525b4-7 | 84 | 6c462c67-5 | 86d91e7c-3 |
| 4000003 | 96db55fb-f | 15 | d64c3ca8-4 | 83c58363-1 |
| 4000004 | ed3d258a-a | 76 | d7ef91ef-9 | 1179e728-3 |
| 4000005 | c03a8321-b | 15 | 176320ef-6 | 109928fe-8 |
| 4000006 | 5ca5eba0-2 | 97 | 06d920cd-7 | 5f37f70e-a |
| 4000007 | ed5500ac-4 | 17 | a49b060b-4 | b92af423-a |
| 4000008 | 659d36ca-d | 57 | 3ec9420a-7 | 048298c2-5 |
| 4000009 | 40e965ba-1 | 96 | 778e34c3-4 | de69750e-d |
| 4000010 | 3d9146e1-0 | 1 | acc5050f-b | 59a0e1a9-6 |
+---------+------------+------+------------+------------+
10 rows in set (0.00 sec)
