MySQL数据库(八)：MySQL索引优化

最新推荐文章于 2022-07-21 16:17:39 发布

慢羊羊6379.*?

最新推荐文章于 2022-07-21 16:17:39 发布

阅读量204

点赞数

分类专栏： mysql数据库

本文链接：https://blog.csdn.net/weixin_49088841/article/details/110095993

版权

mysql数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1、explain索引优化的查询测试

①员工表

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; --current_timestamp 默认之后当修改后它的时间就不会发生变化了，即一旦修改不在刷新，current_timestamp on update就可以同步更改

插入员工数据

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());

②用户表

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 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);

explain 查询语句，索引的口诀如下：
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效(跟创建索引的顺序有关系)
like百分写最右,覆盖索引不写星
不等非空还有or,索引失效要少用
varchar引号不可丢(不加也可以查询出来，sql底层会做一个隐式转，但是索引就会失效),SQL高级也不难
在这里插入图片描述
索引优化使用的练习，对照口诀的使用标准

2、show profiles 进行sql分析

①是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量，连接层、服务层、存储引擎层、文件层的使用情况
在这里插入图片描述
②默认情况下,参数处于关闭状态,并保持最近15次的运行结果。通过set profiling=on修稿当mysql服务重启的时候，配置就会失效，只有修改配置文件才会永久有效
③开启之后，默认会存储15条数据，如果超出会弹出第一条数据，如果想要记录所有sql语句则需要开启记录命令，通过show profiles可以查看自己写的sql语句耗费的时间，查看详情可以通过show profile for query id;来查看
在这里插入图片描述
④查看详情的时候也可以加多个不同的参数去实现

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

⑤如果出现下列参数说明sql语句必须要做优化了

converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘,危险
locked

在这里插入图片描述
⑥保存所有sql语句到表中的命令(全局查询日志)

set global general_log = 1;      # 开启命令
set global log_output = 'TABLE'; # 将SQL语句写到表中
select * from mysql.general_log; # 你所编写的SQL语句,会记录到MySQL库里的genral_log表

3、trace分析sql优化器

①explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？
②从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。
通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。
③如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。
④开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。

3.1使用 trace 进行分析

①开启trace

set session optimizer_trace="enabled=on",end_markers_in_json=on;
optimizer_trace="enabled=on" 表示开启trace
end_markers_in_json=on 表示 JSON 输出开启结束标记

②查看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)

③关闭traceset session optimizer_trace="enabled=off";
TRACE 字段中整个文本大致分为三个过程。
准备阶段：对应文本中的 join_preparation
优化阶段：对应文本中的 join_optimization
执行阶段：对应文本中的 join_execution
使用时，重点关注优化阶段和执行阶段。
explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

4、索引的执行

-- 创建员工表
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);

通过explain来分析用到了哪些索引

4.1索引执行的规则

全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等非空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难

(将所有的索引全部都用上，这是最优的索引执行)、(如果创建的第一个索引没有用上则不会使用到索引)、(索引创建的顺序如果中间的索引没有使用则不能向下使用索引)、(索引使用的时候不能加上函数比如date、lower等)、(不能给索引做加减等计算)、(不能给索引做范围运算，不然索引只能使用本级和上级索引)、(like ‘是%’，将%写在最右边，如果项目中用模糊查找非常浪费性能，一般全文匹配会用es，如果非要写%是%，那么select后面跟对应索引字段，也可以用到索引)、(用什么取什么)、(<>、!=、not null、or会导致索引的失效)、(如果varchar查询整数，mysql会默认加上引号引擎帮做的隐式转换，但是索引就会失效，虽然查询的出数据，但是时间一长，项目就会变得特别慢)

在这里插入图片描述

5、索引优化的案例

5.1单表优化

①建表

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
在这里插入图片描述

优化时候首先考虑的是创建索引，再去跟没有创建索引的时候做对比，可能是因为范围查询有大于号所以会出现文件内排序，考虑讲大于号变为等号，文件内排序优化成功，但是题目就发生变化了，所以不能这样修改
在这里插入图片描述

5.2双表优化

①建表
商品类别表

create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
);

图书表

create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
);

left join往右表添加索引，驱动表的概念，mysql中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。

慢羊羊6379.*?

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录