Mysql 進(jìn)行SQL調(diào)優(yōu)的方法
更新時(shí)間：2025年11月19日 09:28:21 作者：九轉(zhuǎn)蒼翎
文章詳細(xì)介紹了MySQL優(yōu)化索引的多種方法,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧
1.優(yōu)化索引
1.1 初始化數(shù)據(jù)
delimiter //
create procedure p_init_index_data ()
begin
declare id bigint default 100000;
declare age tinyint default 18;
declare gender bigint default 1;
declare class_id bigint default 1;
declare count int default 0;
drop table if exists index_demo;
create table index_demo (
id bigint auto_increment,
sn varchar(10) not null,
name varchar(20) not null,
mail varchar(20),
age tinyint(1),
gender tinyint(1),
password varchar(36) not null,
class_id bigint not null,
create_time datetime not null,
update_time datetime not null,
primary key (id),
index (class_id)
);
insert into index_demo values (100000, '100000', 'testUser',
'100000@qq.com', 18, 1, uuid(), 1, now(), now());
while count < 1000000 do
set id := id + 1;
if count % 10 = 0 then
set age := age + 1;
end if;
if age > 50 then
set age := 16;
end if;
if count % 3 = 0 then
set gender := 0;
else
set gender := 1;
end if;
set class_id := class_id + 1;
if class_id > 10 then
set class_id := 1;
end if;
insert into index_demo values (id, id, concat('user_',id),
concat(id,'@qq.com'), age, gender, uuid(), class_id, now(), now());
set count := count + 1;
end while;
end //
delimiter ;
call p_init_index_data();1.2 explain
作用：用于分析查詢執(zhí)行計(jì)劃的關(guān)鍵字。幫助開發(fā)者理解數(shù)據(jù)庫引擎如何處理特定的SQL查詢，從而優(yōu)化查詢性能(explain本身不會(huì)執(zhí)行查詢，僅顯示執(zhí)行計(jì)劃)。explain輸出通常包含以下關(guān)鍵信息：
- id：表示查詢中執(zhí)行順序的標(biāo)識(shí)符。如果查詢包含子查詢或聯(lián)合查詢，id 會(huì)反映執(zhí)行的層級(jí)關(guān)系。id 值越大，執(zhí)行優(yōu)先級(jí)越高
- select_type： 描述查詢的類型，最常見的是simple(簡(jiǎn)單查詢，不包含子查詢或聯(lián)合查詢)
- table：查詢涉及的表名
- partitions：表示查詢涉及到的分區(qū)表的具體分區(qū)名稱。如果沒有分區(qū)，該字段為null
- 分區(qū)表：是一種數(shù)據(jù)庫優(yōu)化技術(shù)，將邏輯上的單一表按特定規(guī)則劃分為多個(gè)物理存儲(chǔ)的獨(dú)立部分(稱為分區(qū))，但對(duì)用戶而言仍表現(xiàn)為一張完整的表
- type：連接類型(性能關(guān)鍵)
- possible_keys：可能使用的索引
- key：實(shí)際選擇的索引
- key_len：索引使用的字節(jié)數(shù)
- ref：記錄在查詢過程中哪些列或常量與key列中指定的索引進(jìn)??較
- const：與常量值比較，例如where id = 1
- null：未使用索引
- db.table.column：多表連接時(shí)，顯示另一個(gè)表的列名
- func：使用了函數(shù)或表達(dá)式，例如where upper(name) = ‘ABC’）
- rows：預(yù)估需要檢查的行數(shù)(非精確值)，值越小越好
- filtered：表示條件過濾后剩余行數(shù)的百分比，可使用row*filtered估算結(jié)果集行數(shù)
- extra：額外信息
1.3 type列
作用：顯示MySQL決定使用哪種方式來查找表中的行，是執(zhí)行計(jì)劃中非常重要的指標(biāo)。其查詢的性能優(yōu)劣，從最優(yōu)到最差依次為：system > const > eq_ref > ref > range > index > ALL
1.3.1 system
表中只有一行數(shù)據(jù)，不用掃描，直接返回結(jié)果集，僅使用
myisam引擎時(shí)出現(xiàn)
create table test_system_myisam(id int primary key,name varchar(128)) engine myisam; insert into test_system_myisam values (1,'張三'); explain select * from test_system_myisam; +----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | test_system_myisam | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+--------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1.3.2 const
當(dāng)查詢中的條件通過
主鍵索引或唯?索引與常量進(jìn)行比較時(shí)，結(jié)果最多有?個(gè)匹配的行，這種類型查詢性能極?，且只會(huì)返回一行數(shù)據(jù)
-- 主鍵索引 explain select * from index_demo where id = 500000; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ -- 唯一索引：為sn列添加唯一索引 alter table index_demo add unique unique_sn(sn); explain select * from index_demo where sn = '500000'; +----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | const | unique_sn | unique_sn | 42 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
1.3.3 eq_ref
在聯(lián)合查詢中，表連接的條件是
主鍵索引或唯一索引時(shí)出現(xiàn)
create table test_eq_ref (id int primary key,sn varchar(10) unique); insert into test_eq_ref values (1,'1'),(2,'2'),(3,'3'); -- 主鍵索引 explain select * from index_demo,test_eq_ref where index_demo.id = test_eq_ref.id; +----+-------------+-------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+ | 1 | SIMPLE | test_eq_ref | NULL | index | PRIMARY | sn | 43 | NULL | 3 | 100.00 | Using index | | 1 | SIMPLE | index_demo | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql_advance.test_eq_ref.id | 1 | 100.00 | Using where | +----+-------------+-------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+ -- 唯一索引 explain select * from index_demo,test_eq_ref where index_demo.sn = test_eq_ref.sn; +----+-------------+-------------+------------+--------+---------------+-----------+---------+------------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+---------------+-----------+---------+------------------------------+------+----------+--------------------------+ | 1 | SIMPLE | test_eq_ref | NULL | index | sn | sn | 43 | NULL | 3 | 100.00 | Using where; Using index | | 1 | SIMPLE | index_demo | NULL | eq_ref | unique_sn | unique_sn | 42 | mysql_advance.test_eq_ref.sn | 1 | 100.00 | NULL | +----+-------------+-------------+------------+--------+---------------+-----------+---------+------------------------------+------+----------+--------------------------+
1.3.4 ref
使用
普通索引查詢，可能返回多行記錄
explain select * from index_demo where class_id = 1; +----+-------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | ref | class_id | class_id | 8 | const | 209914 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-------
1.3.5 range
索引范圍掃描，常見于
<>, >, >=, <, <=, is null, between, in等操作符
explain select * from index_demo where id > 500000; +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 476754 | 100.00 | Using where | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1.3.6 index
全索引掃描，只遍歷索引樹而不需要回表查數(shù)據(jù)
-- 遍歷主鍵索引樹 explain select id from index_demo; +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | index | NULL | class_id | 8 | NULL | 953508 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
1.3.7 ALL
全表掃描，性能最差的情況。當(dāng)表數(shù)據(jù)量大時(shí)需特別關(guān)注這種類型，應(yīng)考慮添加合適的索引
explain select * from index_demo; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------+
1.4 extra列
1.4.1 Using where
使用非索引列進(jìn)行全表掃描或者使用索引列發(fā)生回表查詢
-- 非索引列進(jìn)行全表掃描 explain select * from index_demo where class_id = 1; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 10.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ -- 索引列發(fā)生回表查詢 explain select * from index_demo where id > 500000; +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 476754 | 100.00 | Using where | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1.4.2 Using index
使用索引列發(fā)生覆蓋索引
explain select id from index_demo; +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | index | NULL | class_id | 8 | NULL | 953508 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
1.4.3 Using filesort
需要額外排序操作，通常出現(xiàn)在
order by未使用索引時(shí)。優(yōu)化方案是為排序字段添加索引
explain select * from index_demo where id < 200000 order by age limit 10; +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | index_demo | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 203406 | 100.00 | Using where; Using filesort | +----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------------+ -- 添加索引 alter table index_demo add index index_age(age); explain select * from index_demo where id < 200000 order by age limit 10; +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | index | PRIMARY | index_age | 2 | NULL | 46 | 21.33 | Using where | +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1.4.4 Using temporary
當(dāng)使用非索引列進(jìn)行分組時(shí)，會(huì)用臨時(shí)表進(jìn)行排序，優(yōu)化時(shí)可以考慮為分組的列加索引
explain select avg(age) from index_demo group by gender; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ -- 添加索引 alter table index_demo add index index_gender(gender); explain select avg(age) from index_demo group by gender; +----+-------------+------------+------------+-------+---------------+--------------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+--------------+---------+------+--------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | index | index_gender | index_gender | 2 | NULL | 953508 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+--------------+---------+------+--------+----------+-------+
1.5 索引覆蓋
定義：當(dāng)查詢的列全部包含在索引樹中時(shí)，查詢語句只需要通過索引樹即可獲取所需數(shù)據(jù)，無需回表操作
2.最左匹配原則
定義：是MySQL中聯(lián)合索引使用的核心規(guī)則，指查詢條件必須從聯(lián)合索引的最左側(cè)字段開始，并按索引定義的字段順序依次匹配，才能充分利用索引。若跳過最左字段或中間字段，索引可能失效
3.MySQL優(yōu)化器
作用：MySQL內(nèi)置優(yōu)化器是數(shù)據(jù)庫系統(tǒng)的核心組件之一，負(fù)責(zé)在執(zhí)行SQL查詢時(shí)生成高效的執(zhí)行計(jì)劃。其主要作用包括：
- 查詢重寫：優(yōu)化器會(huì)對(duì)原始SQL語句進(jìn)行語法分析和重寫
- 成本估算與分析：優(yōu)化器通過成本模型(Cost Model，一種量化評(píng)估機(jī)制)評(píng)估查詢執(zhí)行計(jì)劃的性能
- 執(zhí)行計(jì)劃選擇：優(yōu)化器比較多種可能的執(zhí)行方案，選擇成本最低的計(jì)劃
- 索引優(yōu)化策略：優(yōu)化器決定是否使用索引以及使用哪些索引
以下是MySQL優(yōu)化器對(duì)select語句的重要優(yōu)化機(jī)制
3.1 范圍優(yōu)化
3.1.1 單列索引范圍訪問
當(dāng)滿足以下范圍定義時(shí)，MySQL優(yōu)化器才可能使用單列索引進(jìn)行范圍訪問
- BTREE和HASH索引在使用
=、<=>、in()操作符時(shí)，索引部分與常量值的比較會(huì)被視為范圍條件 - BTREE索引在使用
>、<、>=、<=、between、<>(!=)操作符時(shí)，索引部分與常量值的比較會(huì)被視為范圍條件 - like的參數(shù)是一個(gè)不以通配符開頭的常量字符串，例如
like'abc%' - 無論索引類型如何，多個(gè)范圍條件通過
or或and組合時(shí)，會(huì)形成一個(gè)復(fù)合范圍條件
示例：
-- key為索引列，nokey為非索引列 select * from table1 where (key < 'abc' and (key like 'abcd%' or key like '%c')) or (key < 'bar' and nokey = 4) or (key < 'uux' and key > 'z');
優(yōu)化過程如下：
優(yōu)化后SQL：
select * from table1 where (key < 'abc' and (key like 'abcd%' or true)) or (key < 'bar' and true) or false; -- 化簡(jiǎn) -- 1.(key like 'abcd%' or true)始終為true select * from table1 where (key < 'abc' and true) or (key < 'bar' and true) -- 2.合并范圍 select * from table1 where key < 'bar'
3.1.2 多列索引范圍訪問
當(dāng)滿足以下情況時(shí)，MySQL優(yōu)化器才可能使用多列索引進(jìn)行范圍訪問(此處多列索引指聯(lián)合索引)
1.索引前導(dǎo)列：前導(dǎo)列是多列索引中的第一列。優(yōu)化器使用多列索引時(shí)，必須包含前導(dǎo)列才能生效。例如，索引(a, b, c)在以下查詢中有效：
where a = 1 and b = 2 and c = 3 -- (完全匹配) where a = 1 and b > 2 --(部分匹配，范圍查詢) where a = 1 --(僅前導(dǎo)列) -- 在以下查詢中失效 where b = 2 and c = 3 -- (缺少前導(dǎo)列) where a > 1 and b = 2 -- (范圍查詢后無法使用后續(xù)列)
2.HASH索引：當(dāng)N個(gè)索引列與N個(gè)常量值進(jìn)行=、<=>、is null操作時(shí)生效。例如：
-- key1 ~ keyN 均是索引列，const1 ~ constN均是常量值 -- compare是(=、<=>、is null)運(yùn)算符之一 key1 compare const1 and key2 compare const2 and key3 compare const3 and ... keyN compare constN
3.BTREE索引：- 當(dāng)前導(dǎo)索引使用
=、<=>、is null操作符時(shí)，優(yōu)化器使用索引確定單個(gè)區(qū)間 - 當(dāng)某一個(gè)條件開始使用
>、<、>=、<=、between、<>(!=)、like操作符時(shí)，當(dāng)前條件使用索引，后續(xù)條件不再使用索引。BTREE結(jié)構(gòu)對(duì)上述范圍查詢支持有限。當(dāng)查詢條件中包含范圍操作符時(shí)，數(shù)據(jù)庫只能利用索引快速定位到范圍的起始點(diǎn)，但后續(xù)數(shù)據(jù)需要線性掃描，導(dǎo)致索引失效
- 當(dāng)前導(dǎo)索引使用
-- key1 ~ keyN 均是索引列
key1 = 'abc' and key2 >= 10 and key3 = 'MySQL'
-- 上述SQL可以表示為
('abc',10,-∞) < (key1,key2,key3) < ('abc',+∞,+∞)3.2 索引合并優(yōu)化
在SQL查詢中，當(dāng)where子句包含多個(gè)條件時(shí)，數(shù)據(jù)庫優(yōu)化器會(huì)根據(jù)索引選擇最有效的執(zhí)行計(jì)劃。通常只能利用一個(gè)索引進(jìn)行初步過濾，其他條件會(huì)在回表后進(jìn)行檢查
- 1.索引選擇與回表機(jī)制：數(shù)據(jù)庫優(yōu)化器會(huì)評(píng)估各個(gè)索引的選擇性(過濾能力)，選擇一個(gè)最有利的索引進(jìn)行初步數(shù)據(jù)定位
- 2.回表后的處理流程：通過第一個(gè)索引定位到數(shù)據(jù)后，需要根據(jù)主鍵值回表獲取完整行數(shù)據(jù)。此時(shí)會(huì)對(duì)其他where條件進(jìn)行逐行檢查
當(dāng)回表的記錄數(shù)很多時(shí)，需要進(jìn)行大量的隨機(jī)IO，這可能導(dǎo)致查詢性能下降。MySQL在5.x版本后推出索引合并(Index Merge)來解決該問題
索引合并(Index Merge)：對(duì)?個(gè)表同時(shí)使用多個(gè)索引進(jìn)行條件掃描時(shí)，將滿?條件的多個(gè)主鍵集合取交集或并集后再進(jìn)行回表，可以提升查詢效率(索引合并僅合并單個(gè)表中的索引掃描，而不能跨表掃描。合并的結(jié)果是多個(gè)索引掃描的并集、交集或交集的并集)
3.3 索引下推優(yōu)化
定義：在傳統(tǒng)查詢流程中，存儲(chǔ)引擎僅根據(jù)索引篩選數(shù)據(jù)，然后將滿足條件的數(shù)據(jù)返回給服務(wù)層進(jìn)行進(jìn)一步過濾。索引下推允許將部分過濾條件下推到存儲(chǔ)引擎層執(zhí)行，減少回表操作和無效數(shù)據(jù)傳輸
3.4 order by優(yōu)化
3.4.1 使用索引進(jìn)行order by
在為表中某列創(chuàng)建索引時(shí)，默認(rèn)是有序的，如果使用索引對(duì)查詢結(jié)果進(jìn)行排序，就可以避免額外的資源消耗。以下是使用+索引進(jìn)行order by的情況
對(duì)于單列索引key- 使用索引進(jìn)行排序
select * from table order by key;
- key1與常量值進(jìn)行比較，結(jié)果集按照key1排序(可能使用索引排序，取決于MySQL優(yōu)化器的判斷)
select * from table where key < | > order by key asc | desc;
- 使用索引進(jìn)行排序
對(duì)于聯(lián)合索引(key1,key2)- 僅使用order by而未使用where，兩列可以按同一方向排序(都是asc，或者都是desc)，也可以按相反方向排序(?個(gè)asc，?個(gè)desc)
select * from table order by key1,key2;
- key1取常量值，結(jié)果集按照key2排序
select * from table where key1 = const1 order by key2;
- 僅使用order by而未使用where，兩列可以按同一方向排序(都是asc，或者都是desc)，也可以按相反方向排序(?個(gè)asc，?個(gè)desc)
3.4.2 使用filesort進(jìn)行order by
以下情況MySQL不使?索引來處理order by
- 使用不同索引進(jìn)行排序
-- key1和key2是不同的索引 select * from table order by key1,key2;
- 不符合最左原則
select * from table order by key1,key3;
- where的索引與order by的索引不一致
select * from table where key1 = const1 order by key2;
- 對(duì)索引列進(jìn)行運(yùn)算或轉(zhuǎn)化
-- 對(duì)索引取絕對(duì)值 select * from table where abs(key);
?件排序會(huì)用到額外的內(nèi)存空間，可以通過sort_buffer_size來設(shè)置排序內(nèi)存的大小，默認(rèn)大小256KB。如果結(jié)果集太大超出sort_buffer_size內(nèi)存限制，則文件排序會(huì)根據(jù)需要使用臨時(shí)磁盤文件，這樣會(huì)嚴(yán)重影響查詢效率
- 如果explain輸出的extra列包含Using filesort時(shí)，說明查詢語句執(zhí)行了文件排序，強(qiáng)烈建議進(jìn)行優(yōu)化
3.5 group by優(yōu)化
在執(zhí)行g(shù)roup by分組查詢時(shí)，會(huì)先把符合where條件的結(jié)果保存在?個(gè)新創(chuàng)建的臨時(shí)表中，臨時(shí)表中每個(gè)分組字段的所有行都是連續(xù)的，然后再分離每個(gè)組并應(yīng)用聚合函數(shù)(如果存在)。由于索引本身就是連結(jié)的，通過使用索引可以避免創(chuàng)建臨時(shí)表
- 分組時(shí)遵循最左原則
alter table index_demo add index index_mail_age(mail,age); explain select mail,age from index_demo group by mail,age; -- 使用max和min之外的聚合函數(shù)，優(yōu)化器分析性能后決定是否使用索引 +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | index | index_mail_age_classid,index_mail_age | index_mail_age | 85 | NULL | 953508 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+ explain select max(mail),min(age) from index_demo group by mail,age; +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | index | index_mail_age_classid,index_mail_age | index_mail_age | 85 | NULL | 953508 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------------------------------+----------------+---------+------+--------+----------+-------------+
3.6 函數(shù)調(diào)用優(yōu)化
- MySQL函數(shù)分為確定性函數(shù)和非確定性函數(shù)。多次調(diào)用同?個(gè)函數(shù)并傳?相同的參數(shù)，如果返回不同的結(jié)果，那么這個(gè)函數(shù)就是非確定性函數(shù)，比如rand()，uuid()
- 當(dāng)查詢中使用了非確定性函數(shù)則無法使用索引，但將非確定性函數(shù)的值保存在變量中，然后在查詢語句中使用則可以使用索引
-- 確定性函數(shù),使用索引 select * from table where id = pow(1,2); -- 非確定性函數(shù)，不使用索引 select * from table where id = floor(1 + rand() * 49); -- 創(chuàng)建變量接收非確定性函數(shù)的返回值 set @value = floor(1 + rand() * 49); -- 使用索引 select * from table where id = @value;
4.索引失效
定義：通常是由于查詢語句的編寫方式或數(shù)據(jù)特征導(dǎo)致數(shù)據(jù)庫優(yōu)化器無法有效利用索引
不遵循最左原則
alter table index_demo add index index_mail_age_classid(mail,age,class_id); explain select * from index_demo where age = 16; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 10.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
where條件中存在or，且存在列沒有索引
explain select * from index_demo where id = 500000 or age = 16; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | PRIMARY | NULL | NULL | NULL | 953508 | 10.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
聯(lián)合索引第一個(gè)使用范圍查詢的條件之后的列不使用索引
explain select * from index_demo where mail > '123' and age = 16 and class_id = 1; +----+-------------+------------+------------+------+------------------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+------------------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | index_mail_age_classid | NULL | NULL | NULL | 953508 | 0.50 | Using where | +----+-------------+------------+------------+------+------------------------+------+---------+------+--------+----------+-------------+
模糊查詢以通配符開頭
explain select * from index_demo where mail like '%123'; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 11.11 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
隱式轉(zhuǎn)換，如字符串不加引號(hào)
explain select * from index_demo where sn = 1000000; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | unique_sn | NULL | NULL | NULL | 953508 | 10.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
where 子句中存在表達(dá)式或函數(shù)
-- 不使用索引 explain select * from index_demo where id + 1 = 1000000; +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | index_demo | NULL | ALL | NULL | NULL | NULL | NULL | 953508 | 100.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ -- 使用索引 explain select * from index_demo where id = 1000000 + 1; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | index_demo | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
到此這篇關(guān)于Mysql 進(jìn)行SQL調(diào)優(yōu)的方法的文章就介紹到這了,更多相關(guān)mysql sql調(diào)優(yōu)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql出現(xiàn)ERROR?2003?(HY000):?Can‘t?connect?to?MySQL?ser
本文主要介紹了mysql出現(xiàn)ERROR?2003?(HY000):?Can‘t?connect?to?MySQL?server?on?‘localhost‘?(10061)的解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2025-03-03
計(jì)算機(jī)二級(jí)考試MySQL?？键c(diǎn) 8種MySQL數(shù)據(jù)庫設(shè)計(jì)優(yōu)化方法
這篇文章主要為大家詳細(xì)介紹了計(jì)算機(jī)二級(jí)考試MySQL?？键c(diǎn)，詳細(xì)介紹8種MySQL數(shù)據(jù)庫設(shè)計(jì)優(yōu)化方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-08-08
這篇文章主要給大家介紹了關(guān)于MySQL磁盤碎片整理的相關(guān)資料,為什么數(shù)據(jù)庫會(huì)產(chǎn)生碎片,以及如何清理磁盤碎片，還有一些清理磁盤碎片的注意事項(xiàng),需要的朋友可以參考下2022-04-04
大家在打開MySQL時(shí),可能會(huì)遇到在登陸界面輸入密碼之后就閃退的這個(gè)問題,下面這篇文章主要給大家介紹了關(guān)于如何解決登錄mysql時(shí)出現(xiàn)的閃退問題的相關(guān)資料,需要的朋友可以參考下2023-05-05
這篇文章主要為大家詳細(xì)介紹了mysql5.7.17 zip 解壓安裝詳細(xì)過程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-02-02
淺談sql語句中GROUP BY 和 HAVING的使用方法
GROUP BY語句和HAVING語句，經(jīng)過研究和練習(xí)，終于明白如何使用了，在此記錄一下同時(shí)添加了一個(gè)自己舉的小例子，通過寫這篇文章來加深下自己學(xué)習(xí)的效果，還能和大家分享下，同時(shí)也方便以后查閱，一舉多得，下面由小編來和大家一起學(xué)習(xí)2019-05-05
推薦沒有虛擬主機(jī)的小巧的Mysql數(shù)據(jù)庫備份腳本（PHP）
推薦沒有虛擬主機(jī)的小巧的Mysql數(shù)據(jù)庫備份腳本（PHP）...2007-07-07
MySQL InnoDB通過隱藏列（DB_TRX_ID、DB_ROLL_PTR）記錄事務(wù)ID與回滾指針,結(jié)合ReadView判斷數(shù)據(jù)可見性,利用回滾段存儲(chǔ)舊版本數(shù)據(jù),實(shí)現(xiàn)MVCC機(jī)制,提升并發(fā)性能并保障數(shù)據(jù)一致性與隔離性2025-08-08
本文介紹了MySQL中的游標(biāo)和觸發(fā)器的使用方法,游標(biāo)可以對(duì)查詢結(jié)果集進(jìn)行逐行處理,而觸發(fā)器則可以在數(shù)據(jù)表發(fā)生更改時(shí)自動(dòng)執(zhí)行預(yù)定義的操作,感興趣的朋友跟隨小編一起看看吧2025-12-12
這篇文章主要介紹了mysql數(shù)據(jù)庫的加密與解密方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01