MySQL前缀索引和索引选择性
来源:互联网 发布:中医 数据库 编辑:程序博客网 时间:2024/06/05 18:11
有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值(也称为基数,cardinality)和数据表的记录总数的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引的整个列。换句话说,前缀的”基数“应该接近于完整的列的”基数“。
为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。下面的示例是mysql官方提供的示例数据库
下载地址如下:
http://downloads.mysql.com/docs/sakila-db.zip
在示例数据库sakila中并没有合适的例子,所以从表city中生成一个示例表,这样就有足够数据进行演示:
mysql> select database(); +------------+| database() |+------------+| sakila |+------------+1 row in set (0.00 sec)mysql> create table city_demo (city varchar(50) not null); Query OK, 0 rows affected (0.02 sec)mysql> insert into city_demo (city) select city from city; Query OK, 600 rows affected (0.08 sec)Records: 600 Duplicates: 0 Warnings: 0mysql> insert into city_demo (city) select city from city_demo;Query OK, 600 rows affected (0.07 sec)Records: 600 Duplicates: 0 Warnings: 0mysql> update city_demo set city = ( select city from city order by rand() limit 1);Query OK, 1199 rows affected (0.95 sec)Rows matched: 1200 Changed: 1199 Warnings: 0mysql>
因为这里使用了rand()函数,所以你的数据会与我的不同,当然那不影响聪明的你。
首先找到最常见的城市列表:
mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10; +-----+--------------+| cnt | city |+-----+--------------+| 8 | Garden Grove || 7 | Escobar || 7 | Emeishan || 6 | Amroha || 6 | Tegal || 6 | Lancaster || 6 | Jelets || 6 | Ambattur || 6 | Yingkou || 6 | Monclova |+-----+--------------+10 rows in set (0.01 sec)mysql>
注意到查询结果,上面每个值都出现了6-8次。现在查找到频繁出现的城市前缀。先从3个前缀字母开始,然后4个,5个,6个:
mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;+-----+------+| cnt | pref |+-----+------+| 25 | San || 15 | Cha || 12 | Bat || 12 | Tan || 11 | al- || 11 | Gar || 11 | Yin || 10 | Kan || 10 | Sou || 10 | Bra |+-----+------+10 rows in set (0.00 sec)mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10; +-----+------+| cnt | pref |+-----+------+| 12 | San || 10 | Sout || 8 | Chan || 8 | Sant || 8 | Gard || 7 | Emei || 7 | Esco || 6 | Ying || 6 | Amro || 6 | Lanc |+-----+------+10 rows in set (0.01 sec)mysql> select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10; +-----+-------+| cnt | pref |+-----+-------+| 10 | South || 8 | Garde || 7 | Emeis || 7 | Escob || 6 | Amroh || 6 | Yingk || 6 | Moncl || 6 | Lanca || 6 | Jelet || 6 | Tegal |+-----+-------+10 rows in set (0.01 sec)
mysql> select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10; +-----+--------+| cnt | pref |+-----+--------+| 8 | Garden || 7 | Emeish || 7 | Escoba || 6 | Amroha || 6 | Yingko || 6 | Lancas || 6 | Jelets || 6 | Tegal || 6 | Monclo || 6 | Ambatt |+-----+--------+10 rows in set (0.00 sec)mysql>
通过上面改变不同前缀长度发现,当前缀长度为6时,这个前缀的选择性就接近完整咧的选择性了。甚至是一样的。
当然还有另外更方便的方法,那就是计算完整列的选择性,并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性:
mysql> select count(distinct city) / count(*) from city_demo;+---------------------------------+| count(distinct city) / count(*) |+---------------------------------+| 0.4283 |+---------------------------------+1 row in set (0.05 sec)mysql>
可以在一个查询中针对不同前缀长度的选择性进行计算,这对于大表非常有用,下面给出如何在同一个查询中计算不同前缀长度的选择性:
mysql> select count(distinct left(city,3))/count(*) as sel3, -> count(distinct left(city,4))/count(*) as sel4, -> count(distinct left(city,5))/count(*) as sel5, -> count(distinct left(city,6))/count(*) as sel6 -> from city_demo;+--------+--------+--------+--------+| sel3 | sel4 | sel5 | sel6 |+--------+--------+--------+--------+| 0.3367 | 0.4075 | 0.4208 | 0.4267 |+--------+--------+--------+--------+1 row in set (0.01 sec)mysql>
可以看见当索引前缀为6时的基数是0.4267,已经接近完整列选择性0.4283。
在上面的示例中,已经找到了合适的前缀长度,下面创建前缀索引:
mysql> alter table city_demo add key (city(6));Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>
mysql> explain select * from city_demo where city like 'Jinch%';+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | city_demo | range | city | city | 20 | NULL | 2 | Using where |+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql>
可以看见正确使用刚创建的索引。
前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:
mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。
参考资料
<<高性能MySQL第三版>>
- MySQL前缀索引和索引选择性
- MySQL前缀索引和索引选择性
- MySQL索引之前缀索引和索引选择性
- MySQL索引之前缀索引和索引选择性
- MySQL索引之前缀索引和索引选择性
- mysql前缀索引的索引选择性
- 前缀索引. 索引选择性
- 学习笔记--mysql索引(三) 独立的列,前缀索引和索引的选择性
- Mysql索引的选择性
- MySQL CARDINALITY确认索引选择性
- 索引选择性
- mysql 前缀索引
- 认识mysql前缀索引
- MySQL 前缀索引
- Mysql前缀索引
- mysql前缀索引
- mysql前缀索引
- MYSQL前缀索引
- 12.Scala中的继承:超类的构造、重写字段、重写方法代码实战
- 常用数组扩展
- mysql命令行不用输入用户名和密码的方法
- leetcode:Odd Even Linked List
- 分析if__name__==__main__
- MySQL前缀索引和索引选择性
- Python读写/追加excel文件Demo
- jira 安装 破解 汉化
- scikit-learn K近邻法类库使用小结
- Python多线程爬虫获取电影下载链接
- 模板:并查集
- 关于深浅拷贝
- 从输入url到显示页面发生了什么
- 浏览器缓存