[基础原理-DB]mysql索引

索引原理

用B树/B+树作为存储数据结构，通过索引进行二分法查找。由于mysql索引和数据都是存储在磁盘，每次查找都是磁盘IO加载到内存进行比较查找，B树的高度低减少了磁盘IO次数，提高了查找效率。

索引结构

查找过程

1 IO读取根节点数据，在内存中比较，得到子节点指针

2 重复1，直到得到叶子节点数据指针

3 通过数据指针得到数据

索引创建和使用规则

1 最左匹配原则，范围查询的字段索引无效（>、<、between、like）。where后面查询条件顺序mysql会调整为索引的顺序。

2 索引建在区分度高的字段。因为区分度低意味着非叶子节点的关键少，数据全集中在叶子节点，并且产生大量IO和内存查询。

聚簇索引(主键索引)和非聚簇索引(非主键索引)

主键索引的叶子节点存储的是整行数据，非主键索引的叶子节点存储的是主键的值。如下图所示：非主键索引的查找需要两次查找

索引失效案例

当全表扫描的时间成本小于索引查询的时间成本时会直接通过全表扫描

mysql InnoDB一页是16k，假设记录总数为n

1 计算全表扫描一共会扫描多少页：show table xxx like 'salary_static'得到全表的大小x byte(data_length)，所以一共要扫描 diskTime = x/1024/16个记录页

timeCost1 = diskTime * t1 + n * t2 (t1为IO单位时间，t2为cpu单位时间)

2 通过索引查询:如果是非主键索引，查询时需要回表,msql认为每次回表的时间需要一次单独IO的时间。假设走索引需要扫描k条记录，默认忽略掉索引的查找时间。

timeCost2 = k * t1 + k * t2(t1为IO单位时间，t2为cpu单位时间)

如果timeCost1 > timeCost2，那么mysql会走全表扫描。

参考文章：https://tech.meituan.com/2014/06/30/mysql-index.html