一 索引使用

1.1 概述

1. 定義

索引幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(按照一定規(guī)則)。

2. 定義解釋

MySQL在存儲(chǔ)數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)者滿足特定查找算法的 數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)， 這樣就 可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

3. 優(yōu)缺點(diǎn)

優(yōu)點(diǎn) 提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫IO成本。通過索引對(duì)數(shù)據(jù)進(jìn)行排序降低數(shù)據(jù)排序成本，降低CPU消耗。缺點(diǎn) 實(shí)際上索引也是一張表，該表中保存了主鍵與索引字段，并指向?qū)嶓w類的記錄，所以索引列也是要占用空間的。更新表時(shí)，MySQL 不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息。

1.2 索引結(jié)構(gòu)(InnoDB)

MySQL數(shù)據(jù)庫中默認(rèn)的存儲(chǔ)引擎InnoDB的索引結(jié)構(gòu)為B+樹，而根據(jù) 葉子節(jié)點(diǎn)的內(nèi)存存儲(chǔ)不同，索引類型分為主鍵索引和非主鍵索引。

1. 主鍵索引(聚簇索引)

主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是整行數(shù)據(jù)，其結(jié)構(gòu)如下：

2. 非主鍵索引(二級(jí)索引或輔助索引)

而非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容存儲(chǔ)時(shí)的主鍵的值，其結(jié)構(gòu)如下： 

1.3 索引使用規(guī)則

沒有建立索引，執(zhí)行計(jì)劃如下

建立索引

create index idx_seller_name_status_address on tb_seller(name, status, seller); 

1. 全值匹配，對(duì)索引所有列都制定具體值

explain select * from tb_seller where name='小米科技' and status='1' and 
address='北京市'; 

2. 最左前綴法制

違背最左法則，索引失效

如果符合最左法則，但是出現(xiàn)跳躍某一列，只有最左列索引生效：

3. 范圍查詢右邊的列，不能使用索引

根據(jù)前面的兩個(gè)字段name，status查詢是走索引的，但是最后一 個(gè)條件address 沒有用到索引。

4. 索引列上進(jìn)行運(yùn)算操作，索引失效

5. 字符串不加單引號(hào)，造成索引失效

由于，在查詢是，沒有對(duì)字符串加單引號(hào)，MySQL的查詢優(yōu)化器， 會(huì)自動(dòng)的進(jìn)行類型轉(zhuǎn)換，造成索引失效。

6. 用or分割開的條件

示例，name字段是索引列 ， 而createtime不是索引列，中間是 or進(jìn)行連接是不走索引的 ：

explain select * from tb_seller where name='黑馬程序員' or createtime = '2088-01-01 12:00:00'\G; 

7. 以%開頭的Like模糊查詢，索引失效。

解決方案

8. 如果MySQL評(píng)估使用索引比全表更慢，則不使用索引

9. is NULL，is NOT NULL有時(shí)索引失效。

10. in，not in有時(shí)索引失效

11. 盡量使用覆蓋索引，避免select

盡量使用覆蓋索引(只訪問索引的查詢(索引列完全包含查詢列))，減少select。

如果查詢列，超出索引列，也會(huì)降低性能。

using index ：使用覆蓋索引的時(shí)候就會(huì)出現(xiàn) 
using where：在查找使用索引的情況下，需要回表去查詢所需的數(shù)據(jù) 
using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù) 
using index ; using where：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要 
回表查詢數(shù)據(jù) 

1.4 索引設(shè)計(jì)原則

索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考 慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

對(duì)查詢頻次較高，且數(shù)據(jù)量比較大的表建立索引。 
 
索引字段的選擇，最佳候選列應(yīng)當(dāng)從where子句的條件中提取，如 
果where子句中的組合比較多，那么應(yīng)當(dāng)挑選最常用、過濾效果最 
好的列的組合。 
 
使用唯一索引，區(qū)分度越高，使用索引的效率越高。 
 
