mysql调优-初始mysql体系结构和理解mysql底层B+tree索引机制

一mysql体系结构

 如下图所示

 

正确的创建合适的索引是提升数据库查询性能的基础

二索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构

 

为什么要使用索引？

1.索引能极大的减少存储引擎需要扫描的数据量

2.索引可以把随机IO变成顺序IO

3.索引可以帮助我们在进行分组、排序等操作时，避免使用临时表

三什么是B+tree?（Binary Search Tree）

1.二叉查找树

 

2.平衡二叉查找树（Balanced binary search tree）

 

平衡二叉查找树的缺点：

1.它太深了：数据处的（高）深度决定着他的IO操作次数，IO操作耗时大

2.它太小了：每一个磁盘块（节点/页）保存的数据量太小了 没有很好的利用操作磁盘IO的数据交换特性 也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作

 

3.多路平衡查找树（B-树）

4.加强版多路平衡查找树（B+树）

1，B+树是B-树的变种（PLUS版）多路绝对平衡查找树，他拥有B-树的优势

2，B+树扫库、表能力更强

3，B+树的磁盘读写能力更强

4，B+树的排序能力更强

5，B+树的查询效率更加稳定 

 

四mysql B+Tree 索引体现形式

mysql B+Tree 索引体现形式——myisam

举例如图：

下面是myisam引擎的索引表现图

mysql B+Tree 索引体现形式——Innodb

Innodb与myisam的比较

五索引知识的补充

1.列的离散性

找出离散性最好的列？

越大离散型越好 结论： 离散性越高，选择性就越好

2.最左匹配原则

对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过

3.联合索引

（1）单列索引 节点中关键字[name]

（2）联合索引 节点中关键字[name,phoneNum]

（3）单列索引是特殊的联合索引

（4）联合索引列选择原则

1，经常用的列优先 【最左匹配原则】

2，选择性（离散度）高的列优先【离散度高原则】

3，宽度小的列优先【最少空间原则】

经排查发现最常用的sql语句：
Select * from users where name = ? ;
Select * from users where name = ? and phoneNum = ?;
解决方案：
create index idx_name on users(name);错误的
create index idx_name_phoneNum on users(name,phoneNum);根据最左匹配原则，该索引包含了name索引

4.覆盖索引

（1）如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。 （2）覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

5几条理解

（1）索引列的数据长度能少则少。

（2）索引一定不是越多越好，越全越好，一定是建合适的。

（3）匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引；

（4）Where 条件中 not in 和 <>操作无法使用索引；

（5）匹配范围值，order by 也可用到索引；

（6）多用指定列查询，只返回自己想到的数据列，少用select *；

（7）联合索引中如果不是按照索引最左列开始查找，无法使用索引；

（8）联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；

（9）联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；