从一条SQL是如何执行的到删库跑路了怎么办

最新推荐文章于 2022-04-20 11:21:40 发布
最新推荐文章于 2022-04-20 11:21:40 发布
阅读量214 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39151085/article/details/117351409
版权

一条查询SQL语句是如何执行的?

在这里插入图片描述

1、连接

默认的端口是3306，客户端连接服务端的方式有很多。

可以是同步的也可以是异步的，可以是长连接也可以是短连接,可以是TCP也可以是Unix Socket, MySQL有专门处理连接的模块，连接的时候需要验证权限。

怎么查看有多少个连接

SHOW GLOBAL STATUS LIKE 'Thread%'
字段含义
Threads_cached缓存中的线程连接数
Threads_connected当前打开的连接数
Threads_created为处理连接创建的线程数
Threads_running非睡眠状态的连接数，通常指并发连接数

参数级别说明:

MySQL中的参数(变量)分为session和global级别，分别是在当前会话中生效和全局生效。

所以有些通过sql语句修改的内容，重启服务后会自动改回默认值。

客户端每产生一个连接或者一个会话， 在服务端就会创建一个线程来处理。 反过来,如果要杀死会话，就是Kill线程。

既然是分配线程的话,保持连接肯定会消耗服务端的资源。MySQL会把那些长时间不活动的(SLEEP) 连接自动断开。

2、查询缓存

MySQL内部自带了一个缓存模块。

有一张2000万行数据的表，没有索引，如果我两次执行一模一样的 SQL语句，第二次会不会变得很快?

show variables like 'query_cache%';

MySQL自带的缓存的应用场景有限，第一个是它要求SQL语句必须一模一样，中间多个空格，字母大小写不同都被认为是不同的的SQL.

第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

所以缓存这块，我们还是交给ORM框架(比如MyBatis默认开启了一-级缓存) ，或者独立的缓存服务，比如Redis来处理更合适。

在MySQL 8.0中，查询缓存已经被移除了。

3、词法解析和预处理器

为什么一条 SQL语句能够被识别呢?

假如随便执行一个字符串,服务器报了一个1064的错，它是怎么知道我输入的内容是错误的?

这个就是MySQL的解析器和预处理模块。

这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。

3.1词法解析

你说的是中国话写的是中文我才能懂！

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

比如一个简单的SQL语句:

select name from user_innodb where id = 1

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

所以在回话窗中输入多条语句，点击运行是会报错 因为没加分号，会认为是一句话

select name from user_innodb where id = 1
select name from user_innodb where id = 2
select name from user_innodb where id = 3

在这里插入图片描述

3.2语法解析

第二步就是语法分析，语法分析会对SQL做一些语法检查,比如单引号有没有闭合,然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树

关键字字段关键字表关键字条件
selectuserfromuserwhere

img

3.3预处理器

问题:如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在，会在哪里报错?是在数据库的执行层还是解析器?

实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。

它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

4、查询优化器

得到解析树之后，是不是执行SQL语句了呢?

这里我们有一个问题，一条SQL语句是不是只有一种执行方式? 或者说数据库最终执行的SQL是不是就是我们发送的SQL?

这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?

这个就是MySQL的查询优化器的模块 。

查询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan)，然后选择一种最优的执行计划，MySQL 里面使用的是基于开销(cost) 的优化器，那种执行计划开销最小，就用哪种。

MySQL的优化器能处理哪些优化类型呢?

举两个简单的例子:

1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。

2、有多个索引可以使用的时候，选择哪个索引。

实际上，对于每一引种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。

但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写SQL语句的时候还是要注意。

优化完之后，得到一一个什么东西呢?优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。

explain select name from user where id = 1

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

explain FORMAT=JSON select name from user where id = 1

5、存储引擎

比如这两张表，都是二维的存储结构，表面上看起来是一模一 样的。

在这里插入图片描述

但是他们的表类型是不一样的。

在这里插入图片描述

innodb：

