mysql索引原理、主从延迟问题及如何避免
本文讲一下mysql的整体查询过程
基本的框架
客户端 - > 连接器 - > 分析器 -> 优化器 - >执行器 - > 存储引擎
- > 查询缓存 -
这里还有一个缓存的位置，是在连接器处，如果缓存中存在要查询的结果则直接走缓存返回
但在现实中开启缓存的几率比较低
原因：对于一个表的更新操作，这个表上的所有查询缓存都会被清空
因此除了很少更新的配置表外可以使用查询缓存来提高查询速度，一般不建议开启查询缓存
分析器：分析语法及词法，保证sql的正确性
优化器：一条sql可以通过不同的方式获取数据，优化器需要找到最优的查询方式
查找的依赖：统计信息和代码模型
例： select * from A where a = 3 and b = 4 ;
如果表中a都是3， b 只有一条为4， 优化器会选择b的索引进行查询，因为a的区分度不高，且还需要
进行回表操作，导致代价更高
例： select * from S where ( a between 1 and 1000) and (b between 5000 and 10000) order by b limit 1 ;
mysql 会选择哪个索引？
使用a索引需要最多扫描1000行数据，然后在进行排序
使用b索引需要最多扫描50000行数据，不需要进行排序
mysql5.7之前优化器最终会选择b索引,因为受order by的影响
在5.7之后会选择a索引
优化器确实会存在一些bug，导致选择的最终索引错误，这些内容需要进行具体sql具体分析
原则：尽量使用索引的排序，因为非索引的排序都属于filesort， 一提到文件排序其实就会比较耗时
执行器：
拿到优化器的信息，去调用搜索引擎的api接口，先取b=4的数据，判断a是否=3,如果不等于跳过，否则放入结果集
调用接口再取b=4的下一条数据并返回执行器，重复，直到循环遍历结束
执行器讲结果集返回给客户端
注： mysql将结果返回客户端是一个增量、逐步返回的过程，不一定等所有结果查到才返回
好处：
1、服务器无需查询太多的结果，也不会因为返回太多的结果而消耗太多的内存
2、客户端也可以第一时间返回结果
慢sql的具体原因
磁盘io : 磁盘的访问成本大概是内存的十万倍左右，为了降低磁盘io
在每次io时，不光把磁盘地址的数据，而且把周边的数据也读到内存缓存区
每一次读取就是一page， 具体大小在8k或者16k ，所以在读取一页数据的时候才会发生io
在查找数据的时候，B+树每一层进行一次IO，所以B+树的高度决定了io的次数
索引有最左匹配的特性
-
哪些情况要建索引
1、主键自动建主键索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、在高并发下倾向建立组合索引
5、查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
6、查询中统计或者分组的数据
-
哪些情况不适合建索引
1、频繁更新的字段
2、where条件用不到的字段不创建索引
3、表记录太少
4、经常增删改的表
5、数据重复太多的字段，为它建索引意义不大
进行explain ，速度的排序
type: system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
- 索引失效_复合索引（避免）
1、应该尽量全值匹配
2、复合最佳左前缀法则（第一个索引不能掉，中间不能断开）
3、不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描
4、储存引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
6、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7、is null，is not null也会无法使用索引
8、like以统配符开头
9、字符串不加单引号
10、少用or
有些sql 中会包含force index的写法，强制去走某个索引，但条件中缺不存在这个字段，会导致全表扫描
SELECT * FROM `coupon` FORCE INDEX (`orderid`) WHERE `userid` = 1 AND `status` IN (0,1) ORDER BY `id` ASC ;
这个sql使用了orderid的索引，但在查询条件中却缺少orderid的字段
数据库的主从同步
mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）
binlog dump线程：主库中有数据更新时，根据设置的binlog格式，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。当I/O线程请求日志内容时，将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。
I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。
SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步
puma , databus :
主从延迟问题
-
主库 A 执行完成一个事务，写入 binlog，该时刻记为T1.
-
传给从库B，从库接受完这个binlog的时刻记为T2.
-
从库B执行完这个事务，该时刻记为T3.
是T2-T1 吗？ 不是，如果网络不延迟，T2-T1 是一个很短的时间
是T3-T2吗？ 是的，主要是从库执行的情况（relaylog）
具体原因：
1、从库的机器性能比主库差
2、从库的压力大
3、大事务的执行，如果是大事务，主库必须等事务完成之后才写入binlog，数据传输人到从库，执行容易产生延迟
尽量避免一次性的delete大量数据，尽量批次处理
4、主库的ddl, alter, drop, repair, create
1、只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么只读节点上也会耗费10分钟。
2、只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。
5、锁冲突
如何避免主从延迟
-
降低多线程大事务并发的概率，优化业务逻辑
-
优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。
-
提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。
-
尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
-
实时性要求的业务读强制走主库，从库只做灾备，备份。
