性能调优MySQL 一

性能调优Mysql 一

什么是索引

答：索引是帮助MySql高效获取数据的排好序的数据结构

索引数据结构

二叉树

如果查询条件是“where col2 = 67” 且没有索引的情况下需要io 8次才能得到想要的数据，但是如果采用二叉树的数据结构构建了索引

只需要4次，“col2 = 12”的情况是 6/2 可见使用了二叉树的情况，查询的效率大大提升。

但是这是一个不平衡的二叉树，根结点就是第一次输入的数，呢么如果查询的条件是“where col1 = 6”，col

1的结构是一个线性的数据

呢么二叉树就变成了一个线性的链表的结构，这样的查询效率，等同于遍历，所以MySql没有采用二叉树的方式建立索引

缺点：

对于线性有序的数据，没有影响查询的效率，在占用了额外的存储空间的情况下，效率等同于全表查询

红黑树 （平衡二叉树）

1.结点是红色或黑色。

2.根结点是黑色。

3.每个叶子结点都是黑色的空结点（NIL结点）。

4 每个红色结点的两个子结点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色结点)

5.从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点。

**也就是说二叉查找树（BST）**是自动根据左右子数都曾数作出平衡的数据结构

这样查询条件为 “where col1 = 007”只需要io 三次，数据库性能的提升的核心就是减少Io的次数

缺点：

平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下，所以因为数据量大的情况下树的层数不可控，所以不适用Mysql

考虑到数据库性能的提升的核心就是减少Io的次数，减少io次数的核心是减少索引的数据结构的层数，如果将根结点不作为一个单一的节点，而是作为一个数据叶，里面存放多个数据，这样层数就大大可控，也就是说每个节点存储多个元素、摒弃二叉树结构，采用多叉树

Hash表

对索引的key进行一次hash计算就可以定位出数据的存储位置

很多时候hash索引要比B+索引更高效

仅能满足 “=” “in”，不支持范围查询

hash冲突问题

理想情况下就一次就可以完成磁盘io，hash运算是非常快的

但是工作中99.99%都是用的是B+TREE

原因：1.hash冲突

​ 2.不支持范围查询（呢么久可以回到B+TREE叶子节点的双向指针）

B-Tree

这里首先明确一点影响sql速度的因素有不限于以下两种，但是涉及到索引结构的只有以下两种：

B-Tree

B+Tree

1.io数据页 2.在rom中进行数据的比对 但是io产生的影响要远大于数据比对产生的影响

呢么这样的结构可以把数据控制在3层么（io3次）答案是是的

一个数据页的大小是16k，如果索引的大小是bigint（8b）+ 6位的第二层索引的地址，所以第一层就能存放16k/14b=1170个，同理第二层也能存放1179个索引，第三层因为有一行数据（通常没有特别长的文本的情况下不会超过1k）也就是说第三层能存放16个，按照上述的大小作出估计的话一个b+tree可以存放

2100w数据，按照500w开始做分库分表的经验来看，b+tree足够满足存储数据的要求，此时已经得到了巨大的提升，但是还可以更快

对于Mysql的高版本已经吧根节点、甚至所有非叶子节点全部放到了内存当中，这样会更快（三级缓存）

一般情况下互联网公司500w开始分库分表，2100w就是不想分，可以再加一层，是个数据达到不可想象256亿

叶子节点有指针有链接，提高访问区间的性能（MySQl对B+TREE有优化 ）

首先B+TREE的数据是有序的，可以通过双向指针快速的访问相邻的节点满足范围查询，当然如果需要访问的数据页特别多的情况下，也存在时间较长的问题，相比于B+TREE，HASH索引根本不支持范围查询，B-TREE如果要查找的数据不在一个数据页上，就需要从根接待您重新查找

MyISAM存储引擎索引实现

数据的表和索引都是存储在硬盘中的，MyISAM中表的索引是存储在MYI文件中的从中找到数据行所在的地址去MYD文件中找到对应的DATA

InnoDB存储引擎索引实现

InnoDB的索引存储的方式和MyISAM不一样的是索引和数据是存储在一个文件当中的而且是按照B+tree的结构，叶子节点上的Data就是行数据

这里采用的是聚簇索引

聚簇索引：叶子节点包含了完整的数据记录 （InnoDB）

所以数据库表创建的时候，需要设置一个主键（主键索引），并且推荐使用整型的自增索引，如果真多不设置或者不满足唯一的特征，MySQL会主动去寻找一个能成为主键的列，或者维护一个隐藏的列类似于row ID的列作为主键

注意是整型的自增索引 ：有些公司目前是还有在用uuid作为主键的，uuid是一个字符串，有字母有数字的，这就需要按照ASCII码一位一位的比较速度非常的慢，非常影响效率，使用整型也会节省空间

为什么推荐自增的主键

MySQl在构建索引的时候会不断的新增叶子节点，而且会保证叶子有序，所以如果不是自增的主键会，构建的时候首先会分裂现有的数据页形成新的页面，还存在平衡的问题，速度慢，如果是有序的只需要在数据页的后面新增就行。

主键索引和非主键索引

MySQL只会有一个主键索引是聚簇索引，如果没有设置会默认根据rowID维护

为什么一个主键索引是聚簇索引？

1.节省空间（主要，索引+树+data耗费空间）

2.一致性

列多建二级索引

联合索引

一张表不建议建立很多的联合索引，所以需要设计联合索引

流程：先从联合索引找到主键之后再去聚簇索引寻找数据（回表操作）

非聚簇索引：叶子节点不包含数据，只包含了数据索引的地址（InnoDB）

相同情况下聚簇索引的速度要比非聚簇索引的速度要快

联合索引的底层实现

按照name、age、position三个字段，首先是看name，依次对比age、position，一定会不一样，（辅助索引可能会出现一样的情况）

最左前缀原理

只有第一条会走索引

原因：索引是根据字段的顺序排好顺序的，只有根据name筛选之后，age、position才是有序的