Mysql数据库优化相关

1、mysql通信协议
一般数据库连接都是同步的，mysql即支持长连接也支持短连接；
mysql支持的通信协议
（1）Unix Socket
没有指定 -h参数，就使用socket方式登录。它可以不用通过网络协议，使用mysql.sock连接到mysql的服务器。
（2）如果指定-h参数，则使用TCP/IP协议
编程语言的连接都是使用这种方式
通信方式
单工、半双工、全双工
mysql使用了半双工的通信方式。客户端与服务端之间都可以发送数据，但是不同同时进行。所以客户端发送sql给服务端时，数据是不能分成小块发送的 ，不管sql语句有多大，都是一次性发送。所以当我们使用动态sql，批量插入数据，values后面的值过多，或者 where 条件in 里面的值过多，都会出问题；
同样，对于服务端来说，也是一次性发送所有数据，所以我们在程序里要避免不带limit的查询。

2、查询缓存
mysql内部自带缓存模块，默认是关闭的。
mysql自带的缓存对查询sql的一致性要求严格，同时数据发生变化时缓存也会失效，所以mysql不推荐自带的缓存。一般开发中将缓存交给orm框架或者独立的缓存服务（redis）；
mysql8.0中，自带的缓存也已经移除了。

3、语法解析和预处理
语法解析，解析基本的单词、单双引号等，预处理，字段表名等是否存在；

4、查询优化和查询执行计划
优化器会把上述步骤得到的解析树变成一个查询执行计划，查询执行计划是一个数据结构。执行计划里面定义了多表关联时优先查询那张表，以及使用那些索引。当然，这只是一个相对优选的计划。
mysql中可以通过explain工具查看执行计划。

5、存储引擎
数据库表在存储数据的时候需要组织数据的存储结构，这个存储结构就是由存储引擎决定的，所以存储引擎又可以叫做表类型。
(1)MyISAM
特点：
支持表级别的锁（插入和更新会锁表），不支持事务。
拥有较高的插入和查询速度；
存储了表的行数（count速度较快）；
适合于只读类型数据分析的项目；
（2）InnerDB
特点：
支持事务，支持外键，数据的完整性和一致性很高；
支持行锁和表锁；
支持读写并发，写不阻塞，读MVCC；
特殊的索引存放方式，可以减少IO，提高查询效率；
适用于经常更新的表，存在读写并发或者需要控制事务的业务系统。
（3）Memory
数据存储在内存中，读写的速度很快，但是数据库重启或者奔溃后数据会全部消失，只适合做临时表。
（4）CSV
他的表实际上是带有逗号分隔值的文本文件；
特点：不允许空行、不支持索引。格式通用，可以直接编辑。适合在不同数据库之间导入导出；
（5）Archive

6、mysql体系结构总结

7、更新语句执行流程
更新语句首先需要执行查询流程，得到符合条件的数据，不一样的是得到数据之后的操作。
以下分析是针对InnoDB存储引擎

InnoDB内存结构和磁盘结构

Buffer Pool 缓冲池
Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash
Index，另外还有一个（redo）log buffer。
（1）buffer pool,缓存的是页面信息（InnoDB操作数据的最小逻辑单元），包括数据页、索引页。
（2）change buffer，写缓冲。当数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载数据判断是否重复，这种情况下的数据就存放在change buffer 中，提升更新语句的速度。
（3）Log buffer ，为了防止数据库宕机或者重启时数据的丢失。把所有对页面的修改操作写入日志文件。
（4）Adaptive Hash index

案例：
update user set name=‘penyuyan’ where id=1;
1、事务开始，从内存或磁盘取到这条数据，返回给Server的执行器；
2、执行器修改这一条数据的值为penyuyan；
3、 记录原来的数据name=‘zhoabing’ 到undo log,事务回滚使用；
4、记录name=‘pengyuyan’ 到redo log，防止服务器异常；
5、调用存储引擎接口，在内存（buffer pool）中 修改name=‘pengyuyan’;
6、提交事务；

mysql的内存和磁盘之间的数据同步有很多后台线程执行；

Binlog
以事件的形式记录了所有的DDL和DML语句（记录的是操作而不是数据，所以是逻辑日志），可用于实现主从复制和数据恢复；
与redo log不一样的是，binlog的内容可以追加，大小没有限制，因此有更广的适用性。
开启了binlog的数据更新流程：

