大家好，又见面了，我是你们的朋友全栈君。
目标
- 明确前缀索引使用场景；
- 掌握创建前缀索引的语法；
- 掌握计算索引选择性的方法。
定义
对于字符串列，可以使用语法指定索引前缀长度来创建仅使用列值开头的索引。
语法：
CREATE INDEX 自定义索引名称 ON 表名字(字段名(截取该字段的位数));
举例：
/*为pharmacy表创建一个名字为name_of_drug_10的前缀索引，这个索引截取了name_of_drug字段的前10位。*/
CREATE INDEX name_of_drug_10 ON pharmacy (name_of_drug(10));场景
如果需要对BLOB和TEXT类型的列创建索引，则只能创建前缀索引。
使用方法
通过索引选择性来确定前缀索引截取的字节位数，索引的选择性指不重复的索引值与数据总量的比值。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。
- 案例
/*给pharmacy表建立以name_of_drug字段为前缀的索引，先来计算索引选择性。*/
SELECT
COUNT(DISTINCT LEFT(name_of_drug,3))/COUNT(1) AS selectivity3,
COUNT(DISTINCT LEFT(name_of_drug,4))/COUNT(1) AS selectivity4,
COUNT(DISTINCT LEFT(name_of_drug,5))/COUNT(1) AS selectivity5,
COUNT(DISTINCT LEFT(name_of_drug,6))/COUNT(1) AS selectivity6,
COUNT(DISTINCT LEFT(name_of_drug,7))/COUNT(1) AS selectivity7,
COUNT(DISTINCT LEFT(name_of_drug,8))/COUNT(1) AS selectivity8,
COUNT(DISTINCT LEFT(name_of_drug,9))/COUNT(1) AS selectivity9
FROM pharmacy;- 结果集
![优化MySQL前缀索引[通俗易懂]](https://img-blog.csdnimg.cn/20200913172559795.png)
![优化MySQL前缀索引[通俗易懂]](https://javaforall.net/wp-content/uploads/2020/11/2020110817443450.jpg)
- 结论
截取name_of_drug前6个字节进行对比就已经有99.74%的数据不一样了，所以可以选择name_of_drug前6个字节为前缀创建前缀索引：
CREATE INDEX name_of_drug_6 ON pharmacy (name_of_drug(6));注意
索引的选择性在80%以上适合建立，否则不建议建立索引，例如性别等。
前缀支持和前缀长度（如果支持）取决于存储引擎。例如，对于InnoDB 使用REDUNDANT 或 COMPACT 行格式的表，前缀的最大长度为767个字节。对于InnoDB使用DYNAMIC或COMPRESSED 行格式的表，前缀长度限制为3072字节。对于MyISAM表，前缀长度限制为1000个字节。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/142343.html原文链接：https://javaforall.net
