Mysql数据库--索引解析以及sql优化

索引的本质

索引—是帮助MYSQL高效获取数据的排好序的数据结构

每次在节点上的的查找都是一次磁盘I/O，很费时间，索引在本质上帮助mysql减少了I/O的次数。

索引的数据结构选择史

二叉树

最多只能提高50%的性能
不适用场景，当作为一个主键递增的值的索引的时候，会退化成链表形式。

红黑树

可以自动改变根节点，平衡二叉树。

比二叉树更优秀一点

但是当数据量非常庞大的时候，会导致树太高，层级太多。

试想一下，假如我们拥有几百万条数据，每次插入，可能都要进行一次平衡，每次查找都需要Log(N)的时间复杂度，当树高达到一定高度的时候，效率会变得非常低。

B树（B-Tree）

将红黑树进行改造，使得每个节点横向扩张存储更多的数据，从而限制树高。

B+树（B+Tree）（多叉平衡树）

B树的变种

查找原理：
查找上述的30索引的data
首先将根节点的索引load到内存里（一次I/O）,因为是有序的，利用二分算法等很容易找到在15-56之间找到，其中空白的地方指向的是下一节点在磁盘上的位置，找到之后再次load到内存里，重复上述方法发现30索引应该在20-49之间，最终找到带有30索引的叶子节点取出data。

Mysql里面 每个节点分配的大小为16KB，一个索引bigint类型为8b，一个指针为6b，
那么每个节点能存储的数据大小为16000/（8+6）=1170
假使叶子节点数据+索引大小为1kb
在h=2的情况下能存储1170117016=2000多万数据

Hash表

查找原理
对索引元素的值进行一次哈希运算，得到哈希值直接定位到磁盘存储位置。

但是无法进行范围查找。

存储引擎介绍

MylSAM存储引擎

索引文件和数据文件是分离的（非聚集）

表结构文件 FRM
表数据文件 MYD
表索引文件 MYI

叶节点区数据存储的是数据在磁盘的地址值

InnoDB存储引擎（推荐）

表结构文件
表索引数据文件

主键
数据字段1
数据字段2

为什么要有主键？
因为InnoDB底层采用的B+树，需要主键去组织索引和数据。如果没有设置主键，InnoDB会去字段里找一个（数据不重复）去当作主键，如果找不到，那么会在后台创建一个隐藏的字段去当主键来维护这个表。

为什么推荐整型自增主键？
因为在B+树查找数据的时候，

需要进行比对，整型自增的主键比对起来速度快。
整型占用空间小。

插入的时候，若不是自增，会往叶子节点之间插入，可能还会导致树进行一次平衡，效率极低。

联合索引

把联合索引的字段都放到节点的索引里

最左前缀原则

进行sql查询的时候，不能跳过左边的字段。

原因：后面字段要有序的话，需要前面的字段。缺少了前面的字段的话，后面的字段在整个树里来看是无序的。
比如我第一个字段是10001，找到了这个字段所在的索引区域，那么接下来对于第二个字段，他在这片区域里是程有序递增的方式，同理对于第三个字段也是如此。这样才算真正的经过了咱们的B+树的索引

SQL优化

慢查询优化

找到最慢的sql语句，给它加索引。

给mysql开启慢查询日志
Windows my.ini linux my.conf

需要一个显示慢sql的一个工具
Mysqldumpslow–Mysql自带

T:总时间
C:总次数
L:锁的总时间

寻找优化点

看表结构
上述sql包含一个关联子查询，改成关联查询

表设计优化

三大范式
1、字段具有原子性
2、字段依赖主键
3、字段间不相互依赖