存储引擎及SQL优化原则汇总_ql优化和存储引擎优化-优快云博客

本文链接：https://blog.youkuaiyun.com/u012464024/article/details/106339348

存储引擎及SQL优化原则汇总

不同存储引擎的选择
explain 分析
- 每个字段都表示什么？
- type具体含义
索引使用原则
limit优化
查询效率优化
写入效率
批量写入
删除效率
join、left join选择
profile
Q & A
- Q1. 主从切换反转角色时，转换失败的肯能原因是？

不同存储引擎的选择

主要存储引擎有哪些？

innodb：事务存储引擎
MyISAM：非聚集存储引擎
CSV
Memory：内存存储引擎
Federated：联合存储引擎，默认不安装

聚集索引 vs 非聚集索引？

	聚簇索引	非聚簇索引
代表引擎	InnoDB	MyISAM
插入	按主键顺序插入插入数据到聚集索引所在磁盘文件	按插入顺序到存储在磁盘上
二级索引叶子节点	存主键	存数据行地址
锁	支持行锁、表锁	仅支持表锁
主外键	支持主、外键	仅主键
有无主键	必须有主键	可无
事务还原点	支持	不支持
是否死锁	必须有主键	不会死锁仅支持表锁，不会发生死锁
表空间	大，含索引独立表空间和系统表空间	小仅支持独立表空间
磁盘数据文件	大，含索引	小，索引数据独立
压缩	支持表压缩、页压缩	支持前缀压缩，节约数据空间
事务	支持	不支持
隔离级别	四个隔离级别	仅支持串行级别
并发能力	行锁，并发能力强	仅支持表锁，并发能力弱当大量写时，会发生读饥饿
缓存	缓存索引、数据	仅缓存索引
天然排序	按照主键自然排序	数据按照插入顺序，逻辑无序
select *	无固定字段存储条数执行效率较低	有固定字段存储条数执行效率极高
插入复杂度	复杂对于不同DDL有不同类型的行锁磁盘：随机存储，低效	相对简单 insert：表锁 select：无锁磁盘：顺序存储，高效
索引类型	聚集索引	非聚集索引
select count(*)	无固定字段存储条数执行效率较低	有固定字段存储条数执行效率极高
insert、update、delete	效率高	效率低，表锁
索引	b-tree索引、hash索引	b-tree索引、全文索引
文件格式	FRM：表定义 MYD：表数据 MYI：表索引	FRM：表定义 IDB：表数据、表索引
叶子存储内容	行数据，直接	行数据地址，需再寻址一次，间接
默认安装	是	是

聚集索引 & 非聚集索引哪个更快？

总体而言，聚集索引快，聚集索引是表中行的物理顺序与键值逻辑顺序匹配的，所以查找会更快。

非聚集索引，表中行的物理顺序与键值逻辑顺序不匹配，往往最终只能找到一个行的物理地址，再加上随机磁盘查找降低查询速度。

存储引擎选择？

innodb 多面向更新操作，并发能力强。常用于电子商务、自动化办公系统等。
MyISAM 多面向读、写操作，并发能力弱。常用于新闻、博客、GIS系统等。

explain 分析

每个字段都表示什么？

select_type：查询子句查询类型。共6种：尽量避免SUB_QUERY，表示复杂子查询。
table：当前子查询的表。
type：当前表查询的访问类型。
possible_keys：当前子查询可能用到的索引。
key：使用的索引。
key_len：使用索引长度。（索引长度也是优化的关键，尽量越长越好，索引太短可能发生了索引中断）
ref：索引的具体值或字段。
Extra：额外信息。Using Index：用了覆盖索引。Using temporary：用了临时表或MyISAM存储引擎。Using filesort：用了文件排序，文件排序尽量避免而用索引或主键的本身的顺序。

type具体含义

是访问类型。表示当前表查询的访问类型是什么。依次有：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL （一般来说，至少保证range级别，要求达到ref级别）

