本文轉(zhuǎn)自互聯(lián)網(wǎng)

在市中等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計、成都做網(wǎng)站 網(wǎng)站設(shè)計制作定制制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),全網(wǎng)營銷推廣,外貿(mào)網(wǎng)站建設(shè),市中網(wǎng)站建設(shè)費用合理。

本系列文章將整理到我在GitHub上的《Java面試指南》倉庫，更多精彩內(nèi)容請到我的倉庫里查看

https://github.com/h3pl/Java-Tutorial

喜歡的話麻煩點下Star哈

文章首發(fā)于我的個人博客：

www.how2playlife.com

本文是微信公眾號【Java技術(shù)江湖】的《重新學(xué)習(xí)MySQL數(shù)據(jù)庫》其中一篇，本文部分內(nèi)容來源于網(wǎng)絡(luò)，為了把本文主題講得清晰透徹，也整合了很多我認為不錯的技術(shù)博客內(nèi)容，引用其中了一些比較好的博客文章，如有侵權(quán)，請聯(lián)系作者。

該系列博文會告訴你如何從入門到進階，從sql基本的使用方法，從MySQL執(zhí)行引擎再到索引、事務(wù)等知識，一步步地學(xué)習(xí)MySQL相關(guān)技術(shù)的實現(xiàn)原理，更好地了解如何基于這些知識來優(yōu)化sql，減少SQL執(zhí)行時間，通過執(zhí)行計劃對SQL性能進行分析，再到MySQL的主從復(fù)制、主備部署等內(nèi)容，以便讓你更完整地了解整個MySQL方面的技術(shù)體系，形成自己的知識框架。

如果對本系列文章有什么建議，或者是有什么疑問的話，也可以關(guān)注公眾號【Java技術(shù)江湖】聯(lián)系作者，歡迎你參與本系列博文的創(chuàng)作和修訂。

除非單表數(shù)據(jù)未來會一直不斷上漲，否則不要一開始就考慮拆分，拆分會帶來邏輯、部署、運維的各種復(fù)雜度，一般以整型值為主的表在 千萬級以下，字符串為主的表在 五百萬以下是沒有太大問題的。而事實上很多時候MySQL單表的性能依然有不少優(yōu)化空間，甚至能正常支撐千萬級以上的數(shù)據(jù)量：

字段

• 盡量使用 TINYINT、 SMALLINT、 MEDIUM_INT作為整數(shù)類型而非 INT，如果非負則加上 UNSIGNED

• VARCHAR的長度只分配真正需要的空間

• 使用枚舉或整數(shù)代替字符串類型

• 盡量使用 TIMESTAMP而非 DATETIME，

• 單表不要有太多字段，建議在20以內(nèi)

• 避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間

• 用整型來存IP

索引

• 索引并不是越多越好，要根據(jù)查詢有針對性的創(chuàng)建，考慮在 WHERE和 ORDER BY命令上涉及的列建立索引，可根據(jù) EXPLAIN來查看是否用了索引還是全表掃描

• 應(yīng)盡量避免在 WHERE子句中對字段進行 NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描

• 值分布很稀少的字段不適合建索引，例如”性別”這種只有兩三個值的字段

• 字符字段只建前綴索引

• 字符字段最好不要做主鍵

• 不用外鍵，由程序保證約束

• 盡量不用 UNIQUE，由程序保證約束

• 使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引

查詢SQL

• 可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL

• 不做列運算： SELECT id WHERE age + 1 = 10，任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫教程函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊

• sql語句盡可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫

• 不用 SELECT *

• OR改寫成 IN： OR的效率是n級別， IN的效率是log(n)級別，in的個數(shù)建議控制在200以內(nèi)

• 不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)

• 避免 %xxx式查詢

• 少用 JOIN

• 使用同類型進行比較，比如用 '123'和 '123'比， 123和 123比

• 盡量避免在 WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描

• 對于連續(xù)數(shù)值，使用 BETWEEN不用 IN： SELECT id FROM t WHERE num BETWEEN 1 AND 5

• 列表數(shù)據(jù)不要拿全表，要使用 LIMIT來分頁，每頁數(shù)量也不要太大

引擎

目前廣泛使用的是MyISAM和InnoDB兩種引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默認引擎，它的特點是：

