系列專欄：MySQL數(shù)據(jù)庫(kù)進(jìn)階

前言

在看此篇前，建議先閱讀MySQL索引，對(duì)索引有個(gè)基本了解：MySQL數(shù)據(jù)庫(kù)進(jìn)階-索引-CSDN博客

在進(jìn)行SQL優(yōu)化前，我們必須先了解SQL查詢的性能分析，為什么這條SQL慢，慢在哪里？這樣我們才能更好的進(jìn)行SQL優(yōu)化。

性能分析

查看執(zhí)行頻次

查看當(dāng)前數(shù)據(jù)庫(kù)的 INSERT, UPDATE, DELETE, SELECT 訪問(wèn)頻次：

SHOW GLOBAL STATUS LIKE 'Com_______';

或者 SHOW SESSION STATUS LIKE 'Com_______';

例：show global status like 'Com_______'

慢查詢?nèi)罩?/h4>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有SQL語(yǔ)句的日志。

MySQL的慢查詢?nèi)罩灸J(rèn)沒(méi)有開(kāi)啟，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息： # 開(kāi)啟慢查詢?nèi)罩鹃_(kāi)關(guān) slow_query_log=1

# 設(shè)置慢查詢?nèi)罩镜臅r(shí)間為2秒，SQL語(yǔ)句執(zhí)行時(shí)間超過(guò)2秒，就會(huì)視為慢查詢，記錄慢查詢?nèi)罩?long_query_time=2 更改后記得重啟MySQL服務(wù)，日志文件位置：/var/lib/mysql/localhost-slow.log

查看慢查詢?nèi)罩鹃_(kāi)關(guān)狀態(tài)：

show variables like 'slow_query_log';

profile

show profile 能在做SQL優(yōu)化時(shí)幫我們了解時(shí)間都耗費(fèi)在哪里，通過(guò) have_profiling 參數(shù)，能看到當(dāng)前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling;

profiling 默認(rèn)關(guān)閉，可以通過(guò)set語(yǔ)句在session/global級(jí)別開(kāi)啟 profiling：

SET profiling = 1;

查看所有語(yǔ)句的耗時(shí)：

show profiles;

查看指定query_id的SQL語(yǔ)句各個(gè)階段的耗時(shí)：

show profile for query query_id;

查看指定query_id的SQL語(yǔ)句CPU的使用情況

show profile cpu for query query_id;

explain

EXPLAIN 或者 DESC 命令獲取 MySQL 如何執(zhí)行 SELECT 語(yǔ)句的信息，包括在 SELECT 語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序。 語(yǔ)法： # 直接在select語(yǔ)句之前加上關(guān)鍵字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 HWERE 條件;

EXPLAIN 各字段含義：

• id：select 查詢的序列號(hào)，表示查詢中執(zhí)行 select 子句或者操作表的順序（id相同，執(zhí)行順序從上到下；id不同，值越大越先執(zhí)行）

• select_type：表示 SELECT 的類型，常見(jiàn)取值有 SIMPLE（簡(jiǎn)單表，即不適用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的第二個(gè)或者后面的查詢語(yǔ)句）、SUBQUERY（SELECT/WHERE之后包含了子查詢）等

• type：表示連接類型，性能由好到差的連接類型為 NULL、system、const、eq_ref、ref、range、index、all

• possible_key：可能應(yīng)用在這張表上的索引，一個(gè)或多個(gè)

• Key：實(shí)際使用的索引，如果為 NULL，則沒(méi)有使用索引

• Key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下，長(zhǎng)度越短越好

• rows：MySQL認(rèn)為必須要執(zhí)行的行數(shù)，在InnoDB引擎的表中，是一個(gè)估計(jì)值，可能并不總是準(zhǔn)確的

• filtered：表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered的值越大越好

SQL 優(yōu)化

插入數(shù)據(jù)

普通插入：

