更新的字段能设置索引码_MySQL索引

提到数据库索引，我想大家都不会陌生，在平时工作上会遇到SQL 语句查询慢的问题，该怎么办呢？加个索引吧，但到底什么是索引，索引又是如何工作的？今天我们来聊聊

索引是什么？维基上的定义
数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中数据
理解：
首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据
有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，找到数据存放的磁盘地址以后，就可以拿到数据了
就像我们从一本500页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容
一句话总结：索引的出现其实就是为了提高数据查询的速度，就像书的目录结构一样，通过它可以快速的某一页具体的数据

索引类型
在InnoDB 里面，索引类型有三种，普通索引、唯一索引(主键索引是特殊的唯一索引)、全文索引
普通(Normal): 非唯一索引，是最普通的索引，没有任何的限制。
唯一(Unique): 唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primay key创建。
全文(Fulltext):针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text
索引是一种数据结构，那么它到底一种什么数据结构，才能实现数据的高效检索呢?

索引存储模型推演
二分查找法
有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据，所以只适合存储静态的数据
为了支持频繁的额修改，比如插入数据，需要采用链表结构，链表的话，如果是单链表，它的查找效率还是不够高，有没有使用二分查找的链表

二叉查找树
特点：每个节点最多有两个子树的数据结构，通常被称为左子树或者右子树，左子树所有的节点都小于父节点，右子树所有的节点都大于父节点；二叉查找树既能够实现快速查找，又能够实现快速插入，但是二叉查找树有一个问题，如图：

上图中左图示例的查询时间复杂度O(logn),查询效率很高，但是在数据库中的数据产生增加或者删除后有可能会变成上图右图示例，这样就会变成线性二叉树了，大大增加了IO 查询次数从而降低了查询效率。不论是二叉树还是红黑树，每个节点最大只能有两个子节点，这样数据多的话，会造成这颗树的深度会很深，IO 次数也会很多，这样检索性能会大大降低，无法满足优化查询的需求

多路平衡查找树(B Tree)
每个节点最多有m 个孩子，那么这样的树就是m阶数例如：

B树每个存储快中存储键值、数据地址、节点引用，最多能有几个孩子取决于每个存储块的容量以及数据库的相关配置，通常情况下这个m 很大；除根节点和叶子节点外，其他每个节点至少有ceil(m/2)个孩子；分叉数(路数)永远比关键字多1，比如上图这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点
B树是怎么实现一个节点存储多个关键字还可以保持平衡？
比如设置路数为3，插入数据1、2、3，在插入3的时候本来应该在第一个磁盘块，但是如果一个节点有三个关键字，就意味着有4个指针，子节点路数就会变成4路，所以这个时候会进行分裂。把中间数字2提上去，把1和3 变成2的子节点。如果删除节点，会有相反的合并操作

从这里可以得知，如果在更新索引的时候会有大量的索引结构调整，所以为什么最好不要在更新频繁的列上建索引

B+ 树
B 树的改良版，它相对于B 树来说，在文件系统以及数据库系统当中更有优势，B+ Tree 磁盘读写代价更低。B+ tree 内部结构并没有指向关键字具体信息的指针，也就是不存放数据，只存放索引信息，因此其内部节点相对B 树更小；如果把所有统一内部结点的关键字存放在同一板块中，这个板块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多，相对来说IO 读写次数也就降低了；第2个，就是B+ Tree的查询效率更加稳定，由于内部节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引，所以任何关键字的查找必须走一条从根节点到叶子节点路，所有关键字查询的长度相同，导致每一个数据的查询效率也几乎是相同的。第3个就是B+ 树更有益于对数据库的扫描，B 树在提高了磁盘IO性能的同时呢，并没有解决元素遍历的效率低下的问题，而B+树只需要遍历叶子节点，就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的范围查询
总结B+ Tree 特点
1）它是B 树的加强版，B Tree 能解决的问题，它都能解决。
2）扫库、扫描能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+ Tree 拿到所有的数据)
3）B+Tree 的磁盘读写能力相对于B Tree 来说更强(根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多)
4）排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)
5）效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以IO 次数是稳定的)

索引方式
索引方式有两种：Hash和B Tree
HASH：以KV 的形式检索数据，也就是说，它会根据字段生成哈希码和指针，指向数据。
特点：
查询速度快，因为哈希索引里面的数据不是按顺序存储的，不用于排序
支持等值查询，不支持范围查询
InnoDB 显示创建B+ Tree 索引时，会自动建立自适应Hash 索引，客户端是不可控制的
buffer pool里面有一块区域是Adaptive Hash Index自适应哈希索引
开关默认是ON：

MySQL 数据存储文件
首先，MySQL 的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址
show VARIABLES LIKE 'datadir';
每个数据库有一个目录，对应其一个目录

每张InnoDB 的表有两个文件(.frm 和.ibd)，MyISAM 表有三个文件(.frm、.MYD、.MYI)

MyISAM
一个是.MYD文件，D代表Data，是MyISAM 的数据文件，存放数据记录
一个是.MYI 文件，I代表Index，是MyISAM 的索引文件，存放索引，比如我们在id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面
在 MyISAM 里面，索引和数据是两个独立的文件
MyISAM的B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录
辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据

InnoDB
在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面

索引使用原则
列的离散度
离散度的公式：count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。 数据行数相同的情况下，分子越大，列的离散度就越高
如果在 B+Tree 里面的重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。
show indexes from 表名;

Cardinality 代表基数，代表预估的不重复的值的数量。索引的基数与表总行数越接近，列的离散度就越高
建立字段要使用离散度更高的字段

联合索引最左匹配
联合索引在 B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的
创建联合索引
CREATE INDEX idx_name on 表名(name);
CREATE INDEX idx_name_phone on 名(name,phone);
覆盖索引
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面有没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表
在辅助索引里面，不管是单列索引还是联合索引，如果 select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表
select * ，用不到覆盖索引

先写到这里，后续会继续更新此篇内容，为故而知新