常見索引類型
- 主鍵索引
- 它是一種特殊的唯一索引，不允許有空值。
- 普通索引
- 最基本的索引，它沒有任何限制。
- 唯一索引
- 普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。
- 聯(lián)合索引
- 多個(gè)列值的集合。
- 覆蓋索引
- 通過索引數(shù)據(jù)結(jié)構(gòu)，即可直接返回?cái)?shù)據(jù)，不需要回表
- 前綴索引
- char/varchar太長全部做索引存在浪費(fèi)且效率差
- Blob/text類型不能整列作為索引，所以需要前綴索引
- 無法利用前綴索引完成排序
索引為什么不可用
- 通過索引掃描的記錄數(shù)超過30%，變成全表掃描
- 聯(lián)合索引中，第一個(gè)索引列使用范圍查詢（這時(shí)用到部分索引）
- 聯(lián)合索引中，第一個(gè)查詢條件不是最左索引列
- 模糊查詢條件最左以通配符%開始
- HEAP表使用HASH索引時(shí)，使用范圍檢索或者ORDER BY
- 多表關(guān)聯(lián)時(shí)，排序字段不屬于驅(qū)動(dòng)表，無法利用索引完成排序
- 兩個(gè)獨(dú)立索引，其中一個(gè)用于檢索，一個(gè)用于排序（只能用到一個(gè)）
復(fù)雜SQL
復(fù)雜SQL
復(fù)雜SQL-問題
復(fù)雜SQL-問題
- 問題 ：全表掃秒、臨時(shí)表、文件排序
- 解決辦法 :簡化成2條SQL
- 獨(dú)立的SQL查詢汽修廠ID
- 將join表中的orgid改為直接指定
- 解決辦法 :簡化成2條SQL
- 優(yōu)點(diǎn)：原有SQL改變少，性能從3秒下降為100ms左右
- 缺點(diǎn)：100ms的耗時(shí)仍然偏高，建議拆分為多條獨(dú)立SQL
復(fù)雜SQL-修改
復(fù)雜SQL-修改
- 修改后可以看出需要掃描的行數(shù)大量減少，對應(yīng)的查詢時(shí)間也大幅度下降
結(jié)果
通過索引掃描的記錄數(shù)超過30%，變成全表掃描
CREATE TABLE `table3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` tinyint(3) NOT NULL DEFAULT '1',
`col2` varchar(10) NOT NULL DEFAULT '2',
PRIMARY KEY (`id`),
KEY `idx_col1` (`col1`),
KEY `idx_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
查詢
-
DESC SELECT * FROM table3 WHERE col1 = 1 \G
結(jié)果
-
DESC SELECT * FROM table3 WHERE col1 = 2 \G
結(jié)果
不回表查詢
-
DESC SELECT col1 FROM table3 WHERE col1 = 1 \G
Paste_Image.png
-
DESC SELECT col1 FROM table3 WHERE col1 = 2 \G
Paste_Image.png
函數(shù)查詢不能使用索引
-
DESC SELECT * FROM table3 WHERE left(col2,1) = 2 \G
Paste_Image.png
-
DESC SELECT * FROM table3 WHERE col2 LIKE '2%' \G
Paste_Image.png
小表可以不建索引嗎
- 看情況，通常最好要建索引
- 案例
- 用mysqlslap對只有一萬行記錄的表進(jìn)行簡單壓測，一種是對該表先進(jìn)性排序后讀取30條記錄，另一種是對該表堆積讀取一行記錄，分別對比有索引和沒有索引的表現(xiàn)。結(jié)論：
1、排序后讀取時(shí)沒有索引時(shí)慢了約37倍時(shí)間。壓測期間出現(xiàn)大量的Creating sort index狀態(tài)。
2、隨機(jī)讀取一行記錄時(shí)，沒索引時(shí)慢了約44倍時(shí)間。壓測期間出現(xiàn)大量的Send data狀態(tài)，有索引時(shí)，則更多的是出現(xiàn)Sending to client狀態(tài)。
3、不管是小表還是大表，需要時(shí)還是加上索引吧，否則有可能它就是瓶頸
- 用mysqlslap對只有一萬行記錄的表進(jìn)行簡單壓測，一種是對該表先進(jìn)性排序后讀取30條記錄，另一種是對該表堆積讀取一行記錄，分別對比有索引和沒有索引的表現(xiàn)。結(jié)論：
參考文檔
MySQL 單表百萬數(shù)據(jù)記錄分頁性能優(yōu)化
http://www.cnblogs.com/lyroge/p/3837886.html
MySQL如何利用索引優(yōu)化ORDER BY排序語句
http://www.cnblogs.com/anywei/archive/2011/12/12/mysql.html
索引、提交頻率對InnoDB表寫入速度的影響
http://imysql.com/2014/09/24/mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert.shtml
分頁優(yōu)化
http://imysql.com/2014/07/26/mysql-optimization-case-paging-optimize.shtml
MySQL 5.6 查詢優(yōu)化器新特性的“BUG”
http://imysql.cn/2014/08/05/a-fake-bug-with-eq-range-index-dive-limit.shtml
MySQL開發(fā)規(guī)范之我見
http://imysql.com/2015/07/23/something-important-about-mysql-design-reference.shtml
