第零步:简单说一说
有时候需要索引很长的字符(例如BLOB,TEXT,或者很长的VARCHAR),这样会使得索引又大又慢。
改良方法有:
1.改用哈希索引(这里不讲)。
2.使用字符串的前几个字符作为索引(即前缀索引)。
下面具体主要说第2种方法,主要思路就是选择足够长的前缀以保证较高的选择性,同时又不能太长(造成空间浪费)。
所谓选择性,是指不重复的索引数量除以总记录数,范围是(0,1],唯一索引之所以查询效率高,是因为它的选择性等于1。
首先要做的是准备好足够的数据来进行测试,最简单的方法是:我们刚安装好MySQL的时候,会有一个叫sakila的数据库,这个数据库可以方便我们进行各种练习。
下面我们直接开始行动~用Navicat打开sakila数据库(没有Navicat?那就命令行use sakila吧)
第一步:建立测试表及其数据
-- 新建一个测试表city_demo,并把表city的数据复制过去
INSERT INTO city_demo SELECT city FROM city;
-- 把表city_demo自身的数据复制5次,即反复执行下面这句语句5次
INSERT INTO city_demo SELECT city FROM city_demo;
-- 将表里面的城市名随机打乱(这一步生成的结果会与我之后展示的数据有差别,但并不影响分析)
UPDATE city_demo SET city = (SELECT city FROM city ORDER BY RAND() LIMIT 1);
第二步:计算合适的前缀索引长度
有两种方法计算长度
方法一:
-- 查询重复次数最多的10条完整城市名称及其数量(图1)
SELECT COUNT(*) cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
-- 查询重复次数最多的10条城市名称(前3个字符)及其数量,可以发现:前3个字符的相同数量过大,不适合做前缀索引(图2)
SELECT COUNT(*) cnt, LEFT(city,3) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
-- 查询重复次数最多的10条城市名称(前7个字符)及其数量,可以发现:前7个字符的相同数量和完整城市名称很相近了,可以考虑作为做前缀索引(图3)
SELECT COUNT(*) cnt, LEFT(city,7) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
图1:
方法二:
-- 计算出完整字符串的选择性(图4)
SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;
-- 计算各个前缀的选择性(图5),然后找出选择性与图4相近的
SELECT
COUNT(DISTINCT LEFT(city,3))/COUNT(*) pref3,
COUNT(DISTINCT LEFT(city,4))/COUNT(*) pref4,
COUNT(DISTINCT LEFT(city,5))/COUNT(*) pref5,
COUNT(DISTINCT LEFT(city,6))/COUNT(*) pref6,
COUNT(DISTINCT LEFT(city,7))/COUNT(*) pref7
FROM city_demo;
图4:
不过pref4和pref5是一个陷阱,因为它们看上去已经很接近完整字符串的选择性了,但是我们可以用方法一来看一下:
-- 结果看图6
SELECT COUNT(*) cnt, LEFT(city,4) pref4 FROM city_demo GROUP BY pref4 ORDER BY cnt DESC LIMIT 5;
-- 结果看图7
SELECT COUNT(*) cnt, LEFT(city,5) pref5 FROM city_demo GROUP BY pref5 ORDER BY cnt DESC LIMIT 5;
图6:
可以看出,前缀4和5的分布还是不均匀的以Sant、South为首的城市仍然比较多,结合方法一、二,可以建立长度为7的前缀索引了
第三步:建立前缀索引
ALTER TABLE `city_demo` ADD INDEX `idx_city` (`city`(7)) USING BTREE ;
-- 或者这个也行
ALTER TABLE `city_demo` ADD KEY `idx_city` (`city`(7))
-- 又或者直接用Navicat可视化操作也行
前缀索引的缺点
MySQL中无法使用前缀索引进行ORDER BY和GROUP BY,也无法用来进行覆盖扫描