细说mysql索引

mysql

mysql中读写的比例大概是10：1，有可能更高，我们大部分的场景都是读，很多复杂的查询，通过优化索引，可以达到几十倍甚至几百倍的查询速度效果。

索引的目的在于提高查询效率，举查字典的例子，如果要查“mysql”这个单词，在索引目录快速定位到m字母的那一页，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词找一遍才能找到你想要的。

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

创建索引

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...);

create index idx_name on ap_room (`uname`)
create index idx_uid_time on ap_room (`uid`,`time`)

（2）修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,...);

alter table ap_room add index idx_name (`uname`)
alter table ap_room add index idx_uid_time (`uid`,`time`)

（3）创建表时指定索引：CREATE TABLE 表名 ( [...], KEY 索引名 (列名1，列名 2,...) );

CREATE TABLE `ap_room` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`uname` char(255)  NOT NULL ,
`uid` int(16) NOT NULL ,
`time` int(10) NOT NULL ,
PRIMARY KEY (`id`),
KEY `idx_name` (`uname`),
KEY `idx_name_uid` (`uid`,`time`),
)

UNIQUE索引

表示唯一的，这个字段不能重复, 比如UID等信息。和创建普通索引一样，只是在前面加了一个UNIQUE关键字。

（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列1);

create unique index idx_name on ap_room (`uname`)
create unique index idx_uid_time on ap_room (`uid`,`time`)

（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列1);

alter table ap_room add unique index idx_name (`uname`)
alter table ap_room add unique index idx_uid_time (`uid`,`time`)

（3）创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE KEY 索引名 (列1) );

CREATE TABLE `ap_room` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`uname` char(255)  NOT NULL ,
`uid` int(16) NOT NULL ,
`time` int(10) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY idx_name (`uname`),
UNIQUE KEY idx_name_uid (`uid`,`time`),
)

主键：PRIMARY KEY索引

主键是也是一种唯一性索引，每个表只能有一个主键。申明的方式为为“PRIMARY KEY”。

（1）主键一般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。

CREATE TABLE `ap_room` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`uname` char(255)  NOT NULL ,
`uid` int(16) NOT NULL ,
`time` int(10) NOT NULL ,
PRIMARY KEY (`id`)
)

（2）修改表的方式加入主键：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。

alter table ap_room add PRIMARY KEY idx_name (`uname`)

主键相当于聚合索引，是查找最快的索引。

注：不能用CREATE INDEX语句创建PRIMARY KEY索引

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

索引的分类

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

mysql中用的最多的存储引擎是2种：MyISAM引擎 和 InnoDB引擎。

下表是 MyISAM、InnoDB引擎、Memory三个常用引擎类型的索引比较

现在我们大多数用的B-Tree索引。

索引的匹配原则

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为到c就停止了。如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

• 尽量选择区分度高的列作为索引,区分度的公式是count(DISTINCT(列))/count(*)，显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

比如，一个字段title。几乎每一条都不一样，根据公式：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM ap_room;
0.3039

那么title就很有必要简历索引。

还有一个字段template，只有1和2两个值：

SELECT count(DISTINCT(template))/count(*) AS Selectivity FROM ap_room;
0.0000

几乎等于0，那么这个字段也就没有必要建索引了。

• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。也不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表记录的时候，索引也会跟着更新的，所以索引越多，花的时间越长。

• 表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了;

  至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

• MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(xxx%)。%xx, %xx%, <>, not in, != 则不能使用索引。

explain 查询索引使用情况

一般我们写一个sql查询之前，会去explian他的索引使用情况以及得到一些有用的信息，好让我们优化。

这是我项目中用到的一个查询：

explain 
select roomid, short_id, uname, uid, area, title, live_time, online, attentions from room where on_flag=1 and round_status = 1 and live_time="0000-00-00 00:00:00" order by attentions desc limit 1000

得到下面的结果。我们一个一个来看一下：

• id 表示执行的序号，可以不用深究。

• select_type select子句的类型。一般分为以下类：

  (1) SIMPLE(简单SELECT,不使用UNION或子查询等)
  (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
  (3) UNION(UNION中的第二个或后面的SELECT语句)
  (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  (5) UNION RESULT(UNION的结果)
  (6) SUBQUERY(子查询中的第一个SELECT)
  (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
  (8) DERIVED(派生表的SELECT, FROM子句的子查询)
  (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• table 查询的表名

• type 表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

  ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
  index: Full Index Scan，index与ALL区别为index类型只遍历索引树
  range: 只检索给定范围的行，使用一个索引来选择行
  ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
  NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

• possible_keys 是指MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

• Key 这是最重要的，表示MySQL实际决定使用的索引

  如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

• key_len 索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

  不损失精确性的情况下，长度越短越好

• ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows 表示读取的行数。

• Extra 该列包含MySQL解决查询的详细信息,有以下几种情况：

  Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
  Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
  Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
  Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
  Impossible where：这个值强调了where语句会导致没有符合条件的行。
  Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

参考文件：

http://tech.meituan.com/mysql-index.html
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
http://thephper.com/?p=142
https://segmentfault.com/a/1190000003072424