MySQL调优

二叉树

二叉树存储索引时，单边递增退化成链表

查询6这条记录需要经过6次磁盘IO

缺点：元素单边递增退化成链表，性能差

红黑树

红黑树也叫二叉平衡树，单边递增会自动平衡

查询6元素需要3次磁盘IO.

缺点：当数据达到几百万时，树的高度不可控，需要很多次磁盘IO, 性能低

B树

B+树

所有索引元素存在于叶子节点上

mysql底层每页存储16KB的数据

show global status like 'Innodb_page_size';
1

bigint 占8个字节 +磁盘空白空间占6个字节

16kb/(8+6)k ==1170
1

当bigint 作为主键索引时，可以放1170个元素

假设一个叶子节点占1kb

那么底层叶子可以放16个索引

1170*1170*16=21,902,400 
1

一颗高度为3的B+树，当所有节点放满后可以存两千万条记录。

B+树 叶子节点从左到右依次递增

查找过程

非叶子节点全部加载到内存，快速定位到某个节点，只需要一次磁盘IO

由此可知上千万的数据合理走了索引，查询速度非常快。

MyISAM存储引擎

表test_myisam使用的是MyISAM存储引擎, 查看其表结构

test_myisam.frm 存储表结构信息
test_myisam.MYD 存储表数据
test_myisam.MYI 存储表索引
1
2
3

select *from t where t.col=30
1

MYSQL底层执行步骤：

1. 首先判断条件是否是索引字段
2. 不是索引字段，全表扫描
3. 是索引字段，在MYI文件上快速定位到30元素
4. 通过索引磁盘文件地址在MYD文件快速定位到某行记录

InnoDB存储引擎

InnoDB存储引擎底层存储结构

test_innodb.frm 存储表结构信息
test_innodb.ibd 存储表的索引和数据
1
2

• 叶子节点存了索引和数据

聚集索引（聚簇索引）

• 索引和数据放在一起 InnoDB存储引擎主键索引

非聚集索引（非聚簇索引）

• 索引和数据分开在两个文件存储 MyISAM存储引擎主键索引

主键索引存储结构：

二级索引（普通索引）存储结构：

根据普通索引查找时步骤：

1. 先在二级索引B+树上查找，定位到索引所在行的主键
2. 再根据主键去ibd文件主键索引树上查找，类似回表查询

总结：InnoDB存储引擎用二级索引查找没有主键索引查找快

为什么InnoDB表必须有主键?

先说建索引的情况

如果你建了主键索引他会默认用主键索引来组织你整张表的数据。
1

如果你没有建的话又是另外一种情况

如果你没有建主键，他会帮你找一个主键，从表里面逐列去找一个列，
这个列里面的数据是所有的数据都不重样。可以添加一个唯一索引。找到的话，
他会用这一列的数据来组织你这个表的所有数据，用哪一个索引来建一个B+数的结构 
来组织你表里的所有数据，如果你那个表里找不到这样的列，
MySQL会自动给你找个隐藏列如１２３４５，
帮你自动维护这整张表的B+数的数据结构
1
2
3
4
5
6

为什么推荐主键是整形呢？

MySQL查找过程，就是把节点load到内存然后在内存里不断的去进行数据的比对。假设UUID，既不自增也不是整形。问一下，是整形的1<2比较的效率高还是字符串的“abc”和“abe”比较的效率高呢？显然是前者，因为字符串的比较是转换成ASICI一位一位的比，如果最后一位不一样，比到最后才比较出大小，就比整形比较慢多了，存储空间来说，整形更小。索引越节约资源越好。

1.更方便遍历

如果主键是自增的，那么当遍历数据，从当前节点开始，就可以根据节点间的指针快速找到下一个节点去遍历。

2.更快速的做数据插入操作,避免增加维护索引的开销

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

Hash索引

Hash索引效率高，时间复杂度O(1)，不支持范围查询

联合索引

索引最左前缀原理

先根据name排序，再根据age排序，最后根据position排序

以下三条SQL，那些SQL会走索引？

只有第一条走索引
1

索引底层原理和执行流程

准备数据

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
)engine=InnoDB;
1
2
3
4
5
6
7

查看使用到的索引，索引类型是B树

show index from t1;
1

插入数据

