版權聲明:本文為部落客原創文章,未經部落客允許不得轉載。 https://blog.csdn.net/a724888/article/details/79394168
本文不堆疊網上海量的sql優化技巧或是訣竅。隻通過兩個淺顯易懂又實用的例子介紹mysql的sql語句優化。
首先介紹一下一般的大表優化方案。當MySQL單表記錄數過大時,增删改查性能都會急劇下降,可以參考以下步驟來優化:
單表優化
微信公衆号【黃小斜】大廠程式員,網際網路行業新知,終身學習踐行者。關注後回複「Java」、「Python」、「C++」、「大資料」、「機器學習」、「算法」、「AI」、「Android」、「前端」、「iOS」、「考研」、「BAT」、「校招」、「筆試」、「面試」、「面經」、「計算機基礎」、「LeetCode」 等關鍵字可以擷取對應的免費學習資料。
除非單表資料未來會一直不斷上漲,否則不要一開始就考慮拆分,拆分會帶來邏輯、部署、運維的各種複雜度,一般以整型值為主的表在
千萬級
以下,字元串為主的表在
五百萬
以下是沒有太大問題的。而事實上很多時候MySQL單表的性能依然有不少優化空間,甚至能正常支撐千萬級以上的資料量:
字段
- 盡量使用
、TINYINT
SMALLINT
作為整數類型而非MEDIUM_INT
,如果非負則加上INT
UNSIGNED
-
的長度隻配置設定真正需要的空間VARCHAR
- 使用枚舉或整數代替字元串類型
-
而非TIMESTAMP
,DATETIME
- 單表不要有太多字段,建議在20以内
- 避免使用NULL字段,很難查詢優化且占用額外索引空間
- 用整型來存IP
索引
- 索引并不是越多越好,要根據查詢有針對性的建立,考慮在
和WHERE
指令上涉及的列建立索引,可根據ORDER BY
來檢視是否用了索引還是全表掃描EXPLAIN
- 應盡量避免在
子句中對字段進行WHERE
值判斷,否則将導緻引擎放棄使用索引而進行全表掃描NULL
- 值分布很稀少的字段不适合建索引,例如"性别"這種隻有兩三個值的字段
- 字元字段隻建字首索引
- 字元字段最好不要做主鍵
- 不用外鍵,由程式保證限制
- 盡量不用
,由程式保證限制UNIQUE
- 使用多列索引時主意順序和查詢條件保持一緻,同時删除不必要的單列索引
查詢SQL
- 可通過開啟慢查詢日志來找出較慢的SQL
- 不做列運算:
,任何對列的操作都将導緻表掃描,它包括資料庫教程函數、計算表達式等等,查詢時要盡可能将操作移至等号右邊SELECT id WHERE age + 1 = 10
- sql語句盡可能簡單:一條sql隻能在一個cpu運算;大語句拆小語句,減少鎖時間;一條大sql可以堵死整個庫
- 不用
SELECT *
-
改寫成OR
:IN
的效率是n級别,OR
的效率是log(n)級别,in的個數建議控制在200以内IN
- 不用函數和觸發器,在應用程式實作
- 避免
式查詢%xxx
- 少用
JOIN
- 使用同類型進行比較,比如用
'123'
比,'123'
123
比123
- 盡量避免在
子句中使用!=或<>操作符,否則将引擎放棄使用索引而進行全表掃描WHERE
- 對于連續數值,使用
BETWEEN
IN
SELECT id FROM t WHERE num BETWEEN 1 AND 5
- 清單資料不要拿全表,要使用
來分頁,每頁數量也不要太大LIMIT
引擎
目前廣泛使用的是MyISAM和InnoDB兩種引擎:
MyISAM
MyISAM引擎是MySQL 5.1及之前版本的預設引擎,它的特點是:
- 不支援行鎖,讀取時對需要讀到的所有表加鎖,寫入時則對表加排它鎖
- 不支援事務
- 不支援外鍵
- 不支援崩潰後的安全恢複
- 在表有讀取查詢的同時,支援往表中插入新紀錄
- 支援
BLOB
的前500個字元索引,支援全文索引TEXT
- 支援延遲更新索引,極大提升寫入性能
- 對于不會進行修改的表,支援壓縮表,極大減少磁盤空間占用
InnoDB
InnoDB在MySQL 5.5後成為預設索引,它的特點是:
- 支援行鎖,采用MVCC來支援高并發
- 支援事務
- 支援外鍵
- 支援崩潰後的安全恢複
- 不支援全文索引
總體來講,MyISAM适合
SELECT
密集型的表,而InnoDB适合
INSERT
UPDATE
密集型的表
0、自己寫的海量資料sql優化實踐
首先是建表和導資料的過程。
參考https://nsimple.top/archives/mysql-create-million-data.html
有時候我們需要對大資料進行測試,本地一般沒有那麼多資料,就需要我們自己生成一些。下面會借助記憶體表的特點進行生成百萬條測試資料。
- 建立一個臨時記憶體表, 做資料插入的時候會比較快些
SQL
-- 建立一個臨時記憶體表
DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- -- 建立一個普通表,用作模拟大資料的測試用例
DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '使用者Id',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票數',
`group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '使用者組id 0-未激活使用者 1-普通使用者 2-vip使用者 3-管理者使用者',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '狀态 1-正常 2-已删除',
`create_time` int(10) unsigned NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '建立時間',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH COMMENT '使用者ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票記錄表';
- 為了資料的随機性和真實性,我們需要建立一個可生成長度為n的随機字元串的函數。
-- 建立生成長度為n的随機字元串的函數
DELIMITER // -- 修改MySQL delimiter:'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN
DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i+1;
END WHILE;
RETURN return_str;
END //
- 為了操作友善,我們再建立一個插入資料的存儲過程
-- 建立插入資料的存儲過程
DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE vote_num INT DEFAULT 0;
DECLARE group_id INT DEFAULT 0;
DECLARE status TINYINT DEFAULT 1;
WHILE i < n DO
SET vote_num = FLOOR(1 + RAND() * 10000);
SET group_id = FLOOR(0 + RAND()*3);
SET status = FLOOR(1 + RAND()*2);
INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
SET i = i + 1;
END WHILE;
END //
DELIMITER ; -- 改回預設的 MySQL delimiter:';'
- 開始執行存儲過程,等待生成資料(10W條生成大約需要40分鐘)
-- 調用存儲過程 生成100W條資料
CALL add_vote_record_memory(1000000);
- 查詢記憶體表已生成記錄(為了下步測試,目前僅生成了105645條)
SELECT count(*) FROM `vote_record_memory`;
-- count(*)
-- 105646
- 把資料從記憶體表插入到普通表中(10w條資料13s就插入完了)
INSERT INTO vote_record SELECT * FROM `vote_record_memory`;
- 查詢普通表已的生成記錄
SELECT count(*) FROM `vote_record`;
-- count(*)
-- 105646
- 如果一次性插入普通表太慢,可以分批插入,這就需要寫個存儲過程了:
-- 參數n是每次要插入的條數
-- lastid是已導入的最大id
CREATE PROCEDURE `copy_data_from_tmp`(IN n INT)
BEGIN
DECLARE lastid INT DEFAULT 0;
SELECT MAX(id) INTO lastid FROM `vote_record`;
INSERT INTO `vote_record` SELECT * FROM `vote_record_memory` where id > lastid LIMIT n;
END
- 調用存儲過程:
-- 調用存儲過程 插入60w條
CALL copy_data_from_tmp(600000);
SELECT * FROM vote_record;
全表查詢
建完表以後開啟慢查詢日志,具體參考下面的例子,然後學會用explain。windows慢日志的位置在c盤,另外,使用client工具也可以記錄慢日志,是以不一定要用指令行來執行測試,否則大表資料在指令行中要顯示的非常久。
1 全表掃描select * from vote_record
慢日志
SET timestamp=1529034398;
select * from vote_record;
Time: 2018-06-15T03:52:58.804850Z
User@Host: root[root] @ localhost [::1] Id: 74
Query_time: 3.166424 Lock_time: 0.000000 Rows_sent: 900500 Rows_examined: 999999
耗時3秒,我設定的門檻是一秒。是以記錄了下來。
explain執行計劃
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N ALL \N \N \N \N 996507 100.00 \N
全表掃描耗時3秒多,用不到索引。
2 select * from vote_record where vote_num > 1000
沒有索引,是以相當于全表掃描,一樣是3.5秒左右
3 select * from vote_record where vote_num > 1000
**加索引create **
CREATE INDEX vote ON vote_record(vote_num);
/索引資訊/--------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
----------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ---------------
vote_record 0 PRIMARY 1 id A 996507 (NULL) (NULL) BTREE
vote_record 1 votenum 1 vote_num A 9942 (NULL) (NULL) BTREE
/DDL 資訊/------------
CREATE TABLE
vote_record
(
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id
varchar(20) NOT NULL DEFAULT '' COMMENT '使用者Id',
vote_num
int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票數',
group_id
int(10) unsigned NOT NULL DEFAULT '0' COMMENT '使用者組id 0-未激活使用者 1-普通使用者 2-vip使用者 3-管理者使用者',
status
tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '狀态 1-正常 2-已删除',
create_time
datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '建立時間',
PRIMARY KEY (
id
),
KEY
votenum
vote_num
)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8 COMMENT='投票記錄表'
explain檢視執行計劃
1 SIMPLE vote_record \N ALL votenum,vote \N \N \N 996507 50.00 Using where
還是沒用到索引,因為不符合最左字首比對。查詢需要3.5秒左右
最後修改一下sql語句
EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num > 1000;
1 SIMPLE vote_record \N range PRIMARY,votenum,vote PRIMARY 4 \N 498253 50.00 Using where
用到了索引,但是隻用到了主鍵索引。再修改一次
EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num = 1000;
1 SIMPLE vote_record \N index_merge PRIMARY,votenum,vote votenum,PRIMARY 8,4 \N 51 100.00 Using intersect(votenum,PRIMARY); Using where
用到了兩個索引,votenum,PRIMARY。
這是為什麼呢。
再看一個語句
EXPLAIN SELECT * FROM vote_record WHERE id = 1000 AND vote_num > 1000
1 SIMPLE vote_record \N const PRIMARY,votenum PRIMARY 4 const 1 100.00 \N
也隻有主鍵用到了索引。這是因為隻有最左字首索引可以用>或<,其他索引用<或者>會導緻用不到索引。
下面是幾個網上參考的例子:
一:索引是sql語句優化的關鍵,學會使用慢日志和執行計劃分析sql
背景:使用A電腦安裝mysql,B電腦通過xshell方式連接配接,資料内容我都已經建立好,現在我已正常的進入到mysql中
步驟1:設定慢查詢日志的逾時時間,先檢視日志存放路徑查詢慢日志的位址,因為有慢查詢的内容,就會到這個日志中:
show global variables like "%slow%";
2.開啟慢查詢日志
set global slow_query_log=on;
3.檢視慢查詢日志的設定時間,是否是自己需要的
show global variables like "%long%";
4.如果不是自己想的時間,修改慢查詢時間,隻要超過了以下的設定時間,查詢的日志就會到剛剛的日志中,我設定查詢時間超過1S就進入到慢查詢日志中
set global long_query_time=1;
5.大資料已準備,進行資料的查詢,xshell最好開兩個視窗,一個檢視日志,一個執行内容
Sql查詢語句:select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
發現查資料的總時間去掉了17.74S
檢視日志:打開日志
标記1:執行的sql語句
标記2:執行sql的時間,我的是10點52執行的
标記3:使用那台機器
标記4:執行時間,query_tims,查詢資料的時間
标記5:不知道是幹嘛的
标記6:執行耗時的sql語句,我在想我1的應該是截取錯了!但是記住最後一定是顯示耗時是因為執行什麼sql造成的
6.執行列印計劃,主要是檢視是否使用了索引等其他内容,主要就是在sql前面加上explain 關鍵字
explain select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M';
描述extra中,表示隻使用了where條件,沒有其他什麼索引之類的
7.進行sql優化,建一個fist_name的索引,索引就是将你需要的資料先給篩選出來,這樣就可以節省很多掃描時間
create index firstname on employees_tmp(first_name);
注:建立索引時會很慢,是對整個表做了一個複制功能,并進行資料的一些分類(我猜是這樣,是以會很慢)
8.檢視建立的索引
show index from employees_tmp;
9.在執行查詢語句,檢視語句的執行時間
select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
發現時間已經有所提升了,其實選擇索引也不一開始就知道,我們在試試使用性别,gender進行索引
10.删除已經有的索引,删除索引:
drop index first_name on employees_tmp;
11.建立性别的索引(性别是不怎麼好的索引方式,因為有很多重複資料)
create index index_gendar on employees_tmp(gender);
在執行sql語句查詢資料,檢視查詢執行時間,沒有建立比較優秀的索引,導緻查詢時間還變長了,
為嘛還變長了,這個我沒有弄懂
12.我們在試試使用建立組合索引,使用性别和姓名
alter table employees_tmp add index idx_union (first_name,gender);
在執行sql檢視sql資料的執行時間
select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
速度提升了N多倍啊
檢視建立的索引
show index from employees_tmp;
索引建的好真的一個好幫手,建不好就是費時的一個操作
目前還不知道為什麼建立性别的索引會這麼慢
二:sql優化注意要點,比如索引是否用到,查詢優化是否改變了執行計劃,以及一些細節
場景
我用的資料庫是mysql5.6,下面簡單的介紹下場景
課程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
資料100條
學生表:
create table Student(
id int PRIMARY KEY,
name varchar(10)
)
資料70000條
學生成績表SC
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
資料70w條
查詢目的:
查找國文考100分的考生
查詢語句:
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
執行時間:30248.271s
暈,為什麼這麼慢,先來檢視下查詢計劃:
EXPLAIN
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
發現沒有用到索引,type全是ALL,那麼首先想到的就是建立一個索引,建立索引的字段當然是在where條件的字段。
先給sc表的c_id和score建個索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次執行上述查詢語句,時間為: 1.054s
快了3w多倍,大大縮短了查詢時間,看來索引能極大程度的提高查詢效率,看來建索引很有必要,很多時候都忘記建
索引了,資料量小的的時候壓根沒感覺,這優化感覺挺爽。
但是1s的時間還是太長了,還能進行優化嗎,仔細看執行計劃:
檢視優化後的sql:
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
WHERE
< in_optimizer > (
`YSB`.`s`.`s_id` ,< EXISTS > (
SELECT
1
FROM
`YSB`.`SC` `sc`
WHERE
(
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
AND (
< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
)
)
)
)
補充:這裡有網友問怎麼檢視優化後的語句
方法如下:
在指令視窗執行
有type=all
按照我之前的想法,該sql的執行的順序應該是先執行子查詢
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗時:0.001s
得到如下結果:
然後再執行
select s.* from Student s where s.s_id in(7,29,5000)
這樣就是相當快了啊,Mysql竟然不是先執行裡層的查詢,而是将sql優化成了exists子句,并出現了EPENDENT SUBQUERY,
mysql是先執行外層查詢,再執行裡層的查詢,這樣就要循環70007*11=770077次。
那麼改用連接配接查詢呢?
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
這裡為了重新分析連接配接查詢的情況,先暫時删除索引sc_c_id_index,sc_score_index
執行時間是:0.057s
效率有所提高,看看執行計劃:
這裡有連表的情況出現,我猜想是不是要給sc表的s_id建立個索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執行連接配接查詢
時間: 1.076s,竟然時間還變長了,什麼原因?檢視執行計劃:
優化後的查詢語句為:
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
(
(
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
)
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
)
貌似是先做的連接配接查詢,再執行的where過濾
回到前面的執行計劃:
這裡是先做的where過濾,再做連表,執行計劃還不是固定的,那麼我們先看下标準的sql執行順序:
正常情況下是先join再where過濾,但是我們這裡的情況,如果先join,将會有70w條資料發送join做操,是以先執行where
過濾是明智方案,現在為了排除mysql的查詢優化,我自己寫一條優化後的sql
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
即先執行sc表的過濾,再進行表連接配接,執行時間為:0.054s
和之前沒有建s_id索引的時間差不多
檢視執行計劃:
先提取sc再連表,這樣效率就高多了,現在的問題是提取sc的時候出現了掃描表,那麼現在可以明确需要建立相關索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再執行查詢:
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
執行時間為:0.001s,這個時間相當靠譜,快了50倍
執行計劃:
我們會看到,先提取sc,再連表,都用到了索引。
那麼再來執行下sql
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
執行時間0.001s
這裡是mysql進行了查詢語句優化,先執行了where過濾,再執行連接配接操作,且都用到了索引。
總結:
1.mysql嵌套子查詢效率确實比較低
2.可以将其優化成連接配接查詢
3.建立合适的索引
4.學會分析sql執行計劃,mysql會對sql進行優化,是以分析執行計劃很重要
由于時間問題,這篇文章先寫到這裡,後續再分享其他的sql優化經曆。
三、海量資料分頁查找時如何使用主鍵索引進行優化
mysql百萬級分頁優化
普通分頁
資料分頁在網頁中十分多見,分頁一般都是limit start,offset,然後根據頁碼page計算start
select * from user limit **1**,**20**
這種分頁在幾十萬的時候分頁效率就會比較低了,MySQL需要從頭開始一直往後計算,這樣大大影響效率
SELECT * from user limit **100001**,**20**; //time **0**.151s
explain SELECT * from user limit **100001**,**20**;
我們可以用explain分析下語句,沒有用到任何索引,MySQL執行的行數是16W+,于是我們可以想用到索引去實作分頁
優化分頁
使用主鍵索引來優化資料分頁
select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**; //time **0**.003s
使用explain分析語句,MySQL這次掃描的行數是8W+,時間也大大縮短。
explain select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**;
總結
在資料量比較大的時候,我們盡量去利用索引來優化語句。上面的優化方法如果id不是主鍵索引,查詢效率比第一種還要低點。我們可以先使用explain來分析語句,檢視語句的執行順序和執行性能。