Mysql总结

B-树与B+树

为什么采用b+树？
首先看常用的数据结构，Hash查找很快，可以做到o(1),但是不适合范围查找，而且数据本身无序。平衡二叉树本身是有序的，但是存储数据越多层越高，而且也不适合范围查找。接着就是b树，每层存储的数据更多，数据本身也是有序的，但是同样不适合范围查找。

Mysql的数据存储

插入八条数据

数据存储在叶子节点，如果有很多数据分成了很多页，查找起来就很慢。

把页目录的信息提取到上一层，存储每个页开始的值，形成了B+树的结构。

Mysql数据量为什么是2000w左右

估算三层的B+树能存储多少数据呢？已知页大小为16KB，假设一条数据1KB，一页存储16条数据。
第一层只存索引，一条索引大小按照14字节来算：161024/14
第二层同样：161024/14。前两层的索引数量决定了第三层的数据量
1170117016=21,902,400。大概两千万数据。超过两千万的数据再建索引就要第四层了，对性能有影响。

根据a列建立索引，范围查找如何走索引。找到a<6和a>6都是先找到a=6,然后再根据双向指针去找对应范围的数据。

创建bcd字段聚合索引：只存储bcd三个字段的值再加上主键的值，尽量节省空间。

例如寻找bcd为111的那条数据，先根据bcd聚合索引找到了该条索引，再根据主键1回表找到对应数据1111a。
如果是查询cd值11无法走bcd的聚合索引，因为它没办法与索引进行比较。但是查询bc值是11的数据可以，因为即使只有两位，也可以与索引进行比较大小，进而找到对应的数据，这就是最左匹配原则。

如果从上往下走即是走索引，如果叶子节点从左往右走则是全表扫描。

MySQL性能优化

1.选择最合适的字段属性。在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。
2、尽量把字段设置为NOT NULL。
3.使用索引。一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。
4.不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
5.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
4.JOIN查询如何选择驱动表与被驱动表 ?

JOIN查询如何选择驱动表与被驱动表
在JOIN查询中经常用到的 inner join、left join、right join
问题解答：
1.当使用left join时，左表是驱动表，右表是被驱动表 ;
2.当使用right join时，右表时驱动表，左表是被驱动表 ;
3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

3.2 系统内核优化

大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化。

  
net.ipv4.tcp_fin_timeout = 30
#TIME_WAIT超时时间，默认是60s
net.ipv4.tcp_tw_reuse = 1    
#1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭
net.ipv4.tcp_tw_recycle = 1  
#1表示开启TIME_WAIT socket快速回收，0表示关闭
net.ipv4.tcp_max_tw_buckets = 4096   
#系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息
net.ipv4.tcp_max_syn_backlog = 4096
#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接

2、系统参数调优

thread_concurrency = 8
#并行线程数,该参数取值为服务器逻辑CPU数量*2，比如有两个双核的cpu，那么thread_concurrency的值应该设为8。这是个重点优化参数。
​
innodb_buffer_pool_size = 128M

innodb使用一个缓冲池来保存索引和原始数据；如果表里存的大对象字段（Blob）可以设置大些；如果都是文本数据，只是数据量大，那就128M就够了。这里设置越大，在存取表里面数据时所需要的磁盘I/O越少。设置这个变量不要超过到服务器物理内存大小的80%以上，这样会导致操作系统换页颠簸。

代码执行顺序：

from -> on -> join -> where -> group by -> having ->select -> distinct -> order by -> limit

整体过程：

先对多表进行关联，根据条件找出复合的记录

在符合记录的基础上进行where条件过滤

对筛选出来的记录进行分组操作

分组完成后再进行having操作，过滤出满足条件的数据

对取出的记录进行排序

再按照分页条件取出要显示的数据

区分in与exists

如果in里面子查询的数据少可以用in，（外大内小）例如：

SELECT * FROM sys_user u WHERE u.dept_id IN(SELECT dept_id FROM sys_dept WHERE STATUS=0)
如果内层查询的数据量大，而外层查询的数据少，（外小内大 ）则用exists。
注意exists返回的是true或false，ture的话再执行前面的查询语句。

前提是子查询数据量比较少，可以用小表驱动大表，这样效率比较高，如果子查询数据量过大可能效率还不如全表扫描。

快照读和当前读

出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

select 快照读

当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)，加锁的select都是采用当前读的模式。在执行这几个操作时会读取最新的版本号记录，写操作后把版本号改为了当前事务的版本号，所以即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致幻读。

MySQL中普通索引和唯一索引在读写上性能的差别

读性能
在查询中，由于唯一索引限定了数据只会有一条，所以查到第一条满足条件的数据之后，唯一索引的情况下就会停止检索，普通索引则要检索到下一条不符合条件的数据才会停止。故而理论上唯一索引在查询时性能会大于普通索引，但是MySQL是以页为单位从磁盘中读取数据到内存的，对于CPU和内存来说，这个差距微乎其微可以忽略不计。

