安基網 首頁 系統 數據庫 查看內容

工作中遇到的99%SQL優化,這里都能給你解決方案

2019-12-2 12:44| 投稿: xiaotiger |來自: 互聯網


免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!

摘要: 前幾篇文章介紹了mysql的底層數據結構和mysql優化的神器explain。后臺有些朋友說小強只介紹概念,平時使用還是一臉懵,強烈要求小強來一篇實戰sql優化,經過周末兩天的整理和總結,sql優化實戰新鮮出爐, 大家平時學習和工作中,遇到的90% 的sql優化都會介紹到,介意篇幅過長,分成3篇文章哈。CREATE T ...

前幾篇文章介紹了mysql的底層數據結構和mysql優化的神器explain。后臺有些朋友說小強只介紹概念,平時使用還是一臉懵,強烈要求小強來一篇實戰sql優化,經過周末兩天的整理和總結,sql優化實戰新鮮出爐, 大家平時學習和工作中,遇到的90% 的sql優化都會介紹到,介意篇幅過長,分成3篇文章哈。

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(20) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='員工表';
insert into employees(name,age,position,hire_time) values('LiLei', 22, 'manager', NOW())
insert into employees(name,age,position,hire_time) values('HanMeimei', 23, 'dev', NOW())
insert into employees(name,age,position,hire_time) values('Lucy', 23, 'dev', NOW())

全值匹配

索引的字段類型是varchar(n):2字節存儲字符串長度,如果是utf-8, 則長度是3n+2

EXPLAIN select * from employees where name='LiLei';

EXPLAIN select * from employees where name='LiLei' AND age = 22;

EXPLAIN select * from employees where name='LiLei' AND age = 22 AND position = 'manager';

最左前綴法則

如果索引是多列,要最受最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。以下三條sql根據最左前綴法則,都不會走索引。

EXPLAIN select * from employees where age = 22 AND position='manager';
EXPLAIN select * from employees where position ='manager';
EXPLAIN select * from employees where age=17;

索引失效

不要在索引列上做任何操作(計算、函數、類型轉換),會導致索引失效而轉向全表掃描。

EXPLAIN select * from employees where name='LiLei';

EXPLAIN select * from employees where left(name, 3)='LiLei';

給hire_time增加一個普通索引:

alter table `employees` ADD INDEX `idx_hire_time`(`hire_time`) USING BTREE;
EXPLAIN select * from employees where date(hire_time) = '2019-08-25';

還原最初索引狀態

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

存儲引擎不能使用索引中范圍條件右邊的列

-- EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age>22 AND position ='manager';

看到key_len這個索引長度是78, 也就是只使用到了前兩個字段name和age,postition沒有使用到索引的。

覆蓋索引

盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列)),減少selelct * 語句。

EXPLAIN SELECT name,age,position FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';

條件判斷

mysql在使用不等于(! = 或者 <>)的時候無法使用索引會導致全表掃描

EXPLAIN SELECT * FROM employees WHERE name !='LiLei' ;

空值判斷

is null,is not null也無法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

like

like以通配符開頭(‘$abc’)mysql索引失效會變成全表掃描操作

EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';

字符串不加單引號索引失效

EXPLAIN SELECT * FROM employees WHERE name ='1000';
EXPLAIN SELECT * FROM employees WHERE name =1000;

不加單引號的字符串,mysql底層會使用cust函數將其轉換為字符串,此時索引失效。

or&in少使用

少用or或in,用它查詢時,mysql不一定使用索引,mysql內部優化器會根據索引比例、表大小等多個因素整體評估是否使用索引。

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' or name='HanMeimei';

范圍查詢優化

給年齡添加單值索引

ALTER TABLE `employees`ADD INDEX `idx_age`(`age`) USING BTREE;
EXPLAIN select * from employees where age > 1 and age <= 2000;

沒有走索引原因:mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。這個例子沒有走索引可能是因為單次數據量查詢過大導致優化器最終選擇不走索引。優化方法:可以將大的范圍拆分成多個小范圍。


小編推薦:欲學習電腦技術、系統維護、網絡管理、編程開發和安全攻防等高端IT技術,請 點擊這里 注冊賬號,公開課頻道價值萬元IT培訓教程免費學,讓您少走彎路、事半功倍,好工作升職加薪!

本文出自:https://www.toutiao.com/a6732439031054877187/

免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!


鮮花

握手

雷人

路過

雞蛋

相關閱讀

最新評論

 最新
返回頂部
洗衣店赚钱联系澳洁 平特四连肖中10元是多少 湖南丫丫麻将免费下载 一波中特一肖中马 快乐斗牛棋牌 腾讯 投资股票的技巧 好玩的棋牌app 山东11选5任五全天计划 棋牌游戏开发要多少 娱乐棋牌开发 股票短线选股技巧