MySQL索引

使用索引的目的

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

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

索引的原理

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

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

建立索引的几大原则

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

1. 最左前缀匹配原则，非常重要的原则。mysql会一直向右匹配直到遇到范围查询>、<、between、like就停止匹配，比如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)复合索引为例,它内部结构简单说就是下面这样排列的：

name	cid
a	6
c	4
c	5
h	1
z	9

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

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

那么什么时候才能用到呢?当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是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工作情况。