MySQL索引选择及规则整理

灰太狼 2022-03-17 11:14 293阅读 0赞

**索引选择性就是结果个数与总个数的比值。**

用sql语句表示为：

SELECT COUNT(\*) FROM table\_name WHERE column\_name/SELECT COUNT(\*) FROM table\_name

一般来说(例如书 “SQL Tuning“)，如果选择性超过 20% 那么全表扫描比使用索引性能更优。

但MySQL是没有计算索引的选择性的，只是预测逻辑IO操作的数量，因此对于MySQL索引要慎重选择。

举个栗子，tinyint类型的列，用以保存性别，就算用上“保密”和“变性”2项，选择性也最小也才25%，因此也就没有设置索引的必要了。

以下是整理出来的规则，以供参考：（由于很多实际项目都是查询操作占了绝大部分，因此不必太过呆板）

1、选择维度高的列

2、选择 where,on,group by,order by 中出现的列

3、选择较小的数据列，这样的索引文件更小，同时可装载更多的索引键

4、为较长的字符串使用前缀索引

5、组合索引能减低索引文件的大小，使用速度也优于多个单列索引

6、切勿滥用索引，因为除了磁盘空间的开销外，每次增删改都需要重新建立索引

7、索引不会包含有NULL值的列（若组合索引包含NULL值的列则整个组合索引无效）

另外，使用索引应当尽量避免 “OR” 、“否定查询” 、“模糊查询”、“NOT IN”、“<>” 等操作！

注意：前缀索引和组合索引是建立索引的一种技巧，并非索引类型。

**组合索引**

假设test表中有a，b，c三个列。

ALTER TABLE test ADD INDEX abc(a,b,c);

相当于分别建立了

a,b,c

a,b

a

这样的3组索引，也是“最左前缀”这个规则的结果。

举个使用该组合索引的栗子：

SELECT \* FROM test WHERE a="1" AND b="2" SELECT \* FROM test WHERE a="1"

以下则用不到索引：

SELECT \* FROM test WHERE b="1" AND c="2" SELECT \* FROM test WHERE c="1"

因此组合索引有一定优势，但在使用上需谨慎。

**前缀索引**

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

语法如下：

ALTER TABLE table\_name ADD KEY column\_name(prefix\_length);

建立前缀索引的关键在于"prefix\_length"这个参数，并且前缀索引的选择性上也有一点特殊。

前缀索引的选择性公式为：SELECT COUNT(DISTINCT column\_name)/COUNT(\*) FROM table\_name

继续举栗子！

现在有个user表，列 family\_name varchar(50) 保存的是英文姓氏（我也想用中文姓名来举例，但是不大适合，看下去就明白了。。。）

要取得设置前缀索引最理想的"prefix\_length"，我们首先要取得整列的选择性，如下：

SELECT COUNT(DISTINCT family\_name)/COUNT(\*) FROM user;

假设这里得到值是0.188。

然后我们继续去看看该列前1个字符的选择性又是多少

SELECT COUNT(DISTINCT LEFT(family\_name,1))/COUNT(\*) FROM user;

假设这里得到的结果是0.532，和整列的选择性出入太大，不可取，继续：

SELECT COUNT(DISTINCT LEFT(family\_name,2))/COUNT(\*) FROM user;

SELECT COUNT(DISTINCT LEFT(family\_name,3))/COUNT(\*) FROM user;

...

假设直接到“prefix\_length”为5时，得到的值为0.189，非常接近！

而取6时得到的值为0.18891，这个选择性和5并没有太大的偏差。

再结合减少索引文件大小的这个思路

“prefix\_length”值设置为5才是此处设置前缀索引的最优方案！

选择性讲完，还得再讲清楚这个前缀索引该怎么用！

书接上面的栗子~

正确的用法如下：

SELECT \* FROM user WHERE family\_name LIKE "lee%";

SELECT \* FROM user WHERE family\_name LIKE "david%";

以下则用不上该索引：  
SELECT \* FROM user WHERE family\_name LIKE "\_ee%";

SELECT \* FROM user WHERE family\_name LIKE "%en%";

SELECT \* FROM user WHERE family\_name LIKE "%ar%";

注意：SQL的模式缺省是忽略大小写的！

另外，“\_”代表一个字符，“%”代表任意多个字符！

发表评论取消回复

表情：

评论列表（有 0 条评论，293人围观）

还没有评论，来说两句吧...

相关阅读

相关 ⑩② 【MySQL索引】详解MySQL`索引`：结构、分类、性能分析、设计及使用规则。

索引(index)是帮助MySQL**高效获取数据的数据结构**(有序)：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据...

迈不过友情╰/ 2024年04月17日 10:17/ 0 赞/ 78 阅读

相关 mysql索引命中规则

最左匹配原则 1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。 2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。

￡神魔★判官ぃ/ 2022年07月14日 05:29/ 0 赞/ 252 阅读

相关 mysql 索引匹配规则

explain 中关键字 1. Index Key ：MySQL是用来确定扫描的数据范围，实际就是可以利用到的MySQL索引部分，体现在Key Length。 2.

àì夳堔傛蜴生んèń/ 2022年05月11日 09:14/ 0 赞/ 307 阅读

相关 MySql 索引的命中规则

在解释索引命中规则的前提下, 先了解一下如下原则: 最左匹配原则: 1. 最左前缀匹配原则, mysql会一只向右匹配直到遇到范围查询(>, <, between,

深藏阁楼爱情的钟/ 2022年04月23日 06:38/ 0 赞/ 589 阅读

相关 mysql索引命中规则

最左匹配原则 1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。 2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。

快来打我*/ 2022年04月11日 09:13/ 0 赞/ 251 阅读

相关 mysql索引创建规则、联合与一般索引、执行计划、索引选择，索引重建与下推

数据库索引怎么建,什么时候用到索引优缺点:为主键外建where子句建立索引可以加速数据库查询,但是索引占用内存,同时update和insert的时候需要同步修改;

左手的ㄟ右手/ 2022年04月05日 11:52/ 0 赞/ 633 阅读

相关 Mysql索引整理总结

Mysql索引整理总结 2018年07月18日 18:17:17 [每天都在变得更好的阿飞][Link 1] 阅读数：652 版权声明：本文为博主-阿飞(dufyun)

心已赠人/ 2022年04月02日 15:35/ 0 赞/ 170 阅读

相关 MySQL索引选择及规则整理

索引选择性就是结果个数与总个数的比值。用sql语句表示为： SELECT COUNT(\) FROM table\_name WHERE column\_name/SEL

灰太狼/ 2022年03月17日 11:14/ 0 赞/ 294 阅读

相关【Mysql】索引选择

选择普通索引还是唯一索引？对于查询过程来说： a、普通索引，查到满足条件的第一个记录后，继续查找下一个记录，直到第一个不满足条件的记录 b、唯一索引，由于索引唯一

朱雀/ 2022年03月14日 12:08/ 0 赞/ 228 阅读

相关 MySQL索引整理总结

原文地址：[MySQL索引整理总结][MySQL] 1. 简介索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。举例说明

梦里梦外;/ 2022年03月11日 11:22/ 0 赞/ 220 阅读