MySQL笔记

猛男奥特曼
120 阅读4分钟

MySQL笔记

仅对InnoDB

遇到的面试题记录：
1.SQL查询为什么会变慢？
2.为什么查询语句中查询条件带有索引列，但查询数据库时实际走的索引和我们预期的不一致？
3.in会使索引失效吗？
......

熟悉MySQL的人会知道，在MySQL Server中包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。事实上，我们所写的SQL会被MySQL底层的查询优化器进行改写，也就是当SQL通过解析器语法检查后，会先将其交由查询优化器转化成执行计划，然后才与存储引擎交互。

说白了对于开发人员来说，我们关注的执行计划无非就是SQL语句在数据库中是如何扫描表、如何使用索引的，而这些问题恰恰是会影响到查询效率。一个SQL在系统前期数据量没上来的时候，全表扫描、不用索引可能查询消耗的时间也并不多，但是随着数据量愈发庞大，直接寄！

以上都是废话。

开启慢查询

set global slow_query_log = [ON | OFF]  ##默认OFF 启用ON  show variables like 'slow_query_log'
set global slow_query_log_file = /sql_log/slowlog.log ##指定慢查询文件位置 ps：需要注意写权限
set global long_query_time = ？  ## 可以为0：记录所有  
set global log_queries_not_using_indexes = [ON | OFF]  ##默认OFF 记录没走索引的

long_query_time 设置时间太小记录log特别多话，可以通过工具分析

  • mysqldumpslow [OPTS...][LOGS...] ##mysql提供的工具
  • pt-query-digest [OPTIONS][FILES][DSN] ##个人感觉更好用 percona安装笔记在后面

除了慢查询日志可以看，通过PROCESSLIST也可以，监控长时间运行的sql 。ps：注意权限问题

select id,'uset','host',DB,command,'time',state,info
FROM information_schema.PROCESSLIST where TIME >= ?

EXPLAIN

EXPLAIN
    {explainable_stmt  |  for connecton connection_id}   ## connection_id 是PROCESSLIST中查询的sql连接的id

image.png

id

  • ID表示查询执行的顺序
  • ID相同时由上到下执行
  • ID不同，由大到小执行

select_type

常见的有以下 image.png 此外还有

UNION：表示此查询是UNION的第二个或后续的查询

DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果

UNION RESULT：UNION的结果。

table

表名，如果查询取别名此处则显示别名;可能也会显示查询union产生的结果集或者临时表；

例如<unionM,N>,则表示ID为M,N查询union产生的结果集。

type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

ALL：表示全表扫描，性能最差。

index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

range：表示使用索引范围查询。使用>、>=、<、<=、in等等。

ref：表示使用非唯一索引进行单值查询。

eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。

const：表示使用主键或唯一索引做等值查询，常量查询。

NULL：表示不用访问表，速度最快。

image.png possible_keys

表示查询时能够使用到的索引，显示的是索引名称。注意：并不一定是真正使用

key

表示查询时真正使用到的索引，显示的是索引名称。

rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。关联查询中，rows还表示内嵌循环的次数。

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。注意：ken_len是由表中定义的字段（如：INT、FLOAT：4个字节；varchar(n)：n * 字符集长度 + 2字节）长度计算的，因此在最好在建表的时候设计合适的长度。

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下

image.png

索引

索引的作用无非就是利用它快速的查找到所要的数据。它是在MySQL存储引擎层实现的。

在Innodb中，索引是B+树结构（特点：平衡二叉树，叶子节点到跟节点距离相同，叶子节点通过指针连接（双向循环）等，详细见数据结构笔记）；适用于全值匹配的查询和范围查询。

B+树索引从索引的最左侧列开始匹配查找列，例如有复合索引index_A_B，‘where a = '10'’ 走索引 ，而‘where b > '300'’ 并不会走索引。需要注意的一点: ‘where b > '300' and a = '10' ’ 虽然where过滤条件顺序和符合索引相反，但是仍然可以使用索引的，因为MySQL优化器可以调整过滤条件的顺序以适应索引。

在MySQL中并不是说in列表查询不会用到索引，它可以使用索引，但是当in列表中的值过多时，优化器认为全表扫描优于使用索引，从而不会使用索引。

索引不能乱建，建立索引会对增删改操作有影响。

通常情况下，建立索引以下考虑：

  • 在where字句中的列，同时此列具有筛选性（字段值重复性底），像性别列这种除了男就是女，不具备好的筛选性没必要建立索引。
  • 在order by 、 group by 、 distinct中的字段，可以提高排序性能，避免排序过程产生临时表。注意索引顺序要与order by 字段顺序保持一致 ，升序降序也得一样。
  • 关联查询时，关联列建立索引。
  • 对于复合索引
    • 区分度最高的列放在联合索引的最左侧
    • 使用最频繁的列放在联合索引的最左侧
    • 尽量把长度小的列放在联合索引的最左侧

索引失效

  • 使用索引所命中的数据占了全表中的大部分数据，MySQL查询优化器可能会认为全表扫描性能更高就不会使用索引。
  • 使用受限的Btree索引，也不会使用索引 。
    Btree索引的限制
  • 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键。如 index_A_B_C ,where a='10' and c > '20' 索引失效
  • Not in 和 <> 操作无法使用索引
  • 在索引列上使用计算或者函数，索引失效

索引使用的误区

  • 索引不是越多越好，索引过多不仅会影响到增删改效率，还会影响查询优化器生成最优的执行计划的时间。

  • 使用in列表是可以用到索引的（or可能用不到索引），但是in列表中的值非常多，多到已经可以把表中大部分数据都查询出来时，MySQL会认为全表扫描优于in，从而不使用索引。

  • SQL语句中过滤条件不用必须与索引键相同才可以使用到索引，因为优化器会自动调整过滤条件，从而使用索引。

SQL改写

  1. 使用外关联outer join方式代替Not in 和 <> 操作

  2. 使用CTE代替子查询。（都是临时表）

  3. 拆分复杂大SQL为多个简单小SQL分别计算。（MySQL貌似不支持并发查询，一个SQL只能用一个CPU的核心。8.0.5貌似有，下次再看看。）

  4. 使用计算列代替使用函数进行查询的列。（MySQL5.7以后貌似才支持计算列哈）

avatar
文章
阅读
粉丝