梁越
Home Archives Friends
Home Archives Friends

mysql优化大全

数据库
有 0 人看过

面试常问

如何定位慢语句

如果业务中出现查询需要很长时间才返回的情况，可以使用慢查询日志进行慢语句查询

MySQL 的慢查询日志记录的内容是：在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值0）的语句。

默认情况下，慢查询日志中不会记录管理语句，如果需要记录的请做如下设置，设置log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。默认情况下，也不会记录查询时间不超过 long_query_time 但是不使用索引的语句，可通过配置log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中（即使查询时间没超过 long_query_time 配置的值）。

一般使用慢查询的步骤如下：

  1. 开启慢查询日志
mysql> set global slow_query_log = on;  
Query OK, 0 rows affected (0.00 sec)
  1. 设置时间限制
mysql> set global long_query_time = 1;  
Query OK, 0 rows affected (0.00 sec)
  1. 查看慢日志路径
mysql> show global variables like "datadir";
  1. 查看日志文件名
mysql> show global variables like "slow_query_log_file";
  1. 查看慢语句
SELECT * FROM mysql.slow_log

或者打开日志文件查看

有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行

善用explain

explian可以分析某个语句的状态，使用很简单，直接在查询语句前加上explain就可以，例如

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

列名 描述
id 在一个大的查询语句中，每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际使用的索引
key_len 实际使用的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

一般如果建立了索引，我们会看type字段，这个字段有如下：

  • system：一般整个数据库只有一条记录，那么就是system，能精确的查找

  • const：当我们使用id或者唯一二级索引进行查找的时候，对单表的访问就是const

  • eq_ref：执行连接查询时，如果被驱动表通过id或者唯一二级索引进行查找的就是eq_ref。说到被驱动表，当连接查询没有where条件时，左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反，内连接查询时，哪张表的数据较少，哪张表就是驱动表
    当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表

  • ref：通过普通的二级索引进行查找就是ref

  • fulltext：全文索引

  • ref_or_null：通过普通的二级索引进行查找，并且该索引列的值可以是NULL时

  • index_merge：在一些索引合并的场景下

  • unique_subquery：类似于eq_ref，优化器对IN查询优化为EXISTS子查询时，并且转换后通过id或者唯一二级索引进行查找

  • index_subquery：类似于unique_subquery，只不过在子查询使用的普通索引

  • range：通过索引获取扫描区间的记录

  • index：使用覆盖索引的时候

  • ALL：全表扫描

以上介绍的顺序是由快到慢的

如何更好的建立和使用索引

  1. 只为用于搜索、排序和分组的列创建索引
  2. 索引列中重复的值不能太多
  3. 索引的类型尽量小，能用INT就不要BIGINT，能INT就不要VCHAR
  4. 字符太长的时候只为前缀建立索引
  5. 尽量使用覆盖索引
  6. 多个索引间注意不要出现有包含关系的索引，避免冗余

查询语句如何优化

  1. 尽量避免在 where 子句中对字段进行 null 值判断

    应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

  2. 尽量避免在 where 子句中使用 or 来连接条件

    尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

  3. in 和 not in 要慎用

    尽量使用between，否则导致全表扫描

  4. 如果在 where 子句中使用参数，也会导致全表扫描

    如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

  5. 尽量避免在where子句中对字段进行函数操作

    应尽量避免在where子句中进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

  6. exists 代替 in

    使用in，优化器会优化为exists

  7. 尽量不要修改聚簇索引的列，会导致整个表改动，例如主键。

  8. 尽量使用int代替字符串

分表分库

有时候查询慢不一定是写的有问题，随着业务拓展，数据越来越多，查询效率也会随之降低，这时候可能就需要进行分表分库操作来缓解

何时分表

当查询慢的主要原因是数据量太大时，一般采用分表

如何分表

  1. 水平分表

    单表的数据量太大。按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。这种情况是不建议使用的，因为数据量是逐渐增加的，当数据量增加到一定的程度还需要再进行切分。比较麻烦。

  2. 垂直分表

    表中的字段较多，一般将不常用的、 数据较大、长度较长的拆分到“扩展表“。一般情况加表的字段可能有几百列，此时是按照字段进行数竖直切。注意垂直分是列多的情况。

何时分库

如果单库达到了性能瓶颈，同时并发量还挺高的时候，考虑采用分库的方法

如何分库

  1. 水平分库

    水平分库理论上切分起来是比较麻烦的，它是指将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

  2. 垂直分库

    一个数据库的表太多。此时就会按照一定业务逻辑进行垂直切，比如用户相关的表放在一个数据库里，订单相关的表放在一个数据库里。注意此时不同的数据库应该存放在不同的服务器上，此时磁盘空间、内存、TPS等等都会得到解决。

缓存和数据库

优化的地方都离不开局部性原理，对于经常访问的热点数据，可以采用缓存的方式存储，先访问缓存，如果缓存没有命中，再到数据库里查找

对于使用缓存和数据库保证一致性的方法在另一篇文章有详细介绍

https://mp.weixin.qq.com/s/4W7vmICGx6a_WX701zxgPQ

分布式数据库

上面说到的分库也算是分布式下的一种情形，不过每个节点保存的不同数据，用于分担单机压力。这里说的主要是主从结构的分布式，为了实现高可用和缓解高并发的情况

数据库主从结构可以是一对多，一对一，多对一

主从复制原理和过程

MySQL 主从复制涉及到三个线程：

一个在主节点的线程：log_dump_thread

从库会生成两个线程：一个 I/O 线程，一个 SQL 线程

主库会生成一个 log dump 线程,用来给从库 I/O 线程传 Binlog 数据。

从库的 I/O 线程会去请求主库的 Binlog，并将得到的 Binlog 写到本地的 relay log (中继日志)文件中。

SQL 线程,会读取 relay log 文件中的日志，并解析成 SQL 语句逐一执行。

事务的提交

分布式事务一般有两段式提交和三段式提交，这两种提交方式可以参考这里

对于两段式提交的过程如下：

  1. Master执行提交语句时，事务被发送到slave，slave开始准备事务的提交。

  2. 每个slave都要准备事务，然后向master发送OK(或ABORT)消息，表明事务已经准备好（或者无法准备该事务）。

  3. Master等待所有Slave发送OK或ABORT消息，如果Master收到所有 Slave的OK消息，它就会向所有Slave发送提交消息，告诉Slave提交该事务；如果Master收到来自任何一个Slave的ABORT消息，它就向所有 Slave发送ABORT消息，告诉Slave去中止事务。

  4. 每个Slave等待来自Master的OK或ABORT消息。 如果Slave收到提交请求，它们就会提交事务，并向Master发送事务已提交 的确认；如果Slave收到取消请求,它们就会撤销所有改变并释放所占有的资源，从而中止事务，然后向Masterv送事务已中止的确认。

  5. 当Master收到来自所有Slave的确认后，就会报告该事务被提交（或中止），然后继续进行下一个事务处理。