0、思考題：如果把100萬數(shù)據(jù)插入MYSQL ，如何提高插入效率

（1）關(guān)閉自動(dòng)提交，只手動(dòng)提交一次

（2）刪除除主鍵索引外其他索引

（3）拼寫mysql可以執(zhí)行的長sql，批量插入數(shù)據(jù)

（4）使用java多線程

（5）使用框架，設(shè)置屬性，實(shí)現(xiàn)批量插入

1、計(jì)算、函數(shù)導(dǎo)致索引失效

CREATE INDEX idx_name ON emp (NAME);

EXPLAIN SELECT * FROM emp WHERE emp.name? LIKE 'abc%';

EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; ----索引失效

2 LIKE以%開頭索引失效

EXPLAIN SELECT * FROM emp WHERE NAME LIKE '%ab%'; ----索引失效

3、不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;

EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; ----索引失效

4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; ----索引失效

5、類型轉(zhuǎn)換導(dǎo)致索引失效

EXPLAIN SELECT * FROM emp WHERE NAME='123';

EXPLAIN SELECT * FROM emp WHERE NAME= 123; ----索引失效

6、全值匹配我最愛

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptid = 4 AND emp.name = 'abcd';

CREATE INDEX idx_age ON emp(age);

CREATE INDEX idx_age_deptid ON emp(age,deptid);

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

7、最佳左前綴法則

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;

CREATE INDEX idx_age_name ON emp (age,NAME);

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;

8、索引中范圍條件右邊的列失效

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>1000 ;

CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);

1、數(shù)據(jù)準(zhǔn)備

-- 分類

CREATE TABLE IF NOT EXISTS `class` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`card` INT(10) UNSIGNED NOT NULL,

PRIMARY KEY (`id`)

);

-- 圖書

