聊一聊关系型数据库的索引

索引的作用

在大多数应用系统的数据库中,读写比例在 10:1 左右,插入和一般的更新操作很少出现性能问题,最容易出问题的往往是复杂的查询操作,在大数据量的情况下,查询语句的优化是非常重要的

在数据量和访问量不大的情况下,数据库访问是非常快的,是否加索引影响并不大。这种时候我们并不需要花费太多精力去做索引优化,因为这样的投入产出比并不理想。但是当数据和访问量激增时,数据库运行效率会明显变慢,甚至终止服务。在大数据量的情况下,SQL优化是必要的,给数据库建立合适的索引是SQL优化的一个重要手段

索引存在的目的是提高查询效率。

我们可以类比一下从字典查询一个单词,假如要查询 “China” 这个单词,我们需要从目录索引先找到 c 字母开头的单词所在的第一页,再往下检索 h 字母,重复操作直到找到单词(或未找到)。假如没有首字母索引,我们可能需要将所有的单词看一遍才能找到对应单词。除了字典,生活中随处可见索引的应用,如文章目录、车站车次表等等,原理都是 通过不断缩小数据范围 来检索想要的结果,当然,这里还有一个前提,数据的排列是一个确定的顺序,即所有数据都可以通过同一个查找方式找出(或确认不存在)。

在创建索引时,我们需要考虑哪些列会用于查询,然后会这些列创建一个或多个索引。实际上,索引也是一个表,保存着主键或索引字段,以及其对应的指向实际表中数据位置的指针。数据库用户是看不到索引的,它们仅仅用于加快查询速度,数据库引擎会使用索引来快速定位数据的位置。

在建立了索引的数据表中,SELECT 语句执行速度会加快,但 INSERT 和 UPDATE 语句会变慢。因为在插入和更新时,索引值也会发生相应的变化。

索引的创建、删除

索引的类型有:

  • UNIQUE

    唯一索引,不允许出现相同的值,允许为 NULL

  • INDEX

    普通索引,允许出现相同值

  • PRIMARY KEY

    主键索引,不允许出现相同值

  • FULLTEXT INDEX

    全文索引,可针对值中的某个单词建立索引,但效率很低

  • 组合索引

    又称复合索引,将多个字段建立到一个索引里,列值的组合必须唯一

《阿里巴巴 Java 开发手册》中的 SQL 规范约定,唯一索引建议命名为 uk_字段名,普通索引命名为 idx_字段名

1. 创建索引 —— ALTER TABLE

用于给已经存在的表添加索引

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index) [索引名] (字段名);

使用示例

// 普通索引
ALTER TABLE users ADD INDEX idx_name (name);
// 唯一索引
ALTER TABLE users ADD UNIQUE uk_name (name);
// 主键索引
ALTER TABLE users ADD PRIMARY KEY (name);

2. 创建索引 —— CREATE INDEX

CREATE INDEX 可用于建表时创建普通或唯一索引

CREATE 索引类型 索引名 ON 表名 (字段名);

使用示例

CREATE INDEX idx_name ON users (name);
CREATE UNIQUE uk_name ON users (name);

3. 删除索引

删除索引可以使用 ALTER TABLE 或 DROP INDEX 语句来实现,DROP INDEX 可用于 ALTER TABLE 语句内部

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY;

4. 组合索引、前缀索引

组合索引、前缀索引只是对索引使用方式的称呼,并不是索引的类型。我们建立一个 demo 表,结构如下:

create table USERS
(
  ID                        int not null auto_increment comment '主键',
  LOGIN_NAME        varchar(100) not null comment '登录名',
  PASSWORD             varchar(100) not null comment '密码',
  CITY                    varchar(30)  not null comment '城市',
  AGE                        int not null comment '年龄',
  SEX                        int not null comment '性别:0女,1男',
  primary key (ID)
)

为了进一步提升数据库查询效率,可以考虑建立组合索引,将 LOGIN_NAME, CITY, AGE 建立到一个索引中

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

表结构中,LOGIN_NAME的长度为100,但建立索引时为什么使用16?这是因为大部分名字的长度都不会超过16,这样建立索引可以加快索引查询速度(因为索引数量的减少),同时减少索引文件的大小,提高INSERT、UPDATE效率。

如果分别给 LOGIN_NAME, CITY, AGE 建立单列索引,让该表有3个单列索引,查询时和组合索引的效率是大不一样的,甚至远远低于我们的组合索引。虽然此时有三个索引,但数据库只能用到其中那个它认为是最有效率的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程。

