数据库优化

一、索引数据结构

索引：帮助数据库实现高效获取数据的排好序的数据结构。

二叉树

左边为小，右边为大；对于递增的数据列与不加索引是一样的。

红黑树

二叉平衡树，在二叉树基础上添加自动平衡功能；数据量比较大时，树高还是比较高

B树

同一节点存放多个索引，使得树高可控（确定分叉的索引数），节点与索引均从左到右依次递增

B+树（MYSQL）

B+树也是B树。
非叶子节点仅存放索引，叶子节点包含所有的索引和data，叶子节点之间含有双向指针

-- 查看mysql非叶子节点的大小
SHOW GLOBAL STATUS LIKE 'INNODB_PAGE_SIZE';
-- 16384

B+树的查找过程：获取第一个非叶子节点，RAM到内存中进行比对，获取下一个非叶子节点的地址，RAM新的叶子节点到内存中比较，直到确定叶子节点；
跟RAM时的磁盘与内存的I/O交互相比，在内存中的比对所消耗的时间可忽略不计。
计算数量：若规定树高最多为3，当磁盘存满时大概可存多少数据？
以主键索引为例：bigint：8B，存放下一个字节地址：6B，每个节点的大小约16KB，单节点可存放的索引数16KB/(8+6)B，约为 1170；对于非叶子节点，每个索引位置还要存放data元素，data元素不一定存放该条数据的位置，可能还报错索引对应的其他列，估算值假设有1KB；则1170117016 = 21,902,400。2千多万的数据，一般数据库达到1千万就会分库分表，对于大多数数据场景，使用B+树就足够了。仅通过了有限的I/O操作。同时对于高版本的mysql，非叶子节点的数据量也不是很大，数据库会提前将所有的非叶子节点加载到内存中，只需要定位到非叶子节点后，进行一次I/O操作。

二、MYSQL在磁盘上的存储

关于数据表存放在data文件夹中，一个库对应一个文件夹。

存储引擎

MYSQL常见的存储引擎包括：MyISAM和InnoDB。存储引擎是修饰表的。

MyISAM

一张表包括3个文件：( .form ) 表结构，.MYD 表数据，.MYI 表索引
即数据和索引是两个文件（非聚集索引），叶子节点中的data存放数据所在位置，根据索引文件获取数据存放的位置，在通过数据文件获取数据，两次I/O
当前已基本不使用，现在的应用场景要支持事务

Innodb

一张表包括2个文件：( .form ) 表结构，.ibd 数据和索引
数据和索引存放在同一个文件（聚集索引/聚簇索引），叶子节点中的data存放包括其他列的数据，即包含完整数据。
主键索引和非主键索引的区别：
主键索引包含其他所有列数据，非主键索引存放的data是主键
为什么Innodb表建议创建主键，且为自增的整型？
Innodb表在主键的基础上使用B+树来组织整个表的数据，若未创建主键，MYSQL会从整个表中逐列查找一列数据唯一列，即可对此列添加唯一索引。若不存在这列，MYSQL会添加一列自增的隐藏列来维护隐藏列。若不创建MYSQL还要多做很多工作，影响性能。
关于使用整型的自增主键：
和UUID字符串相比，在使用索引过程中包含多次关于索引元素的比较，整型比较大小明显比字符串快。且整型比UUID所占空间小，在实际生产环境中，为了提高MYSQL的性能，一般会使用SSD（高速固态硬盘），价格是比较昂贵的，在一定程度上要尽量减少MYSQL耗费的存储空间；
自增：为了满足B+树数据从左到右依次增大的规则（支撑范围查找），会出现向叶子节点中间插入数据，可能发生叶子节点的分裂，且数据还需要进一步平衡，效率不如递增的数据。即自增效率高，树旋转的概率小。

遗留问题：为什么非主键索引存放的是主键？

三、Hash索引

数组+链表
插入值，会通过Hash算法（MYSQL有自己的Hash算法）计算Hash值，通过Hash值定位，插入到链表。查询，则是通过Hash值定位，从链表中获取。
在避免Hash冲突的基础上，Hash索引的查询速度是非常快的。
但一般不使用Hash索引，不支持范围查询。
相比B+，叶子节点包含双向节点，即在每个叶子节点的开始和结束有一个小的空间，存放着相邻节点的存储位置。在范围查询时，通过范围点定位界限，依次遍历即可。

B+树和B树的区别：
1. B树叶子节点之间不存在双向指针，不能很好的支持范围查找，范围查找时获取第二个叶子节点只能回到根节点重新定位。
2. B树非叶子节点包含data元素，而B+树将所有索引的data元素存放在叶子节点，使得每个非叶子节点可以存放更多的索引，实现更多的分叉，在同样的数据量下，使得达到更低的树高，提升查询性能。相比增加的部分冗余索引，其节省的空间是更大的。

遗留问题：Hash冲突

四、联合索引

一张表不建议创建太多索引：
每个索引存储都是需要占距存储空间的，且在插入和删除数据时，数据库要对其进行维护，在一定程度上影响数据库性能。一般建议使用联合索引。
多个字段一次比较排序。
使用 explain 查看索引的执行计划。
遗留问题：各字段的含义

最左前缀原则

原因：联合索引按照多字段依次排序，跳过索引后，字段不再有序。