- 1.什么是前缀索引
说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。
那么为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性。
- 2.什么是索引选择性
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记
录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
一个索引的选择性越接近于1，这个索引的效率就越高。
通过如下 SQL 得到全列选择性：
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
再通过如下 SQL 得到某一长度前缀的选择性：
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
- 3.SQL优化实战
数据下载:https://github.com/datacharmer/test_db
MySQL官方文档中提供的示例数据库之一：employees。
使用命令导入数据:
mysql -uroot -padmin -t < employees.sql
要优化的SQL:
EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
执行后结果:
索引选择性判断SQL:
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
执行后结果:
存在问题:
使用前缀索引优化：
前缀索引SQL:
SELECT
count(
DISTINCT (
concat(
first_name,
LEFT ( last_name, 5 ))))/ count(*) AS Selectivity
FROM
employees.employees;
经过不断尝试,发现5这个值是最接近1的。
添加索引SQL:
ALTER TABLE employees.employees ADD INDEX `first_name_last_name5` (first_name, last_name(5));
查看结果:
优化后的效率:
如果想删除索引，可执行如下SQL:
DROP INDEX first_name_last_name5 ON employees;
欢迎小伙伴们点赞转发.