写性能
在数据库中，有一个change buffer。在更新数据时，如果数据页已经在内存中了，则会直接修改内存中的数据，否则会将修改的数据先缓存在change buffer中，等待下一次读取到相关的数据页时再进行一个合并操作merge。虽然change buffer叫buffer，但是他是一个持久化的数据，所以不用担心数据会丢失，且有后台线程会定期的进行merge操作。
有了change buffer可以减少磁盘的读取操作，使得更新语句的执行速度得到明显的提升。

在唯一索引的场景下，由于更新需要先判断数据是否存在，即是否违反唯一性约束，所以必然会有一次数据页的读取，而此时数据页已经在内存中了，所以不需要使用到change buffer。
在普通索引的场景下，更新会直接写入change buffer。

综上，由于有change buffer的加持，普通索引的写性能会大于唯一索引。但是也有反例，假如你在普通索引下写入数据之后立马进行了读取，那么数据库在写完change buffer之后立马需要读取对应的数据页，然后进行merge合并操作，反而增加了多余的磁盘操作，带来副作用。所以change buffer适合在写多读少的场景下使用。

自适应hash索引原理

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升，则：
1、自适应hash索引功能被打开。
2、经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
限制：
　　1、只能用于等值比较，例如=， <=>，in
　　2、无法用于排序
　　3、有冲突可能
　　4、MySQL自动管理，人为无法干预。

意向锁解决了什么问题

在对行记录进行加锁的时候，同时会在这个表上插入一个意向锁，这样其他事务再加互斥锁的时候就不需要一行一行判断是否有行锁来判断能不能加表锁。

count(列名)、count(主键字段)、count(1)、count(*)到底谁的性能高

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

为什么当offset很大时，查询效率会变慢呢？

比如当我们用 limit 1000000, 10 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。

Mysql日志

redo log：InnoDB 存储引擎层方面的日志，所以如果你使用的存储引擎不是 InnoDB 的话，那就根本谈不上 redo log。用于保障已提交事务的ACID特性。

bin log: MySQL Server 层记录的日志，所以不管是用的什么存储引擎，只要是 MySQL 都是会有 binlog 的存在。binlog日志记录数据变更、用于数据备份、数据复制等操作。在做 MySQL 主从复制的时候，利用的就是 binlog。redolog为什么比buffer写入到磁盘更快呢？1.buffer中的数据是随机写的io，每次修改的数据位置都是随机的，而redolog是追加到文件末尾，属于顺序io。buffer持久化是以page为单位的，而redolog只是修改的数据。

undo log 回滚日志，提供回滚操作，用来回滚到某个版本，undo log一般是逻辑日志，根据每行记录进行回滚。每次对数据库里的数据进行改动，都会生成一条undo log日志，同步完成undo log日志时候就，就可以修改数据库表里的数据了。这保证了原子性、

当有一条记录更新的时候， InnoDB 引擎就会先把记录写到 redo log 里面去，同时更新内存，这样就算是更新这条数据成功了。InnoDB 会在恰当的时候，把这条及记录更新到磁盘上去.

二进制日志（binnary log）以【事件形式】记录了对MySQL数据库执行更改的所有操作。binlog记录了所有数据库【表结构】变更（例如CREATE、ALTER TABLE…）以及【表数据】修改（INSERT、UPDATE、DELETE…）的二进制日志。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。

binlog有两个常用的使用场景：
主从复制：我们会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。
数据恢复：通过mysqlbinlog工具来恢复数据。

你能发现 redo log 竟然是先 prepare 状态，等 binlog 写完之后，才是 commit 状态，这种方式就叫”两阶段提交”。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

MVCC原理 多版本并发控制

如上图所示，201 202 203 204 205代表事务id，每个事务对于数据的操作都会生成一个undo log的版本，串起来就形成了一个版本链，这个版本链就会帮助我们实现MVCC。
Read view可以帮我们确定在做数据多版本查询的时候，应该查询哪一条数据

举个例子，RC隔离级别事务204的两次select生成的Read view。读取小于m_ids的最大事务id的那条数据，两次查询结果分别是6000和7000。由此可见RC隔离级别没有解决不可重复读的问题。

RR隔离级别在事务开启的时候生成一个Read view，然后在整个事务运行的过程中，都复用第一次创建的Read view。所以下图两次读取结果都是6000。

RR隔离级别并没有完全解决幻读问题。如下图所示，如果两次快照读之间出现了当前读并且临界锁的范围覆盖了其他事务中的数据，即2003 张三 6000这条数据，还是会出现两次查询结果集不一致的现象。

两阶段提交