前缀索引

前言:

    前缀索引有助于提高索引效率。索引选择性越高则查询效率越高。

   我们应该选择合适长度的前缀索引，保证索引选择性，又不会增加索引空间。

   适用场景:对于blog,text,varchar类型的列，必须使用前缀索引来保证索引的查询效率.

  

   如何计算合适的前缀索引长度:

          完整列的选择性=前缀索引的选择性

  demo:

         首先计算出完整列的选择性

select count(distinct card_id)/count(*) from card
--------------

+----------------------------------+
| count(distinct card_id)/count(*) |
+----------------------------------+
|                           1.0000 |
+----------------------------------+

1 row in set (14.72 sec)

计算前缀索引的选择性

select count(distinct left(card_id,5))/count(*),
count(distinct left(card_id,6))/count(*),
count(distinct left(card_id,7))/count(*),
count(distinct left(card_id,8))/count(*) from card
--------------


+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
| count(distinct left(card_id,5))/count(*) | count(distinct left(card_id,6))/count(*) | count(distinct left(card_id,7))/count(*) | count(distinct left(card_id,8))/count(*) |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
|                                   0.0100 |                                   0.1000 |                                   1.0000 |                                   1.0000 |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+
1 row in set (3 min 4.62 sec)

总结:当前缀索引长度为7时，更接近完整列的选择性.而当索引长度为8时，选择性增加不明显.所以索引长度应设置为7.

mysql> alter table card add index(card_id(7));
--------------
alter table card add index(card_id(7))
--------------


Query OK, 0 rows affected, 2 warnings (1 min 21.63 sec)
Records: 0  Duplicates: 0  Warnings: 2


mysql> show index from card;
--------------
show index from card
--------------


+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| card  |          1 | card_id  |            1 | card_id     | A         |     9719226 |        7 | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)