MySQL優(yōu)化
體系結(jié)構(gòu)
MySQL Server由以下組成：Connection Pool（連接池組件）；Management Service & Utilities（管理服務(wù)和工具組件）；SQL Interface；Optimizer；Caches & Buffers；Pluggable Storage Engines（索引依靠于存儲(chǔ)引擎）；File System；
索引
索引：幫助MySQL高校獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。
索引設(shè)計(jì)原則
以下情況推薦建立索引：
- 主鍵自動(dòng)建立唯一索引
- 頻繁作為查詢(xún)條件的字段
- 查詢(xún)中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
- 單鍵/組合索引的選擇問(wèn)題，高并發(fā)下傾向創(chuàng)建組合索引
- 查詢(xún)中排序的字段，排序字段通過(guò)索引訪(fǎng)問(wèn)大幅提高排序速度
- 查詢(xún)中統(tǒng)計(jì)或分組字段
同時(shí)，設(shè)計(jì)索引時(shí)可以遵循一些原則：
- 對(duì)查詢(xún)頻次較高，且數(shù)據(jù)量比較大的表建立索引；
- 索引字段的選擇，最佳候選應(yīng)當(dāng)從where子句中的條件中提??；
- 使用唯一索引，區(qū)分度越高，使用索引的效率越高；
- 索引越多越不易維護(hù)；
- 使用短索引，索引創(chuàng)建后使用硬盤(pán)存儲(chǔ)，短索引可以提升索引訪(fǎng)問(wèn)的I/O效率，可以提升總體的訪(fǎng)問(wèn)效率；
- 利用符合索引，對(duì)N個(gè)列組合而成的組合索引，就相當(dāng)于創(chuàng)建了N個(gè)索引，如果查詢(xún)時(shí)where子句中使用了組成該索引的前幾個(gè)字段，那么這條查詢(xún)SQL可以利用組合索引來(lái)提升查詢(xún)效率。
SQL性能監(jiān)測(cè)
查看SQL執(zhí)行頻率
以下命令顯示當(dāng)前session中所有統(tǒng)計(jì)參數(shù)的值：
show status like 'Com_______';
可以查看當(dāng)前數(shù)據(jù)庫(kù)以什么操作為主：
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分析執(zhí)行計(jì)劃
explain select * from cnarea_2019 where id = 1;
image-20210305105526395
關(guān)于參數(shù)的初步解釋可以查看參考一。
慢查詢(xún)?nèi)罩?/h3>
默認(rèn)關(guān)閉。
show variables like 'slow_query%';
show variables like 'long_query%';
# 臨時(shí)開(kāi)啟
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
包含用戶(hù)、數(shù)據(jù)庫(kù)地址以及詳細(xì)的SQL語(yǔ)句。
索引的使用
索引驗(yàn)證
在測(cè)試索引之前，我們先準(zhǔn)備一張500萬(wàn)左右的數(shù)據(jù)表：
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)
存儲(chǔ)一些隨機(jī)生成的數(shù)據(jù)。
當(dāng)前表除主鍵外并沒(méi)有索引。
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)
可以看到，通過(guò)主鍵ID進(jìn)行查詢(xún)快于使用username查詢(xún)，接下來(lái)我們?yōu)閡sername添加索引。
mysql> create index ind_username on user(username);
Query OK, 0 rows affected (32.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
再進(jìn)行測(cè)試：
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)
建立索引后數(shù)據(jù)查詢(xún)效率與主鍵查詢(xún)速度相當(dāng)。
索引使用
刪除之前的索引
mysql> drop index ind_username on user;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
為username、age、addresss創(chuàng)建復(fù)合索引：
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觀(guān)察SQL執(zhí)行計(jì)劃
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
可以看到以上三個(gè)查詢(xún)條件都可以走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左右條件該走索引依然會(huì)走。
但是如果查詢(xún)未經(jīng)過(guò)最左邊的列時(shí)便不會(huì)走索引
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
下面總結(jié)了索引失效的可能原因。
索引失效
-
不符合最左匹配原則
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 -
復(fù)合索引中范圍查詢(xún)右邊的列
MySQL8中測(cè)試仍會(huì)走索引
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發(fā)生了變化。
-
like查詢(xún)前有‘%’，eg: "%Hello"
like使用索引如何避免失效我測(cè)試的時(shí)候發(fā)現(xiàn)都會(huì)走索引。
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猜測(cè)：MySQL8對(duì)like模糊查詢(xún)進(jìn)行了優(yōu)化。
-
or連接的條件，一個(gè)有索引，一個(gè)沒(méi)有，則整個(gè)索引都失效
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 -
字符串未加單引號(hào)，存在隱式數(shù)據(jù)轉(zhuǎn)換可能導(dǎo)致索引失效
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 -
在索引列上進(jìn)行運(yùn)算操作
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內(nèi)部?jī)?yōu)化（MySQL覺(jué)得全表掃描會(huì)更快）
回表：簡(jiǎn)單來(lái)說(shuō)就是數(shù)據(jù)庫(kù)根據(jù)索引（非主鍵）找到了指定的記錄所在行后，還需要根據(jù)主鍵再次到數(shù)據(jù)塊里獲取數(shù)據(jù)。
常見(jiàn)SQL優(yōu)化
主鍵優(yōu)化
沒(méi)有特別的需要的話(huà)盡量使用一個(gè)與業(yè)務(wù)無(wú)關(guān)的自增字段作為主鍵。
原因：1.占用空間小；2.插入搜索快；3.檢索搜索快
優(yōu)化insert語(yǔ)句
1.如果需要同時(shí)對(duì)一張表插入很多數(shù)據(jù)時(shí)，盡量使用多個(gè)值表的insert語(yǔ)句。
insert into user(username,age) values("Hello",3),("World",4);
2.在事務(wù)中進(jìn)行數(shù)據(jù)插入。
start transaction;
insert into user(username,age) values("Hello",3);
commit;
3.主鍵有序插入。
優(yōu)化order by語(yǔ)句
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
索引覆蓋：所要查詢(xún)的列已包含在索引中，不需要再查詢(xún)主表。
Extra中為Using Index
第一條語(yǔ)句中的age，username都有對(duì)應(yīng)的索引，而第二條語(yǔ)句中的company沒(méi)有對(duì)應(yīng)的索引。所以第一條語(yǔ)句做到了索引覆蓋，查詢(xún)效率更優(yōu)。
MySQL5.x分組后會(huì)進(jìn)行排序，而8則不會(huì)。要禁用排序可以用 order by null
優(yōu)化嵌套查詢(xún)
MySQL 4.1版本之后，開(kāi)始支持SQL的子查詢(xún)。這個(gè)技術(shù)可以使用SELECT語(yǔ)句來(lái)創(chuàng)建一個(gè)單列的查詢(xún)結(jié)果， 然后把這個(gè)結(jié)果作為過(guò)濾條件用在另一個(gè)查詢(xún)中。使用子查詢(xún)可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫(xiě)起來(lái)也很容易。但是，有些情況下，子查詢(xún)是可以被更高效的連接(JOIN) 替代。
示例，查找有角色的所有的用戶(hù)信息（子查詢(xún)）:
select * from t_user where id in (select user_id from user_role);
優(yōu)化后（多表聯(lián)查）：
select * from t_user u, user_role ur where u.id = ur.user_id;
優(yōu)化分頁(yè)
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)
分頁(yè)查詢(xún)很慢，可以進(jìn)行如下優(yōu)化(多表聯(lián)查)
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)
