关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)

1、两个同样结构的语句一个没有用到索引的问题:

查1到20号的就不用索引,查1到5号的就用索引,为什么呢?不稳定?

mysql> explain select * from test where f_submit_time between '2009-09-01' and '2009-09-20' \G; 

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test

         type: ALL

possible_keys: PRIMARY,submit_time_index

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 365628

        Extra: Using where

1 row in set (0.02 sec)

mysql> explain select * from test where f_submit_time between '2009-09-01' and '2009-09-5' \G;  

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test

         type: range

possible_keys: PRIMARY,submit_time_index

          key: submit_time_index

      key_len: 8

          ref: NULL

         rows: 52073

        Extra: Using where

1 row in set (0.00 sec)

说明:

二叉树索引本来最适合的就是点查询,和小范围的range查询,

当预估返回的数据量超过一定比例( 貌似当预估的查询量达到总量的30% )的时候,

再根据索引一条一条去查就慢了,反而不如全表扫描快了。Mysql有自己内部自动优化机制,

但有些自动优化机制可能不是最优的。这时候就需要人工去干预。

比如长期不优化表,Mysql判断出索引不优,就会不使用索引。

有时候就要人工强制使用真正高效的索引(FORCE INDEX)。

其实当本身的查询就约等于一个全表查询的时候,强不强制使用索引基本上没什么效果。

2、再看个例子:

    今天遇到一个奇怪的问题,明明已经建立了索引,select语句的explain也表明会利用这个索引,可是结果偏偏没有用索引,最后扫描了全表。     两个结构完全一样的sql语句:

     sql1: select * from table where col_a = 123 and col_b in (‘foo’,\'bar’) order by id desc;     sql2: select * from table where col_a = 456 and col_b in (‘foo’,\'bar’) order by id desc;     结果sql1选择利用了col_a的索引,速度很快,sql2利用了主键ID的索引,扫描了全表(40w行)。     仔细分析,发现数据库中,col_a=456的记录数有近1万条,而col_a=123的记录数只有几条。     于是就清楚了,mysql选择索引不仅仅依据查询结构和索引结构,还会根据索引大概估算选择每种索引的数据量,然后选择他认为最快的索引。     可能是主键索引会比普通index更快,所以mysql最后选择了数据量跟大的id索引。     那么,如何解决这个问题呢?      很简单,只要在order语句里写多个键即可,比如:order by col_a, id desc

REF:mysql查询中利用索引的机制  http://blogread.cn/it/article/5023?f=wb

3、本质原因:Cardinality(索引基数)

很关键的一个参数,平均数值组=索引基数/表总数据行,平均数值组越接近1就越有可能利用索引。

索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。    高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1。

4、关于 mysql 索引优化与使用请见:

由浅入深探究mysql索引结构原理、性能分析与优化

http://my.oschina.net/leejun2005/blog/73912

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏happyJared

爬虫进阶:Scrapy抓取慕课网

  完整的爬虫流程大致是这样的:分析页面结构 -> 确定提取信息 -> 设计相应表结构 -> 编写爬虫脚本 -> 数据保存入库;入库可以选择mongo这样的文档...

3424
来自专栏后端技术探索

MySQL优化的奇技淫巧之STRAIGHT_JOIN

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下:

1081
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4646
来自专栏一个爱吃西瓜的程序员

学习SQL【6】-复杂查询

到目前为止,我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上,学习一些实际应用的方法。 一:视图 1:视图和表 表中存...

3419
来自专栏企鹅号快讯

数据分析师必备的数据提取技能

数据分析师必备技能SQL 在数据分析的整个流程中,数据获取是不可或缺的一环,那么作为数据分析师,我们不仅仅需要了解如何获取二手数据,还必须掌握如何从数据库中获取...

28310
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2048
来自专栏数据和云

SQL优化:紧急情况下提高SQL性能竟是这样实现的!

作者 | 黄堋 ,多年一线 Oracle DBA 经验,长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。

936
来自专栏咸鱼不闲

mysql一对多查询合并多的一方的数据。

有时候会有这样一个需求, 查询的一条记录需要包含另一个表的多条记录,并且让多条记录成为一个字段组成最终的一条记录。比较难描述,看例子吧。

1953
来自专栏杨建荣的学习笔记

一条SQL语句的执行计划变化探究(r10笔记第3天)

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落...

3296
来自专栏Grace development

老项目重构手记之用户系统

重构首先要注意几个点 – 重构后功能的可扩展性 – 业务互相依赖的复杂度 – 脱离本身的业务进行重构 – 重构后的代码可读性与可维护性 – 性能的提升...

1222

扫码关注云+社区

领取腾讯云代金券