CREATE TABLE IF NOT EXISTS `book` (

`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`card` INT(10) UNSIGNED NOT NULL,

PRIMARY KEY (`bookid`)

);





-- 插入16條記錄

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));



-- 插入20條記錄

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2、左外連接實(shí)例

（1）明確角色

（2）優(yōu)化

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

CREATE INDEX idx_class_card ON class(card);

CREATE INDEX idx_book_card ON book(card);

*使用LEFT JOIN，前面的是驅(qū)動(dòng)表、后面是被驅(qū)動(dòng)表

針對兩張表的連接條件涉及的列，索引要?jiǎng)?chuàng)建在被驅(qū)動(dòng)表上，驅(qū)動(dòng)表盡量是小表

• 如果驅(qū)動(dòng)表上沒有where過濾條件
  • 當(dāng)驅(qū)動(dòng)表的連接條件沒有索引時(shí)，驅(qū)動(dòng)表是全表掃描
  • 當(dāng)針對驅(qū)動(dòng)表的連接條件建立索引時(shí)，驅(qū)動(dòng)表依然要進(jìn)行全索引掃描
  • 因此，此時(shí)建立在驅(qū)動(dòng)表上的連接條件上的索引是沒有太大意義的
• 如果驅(qū)動(dòng)表上有where過濾條件，那么針對過濾條件創(chuàng)建的索引是有必要的

3、內(nèi)連接實(shí)例

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

CREATE INDEX idx_class_card ON class(card);

CREATE INDEX idx_book_card ON book(card);

*使用INNER JOIN，驅(qū)動(dòng)表、被驅(qū)動(dòng)表不固定，mysql選擇

MySQL優(yōu)化器也會(huì)自動(dòng)選擇驅(qū)動(dòng)表，自動(dòng)選擇驅(qū)動(dòng)表的原則是：索引創(chuàng)建在被驅(qū)動(dòng)表上，驅(qū)動(dòng)表是小表。

4、分析4種查詢sql（mysql5）

#1 NO3

EXPLAIN SELECT ab.name,c.`name` ceoname FROM

(SELECT a.`name`,b.`CEO` FROM emp a

LEFT JOIN dept b ON a.`deptId`=b.`id`)ab

LEFT JOIN emp c ON ab.ceo=c.`id`;



#2 NO4

EXPLAIN SELECT c.name,ab.name ceoname FROM emp c LEFT JOIN

(SELECT a.`name`,b.`id` FROM emp a

INNER JOIN dept b ON b.`CEO` = a.`id`)ab

ON c.`deptId`= ab.id;



#3? NO1

?EXPLAIN SELECT a.`name`,c.`name` ceoname FROM emp a

LEFT JOIN dept b? ON a.`deptId`= b.id

LEFT JOIN emp c ON b.`CEO`= c.`id`;



#4? NO2

EXPLAIN SELECT a.`name`,(SELECT c.name FROM emp c WHERE c.id =b.`CEO`)ceoname

?FROM emp a

LEFT JOIN dept b ON a.`deptId`=b.`id`;

5、總結(jié)

• 保證被驅(qū)動(dòng)表的JOIN字段已經(jīng)創(chuàng)建了索引
• 需要JOIN 的字段，數(shù)據(jù)類型保持絕對一致。
• LEFT JOIN 時(shí)，選擇小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表 。減少外層循環(huán)的次數(shù)。
• INNER JOIN 時(shí)，MySQL會(huì)自動(dòng)將小結(jié)果集的表選為驅(qū)動(dòng)表 。選擇相信MySQL優(yōu)化策略。
• 能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢。(減少查詢的趟數(shù))
• 衍生表建不了索引（MySQL5.5）

1、子查詢優(yōu)化

（1）獲取非掌門人成員

#獲取非掌門人成員

CALL proc_drop_index("atguigudb","emp");

CALL proc_drop_index("atguigudb","dept");

SELECT * FROM t_emp a WHERE a.id NOT IN?

(SELECT b.ceo FROM t_dept b WHERE b.ceo IS NOT NULL);

EXPLAIN SELECT * FROM emp a WHERE a.id NOT IN?

(SELECT b.ceo FROM dept b WHERE b.ceo IS NOT NULL);

#子查詢優(yōu)化NOT IN?

EXPLAIN SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.ceo

WHERE? b.id IS NULL;

（2）結(jié)論

盡量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx = xx WHERE xx IS NULL替代

2、排序優(yōu)化

（1）實(shí)例

CALL proc_drop_index("atguigudb","emp");

CALL proc_drop_index("atguigudb","dept");

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

#無過濾，不索引

EXPLAIN SELECT * FROM emp ORDER BY age,deptid;

EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;

#順序錯(cuò)，不索引

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;

CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,`empno`);

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;

EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

#方向反，不索引

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;

1. 總結(jié)

無過濾，不索引

順序錯(cuò)，不索引

方向反，不索引

3、mysql索引選擇

EXPLAIN SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY `name`;

CREATE INDEX idx_age_empno ON emp (age,`empno`);

CREATE INDEX idx_age_name ON emp (age,NAME);

*當(dāng)【范圍條件】和【group by 或者 order by】的字段出現(xiàn)二選一時(shí)，優(yōu)先觀察條件字段的過濾數(shù)量，如果過濾的數(shù)據(jù)足夠多，而需要排序的數(shù)據(jù)并不多時(shí)，優(yōu)先把索引放在范圍字段上。反之，亦然。

也可以將選擇權(quán)交給MySQL：索引同時(shí)存在，mysql自動(dòng)選擇最優(yōu)的方案：（對于這個(gè)例子，mysql選擇idx_age_empno），但是，隨著數(shù)據(jù)量的變化，選擇的索引也會(huì)隨之變化的。

4、雙路排序和單路排序

（1）雙路排序（慢）

取一批數(shù)據(jù)，要對磁盤進(jìn)行兩次掃描。眾所周知，IO是很耗時(shí)的，所以在mysql4.1之后，出現(xiàn)了第二種改進(jìn)的算法，就是單路排序。

（2）單路排序（快）

它的效率更快一些，因?yàn)?/span>只讀取一次磁盤，避免了第二次讀取數(shù)據(jù)。并且把隨機(jī)IO變成了順序IO。但是它會(huì)使用更多的空間， 因?yàn)樗衙恳恍卸急４嬖趦?nèi)存中了。

5、分組優(yōu)化

• group by 使用索引的原則幾乎跟order by一致。但是group by 即使沒有過濾條件用到索引，也可以直接使用索引（Order By 必須有過濾條件才能使用上索引）
• 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會(huì)很慢。

6、覆蓋索引優(yōu)化

總結(jié)

• 禁止使用select *，禁止查詢與業(yè)務(wù)無關(guān)字段
• 盡量利用覆蓋索引