欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

几个常见的Mysql索引问题

程序员文章站 2023-02-26 13:36:43
1. 选择性较低的列是否适合加索引? 索引选择性等于列中不重复(distinct)的行数量(也叫基数),与记录总数的比值。范围在0 1之间。数值越大,索引越快。 例如主键是唯一的,不重复的,所以选择性=1。 常见的选择性较低的列,例如是否热门,要不1,要不0。选择性等于2/记录总数,所以是非常低的。 ......

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. 多列排序时使用索引的坑

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,所以多列的索引是按全部列的升序存储的。所以只排序一列,全部列升序,全部列降序,都能用索引。但是第一列用升序,第二列用降序,或者第一列降序,第二列用升级,都不能使用索引。

未经同意,请不要转发