高性能Mysql學習筆記-建立高性能索引

1.B-Tree建立一個僞哈希索引

新增一個被索引的crc列，使用CRC32做哈希

資料表大可以使用CRC64或者MD5取部分值，也可以使用FNV64()函數作為哈希函數（來自Percona Server插件）

2.索引的有點

減少需要掃描的資料量

避免排序和臨時表

将随機I/O變為順序I/O

索引存儲實際列值

3.對于特大型表采用分區技術比建立索引更優

4.字首索引優缺點

優點：使索引更小、更快

缺點：無法做ORDER BY和GROUP BY，無法做覆寫掃描

5.字首索引的選擇性

SELECT COUNT(DISTINCT col)/COUNT(*) FROM table

SELECT COUNT(DISTINCT LEFT(col,n)/COUNT(*) FROM table

6.optimizer_switch關閉索引合并功能

7.組合索引選擇性

SELECT CONUNT(DISTINCT col1)/COUNT(*),

CONUNT(DISTINCT col2)/COUNT(*),

COUNT(*)

FROM table

SELECT COUNT(),SUM（col1=）,SUM(col2=)，SUM(col3=)FROM table

除了選擇性和基數的經驗法則外，還需要考慮WHERE子句中的排序、分組和範圍條件等因素

8.使用UUID作為主鍵會影響插入速度以及占用更多的存儲空間

9.OPTIMIZE TABLE 重建表并優化也填充

10.順序主鍵也會造成更壞的結果

高并發，InnoDB會造成明顯争用，可能導緻間隙鎖競争以及AUTO_INCREMENT鎖機制，此時可以考慮重新設計表或者應用，也可以更改innodb_autoinc_lock_mode配置

11.使用延遲關聯複用覆寫索引

先利用二級索引來查主鍵ID，再通過主鍵ID使用覆寫索引擷取資料

例如：

SELECT * FROM table

JOIN（

SELECT id FROM table

WHERE col=**

）AS T1 ON(t1.id=table.id)

12.組合索引前導列為常量時可以用組合索引排序

13.一些不能使用索引做排序的查詢

使用兩種不同的排序方向

引用了不在索引的列

查詢條件與排序條件無法組成最左綴

前導列不是常量

查詢多列時帶有範圍查詢

14.解決備援索引和重複索引方法

寫一些複雜的通路INFORMATION_SCHEMA表的查詢來找

使用Shlomi Noach的common_schema中的一些視圖來定位

使用PerconaToolkit中的pt-duplicate-key-checker

使用Percona工具箱中的pt-upgrade工具來仔細檢查計劃中的索引變更

使用Percona Server或MariaDB中先打開userstates伺服器變量，讓伺服器運作一段時間，再通過查詢INFOMATION_SHEMA.INDEX_STATISTICS查詢每個索引的使用率

使用Percona Toolkit中的pt-index-usage查詢索引的使用率

15.使用IN()複用組合索引

16.CHECK TABLE能夠找出大多數的表和索引的錯誤

17.ANALYZE TABLE重新生成統計資訊

18.OPTIMIZE TABLE重新整理資料

19.使用pt-query-digest的查詢審查“review”功能分析EXPLAIN出來的執行計劃

20.InnoDB所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大

21.盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化。因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。