【Mysql】

1.Mysql执行过程

首先通过mysql连接器建立连接，然后会进入分析器对sql进行分析，优化器进行策略优化，最终执行器调用存储引擎执行语句，返回结果

2.mysql的存储引擎

最常见的innodb，事务型存储引擎

还有MyIsam，适用于读多写少的场景，相比于innodb查询快的原因是：本身非聚簇索引，无需校验MVCC；直接存储行记录的地址，无需回表

还有Memory

2.1 存储引擎执行细节

1.查询放入缓存
2.修改数据页中数据（修改前undo log，修改后redo log）
3.binlog刷盘（刷盘前prepare，刷盘后commit）
1
2
3

2.2 undo log、redo log和binlog

undo log：存储历史值，每个指针指向上一个值

redo log：随机IO改为顺序IO，支持反查（通过和binlog日志对比确定事务是否要提交）和回滚（至少能恢复到prepare阶段）

binlog：真正刷盘时调用

2.3 buffer pool的内存淘汰机制

lru链表：用于维护热点数据的顺序问题，利用了最近最少使用算法

防止全表查询，替换所有数据，会有热数据和冷数据的区别，有一个时间间隔参数进行区分，只有在这个指定时间段后再次被访问才会被刷入热数据区

3.事务和隔离级别

3.1 ACID

A原子性：靠undolog

D持久性：靠redo log

I隔离性：靠MVCC和临键锁

C一致性：靠以上三者共同保证

3.2 隔离级别

read uncommited：已读未提交，脏读

read commited：已读已提交，不可重复读

repeatable：可重复读，幻读，通过MVCC解决

serializable ：串行化读取

3.3 MVCC解决幻读

每条行记录都会有两个隐藏字段，分别是事务id（最后一次修改后记录的事务的id，txid）和回滚指针（指向他上一个修改前版本的undo log，串联起来会形成一个undo log版本链）

1.每次开启事务会创建读视图将所有活跃的事务id保存起来，然后统计出最小的事务id和最大的事务id，

2.然后第二次读会将修改的事务id会和我们的读事务id进行对别，如果修改事务id小于最小的读事务id，说明已经提交无需修改，如果在这之间或者大于则需要通过undolog回滚到对应的读事务id，

3.rc（read commit）每次读都会生成读视图，rr（repeatable read）只有第一次读取会生成读视图

4. 数据库调优

4.1分库分表

数据量达到单表1000w时，

当innodb_buffer_pool_size（默认128m）大于物理机内存的75%时

show global variables like 'innodb_buffer_pool_size';
1

4.2 从配置上调优

 vi /etc/my.cnf 
 
 log-bin=/var/lib/mysql/mysql-bin #开启binlog日志
 server-id=1
 max_user_connections=800 #最大用户连接数，默认0，表示不限制
 max_connections=3000  #MySql的最大连接数，默认151，当并发较多时可以适当调大
 back_log=500 # 等待队列，默认50，不超过cat /proc/sys/net/ipv4/tcp_max_syn_backlog数量
 wait_timeout=1800 # 最大闲置时间，默认8h，改为30分钟
 thread_concurrency=64 #线程并发数，默认8，应设cpu核数*2
 innodb_buffer_pool_size=1024M #InnoDB占用的内存，默认128m，16G内存最大可设12G有21%额外开销
1
2
3
4
5
6
7
8
9
10

调整innodb_buffer_pool_size时机：

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)G

show global status like 'Innodb_buffer_pool_pages_data';
show global status like 'Innodb_page_size';
1
2

4.3 查询设计调优

1.尽可能使用索引，包括主键索引、覆盖索引和

2.查询控制在3层表内

3.慢查询优化通过explain、show profile等