MySQL優(yōu)化
體系結(jié)構(gòu)
MySQL Server由以下組成：Connection Pool（連接池組件）姨拥；Management Service & Utilities（管理服務(wù)和工具組件）翁逞；SQL Interface崖媚；Optimizer梗劫；Caches & Buffers享甸；Pluggable Storage Engines（索引依靠于存儲引擎）截碴；File System；
索引
索引：幫助MySQL高校獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）蛉威。
索引設(shè)計原則
以下情況推薦建立索引：
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段
- 查詢中與其他表關(guān)聯(lián)的字段日丹，外鍵關(guān)系建立索引
- 單鍵/組合索引的選擇問題，高并發(fā)下傾向創(chuàng)建組合索引
- 查詢中排序的字段蚯嫌，排序字段通過索引訪問大幅提高排序速度
- 查詢中統(tǒng)計或分組字段
同時哲虾，設(shè)計索引時可以遵循一些原則：
- 對查詢頻次較高，且數(shù)據(jù)量比較大的表建立索引齐帚；
- 索引字段的選擇妒牙，最佳候選應(yīng)當(dāng)從where子句中的條件中提取对妄；
- 使用唯一索引湘今，區(qū)分度越高，使用索引的效率越高剪菱；
- 索引越多越不易維護(hù)摩瞎；
- 使用短索引，索引創(chuàng)建后使用硬盤存儲孝常，短索引可以提升索引訪問的I/O效率旗们，可以提升總體的訪問效率；
- 利用符合索引构灸，對N個列組合而成的組合索引上渴，就相當(dāng)于創(chuàng)建了N個索引，如果查詢時where子句中使用了組成該索引的前幾個字段喜颁，那么這條查詢SQL可以利用組合索引來提升查詢效率稠氮。
SQL性能監(jiān)測
查看SQL執(zhí)行頻率
以下命令顯示當(dāng)前session中所有統(tǒng)計參數(shù)的值：
show status like 'Com_______';
可以查看當(dāng)前數(shù)據(jù)庫以什么操作為主：
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í)行計劃
explain select * from cnarea_2019 where id = 1;
image-20210305105526395
關(guān)于參數(shù)的初步解釋可以查看參考一。
慢查詢?nèi)罩?/h3>
默認(rèn)關(guān)閉半开。
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
包含用戶隔披、數(shù)據(jù)庫地址以及詳細(xì)的SQL語句。
索引的使用
索引驗證
在測試索引之前寂拆，我們先準(zhǔn)備一張500萬左右的數(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)
存儲一些隨機生成的數(shù)據(jù)奢米。
當(dāng)前表除主鍵外并沒有索引。
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進(jìn)行查詢快于使用username查詢鬓长，接下來我們?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)行測試：
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ù)查詢效率與主鍵查詢速度相當(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觀察SQL執(zhí)行計劃
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左右條件該走索引依然會走怠蹂。
但是如果查詢未經(jīng)過最左邊的列時便不會走索引
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ù)合索引中范圍查詢右邊的列
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發(fā)生了變化。
-
like查詢前有‘%’城侧，eg: "%Hello"
like使用索引如何避免失效我測試的時候發(fā)現(xiàn)都會走索引易遣。
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模糊查詢進(jìn)行了優(yōu)化。
-
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 -
字符串未加單引號屋摇，存在隱式數(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)行運算操作
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)部優(yōu)化（MySQL覺得全表掃描會更快）
回表：簡單來說就是數(shù)據(jù)庫根據(jù)索引（非主鍵）找到了指定的記錄所在行后揩魂，還需要根據(jù)主鍵再次到數(shù)據(jù)塊里獲取數(shù)據(jù)。
常見SQL優(yōu)化
主鍵優(yōu)化
沒有特別的需要的話盡量使用一個與業(yè)務(wù)無關(guān)的自增字段作為主鍵炮温。
原因：1.占用空間谢鹇觥；2.插入搜索快柒啤；3.檢索搜索快
優(yōu)化insert語句
1.如果需要同時對一張表插入很多數(shù)據(jù)時倦挂，盡量使用多個值表的insert語句。
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語句
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都有對應(yīng)的索引，而第二條語句中的company沒有對應(yīng)的索引拳话。所以第一條語句做到了索引覆蓋笛丙，查詢效率更優(yōu)。
MySQL5.x分組后會進(jìn)行排序假颇，而8則不會。要禁用排序可以用 order by null
優(yōu)化嵌套查詢
MySQL 4.1版本之后骨稿，開始支持SQL的子查詢笨鸡。這個技術(shù)可以使用SELECT語句來創(chuàng)建一個單列的查詢結(jié)果， 然后把這個結(jié)果作為過濾條件用在另一個查詢中坦冠。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作形耗，同時也可以避免事務(wù)或者表鎖死，并且寫起來也很容易辙浑。但是激涤，有些情況下，子查詢是可以被更高效的連接(JOIN) 替代判呕。
示例倦踢，查找有角色的所有的用戶信息（子查詢）:
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)化分頁
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)
分頁查詢很慢送滞，可以進(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)
