深入理解MySQL（7）：MySQL如何调优

MySQL

九、MySQL调优

影响MySQL的性能因素：

• 系统各种配置及规则数据
• 超大文本数据
• Schema设计对系统的性能影响
• 硬件环境对系统性能的影响

对于MySQL层优化一般遵从五个原则：

• 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
• 返回更少的数据：只返回需要的字段和数据分页处理 减少磁盘io及网络io
• 减少交互次数：批量DML操作，函数存储等减少数据连接次数
• 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
• 利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源

总结到SQL优化中，就三点:

• 最大化利用索引；
• 尽可能避免全表扫描；
• 减少无效数据的查询；

SQL慢原因分析:

• 查询语句写的不好
• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数等）

低效查询排查思路：

• 是否向数据库请求的不需要的数据，比如select *、没有使用limmt
• 是否扫描了额外的记录

9.1SQL优化步骤

9.1.1 通过 show status 命令了解 SQL 执行次数

比如show status like %Com_%, (show global status like “Com_select” 就是全局会话)
Com_xxx 表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数，即：

• Com_select：执行 select 操作的次数，一次查询会使结果 + 1；
• Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次；
• Com_update：执行 UPDATE 操作的次数；
• Com_delete：执行 DELETE 操作的次数。

以 Innodb_ 为开头的参数主要有：

• Innodb_rows_read：执行 select 查询返回的行数。
• Innodb_rows_inserted：执行 INSERT 操作插入的行数。
• Innodb_rows_updated：执行 UPDATE 操作更新的行数。
• Innodb_rows_deleted：执行 DELETE 操作删除的行数。

通过上面这些参数执行结果的统计，我们能够大致了解到当前数据库是以更新（包括插入、删除）为主，还是以查询为主。

9.1.2 定位执行效率较低的 SQL

可以通过慢查询日志来定位哪些执行效率较低的 SQL 语句。
用set global slow_query_log='ON'打开，也可以在配置文件中设置。用 set global long_query_time=n;用来设置阈值时间，执行超过设置值就会记录到日志中。查询慢日志是否开启 。

慢查询日志会在查询结束以后才记录，所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题
此时应该使用**show processlist**命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等，可以实时的查看SQL 执行情况。

• Id ：Id 就是一个标示，在我们使用 kill 命令杀死进程的时候很有用，比如 kill 进程号；
• User：显示当前的用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句；
• Host：显示 IP ，用于追踪问题；
• db：显示这个进程目前连接的是哪个数据库，为 null 是还没有 select 数据库；
• Command：显示当前连接锁执行的命令，一般有三种：查询 query，休眠 sleep，连接 connect；
• Time：这个状态持续的时间，单位是秒；
• State：显示当前 SQL 语句的状态，非常重要，下面会具体解释；
• Info：显示这个 SQL 语句。

**State **列非常重要，这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。
一些查询语句：

• 得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log

• 得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log

• 也可以和管道配合使用

mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log 

9.1.3EXPLAIN命令

执行explain select * from a;


其中type、key和extra三列是最重要的。这里多说几句关于Using index 和 Usingwhere的。

下图这个例子表里存在一个组合索引(name,age)，第一个sql因为条件name=‘3’符合最左匹配，where 条件作为index filter是在引擎层过滤的数据，Extra的值显示为Using index。

第2个sql，由于查询的结果都在组合索引（name,age）上，所以Extra里显示了Using index，但age并不符合最左匹配，因此，查询时遍历了整颗组合索引(name,age)树，可以看到type是index，数据是返回server层再根据where条件进行过滤的，Extra里会有Usingwhere。

Extra 出现using filesort不是说通过磁盘文件进行排序，只是说明进行了排序操作而已，是否有磁盘文件排序可以通过select * from information_schema.optimizer_trace查看number_of_tmp_files属性的值，大于0的话说明有文件排序。可参考：order by详解、using filesort


explain 后面还可以加上 format=json、extended等查看不同的信息。

9.1.4查看索引使用状况

在 MySQL 索引的使用过程中，有一个 Handler_read_key 值，这个值表示了某一行被索引值读的次数。Handler_read_key 的值比较低的话，则表明增加索引得到的性能改善不是很理想，可能索引使用的频率不高。查询语句show status like "Handler_read%"

还有一个值是 Handler_read_rnd_next，这个值高则意味着查询运行效率不高，应该建立索引来进行抢救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值比较高，就说明表索引不正确或写入的查询没有利用索引。