• 不支持行鎖，讀取時對需要讀到的所有表加鎖，寫入時則對表加排它鎖

• 不支持事務(wù)

• 不支持外鍵

• 不支持崩潰后的安全恢復(fù)

• 在表有讀取查詢的同時，支持往表中插入新紀錄

• 支持 BLOB和 TEXT的前500個字符索引，支持全文索引

• 支持延遲更新索引，極大提升寫入性能

• 對于不會進行修改的表，支持壓縮表，極大減少磁盤空間占用

InnoDB

InnoDB在MySQL 5.5后成為默認索引，它的特點是：

• 支持行鎖，采用MVCC來支持高并發(fā)

• 支持事務(wù)

• 支持外鍵

• 支持崩潰后的安全恢復(fù)

• 不支持全文索引

總體來講，MyISAM適合 SELECT密集型的表，而InnoDB適合 INSERT和 UPDATE密集型的表

0、自己寫的海量數(shù)據(jù)sql優(yōu)化實踐

首先是建表和導(dǎo)數(shù)據(jù)的過程。

參考 https://nsimple.top/archives/mysql-create-million-data.html

有時候我們需要對大數(shù)據(jù)進行測試，本地一般沒有那么多數(shù)據(jù)，就需要我們自己生成一些。下面會借助內(nèi)存表的特點進行生成百萬條測試數(shù)據(jù)。

1. 創(chuàng)建一個臨時內(nèi)存表, 做數(shù)據(jù)插入的時候會比較快些

SQL

-- 創(chuàng)建一個臨時內(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;

1. — 創(chuàng)建一個普通表，用作模擬大數(shù)據(jù)的測試用例

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)建時間',    PRIMARY KEY (`id`),    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用戶ID哈希索引') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票記錄表';

1. 為了數(shù)據(jù)的隨機性和真實性，我們需要創(chuàng)建一個可生成長度為n的隨機字符串的函數(shù)。

SQL

-- 創(chuàng)建生成長度為n的隨機字符串的函數(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 //

1. 為了操作方便，我們再創(chuàng)建一個插入數(shù)據(jù)的存儲過程

SQL

-- 創(chuàng)建插入數(shù)據(jù)的存儲過程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：';'

1. 開始執(zhí)行存儲過程，等待生成數(shù)據(jù)(10W條生成大約需要40分鐘)

SQL

-- 調(diào)用存儲過程 生成100W條數(shù)據(jù)CALL add_vote_record_memory(1000000);

1. 查詢內(nèi)存表已生成記錄(為了下步測試，目前僅生成了105645條)

SQL

SELECT count(*) FROM `vote_record_memory`;-- count(*)-- 105646

1. 把數(shù)據(jù)從內(nèi)存表插入到普通表中(10w條數(shù)據(jù)13s就插入完了)

SQL

INSERT INTO vote_record SELECT * FROM `vote_record_memory`;

1. 查詢普通表已的生成記錄

SQL

SELECT count(*) FROM `vote_record`;-- count(*)-- 105646

1. 如果一次性插入普通表太慢，可以分批插入，這就需要寫個存儲過程了：

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

1. 調(diào)用存儲過程:

SQL

-- 調(diào)用存儲過程 插入60w條CALL copy_data_from_tmp(600000);

SELECT * FROM vote_record；

全表查詢

建完表以后開啟慢查詢?nèi)罩荆唧w參考下面的例子，然后學(xué)會用explain。windows慢日志的位置在c盤，另外，使用client工具也可以記錄慢日志，所以不一定要用命令行來執(zhí)行測試，否則大表數(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

耗時3秒，我設(shè)置的門檻是一秒。所以記錄了下來。

explain執(zhí)行計劃

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

全表掃描耗時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);

explain查看執(zhí)行計劃

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

還是沒用到索引，因為不符合最左前綴匹配。查詢需要3.5秒左右

最后修改一下sql語句

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

用到了兩個索引，votenum,PRIMARY。

這是為什么呢。

再看一個語句

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

也只有主鍵用到了索引。這是因為只有最左前綴索引可以用>或<，其他索引用<或者>會導(dǎo)致用不到索引。

下面是幾個網(wǎng)上參考的例子：

一：索引是sql語句優(yōu)化的關(guān)鍵，學(xué)會使用慢日志和執(zhí)行計劃分析sql

