mysql数据库优化

数据库索引

数据库用的是B+树来作为索引，用以提升查询速度。

数据库索引数据结构

&. 可以作为数据库索引的数据结构及优缺点

* hash散列

# 优势: 精确查找速度快，一次朝朝直接找到需要的数据，避免不必要的IO。

# 不足: 不能使用范围查询

* 完全平衡二叉树

# 优势: 可以有效的进行范围查询（完全平衡二叉树的左边节点永远比右边节点值小）

# 不足: 索引往往以索引文件的形式存储的磁盘上，完全平衡二叉树深度太大，每个节点都走一次IO的话， 耗费太大

每次增加索引都可能改变整个数据结构，性能耗费太大。

* B+Tree: B+Tree相对于哈希散列，精确查询速度较慢，但可以进行范围查询。

相对于完全平衡二叉树深度比较小，增加性能不会耗费过大。

 

B+ Tree数据结构

&. B+ Tree是一种多路搜索树

* 所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page）

* 非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点 上。其次，所有的叶子节点由指针连接

&. B+Tree的层数：

磁盘本身存取就比主存慢很多，所以会预读数据，即使只需要一个字节，磁盘也会从这个位 置开始， 顺序向后读取一定长度的数据放入内存，预读的长度一般为页的整数倍。

B+Tree每个节点都设置为1页，大小为16K.

假设B+Tree深度为2，每条数据为1K；

第一层非叶子节点存放单位数据为bigInt（4B），指针（（暂定）8B），也就是说第一层存放非叶子节点个 数为：16*1000/12 = 1333

第一层非叶子节点每个单位数据对应第二层叶子节点的一页，大小也为16K，也就是说一个非叶子节点对应 的叶子节点存放的数据为16条，

两层深度的B+Tree可以存放数据的条数为：1333*16 = 21328条。

三层可以存放的数据为：1333133316 = 28430224 ，千万级别，所以，一般数据B+Tree深度只有三层

&. 在Innodb中，一定会有主索引（如果没有，会自动建一个隐藏的），主索引存的叶子节点就是具体数据，辅助索引的叶子节点数据为主索引值

&. InnoDB 和MyISAM 两个存储引擎都用的是B+Tree数据结构来存储索引，的区别是：一个InnoDB叶子节点存储的是具体的数据，MyISAM存放的是数据地址

 

数据库的存储（索引页）

&. 数据库查询的最小单位不是行，而是页，当查询时，先通过索引查到数据所在的数据页，然后检测该数据页是否在缓冲池中，如果在直接返回，如果不在就会通过聚簇索引从磁盘中读取对应的数据页，然后把该页放入缓冲池中。缓冲池通过LRU算法把最常用的排在前列，不常用的放在列尾，刚进入缓冲池的排在列中，如果缓冲池满了，会删除列尾的数据页。

&. 索引页组成有一个空间是页目录，页目录里维护多个slot，一个slot包含多条记录。每个slot占2个字节，记录这个slot里的行记录相对页初始位置的偏移量。通过偏移量二分查找发找出数据。

 

InnoDB 存储模型

&. InnoDB 在更新数据的时候会采用 WAL 技术，也就是 Write Ahead Logging ，这个日志就是 redolog ,用来保证数据库宕机后可以通过该文件进行恢复。

在更新数据库记录时，对应redolog的日志可能是：第五数据页偏移量为12的位置写入一个17。

redolog的大小是可配置且循环使用的，例如配置大小为4G，一共4个文件，先从第一个顺序写，写到第四个再从第一个开始，类似于一个环。

如果在更新的时候，该数据所在的数据页在缓冲池中，就会更新缓冲池中的数据页再写入redolog，这时候缓冲池中数据页和磁盘中的数据页不一样，这种数据页就是脏页，当脏页内存不足或其他原因要丢弃的时候，就会写到磁盘中。

&.undolog主要是用于事务回滚和MVCC。

* undolog是逻辑日志，通过记录相反sql用以事务回滚，例：