索引可以有效的提升查詢數(shù)據(jù)的效率，但索引數(shù)量不是多多益 
善，索引越多，維護(hù)索引的代價(jià)自然也就水漲船高。對(duì)于插入、 
更新、刪除等DML操作比較頻繁的表來說，索引過多，會(huì)引入相當(dāng) 
高的維護(hù)代價(jià)，降低DML操作的效率，增加相應(yīng)操作的時(shí)間消耗。 
另外索引過多的話，MySQL也會(huì)犯選擇困難病，雖然最終仍然會(huì)找 
到一個(gè)可用的索引，但無疑提高了選擇的代價(jià)。 
 
使用短索引，索引創(chuàng)建之后也是使用硬盤來存儲(chǔ)的，因此提升索 
引訪問的I/O效率，也可以提升總體的訪問效率。假如構(gòu)成索引的 
字段總長度比較短，那么在給定大小的存儲(chǔ)塊內(nèi)可以存儲(chǔ)更多的 
索引值，相應(yīng)的可以有效的提升MySQL訪問索引的I/O效率。 
 
利用最左前綴，N個(gè)列組合而成的組合索引，那么相當(dāng)于是創(chuàng)建了 
N個(gè)索引，如果查詢時(shí)where子句中使用了組成該索引的前幾個(gè)字 
段，那么這條查詢SQL可以利用組合索引來提升查詢效率。 

二 常見SQL優(yōu)化

2.1 數(shù)據(jù)庫準(zhǔn)備

1. sql