9.1.5 SHOW PROFILES

通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的具体执行情况，索引使用等，还可以结合Show Profiles命令查看执行状态。

• Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
• 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

9.1.6Trace

MySQL5.6新引入的一项跟踪功能： OPTIMIZER_TRACE ，可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等）并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 中，跟踪功能默认是关闭的，我们要用它的话，需要将其开启： set optimizer_trace='enabled=on'; 查看优化器优化步骤：select * from information_schema.OPTIMIZER_TRACE;。查询结果示例如下：

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t_store`.`store_id` AS `store_id`,`t_store`.`store_name` AS `store_name`,`t_store`.`brand_id` AS `brand_id`,`t_store`.`province` AS `province`,`t_store`.`city` AS `city`,`t_store`.`area` AS `area`,`t_store`.`address` AS `address`,`t_store`.`status` AS `status`,`t_store`.`contact` AS `contact`,`t_store`.`contact_phone` AS `contact_phone`,`t_store`.`store_manager_id` AS `store_manager_id`,`t_store`.`is_deleted` AS `is_deleted`,`t_store`.`shop_id` AS `shop_id`,`t_store`.`last_update_time` AS `last_update_time`,`t_store`.`clear_table` AS `clear_table` from `t_store` where (`t_store`.`address` like '%北京%') order by `t_store`.`last_update_time`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t_store`.`address` like '%北京%')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t_store`.`address` like '%北京%')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t_store`.`address` like '%北京%')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t_store`.`address` like '%北京%')"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t_store`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t_store`",
                "table_scan": {
                  "rows": 8,
                  "cost": 1
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_store`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 8,
                      "access_type": "scan",
                      "resulting_rows": 8,
                      "cost": 2.6,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 8,
                "cost_for_plan": 2.6,
                "sort_cost": 8,
                "new_cost_for_plan": 10.6,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`t_store`.`address` like '%北京%')",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`t_store`",
                  "attached": "(`t_store`.`address` like '%北京%')"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t_store`.`last_update_time`",
              "items": [
                {
                  "item": "`t_store`.`last_update_time`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t_store`.`last_update_time`"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`t_store`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t_store`",
                "field": "last_update_time"
              }
            ],
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 7,
              "examined_rows": 8,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 73360,
              //排序的方式 还有一种是packed_additional_fields，全字段的
              "sort_mode": "<sort_key, rowid>"
            }
          }
        ]
      }
    }
  ]
}

还有一个show warnings可以用来查看编译器对sql的优化结果，要注意的是这个两个都需要和相应的sql一起执行。

9.2SQL优化策略

整理自：SQL优化

9.2.1避免索引失效

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。数据量较大的情况，建议引用ElasticSearch、solr。
2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如果是连续数值，可以用between代替，如果是子查询，可以用exists代替。
3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描，可以用union代替or。数据量足够的情况下，or前后都能走索引的话（不是一个索引）会走索引，前后有一个不能走索引时，就会全表扫描。（不包括覆盖索引的情况）。
4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。可以给字段添加默认值0，对0值进行判断
5. 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。可以将表达式、函数操作移动到等号右侧。（这里不一定就不会使用索引，只不过不能使用索引的快速搜索功能，导致扫描全索引树，explain的时候可以看到key是某个索引，type是index）
6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。用 <where>标签。
7. 查询条件不能用 <> 或者 !=。
8. where条件仅包含复合索引非前置列，不符合最左匹配。
9. 隐式类型转换造成不使用索引，如列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。如select col1 from table where col_varchar=123;，会导致等式左边字符串转为数字再比较。

9.2.2select等其他优化

1. 避免出现select *。
2. 避免出现不确定结果的函数，针对主从复制这类场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、 current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
3. 多表关联查询时，小表在前，大表在后。
4. 使用表的别名。当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
5. 用where字句替换HAVING字句。避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。where和having的区别：where后面不能使用组函数。
6. 调整Where字句中的连接顺序。应将过滤数据多的条件往前放，最快速度缩小结果集。 （这是错的，可参考：Does the order of the WHERE clause make a difference?）
7. 分解关联查询，用多个单个查询。
8. 切分查询，比如删除十万行数据可以分解成10个删除一万行。
9. 使用变量获取刚更新数据，避免了再次访问数据表

9.2.3查询条件优化

