几个常见的Mysql索引问题

1. 选择性较低的列是否适合加索引?

索引选择性等于列中不重复(distinct)的行数量(也叫基数),与记录总数的比值。范围在0-1之间。数值越大,索引越快。
例如主键是唯一的,不重复的,所以选择性=1。
常见的选择性较低的列,例如是否热门,要不1,要不0。选择性等于2/记录总数,所以是非常低的。

而这种列适合加索引吗?
答案是要区分看待:
假如表中是否热门is_hot=1的行有100行,is_hot=0的行有100w行。
那么
如果需求是查询is_hot=1的行,我们设置索引idx(is_hot)是有用的
如果需求是查询is_hot=0的行,我们设置索引就没什么用了。

测试:
100w的表,type=1的有11行,其他都是type=2
没有设置type索引,搜索type=1,要检索100w行,也就是全表扫描,用时0.7s
加了type索引,搜索type=1,只要检索11行,用时0.04。搜索type=2,也是要检索100w行,用时0.7s,也是差不多全部扫描

2.Mysql的索引是否会自动加上主键

假如ID是主键,索引a(SongType)和索引b(SongType,ID)是否等价,也就是Mysql是否会为索引加上主键ID?
答案是不会的

测试:
有100w数据,前50w SongType=1,后49w SongType是2,最后1w SongTYpe=1,ID是主键
有索引a(SongType) 。
sql :select * from table where id>500000 and SongType=1
会使用主键索引,需要检索行50w行。用时0.5s
加索引b(SongType,ID)后,会使用索引b,只需要扫描1w行数据。用时0.02s
所以索引a,并不会包含主键

3. 多列排序时使用索引的坑

mysql文档

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

根据Mysql文档的说明,创建索引的时候,可以加上asc或者desc,例如:add index idx(a asc,b desc).但是实际Mysql是会忽略的(好坑。。。)好像8.0版本之后支持desc了。

这会有什么影响呢?

假如有列test1和test2,都是int类型。

我们创建索引`idx1 (test1,test2)`,
假如我们要按test1和test2排序,例如SQL

explain select * from table order by test1 ,test2 limit 1;

可以使用索引的排序:

  • order by test1
  • order by test1 desc
  • order by test1,test2
  • order by test1 desc,test2 desc

不可以使用索引的排序:

  • order by test1,test2,desc
  • order by test1 desc,test2

因为索引不支持desc,所以多列的索引是按全部列的升序存储的。所以只排序一列,全部列升序,全部列降序,都能用索引。但是第一列用升序,第二列用降序,或者第一列降序,第二列用升级,都不能使用索引。

未经同意,请不要转发

posted @ 2019-04-16 15:30 Xjng 阅读(...) 评论(...) 编辑 收藏