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 (秒)