1. 优化group by语句。默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2” 等同添加了 “ORDER BY col1，col2”。如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;group by 的字段如果可以添加索引，能避免排序的过程
2. 可优化子查询为join,具体的要看实际执行情况。
3. 优化union查询。MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
4. limit优化，使用延迟关联解决大偏移量下的分页。类似于select t.* from (select id from t where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t where a.id = t.id; 可参考亿级数据分页的优化。
5. 比如表中有性别这种选择性很低的列，在上面建了联合索引，查询时没有使用到这一列，为了符合最左匹配，可以添加条件 and sex ('m','f')。当然in中的列表不能太长。
6. order by 优化。尽量使用索引排序。索引列顺序与order by顺序一样，排序方向相同，不对order by 字段做运算，满足最左匹配，联表时order by字段全为第一个表列等场景才会使用索引。order by 中的字段在执行计划中利用了索引时，不用排序操作。当where条件使用组合索引最左列的常量，那么order by也能使用索引，比如where a=1 order by b,c，如果MySQL实在是担心排序内存太小，会影响排序效率，会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

十、分库分表

10.1分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

• 垂直拆分
  垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
• 水平拆分(数据分片)
  单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。
  水平分割的几种方法：
• 使用MD5哈希，做法是对UID进行md5加密，取前几位，然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
• 还可根据时间放入不同的表，比如：article_201601，article_201602。
• 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
• 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

10.2分库

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。
优点：

• 减少增量数据写入时的锁对查询的影响
• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

• 分布式事务的问题，数据的完整性和一致性问题。
• 数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

十一、主从复制

复制的基本原理

• slave 会从 master 读取 binlog 来进行数据同步
• 三个步骤

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

复制的基本原则：

• 每个 slave只有一个 master
• 每个 salve只能有一个唯一的服务器 ID
• 每个master可以有多个salve

复制的最大问题

• 延时

几个简单问题

1.count(*) 和 count(1)和count(列名)区别？

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL的数据，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL的数据。
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示NULL）的计数，即某个字段值为NULL时，不统计。

参考阿里的手册

count(* )是 SQL92 定义的标准统计行数的语法，所以 MySQL 对他进行了很多优化，MyISAM 中会直接把表的总行数单独记录下来供 count(* ) 查询，而 InnoDB 则会在扫表的时候选择最小的索引来降低成本。当然，这些优化的前提都是没有进行 where 和 group 的条件查询。

在 InnoDB 中 count(* )和 count(1)实现上没有区别，而且效率一样，但是count(字段)需要进行字段的非 NULL 判断，所以效率会低一些。因为 count(* )是 SQL92 定义的标准统计行数的语法，并且效率高，所以请直接使用 count(* ) 查询表的行数！

再补充点《高性能MySQL》3.0版本中关于count()的一些说法：

• count()函数有两个作用：1.统计某个列值的数量（非空）；2.统计行数。
• count(*)是忽略所有列而直接统计行数。
• MyISAM的count()函数非常快仅限于没有where条件的count(*)，因为该引擎内存储了表的总行数，但如果有where条件，那么它和其他引擎并没有什么区别。
• 当MySQL知道某列值不可能为NULL时，count(列名)会被优化为count(*)

MySQL实战45讲答案：
对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。
对于count(字段)来说：

1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。但是count( * )是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count( * )肯定不是null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照count( * )来处理，多么简单的优化啊。

当然，MySQL专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且MySQL已经优化过count(* )了，你直接使用这种用法就可以了。
所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count( * )，所以我建议你，尽量使用count(*)

2.MySQL中 in和 exists 的区别？

exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false

in：在很多数据库中in查询相当于多个or条件的叠加，但在MySQL中并不成立。MySQL先将in列表中的数据进行排序，再利用二分法判断列表中的值是否符合条件，复杂度是O(log n)，而等效为or查询时复杂度是O(n)，对于列表中有大量取值时，in的速度要比or快一点。关于in和or的效率问题可参考in/or的比较

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如果查询的两个表大小相当，那么用in和exists差别不大。但in会产生中间表，速度稍慢一些

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

此外，NOT IN 和 NOT EXISTS不是等价的，当NOT IN()中的查询结果有NULL的话，它的查询结果是空的，此时要用NOT EXISTS，可参考 三值逻辑与NULL

注：内容是从语雀上的学习笔记迁移过来的，有些参考来源已经无法追溯，侵权私删。