Day22 PythonWeb全栈课程课堂内容

1. Show Profile进行SQL分析

• 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量

• 默认情况下,参数处于关闭状态,并保持最近15次的运行结果。

1.1 Show Profile分析步骤

• 1.是否支持,看看当前MySQL版本是否支持

• 2.开启功能,默认是关闭,使用前需要开启

show variables like ‘profiling’;
查看profile是否开启。

set profiling=on;
打开profiling。

1.2 type

all         显示所有的开销信息
block io    显示块IO相关开销
cpu         显示CPU相关开销信息
ipc         显示发送和接收相关开销信息
memory      显示内存相关开销信息
page faults 显示页面错误相关开销信息

1.3 案例

1. 创建一个表emp;存储相关数据。

    select * from emp group by deptno limit 3;
    
    /*
    报错：
    ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY 
    clause and contains nonaggregated column 'day22.emp.id' which is not 
    functionally dependent on columns in GROUP BY clause; this is incompatible 
    with sql_mode=only_full_group_by
    */

    -- 需要指定字段。
    select deptno from emp group by deptno limit 3;

    show profiles;
    -- 可以看到 写过的sql语句。

	show profile cpu,block io for query 4;
	-- 查看第4条命令的耗时用在什么地方。
	-- 查看cpu和IO相关信息

---

可以使用profile查看当前的sql语句具体消耗的时间。

---

1.4 参数注意

converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬

Creating tmp table 创建临时表

Copying to tmp table on disk 把内存中临时表复制到磁盘,危险

locked

1.5 全局查询日志

开启命令

set global general_log = 1;

将SQL语句写到表中

set global log_output = 'TABLE';

你所编写的SQL语句,会记录到MySQL库里的genral_log表

select * from mysql.general_log;

在常规开发中使用general_log记录sql语句，方便优化。

---

2. trace分析SQL优化器

2.1建表

CREATE TABLE `test_trace` (             
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

2.2 trace分析SQL优化器介绍

• 从前面学到了 explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？

• 从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。

• 通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。

• 如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。

• 开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。

• 下面一起来看下 trace 的使用方法。使用讲解 explain 时创建的表test_trace做实验。

• 首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序)：

select * from test_trace where a >900 and b > 910 order  by a;

2.3 使用 trace 进行分析

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

• optimizer_trace=“enabled=on” 表示开启trace
• end_markers_in_json=on 表示 JSON 输出开启结束标记

