Mysql實(shí)戰(zhàn)-SQL語句優(yōu)化

前面我們講解了索引的存儲(chǔ)結(jié)構(gòu)，B+Tree的索引結(jié)構(gòu)，以及索引最左側(cè)匹配原則，Explain的用法，可以看到是否使用了索引，今天我們講解一下SQL語句的優(yōu)化及如何優(yōu)化

1.表結(jié)構(gòu)

新建表結(jié)構(gòu) user， user_info

#新建表結(jié)構(gòu) user
CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `id_card` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份證ID',
  `user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用戶名字',
  `age` int NOT NULL COMMENT '年齡',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表'

1. id 主鍵id列
2. id_card 身份證id
3. user_name 用戶姓名
4. age 年齡

先插入測(cè)試數(shù)據(jù), 插入 5條測(cè)試數(shù)據(jù)

INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (1, '11', 'aa', 10);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (2, '22', 'bb', 20);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (3, '33', 'cc', 30);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (4, '44', 'dd', 40);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (5, '55', 'ee', 50);

2 where語句及order的列 建立索引

表結(jié)構(gòu)先不創(chuàng)建索引，我們看下執(zhí)行分析
EXPLAIN SELECT * FROM user WHERE user_name=“AA”;

EXPLAIN SELECT * FROM `user` WHERE user_name="AA";

執(zhí)行成功， type=ALL表示沒有索引，查詢效率低下

我們?cè)?user_name上建立索引后，再看下

#創(chuàng)建索引
alter  table `user` add index `idx_name`(`user_name`);

#執(zhí)行分析
EXPLAIN SELECT * FROM `user` WHERE user_name="AA";

使用了索引，查詢效率提升

3. where語句不要使用!=,<>

where語句中使用!= 或者 <>, 或者使用 between and 都會(huì)是引擎放棄索引，進(jìn)行全表掃描

我們新建 age的索引，然后基于age去做查詢分析

#創(chuàng)建age索引
alter  table `user` add index `idx_age`(`age`);
#執(zhí)行分析
EXPLAIN SELECT * FROM `user` WHERE age=10;

使用age索引進(jìn)行查詢，沒有問題

現(xiàn)在我們使用 ！= 或者 <> 來進(jìn)行查詢，執(zhí)行查詢分析

EXPLAIN SELECT * FROM `user` WHERE age !=10;
EXPLAIN SELECT * FROM `user` WHERE age <>10;
EXPLAIN SELECT * FROM `user` WHERE age BETWEEN 10 and 20;
EXPLAIN SELECT * FROM `user` WHERE age > 10 and age < 20 ;

執(zhí)行結(jié)果全都是 type=range 表示在索引范圍內(nèi)查找,對(duì)索引的掃描開始于某一點(diǎn),返回匹配值域的行, 已經(jīng)不是ref類型了，效率已經(jīng)不高了
Extra 其他信息= using index condition 表示會(huì)先條件過濾索引，過濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過濾這些數(shù)據(jù)行；
using index condition = using index + 回表 + where 過濾

4.where語句不要or進(jìn)行判斷

where語句使用or判斷，也會(huì)導(dǎo)致引擎放棄索引，進(jìn)而進(jìn)行全表掃描
使用 or， 也會(huì)造成 type=range的情況

EXPLAIN SELECT * FROM `user` WHERE age =10 or age =20;

這種情況，我們可以采用 union all 來進(jìn)行優(yōu)化

EXPLAIN SELECT * FROM `user` WHERE age =10 union all  SELECT * FROM `user` WHERE age =20 ;

5.where語句不要使用 like模糊查詢

like模糊查詢，也會(huì)導(dǎo)致 全表掃描

#1.左側(cè)開頭精確匹配，右側(cè)結(jié)果模糊
EXPLAIN SELECT * FROM `user` WHERE user_name like "a%";
#2.左側(cè)開頭模糊，右側(cè)結(jié)果精確匹配
EXPLAIN SELECT * FROM `user` WHERE user_name like "%a";
#3.左側(cè)開頭模糊，右側(cè)結(jié)果模糊
EXPLAIN SELECT * FROM `user` WHERE user_name like "%a%";

上面3種情況，我們來逐一分析

1. 左側(cè)開頭精確匹配，右側(cè)結(jié)果模糊, 查詢會(huì)使用左側(cè)索引進(jìn)行匹配，type=range

EXPLAIN SELECT * FROM `user` WHERE user_name like "a%";

2. 左側(cè)開頭模糊，右側(cè)結(jié)果精確匹配， 查詢不會(huì)使用索引，全表掃描 type=ALL

EXPLAIN SELECT * FROM `user` WHERE user_name like "%a";

3. 左側(cè)開頭模糊，右側(cè)結(jié)果模糊, 查詢不會(huì)使用索引，全表掃描 type=ALL

EXPLAIN SELECT * FROM `user` WHERE user_name like "%a%";

6.where語句 不要 in 和not in， 可能也會(huì)導(dǎo)致全表掃描

where子語句，使用 in，not in 也有可能導(dǎo)致全表掃描

所以使用in 到底走不走索引呢？

• in通常是走索引的
• IN 的條件過多，會(huì)導(dǎo)致索引失效，走索引掃描
• 當(dāng)in后面的數(shù)據(jù)在數(shù)據(jù)表中超過一定的數(shù)量 （有人說是30%，假如上面的例子的全部數(shù)據(jù)大約100條，匹配數(shù)據(jù)超過30條 ），會(huì)走全表掃描，即不走索引
• in走不走索引和后面的數(shù)據(jù)有關(guān)系，這個(gè)比例不準(zhǔn)

我表中5條數(shù)據(jù)， 我現(xiàn)在 in(10,20,30,40), in了4條，但是依舊走了索引 type=range， key=idx_age

EXPLAIN SELECT * FROM `user` WHERE age in(10,20,30,40);

我現(xiàn)在再加一個(gè)in條件 in(10,20,30,40,50), 此刻就沒有走索引， type=ALL

EXPLAIN SELECT * FROM `user` WHERE age in(10,20,30,40,50);

但是 not in 是肯定不走索引的，這是我們明確禁止的

EXPLAIN SELECT * FROM `user` WHERE age not in(1,2);

7.where語句不要使用表達(dá)式計(jì)算及函數(shù)運(yùn)算

where子句，不要使用表達(dá)式計(jì)算或者函數(shù)運(yùn)算，這回導(dǎo)致全表掃描

EXPLAIN SELECT * FROM `user` WHERE age / 2 =10;
EXPLAIN SELECT * FROM `user` WHERE SUBSTRING(user_name,1,3)="aa";

執(zhí)行結(jié)果全部都是 type=ALL,使用表達(dá)式計(jì)算和函數(shù)的 都不會(huì)使用索引

至此，我們了解如何去優(yōu)化查詢語句，在平時(shí)項(xiàng)目中，也應(yīng)該多注意這些用法，防止出現(xiàn)線上事故文章來源地址http://www.zghlxwxcb.cn/news/detail-722365.html

到了這里，關(guān)于MSQL系列(六) Mysql實(shí)戰(zhàn)-SQL語句優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！