MySQL索引简明教程

MySQL 索引

在 MySQL 中建立索引就像是为一本书创建目录一样,建立了索引之后,MySQL 就能快速在表中找到所要查询的内容的大致区域,如果不使用索引,那么就需要去整张表中遍历数据才能查询到结果。

MySQL 的索引是建立在表的基础之上的,一个表中可以用多个索引。在表中建立索引后,虽然查询的效率提高了,但是 insert 和 update 操作的效率就降低了。

索引的类型

在 MySQL 中可以建立多种类型的索引,比如最常用的就是主键索引,索引有以下的类型:

  • 唯一索引(UNIQUE):不允许出现相同的值,可以有 NULL 值
  • 普通索引(Index):允许出现相同的索引内容
  • 主键索引(PRIMARY KEY):不允许出现相同的值
  • 全文索引(FULLTEXT INDEX):可以为值中的某个值建立索引,但是全文索引的效率很低,而且全文索引的优先级很高

通常是把一个字段创建为一个索引,但是同时也可以在一个索引中使用多个字段,这种索引称之为组合索引

索引的创建与删除

索引可以在建表阶段或者表建立之后进行创建,在建表阶段,使用 CREATE INDEX 建立索引:

1
2
CREATE INDEX index_name on table_name (column_list)
CREATE UNIQUE INDEX index_name on table_name (column_list)

CREATE INDEX 只能创建普通索引唯一索引这两种类型的索引。

还有一种方式就是通过 ALTER TABLE 方式创建索引:

1
2
3
4
5
6
7
8
9
10
11
// 省略索引名称,索引名称和第一个列名一样
alter table table_name add index (column_list)

alter table table_name add index index_name (column_list)

alter table table_name add uniuque (column_list)

alter table table_name add primary key (column_list)

// 为索引指定长度
alter table table_name add index index_name (column(length))

删除索引可以通过 ALTER TABLEDROP INDEX 来实现:

1
2
3
4
5
DROP INDEX index_name on table_name

alter table table_name drop index index_name

alter table table_name drop primary key

索引的使用

在创建完索引之后,并不需要手动去使用索引,而是由 MySQL 的引擎来决定是否使用索引,可以通过 EXPLAIN 关键字来查看 MySQL 是如何使用索引的,同时也可以通过这个关键词来分析 SQL 语句,使用起来很方便,只需要加在 SQL 前就可以:

1
explain select * from user where username = 'ray'

在 SQL 要避免以下的查询,以下的查询都不会使用索引:

  • 索引列参与了计算或者使用了函数运算
  • 查询的时候使用了正则表达式
  • 字符串与数字进行比较
  • 使用 or 查询,但是不是所有的条件都有索引
  • 语句中使用 NOT IN、<>、!= 操作

索引建立原则

  • 单表中索引的数量需要控制
  • 添加索引时最好不要加在字符串字段上
  • 如果非要加在字段上,要限制索引的长度,只使用前缀索引
  • 不要使用 not in \ like 等查询字段,如果非要使用 like,使用后匹配 like, 如 "beijing%",not in 使用 not exists 替代
  • 不要在低基数列创建索引,比如 status 字段
  • 尽量不要在经常修改的列上添加索引
  • 删除冗余索引,没有用到的索引必须全部删除
  • 创建索引的列不应该有 null 值,有 null 值得列是不会加入到索引中的
  • 如果是多条件查询,不要为每个条件字段创建索引,而是应该创建复合索引,MySQL一次只会使用一个索引
  • 在创建复合索引时,要注意索引的左匹配原则,如果创建了 index(c1, c2, c3),那么就相当于同时创建了 index(c1), index(c1, c2) 和 index(c1, c2, c3) 这三个索引
  • 数据库的主键最好是使用自增的,如果担心主键泄露信息,可以再使用一个uuid类型的字段对外展示

字符串索引优化

添加一个冗余字段,将字符串使用 hash 方法将字符串转化为整数,例如:username_key=hashToInt(username),对username_key建立索引,查询时可以用如下查询

1
select * from user where username_key = hashToInt(‘ray’) and username = ‘ray

微信公众号

© 2018 ray