背景：使用A電腦安裝mysql,B電腦通過xshell方式連接，數(shù)據(jù)內(nèi)容我都已經(jīng)創(chuàng)建好，現(xiàn)在我已正常的進入到mysql中

步驟1：設(shè)置慢查詢?nèi)罩镜某瑫r時間，先查看日志存放路徑查詢慢日志的地址，因為有慢查詢的內(nèi)容，就會到這個日志中：

show global variables like "%slow%";

2.開啟慢查詢?nèi)罩?/p>

set global slow_query_log=on;

3.查看慢查詢?nèi)罩镜脑O(shè)置時間，是否是自己需要的

show global variables like "%long%";

4.如果不是自己想的時間，修改慢查詢時間，只要超過了以下的設(shè)置時間，查詢的日志就會到剛剛的日志中，我設(shè)置查詢時間超過1S就進入到慢查詢?nèi)罩局?/p>

set global long_query_time=1;

5.大數(shù)據(jù)已準備，進行數(shù)據(jù)的查詢，xshell最好開兩個窗口，一個查看日志，一個執(zhí)行內(nèi)容

Sql查詢語句：select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'

發(fā)現(xiàn)查數(shù)據(jù)的總時間去掉了17.74S

查看日志：打開日志

標記1：執(zhí)行的sql語句

標記2：執(zhí)行sql的時間，我的是10點52執(zhí)行的

標記3：使用那臺機器

標記4：執(zhí)行時間，query_tims,查詢數(shù)據(jù)的時間

標記5：不知道是干嘛的

標記6：執(zhí)行耗時的sql語句，我在想我1的應(yīng)該是截取錯了！但是記住最后一定是顯示耗時是因為執(zhí)行什么sql造成的

6.執(zhí)行打印計劃，主要是查看是否使用了索引等其他內(nèi)容,主要就是在sql前面加上explain 關(guān)鍵字

explain select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M';

描述extra中，表示只使用了where條件，沒有其他什么索引之類的

7.進行sql優(yōu)化，建一個fist_name的索引，索引就是將你需要的數(shù)據(jù)先給篩選出來，這樣就可以節(jié)省很多掃描時間

create index firstname on employees_tmp(first_name);

注：創(chuàng)建索引時會很慢，是對整個表做了一個復(fù)制功能，并進行數(shù)據(jù)的一些分類（我猜是這樣，所以會很慢）

8.查看建立的索引

show index from employees_tmp;

9.在執(zhí)行查詢語句，查看語句的執(zhí)行時間

select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'

發(fā)現(xiàn)時間已經(jīng)有所提升了，其實選擇索引也不一開始就知道，我們在試試使用性別，gender進行索引

10.刪除已經(jīng)有的索引，刪除索引：

drop index first_name on employees_tmp;

11.創(chuàng)建性別的索引(性別是不怎么好的索引方式，因為有很多重復(fù)數(shù)據(jù))

create index index_gendar on employees_tmp(gender);

在執(zhí)行sql語句查詢數(shù)據(jù)，查看查詢執(zhí)行時間，沒有創(chuàng)建比較優(yōu)秀的索引，導(dǎo)致查詢時間還變長了，

為嘛還變長了，這個我沒有弄懂

12.我們在試試使用創(chuàng)建組合索引，使用性別和姓名

alter table employees_tmp add index idx_union (first_name,gender);

在執(zhí)行sql查看sql數(shù)據(jù)的執(zhí)行時間

select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'

速度提升了N多倍啊

查看創(chuàng)建的索引

show index from employees_tmp;

索引建的好真的一個好幫手，建不好就是費時的一個操作

目前還不知道為什么建立性別的索引會這么慢

二：sql優(yōu)化注意要點，比如索引是否用到，查詢優(yōu)化是否改變了執(zhí)行計劃，以及一些細節(jié)

場景

我用的數(shù)據(jù)庫是mysql5.6，下面簡單的介紹下場景

課程表

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é)生成績表SC

CREATE table SC(     sc_id int PRIMARY KEY,     s_id int,     c_id int,     score int )

數(shù)據(jù)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 )

執(zhí)行時間：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 )

發(fā)現(xiàn)沒有用到索引，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);

再次執(zhí)行上述查詢語句，時間為: 1.054s

