Rayjun

MySQL索引简明教程

MySQL 索引

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

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

索引的类型

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

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

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

索引的创建与删除

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

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

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

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

    // 省略索引名称，索引名称和第一个列名一样
    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 TABLE 和 DROP INDEX 来实现：

    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 前就可以：

    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建立索引，查询时可以用如下查询

    select * from user where username_key = hashToInt(‘ray’) and username = ‘ray
©2024 Rayjun    PowerBy Hexo