Mysql-sql优化

一、分析sql

1.查看SQL执行频率

分析当前数据库是查询频繁的，还是写入频繁的，一般都是查比较多咯。

MySQL 客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。

show [session|global] status可以根据需要加上参数session或者global来显示对应级别的统计结果。

如果不写，默认使用参数是session。

• session：当前连接的统计结果

• global：自数据库上次启动至今的统计结果

查询结果中的Com_xxx 表示每个xxx语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

• Com_

  这些参数对于所有存储引擎的表操作都会进行累计。

• Innodb_

  这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

示例:

-- 7个占位符
show status like 'Com_______';

-- 查询innodb相关的
show GLOBAL status like 'Innodb_rows_%';

2.定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

• 慢查询日志

  通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

• show processlist

  慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候，慢查询日志也许并不能方便的定位问题。

  可以使用show processlist命令查看当前MySQL正在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

• id列

  用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

• user列

  显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

• host列

  显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

• db列

  显示这个进程目前连接的是哪个数据库

• command列

  显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

• time列

  显示这个状态持续的时间，单位是秒

• state列

  显示使用当前连接的sql语句的状态，很重要的列。

  state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

• info列

  显示这个sql语句，是判断问题语句的一个重要依据

3.explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

explain select * from tb_item where id = 1;

explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为 system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

3.1 准备工作

新建一个t_user、t_role、user_role表。

-- t_user
CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- t_role
CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- user_role
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- t_user
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');

-- t_role
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');

--  user_role
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

t_user表存储用户信息。

t_role表存储角色信息。

关系表user_role

嗯，比较常见的场景。一个用户可能有多个角色，一个角色也可能对应着多个用户。

3.2 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序，id 情况有三种 。

• id 相同：表示加载表的顺序是从上到下

• id 不同：id值越大，优先级越高，越先被执行。

• id 有相同，也有不同，同时存在。

  id相同的可以认为是一组，从上往下顺序执行。

  在所有的组中，id的值越大，优先级越高，越先执行。

3.2.1 id相同

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;

3.2.2 id不同

explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'stu1'));

-- 可以看到，上面的语句里最先被执行的是t_user的这个子查询
select id from t_user where username = 'stu1'

-- 然后走到了这个查询
select role_id from user_role where user_id =

-- 最后才在t_role得出了查询结果

3.2.3 id既有相同也有不同

3.3 explain 之 select_type

表示 SELECT 的类型

常见的取值，如下表所示，从上往下效率越来越低。

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

3.3.1 SIMPLE

单表操作，不包含子查询。

3.3.2 PRIMARY、SUBQUERY

复杂查询，可以看到，子查询的被标记为了SUBQUERY，子查询外面的查询被标记为PRIMARY。

explain SELECT * from t_user where id = (SELECT id from user_role where role_id = '9')

3.3.3 DERIVED

临时表

3.4 explain 之 table

展示这一行的数据是关于哪一张表的

3.5 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，我们的业务系统中一般不会出现
const	表示通过索引一次就找到了，而且只返回了一条记录。 通常是primary key 或者 unique 索引。因为只匹配一行数据，所以很快。 如将主键置于where中，MySQL 就能将该查询转换为一个常量。 const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较。
eq_ref	类似ref，区别在于使用的是唯一索引或者使用主键的关联查询，关联查询出的记录只有一条。 常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。 本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）。
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 类型只是遍历了索引树，通常比ALL 快，all 是遍历数据文件（index 与 all 的区别）。
all	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > range > index > ALL

-- ref到range之间可以再细化下
NULL > system > const > eq_ref > ref 
> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery 
> range > index > ALL

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

3.6 explain 之 key

possible_keys、key、key_len三列的信息如下。

key	含义
possible_keys	显示可能应用在这张表的索引， 一个或多个。
key	实际使用的索引， 如果为NULL， 则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

​

3.7 explain 之 rows

扫描行的数量。

3.8 explain 之 filtered

表示过滤条件对于结果集的预计过滤比例，以百分比形式表示。具体来说，它表示通过索引或其他条件过滤掉的行的比例。

• 如果 filtered 值为 100%

  表示过滤条件将返回结果集中的所有行，没有进行额外的过滤操作。

• 如果filtered 值小于 100%

  则表示过滤条件只过滤掉部分行

通常情况下，filtered 值越接近 100%，表示查询条件更加精确，可以更有效地筛选出所需的结果，从而提高查询性能。相反，如果 filtered 值较低，可能意味着查询条件不够准确，需要扫描更多的行来获取结果，可能会导致性能下降。

需要注意的是，filtered 列的值仅提供一个估计值，并不代表实际的过滤效果。实际的过滤效果还受到索引、统计信息、数据分布等因素的影响。因此，仅凭 filtered 值来判断查询性能并不全面，还需要综合考虑其他因素。

3.9 explain 之 extra

其他的额外的执行计划信息，在该列展示 。

需要优化是的前面两个，保持的是后面using index。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index	表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

4.show profile分析SQL

Mysql在5.0.37版本增加了show profile和show profiles的语句支持。

show profile可以帮助我们具体的了解sql花费的时间都在哪里。

4.1 @@have_profiling

查看是否支持profile

SELECT @@have_profiling

4.2 @@profiling

是否开启profiling

1开启，0关闭。

SELECT @@profiling

-- 开启profiling
set profiling=1; 

4.3 实践

随便执行条sql语句

-- 这个表是个测试用的表,里面有差不多300万数据.
SELECT count(*) from salaries;

SELECT * from salaries where id >= '2844100'

之后执行profiles查看

show profiles 

定位到刚才这个查询的id是176，查看详情。

-- 注意此时的profile后面没有带s了.
show profile for query 176

Sending data：

表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。

例如，选择查看CPU的耗费时间。

show profile cpu for query 42

5.trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

5.1 开关

打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

set optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

1.optimizer_trace

该变量用于启用或禁用优化器跟踪功能。通过将其设置为 "enabled=on"，可以开启优化器跟踪。

优化器跟踪可以记录查询优化器的详细执行过程，包括优化器如何选择执行计划和执行操作的顺序。这对于分析和调优查询性能非常有用。

2.end_markers_in_json=on

表示在优化器跟踪结果中使用 JSON 格式，并在结果中包含标记信息，以便更清晰地区分不同的阶段和操作。

3.optimizer_trace_max_mem_size

该变量用于限制优化器跟踪信息的内存使用。通过设置合适的值，可以控制优化器跟踪所占用的内存大小。在上述示例中，将其设置为 1000000，表示最大使用 1000000 字节的内存空间。

---

通过设置这些变量，您可以启用优化器跟踪，并控制跟踪信息的内存使用。

请注意，启用优化器跟踪可能会对系统性能产生一定影响，因此在生产环境中应谨慎使用，并及时关闭跟踪功能以避免额外的资源消耗。

5.2 实例

执行一个sql语句

select * from tb_item where id < 4;

检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的。

select * from information_schema.optimizer_trace;

-- 运行结果
QUERY: select * from tb_item where id < 4
TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS `num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS `status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS `createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where (`tb_item`.`id` < 4)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tb_item`.`id` < 4)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`tb_item`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`tb_item`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9816098,
                    "cost": 2.04e6
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "id < 4"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 1.6154,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 3,
                      "ranges": [
                        "id < 4"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.6154,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`tb_item`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 2.2154,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2154,
                "rows_for_plan": 3,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`tb_item`.`id` < 4)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tb_item`",
                  "attached": "(`tb_item`.`id` < 4)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`tb_item`",
                "access_type": "range"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

二、优化sql

1.索引的使用

2.sql优化实例