数据库索引和索引优化

索引和索引优化

今天在葡萄城的公共课上，看到一个关于索引的优化的讲解，自己记录一下。

B+树

Mysql InnoDB使用B+树作为索引，如下图，是一个简化的B+数:

使用B+树作为索引有点非常明显的优点
1、B+树的数据都保存在叶子节点中，非叶子节点只保存指针，这样可以极大的减少数的阶数。如图如果每一阶可以存储1000个值，那么3阶树即可以存储1000*1000*1000=10亿个数据。而根节点一般是常驻内存的，所以查找10亿数据，2次磁盘IO即可完成。

2、B+数的所有叶子节点之间按顺序建立了链指针，也就是所有数据一定是按顺序存储的，并且每个叶子到根的距离相同，非常适合范围查找.

 

索引的几个概念

1、聚簇索引: 以主键创建的索引，叶子节点的数据是整行的全部数据，叶子节点也叫数据页。
2、普通索引:以非主键创建的索引，叶子节点数据是主键的值。
3、回表查询:从普通索引查找到主键值以后，再从聚簇索引根据该主键值查找相关数据的行为称为回表。
4、索引覆盖:普通索引可以有多列，如果要查询的数据全部在索引列中，则不会产生回表，称为索引覆盖.

 

 聚簇索引

下图是一个MySql InnoDB的聚簇索引示例:

每一个蓝色块是一页，每页16KB，MySql InnoDB每次磁盘10会读取1页数据。假如主键占用16字节则每页可以保存1024个主键，三阶即可存储10亿行数据的索引。查询任意数据只需要两次磁盘I0.所以，我们应当尽量减少索引字段的长度，这样一次IO读取的数据就越多，查找范围就越大。

例如：我们查找select * from user where id>18 and id <40 示例:

1、从内存中页1（一级节点为内存页），找到>18的数据在页3

2、一次磁盘IO加载页3，找到>18的数据在页8

3、一次磁盘IO加载页8，因为页8是叶子节点，数据(叶子节点)是链表链接的，所以二分法定位到满足条件的数据19

4、因为是范围查找，可以直接遍历链表，页8遍历完了，继续加载页9、页10、页11、页12发现41大于40，查找结束。

说明：普通索引会有一次回表。

 

优化建议

1、【重要】在表中建立索引: 建立索引时，优先考虑where和order by使用到的字段。业务上唯一的字段，即使是组合字段也要建唯一索引。

2、使用自增ID作为主键: 自增ID的是连续的，主键是聚簇索引，会连续的在磁盘中写入数据，减少了并不是业务逻辑的主键，业务逻辑不分页和移动数据的操作，IO速度最快。注意这是逻辑存储的主键推荐使用自增列作为主键，更不推荐与逻辑主键公用一个自增ID

3、索引不会包含有 null 值的列: 避免表字段运行为nul，只要列中包含有 nul 值都将不会被包含在索引中，复合索引中只要有一列含有 null 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时建议不要让字段的默认值为 null。

4、【重要】使用短索引: 如果有一个 varchar (255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。

5、使用复合索引: 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。

6、like要满足最左前缀原则: 一般情况下不推荐使用 ike 操作，如果非使用不可，需要满足最左前缀原则。like “%zh%” 不会使用索引而 like “zh%”可以使用索引。

7、不要在列上进行运算: 这将导致索引失效而进行全表扫描，例如: SELECT * FROM table nameWHERE YEAR(column name)<2017:

8、不使用 not in 和<>操作: 这不属于支持的范围查询条件，不会使用索引。

9、不要使用select *:非聚簇索引的select * 会导致回表，如无必要，应尽量避免。

10、对常用的查询尽量索引覆盖: 对经常需要查询的列，尽量做到索引覆盖，索引也不是越多越好，多了会占用大量磁盘空间，而且太多的索引对后期插入、更新都存在一定的影响，合理规划。

11、不建或少建索引的情况: 表记录太少，或经常插入、删除、修改的表，数据重复且分布平均的表字段 (如10W条记录，只有A\B两种值) ，经常和主字段一块查询但主字段索引值比较多的表字段

12、大数据字段独立建表: 回表查询，需要将整行数据读取，由于大字段占用空间较大，带来大量IO操作，影响数据读取性能。大数据字段独立建表，可以使其他数据存储更集中，IO操作更少。

13、避免进行null值的判断: 因为索引中不含null，所以查询null不会使用索引。

14、关联时使用索引: JOIN也是多表关联的常用的关键字，有LEFT JOIN、RIGHT JOIN、JOIN等。关联时使用索引避免扫描和笛卡尔判断，是提升join性能的绝对杀手锏。

15、【重要】超过2个表禁止join: 要join的字段，数据类型保持绝对一致，关联的表字段都要建立索引。

 

出处：https://gcdn.grapecity.com.cn/course-58.html