学习总结与分享-MySQL的优化(完结篇)
在日常代码开发中,我们总是要写各种各样的sql语句,一段程序或者说一次客户端与服务端的交互,最能影响执行速度的就是网络的传输和底层数据的存取等。。那么底层数据的存取如果是使用的是磁盘存储的关系型数据库,那么就是依靠sql语句了,所以,一个或者一组sql的执行效率,直接的影响到了程序的执行速度和用户体验,所以我们在编程时,要学会sql的优化,最大化的优化sql的执行时间,这样不仅可以增加执行速度,还可以减少锁阻塞和死锁的情况。
架构层面:
MySQL的架构体系:
连接层:客户端(Client Connection)、系统管理(Services)、连接池管理(Connection pool)。
解析层:SQL api(SQL interface),缓存(Caches)、解析(Parser)、优化(Optimizer)。
数据层:存储引擎(Engines)、数据文件(File System)、日志文件(Log )
SQL执行的五个阶段:
阶段一 :客户端/服务端通信;
阶段二 :查询缓存;
阶段三 :查询优化;
阶段四 :查询执行引擎;
阶段五 :返回客户端结果集;
MySQL的存储引擎:
1、CSV:主要用户导入导出数据,文件格式.csv,因为可随意通过修改文件从而修改数据库数据,所以安全性很低。
2、Archive :压缩数据格式的数据引擎,数据格式.arz, 支持持insert,select 不支持事务,用于数据备份。
3、Memory|heap:数据存储在内存中,大小限制只有16M,主要用于临时表,如果数据大于16M则使用Misam作为临时表。
4、Myisam:Mysql 5.5以前的默认存储引擎,不支持事务,锁为表级锁,非聚簇索引。
5、INNODB:Mysql默认存储引擎,支持事务,行级锁,聚集索引,支持外键。
INNODB 和MISAM的区别:
1、索引机制不同
innodb 聚集索引, MISAM 非聚集索引
2、锁的机制不同
MISAM只支持表锁,INNODB支持表锁、行锁、间隙锁
3、事务机制不同
MISAM不支持事务,INNODB支持事务
什么是聚集索引:
索引的顺序和物理存放顺序一致,只要索引的顺序是相邻的,那么数据在磁盘的位置也一定是相邻的。
INNODB的聚集索引:
INNODB把索引和数据保存在一起,索引即数据,数据即索引。
INNODB普通索引是如何查找数据的:
普通索引存的是聚集索引关键字的值,然后通过此值来查询聚集索引来找到对应的数据。
通过上面架构层面的了解,我们可以知道,MySQL在执行SQL之前实际上是为我们做了一些优化的,比如:
优化SQL找到最优的执行计划
1、等价变化原则:b>a and a=5 改成 b>5 and a=5;
2、min、max、count函数优化:基于索引,Min 索引左边,max索引右边的节点,不需要一个个去比较。额外保留一个count 记录数,Myisam 引擎的count(*)
3、in优化:把 or 改成In(二分法优化)
4、子查询有优化
5、覆盖索引扫描
6、提前终止查询:Select * from user_info where id=-1;
但是我们如果要尽可能的提高效率,就要在写sql的时候就避免让MySQL来帮我们优化sql语句,尽量写高效的sql。
根据InnoDB的聚集索引和非聚集索引的查询方式,我们在写sql的时候,即使是使用索引也要优先使用主键的字段来查询,避免二次查询。
SQL语句的优化:
join关键字的算法原理:
1、Simple Nested-Loop Join(简单的嵌套循环连接):
简单来说嵌套循环连接算法就是一个双层for 循环 ,通过循环外层表的行数据,逐个与内层表的所有行数据进行比较来获取结果;
2、Index Nested-Loop Join(索引嵌套循环连接):
Index Nested-Loop Join其优化的思路 主要是为了减少内层表数据的匹配次数;
简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较, 这样极大的减少了对内层表的匹配次数;
从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度,极大的提升了 join的性能;
3、Block Nested-Loop Join(缓存块嵌套循环连接):
Block Nested-Loop Join 其优化思路是减少外层表的循环次数;
Block Nested-Loop Join 通过一次性缓存多条数据,把参与查询的列缓存到join buffer 里,然后拿join buffer里的数据批量与内层表的数据进行匹配,从而减少了外层循环的次数;
当我们不使用Index Nested-Loop Join的时候,默认使用的是Block Nested-Loop Join。
根据上面的算法,无疑是第二种使用了索引的算法更为快速,但是如果没有用到索引,必须要使用第三种缓存的形式的话,也要有意识的有几种优化的思路,比如:
1、永远用小结果集驱动大结果集 (其本质就是减少外层循环的数据数量)
2、为匹配的条件增加索引 (减少内层表的循环次数)
3、增大join buffer size的大小 (一次缓存的数据越多,那么外层表循环的次数就越少)
4、减少不必要的字段查询 (字段越少,join buffer 所缓存的数据就越多,外层表的循环次数就越少)
表结构与sql的写法优化:
经过了深入的了解了MySQL的各种底层原理,事务,锁机制等。也渐渐可以理解了一些sql优化的规则与理念,在以后日常创建表的时候要遵从大概几点:
1.选择合适的字段类型
2.精确字段长度
3.适当利用冗余字段(减少连表查询、减少计算);
4.把字段定义为NOT NULL并且提供默认值
5.不要存储大文件或者大照片
6.不要使用外键
7.在合适的字段上建索引(离散性不高、查询频率不高、更新频率高的字段不适合建索引);
8.联合索引区分度高的字段放在前面(最左匹配原则)
9.单表索引不能过多(在5个以内)
由于我们在查询的时候大部分的sql查询,命中索引的sql大概率是要比全表扫描的sql执行起来要快的。所以我们要清楚的知道如何来命中索引。
命中索引的规则:
1、索引最左匹配原则
2、隐式转换无法使用索引
3、like ‘%张三’ 无法使用到索引
4、负向查询无法使用到索引(待考察)
5、or 无法使用到索引
6、WHERE条件 为函数或表达式无法使用索引
至于如何查看sql执行的效率,我们可以用执行计划来查看,在sql前加上EXPLAIN关键字即可。如:Explain select * from user_info;
查询计划的字段:
select_type: 查询分类,用于区分普通查询(SIMPLE)、联合查询,子查询。
table: 查询所作用的表;
type : 查询表所用的方式 :
possoble_key: 查询过程中可能用到的索引。
key: 实际使用的索引;
key_len: 索引的长度;
ref: 关联的ID 等信息;
row: 扫描数据行数;
filtered: 返回数据所占扫描数据的比率;
Extra: 其他信息;
type=ALL:全表扫描。
type=index:索引全扫描,MySQL遍历整个索引来查询。
type=range:索引范围扫描,常见于<、<=、>、 >=、 between。
type=ref:使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录。
type=eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配。
type=const/system:单表中最多有一个匹配行,查询起来非常迅速,一般主键primary key或者唯一索引unique index进行的查询。
SQL的优化与规范:
1、只取需要的列,避免SELECT * (索引,网络传输,SQL解析,join buffer ,覆盖索引)
2、禁止使用属性隐式转换
3、禁止在WHERE条件的属性上使用函数或者表达式
4、避免使用or查询
5、联表查询用小结果集驱动大结果集
6、禁止负向查询(NOT、!=、<>、!<、!>、NOT IN、NOT LIKE),以及%开头的模糊查询
7、禁止使用enum类型
8、禁止使用外键
9、禁止使用存储过程、函数、触发器
10、字段必须加注释
11、必须要有主键自增长ID
12、字段必须定义为not null,有默认值
13、编码必须统一UTF-8
14、索引命名:非唯一索引idx_xx,唯一索引名uniq_xxx。
未完待续。。。(补充SQL优化实践结果)