学习总结与分享-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优化实践结果）