MySql索引技术与性能优化分享

一、背景

MySQL 数据库存储数据最终是以文件的形式存储到硬盘的。磁盘IO的速度较慢，全表扫描查询所需数据的成本很高。因此需要有一种技术减少磁盘IO的次数。

二、解决思路

• 优点：查询速度快，插入速度快。

• 缺点：无法利用索引完成排序，范围查询需要全表扫描，只适用于等值查询的场景；比如 Memcached 及其他一些 NoSQL 引擎。

  哈希索引不支持多列联合索引的最左匹配规则。

  如果有大量哈希碰撞的情况下，哈希索引的效率会很低。

1.二分查找

优点：数据搜索速度从O(n)降低到了O(log2n)

缺点：必须使用数组，数组插入和删除数据比较麻烦。

2.简单二叉查找树/平衡二叉查找树

优点：数据的增删改查比较方便，。

缺点：某些情况会退化成链表。可以使用平衡二叉树，但平衡二叉查找树插入数据时需要通过再平衡算法保持叶子节点层级相差小于等于1，再平衡算法成本比较高可能到O(logn)。

因此需要在舍弃一定查询效率的基础上，提高数据插入效率。

3.红黑树

 

优点：红黑树叶子节点层级最多相差2，牺牲了一定的查询效率，但是降低了数据维护成本，相当于2-3树。

缺点：数据库是在磁盘上进行读取的，磁盘读取是以页（默认为4k大小）为单位进行读取，红黑树解决不了因数的深度增加导致数据IO次数过多的问题。

因此，需要有一种以页为单位存放数据的树，以减少磁盘的IO次数。

4.b树

假设磁盘单页大小4k，data均为1k，则一个页可以放4个data（指针空间忽略）。一个层级为3的树，为4叉树，可以存放的data为4*4*4=256个数据。即三次IO内，可以检索到256个数据。

优点：以页为单位存放指针和数据，减少了磁盘IO读写次数。

缺点：指针和数据存在同一个页中，磁盘IO读写次数还是比较高。

5.b+树

假设磁盘单页大小4k，data均为1k，一个指针10个字节，则一个页可以放4个data。一个层级为3的树，为400叉树，可以存放的data为(4k/10)*(4k/10)个，约等于160000个数据。即，三次IO读写内，可以检索到160000个数据。

优点：相同深度（IO读写次数）下，能存放更多的数据。反过来说，相同数据下，能减少IO读写次数，（检查索引的时候是随机IO）。 同时，范围查询速度更快。