本文轉(zhuǎn)自互聯(lián)網(wǎng)
在市中等地區(qū),都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局,加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力,以專注、極致的服務(wù)理念,為客戶提供成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站 網(wǎng)站設(shè)計(jì)制作定制制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),全網(wǎng)營(yíng)銷(xiāo)推廣,外貿(mào)網(wǎng)站建設(shè),市中網(wǎng)站建設(shè)費(fèi)用合理。
本系列文章將整理到我在GitHub上的《Java面試指南》倉(cāng)庫(kù),更多精彩內(nèi)容請(qǐng)到我的倉(cāng)庫(kù)里查看
https://github.com/h3pl/Java-Tutorial
喜歡的話麻煩點(diǎn)下Star哈
文章首發(fā)于我的個(gè)人博客:
www.how2playlife.com
本文是微信公眾號(hào)【Java技術(shù)江湖】的《重新學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)》其中一篇,本文部分內(nèi)容來(lái)源于網(wǎng)絡(luò),為了把本文主題講得清晰透徹,也整合了很多我認(rèn)為不錯(cuò)的技術(shù)博客內(nèi)容,引用其中了一些比較好的博客文章,如有侵權(quán),請(qǐng)聯(lián)系作者。
該系列博文會(huì)告訴你如何從入門(mén)到進(jìn)階,從sql基本的使用方法,從MySQL執(zhí)行引擎再到索引、事務(wù)等知識(shí),一步步地學(xué)習(xí)MySQL相關(guān)技術(shù)的實(shí)現(xiàn)原理,更好地了解如何基于這些知識(shí)來(lái)優(yōu)化sql,減少SQL執(zhí)行時(shí)間,通過(guò)執(zhí)行計(jì)劃對(duì)SQL性能進(jìn)行分析,再到MySQL的主從復(fù)制、主備部署等內(nèi)容,以便讓你更完整地了解整個(gè)MySQL方面的技術(shù)體系,形成自己的知識(shí)框架。
如果對(duì)本系列文章有什么建議,或者是有什么疑問(wèn)的話,也可以關(guān)注公眾號(hào)【Java技術(shù)江湖】聯(lián)系作者,歡迎你參與本系列博文的創(chuàng)作和修訂。
除非單表數(shù)據(jù)未來(lái)會(huì)一直不斷上漲,否則不要一開(kāi)始就考慮拆分,拆分會(huì)帶來(lái)邏輯、部署、運(yùn)維的各種復(fù)雜度,一般以整型值為主的表在
千萬(wàn)級(jí)
以下,字符串為主的表在
五百萬(wàn)
以下是沒(méi)有太大問(wèn)題的。而事實(shí)上很多時(shí)候MySQL單表的性能依然有不少優(yōu)化空間,甚至能正常支撐千萬(wàn)級(jí)以上的數(shù)據(jù)量:
盡量使用
TINYINT
、
SMALLINT
、
MEDIUM_INT
作為整數(shù)類型而非
INT
,如果非負(fù)則加上
UNSIGNED
VARCHAR
的長(zhǎng)度只分配真正需要的空間
使用枚舉或整數(shù)代替字符串類型
盡量使用
TIMESTAMP
而非
DATETIME
,
單表不要有太多字段,建議在20以內(nèi)
避免使用NULL字段,很難查詢優(yōu)化且占用額外索引空間
用整型來(lái)存IP
索引并不是越多越好,要根據(jù)查詢有針對(duì)性的創(chuàng)建,考慮在
WHERE
和
ORDER BY
命令上涉及的列建立索引,可根據(jù)
EXPLAIN
來(lái)查看是否用了索引還是全表掃描
應(yīng)盡量避免在
WHERE
子句中對(duì)字段進(jìn)行
NULL
值判斷,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
值分布很稀少的字段不適合建索引,例如”性別”這種只有兩三個(gè)值的字段
字符字段只建前綴索引
字符字段最好不要做主鍵
不用外鍵,由程序保證約束
盡量不用
UNIQUE
,由程序保證約束
使用多列索引時(shí)主意順序和查詢條件保持一致,同時(shí)刪除不必要的單列索引
可通過(guò)開(kāi)啟慢查詢?nèi)罩緛?lái)找出較慢的SQL
不做列運(yùn)算:
SELECT id WHERE age + 1 = 10
,任何對(duì)列的操作都將導(dǎo)致表掃描,它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等,查詢時(shí)要盡可能將操作移至等號(hào)右邊
sql語(yǔ)句盡可能簡(jiǎn)單:一條sql只能在一個(gè)cpu運(yùn)算;大語(yǔ)句拆小語(yǔ)句,減少鎖時(shí)間;一條大sql可以堵死整個(gè)庫(kù)
不用
SELECT *
OR
改寫(xiě)成
IN
:
OR
的效率是n級(jí)別,
IN
的效率是log(n)級(jí)別,in的個(gè)數(shù)建議控制在200以內(nèi)
不用函數(shù)和觸發(fā)器,在應(yīng)用程序?qū)崿F(xiàn)
避免
%xxx
式查詢
少用
JOIN
使用同類型進(jìn)行比較,比如用
'123'
和
'123'
比,
123
和
123
比
盡量避免在
WHERE
子句中使用!=或<>操作符,否則將引擎放棄使用索引而進(jìn)行全表掃描
對(duì)于連續(xù)數(shù)值,使用
BETWEEN
不用
IN
:
SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表數(shù)據(jù)不要拿全表,要使用
LIMIT
來(lái)分頁(yè),每頁(yè)數(shù)量也不要太大
目前廣泛使用的是MyISAM和InnoDB兩種引擎:
MyISAM引擎是MySQL 5.1及之前版本的默認(rèn)引擎,它的特點(diǎn)是:
不支持行鎖,讀取時(shí)對(duì)需要讀到的所有表加鎖,寫(xiě)入時(shí)則對(duì)表加排它鎖
不支持事務(wù)
不支持外鍵
不支持崩潰后的安全恢復(fù)
在表有讀取查詢的同時(shí),支持往表中插入新紀(jì)錄
支持
BLOB
和
TEXT
的前500個(gè)字符索引,支持全文索引
支持延遲更新索引,極大提升寫(xiě)入性能
對(duì)于不會(huì)進(jìn)行修改的表,支持壓縮表,極大減少磁盤(pán)空間占用
InnoDB在MySQL 5.5后成為默認(rèn)索引,它的特點(diǎn)是:
支持行鎖,采用MVCC來(lái)支持高并發(fā)
支持事務(wù)
支持外鍵
支持崩潰后的安全恢復(fù)
不支持全文索引
總體來(lái)講,MyISAM適合
SELECT
密集型的表,而InnoDB適合
INSERT
和
UPDATE
密集型的表
首先是建表和導(dǎo)數(shù)據(jù)的過(guò)程。
參考 https://nsimple.top/archives/mysql-create-million-data.html
有時(shí)候我們需要對(duì)大數(shù)據(jù)進(jìn)行測(cè)試,本地一般沒(méi)有那么多數(shù)據(jù),就需要我們自己生成一些。下面會(huì)借助內(nèi)存表的特點(diǎn)進(jìn)行生成百萬(wàn)條測(cè)試數(shù)據(jù)。
SQL
-- 創(chuàng)建一個(gè)臨時(shí)內(nèi)存表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;
SQL
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 '投票數(shù)', `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 '狀態(tài) 1-正常 2-已刪除', `create_time` int(10) unsigned NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創(chuàng)建時(shí)間', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH COMMENT '用戶ID哈希索引') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票記錄表';
SQL
-- 創(chuàng)建生成長(zhǎng)度為n的隨機(jī)字符串的函數(shù)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 //
SQL
-- 創(chuàng)建插入數(shù)據(jù)的存儲(chǔ)過(guò)程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 ; -- 改回默認(rèn)的 MySQL delimiter:';'
SQL
-- 調(diào)用存儲(chǔ)過(guò)程 生成100W條數(shù)據(jù)CALL add_vote_record_memory(1000000);
SQL
SELECT count(*) FROM `vote_record_memory`;-- count(*)-- 105646
SQL
INSERT INTO vote_record SELECT * FROM `vote_record_memory`;
SQL
SELECT count(*) FROM `vote_record`;-- count(*)-- 105646
SQL
-- 參數(shù)n是每次要插入的條數(shù)-- lastid是已導(dǎo)入的最大idCREATE 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
SQL
-- 調(diào)用存儲(chǔ)過(guò)程 插入60w條CALL copy_data_from_tmp(600000);
SELECT * FROM vote_record;
全表查詢
建完表以后開(kāi)啟慢查詢?nèi)罩?,具體參考下面的例子,然后學(xué)會(huì)用explain。windows慢日志的位置在c盤(pán),另外,使用client工具也可以記錄慢日志,所以不一定要用命令行來(lái)執(zhí)行測(cè)試,否則大表數(shù)據(jù)在命令行中要顯示的非常久。
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
耗時(shí)3秒,我設(shè)置的門(mén)檻是一秒。所以記錄了下來(lái)。
explain執(zhí)行計(jì)劃
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
全表掃描耗時(shí)3秒多,用不到索引。
2 select * from vote_record where vote_num > 1000
沒(méi)有索引,所以相當(dāng)于全表掃描,一樣是3.5秒左右
3 select * from vote_record where vote_num > 1000
加索引create
CREATE INDEX vote ON vote_record(vote_num);
explain查看執(zhí)行計(jì)劃
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N ALL votenum,vote \N \N \N 996507 50.00 Using where
還是沒(méi)用到索引,因?yàn)椴环献钭笄熬Y匹配。查詢需要3.5秒左右
最后修改一下sql語(yǔ)句
EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num > 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
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;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N index_merge PRIMARY,votenum,vote votenum,PRIMARY 8,4 \N 51 100.00 Using intersect(votenum,PRIMARY); Using where
用到了兩個(gè)索引,votenum,PRIMARY。
這是為什么呢。
再看一個(gè)語(yǔ)句
EXPLAIN SELECT * FROM vote_record WHERE id = 1000 AND vote_num > 1000
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N const PRIMARY,votenum PRIMARY 4 const 1 100.00
也只有主鍵用到了索引。這是因?yàn)橹挥凶钭笄熬Y索引可以用>或<,其他索引用<或者>會(huì)導(dǎo)致用不到索引。
下面是幾個(gè)網(wǎng)上參考的例子:
一:索引是sql語(yǔ)句優(yōu)化的關(guān)鍵,學(xué)會(huì)使用慢日志和執(zhí)行計(jì)劃分析sql
背景:使用A電腦安裝mysql,B電腦通過(guò)xshell方式連接,數(shù)據(jù)內(nèi)容我都已經(jīng)創(chuàng)建好,現(xiàn)在我已正常的進(jìn)入到mysql中
步驟1:設(shè)置慢查詢?nèi)罩镜某瑫r(shí)時(shí)間,先查看日志存放路徑查詢慢日志的地址,因?yàn)橛新樵兊膬?nèi)容,就會(huì)到這個(gè)日志中:
show global variables like "%slow%";
2.開(kāi)啟慢查詢?nèi)罩?/p>
set global slow_query_log=on;
3.查看慢查詢?nèi)罩镜脑O(shè)置時(shí)間,是否是自己需要的
show global variables like "%long%";
4.如果不是自己想的時(shí)間,修改慢查詢時(shí)間,只要超過(guò)了以下的設(shè)置時(shí)間,查詢的日志就會(huì)到剛剛的日志中,我設(shè)置查詢時(shí)間超過(guò)1S就進(jìn)入到慢查詢?nèi)罩局?/p>
set global long_query_time=1;
5.大數(shù)據(jù)已準(zhǔn)備,進(jìn)行數(shù)據(jù)的查詢,xshell最好開(kāi)兩個(gè)窗口,一個(gè)查看日志,一個(gè)執(zhí)行內(nèi)容
Sql查詢語(yǔ)句:select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
發(fā)現(xiàn)查數(shù)據(jù)的總時(shí)間去掉了17.74S
查看日志:打開(kāi)日志
標(biāo)記1:執(zhí)行的sql語(yǔ)句
標(biāo)記2:執(zhí)行sql的時(shí)間,我的是10點(diǎn)52執(zhí)行的
標(biāo)記3:使用那臺(tái)機(jī)器
標(biāo)記4:執(zhí)行時(shí)間,query_tims,查詢數(shù)據(jù)的時(shí)間
標(biāo)記5:不知道是干嘛的
標(biāo)記6:執(zhí)行耗時(shí)的sql語(yǔ)句,我在想我1的應(yīng)該是截取錯(cuò)了!但是記住最后一定是顯示耗時(shí)是因?yàn)閳?zhí)行什么sql造成的
6.執(zhí)行打印計(jì)劃,主要是查看是否使用了索引等其他內(nèi)容,主要就是在sql前面加上explain 關(guān)鍵字
explain select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M';
描述extra中,表示只使用了where條件,沒(méi)有其他什么索引之類的
7.進(jìn)行sql優(yōu)化,建一個(gè)fist_name的索引,索引就是將你需要的數(shù)據(jù)先給篩選出來(lái),這樣就可以節(jié)省很多掃描時(shí)間
create index firstname on employees_tmp(first_name);
注:創(chuàng)建索引時(shí)會(huì)很慢,是對(duì)整個(gè)表做了一個(gè)復(fù)制功能,并進(jìn)行數(shù)據(jù)的一些分類(我猜是這樣,所以會(huì)很慢)
8.查看建立的索引
show index from employees_tmp;
9.在執(zhí)行查詢語(yǔ)句,查看語(yǔ)句的執(zhí)行時(shí)間
select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
發(fā)現(xiàn)時(shí)間已經(jīng)有所提升了,其實(shí)選擇索引也不一開(kāi)始就知道,我們?cè)谠囋囀褂眯詣e,gender進(jìn)行索引
10.刪除已經(jīng)有的索引,刪除索引:
drop index first_name on employees_tmp;
11.創(chuàng)建性別的索引(性別是不怎么好的索引方式,因?yàn)橛泻芏嘀貜?fù)數(shù)據(jù))
create index index_gendar on employees_tmp(gender);
在執(zhí)行sql語(yǔ)句查詢數(shù)據(jù),查看查詢執(zhí)行時(shí)間,沒(méi)有創(chuàng)建比較優(yōu)秀的索引,導(dǎo)致查詢時(shí)間還變長(zhǎng)了,
為嘛還變長(zhǎng)了,這個(gè)我沒(méi)有弄懂
12.我們?cè)谠囋囀褂脛?chuàng)建組合索引,使用性別和姓名
alter table employees_tmp add index idx_union (first_name,gender);
在執(zhí)行sql查看sql數(shù)據(jù)的執(zhí)行時(shí)間
select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'
速度提升了N多倍啊
查看創(chuàng)建的索引
show index from employees_tmp;
索引建的好真的一個(gè)好幫手,建不好就是費(fèi)時(shí)的一個(gè)操作
目前還不知道為什么建立性別的索引會(huì)這么慢
二:sql優(yōu)化注意要點(diǎn),比如索引是否用到,查詢優(yōu)化是否改變了執(zhí)行計(jì)劃,以及一些細(xì)節(jié)
場(chǎng)景
我用的數(shù)據(jù)庫(kù)是mysql5.6,下面簡(jiǎn)單的介紹下場(chǎng)景
課程表
create table Course( c_id int PRIMARY KEY, name varchar(10) )
數(shù)據(jù)100條
學(xué)生表:
create table Student( id int PRIMARY KEY, name varchar(10) )
數(shù)據(jù)70000條
學(xué)生成績(jī)表SC
CREATE table SC( sc_id int PRIMARY KEY, s_id int, c_id int, score int )
數(shù)據(jù)70w條
查詢目的:
查找語(yǔ)文考100分的考生
查詢語(yǔ)句:
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 )
執(zhí)行時(shí)間:30248.271s
暈,為什么這么慢,先來(lái)查看下查詢計(jì)劃:
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 )
發(fā)現(xiàn)沒(méi)有用到索引,type全是ALL,那么首先想到的就是建立一個(gè)索引,建立索引的字段當(dāng)然是在where條件的字段。
先給sc表的c_id和score建個(gè)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次執(zhí)行上述查詢語(yǔ)句,時(shí)間為: 1.054s
快了3w多倍,大大縮短了查詢時(shí)間,看來(lái)索引能極大程度的提高查詢效率,看來(lái)建索引很有必要,很多時(shí)候都忘記建
索引了,數(shù)據(jù)量小的的時(shí)候壓根沒(méi)感覺(jué),這優(yōu)化感覺(jué)挺爽。
但是1s的時(shí)間還是太長(zhǎng)了,還能進(jìn)行優(yōu)化嗎,仔細(xì)看執(zhí)行計(jì)劃:
查看優(yōu)化后的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` ) ) ) )
補(bǔ)充:這里有網(wǎng)友問(wèn)怎么查看優(yōu)化后的語(yǔ)句
方法如下:
在命令窗口執(zhí)行
有type=all
按照我之前的想法,該sql的執(zhí)行的順序應(yīng)該是先執(zhí)行子查詢
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗時(shí):0.001s
得到如下結(jié)果:
然后再執(zhí)行
select s.* from Student s where s.s_id in(7,29,5000)
耗時(shí):0.001s
這樣就是相當(dāng)快了啊,Mysql竟然不是先執(zhí)行里層的查詢,而是將sql優(yōu)化成了exists子句,并出現(xiàn)了EPENDENT SUBQUERY,
mysql是先執(zhí)行外層查詢,再執(zhí)行里層的查詢,這樣就要循環(huán)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
這里為了重新分析連接查詢的情況,先暫時(shí)刪除索引sc_c_id_index,sc_score_index
執(zhí)行時(shí)間是:0.057s
效率有所提高,看看執(zhí)行計(jì)劃:
這里有連表的情況出現(xiàn),我猜想是不是要給sc表的s_id建立個(gè)索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執(zhí)行連接查詢
時(shí)間: 1.076s,竟然時(shí)間還變長(zhǎng)了,什么原因?查看執(zhí)行計(jì)劃:
優(yōu)化后的查詢語(yǔ)句為:
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) )
貌似是先做的連接查詢,再執(zhí)行的where過(guò)濾
回到前面的執(zhí)行計(jì)劃:
這里是先做的where過(guò)濾,再做連表,執(zhí)行計(jì)劃還不是固定的,那么我們先看下標(biāo)準(zhǔn)的sql執(zhí)行順序:
正常情況下是先join再where過(guò)濾,但是我們這里的情況,如果先join,將會(huì)有70w條數(shù)據(jù)發(fā)送join做操,因此先執(zhí)行where
過(guò)濾是明智方案,現(xiàn)在為了排除mysql的查詢優(yōu)化,我自己寫(xiě)一條優(yōu)化后的sql
SELECT s.*FROM ( SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100 ) tINNER JOIN Student s ON t.s_id = s.s_id
即先執(zhí)行sc表的過(guò)濾,再進(jìn)行表連接,執(zhí)行時(shí)間為:0.054s
和之前沒(méi)有建s_id索引的時(shí)間差不多
查看執(zhí)行計(jì)劃:
先提取sc再連表,這樣效率就高多了,現(xiàn)在的問(wèn)題是提取sc的時(shí)候出現(xiàn)了掃描表,那么現(xiàn)在可以明確需要建立相關(guān)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再執(zhí)行查詢:
SELECT s.*FROM ( SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100 ) tINNER JOIN Student s ON t.s_id = s.s_id
執(zhí)行時(shí)間為:0.001s,這個(gè)時(shí)間相當(dāng)靠譜,快了50倍
執(zhí)行計(jì)劃:
我們會(huì)看到,先提取sc,再連表,都用到了索引。
那么再來(lái)執(zhí)行下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
執(zhí)行時(shí)間0.001s
執(zhí)行計(jì)劃:
這里是mysql進(jìn)行了查詢語(yǔ)句優(yōu)化,先執(zhí)行了where過(guò)濾,再執(zhí)行連接操作,且都用到了索引。
總結(jié):
1.mysql嵌套子查詢效率確實(shí)比較低
2.可以將其優(yōu)化成連接查詢
3.建立合適的索引
4.學(xué)會(huì)分析sql執(zhí)行計(jì)劃,mysql會(huì)對(duì)sql進(jìn)行優(yōu)化,所以分析執(zhí)行計(jì)劃很重要
由于時(shí)間問(wèn)題,這篇文章先寫(xiě)到這里,后續(xù)再分享其他的sql優(yōu)化經(jīng)歷。
三、海量數(shù)據(jù)分頁(yè)查找時(shí)如何使用主鍵索引進(jìn)行優(yōu)化
數(shù)據(jù)分頁(yè)在網(wǎng)頁(yè)中十分多見(jiàn),分頁(yè)一般都是limit start,offset,然后根據(jù)頁(yè)碼page計(jì)算start
select * from user limit **1**,**20**
這種分頁(yè)在幾十萬(wàn)的時(shí)候分頁(yè)效率就會(huì)比較低了,MySQL需要從頭開(kāi)始一直往后計(jì)算,這樣大大影響效率
SELECT * from user limit **100001**,**20**; //time **0**.151s explain SELECT * from user limit **100001**,**20**;
我們可以用explain分析下語(yǔ)句,沒(méi)有用到任何索引,MySQL執(zhí)行的行數(shù)是16W+,于是我們可以想用到索引去實(shí)現(xiàn)分頁(yè)
使用主鍵索引來(lái)優(yōu)化數(shù)據(jù)分頁(yè)
select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**; //time **0**.003s
使用explain分析語(yǔ)句,MySQL這次掃描的行數(shù)是8W+,時(shí)間也大大縮短。
explain select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**;
![](https://oscimg.oschina.net/oscnet/05fffbffc5e3ef9add4719846ad53f25099.jpg)
在數(shù)據(jù)量比較大的時(shí)候,我們盡量去利用索引來(lái)優(yōu)化語(yǔ)句。上面的優(yōu)化方法如果id不是主鍵索引,查詢效率比第一種還要低點(diǎn)。我們可以先使用explain來(lái)分析語(yǔ)句,查看語(yǔ)句的執(zhí)行順序和執(zhí)行性能。
轉(zhuǎn)載于: https://my.oschina.net/alicoder/blog/3097141
新聞名稱:重新學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)12:從實(shí)踐sql語(yǔ)句優(yōu)化開(kāi)始
文章源于:http://bm7419.com/article16/jjeedg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供品牌網(wǎng)站建設(shè)、網(wǎng)站營(yíng)銷(xiāo)、網(wǎng)頁(yè)設(shè)計(jì)公司、面包屑導(dǎo)航、關(guān)鍵詞優(yōu)化、做網(wǎng)站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)