上面建立的组合索引,相当于分别建立以下三个组合索引:

LOGIN_NAME, CITY, AGE
LOGIN_NAME, CITY
LOGIN_NAME

为什么没有 CITY, AGE 这样的组合索引呢?这是因为数据库组合索引 “最左前缀” 原则的结果,简单的理解就是,只从最左边的索引开始组合,假如查询的条件顺序是 SEX, LOGIN_NAME, CITY ,由于没有 SEX 索引,即使有右边的两个索引,数据库也不会执行索引查询。

索引使用的注意事项

EXPLAIN 语句可以帮助开发人员分析 SQL 语句的问题,它会将数据库如何使用索引处理 SELECT 语句以及连接表的过程显示出来,从而帮助开发人员分析优化索引和查询语句。

直接在 SELECT 语句前加上 EXPLAIN 即可

EXPLAIN SELECT * from user where id = 1;

编写 SQL 查询语句时,我们需要注意一些事项:

  • 尽量避免对索引列进行计算

    查询语句涉及对索引列进行计算时,数据库检索时不会使用索引。

    // 因涉及索引列计算,检索时不会使用 age 索引
    SELECT name, age from users where age + 10 = 30;
    
  • 尽量避免对索引列进行函数运算

    原理与上一条相同

  • 尽量避免使用前模糊查询

    SELECT * FROM users WHERE name LIKE '%quincy'; // 不使用索引
    SELECT * FROM users WHERE name LIKE 'quincy%'; // 使用索引
    
  • 尽量避免字符串与数字做比较

    当索引列类型为字符串,查询与数字比较(或反过来)时,数据库检索时不使用索引

    // 假设age是varchar类型
    SELECT * FROM users WHERE age='24'; // 走索引
    SELECT * FROM users WHERE age=24;        // 不走索引
    
  • 尽量不使用 or 语句,除非所有条件列都存在索引

    // 假设 name, age 存在索引,city无索引
    SELECT * FROM users WHERE name = 'quincychen' or age = 24 // 走索引
    SELECT * FROM users WHERE name = 'quincychen' or city = 'shenzhen' // 不走索引
    

索引的好处有很多,但与此同时,也存在一些缺点:

  • 虽然索引大大的提高了查询速度,但同时也会降低表的更新效率

    对表进行 INSERT, UPDATE, DELETE 时,更新数据的同时要更新索引文件

  • 建立索引会占用磁盘空间

    一般而言,由于磁盘成本较低,这个问题不太重要,但是如果给一个数据量极大的表添加过多的索引时,索引文件的膨胀速度是惊人的。

使用索引的一些技巧

  1. 尽量使用短索引

    短索引可以提高查询效率,同时减小索引文件的体积。例如,用户名最长为 255,但大部分用户名不超过30,则对用户名列建立长度为30的索引即可。

  2. 索引列排序问题

    一张表查询只能用到一个索引。当 where 子句已经使用了索引时, order by 语句中的列不会使用索引。因此,当数据库默认排序符合要求时不要使用排序操作,尽量不要包含多个列的排序,必要时可以给这些列建立复合索引。如果 where 中的列和 order 中的列是分开建立的索引,则 where 语句会使用索引,但 order 语句不使用索引。

  3. 正确使用 like 语句

    尽量减少 like 语句的使用,必要时,注意使用方式,避免使用前模糊查询 %test

  4. 尽量不对索引列进行运算

    包括算数计算、函数运算,查询中对索引列进行运算时数据库检索将不使用索引

  5. 正确使用操作符

    尽量减少不使用的索引的操作符,如:NOT IN, <>, !=,用走索引的操作符代替,如:<, <=, =, >=, BETWEEN, IN

  6. 只对经常用于 SELECT 操作的列建立索引

    如果列很少被用到,那么有无索引并不会明显改善查询速度,相反,还会增加数据库的更新速度和磁盘占用

  7. 对常出现在 where 和 join 语句中的列建立索引

  8. 尽量将索引建立在值比较唯一的列

  9. 尽量不对 text, bit, boolean, blob 等数据类型的列建立索引

    这些数据类型要么数据量非常大,要么取值范围小,建立索引的回报率太低

  10. 不同表的关联字段尽量采用相同类型和长度

    在执行 join 语句时,只有主键和外键类型一致时,数据库才会使用索引

  11. 以下情况时不适合建立索引

    • 表数据量很少
    • 表更新非常频繁
    • 数据重复度很高且分布比较均匀的列
  12. 尽量避免索引列值为null,建议设置默认值

  13. 可以通过扩展已有索引满足时,不新建索引

0%