insert into t1 values(4,3,1,1,'d');
insert into t1 values(1,1,1,1,'a');
insert into t1 values(8,8,8,8,'h');
insert into t1 values(2,2,2,2,'b');
insert into t1 values(5,2,3,5,'e');
insert into t1 values(3,3,2,2,'c');
insert into t1 values(7,4,5,5,'g');
insert into t1 values(6,6,4,4,'f');
1
2
3
4
5
6
7
8

查看发现元素自动排序

此处查询时没有用order by排序, 由此可见mysql是在插入时排序

InnoDB页的作用

select *from t1 where a =7; 
-- 如果不使用innodb页，则需要查询7次，需要7次磁盘IO
-- innodb页=16kb, 如果使用innodb页只需要查询一次
-- 每次从磁盘读取一页的数据到内存，在内存中遍历查找记录
1
2
3
4

如下图，插入时已经排好序，查询时就不需要排序

当查询 a=3000 时需要遍历链表，此时就比较慢了

select*from t1 where a=3000;
1

使用页目录可以提高性能

参考书目录，1，10，30每章起始位置，有目录就可以确认元素在哪一章

通过页目录查找a=3在第一组

首先利用二分查找法查找数据在哪一组，沿着指针顺着链表遍历组里的数据

当一页16kb放满数据后，会开辟新的页存储。

当页放满后，形成一个两层的B+树

三层B+树，按照主键排序生成的B+树，主键索引

全表扫描：从叶子节点从左往右查找
走索引：从上往下查找

bcd索引生成的B+树

create index idx_t1_bcd on t1(b,c,d); -- B+
1

非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询

explain select *from t1 where b>1;-- 走全表扫描，此时全表扫描比索引更快
-- 走索引，七次回表
-- 先找b=1
-- 找到111，后面七条记录都是不完整的
-- 根据七条记录的主键去回表查询，需要七次回表查询

-- 全表扫描
-- 主键索引
1
2
3
4
5
6
7
8

explain select b from t1 where b>1; -- 走索引
explain select b,c,d from t1 where b>1; -- 走索引
explain select a,b,c,d from t1 where b>1;-- 走索引
1
2
3

BufferPool内存结构与优化分析

select时，会从磁盘拷贝一页数据放到缓存池 ，Buffer Pool默认是128M.
每页16KB, 可以放 128M/16KB =8192页

执行update时，会将磁盘中的页数据读取到Buffer Pool,
在Buffer Pool修改页的数据后持久化到磁盘。修改后的页称之为脏页，
mysql会启动一个后台线程定期将脏页持久化到磁盘。
此时释放Buffer Pool中页的空间，变成白色的空白块。
空白块在Buffer Pool中不是连续存储的，需要free 链表来维护。
页释放后将新增一个控制块添加到Free 链表中。

Buffer Pool修改页后，将页添加到Flush链表的控制块
flush链表维护脏页数据。

那么假设Buffer Pool存满了，此时又有新的页读取到Buffer Pool中 怎么办？

需要淘汰Buffer Pool中最近最少使用的页。
此时需要 lru链表来维护，将最近最少使用的页添加到链表中。
按照最近使用时间先后排列，比如排在最前面的控制块是最近使用过的。

淘汰策略： 淘汰LRU链表最后一个控制块

执行全表扫面时，可能把Buffer Pool中的缓存的所有页全部覆盖。热点数据丢失，相当于换血，影响性能。

怎么解决？

Lru链表分为两部分控制块 热数据区域(占5/8) 冷数据区域占(3/8)

读取新的页时将新页添加到Lru链表冷数据区域头节点，并删除最后一个控制块

当执行全表扫描时，两次访问冷数据区域同一个控制块的时间大于1s时，控制块就被转移到热数据区域头节点，某一页被快速的访问两次，则不是热数据，是全表扫描

如果某一页两次被访问的时间间隔>1s，则认为是两次请求，冷数据控制块就被转移到热数据区域。

update语句底层分析

第二种方式性能低，将整页数据持久化到磁盘，逻辑上连续，物理上不连续，用的是随机IO

第一种方式性能高，顺序IO：文件在磁盘中，提前生成文件
redo log持久化后即使mysql 重启或挂掉，仍然可以恢复数据