devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql调优_waiting for handler commit

作者：Li_阴宅 | 2024-08-20 10:20:22

赞

踩

waiting for handler commit

优化有两种：RBO（基于规则的优化）

​ CBO（基于成本的优化）

Mysql 8.0之前，server中有缓存，为了减少IO。但关系数据库内容修改频繁，所以8.0去除了这个功能。

一、性能监控

1、show profile

官网文档

使用show profile查看sql执行时间。

注意：该方式会在新版本中被弃用和替代。

在这里插入图片描述

使用方法：

  1. 设置属性

    set profiling=1;

    • 1

  2. 执行sql

  3. 查看profile

    -- 显示sql及执行总时长
show profiles; 
-- 显示sql每个步骤所用时长
show profile;  

    • 1
    • 2
    • 3
    • 4

    如下：

    mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from store;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update         |
+----------+------------------+------------+---------------------+
|        1 |                1 |          1 | 2006-02-15 04:57:12 |
|        2 |                2 |          2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00030825 | select * from store |
+----------+------------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000039 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000121 |
| init                           | 0.000004 |
| System lock                    | 0.000006 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000008 |
| preparing                      | 0.000012 |
| executing                      | 0.000035 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| waiting for handler commit     | 0.000005 |
| closing tables                 | 0.000004 |
| freeing items                  | 0.000044 |
| cleaning up                    | 0.000015 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    查的是query_Id为2的sql的:

    mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00030825 | select * from store |
|        2 | 0.00203925 | select * from staff |
+----------+------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000064 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000698 |
| init                           | 0.000004 |
| System lock                    | 0.000004 |
| optimizing                     | 0.000002 |
| statistics                     | 0.000008 |
| preparing                      | 0.000011 |
| executing                      | 0.001179 |
| end                            | 0.000005 |
| query end                      | 0.000002 |
| waiting for handler commit     | 0.000006 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000026 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

查询其他信息，可以制定type，如官网所示:

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

all：显示所有性能信息

block io：显示块io操作次数

coentext switches：显示上下文切换次数，被动和主动（

cpu：显示用户cpu时间、系统cpu时间（）

IPC：显示发送和接受的消息数量（）

Menory：暂未实现

page faults：显示页错误数量（）

source：显示源码中的函数名称与位置（）

swaps：显示swap的次数（）

使用：show profile [block io | all | coentext switches |…] for query n

2、performance schema

官方文档

MYSQL performance schema详解

默认情况，该模式是开启的，查看方式：

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.03 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

手动关闭，提示该变量为一个只读变量：

mysql> set performance_schema=off;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable

  • 1
  • 2

这时候需要修改mysql的一个配置文件my.cnf，才可以修改这个属性。

详细介绍：

3、show processlist

官网文档

查看连接数量：

mysql> show processlist;
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db     | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 170180 | Waiting on empty queue | NULL             |
|  8 | root            | localhost:60421 | NULL   | Query   |      0 | starting               | show processlist |
|  9 | root            | localhost:61885 | mytest | Sleep   |      3 |                        | NULL             |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

属性介绍：

id表示session id

user表示操作的用户

host表示操作的主机

db表示操作的数据库

command表示当前状态

  • ​ sleep：线程正在等待客户端发送新的请求

  • ​ query：线程正在执行查询或正在将结果发送给客户端

  • ​ locked：在mysql的服务层，该线程正在等待表锁

  • ​ analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划

  • ​ Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中

  • ​ sorting result：线程正在对结果集进行排序

  • ​ sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据

info表示详细的sql语句

time表示相应命令执行时间

state表示命令执行状态

目前性能最好的连接池，阿里的Druid：官方文档

二、schema与数据类型优化

1、后缀名含义

.frm是表结构文件

.ibd表示表结构为InnoDB的数据和索引文件文件

.myd表示表结构为MyISAM的数据文件

.myi表示数据结构为MyISAM的索引文件

2、数据类型优化

（1）规则

1）使用最小数据类型：占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少

​ 案例：设计两张表，设计不同的数据类型，查看表的容量（读取都是以4k为单位）

2）简单就好：简单数据类型的操作通常需要更少的CPU周期

​ 例如:

​ a、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，

​ b、使用mysql自建类型而不是字符串来存储日期和时间

​ c、用整型存储IP地址

​ 案例：创建两张相同的表，改变日期的数据类型，查看SQL语句执行的速度

3）尽量避免null：列中包含null，查询难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂

（2）具体类型

1）整型

定义的长度无关，底层定了他的长度；TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。

2）字符和字符串类型

  • varchar

    • varchar(n)，n<=255时使用额外一个字节保存长度，n>255使用额外两个字节保存长度。

    • varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。

    • varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

    • 应用场景：

    ​ a、存储长度波动较大的数据，如：文章，有的会很短有的会很长

    ​ b、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度

    ​ c、适合保存多字节字符，如：汉字，特殊字符等

  • char

    • 最大长度：255

    • 会自动删除末尾的空格

    • 检索效率、写效率 会比varchar高，以空间换时间

    • 应用场景：

    ​ a、存储长度波动不大的数据，如：md5摘要

    ​ b、存储短字符串、经常更新的字符串

3）BLOB和TEXT类型

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。基本不会使用该方式，而是使用ftp服务器代替。

4）datetime和timestamp

  • datetime

    • 占用8个字节

    • 与时区无关，数据库底层时区配置，对datetime无效

    • 可保存到毫秒

    • 可保存时间范围大

    • 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

  • timestamp

    • 占用4个字节

    • 时间范围：1970-01-01到2038-01-19

    • 精确到秒

    • 采用整形存储

    • 依赖数据库设置的时区

    • 自动更新timestamp列的值

  • date

    • 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
    • 使用date类型还可以利用日期时间函数进行日期之间的计算
    • date类型用于保存1000-01-01到9999-12-31之间的日期
5）使用枚举代替字符串类型

查询和显示的时候是字符串，其实存储的是数字，节约空间，也可以按顺序排序查询

6）IP地址存储

ip地址和整型之间转换，最大转化为255.255.255.255

mysql> select INET_ATON('192.168.88.123');
+-----------------------------+
| INET_ATON('192.168.88.123') |
+-----------------------------+
|                  3232258171 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select INET_NTOA('3232258171');
+-------------------------+
| INET_NTOA('3232258171') |
+-------------------------+
| 192.168.88.123          |
+-------------------------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

3、合理使用范式和反范式

三范式最终目的是为了解决数据冗余

第一范式：列不允许再分

第二范式：属性必须完全依赖主键

第三范式：属性不允许出现传递依赖

使用了limit后order by失效

范式和反范式各有优缺点，实际项目中都是一起使用的

4、其他注意点

（1）代理主键

主键使用和业务无关的数字序列，优点：

  • 不和业务耦合，方便维护。
  • 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。
（2）字符集选择

纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

mysql中utf8只能存两个字节的字符，utf8mb4才能存3个

在linux中使用 man utf8可以查看编码介绍

（3）存储引擎的选择
MyISAMInnoDB
索引类型非聚簇索引聚簇索引
支持事务否是
支持表锁是是
支持行锁否是
支持外键否是
支持全文索引是是（5.6后支持）
适合操作类型大量select大量insert、delete、update

  • 建表时不设置存储引擎的话，默认的是INNODB，默认值在my.ini文件中，可修改

  • default-storage-engine=INNODB

  • INNODB加锁是默认加在索引上的，where条件后的列如果加了索引，则加的是行锁，否则为表锁

  • 存储引擎代表的是数据文件的组织形式

聚簇索引：数据文件和索引文件放在一起

非聚簇索引：数据文件和索引文件不放在一起

（4）适当的数据冗余
  • 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
  • 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

oracle中有物化视图，有两种方式，一种基表更改，视图更新；另一种，查询的时候才从基表更新。

（5）适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

三、执行计划

mysql执行计划

四、索引优化

1、索引零散知识

（1）不使用其他数据结构做索引的原因

hash表：

  • 使用hash表的话，要加载所有的数据文件，比较耗内存空间
  • 等值查找快，但是不能进行范围查找
  • 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
  • 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
  • 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
  • 哈希冲突比较多的话，维护的代价也会很高

memory存储引擎就是使用hash表作为索引文件的。

