Mysql——》索引的创建与使用

一、介绍

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。

如果没有索引，我们要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据（循环调用存储引擎的读取下一行数据的接口），直到找到这条数据。
如果有了索引，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

示例：就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。 这本书会有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，我们只要确定内容对应的页码，就能很快地找到我们想要的内容

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

二、索引类型

在InnoDB中，索引类型有3种：
普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引

1、普通：Normal

普通索引 = 非唯一索引

2、唯一：Unique

要求键值不能重复
要求键值不能为空
主键索引是特殊的唯一索引，使用primay key创建

3、全文：Fulltext

针对比较大的数据，比如我们存放的是消息内容。
如果要解决like查询效率低的问题，可以创建全文索引。
只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

三、创建索引

1、建议字段

1）where 判断的字段
2）order 排序的字段
3） join 的（on）字段

2、不建议字段

1）区分度低（离散度 低）的字段
例如：性别
原因：离散度太低，导致扫描行数过多

2）频繁更新的字段
原因：页分裂

3）随机无序的字段
例如：身份证、UUID
原因：无序，分裂

四、索引失效

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规则。
用不用索引，最终都是优化器说了算。

Q：优化器是基于什么的优化器？
A：基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），也不是基
于语义。怎么样开销小就怎么来。 参考链接：The Optimizer Cost Model

索引的值不确定，有以下4种情况，会导致索引失败

1、索引列上使用 函数 （replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）

explain SELECT * FROM user where id+1 = 4;

2、字符串不加引号，出现隐式转换

explain SELECT * FROM user where name = 136; 
explain SELECT * FROM user where name = '136';

3、like 条件中前面带%

explain select *from user where name like 'wang%'; 
explain select *from user where name like '%wang';

4、负向查询：not like

--  NOT LIKE 不能使用索引
explain select *from user where user not like 'wang'

-- != （<>）和 NOT IN 在某些情况下可以使用索引
explain select *from employees where emp_no not in (1) ;
explain select *from employees where emp_no <> 1;

五、索引个数

索引的个数不要过多。
原因：浪费空间，更新变慢。

六、索引最左匹配

参考链接：Mysql——》联合索引 && 最左匹配

七、单列索引、联合索引

创建复合（联合）索引，而不是修改单列索引