MySQL日志、范式、SQL语句优化、JOIN优化、EXPLAIN
MySQL是单进程多线程的
MySQL 采用插件式的存储引擎。存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过 API 与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
my.ini文件为数据库配置文件
MySQL体系结构
网络连接层
查看连接状态：
show processlist;服务层
连接池、管理服务和工具组件、SQL接口、查询解析器、查询优化器、缓存
SQL语句先查询缓存（一个查找表），没有的话就经过查询解析器、查询优化器
查看缓存情况：
show status like 'Qcache%'储存引擎层
插件式储存引擎，默认为InnoDB
调用储存引擎的原子API执行SQL语句，然后返回结果（如需缓存就缓存）
系统文件层
文件系统，数据，日志（redo、undo），索引，错误日志，查询记录，慢查询等
MySQL日志文件
Error log（错误日志）：err结尾文件
show variables like '%log_err%';Slow query log（慢查询日志）：可以设置参数来开启慢查询日志以及慢查询阈值等。默认不开启。主机名-slow.log文件。最好使用mysqldumpslow 来查看
show variables like '%query%';General query log（通用查询日志）：记录mysql所有操作日志。主机名.log
SHOW VARIABLES LIKE '%general%';binary log（二进制日志）：即bin log，用于数据恢复和主从复制。记录修改数据的语句。主机名-bin.编号
show variables like '%log_bin%';在数据目录下，一个database就是一个文件夹，每张表对应一组文件，IBD 文件（独享表，每个表放在一个独自的文件）和 IBDATA 文件（共享表，所有表放在一个文件）存放 InnoDB 的数据文件（包括索引）
（共享表碎片化验证，独享表可能占用空间大）
ibdata1文件:undo段
pid文件：存放进程id（unix/linux有）
socket文件：（unix/linux有）
数据库三范式
1NF：字段不可再分（每一列只有单一值，不可再分）
2NF：只有对主键的完全依赖，每一行被主键唯一标识（靠主键能区分出每一行）
3NF：不存在传递依赖（非主键字段只依赖于主键）
三范式只是一个指导，完全按照这样做也会带来问题，比如太多表，以及表关联，外键，带来性能损耗，很多额外开销。所以我们应该平衡范式和冗余
可以通过3范式，1->2->3来进行划分
MySQL优化
可以从几个角度进行
- 索引（见MySQL索引）
- 缓存（见MySQL并发相关原理）
- SQL语句（见下）
- 分表（水平、垂直）
- 范式（属于建库的时候）
SQL语句优化
禁用储存过程（将它分离到业务层）、储存文件不要太大、平衡范式和冗余、禁止使用外键、建议分离冷热数据
尽量定义为not null并提供默认值、禁用TEXT、根据业务选择char/varcahr
索引不宜过多、禁止在更新十分频繁的属性建立索引、禁止在区分度不高的属性上建立索引、建立联合索引遵循最左原则
禁止以下语句，否则会导致遍历全表：select *、属性隐式转换、where条件属性上使用函数表达式、负向查询、模糊查询、null判断
禁止大表用join（会产生临时表）、尽量用union all代替union（后者有去重功能）、使用limit、尽量使用inner join（默认join，因为inner join会选择较小的表作为驱动表，大表作为被驱动表）、尽量用小表去驱动大表（减少连接次数）
当mysql优化器选错索引的时候，我们可以用FORCE INDEX来强制使用某索引（SQL索引-慢查询-3）
应用程序需要捕获SQL异常并处理
P.S. null还有一些坑：负向查询无法获取null值行，通配符不能匹配到null，group by时null是一个单独分组，count函数会忽略null行，最大的坑还是where x is null会引发全表扫描
（整理自：https://www.yuque.com/yinjianwei/vyrvkf/mpu8gk）
JOIN优化
select * from user tb1 left join level tb2 on tb1.id=tb2.user_id【JOIN本质就是在两个list中，找出一样的行】
首先，禁止大表用join（会产生临时表占很大空间）、尽量使用inner join（默认join，因为inner join会选择较小的表作为驱动表，大表作为被驱动表）、尽量用小表去驱动大表（减少连接次数）
①暴力比较方案（Simple Nested-Loop Join）：通过两层循环一一比较驱动表和非驱动表显然开销非常大，要扫描MxN（两表行数乘积次），于是MySQL在此之上进行了优化
②基于索引优化（Index Nested-Loop）：当被驱动表上有索引时，可以使用索引来减少比较次数：外表（被驱动表）中的每条记录通过内表（驱动表）的索引进行查找访问，由于索引查找开销小，所以速度快。索引如果采用的是主键索引，那么就很快，但如果索引是辅助索引，那就要回表查询（两次IO），那这样就又拖累了join速度
③基于缓冲优化（Block Nested-Loop）：在暴力比较上进行修改，一次性缓存多条外表数据，把参与查询的列缓存到join buffer 里，,然后拿join buffer里的数据【批量与内层表的数据进行匹配】，这样一个buffer只需要扫内表一次，从而减少了外层循环的次数
所谓批量匹配就是buffer在内表的循环上，每往下一栋一个是一次循环，每次循环进行了连续多次比较，即buffer行和内表对位行的比较，由于每个循环子问题都是拿buffer_size条外表和内表数据进行比较，大大减少了磁盘IO。所以外层循环次数大大减小了，比较次数虽不变但顺序比较变快了
假设外表有1000行，buffer大小为10，那么外循环就只有10轮
所以buffer当然越大越好
MySQL查询优化器算法的选择：如果外表有索引，那就用Index Nested-Loop，否则用Block Nested-Loop（需要开启优化器配置管理的optimizer_switch的设置block_nested_loop为on默认为开启，否则使用暴力，设置join_buffer_size指定buffer大小）
参考：学习Mysql的join算法：Index Nested-Loop Join和Block Nested-Loop Join
未使用的hash方法（也很有效）：Hash join散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。
MySQL查询优化器
自动进行几种优化方式，比如
①语句等价变换，将两个语句合成一个
②覆盖索引
③将多级子查询优化成一级查询
④limit提前终止查询
⑤对in查询进行排序，然后二分查找。比如in(2,1,3)优化成(1,2,3)，然后第一找了1，发现第二次2>1，那就只找1右边，这样找
等等。
查询优化器基于数据抽样进行试验，成本最低原则，找到最优执行计划
EXPLAIN语句
通过EXPLAIN语句可以查看SQL语句的执行计划，并未实际执行
（explain参数见以前的文章）
常关注的有使用了什么索引，扫描了多少行，是否使用索引优化查询算法，是否全表扫描，是否覆盖索引，过滤百分比，可能用到的索引等
数据库设计常用结构
水平分片+垂直分组（读写分离）
保证读库的高可用：主从备份，但需要考虑主从一致性问题（同步延时）
保证写库的高可用：双主同步（两个互相同步），但要考虑双主的键冲突问题
然后就一堆分布式问题了。
其它
select * from table_name limit 1;从表中随机取出一条数据VARVHAR最大长度是16384，储存app meta够用。CHAR的长度是255。
全双工：双发可以同时发送和接收，半双工：一方发送的时候另一方只能接收，单工：单向通信
- InnoDB的优点：支持热备份、ACID事务、支持行级锁、聚类索引方式储存、MVCC非阻塞解决脏读、不可重复读
