一 索引使用

1.1 概述

1. 定義

索引幫助MySQL高效獲取數據的數據結構(按照一定規則)。

2. 定義解釋

MySQL在存儲數據之外，數據庫系統還維護者滿足特定查找算法的 數據結構，這些數據結構以某種方式引用(指向)數據， 這樣就 可以在這些數據結構上實現高級查找算法，這種數據結構就是索引。

3. 優缺點

優點 提高數據檢索效率，降低數據庫IO成本。通過索引對數據進行排序降低數據排序成本，降低CPU消耗。缺點 實際上索引也是一張表，該表中保存了主鍵與索引字段，并指向實體類的記錄，所以索引列也是要占用空間的。更新表時，MySQL 不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息。

1.2 索引結構(InnoDB)

MySQL數據庫中默認的存儲引擎InnoDB的索引結構為B+樹，而根據 葉子節點的內存存儲不同，索引類型分為主鍵索引和非主鍵索引。

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

主鍵索引的葉子節點存儲的是整行數據，其結構如下：

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

而非主鍵索引的葉子節點內容存儲時的主鍵的值，其結構如下： 

1.3 索引使用規則

沒有建立索引，執行計劃如下

建立索引

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

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

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

 

2. 最左前綴法制

違背最左法則，索引失效

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

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

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

4. 索引列上進行運算操作，索引失效

5. 字符串不加單引號，造成索引失效

由于，在查詢是，沒有對字符串加單引號，MySQL的查詢優化器， 會自動的進行類型轉換，造成索引失效。

6. 用or分割開的條件

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

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

 

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

解決方案

8. 如果MySQL評估使用索引比全表更慢，則不使用索引

9. is NULL，is NOT NULL有時索引失效。

10. in，not in有時索引失效

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

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

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

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

1.4 索引設計原則

索引的設計可以遵循一些已有的原則，創建索引的時候請盡量考 慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

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

二 常見SQL優化

2.1 數據庫準備

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優化

1. filesort 排序

第一種是通過對返回數據進行排序，也就是通常說的 filesort排 序，所有不是通過索引直接返回排序結果的排序都叫 FileSort排 序。

2. using index

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

多字段排序

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

3. 對上面兩種進行優化

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

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

可以適當提高 sort_buffer_size max_length_for_sort_data 系統變量，來增大排序區的大小，提高排序的效率。

2.3 group by優化

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

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

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

 

優化后

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

 

從上面的例子可以看出，第一個SQL語句需要進行"filesort"，而 第二個SQL由于order by null 不需要進行 "filesort"， 而上 文提過Filesort往往非常耗費時間。

創建索引

create index idx_emp_age_salary on emp(age,salary); 

 

2.4 limit優化

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

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

優化方案

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優化

在很多的業務系統中，都需要考慮進行分頁操作，但是當我們執 行分頁操作時，都需要進行一次count操作，求取總記錄數，如果 數據庫表的數據量大，在InnoDB引擎中，執行count操作的性能是 比較低的，需要遍歷全表數據，對計數進行累加。

優化方案

①. 在大數據量的查詢中，只查詢數據，而不展示總記錄數 ； 
②. 通過緩存redis維護一個表的計數，來記錄數據庫表的總記錄數，在執行插入/刪除時，需要動態更新； 
③. 在數據庫表中定義一個大數據量的計數表，在執行插入/刪除時，需要動態更新。 

2.6 大批量插入優化

1. 環境準備

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 ; 

當使用 load 命令導入數據的時候，適當的設置可以提高導入的效率。

對于InnoDB 類型的表，有以下幾種方式可以提高導入的效率：

主鍵順序插入

因為InnoDB類型的表是按照主鍵的順序保存的，所以將導入的數 據按照主鍵的順序排列，可以有效的提高導入數據的效率。如果 InnoDB表沒有主鍵，那么系統會自動默認創建一個內部列作為主 鍵，所以如果可以給表創建一個主鍵，將可以利用這點，來提高 導入數據的效率。

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

插入ID順序排列數據：

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

 

 插入ID無序排列數據：

關閉唯一性校驗

在導入數據前執行 SET UNIQUE_CHECKS=0，關閉唯一性校驗，在 導入結束后執行 SET UNIQUE_CHECKS=1，恢復唯一性校驗，可以 提高導入的效率。

手動提交事務

如果應用使用自動提交的方式，建議在導入前執行 SET AUTOCOMMIT=0，關閉自動提交，導入結束后再執行 SET AUTOCOMMIT=1，打開自動提交，也可以提高導入的效率。