数据库性能调优

数据库调优

1. MySQL数据库调优实践

1.1 MySQL数据库架构设计

核心架构模块

数据物理存放位置

• 日志文件：一般存放路径/var/lib/mysql
• 数据索引文件：一般存放日录/var/lib/mysql

常用日志文件

• 错误日志：error log
• 二进制日志：bin log
• 通用査询日志：general query log
• 慢查询日志：slow query log

1.2 SQL语句执行流程剖析

一条完整SQL语句执行流程：

一条SQL语句:

select c id,first name,last name from customer where c id=14;

Server层执行：连接器、查询缓存、分析器、优化器、执行器等

存储引擎层执行：负责数据的存储和提取

1. 连接数据库：show processlist 显示所有连接进程。
2. 査询缓存数据：show variables like ‘query cache type’;
3. 分析SQL语句：语法分析、词法分析、预处理器
4. 优化SQL语句：对查询进行语法调优
5. 执行SQL语句：判断执行权限，然后调用存储引接口

查询缓存数据

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句hash之后的值，value 是查询的结果。

1)查看是否开启缓存

mysql>show variables like ‘query cache type’;

2)查看缓存的命中次数:

mysql> show status like ‘qcache hits’,

3)开启缓存

在/etc/my.cnf文件中修改“query_cache_type”参数值为0或OFF会禁止使用缓存。

值为1或ON将启用缓存，但以SQ!拿到个查询信息层的语句除外。

值为2或DEMAND时，只缓存以SELECTSQL CACHE开头的语句。

修改配置文件my.cnf，在文件中增加如下内容开启缓存:query cache type=1

SQL语句如何解析

分析前SQL语句: select c_id,first name,last name from customer where c id=14;分析后关键字分隔:select,c_id,first name,last name,from，customer，where c_id，=，14

预处理器
进一步检查解析树是否合法，例如表名是否存在，语句中查询的列是否存在等

执行SQL语句

判断执行权限

调用存储引擎接口进行查询

1.3 MySQL存储引擎

MyISAM：高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB：5.5版本后MySQL的默认数据库存储引擎，支持事务和行级锁，比MyISAM处理速度稍慢
ISAM：MyISAM的前身，MySQL5.0以后不再默认安装
MRG MYISAM：将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory：内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Archive：将数据压缩后进行存储，非常适合存储大量的独立的作为历史记录的数据，但是只能进行插入和查询操作
CSV：CSV存储引擎是基于CSV格式文件存储数据(应用于跨平台的数据交换)

InnoDB内存结构

1.3.1 innoDB 内存结构

• InnoDB 内存结构主要分为如下四个区域:
• Buffer Pool 缓冲池
• Change Buffer 修改缓冲
• Adaptive HashIndex 自适应索引
• Log Buffer 日志缓冲

（1）缓冲池

• 缓冲池Buffer Pool用于加速数据的访问和修改，通过将热点数据缓存在内存的方法最大限度限度地减少磁盘IO，加速热点数据读写。
• 默认大小为128M，Buffer Pool中数据以页为存储单位，其实现的数据结构是以页为单位的单链表。
• 由于内存的空间限制，Buffer Pool仅能容纳最热点的数据。
• Buffer Pool 使用LRU算法(Least Recently Used 最近最少使用)淘汰非热点数据页。

​ LRU:根据页数据的历史访问来淘汰数据，如果数据最近被访问过，那么将来被访问的几率也更高，优先淘汰最近没有被访问到的数据。

• 对于 Buffer Pool中数据的查询，InnoDB 直接读取返回。
• 对于 Buffer Pool中数据的修改，InnoDB 直接在 Buffer Pool 中修改，并将修改写入redo log。

（2）修改缓冲(Change Buffer)
Change Buffer(在 MySQL 5.6 之前叫insert buffer，简称 ibuf)是InnoDB 5.5 引入的一种优化策略。Change Buffer(在MySQL5.6 之前叫 insert buffer，简称 ibuf)是InnoDB5.5 引入的一种优化策略。Change Buffer 用于加速非热点数据中二级索引的写入操作。Change Buffer 用于加速非热点数据中二级索引的写入操作。由于二级索引数据的不连续性，导致修改二级索引时需要进行频繁的磁盘10 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同时将写操作录入redolog中，在缓冲到一定量或系统较空闲时进行 由于二级索引数据的不连续性，导致修改二级索引时需要进行