mysql索引
数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询，更新数据库表中的数据；
索引类型：Normal、Unique、Fulltext
索引数据结构：
常见数据结构：
（1）二叉查找树 Binary Search Tree

可能存在的问题：

（2）平衡二叉树（AVL Trree-Balanced Binary Search Tree）
左右子树深度差绝对值不能超过1
左旋、右旋
数据存储：

一个磁盘块就是一个数据页（InnorDB–>16k）；
AVL数的问题：
1）操作系统，与磁盘交互频繁；
2）树的深度；

（3）多路平衡查找树（Balanced Tree B树）

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

非叶子节点存储键值+指针；
叶子节点存储数据；
B+Tree的特点：
1）B Tree能解决的问题，B+Tree都能解决；
2）扫库、扫表能力更强；
3）磁盘读写能力更强；（IO次数减少）
4）排序能力更强；（叶子节点的数据排序，范围查找时不需要返回跟节点，从数据节点可以直接往后顺序查找）
5）效率更加稳定；

mysql数据库事务
1、事务并发带来的问题；
1）脏读
一个事务读取了另一个事务未提交的数据；
2）幻读
一个事务前后两次读取的数据不一致，是由于其他事务插入数据造成的。
3）不可重复读
一个事务前后两读取数据不一致，是由于修改或删除数据造成的。
2、SQL92事务隔离级别；
1）Read Uncommitted(未提交读)
一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做RU，它没有解决任何的问题。
2）Read Commited(已提交读)
一个事务只能读取到其他事务已提交的数据，解决了脏读，但是会出现不可重复读。
3）Repeatable Read(可重复读)
在同一个事务里面，多次读取同样的数据，结果是一致的。解决了不可重复读，会出现幻读。
4）Serializable(串行化)
在这个隔离级别里面，所有的操作都是穿行执行的，不存在事务的并发，所以解决了所有的问题。

Mysql InnoDB事务隔离级别

InnoDB在RR级别就解决了幻读的问题，所以InnoDB默认使用RR作为事务隔离级别。

事务隔离级别的实现
（1）LBCC
基于锁的并发控制，读取数据的时候就锁定数据。
（2）MVCC
多版本的并发控制，在读取数据的时候创建一个备份（快照），后面再来读取这个备份，从而保证读取的数据一致性。

MYSQL InnoDB锁的基本类型
（1）根据粒度划分：行锁、表锁
表锁的加锁效率高，但是冲突率也更高；
1）行锁又有共享锁（读锁）和排他锁（写锁）
获取了一行数据的行锁以后，可以读取数据，但修改数据会造成死锁。
当一个事务获取排他锁以后，其他的事务不能再获取共享锁和排他锁。
2）表锁有意向锁
意向锁是由数据库自动维护的，当加共享锁时，会自动加上意向共享锁；当加上排他锁时会自动级商意向排他锁。

Mysql优化
1、配置优化
1）服务端
（1）修改配置参数max_connections，增加服务端的可用连接数；
（2）及时释放不活动的连接；
2）客户端
减少从服务端获取的连接数，不是每执行一次sql都要创建一个连接，则使用连接池；
连接池的使用：Mybatis自带的连接池，或者专用的连接池工具（）
2、架构优化
1）缓存
在应用系统的并发数非常大的情况下，如果不使用缓存，会带来两个问题：一是数据库的压力非常大，另一方面查询也很慢。可以引入第三方的缓存服务。
2）主从复制
第三方缓存是从架构层面进行优化，如何减轻单台服务器的读写压力？考虑做数据库集群。
如何解决主从复制的延迟问题？
（1）异步复制（mysql默认）
对于主节点来说，操作写入binlog，事务结束，返回客户端。对于slave来说，接收到binlog就结束，master并不关心slave的数据是否写入成功。
（2）全同步复制
等待全部事务执行完成再返回给客户端。
（3）半同步复制
主库在执行完客户端提交的事务后不是立刻返回客户端，而是等待至少一个从库
接收到binlog并写到relaylog中才返回给客户端。master不会等待很长的时间，但是
返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取relay
log，写入从库。
3）分库分表
垂直分库，减少并发压力；水平分表，解决存储瓶颈；
4）高可用方案
3、优化器—sql语句优化
4、存储引擎与表结构
5、索引

总结