精华内容
下载资源
问答
  • Explain

    2019-12-20 16:37:31
    Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返 回执行计划的信息,而...

    Explain工具介绍
    使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈
    在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返
    回执行计划的信息,而不是执行这条SQL
    注意:如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中

    explain中的列
    接下来我们将展示 explain 中每个列的信息。
    1. id列
    id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的顺序增长的。
    id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。

    2. select_type列
    select_type 表示对应行是简单还是复杂的查询。
    1)simple:简单查询。查询不包含子查询和union
    mysql> explain select * from film where id = 2;

    2)primary:复杂查询中最外层的 select                                                                                                                                          3)subquery:包含在 select 中的子查询(不在 from 子句中)   

    4)derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)

    explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;    

                

    5)union:在 union 中的第二个和随后的 select 

    explain select 1 union all select 1;

    3. table列
    这一列表示 explain 的一行正在访问哪个表。
    当 from 子句中有子查询时,table列是 <derivenN> 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。
    当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的select 行id。 

    4. type列
    这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。
    依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL
    一般来说,得保证查询达到range级别,最好达到ref
    NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表

     explain select min(id) from film;

    5. possible_keys列
    这一列显示查询可能使用哪些索引来查找。
    explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果。 

    6. key列
    这一列显示mysql实际采用哪个索引来优化对该表的访问。
    如果没有使用索引,则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。

    7. key_len列
    这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
    8. ref列
    这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)

    9. rows列
    这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。

    10. Extra列
    这一列展示的是额外信息。常见的重要值如下:

    1)Using index:使用覆盖索引

    2)Using where:使用 where 语句来处理结果,查询的列未被索引覆盖

    3)Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围

    4)Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。

    5)Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

    6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段

    索引最佳实践

    CREATE TABLE `employees` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
     `age` INT(11) NOT NULL DEFAULT '0' COMMENT '年龄',
     `position` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
     `hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
     PRIMARY KEY (`id`),
     KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
     ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
     INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('LiLei',22,'manager',NOW());
     INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('HanMeimei',23,'dev',NOW());
     INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('Lucy',23,'dev',NOW());

     

    1.全值匹配

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

    EXPLAIN SELECT * FROM employees WHERE NAME= 'LiLei' AND age = 22 AND POSITION ='manager';
    -- 交换顺序一样,mysql内部会做优化调整查询条件顺序,前提是必须有前导列
    EXPLAIN SELECT * FROM employees WHERE age = 22 AND NAME= 'LiLei'  AND POSITION ='manager';

     

    2.最左前缀

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

    EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
    EXPLAIN SELECT * FROM employees WHERE position = 'manager';
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

    3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描 

    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
    EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

    4.存储引擎不能使用索引中范围条件右边的列 

    -- 将不会使用POSITION索引,只有前两个索引生效
    EXPLAIN SELECT * FROM employees WHERE NAME= 'LiLei' AND age > 22 AND POSITION ='manager';

    5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句 

    6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

    7.is null,is not null 也无法使用索引

    8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描操作

    9.字符串不加单引号索引失效

    10.少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

    11.范围查询优化(拆分查询范围)

    展开全文
  • explain

    2015-04-20 15:43:11
    1、explain 简介 explain 命令可以用来分析select 语句的运行效果,通过explain命令可以得到下面这些信息: 表的读取顺序,数据读取操作的操作类型 ,哪些索引可以使用,哪些索引被实际使用,表之间的引用,每张...

    1、explain 简介

    explain 命令可以用来分析select 语句的运行效果,通过explain命令可以得到下面这些信息: 表的读取顺序,数据读取操作的操作类型  ,哪些索引可以使用,哪些索引被实际使用,表之间的引用,每张表有多少行被优化器查询等信息。   

    两个扩展命令:

    • explain extended

     能够在原本explain的基础上额外的提供一些查询优化的信息,通过mysql的show warnings命令可以得到优化后的查询语句,可以看出优化器能优化什么。用explain extended查看执行计划会比explain多一列 filtered。filtered列给出了一个百分比的值,这个百分比值和rows列的值一起使用,rows * filtered/100 可以估计出那些将要和explain中的前一个表进行连接的行的数目。前一个表就是指explain 的 id列的值比当前表的id小的表。具体的使用,下面会有涉及。

    • explain partitions(TODO)

      查看语句访问的分区。

    2、explain 各列解释

     2.1 id

    MySQL Query Optimizer选定的执行计划中查询的序列号。

    这里的id,是select的序号,有几个select,就有多少个不同的id。并且id的顺序,是按照select在语句中出现的顺序往后延续的。

    如果没有出现derived等特殊语法,那么explain的执行顺序是按照从上到下来执行的。

    下面这个sql 的执行顺序是从里往外,依次执行。


    下面这个查询语句,子查询id为2先执行,inner join的两个查询的id都是1。

    explain select pd.poi_id,pc.store_name from poi_deal pd inner join (select * from poi_crm where category_id = 1) pc on pd.poi_id = pc.poi_id;

     2.2 select_type

    (1)SIMPLE

    简单的select查询语句。

    (2) PRIMARY
     复杂查询的最外层的select,这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

    explain select * from (select * from poi_crm where category_id =1) pc;

    (3) DERIVED

     当一个表不是一个物理表时,那么就被叫做DERIVED

    (4) UNION

    UNION 会产生没有id的行,select_type对应为UNION RESULT

    (5) UNION RESULT
              见上
     
    (6) SUBQUERY
              
    一般的子查询

    (7) DEPENDENT SUBQUERY
              
    与上面的区别就是子查询的select,取决于外面的查询。什么意思呢?请看下面的查询语句

    explain extended select * from business_opportunity bo where bo.id in (select boa.opportunity_id from business_opportunity_action boa where user_id in (26494, 9239, 23705,82));

    这个查询是分为下面两步的:
    a.首先是select * from business_opportunity 得到411198条结果,组成一个大的结果集。
    b.然后,上面的结果集的每一条记录将与子查询组成新的查询语句select boa.opportunity_id from business_opportunity_action boa where user_id in (26494, 9239, 23705,82) and   boa.opportunity_id= %bo.id%。这就相当于子查询要执行411198次。
    所以说子查询取决于外面的查询,导致查询时间很长,为3.69s。

    可以从show warnings 看出优化后的查询语句。

    从中可以看出优化的in,where <in_optimizer>(`meituancrm`.`bo`.`id`,<exists>(<index_lookup>(<cache>(`meituancrm`.`bo`.`id`) in business_opportunity_action on idx_opportunity_id_action_time where ((`meituancrm`.`boa`.`user_id` in (26494,9239,23705,82)) and (<cache>(`meituancrm`.`bo`.`id`) = `meituancrm`.`boa`.`opportunity_id`)))))
    注意其中and (<cache>(`meituancrm`.`bo`.`id`) = `meituancrm`.`boa`.`opportunity_id`))) 作为了where里面的条件,从而验证了上面的分析。


    优化策略:
    使用临时表,将bo表和临时表连接起来查。sql语句如下

    select bo.* from business_opportunity bo inner join (select distinct opportunity_id from business_opportunity_action where user_id in (26494, 9239, 23705,82)) boa on boa.opportunity_id = bo.id;

    show warnings的结果如下,可以看出没有对语句进行优化。

    语句的执行时间如下:

    ps.

    mysql有时候优化子查询很糟糕,特别是在In()子查询的时候
    通常情况下,我们希望由内到外,先完成子查询的结果,然后再用子查询来驱动外查询的表,完成查询。
    例如:
    select * from test where tid in(select fk_tid from sub_test where gid=10)
    通常我们会感性地认为该 sql 的执行顺序是:
    sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录,
    然后再到 test 中,带入 tid=2,3,4,5,6,取得查询数据。

    但是实际mysql的处理方式为:
    select * from test where exists (
    select * from sub_test where gid=10 and sub_test.fk_tid=test.tid
    )
    mysql 将会扫描 test 中所有数据,每条数据都将会传到子查询中与 sub_test 关联,子查询不会先被执行,所以如果 test 表很大的话,那么性能上将会出现问题。 
     
    mysql 不总是把子查询优化的很糟糕,上面只是特列,所系具体问题具体分析,多用explain。


     2.3 table

    显示操作的哪张表

     2.4 type

    连接使用了哪种类别以及是否使用了索引。
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    一般来说,得保证查询至少达到range级别,最好能达到ref。

    (1)System
    这是const联接类型的一个特例。表仅有一行满足条件。

    explain select * from (select * from user where id = 26494) a;

    (2)const
    表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数(可以参加下面show warnings的结果)(如何查出这些常数? TODO)。const表很快,因为它们只读取一次! const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。

    explain extended select * from user where id = 26494;

    show warnings结果

    (3)eq_ref
    对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。(简单select查询应该不会有这种type出现)

    explain extended select * from business_opportunity bo inner join business_opportunity_action boa on bo.id=boa.opportunity_id;

    (4)ref
    与 eq_ref 类似,区别在于不是使用 primary key 或者 unique index ,而是使用普通的索引
    ref出现在使用=或<=>(安全等于)操作符的带索引的列。
    1、简单查询,匹配索引值的是联合PRIMARY KEY或UNIQUE索引的前缀

    explain select * from bd_city_date where employeeid = 3 and datekey = 20140401;

    3、复杂查询,联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY

    explain select * from user inner join orgunit_district ora on user.org_id = ora.orgunit_id;

    (5)ref_or_null
     
    该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    在下面的例子中,MySQL可以使用ref_or_null联接来处理ref_tables:
    SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;

    (6)index_merge
    该联接类型表示使用了索引合并优化方法。

    当 and的时候用的联合主键索引
    or的时候没有去用联合主键索引,而是同时用了联合主键索引和datekey索引

    explain select * from bd_city_date where employeeid = 3 and datekey = 20140401;

    explain select * from bd_city_date where employeeid = 3 or datekey = 20140401;

     
    (7)unique_subquery(没理解什么意思,TODO
    该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列,其中第二行显示的是func,表明unique_subquery是一个函数,而不是一个普通的ref。

    explain select * from branch_user where branch_id in (select poi_id from poi_crm where source = 1);

    (8)index_subquery(没理解什么意思,TODO
    该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

    explain select * from bd_city_date where name in (select name from user where city_id = 62);

    (9)range
    只检索给定范围的行,使用一个索引来选择行。

    explain select * from user where id >26778;

    explain select * from user where name like "丁%";

    (10)index
    该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

    (为什么用的是city_id这个索引,主键是id? TODO)

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

     2.5 possible_keys

    possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
     

    explain的时候可能会出现possible_keys为有列,而key显示为NULL的情况。出现这种情况的原因是因为表中数据内容不多,查询语句需要查询全表,索引对查询帮助不大。如果出现这种情况,可以通过在查询语句中强制使用索引,来验证。(这个没有找到合适的例子,可以参考http://stackoverflow.com/questions/5719392/why-isnt-mysql-using-any-of-these-possible-keys

     
    如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

     2.6 key

    key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

     2.7 key_len

    key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
    使用的索引的长度。在不损失精确性的情况下,长度越短越好 

    一些示例值如下:

     key_len: 4 // INT NOT NULL
     key_len: 5 // INT NULL
     key_len: 30 // CHAR(30) NOT NULL
     key_len: 32 // VARCHAR(30) NOT NULL
     key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

    因为varchar是可变长度的,所以这2个字节用来存储字符串长度。同时,如果该字段是可以为null的话,那么索引也需要额外增加一个字节用来记录是否为null。

    索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来来做索引。

     2.8 ref

    指出对 key 列所选择的索引的查找方式,常见的值有 const, func, NULL, 具体字段名。

     2.9 rows

    rows列显示MySQL认为它执行查询时必须检查的行数。

    注意,explain是mysql分析你的sql不预估会扫描多少行,所以这个数字是不准确的。

    参见mysql官方文档

    rows

    The rows column indicates the number of rows MySQL believes it must examine to execute the query.

    For InnoDB tables, this number is an estimate, and may not always be exact.

    — http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows

     2.10 Extra

    (1).Distinct 
    一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

    explain extended select distinct ora.rank from orgunit_rank ora inner join orgunit_district od on ora.id= od.orgunit_id;

     

     
    (2).Using filesort 
    看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 
    此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行。
    会浏览整个user表,并保存所有的排序关键字job_number以及对应row的指针,然后排序关键字并指针去检索行。

    explain extended select * from user order by job_number;

     

     
    (3).Using index 
    列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 
    返回的内容只有索引的信息,没有读取实际的行。这是性能很高的一种表现。

     

    (4).Using temporary (需要优化)
    看到这个的时候,查询需要优化了。发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。

    用临时表来进行distinct

    explain select distinct org_name from user ;


    这个查询是using index,因为name有索引,不需要临时表来进行distinct操作。所以在遇到using temporary的时,首先能想到加索引来优化。

    explain select distinct name from user ;

     

    (5).Using where
    表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”(Post-filter)。所谓“后过滤”,就是先读取整行数据,再检查此行是否符合 where 句的条件,符合就留下,不符合便丢弃。因为检查是在读取行后才进行的,所以称为“后过滤”。


    当使用主键索引或unique key时 where 进行=匹配时,通过索引便能确定返回的行,无需进行“后过滤”,是不会显示using where的。


    3、explain用于sql优化经验谈

    TODO

    展开全文
  • EXPLAIN 解释 通过EXPLAIN来分析索引的有效性 EXPLAIN SELECT clause 获取查询执行计划信息,用来查看查询优化器如何执行查询 输出信息说明: 参考 ...

    EXPLAIN 解释

    • 通过EXPLAIN来分析索引的有效性
    • EXPLAIN SELECT clause
      获取查询执行计划信息,用来查看查询优化器如何执行查询
    • 输出信息说明:
      参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
    • id: 当前查询语句中,每个SELECT语句的编号
      复杂类型的查询有三种:
      简单子查询
      用于FROM中的子查询
      联合查询:UNION
      注意:UNION查询的分析结果会出现一个额外匿名临时表

    EXPLAIN

    • select_type:
      简单查询为SIMPLE
      复杂查询:
      SUBQUERY 简单子查询
      PRIMARY 最外面的SELECT
      DERIVED 用于FROM中的子查询
      UNION UNION语句的第一个之后的SELECT语句
      UNION RESULT 匿名临时表
    MariaDB [hellodb]> explain select name,age from students where age > (select avg(age) from students);
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    | id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    |    1 | PRIMARY     | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
    |    2 | SUBQUERY    | students | ALL  | NULL          | NULL | NULL    | NULL |   25 |             |
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    2 rows in set (0.00 sec)
    
    • table:SELECT语句关联到的表

    EXPLAIN

    • type:关联类型或访问类型,即MySQL决定的如何去查询表中的行的方式,以下顺序,性能从低到高
      • ALL: 全表扫描
      • index:根据索引的次序进行全表扫描;如果在Extra列出现“Using index”表示了使用覆盖索引,而非全表扫描
      • range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点
      • ref: 根据索引返回表中匹配某单个值的所有行
      • eq_ref:仅返回一个行,但与需要额外与某个参考值做比较
      • const, system: 直接返回单个行
    • possible_keys:查询可能会用到的索引
    • key: 查询中使用到的索引
    • key_len: 在索引使用的字节数

    EXPLAIN

    • ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
    • rows:MySQL估计为找所有的目标行而需要读取的行数
    • Extra:额外信息
      Using index:MySQL将会使用覆盖索引,以避免访问表
      Using where:MySQL服务器将在存储引擎检索后,再进行一次过滤
      Using temporary:MySQL对结果排序时会使用临时表
      Using filesort:对结果使用一个外部索引排序

    并发控制

    • 锁粒度:
      • 表级锁
      • 行级锁
    • 锁:
      • 读锁:共享锁,只读不可写,多个读互不阻塞
      • 写锁:独占锁,排它锁,一个写锁会阻塞其它读和它锁
    • 实现
      • 存储引擎:自行实现其锁策略和锁粒度
      • 服务器级:实现了锁,表级锁;用户可显式请求
    • 分类:
      • 隐式锁:由存储引擎自动施加锁
      • 显式锁:用户手动请求

    并发控制

    • 锁策略:在锁粒度及数据安全性寻求的平衡机制
    • 显式使用锁
      • LOCK TABLES 加锁(READ,WRITE)
    MariaDB [hellodb]> lock table students read;
    

    还能读可能有缓存,可修改服务器变量

    show variables like 'query_cache_wlock_invalidate';
    set query_cache_wlock_invalidate=ON;写到服务器选项里
    vim /etc/my.cnf
    [mysqld]
    query_cache_wlock_invalidate=ON
    
    - UNLOCK TABLES 解锁
    
    MariaDB [hellodb]> unlock tables;
    
    - FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
    

    关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁

    MariaDB [hellodb]> flush tables with read lock;
    
    - SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
    

    查询时加写或读锁

    备份

    温:读,不可写
    热:可读,可写
    冷:不可读,不可写

    事务

    • 事务Transactions:一组原子性的SQL语句,或一个独立工作单元

    • 事务日志:记录事务信息,实现undo,redo等故障恢复功能

    • ACID特性:

      • A:atomicity原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
      • C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态
      • I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
      • D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中

    事务

    • 启动事务:
      BEGIN
      BEGIN WORK
      START TRANSACTION

    • 结束事务:
      COMMIT:提交
      ROLLBACK: 回滚
      注意:只有事务型存储引擎中的DML语句方能支持此类操作

    • 自动提交:set autocommit={1|0} 默认为1,为0时设为非自动提交
      建议:显式请求和提交事务,而不要使用“自动提交”功能

    • 事务支持保存点:savepoint
      SAVEPOINT identifier
      ROLLBACK [WORK] TO [SAVEPOINT] identifier
      RELEASE SAVEPOINT identifier 删除事务点

    savepoint sp23 储存点( 可不写identifier)
    savepoint sp24 
    rollback to 24
    release sp24 撤销sp24
    

    事务隔离级别

    • 事务隔离级别:从上至下更加严格

      • READ UNCOMMITTED 可读取到未提交数据,产生脏读

      • READ COMMITTED 可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致

      • REPEATABLE READ 可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置

      • SERIALIZABILE 可串行化,未提交的读事务阻塞修改事务,或者未提交的修改事务阻塞读事务。导致并发性能差

    • MVCC: 多版本并发控制,和事务级别相关

    事务 - 指定事务隔离级别:

    • 服务器变量tx_isolation指定,默认为REPEATABLE-READ,可在GLOBAL和SESSION级进行设置

    SET tx_isolation=’’
    READ-UNCOMMITTED
    READ-COMMITTED
    REPEATABLE-READ
    SERIALIZABLE

    查看设置:show variables like ‘tx_isolation’

    • 服务器选项中指定
      vim /etc/my.cnf
      [mysqld]
      transaction-isolation=SERIALIZABLE

    并发控制

    • 死锁:
      两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态

    查看当前等待命令:show processlist\G
    终止等待命令(慎用,可能修改确实占用时间长):kill 2

    • 事务日志:保证数据的安全性,完整性
      事务日志的写入类型为“追加”,因此其操作为“顺序IO”;通常也被称为:预写式日志 write ahead logging
      事务日志文件: ib_logfile0, ib_logfile1

    优化思路:放在一个干净的分区中放事务日志。

    展开全文
  • explain详解

    千次阅读 2020-08-24 22:17:30
    使用explain可以模拟优化器执行sql语句,分析查询语句的结构,是否使用索引等等 使用方法: 在查询语句select关键字前面加上explain关键字,如下图的格式,然后就会返回分析的结果 下面来详解使用explain后返回的...

    explain是干嘛的?

    使用explain可以模拟优化器执行sql语句,分析查询语句的结构,是否使用索引等等

    使用方法:
    在查询语句select关键字前面加上explain关键字,如下图的格式,然后就会返回分析的结果

    在这里插入图片描述
    下面来详解使用explain后返回的数据字段的含义

    首先这是表结构:

    DROP TABLE IF EXISTS `actor`; 3 CREATE TABLE `actor` ( 4 `id` int(11) NOT NULL, 5 `name` varchar(45) DEFAULT NULL, 6 `update_time` datetime DEFAULT NULL, 7 PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 910 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18'); 1112 DROP TABLE IF EXISTS `film`; 13 CREATE TABLE `film` ( 14 `id` int(11) NOT NULL AUTO_INCREMENT, 15 `name` varchar(10) DEFAULT NULL, 16 PRIMARY KEY (`id`), 17 KEY `idx_name` (`name`) 18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1920 INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2'); 2122 DROP TABLE IF EXISTS `film_actor`; 23 CREATE TABLE `film_actor` ( 24 `id` int(11) NOT NULL, 25 `film_id` int(11) NOT NULL, 26 `actor_id` int(11) NOT NULL, 27 `remark` varchar(255) DEFAULT NULL, 28 PRIMARY KEY (`id`), 29 KEY `idx_film_actor_id` (`film_id`,`actor_id`) 30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 3132 INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
    
    1. id字段列:

    id有几个就代表这个sql语句被解析成多少个select. 其中id越大,代表执行的优先级别越高,id相等则从上到下依次执行,id为NULL的最后执行

     explain select * from film t1 left join film_actor t2 on t1.id=t2.film_id;
    

    在这里插入图片描述
    2.select_type字段列:代表查询的语句的复杂类型
    1).simple:简单查询
    2).primary:复合查询的最外层查询
    3).subquery:是select 后面的子查询

     explain select (select 1 from actor where id = 1);
    

    在这里插入图片描述

    4).derived是紧跟在from后面的子查询,MySQL会将结果存放在一个临时表中,也称为派生表
    在这里插入图片描述
    为什么这里查出来的是一个简单的语句呢?而不是想象中的derived类型呢,原因很简单,在mysql5.7以后,对派生表进行优化了,优化器引入derived_merge。当然我们也可以通过设置参数将派生表优化给关闭。

    set optimizer_switch='derived_merge=off'
    

    在这里插入图片描述
    5)union:紧跟union后面的select
    3.table字段列:表示访问的是哪一个表
    4.type列:表示MYSQL决定如何查找表中的行。
    他有七个级别,依次从最优到最差分别为:system>const>eq_red>red>range>index>ALL
    一般来说,最好达到ref级别,实在达不到,range级别也不差。
    1)NULL:当type为null时,表示查询语句在执行过程中被优化,不用访问表,直接从索引中得到值

    EXPLAIN SELECT min(id) from film;
    

    在这里插入图片描述
    2)const:表示对查询的部分进行优化,并将其转化为一个常量。用于primary key 或者unique key的所有列与常量比较时,所以表中只有一条记录,查询速度快。

    EXPLAIN SELECT *from film where id =1;
    

    在这里插入图片描述
    3)eq_ref:primary key 或者unique key 索引的所有部分被连接使用,最多只返回一条符合条件的记录。

     explain select * from film_actor left join film on film_actor.film_id = film.id;
    

    在这里插入图片描述
    4)ref:相比rq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行

    explain select * from film where name = 'film1';
    

    在这里插入图片描述
    5)range:范围扫描通常出现在in(),between,>,<,>=等操作中。

    explain select * from actor where id > 1;
    

    在这里插入图片描述
    6)index:扫描全索引,一般是扫描某个二级索引,她不会从索引根节点查找,而是直接对二级索引的叶子节点遍历和扫描。速度较慢。这种一般为使用了覆盖索引,二级索引一般比较小。(因为select * 这里面包含二级索引字段,所以就会走二级索引)

    explain select * from film;
    

    7)all:即全表扫描,扫描聚集索引下的所有叶子节点,因为select * 这里面的字段都没有建立除了聚集索引外的索引,所以就只会走聚集索引,如果不知道聚集索引的可以去看一下这篇博文(https://editor.csdn.net/md/?articleId=107474948),因为聚集索引叶子节点存了data数据多,所以效率就会低

    explain select * from actor;
    

    在这里插入图片描述
    5.possible_keys列
    表示该查询语句可能使用哪个索引,如果为NULL,则表明没有相关索引以供查询,这时候可以在where语句后面字段加个索引,看是否提高了性能
    6.key列
    表示实际用了哪个索引,如果possible_keys不为空,key为null,说明数据少,不需要走索引,直接全表扫描。
    7.key_len列
    这表示用到的索引字段的字节数

    计算规则如下:
    (当然,索引的最大长度为768,如果超过了这个长度,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索 引。)


    字符串 :

    • char(n):n字节长度
    • varchar(n):如果是utf-8,则长度 3n + 2 字节,加的2字节用来存储字符串长度

    数值类型:

    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节

    时间类型:

    • date:3字节
    • timestamp:4字节
    • datetime:8字节
    • 如果字段允许为 NULL,需要1字节记录是否为 NULL

    8. ref列
    这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)
    9. rows列
    这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。
    10. Extra列
    这一列展示的是额外信息。常见的重要值如下
    1)Using index:使用覆盖索引 覆盖索引定义:如果select后面查询的字段都可以从这个索引的树中 获取,这种情况一般可以说是用到了覆盖索引,extra里一般都有using index;覆盖索引一般针对的是辅助索引,整个 查询结果只通过辅助索引就能拿到结果,不需要通过辅助索引树找到主键
    在这里插入图片描述
    2)Using where:where后面查询的字段没有建立索引,使用 where 语句来处理结果
    在这里插入图片描述
    3)Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围;
    在这里插入图片描述
    4)Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索 引来优化
    5)Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
    6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段是

    展开全文
  • Explain详解

    千次阅读 2020-02-01 16:05:56
    二、explain 的两种使用方式 三、explain中的列 1. id列 2. select_type列 3. table列 4. type列 5. possible_keys列 6. key列 7. key_len列 8. ref列 9. rows列 10. Extra列 四、索引最佳实践 使用...
  • explain语句

    2019-11-15 20:26:40
    explain(解释)语句 使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句的,这可以帮你分析你的查询语句或是表结构的性能瓶颈,通过explain命令可以得到: 1.表的读取顺序 2.数据读取...
  • Type级别详解 system>const>eq_ref>ref>range>inex>ALL 越往左边,性能越高,比如system就比ALL类型性能要高出许多,其中system、const只是理想类型,基本达不到; 我们自己实际能优化到ref>...
  • Explain 详解

    2021-03-25 10:30:06
    Explain工具介绍 使用 Explain 关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈。在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而...
  • SQL优化 MySQL版 -分析explain SQL执行计划与Type级别详解【全干货】 type索引类型、类型 【转载,作者 : Stanley 罗昊,来源:http://www.likecs.com/default/index/show?id=52490】 注:看此文章前,需要有一定的...
  • mysql explain

    2018-11-12 14:25:00
    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。...
  • Explain 关键字

    2021-04-18 10:15:59
    explain关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下:| 一、ID 参数 select 查询的序列号,...
  • MySQL 之 Explain 输出分析背景前面的文章写过 MySQL 的事务和锁,这篇文章我们来聊聊 MySQL 的 Explain,估计大家在工作或者面试中多多少少都会接触过这个。可能工作中实际使用的不多,但是不论的自己学习还是面试...
  • MySQL EXPLAIN

    2019-03-13 10:23:00
    MySQL explain结果字段解释 在执行select语句前 加上 explain,可以获取对这条sql语句的解析,根据解析的内容,我们可以更清晰的了解sql的具体执行情况,从而可以有针对性的去优化。 1. id id是执行本条select语句的...
  • sql之Explain

    2019-05-16 17:18:59
    Explain Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。 ...
  • EXPLAIN用法解析

    千次阅读 2019-11-27 15:33:52
    1. EXPLAIN简介 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 ➤ 通过EXPLAIN,我们可以分析出以下结果: 表的读取顺序 数据...
  • Explain 输出分析

    2019-06-13 18:44:01
    MySQL 之 Explain 输出分析 也可以使用EXPLAIN EXTENDED命令,执行完后再执行SHOW WARNINGS查看到警告信息(进一步分析) 背景 前面的文章写过 MySQL 的事务和锁,这篇文章我们来聊聊 MySQL 的 Explain,估计大家...
  • mysql explain笔记

    2019-12-04 15:18:47
    一、explain是什么 根据表,列,索引以及where中的条件,mysql优化器底层使用了许多相关的优化技术来进行sql查询的最优化,使之单表查询时不用全表扫描,多表连接时,无需进行逐个比较等。 mysql优化器选择最优...
  • mysql 用法 Explain

    万次阅读 多人点赞 2019-06-04 10:05:29
    前言:explain(执行计划),使用explain关键字可以模拟优化器执行sql查询语句,从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。 注:本系列随笔如无特殊说明都MySQL版本都为...
  • Mysql explain详解

    2021-03-17 20:47:22
    mysql Explain详解 explain声明 DESCRIBE和EXPLAIN语句是同义词。实际上,DESCRIBE关键字通常用于获取有关表结构的信息,而EXPLAIN用于获取查询执行计划(即,有关MySQL如何执行查询的说明) EXPLAIN语句提供...
  • MySQL之Explain

    2020-08-05 02:10:24
    Explain是什么 Explain能做什么 Explain怎么用 Explain名词解释 id Explain是什么 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 29,638
精华内容 11,855
关键字:

explain级别