MySQL索引

使用索引的目的

使用索引的目的是提高数据库查询的效率。索引是怎么提高数据库查询的效率的呢?举个通俗的例子,查字典。数据库中的数据就好比新华字典中的词条,索引就是新华字典的目录。没有建立索引的数据库就好像被撕掉目录的新华字典,只能从头到尾一条一条地查询,效率极其低下。

为了能更快地查字典,哦不,是查询数据库,我们就需要为数据库建立索引。

索引的原理

索引的主要思想是将数据分段,从而减少查询时的无效数据,提高查询效率。比如有1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。

当然,上面只是主要思想,MySQL使用了更加具体的数据结构来实现索引。本文不对索引的数据结构展开讨论。

建立索引的几大原则

如何建立合适的索引,从而最大程度地优化查询效率是一件需要精心设计的事情。本节只介绍几个建立索引时须遵循的原则。

  1. 最左前缀匹配原则,非常重要的原则。mysql会一直向右匹配直到遇到范围查询><betweenlike就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。关于最左前缀匹配原则,在最左前缀匹配原则一节有详细说明。
  2. 尽量选择区分度高的列作为索引,区分度公式为count(distinct col)/count(*),即一列中内容不同的记录数占总记录数的比例。通过这个公式我们可以得到字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0。所以唯一索引的效率是最高的。在不是唯一键的时候,就要具体情况具体分析了,这也是索引设计的关键点之一。
  3. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,索引的数据结构中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  4. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
  5. 使用短索引。如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做 。
    例如,如果有一个 CHAR(200) 列,如果在前 10 个或 20 个字符内,多数值是惟一的,
    那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空
    间,也可能会使查询更快。

最左前缀匹配原则

最左前缀使用场景是使用复合索引的时候。当使用复合索引时,如果想要索引有效,where之后的表达式就要满足最左前缀匹配原则。

我总结了一下最左前缀匹配的特点,就是从表达式最左边开始,到第一个范围查询结束,在这个闭区间内的字段应该是索引字段的最左前缀

最左前缀

在这里解释一下最左前缀,因为没有百度到感觉比较靠谱的解释,所以我在这里谈一下我的理解,仅供参考。

前缀应该不用解释了,学过英语的都该懂点。前缀加个就是左前缀了,表示从左边开始查找的前缀。但是,这个左只是表示查找的顺序是从左边开始,不是从右边开始,并没有说从哪一位开始,可能是第一位,也可能是第三位。这时候再加一个 就表示了从最左边开始。

比如复合索引是(a,b,d,c),那么查询时表达式的字段顺序为(a)、(a,b)、(a,b,d)、(a,b,d,c)的这些都是它的最左前缀,而(b)、(a,d)、(a,b,c)、(a,b,c,d)这些就不是。

关于MySQL的查询优化器

最左前缀的概念我们已经明白了,但是在MySQL中的情况又有点不同。比如我们发现,索引的顺序是(a,b,d,c),我们的查询条件是这样写的:where b=10 and c=16 and a=26 and d=0,从严格意义上来讲,这个顺序是不符合最左前缀匹配原则的,但是MySQL的确使用索引完成了查询。这是怎么回事呢?这是因为MySQL的查询优化器帮我们调整了查询条件的顺序。MySQL查询优化器会判断纠正一条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。在有索引的情况下当然是利用索引查询顺序的效率最高咯,所以,MySQL查询优化器会最终以索引的顺序进行查询执行。

最左前缀匹配原则的原理

上面说了这么多的最左前缀的概念,那么我们到底为什么一定要符合最左前缀匹配原则呢?

因为复合索引只有第一个字段是绝对有序的,从第二个开始的字段都只是相对前一个字段有序,在全局范围内是无序的。只有满足最左前缀原则,才可以保证查询内容的有序,而有序又是索引使用的前提。

我们来看个例子,以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:

namecid
a6
c4
c5
h1
z9

MySQL创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个cid字段进行条件判断是用不到索引的。

那么什么时候才能用到呢?当然是cid字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。发现没有,观察两个name名字为 ccid字段是不是有序的呢。从上往下分别是4 5。这也就是MySQL索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因(最左前缀匹配原则)。


MySQL常用索引

1.主键索引

  • 主键索引必定是唯一索引,且不允许空值
    create table `example1` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例1';

2.唯一索引

  • 除开取值唯一以外,与普通索引没有区别
    create table `example2` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uniq_sid` (`student_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例2';

3.普通索引

  • 建立索引应在区分度大的字段上,不要在低基数列上建立索引,比如 性别
    create table `example3` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
      `grade` int unsigned NOT NULL DEFAULT 0 COMMENT '分数',
      PRIMARY KEY (`id`),
      KEY `idx_grade` (`grade`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例3';

4.组合索引

  • 区分度大的列放在前,有范围性的列放在后
  • 联合索引(a, b, c)相当于(a), (a, b ), (a, b, c)
  • 合理使用索引覆盖减少IO,避免排序
  • 单个索引字段不应超过5个
    create table `example3` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
      `grade` int unsigned NOT NULL DEFAULT 0 COMMENT '分数',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `idx_sid_grede_ctime` (`student_id`, `grade`, `create_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例3';

Mysql索引与优化

1. 加索引

聚集索引和非聚集索引

聚集索引和非聚集索引的区别有哪些_百度经验

聚集索引:数据存储顺序与索引存储顺序一致

非聚集索引:存储顺序和索引顺序不一致

2.分表 

(1)水平分表。比如按照月份,地狱等

(2)垂直分表。分字段,有的字段数据量很大,比如试卷内容,日志内容等。可以跟原表ID相对应,一对一的分离出来。

3.减少不必要的表关联

4. 大批量插入前先删除索引,插入后再建立索引,效果要比直接插入好的多。

5.报表统计,将结果集作为列处理

6.用中间件实现读写分离。写用一个库,读用其他的库。

7.用explain 看SQL工作情况。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值