mysql設定合適的索引長度

標籤：

 

理想的索引：

1. 相對於寫操作來說，表查詢很頻繁的表建立索引

2. 欄位區分度高

3. 長度小（合適的長度，不是越小越好）

4. 盡量能夠覆蓋常用欄位

 

這些條件綜合起來才能夠達到最優索引，本次我們著重聊一下建立合適長度的索引，索引的長度直接影響索引檔案的大小，因此會影響增刪改查的速度

給字元類型的欄位設定長度欄位查詢時區分度要高，如果欄位只是設定了一個那麼回查詢很多相似的匹配度不高，長度要恰到好處，否則太長索引檔案就會大，因此

要在區分度和長度上做一個平衡。

1.先來看一下沒設定索引的查詢

mysql> explain select id,title from b2b_goods where title="測試商品";

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | b2b_goods | ALL  | NULL          | NULL | NULL    | NULL | 5061 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

總結：發現該語句沒有使用到索引，掃描了5061條資料

 

2.給title欄位建立索引

mysql> alter table b2b_goods add index index_title(`title`);

Query OK, 0 rows affected (0.19 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> explain select id,title from b2b_goods where title="測試商品";

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+--------------------------+

| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra                    |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+--------------------------+

|  1 | SIMPLE      | b2b_goods | ref  | index_title   | index_title | 150     | const |    1 | Using where; Using index |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

總結：發現該語句掃描條數降低了成了一條，說明是對的，但發現索引長度（key_len）過長這樣當更新時是比較占記憶體的。

 

3.設定區分度高的並且長度適合的索引

習慣的演算法：

select count(distinct left(`title`,num))/count(*) from b2b_goods;

這裡num是指截取的長度，實際上也可以發現設定該長度的查詢度，比例越大說明越良好

（1）.設定6個長度

mysql> select count(distinct left(`title`,6))/count(*) from b2b_goods;

+------------------------------------------+

| count(distinct left(`title`,6))/count(*) |

+------------------------------------------+

|                                   0.7718 |

+------------------------------------------+

1 row in set (0.01 sec)

 

（2）.設定13個長度

mysql> select count(distinct left(`title`,13))/count(*) from b2b_goods;

+-------------------------------------------+

| count(distinct left(`title`,13))/count(*) |

+-------------------------------------------+

|                                    0.8288 |

+-------------------------------------------+

1 row in set (0.01 sec)

 

（3）.設定25個長度

mysql> select count(distinct left(`title`,25))/count(*) from b2b_goods;

+-------------------------------------------+

| count(distinct left(`title`,25))/count(*) |

+-------------------------------------------+

|                                    0.8562 |

+-------------------------------------------+

1 row in set (0.01 sec)

（4）.設定30個長度

mysql> select count(distinct left(`title`,30))/count(*) from b2b_goods;

+-------------------------------------------+

| count(distinct left(`title`,30))/count(*) |

+-------------------------------------------+

|                                    0.8573 |

+-------------------------------------------+

1 row in set (0.01 sec)

（5）.設定35個長度

mysql> select count(distinct left(`title`,35))/count(*) from b2b_goods;

+-------------------------------------------+

| count(distinct left(`title`,35))/count(*) |

+-------------------------------------------+

|                                    0.8573 |

+-------------------------------------------+

1 row in set (0.01 sec)

總結：發現設定欄位的長度剛開始越來越大的時候匹配度高，到一定值變化就地區平緩，發現截取30個和35個沒什麼區分，綜上所述並保持適當的長度
我們建立一個長度為25的索引

4.建立區分度高長度適中的索引

alter table b2b_goods  drop index index_title;

alter table b2b_goods add index index_title(`title`(25));

mysql>  explain select id,title from b2b_goods where title="測試商品";

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra       |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

|  1 | SIMPLE      | b2b_goods | ref  | index_title   | index_title | 75      | const |    1 | Using where |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

總結：跟之前沒有設定長度的比較key_len由150減少為75，掃描條數依然是一條，相比之下這個長度是優秀的索引長度。

 

mysql設定合適的索引長度