[关闭]
@phper 2017-04-25T09:38:17.000000Z 字数 5534 阅读 453

细说mysql索引

mysql


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

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

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

创建索引

普通索引

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

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

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

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

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

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

  1. CREATE TABLE `ap_room` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT ,
  3. `uname` char(255) NOT NULL ,
  4. `uid` int(16) NOT NULL ,
  5. `time` int(10) NOT NULL ,
  6. PRIMARY KEY (`id`),
  7. KEY `idx_name` (`uname`),
  8. KEY `idx_name_uid` (`uid`,`time`),
  9. )

UNIQUE索引

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

(1)创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列1);

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

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

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

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

  1. CREATE TABLE `ap_room` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT ,
  3. `uname` char(255) NOT NULL ,
  4. `uid` int(16) NOT NULL ,
  5. `time` int(10) NOT NULL ,
  6. PRIMARY KEY (`id`),
  7. UNIQUE KEY idx_name (`uname`),
  8. UNIQUE KEY idx_name_uid (`uid`,`time`),
  9. )

主键:PRIMARY KEY索引

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

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

  1. CREATE TABLE `ap_room` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT ,
  3. `uname` char(255) NOT NULL ,
  4. `uid` int(16) NOT NULL ,
  5. `time` int(10) NOT NULL ,
  6. PRIMARY KEY (`id`)
  7. )

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

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

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

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

删除索引

  1. DROP INDEX index_name ON talbe_name
  2. ALTER TABLE table_name DROP INDEX index_name
  3. ALTER TABLE table_name DROP PRIMARY KEY

索引的分类

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

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

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

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-text 索引 不支持 暂不支持 不支持

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

索引的匹配原则

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

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

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

还有一个字段template,只有1和2两个值:

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

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

explain 查询索引使用情况

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

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

  1. explain
  2. 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 table type possible_keys key key_len ref rows Extra
1 SIMPLE ap_room ref idx_live_time idx_live_time 4 const 198153 Using where; Using filesort

参考文件:

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

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注