CREATE TABLE `user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8mb4;

myisam：

CREATE TABLE `user_myisam` (
  `id` bigint(64) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这里我们有几个问题:

1、表类型是怎么选择的? 可以修改吗?

2、MySQL为什么支持这么多存储引擎呢?一种还不够用吗?

3、这些不同的存储引擎,到底有什么区别?

试想一下：

如果我有一张表，需要很高的访问速度，而不需要考虑持久化的问题，是不是要把数据放在内存?

如果一张表，是用来做历史数据存档的，不需要修改，也不需要索引，那它是不是要支持数据的压缩?

如果一张表用在读写并发很多的业务中，是不是要支持读写不干扰，而且要保证比较高的数据一致性呢?

功能MylSAMMEMORYInnoDBArchive
存储限制256TBRAM64TBNone
支持事务NoNoYesNo
支持全文索引YesNoNoNo
支持树索引YesYesYesNo
支持哈希索引NoYesNoNo
支持数据缓存NoN/AYesNo
支持外键NoNoYesNo

MYISAM：

应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。

特点:

支持表级别的锁(插入和更新会锁表)。不支持事务。

拥有较高的插入(insert) 和查询(select) 速度。

存储了表的行数(count 速度更快)。

适合:

只读之类的数据分析的项目。

InnoDB :

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

特点:

支持事务，支持外键，因此数据的完整性、一致性更高。

支持行级别的锁和表级别的锁。

支持读写并发，写不阻塞读(MVCC)。

特殊的索引存放方式，可以减少I0,提升查询效率。

适合:

经常更新的表，存在并发读写或者有事务处理的业务系统。

如果对数据一致性要求比较高,需要事务支持，可以选择InnoDB。

如果数据查询多更新少，对查询性能要求比较高，可以选择MylSAM。

如果需要一个用于查询的临时表，可以选择Memory。

如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎

6、执行引擎

存储引擎分析完了，它是我们存储数据的形式，那是谁使用执行计划去操作存储引擎呢?

这就是我们的执行引擎，它利用存储引擎提供的相应的API来完成操作。

为什么我们修改了表的存储引擎，操作方式不需要做任何改变?

因为不同功能的存储引擎实现的API是相同的。最后把数据返回给客户端。

为什么能支持这么多存储引擎，还能自定义存储引擎，表的存储引擎改了对Server访问没有任何影响，就是因为大家都遵循了一定了规范，提供了相同的操作接口。

每个存储引擎都有自己的服务。

show engine innodb status;

这些存储引擎用不同的方式管理数据文件，提供不同的特性，但是为上层提供相同的接口。

MYSQL架构总结

在这里插入图片描述

1、连接层

我们的客户端要连接到MySQL服务器3306端口,必须要跟服务端建立连接,那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。

2、服务层

连接层会把SQL语句交给服务层，这里面又包含一系列的流程 :

比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析(比如关键字怎么识别，别名怎么识别，语法有没有错误等等)。

然后就是优化器，MySQL 底层会根据一定的规则对我们的SQL语句进行优化，最后再交给执行器去执行。

3、存储引擎

存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储引擎。

再往下就是内存或者磁盘。

InnoDB

在这里插入图片描述

这里有一个问题,操作数据的时候,每次都要从磁盘读取到内存(再返回给Server),

有没有什么办法可以提高效率?

1、缓冲池（Buffer Pool）

还是缓存的思想。把读取过的数据页缓存起来。

InnoDB设计了一一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。

这个内存区域有个专属的名字，叫Buffer Pool。

在这里插入图片描述

修改数据的时候，也是先写入到buffer pool,而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候， 我们把它叫做脏页。那脏页什么时候才同步到磁盘呢?

InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘,每隔一段时间就

一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

总结一下:

Buffer Pool的作用是为了提高读写的效率。

Buffer Pool默认大小是128M (134217728 字节)，可以调整。

查看系统变量:

SHOW VARIABLES like "%innodb_buffer_pool%';

查看服务器状态，里面有很多跟Buffer Pool相关的信息:

SHOW STATUS LIKE '%innodb_buffer_pool%';

设置bufferpool大小 单位是字节 Session级别

SET GLOBAL innodb_buffer_pool_size=size_in_bytes

如果mysql单机部署，一般设置为内存大小的 40-80%

内存的缓冲池写满了怎么办?

InnoDB 用LRU算法来管理缓冲池(链表实现,不是传统的LRU,分成了young和old)，经过淘汰的数据就是热点数据。

2、BinLog

binlog以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是数据值，属于逻辑日志)，可以用来做主从复制和数据恢复。

跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。

binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog,然后执行一遍。

在这里插入图片描述

2.1、binlog 配置

做数据恢复的前提是必须有全量的备份！然后通过binlog日志进行恢复

binlog 默认是不开启的，需要在服务端手动配置。注意有一定的性能损耗。

是否开启 binlog

show variables like 'log_bin%';

2.2、binlog 格式

查看 binlog 格式：

show global variables like '%binlog_format%';

STATEMENT：记录每一条修改数据的 SQL 语句（减少日志量，节约 IO）。

ROW：记录哪条数据被修改了，修改成什么样子了（5.7 以后默认）。

MIXED：结合两种方式，一般的语句用 STATEMENT，函数之类的用 ROW。

Binlog 文件超过一定大小就会产生一个新的，查看 binlog 列表：

show binary logs;

大小：

show variables like 'max_binlog_size';

查看 binlog 内容

show binlog events in 'mysql-bin.000001';

附：MySQL使用binlog日志做数据恢复

https://blog.csdn.net/king_kgh/article/details/74890381

索引

在这里插入图片描述

第一个是索引的名称，第二个是索引的列,比如我们是要对id创建索引还是对name创建索引。后面两个很重要, -个叫索引类型。

在InnoDB里面，索引类型有三种，普通索引、唯- -索引(主键索引是特殊的唯一索引)、全文索引。

普通(Normal) :也叫非唯一索引， 是最普通的索引，没有任何的限制。

唯一(Unique) :唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引, 它还多了一个限制条件,要求键值不能为空。主键索引用primay key创建。

全文(Fulltext) :针对比较大的数据，比如我们存放的是消息内容、一篇文章， 有几KB的数据的这种情况，如果要解决like查询在全文匹配的时候效率低的问题，可以创建

1、索引使用原则

我们容易有以一个误区，就是在经常使用的查询条件上都建立索引，索引肯定不是越多越好

2、离散度

c o u n t ( d i s t i n c t ( c o l u m n n a m e ) ) : c o u n t ( ∗ ) count(distinct(column_ name)) : count(*) count(distinct(columnn​ame)):count(∗)
列的全部不同值和所有数据行的比例。

数据行数相同的情况下，分子越大，列的离散度就越高。

按照这个定义，name的离散度更高，还是gender的离散度更高?

在这里插入图片描述

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

所以不建议大家在离散度低的字段上建立索引。

3、联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。

比如我们在user表上面，给name和phone建立了一个联合索引。

ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

在这里插入图片描述

咕泡学员串一串同学-联合索引桥

那么问题来了下列四条语句，那条用到了联合索引

EXPLAIN SELECT * FROM user_innodb WHERE name='公良祸' AND phone ='15006237319';

EXPLAIN SELECT * FROM user_innodb WHERE name='公良祸'

EXPLAIN SELECT * FROM user_innodb WHERE phone ='15006237319';

EXPLAIN SELECT * FROM user_innodb WHERE phone ='15006237319' and name='公良祸';

4、什么时候用不到索引?

1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式计算(±*/) :

explain SELECT * FROM 't2' where id+1 = 4;

2、字符串不加引号,出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain SELECT * FROM user_innodb where name= 136;
explain SELECT * FROM user_innodb where name = '136';

3、like 条件中前面带%

where条件中like abc%、 like %2673%、like %888都用不到索引吗?为什么?

explain select * from user_innodb where name like 'wang%';
explain select * from user_innodb where name like "%wang';

讨滤的开销大大、这个时候可以 用全文索引

4、负向查询

NOT LIKE不能:

explain select * from employees where last_name not like 'wang'

!= (<>)和NOT IN在某些情况下可以:

explain select * from employees where emp_no not in (1)
explain select * from employees where emp_no <> 1

注意跟数据库版本、数据量、数据选择度都有关系。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?

基于cost开销(Cost Base Optimizer) ,它不是基于规则(Rule-Based Optimizer),也不是基于语义。怎么样开销小就怎么来。

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引， 什么情况一定不用索引的规则。

优化总结

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的流程又是由很多个环节组成的，每个环节都会消耗时间。

要减少查询所消耗的时间，就要从每一个环节入手。

1、连接

第一个环节是客户端连接到服务端，连接这-块有 可能会出现什么样的性能问题?

有可能是服务端连接数不够导致应用程序获取不到连接。

比如我们遇到过的Mysql: error 1040: Too many connections的错误。这个是超过了服务端设置的最大并发连接数。

我们可以从两个方面来解决连接数不够的问题:

1、从服务端来说，我们可以增加服务端的可用连接数。

如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以:

(1) 增加可用连接数，修改max_ connections的大小:

SHOW VARIABLES LIKE 'max_connections'

(2)或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。

show global variables like 'wait_timeout'; -- 非交互式超时时间，如JDBC程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具

及时释放不活动的连接，注意不要释放连接池还在使用的连接

2、从客户端来说，可以减少从服务端获取的连接数。如果我们想要不是每一次执行SQL都创建一个新的连接， 应该怎么做?

这个时候我们可以引入连接池，实现连接的重用。

常见的数据库连接池有老牌的DBCP和C3P0、阿里的Druid、Hikari (Spring Boot2.x版本默认的连接池)。

2、缓存

在系统里面有一些很慢的查询，要么是数据量大，要么是关联的表多，要么是计算逻辑非常复杂，这样的查询每次会占用连接很长的时间。

所以为了减轻数据库的压力，和提升查询效率,我们可以把数据放到内存缓存起来,比如使用Redis。

缓存适用于实时性不是特别高的业务，例如报表数据，一次查询要 2分钟，但是一天只需要更新一次。

3、解析器

解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。

4、优化器

优化器的作用就是对我们的SQL语句进行优化分析，生成执行计划。

问题:在我们做项目的时候，我们的服务层每天执行了这么多SQL语句，它怎么知道哪些SQL语句比较慢呢?

第一步，我们要把SQL执行情况记录下来，用到服务端的慢查询日志。

4.1慢日志

因为开启慢查询日志是有代价的(跟binlog一样)，所以它默认是关闭的:

show variables like 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variables like '%long_query%';
4.1.1修改配置：

SESSION级别（重启失效）

set @@global.slow_query_log=l; -- 1开启，0关闭，重启后失效
set @@global.long_query_ftime=3; -- 默认10秒，另开一个窗口后才会查到最新值

my.ini

slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/localhost-slow.log
4.1.2日志分析

在这里插入图片描述

MySQL提供了mysqldumpslow的工具，在MySQL的bin目录下。

mysqldumpslow常用参数:

-s 排序方式 后面跟排序列

al 平均锁定时间

ar 平均返回记录时间

at 平均查询时间（默认）

c 计数 l 锁定时间

r 返回记录

t 查询时间

-r 反向排序，最大的在最后

-t 后面跟数字，返回行数

-a 不将所有的数字抽象为N，字符串抽象为S

-n 后面跟数字，在名称中至少有n个数字抽象为数字 -g 正则后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s t-t 10 -g 'select' /var/lib/mysql/localhost-slow.log

Reading mysql slow query log from /var/lib/mysql/192-slow.log
Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[192.168.0.1]
  SELECT trigger_name, event_manipulation, event_object_table, action_statement, action_timing, DEFINER FROM information_schema.triggers WHERE BINARY event_object_schema='S' AND BINARY event_object_table='S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=3.0 (6), root[root]@[192.168.0.1]
  SELECT * FROM `information_schema`.`columns` WHERE TABLE_NAME='S' AND TABLE_SCHEMA='S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[192.168.0.1]
  SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA LIKE 'S' AND NOT ISNULL(PARTITION_NAME) AND TABLE_NAME LIKE 'S' ORDER BY TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.0.1]
  SELECT * FROM `t3` LIMIT N, N

Count: 1  Time=3.01s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)

Count代表这个SQL执行了多少次；

Time代表执行的时间，括号里面是累计时间;

Lock表示锁定的时间，括号是累计;

Rows表示返回的记录数，括号是累计。

4.2EXPLAIN 执行计划

在任何执行语句前加上 explain 关键字 就能看到执行计划

在这里插入图片描述

4.2.1 id

id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号。

id值不同:

id值不同的时候，先查询id值大的(先大后小)。

在这里插入图片描述

id值相同:

id值相同的时候，表的查询顺序是从上往下顺序执行。

在这里插入图片描述

在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表。 小表驱动大表的思想

4.2.2 select_type

示查询中每个select子句的类型

SIMPLE

简单SELECT，不使用UNION或子查询等

PRIMARY

子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY

UNION

UNION中的第二个或后面的SELECT语句

DEPENDENT UNION

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

UNION RESULT

UNION的结果，union语句中第二个select开始后面所有select

SUBQUERY

子查询中的第一个SELECT，结果不依赖于外部查询

DEPENDENT SUBQUERY

子查询中的第一个SELECT，依赖于外部查询

DERIVED

派生表的SELECT, FROM子句的子查询

UNCACHEABLE SUBQUERY

一个子查询的结果不能被缓存，必须重新评估外链接的第一行

4.2.3table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

4.2.4 type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system（从左到右，性能从差到好）

system

system是const的一种特例，只有一行满足条件, 对于MyISAM、Memory的表,只查询到一条记录，也是system。

const

主键索引或者唯一索引，只能查到一条数据的SQL。

eq_ ref

通常出现在多表的join 查询，被驱动表通过唯一性索引 (UNIQUE或PRIMARYKEY)进行访问，此时被驱动表的访问方式就是eq_ ref

是除const之外最好的访问类型。

小结:

以上三种system, const, eq_ ref, 都是可遇而不可求的，基本上很难优化到这个状态。

ref

查询用到了非唯一-性索引， 或者关联操作只使用了索引的最左前缀。

range

索引范围扫描。

如果where后面是between and 或<或>或>=或<=或in这些，type类型就为range。

index

Full Index Scan，查询全部索引中的数据(比不走索引要快)。

all

Full Table Scan,如果没有索引或者没有用到索引，type 就是ALL。代表全表扫描。

小结:

一般来说， 需要保证查询的type至少达到range级别，最好能达到ref.

ALL (全表扫描)和index (查询全部索引)都是需要优化的。

4.2.5 possible_ key、 key

可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。

4.2.6 key_ len

索引的长度(使用的字节数)。跟索引字段的类型、长度有关。

不损失精确性的情况下，长度越短越好

4.2.7 filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。

4.2.8 Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where

不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort

当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 需要优化

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where

这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used

Query语句中使用from dual 或不含任何from子句

4.3 optimizer trace

如果觉得EXPLAIN还不够详细，可以用开启optimizer trace.

SHOW VARIABLES LIKE 'optimizer trace';
set optimizer_trace='enabled=on';
select * from information schema optimizer trace\G

4.4总结:

模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。

通过这种方式我们可以分析语句或者表的性能瓶颈。

分析出问题之后，就是对SQL语句的具体优化。

优化案例

在这里插入图片描述

当然肯定不是这个

1、服务端状态分析:

如果出现连接变慢，查询被阻塞，无法获取连接的情况。

1、 重启!

2、 show processlist查看线程状态，连接数数量、连接时间、状态

3、分查看锁的状态

4、 kill有问题的线程

2、分析查询基本情况

涉及到的表的表结构，字段的索引情况、每张表的数据量、查询的业务含义。

这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计是有问题的。

3、找出慢的原因

1、查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫描行数等信息。

2、如果总体的时间很长，不确定哪一个因素 影响最大，通过条件的增减，顺序的调整，找出引起查询慢的主要原因，不断地尝试验证。

找到原因

比如是没有走索引引起的，还是关联查询引起的，还是order by引起的。

找到原因之后:

4、对症下药

1、创建索引或者联合索引

2、改写SQL,这里需要平时积累经验，例如:

1)使用小表驱动大表

2)用join来代替子查询

  1. or 改成union

4)如果结果集允许重复的话，使用UNION ALL代替UNION

如果SQL本身解决不了了，就要上升到表结构和架构了。

3、表结构(冗余、拆分、not null等) 、架构优化(缓存读写分离分库分表)。

4、业务层优化

https://blog.csdn.net/chenshun123/article/details/79677037 MySQL 架构及优化原理 详解

https://gper.club/articles/7e7e7f7ff4g5agc2g66 赶集网mysql开发36军规

https://gper.club/articles/7e7e7f7ff4g5agc2g67 58到家MySQL军规升级版

https://www.cnblogs.com/joeyJss/p/11096597.html Sql优化总结

小风嗖嗖的吹
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
数据库入门到删库到跑路 (塑料篇)
yzl123kk的博客
11-23 1037
命令 启动-关闭-远程链接-退出-跳过开始权限-修改密码 \#启动数据库 net start mysql \#关闭数据库 net stop mysql \#远程链接数据库 mysql -h 主机地址 -u 用户名 -p 用户密码 mysql -u root -stop /* 连接与断开服务器 */ mysql -h 地址 -P 端口 -u 用户名 -p 密码 \#退出数据库 exit; quit; \q; /* 跳过权限验证登录MySQL */ mysqld -
Sqlserver防止删库.txt
02-17
SQLSERVER的操作都是不可逆的，所以为了避免误操作的删库和删表，完成了这个文档，虽然简单，但是实用。
从删库到跑路
我思故我在
08-15 589
今天写写数据误删怎么办 = = 一 预防 大于 恢复 删了之后怎么恢复数据，那都是事后操作，在事情发生前，如何避免问题发生，用较少的资源合理有效避免降低风险才是王道，下面聊聊如何避免 误删数据。 1 权限 一般情况下，操作线上数据的所有入口都会有权限，一般都会给开发人员线上查询的权限，像这种的，就完全杜绝删除更改数据操作语句，做上限制，不给更改数据的权限。 如果要修复线上数据，一般都是有审批流的（其实一般审核的都不看），所以也要做限制，sql_safe_updates 参数设...
mysql从删库到跑路 Ubuntu篇
水金的博客
07-12 1181
sudo apt purge mysql-* sudo rm -rf /etc/mysql/ /var/lib/mysql sudo apt autoremove sudo apt autoclean删除mysql的配置文件sudo rm /etc/mysql/ -R自动卸载mysql（包括server和client）sudo apt-get autoremove mysql* --purge ...
从删库到跑路(1)
weixin_43934507的博客
12-03 783
** 数据库学习 1- MySQL简介 是一个数据库管理系统 MySQL数据库是关系型的： 关系数据库将数据存储在单独的表中，而不是将所有数据放在一个大的库房中。逻辑模型具有数据库，表，视图，行和列等对象，可提供灵活的编程环境。您可以设置管理不同数据字段之间关系的规则，例如一对一，一对多，唯一，必需或可选，以及 不同表之间的“ 指针 ”。 MySQL软件是开源的 2- 数据库语言 DDL：...
sqlserver如何删库跑路
weixin_43161923的博客
01-25 295
USE master --设置当前数据库为master，以便访问sysdatabases表 GO IF EXISTS (SELECT * FROM sysdatabases WHERE name ='myData') DROP DATABASE myData
Java执行SQL脚本文件到数据库详解
08-30
Java执行SQL脚本文件到数据库详解是指将SQL脚本文件执行到数据库中的一种方法，该方法可以将SQL脚本文件中的SQL语句执行到数据库中，实现数据的增删改查等操作。本文将从两个方面介绍Java执行SQL脚本文件到数据库的...
Oracle批处理：使用C# 自带Oracle驱动一次执行多条Sql语句
03-24
本文将深入探讨如何在C#中利用Oracle自带的驱动来实现批处理，以一次执行多条SQL语句。 首先，我们需要了解Oracle的数据驱动，即ODP.NET（Oracle Data Provider for .NET）。这是Oracle公司为.NET开发者提供的一个...
C#Winform 增删改查 SqlServer（附数据库）
05-30
在本文中，我们将深入探讨如何使用C# Winform与Sql Server数据库进行交互，实现增删改查功能。这个系统特别适合初学者学习，通过Visual Studio 2017开发环境，我们可以构建一个用户友好的图形界面，以方便地管理学生...
Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载（一）
05-11
Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载（一） http://download.csdn.net/source/3268267 Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载（二） ...
MySQL从删库到跑路（2）：大爷的SQL私房菜
katyusha1的博客
05-16 521
夜色如墨，月凉如水，一轮皎洁的圆月高高地挂在夜空之上，平日里鼾声如雷的室友今夜也停止了打鼾，如此静谧的夜晚，李有为却辗转难眠。 时间悄然来到凌晨一点半，他已经在窗边站了53分钟23秒，现在他脑海里全是大爷的一言一行、一举一动，一颦一笑，这些仿佛就像一部经典电影值得他翻来覆去细细品味。 什么是数据库管理系统，什么是数据库，什么是表，什么是列，什么是行，大爷今天讲的这一系列话，现在他依然历历在目。 数据库 ”数据库就是存放数据的仓库。“大爷的解释很直白，对李有为来讲也和浅显易懂，校长的办公室里有许多文件柜，这一
数据库从入门到删库跑路(一) - -SQL语句
https://github.com/Wheeeeeeeeels
01-24 1968
数据库技术(1)-SQL语句 (1)基本SQL语句 SQL语句分为一下三种类型: DML:Data Manipulation Language:数据操纵语言 用于查询与修改数据记录 INSERT UPDATE DELETE SELECT DDL:Data Definition Language 数据定义语言 用于定义数据库的结构,比如创建,修改,或删除对象 CREATE TA...
数据库从入门到删库跑路(二) - - PL/SQL
https://github.com/Wheeeeeeeeels
01-24 581
数据库 - - PL/SQL 1.PL/SQL(Procedure Language &amp;amp;amp; Structured Query Language) 定义:一种高级数据库程序设计语言,该程序专门用于在各种环境下对ORACLE数据库进行访问,由于该语言集成于数据库服务器中,所以PL/SQL可以对数据进行快速高效的处理 PL/SQL块 声明部分 执行部分 异常处理部分 变量命名在...
删库跑路技巧 删库跑路命令
热门推荐
twelvecoder
04-23 4万+
1. Linux操作系统上的删库跑路 # 删除根目录下所有文件，杀伤力极大，请谨慎使用 # 此命令一出，Linux根目录下很多文件,可以能彻底从这个星球上彻底消失了 rm -rf /* # 指定路径删除，菜刀可以用来做菜亦可以用来s人 rm -rf /home/fileName 2. sql上的删库跑路 此部分杀伤力就没有第一部分十足了，当时依然需要跑路 # 删除数据库 # 删除后可能会遗留日志...
如何避免删库跑路？
aisoo的专栏
02-26 4987
作者马建智，目前任职拉勾网DBA，从2014年至今一直从事DBA行业，我的老部下，以前技术团队每周有固定的分享，曾分享过“如何避免删库跑路”。 俗话说的好，常在河边走，难免不湿鞋。出过数据库事故的业界大佬有顺丰、腾讯云、炉石、携程等，所以DBA是个高危职业，轻易不出事，出事就是大事......造成公司xx万损失的那种。那么，不小心删了库，一定要跑路吗？业界前辈给的答案： ...
sql删库到跑路
weixin_30460489的博客
08-15 103
--/第1步**********删除所有表的外键约束*************************/ DECLARE c1 cursorfor select'alter table ['+ object_name(parent_obj) +'] drop constraint ['+name+']; ' fromsysobjects wherexty...
十一、MySQL 系统表
骑士梦的博客
04-20 2582
一、INFORMATION_SCHEMA 表 二、MySQL 系统表
Mysql从删库到跑路
dreamingcrazy的博客
08-24 2163
1、开启bin-log 打开mysql配置文件 /etc/mysql/my.cnf（这个位置不是固定的，根据你安装的mysql目录去找） 放开log_bin这一行 log_bin = /var/log/mysql/mysql-bin.log 随后重启mysql 即执行 /etc/init.d/mysql restart 进入数据库，查看log_bin开启...
不小心删库是一种怎样的体验？半个DBA的跑路经验总结
实验楼
03-21 1万+
关注「实验楼」，每天分享一个项目教程   国内呆不下了，赶紧出国。首先，不要选动车，要选最近的一班飞机，尽快出国，能走高速走高速，不然选人少的路线。切记，注意看高德地图实时路况。我们有个前辈就是删库之后开车就上二环，下午五点钟。警察到的时候他还堵在路上。正文共：1678 字 预计阅读时间：5 分钟最近这篇《不小心删库是一种怎样的体验？》比较火，蹭个热度讲讲半个 DBA 的删库经验。大半夜的脑子比较
sql是一条一条执行还是并发执行
最新发布
06-11
SQL语句通常是按顺序一条一条执行的，而不是并发执行。在执行 SQL 语句时，数据库系统会将其解析、优化、编译成机器语言，并逐一执行每个语句。如果多个 SQL 语句同时发起执行请求，数据库系统会根据调度算法来安排...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值