mysql 组合索引的选择_MySQL索引选择及规则整理

相当于分别建立了

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的模式缺省是忽略大小写的！

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

MySQL索引选择及规则整理

标签：一点   姓名   key   index   VID   参数   like   结果   nbsp

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/fengxf/p/10791505.html