二插查找树：可能造成树过于深，导致查找IO过于庞大。

AVL树：由于要保证平衡最短子树和最长子树不能超过1，每次超出标准是要进行左旋、右旋，而导致插入效率低，而且也可能树比较深。

红黑树：最长子树不超过最短子树的两倍即可。牺牲了插入效率，来提高查询效率。

B-Tree：B-Tree非叶子结点有数据，导致IO的时候需要读入太多数据到内存。

B Tree*：B*Tree是非也只节点也有指针，对索引来说没必要

（2）InnoDB和MyISAM索引结构区别

两者都是使用B+树作为索引结构。

InnoDB：数据和索引在同一个文件，所以索引B+Tree叶子节点存放了数据。

MyISAM：数据和索引不在一个文件，所以索引B+Tree叶子节点存放的是数据文件的数据地址。

（3）创建表一定要建立主键

InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键，该主键在mysql中不可见，在oracle中可见。

（4）回表

非主键索引的叶子节点存的是主键。那么查询条件为非主键索引的时候，查询费主键索引树后，得到主键，然后还需要通过主键查询主键索引树，从而来查找到内容，这个过程叫回表。

（5）索引覆盖

  • 如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
  • explain 中的Extra列为Using index表示索引覆盖，没有表示不是索引覆盖
  • memory不支持覆盖索引
  • 一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
  • 由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

（6）索引下推

​ 组合索引中有（name,age）

​ sql语句有where name=‘’ and age=‘ ’；

​ 老版本先把name匹配出来，把age全部取出来，但是后来在匹配name的时候就把age过滤了（5.7在存储引擎中就已经做了匹配了，而没有优化前的是在service中做的匹配）。

（7）索引优点

  • 大大减少了服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 将随机io变成顺序io

（8）索引用处

  • 快速查找匹配WHERE子句的行
  • 从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
  • 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  • 当有表连接的时候，从其他表检索行数据
  • 查找特定索引列的min或max值
  • 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
  • 在某些情况下，可以优化查询以检索值而无需查询数据行

（9）索引分类

  • 主键索引
  • 唯一索引
  • 普通索引
  • 全文索引
  • 组合索引

（10）索引匹配的方式

全值匹配：指的是和索引中的所有列进行匹配

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

  • 1

匹配最左前缀：只匹配前面的几列

explain select * from staffs where name = 'July' and age = '23';
explain select * from staffs where name = 'July';

  • 1
  • 2

匹配列前缀：可以匹配某一列的值的开头部分

explain select * from staffs where name like 'J%';
explain select * from staffs where name like '%y';

  • 1
  • 2

匹配范围值：可以查找某一个范围的数据

explain select * from staffs where name > 'Mary';

  • 1

精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分

explain select * from staffs where name = 'July' and age > 25;

  • 1

只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

  • 1

2、组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

案例，建立组合索引a,b,c，不同SQL语句使用索引情况：

语句索引是否发挥作用
where a = 3是，只使用了a
where a = 3 and b = 5是，使用了a,b
where a = 3 and b = 5 and c = 4是，使用了a,b,c
where b = 3 or where c = 4否
where a = 3 and c = 4是，仅使用了a
where a = 3 and b > 10 and c = 7是，使用了a,b
where a = 3 and b like ‘%xx%’ and c = 7使用了a,b

3、优化小细节

  • 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
select actor_id from actor where actor_id=4;select actor_id from actor where actor_id+1=5;

  • 1

  • 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

  • 使用前缀索引

​ 前缀索引实例说明

  • 使用索引扫描来排序

  • union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;explain select * from actor where actor_id in (1,2);explain select * from actor where actor_id = 1 or actor_id =2;

  • 1

  • 范围列可以用到索引

    • 范围条件是：<、<=、>、>=、between

    • 范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

  • 强制类型转换会全表扫描

explain select * from user where phone=13800001234;#不会触发索引explain select * from user where phone='13800001234';#触发索引

  • 1

  • 更新十分频繁，数据区分度不高的字段上不宜建立索引

    • 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能

    • 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，、

    • 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

  • 创建索引的列，不允许为null，可能会得到不符合预期的结果

  • 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

  • 能使用limit的时候尽量使用limit

  • 单表索引建议控制在5个以内

  • 单索引字段数不允许超过5个（组合索引）