insert 对应delete delete 对应insert update对应另一个update。

* MVCC是指镜像读，当一个事务需要查询某条记录，而该记录已经被其他事务修改，但该事务还没提交，而 当前事务可以通过 undolog 计算到之前的值。

&. binlog用以进行高可用，用来将数据同步到集群内其他的 MySQL 实例。

* 与redolog不同：

# 他是在存储引擎上层 Server 层写入的，他记录的是逻辑操作，也就是对应的 sql ,而 redolog 记录的底层 某个数据页的物理操作

# redolog 是循环写的，而binlog 是追加写的，不会覆盖以前写的数据。

* 与redolog相同：

# 都需要在事务提交前写入文件

# 写入页都需要通过 fsync 来保证落盘

&. 由于事务的一致性，redolog和binlog都写入成功才commit，有一个失败就会回滚。

&. 一条sql语句的执行流程：update person set age = 30 where id = 1;

* 分配事务id,开启事务，获取数据库锁，如果没有等待直到获取锁

* 执行器先找到id=1的数据页，如果缓冲池中没有，就从磁盘中获取到这个数据页放到缓冲池中

* 在数据页中找到id=1的数据，去除，把age改成30写入内存中

* 生成redolog undolog 到内存，把redolog状态改为prepare

* 将redolog undolog 写到文件并调用fsync

* server生成的binlog并写入文件调用fsync

* 事务提交，将redolog状态改为commited释放锁

 

数据库调优

数据库调优遵循漏斗原则

减少数据访问（减少磁盘访问）

&. 因为加索引会导致增删改性能，增加索引这种方式适合查询次数比较多，增删改的次数比较少的场景

&. 创建并正确使用索引:

* 避免过度索引：不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还 会影响到更新速度，这被称为过度索引。

* 复合索引: mysql每次查询只能用一个索引，当多个条件时，就可以用符合索引。

创建联合索引（a,b,c），相当于创建了（a,b）（a,c）（a）索引，b或c不能单独用作索引。

* 索引不会包含有NULL值的列：只要列中包含null值索引就是无效的，符合索引有一列为null也是无效的，所 以数据库实际时尽量不要让字段默认值为null

* 尽量使用短索引：短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

* 排序索引：mysql查询只能用一个索引，如果where使用索引，order by就不会使用索引的，如果一定要用多 个列的排序，最好用组合索引

* like语句：最好不要用like语句，如果要用，like 'aa%'会使用索引，而like '%aaa%'不会使用索引。

* 不要在列上进行运算：比如：select * from table1 where YEAR（data）;不会使用索引。

* 最好不要使用not in语句:因为not in语句是全表查的，可以用NOT EXISTS 代替。

&. 例：select id,name from table1 where type = 2;

如果这个sql经常使用，则把id、name、type组成创建复合索引，则不需要访问table1直接就可返回。

 

减少交互次数（减少网络传输）：

&. 批量提交：当要往一个数据库中插入大量数据就要使用批量插入，减少交互次数。

&. In List :很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库.

注意：如果list数量过大，性能也会收到较大影响，可用exists来代替

&. 设置Fetch Size：当我们采用select从数据库查询数据时，根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。

所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

&. 使用存储过程

&. 优化业务逻辑

 

返回更少数据（减少网络传输或磁盘访问）

&. 数据分页处理

&. 只返回需要的字段

 

减少服务器CPU开销（减少CPU及内存开销）

&. 使用绑定变量

* 非绑定变量写法：Select * from employee where id=1234567

* 绑定变量写法：Select * from employee where id=? Preparestatement.setInt(1,1234567)

* 优点：1、防止SQL注入 2、提高SQL可读性 3、提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使 用绑定变量我们称为软解析。

&. 合理使用排序：大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降。

&. 减少比较操作: 相对于a>b a==b,数据库并不擅长操作 a like '%abc%'

&. 大量复杂运算在客户端处理：如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等应该尽量放在客户端处理。

 

利用更多资源（增加资源）

&. 增加服务器资源