精华内容
下载资源
问答
  • mysql慢查询排查,执行效率
    千次阅读
    2019-10-29 11:18:13

    如何在MySQL中查找效率慢的SQL语句呢?这可能是困扰很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL 。

    MySQL慢查询定义

    分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划,还可以让MySQL记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句查询称为“慢查询”。

    慢查询的体现

    慢查询主要体现在慢上,通常意义上来讲,只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU,内存消耗过高。数据库服务器压力陡然过大,那么大部分情况来讲,肯定是由某些慢查询导致的。

    开启慢查询的方法:

    MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

    mysql> show variables like 'long%';
    
    long_query_time | 10.000000
    
    mysql> set long_query_time=1;
    
    mysql> show variables like 'slow%';
    
    slow_launch_time    | 2
    
    slow_query_log      | ON
    
    slow_query_log_file | /tmp/slow.log
    
    mysql> set global slow_query_log='ON'
    

    字段解释

    long_query_time 当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

    slow_query_log 这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

    slow_query_log_file 记录日志的文件名。

    一旦slow_query_log变量被设置为ON,MySQL会立即开始记录。

    /etc/my.cnf   里面可以设置上面MySQL全局变量的初始值。

    分析SQL执行效率浅谈

    一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

    slow_query_log

    这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

    long_query_time

    当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

    slow_query_log_file

    记录日志的文件名。

    log_queries_not_using_indexes

    这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

    二、检测MySQL中sql语句的效率的方法

    1. 通过查询日志

    1)Windows下开启MySQL慢查询

    MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上

    代码如下

    log slow queries = F:/MySQL/log/mysqlslowquery.log

    long_query_time = 2

    2)Linux下启用MySQL慢查询

    MySQL在Linux系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

    代码如下

    log slow queries = /data/mysqldata/slowquery.log

    long_query_time = 2

    说明:

    log slow queries = /data/mysqldata/slowquery.log

    为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录;

    long_query_time = 2中的2表示查询超过两秒才记录;

     

    2. show processlist 命令

    SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。

    例如如图:

    各列的含义和用途:

    Id列:一个标识,你要kill一个语句的时候很有用,用命令杀掉此查询 /*/mysqladmin kill 进程号。

    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语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

    这个命令中最关键的就是state列,MySQL列出的状态主要有以下几种:

    Checking table:正在检查数据表(这是自动的)。

    Closing tables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

    Connect Out:复制从服务器正在连接主服务器。

    Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

    Creating tmp table:正在创建临时表以存放部分查询结果。

    deleting from main table:服务器正在执行多表删除中的第一部分,刚删除第一个表。

    deleting from reference tables:服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

    Flushing tables:正在执行FLUSH TABLES,等待其他线程关闭数据表。

    Killed:发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

    Locked:被其他查询锁住了。

    Sending data:正在处理SELECT查询的记录,同时正在把结果发送给客户端。

    Sorting for group:正在为GROUP BY做排序。

    Sorting for order:正在为ORDER BY做排序。

    Opening tables:这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。

    Removing duplicates:正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

    Reopen table:获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

    Repair by sorting:修复指令正在排序以创建索引。

    Repair with keycache:修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

    Searching rows for update:正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。

    Sleeping:正在等待客户端发送新请求.

    System lock:正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

    Upgrading lock:INSERT DELAYED正在尝试取得一个锁表以插入新记录。

    Updating:正在搜索匹配的记录,并且修改它们。

    User Lock:正在等待GET_LOCK()。

    Waiting for tables:该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

    waiting for handler insert:INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

    大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。

    还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

    三、explain来了解SQL执行的状态

    explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在select语句前加上explain就可以了,例如下:

    explain select c.title, cc.content form comment_content cc, comment c where cc.id=c.id

    结果如图:

    EXPLAIN列的解释

    table列:显示这一行的数据是关于哪张表的

    type列:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

    possible_keys 列:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key列:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句 中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

    key_len列:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref列:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows列:MYSQL认为必须检查的用来返回请求数据的行数

    Extra列:关于MYSQL如何解析查询的额外信息。

    最后MySQL优化建议

    索引优化,最简单粗暴的办法,给查询语句添加复合索引,但不是最好的方式

    将大表拆成小的汇总表

    重在实践,MySQL优化器在很多情况下不能给出,最快的实现方式

    避免在大表上的group by,order by,offset 操作,除非你知道如何优化的前提下

    SQL WHERE查询条件,尽量按照添加的索引顺序来

    更多相关内容
  • 查询mysql执行效率低的sql语句的方法,需要的朋友可以参考一下
  • 主要介绍了MySql批量插入优化Sql执行效率实例详解的相关资料,需要的朋友可以参考下
  • 根据mysql慢日志监控SQL语句执行效率 启用MySQL的log-slow-queries(慢查询记录)。
  • 主要介绍了php使用mysqli和pdo扩展,测试对比mysql数据库的执行效率,结合完整实例形式对比分析了php分别使用mysqli与pdo进行数据库插入操作的执行时间,需要的朋友可以参考下
  • MySQL通过慢查询日志定位执行效率较低的SQL语句,当慢查询日志的内容过多时,通过mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。  MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,...
  • 查询到效率低的 SQL 语句 后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序,比如我们想计算 2006 年所有公司的销售额,需要关联 ...
  • 查查询询mysql中中执执行行效效率率低低的的sql语语句句的的方方法法 查询mysql执行效率低的sql语句的方法需要的朋友可以参考一下 一些小技巧 1. 如何查出效率低的语句 MySQL下 启动参数中设置 --log-slow-queries...
  • 关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。 通过慢查询日志定位那些执行效率较低的 SQL 语句,用 –log-slow-queries[=file_name] 选项启动时, mysqld 会 写一个包含所有执行时间超过 long...
  • MySQL数据库执行效率对程序的执行速度有很大的影响,有效的处理优化数据库是非常有用的。尤其是大量数据需要处理的时候。 1. 优化你的MySQL查询缓存 在MySQL服务器上进行查询,可以启用高速查询缓存。让数据库引擎...
  • 【0】如何分析mysql中sql执行较慢的问题 步骤1、观察,至少跑一天,看看生产的慢sql情况; 步骤2、开启慢查询日志,设置阈值,比如超过5秒钟就是慢sql, 并将它抓取出来; 步骤3、explain+慢sql分析; 步骤4、...

    【0】如何分析mysql中sql执行较慢的问题

    • 步骤1、观察,至少跑一天,看看生产的慢sql情况;
    • 步骤2、开启慢查询日志,设置阈值,比如超过5秒钟就是慢sql, 并将它抓取出来;
    • 步骤3、explain+慢sql分析;
    • 步骤4、show profile;(推荐)
    • 步骤5、运维经理或dba,进行sql数据库服务器的参数调优;(不推荐)

    【总结】 

    • 总结1、慢查询的开启并捕获;
    • 总结2、explain + 慢sql分析;
    • 总结3、show profile 查询sql在mysql 服务器里面的执行细节和声明周期情况;
    • 总结4、sql数据库服务器的参数调优;

    ==============================================================================================

    【1】exists + order by + group by 优化 

    1.1、查询优化:使用 explain 查看执行计划,看是否可以基于索引查询;

    1.2、小表驱动大表:当两个表做连接操作时, 其实就是双层for循环, 小表驱动大表的意思是, 小表(小数据集的表)放在第1层循环,大表(大数据集的表)放在第2层循环;

    【补充】关于exists 语法 与 in 的区别:

    exists语法:把 where id in 换位 where  exists 即可;

    select ... from tbl where exists (subquery);

    exists语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果 true 或 false, 来决定主查询的数据结果是否保留;
    关于exists的提示:

    • 提示1:exists(subquery) 只返回true或false, 因此子查询中的select * 也可以是 select 1 或其他, 官方说法是实际执行时会忽略 select 清单,因此没有区别;
    • 提示2:exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担心效率问题,可以进行实际检验;
    • 提示3:exists 子查询往往也可以用条件表达式,其他子查询或join来替代,哪种方法最优需要具体问题具体分析;

    exists用法荔枝(exists与in的区别):

    -- exists 语法 
    select * from emp_tbl a where exists (select 1 from dept_tbl b where b.dept_id = a.dept_id)
    order by a.rcrd_id 
    limit 10
    ;
    -- in 语法
    select * from emp_tbl a where a.dept_id in (select b.dept_id from dept_tbl b)
    order by a.rcrd_id 
    limit 10
    ;
    

    -- 执行计划18
    explain  select * from emp_tbl a where exists (select 1 from dept_tbl b where b.dept_id = a.dept_id)
    ;
    -- 执行计划19
    explain select * from emp_tbl a where a.dept_id in (select b.dept_id from dept_tbl b)
    ;

    ==============================================================================================

    1.2、order by 关键字优化 
    优化1、尽量使用index方式排序,避免使用 filesort方式;

    -- 建表
    drop table if exists birth_tbl;
    create table birth_tbl (
        `rcrd_id` int(10) unsigned primary key auto_increment COMMENT '记录编号'
        , age int default 0 comment '年龄'
        , birth timestamp default current_timestamp comment '生日'
    ) engine=innodb default charset=utf8 comment '生日表'
    ;
    -- 插入数据
    insert into birth_tbl(age) values 
    (floor(1+(rand()*100)))
    , (floor(1+(rand()*100)))
    , (floor(1+(rand()*100)))
    , (floor(1+(rand()*100)))
    , (floor(1+(rand()*100)))
    ;
    -- 添加索引
    alter table birth_tbl
    add key `idx_age_birth`(`age`, `birth`)
    ; 

    -- 查看order by的执行计划是否使用了文件排序; 

    -- 执行计划20  索引排序
    explain select * from birth_tbl where age > 30 order by age
    ;
    -- 执行计划21  索引排序
    explain select * from birth_tbl where age > 30 order by age, birth
    ;
    -- 执行计划22  文件排序
    explain select * from birth_tbl where age > 30 order by birth
    ;
    -- 执行计划23  文件排序
    explain select * from birth_tbl where age > 30 order by birth, age
    ;
    -- 执行计划24  文件排序
    explain select * from birth_tbl order by birth
    ;
    -- 执行计划25  文件排序
    explain select * from birth_tbl where birth > '2018-01-01 00:00:00' order by birth
    ;
    -- 执行计划26  索引排序
    explain select * from birth_tbl where birth > '2018-01-01 00:00:00' order by age
    ;
    -- 执行计划27  文件排序
    explain select * from birth_tbl order by age asc, birth desc
    ;

    mysql支持两种方式的排序: 文件排序filesort 和 index 索引排序, index排序的效率较高; 它指mysql 扫描索引本身完成排序,文件排序filesort 效率较低;

    【补充】 order by 满足两个情况:会使用索引排序:

    • 情况1:order by 语句使用索引最左前列;
    • 情况2: 使用where子句与order by 子句条件组合满足索引最左前列;

    优化2、尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀;

    优化3、如果不在索引列上,文件排序filesort有两种算法: mysql需要启动双路排序或单路排序;

    优化策略:

    • 策略1:增大 sort_buffer_size 参数设置;
    • 策略2:增大 max_length_for_sort_data 参数的设置;
    • 策略3:why ?

    【总结】order by 总结-为排序使用索引:

    • 总结1、mysql两种排序方式: 文件排序或扫描有序索引排序;
    • 总结2、mysql能为排序和查询使用相同的索引;
    • 总结3、具体执行计划:

    key idx_a_b_c(a, b, c);

    • 总结3.1、order by 能使用索引最左前缀的有:
    • order by a;
      order by a, b;
      order by a, b, c;
      order by a desc, b desc, c desc;  -- (要么全部升序,要么全部降序)
    •  
    • 总结3.2、如果where 使用索引的最左前缀定义为常量, 则order by 能使用索引;
    • where a = const order by b, c;
      where a=const and b=const order by c;
      where a = const and b > const order by b, c;
    •  
    • 总结3.3、不能使用索引进行排序:
    • order by a asc, b desc, c desc;
      where g = const order by b, c;
      where a = const order by c;
      where a=const order by a, d -- d不是索引的一部分;
      where a in (...) order by b, c;

    1.3、group by 关键字优化;(均和order by 一样)

    • (1)group by 实质是先排序后再分组,遵照索引建的最佳左前缀;
    • (2)当无法使用索引列,增大 max_length_for_sort_data 参数的设置+增大 sort_buffer_size 参数的设置;
    • (3)where高于 having, 能写在 where 限定的条件就不要去 having 限定了 ;

    =============================================================================================

    【2】慢查询日志

    0、什么是慢查询sql:sql运行时间超过 long_query_time 的sql 将会被记录到 慢查询日志中;

    • 0.1)mysql的慢查询日志是mysql提供的一种日志记录, 它用来记录在mysql中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的sql, 则会被记录到慢查询日志中;
    • 0.2)long_query_time 的默认值为10, 则表示运行时间超过10秒的sql语句被记录到慢查询日志中;
    • 0.3)通过收集超过10秒的sql, 结合之前 explain 进行全面分析;

    1)mysql的慢查询日志

    • 1.1)默认情况下, mysql没有开启慢查询日志,需要手工开启;
    • 1.2)如果不是调优需要的话,一般不建议启动该参数; 因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件;

    2)查看是否开启慢查询日志以及如何开启? 
    默认: show variables like '%slow_query_log%';

    开启: set global slow_query_log=1;

    【注意】

    • 注意1: 使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效, 如果mysql重启以后则会失效;
    • 注意2:如果要永久生效,必须修改配置文件 my.cnf ;

    如何设置long_query_time ?
    show variables like 'long_query_time%';
    查询 long_query_time的值?即查询当前多少秒算慢?
    show variables like 'long_query_time'

    设置慢的阈值时间? 
    set global long_query_time=3;

    为什么设置后期 long_query_time 还是没变;
    这个时候需要重新连接或新开一个会话; 或者执行 show global variables like 'long_query_time' ;

    如何制造 执行时间超过3秒的SQL?
    如  select sleep(4);

    查看当前有多少条慢查询sql?
    show global status like '%slow_queries%';

    补充1:如何在my.ini文件中配置mysql的慢查询参数, 如下:
    补充2: 日志分析工具 mysqldumpslow ,常用于在生产中分析sql的性能;

     

    =============================================================================================
    【3】、批量数据脚本;

    -- 新建函数-产生随机的字符串
    drop function if exists rand_str;
    delimiter ##
    create function rand_str(n int) returns varchar(255)
    begin 
    	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    	declare return_str varchar(255) default '';
    	declare i int default 0;
    	while i < n do 
    		set return_str=concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
    		set i=i+1;
    	end while;
    	return return_str;
    end ## 
    delimiter ;
    
    -- 新建函数-产生随机的整数
    drop function if exists rand_num; 
    delimiter ##
    create function rand_num() returns int
    begin 
    	declare i int default 0;
    	set i=floor(100+rand()*10);
    	return i;
    end ## 
    delimiter ;
    
    -- 创建存储过程,函数没法单独被调用,只能通过存储过程进行调用;
    -- 新建存储过程-调用函数批量插入数据 
    drop procedure if exists insert_emp; 
    delimiter ##
    create procedure insert_emp(in start_num int, in max_num int) 
    begin 
    	declare i int default 0;
    	set autocommit=0;
    	repeat 
    		set i=i+1;
    		INSERT INTO mybatis.emp_tbl (emp_id, dept_id, name)VALUES(rand_num(), rand_num(), rand_str(20));
    		until i = max_num
    	end repeat;
    	commit;
    end ## 
    delimiter ;
    
    
    call insert_emp(0, 100000)
    ;

    =============================================================================================

    【4】show profile

    4、show profile
    4.0)intro:
    show profile 提供了比 explain 更加细粒度的sql执行计划分析和sql优化;
    4.1)是什么: 是mysql提供可以用来分析当前回话中语句执行的资源消耗情况。可以用于sql的调优测量;
    4.2)官网: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html 
    4.3)默认情况下,参数处于关闭状态,并保持最近15次的运行结果;
    4.4)分析步骤:

    • 步骤1:是否支持,看看当前的mysql版本是否支持 show profile;
    • show variables like 'profiling';
    • 步骤2:开启功能,默认是关闭,使用前需要开启;
    • 步骤3:运行 sql; 且要运行耗时长的sql;
    • select * from emp_tbl e inner join dept_tbl d on e.dept_id = d.dept_id
      ;
      select * from emp_tbl e left join dept_tbl d on e.dept_id = d.dept_id
      ;
      select * from emp_tbl group by rcrd_id%10 
      ; 
    • 步骤4:查看结果,show profiles;
    • 步骤5:诊断sql, show profile cpu, block io for query 上一步前面的问题sql数字号码; 查看一条sql的完整生命周期;  show profile cpu, block io for query 3; 
    • 补充: 不仅仅可以查看 cpu, block io , 还可以查看如下类型的信息;
    • 步骤6: 日常开发需要注意的结论;以下结论都是性能比较差的sql的表现形式,即 show profile cpu, block io for query 3; 中的status中出现以下4种中的一种或几种,则sql执行效率较差,需要优化; 

    【关于show profile的结论】

    • 结论1)converting heap to myisam 查询结果太大, 内存都不够用了,往磁盘上搬;
    • 结论2)creating tmp table 创建临时表: 拷贝数据到临时表,用完再删除;
    • 结论3)copying to tmp table on disk, 把内存中临时表复制到磁盘,危险
    • 结论4) locked :锁住;  
       

    =============================================================================================

    【5】、全局查询日志;
    5.1、配置启用: 在mysql的 my.ini 中,配置如下:

    #开启
    general_log=1
    #记录日志文件的路径
    general_log_file=/path/logfile
    #输出格式
    log_output=FILE

    5.2、编码启用:命令如下:

    set global general_log=1;
    set global log_output='TABLE';


    此后, 你所编写的sql语句, 将会记录到 mysql库里的general_log 表,可以用下面的命令查看:
    select * from mysql.general_log; 


    5.3、建议不要在生产环境开启这个功能, 仅在测试环境开启以便调试;

     

    【建议】 建议使用 show profile 功能分析和优化sql性能; 
     

     

    展开全文
  • mysql中explain查看sql语句执行效率

    千次阅读 2016-12-27 14:54:45
    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 id SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序 select_type ...

    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。


    explain列的解释
    table:显示这一行的数据是关于哪张表的

    type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和all

    possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

    key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引

    key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows:mysql认为必须检查的用来返回请求数据的行数

    extra:关于mysql如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是using temporary和using filesort,意思mysql根本不能使用索引,结果是检索会很慢


    extra列返回的描述的意义

    distinct:一旦mysql找到了与行相联合匹配的行,就不再搜索了

    not exists: mysql优化了left join,一旦它找到了匹配left join标准的行,就不再搜索了

    range checked for each record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,mysql检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

    using filesort: 看到这个的时候,查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

    using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

    using temporary 看到这个的时候,查询需要优化了。这里,mysql需要创建一个临时表来存储结果,这通常发生在对不同的列集进行order by上,而不是group by上

    where used 使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型all或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)

    system 表只有一行:system表。这是const连接类型的特殊情况

    const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为mysql先读这个值然后把它当做常数来对待

    eq_ref:在连接中,mysql在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

    ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

    range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况

    index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all更好,因为索引一般小于表数据)

    all:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免


    id

    SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序

    select_type

    select类型,它有以下几种值

     simple 它表示简单的select,没有union和子查询

     primary 最外面的select,在有子查询的语句中,最外面的select查询就是primary,上图中就是这样

     union union语句的第二个或者说是后面那一个.现执行一条语句,explain
    select * from uchome_space limit 10 union select * from uchome_space limit 10,10

    会有如下结果

    第二条语句使用了union

     dependent union    UNION中的第二个或后面的SELECT语句,取决于外面的查询

     union result        UNION的结果,如上面所示

    还有几个参数,这里就不说了,不重要

     table

    输出的行所用的表,这个参数显而易见,容易理解

     type

    连接类型。有多个参数,先从最佳类型到最差类型介绍 重要且困难

    system 表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计

    const 表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快

    记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const,看下面这条语句

    explain SELECT * FROM `asj_admin_log` limit 1,结果是

    虽然只搜索一条数据,但是因为没有用到指定的索引,所以不会使用const.继续看下面这个

    explain SELECT * FROM `asj_admin_log` where log_id = 111

    log_id是主键,所以使用了const。所以说可以理解为const是最优化的

    eq_ref 对于eq_ref的解释,mysql手册是这样说的:"对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。看下面的语句

    explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid

    得到的结果是下图所示。很明显,mysql使用eq_ref联接来处理uchome_space表。

    目前的疑问:

     为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成

           explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid

           结果还是一样,需要说明的是uid在这两个表中都是primary

    ref  对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。

    看下面这条语句 explain select * from uchome_space where uchome_space.friendnum = 0,得到结果如下,这条语句能搜出1w条数据


    ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

    上面这五种情况都是很理想的索引使用情况

    index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。

     unique_subquery 是一个索引查找类型,可以完全替换子查询,效率更高。

    index_subquery该联接类型类似于unique_subquery,不过索引类型不需要是唯一索引,可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:

    range 给定范围内的检索,使用一个索引来检查行。看下面两条语句

    explain select * from uchome_space where uid in (1,2)

    explain select * from uchome_space where groupid in (1,2)

    uid有索引,groupid没有索引,结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显

    explain select * from uchome_space where friendnum = 17

    这样的语句是不会使用range的,它会使用更好的联接类型就是上面介绍的ref

     index     该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)

    当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

    ALL  对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    possible_keys 提示使用哪个索引会在该表中找到行,不太重要

    keys MYSQL使用的索引,简单且重要

    key_len MYSQL使用的索引长度

    ref   ref列显示使用哪个列或常数与key一起从表中选择行。

    rows 显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引

    Extra  该列包含MySQL解决查询的详细信息。

    Distinct    MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。一直没见过这个值

    Not exists 

     range checked for each record没有找到合适的索引

    using filesort    MYSQL手册是这么解释的“MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。”目前不太明白

     using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解,就是说明是否使用了索引

    explain select * from ucspace_uchome where uid = 1的extra为using index(uid建有索引)

    explain select count(*) from uchome_space where groupid=1 的extra为using where(groupid未建立索引)

     using temporary为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

    出现using temporary就说明语句需要优化了



    展开全文
  • MySQL执行流程

    千次阅读 多人点赞 2020-04-21 17:59:51
    一条SQL语句是怎么执行的? 1.1 连接 首先,数据是存储在MySQL服务端的,应用程序或者工具都是客户端,客户端想要读写数据,第一步得跟服务端建立连接。 1.2 查询缓存 MySQL内部自带了一个缓存模块,但是MySQL的...

    1. 一条SQL语句是怎么执行的?

    在这里插入图片描述

    1.1 连接

    首先,数据是存储在MySQL服务端的,应用程序或者工具都是客户端,客户端想要读写数据,第一步得跟服务端建立连接。

    1.2 查询缓存

    MySQL内部自带了一个缓存模块,但是MySQL的缓存默认是关闭的,因为MySQL自带的缓存的应用场景有限,第一个是它要求SQL语句必须一模一样,例如中间多一个空格、字母大小写不同都被认为是不一样的SQL语句。
    第二个是表里面的任何一条数据发生变化的时候,这张表所有的缓存都会失效,所以对于大量数据更新的应用,也不适合。
    缓存这一块还是交给ORM框架,或者独立的缓存服务,比如Redis来处理更合适。
    在MySQL8.0中,查询缓存已经被移除了。
    可以使用这行语句来查看MySQL查询缓存的相关属性

    show variables like 'query_cache%'; 
    

    1.3 语法解析和预处理

    假如随便执行一个字符串,服务器报了一个1064的错:
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the rightsyntaxtousenear’penyuyan’atline1 它是怎么知道输入的内容是错误的?
    这个就是MySQL的Parser解析器和Preprocessor预处理模块。
    这一步主要做的事情是对SQL语句进行词法和语法分析和语义的解析

    1.3.1.词法解析

    词法分析就是把一个完整的SQL语句打碎成一个个的单词。
    比如一个简单的SQL语句:

    select name from user where id=1
    

    它会打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。

    1.3.2.语法解析

    第二步就是语法分析,语法分析会对SQL做一些语法检查,比如单引号有没有闭合,
    然后根据MySQL定义的语法规则,根据SQL语句生成一个数据结构。
    这个数据结构我们把它叫做解析树(select_lex)。
    在这里插入图片描述
    词法语法分析是一个非常基础的功能,编译器、搜索引擎如果要识别语句,必须也要有词法语法分析功能。

    1.3.3 预处理器

    如果写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?
    是解析的时候报错还是执行的时候报错?
    实际上还是在解析的时候报错,解析SQL的环节里面有个预处理器。
    它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。
    预处理之后得到一个新的解析树。

    1.4. 查询优化(Query Optimizer)与查询执行计划

    1.4.1 什么是优化器?

    解析树是一个可以被执行器认识的数据结构。
    一条SQL语句是不是只有一种执行方式?或者说数据库最终执行的SQL是不是就是发送的SQL?
    这个答案是否定的。一条SQL语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?
    这个就是MySQL的查询优化器的模块(Optimizer)。
    查询优化器的目的就是根据解析树生成不同的执行计划(ExecutionPlan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。
    可以使用这个命令查看查询的开销:

    show status like 'Last_query_cost';
    

    1.4.2 优化器可以做什么?
    MySQL的优化器能处理哪些优化类型呢?
    举两个简单的例子:
    1、当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表(先访问哪张表)。
    2、有多个索引可以使用的时候,选择哪个索引。
    3、对于查询条件的优化,比如移除1=1 之类的恒等式,移除不必要的括号,表达式的计算,子查 询和连接查询的优化。

    1.4.3. 优化器得到的结果

    优化器最终会把解析树变成一个执行计划(execution_plans),执行计划也是一个数据结构。
    当然,这个执行计划是不是一定是最优的执行计划呢?不一定,因为MySQL也有可能覆盖不到所有的执行计划。
    我们怎么查看MySQL的执行计划呢?比如多张表关联查询,先查询哪张表?在执行查询的时候可能用到哪些索引,实际上用到了什么索引?
    MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

    EXPLAIN select cust_name from customer where cust_id=14;
    

    如果要得到详细的信息,还可以用FORMAT=JSON。

    1.5 存储引擎

    1.5.1 存储引擎基本介绍

    表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。
    在MySQL里面,支持多种存储引擎,他们是可以替换的,所以叫做插件式的存储引擎。

    1.5.2 查看存储引擎

    查看数据库里面已经存在的表的存储引擎:

    show table status from `ssm_crm`;
    

    在MySQL里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的(所以叫表类型)。而且,创建表之后还可以修改存储引擎。
    默认情况下,每个数据库都有一个自己的文件夹,存储在服务器端,可以通过下面这条语句查看

    show variables like 'datadir';
    

    任何一个存储引擎都有一个frm文件,这个是表结构定义文件。
    不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb 是 1 个,
    memory没有,myisam是两个。

    1.5.3 存储引擎比较

    我们可以用这个命令查看MySQL对存储引擎的支持情况:

    show engines;
    

    其中有存储引擎的描述和对事务、XA协议和savepoints的支持。
    XA协议用来实现分布式事务(分为本地资源管理器,事务管理器)。
    Savepoints用来实现子事务(嵌套事务)。创建了一个Savepoints之后,事务就可以回滚到这个点,不会影响到创建Savepoints之前的操作。
    在这里插入图片描述
    MyISAM(3个文件)

    MySQL自带的存储引擎,由ISAM升级而来。
    应用范围比较小。表级锁定限制了读/写的性能,因此在Web 和数据仓库配置中,它通常用于只读或以读为主的工作。
    特点:
    支持表级别的锁(插入和更新会锁表)。不支持事务。
    拥有较高的插入(insert)和查询(select)速度。
    存储了表的行数(count速度更快)。
    (怎么快速向数据库插入100万条数据?我们有一种先用MyISAM插入数据,然后
    修改存储引擎为InnoDB的操作。)
    适合:只读之类的数据分析的项目。

    InnoDB(2个文件)

    mysql5.7中的默认存储引擎。 InnoDB是一个事务安全(与ACID兼容)的MySQL存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级为更粗粒度的锁)和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性,InnoDB还支持外键引用完整性约束。

    特点:

    • 支持事务,支持外键,因此数据的完整性、一致性更高。
    • 支持行级别的锁和表级别的锁。
    • 支持读写并发,写不阻塞读(MVCC)。
    • 特殊的索引存放方式,可以减少IO,提升查询效率。
    • 适合:经常更新的表,存在并发读写或者有事务处理的业务系统。

    Memory(1 个文件)

    将所有数据存储在RAM中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少; InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中,而ndbcluster为大型分布式数据集提供了快速的键值查找。

    特点:

    • 把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失。只适合做临时表。
    • 将表中的数据存储到内存中。
    • 默认使用哈希索引。

    CSV(3个文件)

    它的表实际上是带有逗号分隔值的文本文件。 csv表允许以csv格式导入或转储数据,以便与读写相同格式的脚本和应用程序交换数据。因为csv 表没有索引,所以通常在正常操作期间将数据保存在innodb表中,并且只在导入或导出阶段使用csv表。
    特点:不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之
    间导入导出。

    不同的存储引擎提供的特性都不一样,它们有不同的存储机制、索引方式、锁定水平等功能。
    我们在不同的业务场景中对数据操作的要求不同,就可以选择不同的存储引擎来满足我们的需求,这个就是MySQL支持这么多存储引擎的原因。

    1.5.4 如何选择存储引擎

    • 如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。
    • 如果数据查询多更新少,对查询性能要求比较高,可以选择MyISAM。
    • 如果需要一个用于查询的临时表,可以选择Memory。
    • 如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用C语言开发一个存储引擎,按照这个开发规范,实现相应的接口,给执行器操作。也就是说,为什么能支持这么多存储引擎,还能自定义存储引擎,表的存储引擎改了对Server访问没有任何影响,就是因为大家都遵循了一定了规范,提供了相同的操作接口。

    1.6. 执行引擎(Execution Engine),返回结果

    执行器,或者叫执行引擎,它利用存储引擎提供的相应的API来完成操作。最后把数据返回给客户端,即使没有结果也要返回。

    总结:一条SQL语句的执行流程大致为,客户端先与MySQL服务器建立连接,然后,发送一条查询语句,如果MySQL开启了缓存的话,会将SQL语句存到缓存中,然后,解析器进行词法和语法的解析,接着预处理器会进行再次检查,比如检查表名和列名是否存在,没有问题的话,优化器会对SQL语句进行优化,生成一个执行计划,交给执行器执行SQL,执行器调用存储引擎,存储引擎读取磁盘数据,将查询结果交给执行器,执行器再将查询结果反馈给客户端或缓存。

    2. 架构分层

    总体上,我们可以把 MySQL 分成两层,执行操作的服务层,和存储管理数据的存储引擎层(参考 MyBatis:接口、核心、基础)
    在这里插入图片描述

    2.1 服务层

    包括客户端跟服务端的连接,查询缓存的判断、对 SQL 语句进行词法和语法的解析(比如关键字怎么识别,别名怎么识别,语法有没有错误等等)。然后就是优化器,MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化,最后交给执行器去执行。

    2.2 存储引擎层

    存储引擎就是我们的数据真正存放的地方,在 MySQL 里面支持不同的存储引擎。再往下就是文件管理系统,内存或者磁盘。

    3. 一条更新 SQL 是如何执行的?

    更新流程和查询流程有什么不同呢?基本流程也是一致的,也就是说,它也要经过解析器、优化器的处理,最后交给执行器。 区别就在于拿到符合条件的数据之后的操作。

    3.1 缓冲池 Buffer Pool

    InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),作为访问数据库的缓冲:当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool;当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中 。在这里插入图片描述

    修改数据的时候,先修改内存缓冲池里面的页。内存的数据页和磁盘数据不一致的时候,我们把它叫做脏页。那脏页什么时候同步到磁盘呢?
    InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏

    3.2 InnoDB 内存结构和磁盘结构

    官方文档
    在这里插入图片描述

    内存结构里面主要是 Buffer Pool、Change Buffer、Log Buffer、AHI,下面分别讲一下。

    Buffer Pool

    官方文档

    Buffer Pool 缓存的是 page 页面信息。
    查看服务器状态,里面有很多跟 Buffer Pool 相关的信息,这些状态都可以在官网查到详细的含义。

    SHOW STATUS LIKE '%innodb_buffer_pool%';
    

    在这里插入图片描述
    Buffer Pool 默认大小是 128M(134217728 字节),可以调整。
    查看参数(系统变量):

    SHOW VARIABLES like '%innodb_buffer_pool%';
    

    (redo)Log Buffer

    Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了避免这个问题,InnoDB 把所有对页面的修改操作专门写入一个日志文件Redo Log。如果有未同步到磁盘的数据,数据库在启动的时候,会从这个日志文件进行恢复操作(实现 crash-safe)。我们说的事务的 ACID 里面 D(持久性),就是用它来实现的。
    在这里插入图片描述

    这个日志文件就是磁盘的 redo log(叫做重做日志),对应于/var/lib/mysql/目录下的 ib_logfile0 和 ib_logfile1,默认 2 个文件,每个 48M,可以使用如下命令查看

    show variables like 'innodb_log%';
    
    参数含义
    innodb_log_file_size指定每个文件的大小,默认 48M
    innodb_log_files_in_group指定文件的数量,默认为 2
    innodb_log_group_home_dir指定文件所在路径,相对或绝对。如果不指定,则为datadir 路径。

    3.3 redo log如何保证事务的持久性?

    Redo log可以简单分为以下两个部分:

    • 一是内存中重做日志缓冲 (redo log buffer),是易失的,在内存中
    • 二是重做日志文件 (redo log file),是持久的,保存在磁盘中

    这里再细说下写入Redo Log的时机:

    • 在数据页修改完成之后,在脏页刷出磁盘之前,写入redo日志。注意的是先修改数据,后写日志
    • redo日志比数据页先写回磁盘
    • 聚集索引、二级索引、undo页面的修改,均需要记录Redo日志。

    在这里插入图片描述
    在 MySQL中,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就采用了日志(redo log)来提升更新效率。

    当事务提交时,先将 redo log buffer 写入到 redo log file 进行持久化,待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化),在将数据写入磁盘前,先将内存中相应的日志页持久化。

    具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(redo log buffer)里面,并更新内存(buffer pool),这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候(如系统空闲时),将这个操作记录更新到磁盘里面(刷脏页)。

    3.4 同样是写磁盘,为什么不直接写到 db file 里面去?为什么先写日志再写磁盘?

    如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到我们所需要的一块数据,一次进行此过程直到找完所有数据,这个就是随机 IO,读取数据速度较慢。

    假设我们已经找到了第一块数据,并且其他所需的数据就在这一块数据后边,那么就不需要重新寻址,可以依次拿到我们所需的数据,这个就叫顺序 IO。

    刷盘是随机 I/O,而记录日志是顺序 I/O(连续写的),顺序 I/O 效率更高。因此先把修改写入日志文件,在保证了内存数据的安全性的情况下,可以延迟刷盘时机,进而提升系统吞吐。

    3.5 数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?

    实际上,redo log 并没有记录数据页的完整数据,所以它并没有能力自己去更新磁盘数据页,也就不存在由 redo log 更新过去数据最终落盘的情况。

    ① 数据页被修改以后,跟磁盘的数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这个过程与 redo log 毫无关系。

    ② 在崩溃恢复场景中,InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,就会将它读到内存,然后让 redo log 更新内存内容。更新完成后,内存页变成脏页,就回到了第一种情况的状态。

    3.6 redo log buffer 是什么?是先修改内存,还是先写 redo log 文件?

    在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:

    begin;
    INSERT INTO T1 VALUES ('1', '1');
    INSERT INTO T2 VALUES ('1', '1');
    commit;
    
    

    这个事务要往两个表中插入记录,插入数据的过程中,生成的日志都得先保存起来,但又不能在还没 commit 的时候就直接写到 redo log 文件里。

    因此就需要 redo log buffer 出场了,它就是一块内存,用来先存 redo 日志的。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。

    但是,真正把日志写到 redo log 文件,是在执行 commit 语句的时候做的。

    redo log buffer 本质上只是一个 byte 数组,但是为了维护这个 buffer 还需要设置很多其他的 meta data,这些 meta data 全部封装在 log_t 结构体中。

    3.7 redo log顺序写入磁盘?

    redo log以顺序的方式写入文件,当全部文件写满的时候则回到第一个文件相应的起始位置进行覆盖写,每次提交事务之后,都先将相关的操作日志写入redo日志文件中,并且都追加到文件末尾,这是一个顺序I/O。
    在这里插入图片描述
    图中展示了一组 4 个文件的 redo log 日志,checkpoint 是当前要擦除的位置,擦除记录前需要先把对应的数据落盘(更新内存页,等待刷脏页)。write pos 到 checkpoint 之间的部分可以用来记录新的操作,如果 write pos 和 checkpoint 相遇,说明 redolog 已满,这个时候数据库停止进行数据库更新语句的执行,转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘(先更新内存页,然后等待刷脏页)。

    有了 redo log 日志,那么在数据库进行异常重启的时候,可以根据 redo log 日志进行恢复,也就达到了 crash-safe。

    redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失。

    3.8 redo log 有什么特点?

    1. redo log 是 InnoDB 存储引擎实现的,并不是所有存储引擎都有。支持崩溃恢复是 InnoDB 的一个特性。
    2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”。
    3. redo log 的大小是固定的,前面的内容会被覆盖,一旦写满,就会触发 buffer pool到磁盘的同步,以便腾出空间记录后面的修改。
    4. redo log 的内容主要是用于崩溃恢复。磁盘的数据文件,数据来自 buffer pool(只有 redo log 写满了,不能再记录更多内存的数据了,才把 buffer pool 刷盘,然后覆盖redo log)。

    3.9 undo Log

    undo Log(撤销日志或者回滚日志) 记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常,可以用 undo log 来实现回滚操作**(实现原子性)**。在执行 undo 的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从物理页面上操作实现的,属于逻辑格式的日志。

    实现原子性的关键,是当事务回滚时能够撤销所有已经成功执行的sql语句。 InnoDB 实现回滚,靠的是undo log :当事务对数据库进行修改时,InnoDB 会生成对应的undo log ,如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

    3.10 bin Log

    前面讲的两个日志是存储引擎(InnoDB)层的日志,而 Server 层也有自己的日志,称为 bin Log(归档日志)。

    binlog 以事件的形式记录了所有的 DDL 和 DML 语句,比如“给 ID=1 这一行的count 字段加 1 ”,因为它记录的是操作而不是数据值,属于逻辑日志)。binlog 可以用来做主从复制和崩溃恢复。
    跟 redo log 不一样,它的文件内容是可以追加的,没有固定大小限制。
    在开启了 binlog 功能的情况下,我们可以把 binlog 导出成 SQL 语句,把所有的操作重放一遍,来实现数据的(归档)恢复。
    binlog 的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的 binlog,然后执行一遍

    为什么会有两份日志呢?

    因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

    redo Log 和 bin Log的不同点

    1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
    2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
    3. redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

    3.11 更新语句流程

    在这里插入图片描述

    有了对这两个日志的概念性理解后,再来看执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程。

    ① 执行器先找引擎取 ID=1 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=1 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。

    ② 执行器拿到引擎给的行数据,修改行数据,得到新的一行数据,再调用引擎接口写入这行新数据。

    ③ 引擎将这行新数据更新到内存(InnoDB Buffer Pool)中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。

    ④ 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。

    ⑤ 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成。

    其中将 redo log 的写入拆成了两个步骤:prepare 和 commit,这就是两阶段提交(2PC)。

    3.12 为什么必须有“两阶段提交”呢?

    如果不使用两阶段提交,假设当前 ID=2 的行,字段 c 的值是 0,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash,会出现什么情况呢?

    先写 redo log 后写 binlog。 假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 c 的值是 1。
    但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句。因此,之后备份日志的时候,存起来的 binlog 里面就没有这条语句。
    然后你会发现,如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,这个临时库就会少了这一次更新,恢复出来的这一行 c 的值就是 0,与原库的值不同。

    先写 binlog 后写 redo log。 如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 c 的值就是 1,与原库的值不同。

    可以看到,如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

    简单说,redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。

    3.13 两阶段提交原理

    两阶段提交原理描述:

    ① redo log 写盘,InnoDB 事务进入 prepare 状态。

    ② 如果前面 prepare 成功,binlog 写盘,那么再继续将事务日志持久化到 binlog,如果持久化成功,那么 InnoDB 事务则进入 commit 状态

    redo log 和 binlog 有一个共同的数据字段,叫 XID。崩溃恢复的时候,会按顺序扫描 redo log:

    ① 如果碰到既有 prepare、又有 commit 的 redo log,就直接提交;

    ② 如果碰到只有 parepare、而没有 commit 的 redo log,就拿着 XID 去 binlog 找对应的事务。binlog无记录(不完整),回滚事务,binlog有记录(完整),提交事务。

    推荐阅读 count(*)实现原理+两阶段提交总结

    如有不对或不足之处,欢迎指正,谢谢!

    展开全文
  • MySQL——执行计划

    千次阅读 2021-01-18 19:45:54
    项目开发中,性能是我们比较关注的问题,特别是数据库的性能;作为一个开发,经常和SQL语句打交道...下面我们以MySQL5.7为例了解一下执行计划:注:文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划,...
  • mysql视图效率测试

    千次阅读 2016-08-02 16:33:11
    mysql中的视图不太只能,稍微复杂些的视图,不能展开,下面是测试: CREATE ALGORITHM=UNDEFINED DEFINER=myadmin@xxSQL SECURITY DEFINER VIEW v_big_table AS select big_table.TABLE_CATALOG AS TABLE_CATALOG,...
  • MySQL执行计划 项目开发中,性能往往都是是我们重点关注的问题,其实很多时候一个SQL往往是整个请求中瓶颈最大的地方,因此我们必须了解SQL语句的执行过程、数据库中是如何扫描表、如何使用索引的、是否命中索引等...
  •  在数据库设计的时候,我们经常会需要设计时间字段,在MYSQL中,时间字段可以使用int、timestamp、datetime三种类型来存储,那么这三种类型哪一种用来存储时间性能比较高,效率好呢?飘易就这个问题,来一个实践出...
  • Mysql优化之四:mysql执行计划之type

    千次阅读 2019-01-17 13:05:31
    mysql执行计划作为分析一条sql的执行效率的工具十分有效,通过explain关键字便可查看select语句的具体执行计划,分析其是否按我们设计的执行,是否使用了索引,是否全表扫描等等。不过有很多开发同学对explain返回...
  • Mysql语句执行逻辑

    千次阅读 多人点赞 2022-02-28 19:55:39
    一条SQL查询语句执行流程 select * from table where Id=4 要弄懂这条语句做的事情,我们先看下mysql整个架构涉及的 分为客户端,server端以及存储引擎,存储引擎层负责数据的存储和提取,其架构模式是插件式的,...
  • Mysql 执行一条语句的过程

    千次阅读 2022-03-11 14:37:34
    Mysql的逻辑架构 Mysql的逻辑架构如下所示,整体分为两部分,Server层和存储引擎层。 与存储引擎无关的操作都是在Server层完成的,存储引擎层负责数据...但是长连接也有优化的空间,即长连接过多,随着执行大的查询操作
  • MySQL执行引擎对比与SQL优化

    千次阅读 2019-07-03 21:08:29
    执行引擎 对于一个中级Java开发者来说...首先简单了解下MySQL执行引擎,首先MySQL执行引擎分类比较多,InnoDB(重要)、MyIsam(重要)、Memory、Mrg_Myisam、Blackhole等,虽然看着多,不过在日常学习和使用过程...
  • mysql查询,子句查询效率和排序效率测试
  • Mysql执行计划和Mysql优化

    万次阅读 2020-12-05 10:19:56
    执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。 作用:分析你的查询语句或是表结构的性能瓶颈。 语法:Explain + SQL语句 执行计划输出内容介绍:表的...
  •  统计信息用于指导mysql生成执行计划,执行计划的准确与否直接影响到SQL的执行效率;如果mysql一重启  之前的统计信息就没有了,那么当SQL语句来临时,那么mysql就要收集统计信息然后再生成SQL语句的执行  计划。...
  • MySQL中的SQL执行流程

    千次阅读 2022-03-12 13:23:30
    需要说明的是,因为查询混窜往往效率不高,所以在MySQL8.0之后就抛弃了这个功能 MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式,被...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 272,589
精华内容 109,035
关键字:

mysql执行效率

mysql 订阅
友情链接: EvaluatingSystem-master.zip