前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)

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

作者头像
用户1177713
发布2018-02-24 10:56:46
2.7K0
发布2018-02-24 10:56:46
举报
文章被收录于专栏:数据之美

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1、两个同样结构的语句一个没有用到索引的问题:
  • 2、再看个例子:
  • 3、本质原因:Cardinality(索引基数)
  • 4、关于 mysql 索引优化与使用请见:
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档