1. 采用批量插入（一次插入的數(shù)據(jù)不建議超過(guò)1000條）

2. 手動(dòng)提交事務(wù)

3. 主鍵順序插入

大批量插入： 如果一次性需要插入大批量數(shù)據(jù)，使用insert語(yǔ)句插入性能較低，此時(shí)可以使用MySQL數(shù)據(jù)庫(kù)提供的load指令插入。

# 客戶端連接服務(wù)端時(shí)，加上參數(shù) --local-infile（這一行在bash/cmd界面輸入）
mysql --local-infile -u root -p
# 設(shè)置全局參數(shù)local_infile為1，開(kāi)啟從本地加載文件導(dǎo)入數(shù)據(jù)的開(kāi)關(guān)
set global local_infile = 1;
select @@local_infile;
# 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主鍵優(yōu)化

數(shù)據(jù)組織方式：在InnoDB存儲(chǔ)引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲(chǔ)方式的表稱為索引組織表（Index organized table, IOT）

頁(yè)分裂：頁(yè)可以為空，也可以填充一般，也可以填充100%，每個(gè)頁(yè)包含了2-N行數(shù)據(jù)（如果一行數(shù)據(jù)過(guò)大，會(huì)行溢出），根據(jù)主鍵排列。

頁(yè)合并：當(dāng)刪除一行記錄時(shí)，實(shí)際上記錄并沒(méi)有被物理刪除，只是記錄被標(biāo)記（flaged）為刪除并且它的空間變得允許被其他記錄聲明使用。當(dāng)頁(yè)中刪除的記錄到達(dá) MERGE_THRESHOLD（默認(rèn)為頁(yè)的50%），InnoDB會(huì)開(kāi)始尋找最靠近的頁(yè)（前后）看看是否可以將這兩個(gè)頁(yè)合并以優(yōu)化空間使用。

MERGE_THRESHOLD：合并頁(yè)的閾值，可以自己設(shè)置，在創(chuàng)建表或創(chuàng)建索引時(shí)指定

主鍵設(shè)計(jì)原則：

• 滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長(zhǎng)度

• 插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵

• 盡量不要使用 UUID 做主鍵或者是其他的自然主鍵，如身份證號(hào)

• 業(yè)務(wù)操作時(shí)，避免對(duì)主鍵的修改

order by優(yōu)化

????????Using filesort：通過(guò)表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作，所有不是通過(guò)索引直接返回排序結(jié)果的排序都叫 FileSort 排序

????????Using index：通過(guò)有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，則都會(huì)走索引，但是如果一個(gè)字段升序排序，另一個(gè)字段降序排序，則不會(huì)走索引，explain的extra信息顯示的是Using index, Using filesort，如果要優(yōu)化掉Using filesort，則需要另外再創(chuàng)建一個(gè)索引，如：

此時(shí)使用如下sql會(huì)全部走索引

select id, age, phone from tb_user order by age asc, phone desc;

總結(jié)

• 根據(jù)排序字段建立合適的索引，多字段排序時(shí)，也遵循最左前綴法則

• 盡量使用覆蓋索引

• 多字段排序，一個(gè)升序一個(gè)降序，此時(shí)需要注意聯(lián)合索引在創(chuàng)建時(shí)的規(guī)則（ASC/DESC）

• 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時(shí)，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size（默認(rèn)256k）

group by優(yōu)化

• 在分組操作時(shí)，可以通過(guò)索引來(lái)提高效率

• 分組操作時(shí)，索引的使用也是滿足最左前綴法則的

如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則

limit優(yōu)化

常見(jiàn)的問(wèn)題如limit 2000000, 10，此時(shí)需要 MySQL 排序前2000000條記錄，但僅僅返回2000000 - 2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大。 優(yōu)化方案：一般分頁(yè)查詢時(shí)，通過(guò)創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過(guò)覆蓋索引加子查詢形式進(jìn)行優(yōu)化

例如：

