常見索引類型
- 主鍵索引
- 它是一種特殊的唯一索引鸳君，不允許有空值倒得。
- 普通索引
- 最基本的索引泻红，它沒有任何限制。
- 唯一索引
- 普通索引類似霞掺，不同的就是：索引列的值必須唯一谊路，但允許有空值。
- 聯(lián)合索引
- 多個列值的集合菩彬。
- 覆蓋索引
- 通過索引數(shù)據(jù)結構缠劝，即可直接返回數(shù)據(jù)，不需要回表
- 前綴索引
- char/varchar太長全部做索引存在浪費且效率差
- Blob/text類型不能整列作為索引骗灶，所以需要前綴索引
- 無法利用前綴索引完成排序
索引為什么不可用
- 通過索引掃描的記錄數(shù)超過30%惨恭，變成全表掃描
- 聯(lián)合索引中，第一個索引列使用范圍查詢（這時用到部分索引）
- 聯(lián)合索引中耙旦，第一個查詢條件不是最左索引列
- 模糊查詢條件最左以通配符%開始
- HEAP表使用HASH索引時脱羡，使用范圍檢索或者ORDER BY
- 多表關聯(lián)時，排序字段不屬于驅(qū)動表免都，無法利用索引完成排序
- 兩個獨立索引锉罐，其中一個用于檢索，一個用于排序（只能用到一個）
復雜SQL
復雜SQL
復雜SQL-問題
復雜SQL-問題
- 問題 ：全表掃秒绕娘、臨時表脓规、文件排序
- 解決辦法 :簡化成2條SQL
- 獨立的SQL查詢汽修廠ID
- 將join表中的orgid改為直接指定
- 解決辦法 :簡化成2條SQL
- 優(yōu)點：原有SQL改變少，性能從3秒下降為100ms左右
- 缺點：100ms的耗時仍然偏高险领，建議拆分為多條獨立SQL
復雜SQL-修改
復雜SQL-修改
- 修改后可以看出需要掃描的行數(shù)大量減少抖拦，對應的查詢時間也大幅度下降
結果
通過索引掃描的記錄數(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
結果
-
DESC SELECT * FROM table3 WHERE col1 = 2 \G
結果
不回表查詢
-
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對只有一萬行記錄的表進行簡單壓測态罪，一種是對該表先進性排序后讀取30條記錄，另一種是對該表堆積讀取一行記錄下面，分別對比有索引和沒有索引的表現(xiàn)复颈。結論：
1、排序后讀取時沒有索引時慢了約37倍時間沥割。壓測期間出現(xiàn)大量的Creating sort index狀態(tài)耗啦。
2、隨機讀取一行記錄時机杜，沒索引時慢了約44倍時間帜讲。壓測期間出現(xiàn)大量的Send data狀態(tài)，有索引時椒拗，則更多的是出現(xiàn)Sending to client狀態(tài)似将。
3、不管是小表還是大表蚀苛，需要時還是加上索引吧在验，否則有可能它就是瓶頸
- 用mysqlslap對只有一萬行記錄的表進行簡單壓測态罪，一種是對該表先進性排序后讀取30條記錄，另一種是對該表堆積讀取一行記錄下面，分別對比有索引和沒有索引的表現(xiàn)复颈。結論：
參考文檔
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
