MySQL索引笔记

索引是表的目录,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,以及快速定位查询数据。对于索引,会保存在额外的文件中。

索引可以提高SELECT查询速度,同时也降低了INSERT及UPDATE的效率,因为INSERT或UPDATE是有可能会重建索引

索引的适用场景

  1. 表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询。
  2. 经常与其他表进行连接的表,在连接字段上应该建立索引。
  3. 重要的SQL或调用频率高的SQL,比如经常出现在where子句中的字段,order by,group by, distinct的字段都要添加索引。
  4. 经常用到排序的列上,因为索引已经排序。
  5. 经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的。

MySQL索引用法

普通索引

这是最基本的索引,它没有任何限制。

  1. 直接创建索引
1
CREATE INDEX index_name ON table(column(length))
  1. 修改表结构的方式添加索引
1
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
  1. 创建表的时候同时创建索引
1
2
3
4
5
6
7
8
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)

唯一索引

不同与普通索引的是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似

  1. 创建唯一索引
1
CREATE UNIQUE INDEX indexName ON table(column(length))
  1. 修改表结构
1
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
  1. 创建表的时候直接指定
1
2
3
4
5
6
7
8
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);

全文索引

全文索引只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。

这类场合正是全文索引(full-textindex)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用命令添加。

  1. 直接创建索引
1
CREATE FULLTEXT INDEX index_content ON article(content)
  1. 修改表结构添加全文索引
1
ALTER TABLE article ADD FULLTEXT index_content(content)
  1. 创建表的时候直接指定
1
2
3
4
5
6
7
8
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);

组合索引(最左前缀)

平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。

例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

1
2
- title,time
- title

为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:

  • 使用到上面的索引
1
2
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE title='测试';
  • 不使用上面的索引
1
SELECT * FROM article WHREE time=1234567890;

MySQL索引优化

聚集索引

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。  

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序

当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员ID列emp_id查找特定雇员的最快速的方法,是在emp_id列上创建聚集索引或PRIMARY KEY约束。

聚集索引一张表只能创建一个,非聚集索引一张表可以创建多个,在MySQL中,InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键(Primary Key)进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集。

索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对字符串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

例:CREATE INDEX index_name ON table(column(10 or 20))

索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like %aaa%不会使用索引而like aaa%可以使用索引。

不要在列上进行运算

例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′

参考


----------- 本文结束啦感谢您阅读 -----------

赞赏一杯咖啡

欢迎关注我的其它发布渠道