MySQL数据库：13、索引
MySQL数据库之索引
一、索引的概念
1、什么是索引
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容
2）让获取的数据更有目的性，从而提高数据库检索数据的性能
2、索引的存储类型
MySQL中索引的存储类型有两种，即 BTree 和 Hash。
3、索引的实现
索引是在存储引擎中实现的。（MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap）
- InnoDB / MyISAM 只支持 BTree 索引
- Memory / Heap 都支持 BTree 和 Hash 索引
4、什么是存储引擎
存储引擎就是指 表的类型 以及 表在计算机上的存储方式。
5、索引的优缺点
-
优点：
- 提高数据的查询的效率（类似于书的目录）
- 可以保证数据库表中每一行数据的唯一性（唯一索引）
- 减少分组和排序的时间（使用分组和排序子句进行数据查询）
- 被索引的列会自动进行分组和排序
-
缺点：
- 占用磁盘空间
- 降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）
二、索引的分类
1、普通索引 和 唯一索引
普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值
唯一索引：要求索引列的值必须 唯一，但允许 有空值
如果是组合索引，则列值的组合必须 唯一
主键索引是一种特殊的唯一索引，不允许 有空值
2、单列索引 和 组合索引
单列索引：一个索引只包含单个列，一个表可以有多个单列索引
组合索引：在表的 多个字段 组合上 创建的 索引
只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）
3、全文索引
全文索引 的类型为 fulltext
在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
全文索引 可以在 char、varchar 和 text 类型的 列 上创建
4、空间索引
空间索引 是对 空间数据类型 的字段 建立的索引
MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon
MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引
创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。
5、前缀索引
在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度
三、索引加快查询的本质
1、primary key
2、unique key
3、index key
1.上述的三个key都可以加快数据查询
2.primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
3.外键不属于索引键的范围 是用来建立关系的 与加快查询无关
id int primary key auto_increment,
name varchar(32) unique,
province varchar(32)
age int
phone bigint
select name from userinfo where phone=18818888888; # 一页页的翻
select name from userinfo where id=99999; # 按照目录确定页数找
索引可以加快数据查询 但是会降低增删的速度
通常情况下我们频繁使用某些字段查询数据
为了提升查询的速度可以将该字段建立索引
聚集索引(primary key)
主键、主键索引
辅助索引(unique,index)
除主键意外的都是辅助索引，辅助索引其实本质上也是用的聚集索引的数据来查询的（用的主键的数据）
覆盖索引
select name from user where name='jason';
所谓覆盖索引就是条件中的字段名和查看的字段名称相同
非覆盖索引
select age from user where name='jason';
四、索引的结构
MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。
1、Hash：
hash：也可称为‘二叉树’
使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。
Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。
2、BTree：
属于多叉树，又名多路平衡查找树。
MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？
可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！
为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找树，主要的特点是：
- BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
- BTree 节点的键值按 非降序 排列
- BTree 所有叶子节点都位于同一层（具有相同的深度）
下面模拟下查找key为27的data的过程：
存在的一些问题：
- B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响查询效率。
- 不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
3、B+Tree：
只有叶子节点才会存放真正的数据 其他节点只存放索引数据
对比B树和B+树，我们发现二者主要存在以下几点不同的地方：
- 数据都存放在叶子节点中
- 非叶子节点只存储键值信息，不再存储数据
- 所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
等值查询
下面模拟下查找key为9的data的过程：
范围查询
下面模拟下查找key的范围为9到26这个范围的data的过程：
从上面的结果，我们可以知道B+树作为索引结构带来的好处：
- 磁盘IO次数更少
- 数据遍历更为方便
- 查询性能更稳定
由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。
B*树
B*叶子节点和枝节点都有指向其他节点的指针
ps：
- 辅助索引在查询数据的时候最会还是需要借助于聚集索引
- 辅助索引叶子节点存放的是数据的主键值
- 有时候就算采用索引字段查询数据 也可能不会走索引!!!
五、索引失效的情况
1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
2.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
3.OR前后存在非索引的列，索引失效
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
4.普通索引的不等于不会走索引；如果是主键，则还是会走索引；如果是主键或索引是整数类型，则还是会走索引
5.组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
6.is null可以使用索引，is not null无法使用索引
最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。
7.计算、函数导致索引失效另外一种情况
#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';
扩展：
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';
如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。这也相当于是函数导致的索引失效。
8.字符集不统一
统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。。
ps：最好能记三个左右的特殊情况
