- 一、索引概念
- 二、索引类型
- （一）业务逻辑分类
- （二）索引字段个数不同分类
- 三、索引使用
- （一）如何判断sql使用了索引？
- （二）如何使用索引？
- （三）如何避免索引失效？
- （四）建立索引时需要注意什么？
- 四、索引的优缺点
一、索引概念
索引是一种提升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)
可以改成：
select * from users where adddate
2、不要使用not in 或 !=、 <>
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id
Original: https://www.cnblogs.com/ZJJCodeLife-520/p/16463540.html
Author: 桃子来了
Title: MySQL索引（一）
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621086/
转载文章受原作者版权保护。转载请注明原作者出处！