system：一张表里只有一条数据
const：使用主键查询
eq_ref：使用索引查询，覆盖索引
ref：不唯一索引。in、between
range：key范围查找
index：全索引表扫描。没有where条件，扫描全表的索引。
ALL：全表扫描

索引使用原则

选择合适的索引算法：
B-Tree适合范围和左前缀模糊查询
hash索引适合等值、非等值查询
大字段适合全文索引
不冗余原则：索引冗余降低insert、update的执行效率
不在text、blog等大字段上加索引
不在常修改的字段加索引
在查询较多的字段加索引
不要使用函数
字符转化造成索引失效
小心使用in、or
尽量使用覆盖索引：避免回表
or即使有一个索引可用，也可能因为范围优化器估算范围较大放弃使用索引
建议使用复合索引
复合索引顺序：区分度越大的字段索引顺序越靠前
like的最左前缀原则
索引最大选择性原则：尽量选择区分度大的字段作为索引
索引列非空属性最佳
索引建立在固定长度字段
索引查询使用数据类型优化
char因是固定长度比varchar索引查询的效率高
不同数据类型索引效率对比
enum > char > varchar
水平分区：单表数量控制在1000w以内
垂直分区：可将一个非固定长度静态表划分为一个固定长度静态表和一个非固定长度静态表

limit优化

使用limit防止全表扫描，非常有意义：不论是select还是delete语句都有助于避免全扫提高效率
尤其在含有索引，但优化器判断因范围较大而全表扫描时，如果有limit子句则mysql一定会使用索引查询而非全表扫描
limit适用于小范围顺序查询，即offset较小比较好。如果需要大范围定位建议使用where子句

查询效率优化

union all 效率高于union：union all不需要排序，当数据条数较多时越明显，因为可能发生外排序
order by 子句尽量缩小排序范围：最大程度避免外排序
order by 适当提高排序缓存大小：sort_buffer_size，默认32KB
加大可打开表数量提高并发，table_open_cache
MyISAM打开索引缓存大小：key_buffer_size
排序较多时：选择客户端自排序，或适当增大innodb缓冲区以提供相对充足内存空间排序
尽量减小子查询：子查询是mysql查询的杀手
避免多级连表：对于分表分库也非常不利，mycat对join支持也较弱
分区：将数据分区到不同磁盘减少磁盘随机读取频率，增大磁盘读取效率
提高预读灵敏度：更早开启预读增大查询效率
视图减少冷数据查询
开启自适应哈希索引：并适当增加哈希槽减少碰撞带来的性能消耗
增大读取并发数：
增大innodb缓冲区大小：innodb_log_buffer_size
复杂查询可以引入ElasticSearch搜索引擎查询
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，性能开销大

写入效率

事务的写入最重要的配置是binlog的写入和redo日志的写入，默认均配置为1，是最安全的但也是性能最低的
mysql复制架构下，不同的复制模式写入效率不同。异步复制最高，同步复制、半同步复制较低

批量写入

大量时，可以分批次写入
加入limit子句，防止全扫

删除效率

自适应哈希索引：对于innodb引擎提升性能
缓冲区大小：缓存索引
查询缓存：对MyISAM引擎提升查找效率

join、left join选择

mysql针对不同的join使用不同的内圈循环方式。
join选择数量更小的内循环表作为内循环，left join则是选择表数量更多的表。

profile

explain主要是对索引分析，profile是对explain的补充，用来分析sql性能消耗的分布情况，对sql进行更细致的分析，找出sql耗时主要消耗在哪个部分，确认sql的性能瓶颈。

何时使用：explain不能提供更多的信息时，或者具体对某一个查询语句的执行情况进行具体分析时。
可以看到哪些信息：查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 耗时，也可以查看memory,swaps,context switches,source等信息

基本使用：

select @@profiling //查看是否开启profile
set profiling=1   //开启profile
show profiles   //所有查询SQL及耗时
show profile for query ${query_id}  //具体查询的详细时间耗费
show profile block io,cpu for query ${query_id}  //查看CPU等