怎么理解并掌握mysql索引之前缀索引

本篇内容主要讲解“怎么理解并掌握MySQL索引之前缀索引”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么理解并掌握mysql索引之前缀索引”吧!

企业建站必须是能够以充分展现企业形象为主要目的，是企业文化与产品对外扩展宣传的重要窗口，一个合格的网站不仅仅能为公司带来巨大的互联网上的收集和信息发布平台，成都创新互联公司面向各种领域：塔吊租赁等网站设计、全网营销推广解决方案、网站设计等建站排名服务。

有时候需要很长的索引字符串,这样会使得索引变的很大而且很慢.通常可以索引开始的部分字符,这样可以大大节省空间提升索引效率,但这样也会降低索引的选择性.索引的选择性是指,不重复的索引值和数据表的记录总数的比值,范围从1#T到1之间.索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤掉更多的行,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.

    一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能.对于BLOB,TEXT或者很长的varchar类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度.

    诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长.前缀长的选择性接近于索引整个列.换句话说,前缀的基数应该接近于完整列的基数.

    为了决定前缀合适长度,需要找到最常见值的列表,然后和最常见的前缀列表进行比较.

如下构建一张表:

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table city_demo(city varchar(50) not null);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into city_demo(city) select city from city;
Query OK, 600 rows affected (0.11 sec)
Records: 600  Duplicates: 0  Warnings: 0
mysql> insert into city_demo(city) select city from city_demo;
Query OK, 600 rows affected (0.09 sec)
Records: 600  Duplicates: 0  Warnings: 0
mysql> update city_demo set city=(select city from city order by rand() limit 1);
Query OK, 1196 rows affected (0.85 sec)
Rows matched: 1200  Changed: 1196  Warnings: 0

有了数据集,数据分布不是真实分布,仅为演示.首先找到最常见的城市列表:

mysql> select count(*) as cnt,city from city_demo group by city order by cnt desc limit 10;
+-----+-------------------------+
| cnt | city                    |
+-----+-------------------------+
|   7 | Oshawa                  |
|   7 | Uijongbu                |
|   7 | Ktahya                  |
|   6 | Haiphong                |
|   6 | Berhampore (Baharampur) |
|   6 | Urawa                   |
|   6 | Mysore                  |
|   6 | Witten                  |
|   6 | Sunnyvale               |
|   6 | Esfahan                 |
+-----+-------------------------+
10 rows in set (0.01 sec)

如上每个值都出现了6-7次,现在找出最频繁出现城市的前缀,先从前缀字母开始:

mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  28 | San  |
|  16 | Cha  |
|  14 | Hal  |
|  12 | al-  |
|  11 | Bat  |
|  11 | Shi  |
|  10 | Val  |
|  10 | Ben  |
|  10 | Bra  |
|   9 | Tar  |
+-----+------+
10 rows in set (0.00 sec)

每个前缀出现的都比原来城市次数多,因此唯一前缀比唯一城市要少得多,然后增加前缀长度,直到这个前缀的选择性接近完整列的选着性,计算合适前缀长度的一个办法计算完整列的选择性,并使前缀的选择性趋于完整列的选择性.如下计算完整列的选择性:

mysql> select count(distinct city)/count(*) from city_demo;
+-------------------------------+
| count(distinct city)/count(*) |
+-------------------------------+
|                        0.4300 |
+-------------------------------+
1 row in set (0.01 sec)

计算前缀选择性趋于或接近0.43这个值:

mysql> select count(distinct left(city,3))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,3))/count(*) |
+---------------------------------------+
|                                0.3350 |
+---------------------------------------+
1 row in set (0.01 sec)
mysql> select count(distinct left(city,4))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,4))/count(*) |
+---------------------------------------+
|                                0.4058 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct left(city,5))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,5))/count(*) |
+---------------------------------------+
|                                0.4208 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct left(city,6))/count(*) from city_demo;
+---------------------------------------+
| count(distinct left(city,6))/count(*) |
+---------------------------------------+
|                                0.4267 |
+---------------------------------------+

查询显示当前缀长度达到5的时候,再增加长度,选择性提升幅度已经不大.

只看平均选择性是不够的,也有列外情况,需要考虑最坏情况下的选择性,平均选择性会让你认为前缀长度为3或4的索引已经足够,但是如果数据分布很不均匀就会有陷阱.

上面示例如果找到合适前缀长度,下面示例如何创建前缀索引:

mysql> alter table city_demo add key(city(5));
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

到此，相信大家对“怎么理解并掌握mysql索引之前缀索引”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！