1.高性能索引策略

1.1獨立的列

“獨立的列”是指索引不能是表達式的一部分也不能使函數(shù)的參數(shù)

select name from actor where id+1=5;

這種情況索引失效。我們應(yīng)該簡化where條件始終將單獨的列放在比較符號的一側(cè)。

1.2前綴索引和索引選擇性

當(dāng)索引是很長的字符列的時候，會讓索引變得大且慢，除了模擬哈希索引的方法，通常選擇索引的部分開始字符，從而提高索引效率。但是這樣會降低索引的選擇性，索引的選擇性是指，不重復(fù)的索引值（也稱為基數(shù),cardinality）和數(shù)據(jù)表總記錄的比值,基數(shù)越大查詢效率越高，唯一索引的選擇性是1，這是最好的選擇性，性能也是最好的。
前綴索引的選擇應(yīng)當(dāng)保證較高的選擇性同時又不能太長。

• 計算列的完整性

select count(distinct rowname)/count(*) from table;

• 統(tǒng)計不同長度前綴的選擇性

select count(distinct left(rowname,num))/count(*) as selnum,
... 
from table;

• 創(chuàng)建前綴索引

alter table tablename add key (rowname(num));

在mysql中不能用前綴索引做order by 和 group by,也沒有辦法做覆蓋掃描。

1.3 多列索引

當(dāng)where有多個and,or條件的時候，應(yīng)當(dāng)創(chuàng)建多列索引而不是單列索引，應(yīng)當(dāng)將or改寫為union更加合適，當(dāng)explain中的extra 出現(xiàn)using union證明使用了索引合并，說明索引建立的很糟糕。

1.4選擇合適的索引列順序

當(dāng)不排序或者分組的時候，將選擇性最高的列放在前面通常的結(jié)果可能是不錯的。

1.4聚簇索引

在InnoDB引擎中表數(shù)據(jù)沒有什么聚集，應(yīng)當(dāng)盡量的使用自增主鍵(單調(diào)遞增的聚簇主鍵)按主鍵順序去插入數(shù)據(jù)，避免大量的隨機IO。

1.5覆蓋索引

如果一個索引包含所有需要查詢的字段值，我們就稱之為覆蓋索引。只有B樹索引能做覆蓋索引，因為覆蓋索引必須存儲索引列的值。并且不是所有的存儲引擎都支持覆蓋索引，例如menory就不支持覆蓋索引。使用覆蓋索引會帶來較大的性能提升，因為索引的條目要遠遠小于數(shù)據(jù)行大小，可以轉(zhuǎn)化隨機IO為順序IO,減少IO的次數(shù)。在InnoDB引擎中二級索引包含了主鍵值,所以即使索引字段不包含主鍵，依然能對主鍵值做覆蓋索引。

CREATE TABLE products (
 id int(11) NOT NULL AUTO_INCREMENT ,
 actor  varchar(30)   NOT NULL ,
 sur_name varchar(30) DEFAULT NULL ,
 name  varchar(30)   NULL DEFAULT NULL ,
 title  varchar(30)  S NOT NULL ,
 PRIMARY KEY (id),
 INDEX key_1 (actor, title) USING BTREE 
);

查詢

explain select * from products where actor='tom' and title='%asd%';

圖片.png

此時代表通過索引訪問表，看是否需要全表掃描，這時索引的信息推遲使用，必要時讀全表。因為雖然索引覆蓋了where中的字段，但是沒有覆蓋整個查詢的字段。所以還是會讀表，而且"%%"導(dǎo)致只能使用第一列索引。

優(yōu)化：

explain select id from products where actor='tom' and title like '%asd%';

圖片.png

EXPLAIN SELECT  * FROM products
JOIN (
    SELECT
        id
    FROM
        products
    WHERE
        actor = 'tom'
    AND title LIKE '%asd%'
) AS t1 ON (t1.id = products.id);

這里采用的方式是延遲關(guān)聯(lián) 。

圖片.png

1.6使用索引掃描做排序

當(dāng)explain的type列值為index則說明使用索引掃描做排序。
限制：

• 只有當(dāng)索引列順序和order by子句順序完全一致，并且所有列的排序方向一致時，才能使用索引對結(jié)果做排序。
• 如果查詢要關(guān)聯(lián)多張表，只有當(dāng)order by子句的字段全部為第一張表時才能用索引做排序。
• order by 子句也需要滿足最左前綴要求，否則不能做排序。
• 范圍查詢可能會導(dǎo)致索引排序失效
  當(dāng)索引為覆蓋索引時按索引排序的效率是高的。否則性能將會下降。

總結(jié)

1. 通常情況下我們使用我們使用選擇性高的字段作為索引的第一列，
   但是有時候某一個字段的選擇性不高但是卻經(jīng)常出現(xiàn)在where子句中，例如sex,這時我們可以選用sex做為索引字段的第一列，在不需要性別的時候通過sex in ('w','m'),過濾這個索引。但是要注意的是in()的列表不能太長。
2. 對于范圍查詢應(yīng)當(dāng)避免多個范圍條件，而使用IN(),但是濫用IN()會導(dǎo)致優(yōu)化器組合成指數(shù)形式增加，范圍條件應(yīng)當(dāng)建立在索引的最后一列。
3. 在同時使用order by 和 limit 查詢的時候即使使用了索引在翻頁到最后的面的時候，需要花費大量的時間丟棄數(shù)據(jù)，是一個嚴重的問題。

select <cols> from profiles where sex='m' order by rating limit 10000,10;

優(yōu)化此類索引應(yīng)當(dāng)使用延遲關(guān)聯(lián)

 select <cols> from profiles 
 join(
   select <primary key> from profiles 
   where sex='m' order by rating limit 10000,10;

 ) as t1 on(t1.<primary key>=profiles.<primary key>); 

4. 減少索引和數(shù)據(jù)碎片

optimize table

對于不支持optimize table的存儲引擎可以先改為InnoDB執(zhí)行后，再修改回去。