2.4 查看trace分析结果

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
TRACE: {
  "steps": [
    {
      "join_preparation": {             --SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {            --SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {    --条件处理
              "condition": "WHERE",
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"      --等值传递转换
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */   --替换虚拟生成列
          },
          {
            "table_dependencies": [     --表依赖详情
              {
                "table": "`t1`",
                "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": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,       --扫描行数
                    "cost": 207.1       --成本
                  } /* table_scan */,
                  "potential_range_indexes": [    --分析可能使用的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,       --为false，说明主键索引不可用
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_a",      --可能使用索引idx_a
                      "usable": true,
                      "key_parts": [
                        "a",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_b",      --可能使用索引idx_b
                      "usable": true,
                      "key_parts": [
                        "b",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "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": "idx_a",   --使用索引idx_a的成本
                        "ranges": [
                          "900 < a"         --使用索引idx_a的范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,     --是否使用mrr
                        "index_only": false,    --是否使用覆盖索引
                        "rows": 100,            --使用该索引获取的记录数
                        "cost": 121.01,         --使用该索引的成本
                        "chosen": true          --可能选择该索引
                      },
                      {
                        "index": "idx_b",       --使用索引idx_b的成本
                        "ranges": [
                          "910 < b"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 90,
                        "cost": 109.01,
                        "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": "idx_b",
                      "rows": 90,
                      "ranges": [
                        "910 < b"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 90,
                    "cost_for_plan": 109.01,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  --考虑的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {          --最优的访问路径
                  "considered_access_paths": [ --决定的访问路径
                    {
                      "rows_to_scan": 90,      --扫描的行数
                      "access_type": "range",  --访问类型：为range
                      "range_details": {
                        "used_index": "idx_b"  --使用的索引为：idx_b
                      } /* range_details */,
                      "resulting_rows": 90,    --结果行数
                      "cost": 127.01,          --成本
                      "chosen": true,          --确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 90,
                "cost_for_plan": 127.01,
                "sort_cost": 90,
                "new_cost_for_plan": 217.01,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`a`",
              "items": [
                {
                  "item": "`t1`.`a`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`a`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`t1`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_b",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [          --改进的执行计划
              {
                "table": "`t1`",
                "pushed_index_condition": "(`t1`.`b` > 910)",
                "table_condition_attached": "(`t1`.`a` > 900)"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {             --SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t1`",
                "field": "a"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,             --未使用优先队列优化排序
              "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {           --排序详情
              "rows": 90,
              "examined_rows": 90,          --参与排序的行数
              "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
              "sort_buffer_size": 115056,
              "sort_mode": "<sort_key, additional_fields>"   --排序模式（详解请看下方知识扩展）
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0    --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
          INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)

关闭trace

set session optimizer_trace="enabled=off";

TRACE 字段中整个文本大致分为三个过程。

• 准备阶段：对应文本中的 join_preparation
• 优化阶段：对应文本中的 join_optimization
• 执行阶段：对应文本中的 join_execution

使用时，重点关注优化阶段和执行阶段。

在 trace 结果的 analyzing_range_alternatives 这一项可以看到：使用索引 idx_a 的成本为 121.01，使用索引 idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引；

2.5总结

• explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
• trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

---

3. 索引优化

3.1 索引的分类

分类角度	索引名称
数据结构	B+树，Hash索引，B-Tree等
存储层面	聚簇索引(InnoDB)，非聚簇索引(MyISAM)
逻辑层面	主键索引，普通索引，复合索引(MySQL 组合索引和联合索引和复合索引都是一个东西)，唯一索引，空间索引等

3.2 回表

假设我们执行一条查询语句:

select * from person where ID = 6

因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。

如果执行的sql语句是非主键索引:

select * from person where age = 18

上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。

**从普通索引查出主键索引，然后查询出数据的过程叫做回表。**由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

3.3 覆盖索引

我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做"覆盖索引"

create index idx_name_age on xxx(name, age);

select name,age from xxx where name='xxx';

select name,age from xxx where name='xxx';就是覆盖索引。

3.4 索引下推

select * from xxx where name=? and age=?;

• 没有索引下推： 现根据name从引擎中获取复合规则的数据，再放到server层对age进行过滤。

• 有索引下推：格局name,age两个条件来从引擎中获取对应的数据。

3.5 键表SQL

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default "",
    age int not null default 0,
    pos varchar(20) not null default "",
    add_time timestamp not null default CURRENT_TIMESTAMP 
)charset utf8;

create table user(
    id int not null auto_increment primary key,
    name varchar(20) default null,
    age int default null,
    email varchar(20) default null
) engine=innodb default charset=utf8;

插入数据

insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now());

insert into user(name,age,email) values('1aa1',21,'b@163.com');
insert into user(name,age,email) values('2aa2',22,'a@163.com');
insert into user(name,age,email) values('3aa3',23,'c@163.com');
insert into user(name,age,email) values('4aa4',25,'d@163.com');

建立复合索引

create index idx_staffs_nameAgePos on staffs(name,age,pos);

3.6 口诀

全值匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

like百分写最右,覆盖索引不写星

不等空值还有or,索引失效要少用

varchar引号不可丢,SQL高级也不难

3.7 口诀分析

• 全值匹配我最爱

从key_len中可以看出来，长度在不停的变化。当用到所有的索引时key_len的长度是最长的，即查询条件为where name='Sam' and age= 18 and pos='dev',此语句是不需要优化的。

• 最左前缀要遵守

type变成ALL，查询从索引的最左侧开始查询，即创建索引时create index idx_staffs_nameAgePos on staffs(name,age,pos);name是最左侧的。

• 带头大哥不能忘
  

• 中间兄弟不能断

中间的索引age缺少，只使用到name字段的索引。

• 索引列上少计算

• 范围之后全失效

• 注意无论age>18变换到哪个位置都key_len的长度都是78，都只用到name和age两个字段的索引：
  

• like百分写最右
  

• 覆盖索引不写星

当查询的字段不为索引时，同样type会变成全表扫描。

• 不等空值还有or

  

  当name字段not null时和null时

  not null:

null:

or：

• 索引失效要少用

• varchar引号不可丢

不加''时，系统会有隐式转换。加上引号之后，使用到索引。

• SQL高级也不难

练习

假设index(a,b,c)

where语句	索引是否被使用到
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a,b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4	N
where a = 3 and c = 5	使用到a,c没有被使用,b中间断了
where a = 3 and b > 4 and c = 5	使用到了a,b
where a = 3 and b like ‘kk%’ and c = 4	使用到了a,b,c
where a = 3 and b like ‘%kk’ and c = 4	使用到了a
where a = 3 and b like ‘%kk%’ and c = 4	使用到了a

4. 索引优化案例

单表优化

建表

create table article(
    id int unsigned not null primary key auto_increment,
    author_id int unsigned not null,
    category_id int unsigned not null,
    views int unsigned not null,
    comments int unsigned not null,
    title varchar(255) not null,
    content text not null
);

插入数据

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

需求：查询category_id为1且comments大于1的情况下,views最多的article_id

select article_id from article where category_id =1 and comments > 1 order by views desc limit 1;

为解决出现的using filesort，为category和views字段添加一个复合索引。

create index idx_article_cv on article(category_id,views);

也可以创建三个索引

create index idx_article_cvc on article(category_id,viewscomments);

效果与两个相同，所以只要创建两个索引即可，索引同样会占用内存。