MySQL性能优化 - 存储引擎与执行引擎

1、MySQL体系结构图

2、插拔式存储引擎

MySQL存储的存储引擎是插拔式的，指定在表之上，即一个库中的每一个表都可以指定专用的存储引擎。

不管采用什么样的存储引擎结构，都会在数据区产生对应的一个frm文件（表结构定义描述文件）。

（1）CSV存储引擎

即数据存储在CSV文件中，特点：

> 不能定义索引，列定义必须为not null，不能设置自增列。不适用大表或数据的在线处理。

> 数据存储使用逗号隔开，可直接编辑CSV文件进行数据的编排。数据安全性低。

注：编辑后，要生效使用flush table xxx命令。

应用场景：数据的快速导入导出；表格直接转换为CSV。

（2）Archive存储引擎

对数据进行压缩存储，数据存储为ARZ格式。特点

> 只支持insert和select操作

> 允许自增ID建立索引

> 行级锁

> 不支持事物

> 数据占用磁盘少

（3）Memory存储引擎

数据存储在内存中，IO效率比其他引擎高，服务重启丢失数据，内存表数据默认是16M。

特点：

> 支持hash索引, B tree索引，默认是hash（查找复杂度0（1））

> 字段长度都是固定长度varchar（32）=char（32）

> 不支持大数据存储类型，如blog、text

> 表级锁

（4）Myisam

5.5版本之前的默认存储引擎，特点：

> select count(*) from table 无须进行数据扫描

> 索引（MYI）和数据（MYD）分开存储

> 表级锁

> 不支持事物

（5）InnoDB

5.5版本之后的默认存储引擎，特点：

> 事物ACID

> 行级锁

> 聚集索引（主键索引）方式进行数据存储

> 支持外键关系保证数据完整性

 

3、查询优化

（1）查询执行的路径

 

（2）客户端

半双工工作模式，数据接收端一直等待数据传输结束。

查看mysql连接 show （full）processlist

线程状态 sleep、query、locked、sorting result、sending data

通过 kill {id}

（3）查询缓存

缓存sql查询结果集和SQL，新的SQL先从缓存获取数据。

判断标准：判断SQL语句是相同

缓存参数：

query_cache_type  0 不启动缓存 1 启动查询缓存，SQL_NO_CACHE不缓存 2 启动缓存，SQL添加参数 SQL_CACHE

query_cache_size  允许缓存的存储大小，最新40K，默认1M

query_cache_limit  限制查询缓存最大能存储的结果集，默认1M

show status like '%Qcache%' 查询缓存情况

查询缓存不会被缓存的情况：

> 查询语句中有一些不确定的数据时，不会被缓存，如now() ,curdate()等字段

> 查询结果大于 query_cache_limit

> 对于InnoDB，事物修改一个表，事物提交前相关查询缓存失效

> 查询表示系统表

> 查询语句不涉及表

（4）查询优化器

> 使用等价变化规则 如：5=5 and a > 5  改为 a > 5 或者 a < b and a =5 改为 b > 5 and a = 5

> 优化 count、min、max等函数

min 函数只找索引最左表；max 找索引最右边；myisam 引擎 count(*)

> 覆盖索引

> 提前中止索引

> in 优化  先进行排序，然后使用二叉树

（5）执行计划

执行计划id ：select查询序列号，id相同，执行顺序由上而下，id不同，如果是子查询id的序列号会递增，id越大，执行优先级越高。

执行计划select : 查询从好到坏 system、const、eq_ref、ref、range、index、all

执行计划extra：

using filesort 对外部文件排序，而不是表内的索引。

using temporary 临时表保存中间结果，常见 order by 或 group by

using index 使用覆盖索引，避免访问表的数据行，效率高

using where 

select tables optimized away

3、定位慢查询SQL

show variables like '%show_query_log%'

set global show_query_log = on

set global show_query_log_file = "/var/*.log"

set global log_queries_not_using_indexes=on

set global long_query_time = 0.1 (秒)