MySQL索引之单列索引

最新推荐文章于 2024-08-31 23:40:08 发布

原创最新推荐文章于 2024-08-31 23:40:08 发布 · 985 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #单列索引 #前缀索引 #索引选择性 #mysql索引

Mysql 专栏收录该内容

10 篇文章

订阅专栏

前提:根据反馈，需要优化查询速度比较慢的sql，架构师聊到组合索引。为了扩展知识，下载了《高性能MySQL》pdf及时充电一下。

1.概念

单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引;

2.多个单列索引

1.当查询where条件只有一个时,完全可以利用单列索引,因为这样速度可能会提高;

2.当涉及到的业务需要经常查询多个组合列,不要分别基于单个列建立多个单列索引(why?)

原因:因为就算有多个单列索引,mysql也只会选择其中那个它认为最有效率的单列索引.

举例:表名及表结构(PlanDoctorID:值班医生ID)

添加测试索引:

ALTER TABLE `table_name` ADD INDEX `index_name` ( `column` );//或者直接在navicat添加

>mysql执行计划详解.

分别在PlanDoctorID、DepartID添加索引:

查看执行sql的执行计划,可见mysql只选择走PlanDoctorID的索引:

3.前缀索引和索引选择性

当涉及到大且慢的索引,通常可以索引开始的部分字符,这样可以大大的节约索引空间,从而提高索引效率.但是会降低索引的选择性.

索引选择性:不重复的索引(基数)和数据表的记录总数(#T)的比值,范围1/#T - 1.

（注:索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行.）

一般情况下某个列前缀的选择性也是足够高,也是足以满足查询性能.对于BLOB、TEXT或很长的Varchar类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度.

诀窍在于要选择足够长的前缀保证较高的选择性,同时又不能太长(以便节约空间),那如何决定前缀的合适长度呢?

使用如下的sql去计算某个字段完整列的选择性:

select count(distinct [column]) / count(*) as lenFull from [tableName]

使用如下的sql去计算某长度的前缀索引的选择性:

select count(distinct left ([column], 4)) / count(*) as len4 ,   
 count(distinct left ([column], 5)) / count(*) as len5 ,  
 count(distinct left ([column], 6)) / count(*) as len6 ,  
 count(distinct left ([column], 7)) / count(*) as len7 ,  
 count(distinct left ([column], 8)) / count(*) as len8 from [tableName]

示例:

城市列表测试数据：