在開始部落格之前,還是同樣的給一個大概的目錄結構,實則即為一般MySQL的優化步驟
1、檢視SQL的執行頻率—————使用show status命令
2、定位哪些需要優化的SQL————通過慢查詢記錄+show processlist命令檢視當前執行緒
3、分析為什麼SQL執行效率低————使用explain/desc命令分析
- 相關列簡單解釋:type、table、select_type…
4、對症下藥採取優化措施———–舉例採取index進行優化
- 如何使用索引?
- 使用索引應該注意的事項
- 檢視索引使用情況
主要參考資料:《深入淺出MySQL》,https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
一、檢視SQL執行頻率
使用show [session|gobal] status命令瞭解SQL執行頻率、執行緒快取內的執行緒的數量、當前開啟的連線的數量、獲得的表的鎖的次數等。
比如執行show status like `Com_%`檢視每個語句執行的次數即頻率,其中Com_xxx中xxx表示就是語句,比如Com_select:執行select操作的次數。
1 mysql> use test; 2 Database changed 3 mysql> show status like `Com_%`; 4 +-----------------------------+-------+ 5 | Variable_name | Value | 6 +-----------------------------+-------+ 7 | Com_admin_commands | 0 | 8 | Com_assign_to_keycache | 0 | 9 | Com_alter_db | 0 | 10 | Com_alter_db_upgrade | 0 | 11 | Com_alter_event | 0 | 12 | Com_alter_function | 0 | 13 | Com_alter_instance | 0 | 14 | Com_alter_procedure | 0 | 15 | Com_alter_server | 0 | 16 | Com_alter_table | 0 | 17 | Com_alter_tablespace | 0 | 18 | Com_alter_user | 0 | 19 | Com_analyze | 0 | 20 | Com_begin | 0 | 21 | Com_binlog | 0 | 22 | Com_call_procedure | 0 | 23 | Com_change_db | 2 | 24 | Com_change_master | 0 | 25 | Com_change_repl_filter | 0 | 26 | Com_check | 0 | 27 | Com_checksum | 0 | 28 | Com_commit | 0 | 29 | Com_create_db | 0 | 30 | Com_create_event | 0 | 31 | Com_create_function | 0 | 32 | Com_create_index | 0 |
..............................
比如執行show status like `slow_queries`檢視慢查詢次數(黑人問號??什麼是慢查詢呢?就是通過設定查詢時間閾值long_query_time(0-10s)並開啟開關
當超過這個閾值的查詢都稱之為慢查詢,通常用來劃分執行SQL效率)show_query_log(1=OFF/0=ON),
mysql> show status like `slow_queries`; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set
比如執行show status like `uptime`檢視服務工作時間(即執行時間):
mysql> show status like `uptime`; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 21645 | +---------------+-------+ 1 row in set
比如執行show status like `connections`檢視MySQL連線數:
mysql> show status like `connections`; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 6 | +---------------+-------+ 1 row in set
通過show [session|gobal] status命令很清楚地看到哪些SQL執行效率不如人意,但是具體是怎麼個不如意法,還得繼續往下看,使用EXPLAIN命令分析具體的SQL語句
二、定位效率低的SQL
上面也提到過慢查詢這個概念主要是用來劃分效率低的SQL,但是慢查詢是在整個查詢結束後才記錄的,所以光是靠慢查詢日誌是跟蹤不了效率低的SQL。一般有兩種方式定位效率低的SQL:
1、通過慢查詢日誌檢視效率低的SQL語句,慢查詢日誌是通過show_query_log_file指定儲存路徑的,裡面記錄所有超過long_query_time
的SQL語句(關於日誌的檢視,日後再一步研究學習),但是需要慢查詢日誌的產生是在查詢結束後才有的。
2、通過show processlist命令檢視當前MySQL進行的執行緒,可以看到執行緒的狀態資訊
mysql> show processlist; +----+------+-----------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+------------------+ | 2 | root | localhost:58377 | NULL | Sleep | 2091 | | NULL | | 3 | root | localhost:58382 | test | Sleep | 2083 | | NULL | | 4 | root | localhost:58386 | test | Sleep | 2082 | | NULL | | 5 | root | localhost:59092 | test | Query | 0 | starting | show processlist | +----+------+-----------------+------+---------+------+----------+------------------+ 4 rows in set
其中主要的是state欄位,表示當前SQL語句執行緒的狀態,如Sleeping 表示正在等待客戶端傳送新請求,Sending data把查詢到的data結果傳送給客戶端等等,具體請看https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
三、 檢視分析效率低的SQL
MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以後就可以EXPLAIN SELECT,UPDATE,DELETE,現在我們先建立一個user_table的表,之後分析select* from user where name=“語句
mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB; Query OK, 0 rows affected
之後插入三條資料:
mysql> insert into user values(1,`Zhangsan`,replace(UUID(),`-`,``)),(2,`Lisi`,replace(UUID(),`-`,``)),(3,`Wangwu`,replace(UUID(),`-`,``)); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> select* from user; +----+----------+----------------------------------+ | id | name | password | +----+----------+----------------------------------+ | 1 | Zhangsan | 2d7284808e5111e8af74201a060059ce | | 2 | Lisi | 2d73641c8e5111e8af74201a060059ce | | 3 | Wangwu | 2d73670c8e5111e8af74201a060059ce | +----+----------+----------------------------------+ 3 rows in set
下面以分析select*from user where name=`Lisi`語句為例:
mysql> explain select*from user where name=`Lisi`; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
下面講解select_type等常見列的含義的:
(1)select_type:表示SELECT的型別,主要有:
- SIMPLE:簡單表,沒有表連線或者子查詢
- PRIMARY:主查詢,即最外城的查詢
- UNION:UNION中的第二個或者後面的語句
- SUBQUERY:子查詢中的第一個SELECT
(2)table:結果輸出的表
(3)type:表示表的連線型別,效能由好到差為:
- system:常量表
- const:單表中最多有一行匹配,比如primary key,unique index
- eq_ref:多表連線中使用primary key,unique index
- ref:使用普通索引
- ref_or_null:與ref類似,但是包含了NULL查詢
- index_merge:索引合併優化
- unique_subquery:in後面是一個查詢主鍵欄位的子查詢
- index_subquery:in後面是非唯一索引欄位的子查詢
- range:單表中範圍檢視,使用like模糊查詢
- index:對於後面每一行都通過查詢索引得到資料
- all:表示全表查詢
(3)possible_key:查詢時可能使用的索引
(4)key:表示實際使用的索引
(5)key_len:索引欄位的長度
(6)rows:查詢時實際掃描的行數
(7)Extra:執行情況的說明和描述
(8)partitions:分割槽數目
(9)filtered:查詢過濾的表佔的百分比,比如這裡查詢的記錄是name=Lisi的記錄,佔三條記錄的33.3%
四、 關於索引的優化
1、使用索引優化的舉例
上個例子我們看到到執行explain select*from user where name=`Lisi`,掃描了3行(全部行數)使用了全表搜尋all。如果實際業務中name是經常用到查詢的欄位(是指經常跟在where後的欄位,不是select後的欄位)並且資料量很大的情況呢?這時候就需要索引了(索引經常用到where後面的欄位比select後面的欄位效果更好,或者說就是要使用在where後面的欄位上)
增加name字首索引(這裡只是舉例,並沒有選擇最合適的字首):
mysql> create index index_name on user(name(2)); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0
執行explain分析
mysql> explain select*from user where name = `Lisi`; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | index_name | index_name | 9 | const | 1 | 100 | Using where | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set
可以看到type變為ref、rows降為1(實際上只要使用了索引都是1),filtered過濾百分比為100%,實際用到的索引為index_name。如果資料量很大的話使用索引就是很好的優化措施,對於如何選擇索引,什麼時候用索引,我做出瞭如下總結:
2、如何高效使用索引?
(1) 建立多列索引時,只要查詢條件中用到最左邊的列,索引一般都會被用到
我們建立一張沒有索引的表user_1:
mysql> show create table user_1; +--------+--------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------+ | user_1 | CREATE TABLE `user_1` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set
之後同樣插入資料:
mysql> select *from user_1; +----+----------+ | id | name | +----+----------+ | 1 | Zhangsan | | 2 | Lisi | +----+----------+ 2 rows in set
建立多列索引index_id_name
mysql> create index index_id_name on user_1(id,name); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0
實驗查詢explain分析name與id
mysql> explain select * from user_1 where id=1; +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user_1 | NULL | ref | index_id_name | index_id_name | 5 | const | 1 | 100 | Using index | +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ 1 row in set mysql> explain select * from user_1 where name=`Lisi`; +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | user_1 | NULL | index | NULL | index_id_name | 38 | NULL | 2 | 50 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ 1 row in set
可以看到使用最左列id的時候,rows為1,並且Extra明確使用了index,key的值為id_name_index,type的值為ref,而where不用到id,而是name的話,rows的值為2。filtered為50%,雖然key是index_id_name,但是表明是索引(個人理解,應該不太準確)
(2) 使用like的查詢,只有%不是第一個字元並且%後面是常量的情況下,索引才可能會被使用。
執行explain select *from user where name like ‘%Li’後type為ALL且key的值為NULL,執行explain select *from user where name like ‘Li%’後key值不為空為index_name。
mysql> explain select*from user where name like `%Li`; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set mysql> explain select*from user where name like `Li%`; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | range | index_name | index_name | 9 | NULL | 1 | 100 | Using where | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set
(3) 如果對打的文字進行搜尋,使用全文索引而不是用like ‘%…%’(只有MyISAM支援全文索引)。
(4) 如果列名是索引,使用column_name is null將使用索引。
mysql> explain select*from user where name is null; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | index_name | index_name | 9 | const | 1 | 100 | Using where | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set mysql> explain select*from user where password is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
3、哪些情況下即使有索引也用不到?
(1) MySQL使用MEMORY/HEAP引擎(使用的HASH索引),並且WHERE條件中不會使用”=”,in等進行索引列,那麼不會用到索引(這是關於引擎部分特點,之後會介紹)。
(2) 用OR分隔開的條件,如果OR前面的條件中的列有索引,而後面的列沒有索引,那麼涉及到的列索引不會被使用。
執行命令show index from user可以看出password欄位並沒有使用任何索引,而id使用了兩個索引,但是where id=1 or password=`2d7284808e5111e8af74201a060059ce` 導致沒有使用id列的primary索引與id_name_index索引
mysql> show index from user; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name | 1 | name | A | 3 | 2 | NULL | YES | BTREE | | | | user | 1 | id_name_index | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | id_name_index | 2 | name | A | 3 | NULL | NULL | YES | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set mysql> explain select*from user where id=1 or password=`2d7284808e5111e8af74201a060059ce`; +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | PRIMARY,id_name_index | NULL | NULL | NULL | 3 | 55.56 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set
(3) 不是用到複合索引中的第一列即最左邊的列的話,索引就不起作用(上面已經介紹)。
(4) 如果like是以%開頭的(上面已經介紹)
(5) 如果列型別是字串,那麼where條件中字元常量值不用’’引號引起來的話,那就不會失去索引效果,這是因為MySQL會把輸入的常量值進行轉換再使用索引。
select * from user_1 where name =250,其中name的索引為name_index,並且是varchar字串型別,但是並沒有將250用引號變成’250’,那麼explain之後的ref仍然為NULL,rows為3
mysql> show index from user_1; +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user_1 | 1 | index_id_name | 1 | id | A | 2 | NULL | NULL | YES | BTREE | | | | user_1 | 1 | index_id_name | 2 | name | A | 2 | NULL | NULL | YES | BTREE | | | | user_1 | 1 | name_index | 1 | name | A | 3 | 5 | NULL | YES | BTREE | | | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set mysql> explain select*from user_1 where name=250; +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | user_1 | NULL | index | name_index | index_id_name | 38 | NULL | 3 | 33.33 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ 1 row in set mysql> explain select*from user_1 where name=`250`; +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user_1 | NULL | ref | name_index | name_index | 18 | const | 1 | 100 | Using where | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set
4、檢視索引的使用情況
執行show status like ‘Handler_read%’可以看到一個值Handler_read_key,它代表一行被索引值讀的次數,如果值很低說明增加索引得到的效能改善不高,因為索引並不經常使用。
mysql> show status like `Handler_read%` ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 3 | | Handler_read_key | 5 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 20 | +-----------------------+-------+ 7 rows in set
(1)Handler_read_first:索引中第一條被讀的次數。如果較高,它表示伺服器正執行大量全索引掃描;
(2)Handler_read_key:如果索引正在工作,這個值代表一個行被索引值讀的次數,如果值越低,表示索引得到的效能改善不高,因為索引不經常使用。
(3)Handler_read_next :按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列,該值增加。
(4)Handler_read_prev:按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY … DESC。
(5)Handler_read_rnd :根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連線沒有正確使用鍵。這個值較高,意味著執行效率低,應該建立索引來補救。
(6)Handler_read_rnd_next:在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描,該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。
注:以上6點來自於網路總結,其中比較重要的兩個引數是Handler_read_key與Handler_read_rnd_next。