快了3w多倍，大大縮短了查詢時間，看來索引能極大程度的提高查詢效率，看來建索引很有必要，很多時候都忘記建

索引了，數(shù)據(jù)量小的的時候壓根沒感覺，這優(yōu)化感覺挺爽。

但是1s的時間還是太長了，還能進行優(yōu)化嗎，仔細看執(zhí)行計劃：

查看優(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`                    )                )        )    )

補充：這里有網(wǎng)友問怎么查看優(yōu)化后的語句

方法如下：

在命令窗口執(zhí)行

有type=all

按照我之前的想法，該sql的執(zhí)行的順序應(yīng)該是先執(zhí)行子查詢

select s_id from SC sc where sc.c_id = 0 and sc.score = 100

耗時：0.001s

得到如下結(jié)果：

然后再執(zhí)行

select s.* from Student s where s.s_id in(7,29,5000)

耗時：0.001s

這樣就是相當快了啊，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

這里為了重新分析連接查詢的情況，先暫時刪除索引sc_c_id_index，sc_score_index

執(zhí)行時間是：0.057s

效率有所提高，看看執(zhí)行計劃：

這里有連表的情況出現(xiàn)，我猜想是不是要給sc表的s_id建立個索引

CREATE index sc_s_id_index on SC(s_id);

show index from SC

在執(zhí)行連接查詢

時間: 1.076s，竟然時間還變長了，什么原因？查看執(zhí)行計劃：

優(yōu)化后的查詢語句為：

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過濾

回到前面的執(zhí)行計劃：

這里是先做的where過濾，再做連表，執(zhí)行計劃還不是固定的，那么我們先看下標準的sql執(zhí)行順序：

正常情況下是先join再where過濾，但是我們這里的情況，如果先join，將會有70w條數(shù)據(jù)發(fā)送join做操，因此先執(zhí)行where

過濾是明智方案，現(xiàn)在為了排除mysql的查詢優(yōu)化，我自己寫一條優(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表的過濾，再進行表連接，執(zhí)行時間為：0.054s

和之前沒有建s_id索引的時間差不多

查看執(zhí)行計劃：

先提取sc再連表，這樣效率就高多了，現(xiàn)在的問題是提取sc的時候出現(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í)行時間為：0.001s，這個時間相當靠譜，快了50倍

執(zhí)行計劃：

我們會看到，先提取sc，再連表，都用到了索引。

那么再來執(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í)行時間0.001s

執(zhí)行計劃：

這里是mysql進行了查詢語句優(yōu)化，先執(zhí)行了where過濾，再執(zhí)行連接操作，且都用到了索引。

總結(jié)：

1.mysql嵌套子查詢效率確實比較低

2.可以將其優(yōu)化成連接查詢

3.建立合適的索引

4.學(xué)會分析sql執(zhí)行計劃，mysql會對sql進行優(yōu)化，所以分析執(zhí)行計劃很重要

由于時間問題，這篇文章先寫到這里，后續(xù)再分享其他的sql優(yōu)化經(jīng)歷。

三、海量數(shù)據(jù)分頁查找時如何使用主鍵索引進行優(yōu)化

mysql百萬級分頁優(yōu)化

　　普通分頁

　　數(shù)據(jù)分頁在網(wǎng)頁中十分多見，分頁一般都是limit start,offset,然后根據(jù)頁碼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執(zhí)行的行數(shù)是16W+，于是我們可以想用到索引去實現(xiàn)分頁

　　 優(yōu)化分頁

使用主鍵索引來優(yōu)化數(shù)據(jù)分頁

 select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**; //time **0**.003s

使用explain分析語句，MySQL這次掃描的行數(shù)是8W+，時間也大大縮短。

 explain select * from user where id>(select id from user where id>=**100000** limit **1**) limit **20**;

 ![](https://oscimg.oschina.net/oscnet/05fffbffc5e3ef9add4719846ad53f25099.jpg)

　　總結(jié)

在數(shù)據(jù)量比較大的時候，我們盡量去利用索引來優(yōu)化語句。上面的優(yōu)化方法如果id不是主鍵索引，查詢效率比第一種還要低點。我們可以先使用explain來分析語句，查看語句的執(zhí)行順序和執(zhí)行性能。

轉(zhuǎn)載于: https://my.oschina.net/alicoder/blog/3097141