4、索引监控

查看参数：

show status like 'Handler_read%';

  • 1

参数解释：

Handler_read_first：读取索引第一个条目的次数

Handler_read_key：通过index获取数据的次数

Handler_read_last：读取索引最后一个条目的次数

Handler_read_next：通过索引读取下一条数据的次数

Handler_read_prev：通过索引读取上一条数据的次数

Handler_read_rnd：从固定位置读取数据的次数

Handler_read_rnd_next：从数据节点读取下一条数据的次数

5、索引优化分析案例

五、查询优化

1、查询慢的原因

  • 网络
  • CPU
  • IO
  • 上下文切换
  • 系统调用
  • 生成统计信息
  • 锁等待时间

2、优化数据访问

（1）查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化

  • 确认应用程序是否在检索大量超过需要的数据
  • 确认mysql服务器层是否在分析大量超过需要的数据行

（2）是否向数据库请求了不需要的数据

  • 查询不需要的记录

我们常常会误以为mysql会只返回需要的数据，实际上mysql却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。

优化方式是在查询后面添加limit

  • 多表关联时返回全部列
select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';select actor.* from actor...;

  • 1
  • 总是取出全部列

在公司的企业需求中，禁止使用select *,虽然这种方式能够简化开发，但是会影响查询的性能，所以尽量不要使用

  • 重复查询相同的数据

如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，因此，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率

3、执行过程的优化

（1）查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端

（2）查询优化处理

1）语法解析器和预处理

mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询，例如验证使用使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等

2）查询优化器

当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划

mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个

1.一个案例
select count(*) from film_actor;
show status like 'last_query_cost';

  • 1
  • 2

可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的

  • 每个表或者索引的页面个数
  • 每个表或者索引的页面个数
  • 索引和数据行的长度
  • 索引的分布情况
2.mysql在有些情况下会选择错误的执行计划

原因：

  • 统计信息不准确

InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息

  • 执行计划的成本估算不等同于实际执行的成本

有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，mysql层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的

  • mysql的最优可能跟你想的不一样

mysql的优化是基于成本模型的优化，但是有可能不是最快的优化

  • mysql不考虑其他并发执行的查询
  • mysql不会考虑不受其控制的操作成本

执行存储过程或者用户自定义函数的成本

3.优化器的优化策略
  • 静态优化：直接对解析树进行分析，并完成优化
  • 动态优化：动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关
  • mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估
4.优化器的优化类型
  • 重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能

  • 将外连接转化成内连接，内连接的效率要高于外连接
  • 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式
  • 优化count(),min(),max()

索引和列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较

  • 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理
explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1

  • 1
  • 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引
  • 子查询优化

mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中

  • 等值传播

如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

  • 1

这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表

explain 
select film.film_id 
from film 
inner join film_actor 
using(film_id) 
where film.film_id > 500 
and film_actor.film_id > 500;

3

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
5.关联查询

1.join的实现方式原理：

A、Simple Nested-Loop Join：

在这里插入图片描述

B、Index Nested-Loop Join：

在这里插入图片描述

C、Block Nested-Loop Join：

在这里插入图片描述

  • Join Buffer会缓存所有参与查询的列而不是只有Join的列。
  • 可以通过调整join_buffer_size缓存大小
  • join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
  • 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。
show variables like '%optimizer_switch%'

  • 1

2.案例演示：

查看不同的顺序执行方式对查询性能的影响：

explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);

  • 1

查看执行的成本：

show status like 'last_query_cost'; 

  • 1

按照自己预想的规定顺序执行：

explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);

  • 1

查看执行的成本：

show status like 'last_query_cost'; 

  • 1
6.排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度出发，应该尽可能避免排序或者尽可能避免对大量数据进行排序。

推荐使用利用索引进行排序，但是当不能使用索引的时候，mysql就需要自己进行排序，如果数据量小则再内存中进行，如果数据量大就需要使用磁盘，mysql中称之为filesort。

如果需要排序的数据量小于排序缓冲区(show variables like ‘%sort_buffer_size%’

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/1006588
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。