-- 此語(yǔ)句耗時(shí)很長(zhǎng)
select * from tb_sku limit 9000000, 10;
-- 通過(guò)覆蓋索引加快速度，直接通過(guò)主鍵索引進(jìn)行排序及查詢
select id from tb_sku order by id limit 9000000, 10;
-- 下面的語(yǔ)句是錯(cuò)誤的，因?yàn)?MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通過(guò)連表查詢即可實(shí)現(xiàn)第一句的效果，并且能達(dá)到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count優(yōu)化

MyISAM 引擎把一個(gè)表的總行數(shù)存在了磁盤(pán)上，因此執(zhí)行 count(*) 的時(shí)候會(huì)直接返回這個(gè)數(shù)，效率很高（前提是不適用where）； InnoDB 在執(zhí)行 count(*) 時(shí)，需要把數(shù)據(jù)一行一行地從引擎里面讀出來(lái)，然后累計(jì)計(jì)數(shù)。 優(yōu)化方案：自己計(jì)數(shù)，如創(chuàng)建key-value表存儲(chǔ)在內(nèi)存或硬盤(pán)，或者是用redis

count的幾種用法：

• 如果count函數(shù)的參數(shù)（count里面寫(xiě)的那個(gè)字段）不是NULL（字段值不為NULL），累計(jì)值就加一，最后返回累計(jì)值

• 用法：count(*)、count(主鍵)、count(字段)、count(1)

• count(主鍵)跟count(*)一樣，因?yàn)橹麈I不能為空；

• count(字段)只計(jì)算字段值不為NULL的行；count(1)引擎會(huì)為每行添加一個(gè)1，然后就count這個(gè)1，返回結(jié)果也跟count(*)一樣；count(null)返回0

各種用法的性能：

• count(主鍵)：InnoDB引擎會(huì)遍歷整張表，把每行的主鍵id值都取出來(lái)，返回給服務(wù)層，服務(wù)層拿到主鍵后，直接按行進(jìn)行累加（主鍵不可能為空）

• count(字段)：沒(méi)有not null約束的話，InnoDB引擎會(huì)遍歷整張表把每一行的字段值都取出來(lái)，返回給服務(wù)層，服務(wù)層判斷是否為null，不為null，計(jì)數(shù)累加；有not null約束的話，InnoDB引擎會(huì)遍歷整張表把每一行的字段值都取出來(lái)，返回給服務(wù)層，直接按行進(jìn)行累加

• count(1)：InnoDB 引擎遍歷整張表，但不取值。服務(wù)層對(duì)于返回的每一層，放一個(gè)數(shù)字 1 進(jìn)去，直接按行進(jìn)行累加

• count(*)：InnoDB 引擎并不會(huì)把全部字段取出來(lái)，而是專門(mén)做了優(yōu)化，不取值，服務(wù)層直接按行進(jìn)行累加

按效率排序：count(字段) < count(主鍵) < count(1) < count(*)，所以盡量使用 count(*)

update優(yōu)化（避免行鎖升級(jí)為表鎖）

前面講的都是基于查詢的，基于update最常見(jiàn)的問(wèn)題就是鎖升級(jí)，也是update最重要的點(diǎn)。

InnoDB 的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，并且該索引不能失效，否則會(huì)從行鎖升級(jí)為表鎖。

如以下兩條語(yǔ)句： update student set no = '123' where id = 1;，這句由于id有主鍵索引，所

以只會(huì)鎖這一行；

update student set no = '123' where name = 'test';這句由于name沒(méi)有索引，所以會(huì)把整張表都鎖住進(jìn)行數(shù)據(jù)更新，解決方法是給name字段添加索引。

如果本文對(duì)你有幫助，記得點(diǎn)贊關(guān)注，你的支持是我最大的動(dòng)力！

如果您有任何疑問(wèn)或建議，歡迎在評(píng)論區(qū)留言，一起探討交流！