MySQL索引（一）

目录

• 一、索引概念
• 二、索引类型
  • （一）业务逻辑分类
    • 1、NORMAL - 普通索引
    • 2、UNIQUE - 唯一索引
    • 3、PRIMARY KEY - 主键索引
    • 4、FULLTEXT - 全文索引
    • 5、SPATIAL - 空间索引 （不常用）
  • （二）索引字段个数不同分类
    • 1、单列索引
    • 2、多列索引
• 三、索引使用
  • （一）如何判断sql使用了索引？
  • （二）如何使用索引？
    • 1、where 索引字段
    • 2、order by 索引字段
  • （三）如何避免索引失效？
    • 1、不要在列上进行运算
    • 2、不要使用not in 或 !=、 <>
    • 3、索引不会包含有NULL值的列
    • 4、不要使用like “%xxx”
  • （四）建立索引时需要注意什么？
• 四、索引的优缺点

一、索引概念

索引是一种提升SQL查询速度的数据存储结构，数据源于定义索引时指定的字段列。

索引类似于目录，人们能根据目录快速定位到书的某一页，MySQL也是通过索引快速定位对应的数据列。

如果没有索引，MySQL在查询某表id=90的数据时，就必须从第一行开始，遍历全表来找相应的行，时间成本太大，没人会用的。

二、索引类型

（一）业务逻辑分类

如图为Navicat建立时显示的索引类型，基本符合逻辑分类。

1、NORMAL - 普通索引

普通索引限制没有那么多，允许在定义索引的列中插入重复值和空值。

底层数据结构为B-树。

create index idx_name on user(name); -- 为name字段建立名为“idx_name”的普通索引

2、UNIQUE - 唯一索引

与普通索引相比，唯一索引不允许数据出现重复，但允许出现空值。

底层数据结构为B-树。

create unique index u_image on user(image);	-- 为image字段建立名为“u_image”的唯一索引

3、PRIMARY KEY - 主键索引

专门为主键字段建立的索引，不允许重复值或者值为空。

底层数据结构为B-树。

一般在建表的时候声明主键，会自动创建主键索引。

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL
  PRIMARY KEY (`id`) -- 声明id为主键
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

4、FULLTEXT - 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

底层数据结构为B-树。

create fulltext index full_content on user(content); -- 为content字段建立名为“full_content”的唯一索引

5、SPATIAL - 空间索引 （不常用）

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。空间索引主要用于地理空间数据类型 GEOMETRY.

create spatial index spa_line on user(line); -- 为line字段建立名为“spa_line”的空间索引

（二）索引字段个数不同分类

1、单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

如下为针对name字段的索引。

create index idx_name on user(name); -- 为name字段建立名为“idx_name”的普通索引

2、多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

如下为name、age、image字段建立名为“idx_name_age_image”的普通多列索引。

create index idx_name_age_image ON user(name,age,image); 

为什么有了单列索引，还需要多列索引？

最左前缀原则

建 (name,age,image) 一个多列索引时，其实相当于建立了 (name)、(name,age)、(name,age,image) 三个索引。

多个查询条件均建立索引的情况下，查询效率也会快不少，因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

三、索引使用

（一）如何判断sql使用了索引？

使用explain关键字。

参考：Explain关键字解析

（二）如何使用索引？

1、where 索引字段

explain select * from user where name = 'a'; -- name是需要加索引的字段，放在where后，就能使用索引

2、order by 索引字段

explain select * from user where age > 10 order by id; -- 对id加了索引，排序时直接使用索引排序

（三）如何避免索引失效？

1、不要在列上进行运算

select * from users where YEAR(adddate)<2007;

可以改成：

select * from users where adddate<‘2007-01-01';

2、不要使用not in 或 !=、 <>

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

3、索引不会包含有NULL值的列

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

4、不要使用like “%xxx”

把%放前面不会使用索引，如下

select * from user where name like '%zhangsan%';

而like “xxx%”可以使用索引，如下

select * from user where name like 'zhangsan%';

（四）建立索引时需要注意什么？

1、对区分度高的列做索引， 对于那种只有两三个值的做索引并没有多大意义。

2、只对需要作为查询条件或排序字段的列建索引，否则会拖慢插入、更新速度。

3、可以使用覆盖索引时，尽量使用覆盖索引（查询条件字段和查询的目标字段都是索引字段），避免回表查询。

四、索引的优缺点

优点

创建索引可以大大提高系统的性能。

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？

缺点

这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。