当前位置:Gxlcms > 数据库问题 > mysql索引原理、主从延迟问题及如何避免

mysql索引原理、主从延迟问题及如何避免

时间:2021-07-01 10:21:17 帮助过:33人阅读

本文讲一下mysql的整体查询过程

1、基本的框架

客户端    - >  连接器  - > 分析器  -> 优化器  - >执行器  - > 存储引擎 

             -  > 查询缓存 - >

这里还有一个缓存的位置,是在连接器处,如果缓存中存在要查询的结果则直接走缓存返回

但在现实中开启缓存的几率比较低

原因1、对于一个表的更新操作,这个表上的所有查询缓存都会被清空

因此除了很少更新的配置表外可以使用查询缓存来提高查询速度,一般不建议开启查询缓存

一般也不建议开启

分析器:分析语法及词法,保证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

3.1 索引失效_复合索引(避免)

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 ;

 

数据库的主从同步

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、锁冲突

如何避免主从延迟

  1. 降低多线程大事务并发的概率,优化业务逻辑

  2. 优化SQL,避免慢SQL,减少批量操作,建议写脚本以update-sleep这样的形式完成。

  3. 提高从库机器的配置,减少主库写binlog和从库读binlog的效率差。

  4. 尽量采用短的链路,也就是主库和从库服务器的距离尽量要短,提升端口带宽,减少binlog传输的网络延时。

  5. 实时性要求的业务读强制走主库,从库只做灾备,备份。

 

mysql索引原理、主从延迟问题及如何避免

标签:框架   线程   tab   使用   位置   应该   事件   red   upd   

人气教程排行