心安

聊一聊mysql索引

字数统计: 1.5k阅读时长: 5 min
2019/01/05 Share

索引

前言

在mysql中,假设我们在user表中查询一个名为“test01”的用户,那么我们使用的sql语句应该是SELECT * FROM user WHERE username = 'test01'
在没有建立索引的情况下,mysql会扫描整张表来查找这条数据,然后返回结果。
如果数据量非常大,那么响应所消耗的时间也是非常多的。但是在“username”这一列上面建立了索引,相应的查询速度将会提升很多。
那么索引到底是一个上面东西呢?我们可以把索引比喻成字典里的目录。假设我们需要在字典里面查找某一个字,在不借助目录的情况下,我们就需要“扫描”整个字典。
但是如果通过目录来确定这个字的页数,我们就可以迅速的查找到我们想要的内容。
本文就讲从如何建立索引、介绍索引内容、索引的利与弊、以及建立索引时需要注意的地方等几个方面来介绍mysql的索引相关的知识。

一、索引的分类

  1. 普通索引:这是最基本的索引,它没有任何限制。
  2. 唯一索引:它和普通索引类似,不同的是它要求列的值必须唯一,允许空值。
  3. 联合索引:联合索引又分为联合主键索引、联合唯一索引、联合普通索引。
  4. 全文索引:用于搜索很长一篇文章的时候,效果最好,用的不多。
  5. 空间索引:很少会用到,我也不太懂,所以忽略它吧,抓住重点就好。

本文着重介绍前三种索引。

二、索引的创建

  1. 普通索引
  • 在建表的时候创建索引
1
2
3
4
5
CREATE TABLE user(
id INT PRIMARY KEY,
username VARCHAR(32) NOT NULL,
KEY idx_username(username) # 或者用INDEX替换KEY也是可以的
)
  • 给某一列添加索引

语法CREATE INDEX [index name] ON [table name]([column name])或者
ALTER TABLE [table name] ADD INDEX [index name]([column name])

1
CREATE INDEX idx_username ON user(username)
1
ALTER TABLE user ADD INDEX idx_username(username)
  1. 唯一索引
1
2
3
4
5
CREATE TABLE user(
id INT PRIMARY KEY,
username VARCHAR(32) NOT NULL,
UNIQUE INDEX idx_username(username)
)
  • 给某一列添加索引

语法CREATE UNIQUE INDEX [index name] ON [table name]([column name])或者
ALTER TABLE [table name] ADD UNIQUE INDEX [index name]([column name])

1
CREATE UNIQUE INDEX idx_username ON user(username)
1
ALTER TABLE user ADD UNIQUE INDEX idx_username(username)
  1. 联合索引

语法CREATE UNIQUE INDEX [index name] ON [table name]([column name1],[column name2],...)或者
ALTER TABLE [table name] ADD UNIQUE INDEX [index name]([column name1],[column name2],...)

1
CREATE INDEX idx_username ON user(username,password,phone_number)

这里我们发现,联合索引和普通索引似乎区别不大,只不过是建立了多列的索引而已。
实际上,以上这个sql所建立的是三个索引,分别是(username),(username,password),(username,password,phone_number)。
值得注意的是,这个sql不会建立(password),(password,phone_number)这样的索引,这是因为mysql联合索引遵循“最左前缀”的原则
最简单的理解就是从最左边开始组合,而不是只要包含这些列都会使用到这些索引。
举个简单的例子:

1
2
3
4
5
6
7
-- 以下三条sql语句会使用到上面的索引
SELECT * FROM user WHERE username = "test01";
SELECT * FROM user WHERE username = "test01" AND password = "test02";
SELECT * FROM user WHERE username = "test01" AND password = "test02" AND phone_number = "123546";
-- 而下面的sql不会使用到上面的索引
SELECT * FROM user WHERE password = "test02";
SELECT * FROM user WHERE password = "test02" AND phone_number = "123546";

三、使用索引的利与弊

索引可以从很大程度上加速我们的查询速度,那么是不是就意味着我们应该给所有的表以及所有的列都创建索引呢?
肯定不能这样的,下面我们就分析一下索引的优缺点。

  • 索引的优点
  1. 通过建立唯一索引或者主键索引,可以保证表中某一列数据的唯一性。
  2. 建立索引可以大大的提高检索数据的速度以及减少检索的数据条数。
  3. 在表连接的时候,可以加速表与表之间的连接。
  4. 在分组和排序查询的时候,也会减少查询时间(这也就意味着索引不光会在WHERE子句中起作用,也会在GROUP BY,ORDER BY子句中起作用)。
  • 索引的缺点
  1. 创建索引和维护索引会耗费时间,而且随着数据量的增加而增加。
  2. 索引文件会占用物理空间。
  3. 对表进行INSERT、UPDATE、DELETE等操作的时候,需要维护索引,这是比较消耗时间的。

四、使用索引需要注意的地方

既然上面提到了索引的利与弊,那么我们在建立索引的时候就应该考虑如何合理的使用索引。
在哪里建立索引才是最合适的,一般从以下几个角度考虑:

  1. 在经常需要搜索的列上,比如说经常需要使用用户名来查询用户信息,但是几乎不会使用密码来查询用户信息,所以更适合在username这样的列上建立索引。
  2. 表与表的连接上面建立索引,可以加速连接查询的速度。所以可以考虑建立在外键上。
  3. 在经常需要排序和分组查询的列上面建立索引可以加速排序速度,例如create_date这样的列上面。

通常以下情况不创建索引:

  1. 查询条件很少会使用到的列。
  2. 数据量很小的列不应该使用索引。例如某些枚举值,性别等。他们的值只有“男”,“女”,“YES”,“NO”等,建立索引的意义不大。
  3. 当表的查询操作的频率远远小于表的更新、删除、插入等操作的频率的时候不应该建立索引。
  4. 数据类型为text、image的时候不应该使用索引。

以上就是博主今天为您带来的关于mysql索引的内容,有任何的表达的不清晰或者错误的地方,欢迎您在下方的评论区进行留言,您也可以通过我的联系方式联系我。另外,喜欢我的文章可以关注笔者,感谢您的阅读。

原文作者:XinAnzzZ

原文链接:https://www.yuhangma.com/2019/mysql/2019-01-05-mysql-index/

发表日期:January 5th 2019, 12:00:00 am

更新日期:September 26th 2019, 10:46:42 am

版权声明:(转载本站文章请注明作者和出处 心 安 – XinAnzzZ ,请勿用于任何商业用途)

CATALOG
  1. 1. 索引
    1. 1.1. 前言
    2. 1.2. 一、索引的分类
    3. 1.3. 二、索引的创建
    4. 1.4. 三、使用索引的利与弊
    5. 1.5. 四、使用索引需要注意的地方