CREATE TABLE `emp` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(100) NOT NULL, 
  `age` int(3) NOT NULL, 
  `salary` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('2','Jerry','30','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('3','Luci','25','2800'); 
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('5','Tom2','21','2200'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('6','Jerry2','31','3300'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('7','Luci2','26','2700'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('8','Jay2','33','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('9','Tom3','23','2400'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('10','Jerry3','32','3100'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('11','Luci3','26','2900'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('12','Jay3','37','4500'); 
create index idx_emp_age_salary on emp(age,salary); 

2.2 order by優(yōu)化

1. filesort 排序

第一種是通過對(duì)返回?cái)?shù)據(jù)進(jìn)行排序，也就是通常說的 filesort排 序，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort排 序。

2. using index

第二種通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高。

多字段排序

了解了MySQL的排序方式，優(yōu)化目標(biāo)就清晰了：盡量減少額外的排 序，通過索引直接返回有序數(shù)據(jù)。where 條件和Order by 使用 相同的索引，并且Order By 的順序和索引順序相同， 并且 Order by 的字段都是升序，或者都是降序。否則肯定需要額外的 操作，這樣就會(huì)出現(xiàn)FileSort。

3. 對(duì)上面兩種進(jìn)行優(yōu)化

通過創(chuàng)建合適的索引，能夠減少 Filesort 的出現(xiàn)，但是在某些 情況下，條件限制不能讓Filesort消失，那就需要加快Filesort 的排序操作。對(duì)于Filesort ， MySQL 現(xiàn)在采用的是一次掃描算 法：一次性取出滿足條件的所有字段，然后在排序區(qū) sortbuffer 中排序后直接輸出結(jié)果集。排序時(shí)內(nèi)存開銷較大，但是排序效率 比兩次掃描算法要高。

MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data 的大小 和Query語句取出的字段總大小， 來判定是否那種排序算法，如 果max_length_for_sort_data 更大，那么使用第二種優(yōu)化之后 的算法;否則使用第一種。

可以適當(dāng)提高 sort_buffer_size max_length_for_sort_data 系統(tǒng)變量，來增大排序區(qū)的大小，提高排序的效率。

2.3 group by優(yōu)化

由于GROUP BY 實(shí)際上也同樣會(huì)進(jìn)行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當(dāng)然，如果 在分組的時(shí)候還使用了其他的一些聚合函數(shù)，那么還需要一些聚 合函數(shù)的計(jì)算。所以，在GROUP BY 的實(shí)現(xiàn)過程中，與 ORDER BY 一樣也可以利用到索引。

如果查詢包含 group by 但是用戶想要避免排序結(jié)果的消耗， 則 可以執(zhí)行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp; 
explain select age,count(*) from emp group by age; 

優(yōu)化后

explain select age,count(*) from emp group by age order by null; 

從上面的例子可以看出，第一個(gè)SQL語句需要進(jìn)行"filesort"，而 第二個(gè)SQL由于order by null 不需要進(jìn)行 "filesort"， 而上 文提過Filesort往往非常耗費(fèi)時(shí)間。

創(chuàng)建索引

create index idx_emp_age_salary on emp(age,salary); 

2.4 limit優(yōu)化

一般分頁查詢時(shí)，通過創(chuàng)建覆蓋索引能夠比較好地提高性能。一 個(gè)常見又非常頭疼的問題就是 limit5000000,10 ，此時(shí)需要 MySQL排序前5000010 記錄，僅僅返回5000000 - 5000010 的記 錄，其他記錄丟棄，查詢排序的代價(jià)非常大 。

limit分頁操作, 越往后, 性能越低 :

優(yōu)化方案

select * from tb_sku t , (select id from tb_sku order by id limit 9000000,1) a where t.id = a.id; 

2.5 count優(yōu)化

在很多的業(yè)務(wù)系統(tǒng)中，都需要考慮進(jìn)行分頁操作，但是當(dāng)我們執(zhí) 行分頁操作時(shí)，都需要進(jìn)行一次count操作，求取總記錄數(shù)，如果 數(shù)據(jù)庫表的數(shù)據(jù)量大，在InnoDB引擎中，執(zhí)行count操作的性能是 比較低的，需要遍歷全表數(shù)據(jù)，對(duì)計(jì)數(shù)進(jìn)行累加。

優(yōu)化方案

①. 在大數(shù)據(jù)量的查詢中，只查詢數(shù)據(jù)，而不展示總記錄數(shù) ； 
②. 通過緩存redis維護(hù)一個(gè)表的計(jì)數(shù)，來記錄數(shù)據(jù)庫表的總記錄數(shù)，在執(zhí)行插入/刪除時(shí)，需要?jiǎng)討B(tài)更新； 
③. 在數(shù)據(jù)庫表中定義一個(gè)大數(shù)據(jù)量的計(jì)數(shù)表，在執(zhí)行插入/刪除時(shí)，需要?jiǎng)討B(tài)更新。 

2.6 大批量插入優(yōu)化

1. 環(huán)境準(zhǔn)備

CREATE TABLE `tb_user` ( 
  `id` INT(11) NOT NULL AUTO_INCREMENT, 
  `username` VARCHAR(50) NOT NULL, 
  `password` VARCHAR(50) NOT NULL, 
  `name` VARCHAR(20) NOT NULL, 
  `birthday` DATE DEFAULT NULL, 
  `sex` CHAR(1) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `unique_user_username` (`username`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 ; 

當(dāng)使用 load 命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的效率。

對(duì)于InnoDB 類型的表，有以下幾種方式可以提高導(dǎo)入的效率：

主鍵順序插入

因?yàn)镮nnoDB類型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù) 據(jù)按照主鍵的順序排列，可以有效的提高導(dǎo)入數(shù)據(jù)的效率。如果 InnoDB表沒有主鍵，那么系統(tǒng)會(huì)自動(dòng)默認(rèn)創(chuàng)建一個(gè)內(nèi)部列作為主 鍵，所以如果可以給表創(chuàng)建一個(gè)主鍵，將可以利用這點(diǎn)，來提高 導(dǎo)入數(shù)據(jù)的效率。

腳本文件介紹 : 
  sql1.log ----> 主鍵有序 
  sql2.log ----> 主鍵無序 

插入ID順序排列數(shù)據(jù)：

load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n'; 

 插入ID無序排列數(shù)據(jù)：

關(guān)閉唯一性校驗(yàn)

在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)，在 導(dǎo)入結(jié)束后執(zhí)行 SET UNIQUE_CHECKS=1，恢復(fù)唯一性校驗(yàn)，可以 提高導(dǎo)入的效率。

手動(dòng)提交事務(wù)

如果應(yīng)用使用自動(dòng)提交的方式，建議在導(dǎo)入前執(zhí)行 SET AUTOCOMMIT=0，關(guān)閉自動(dòng)提交，導(dǎo)入結(jié)束后再執(zhí)行 SET AUTOCOMMIT=1，打開自動(dòng)提交，也可以提高導(dǎo)入的效率。