mysql高级

帅一个千秋

已于 2023-02-03 03:16:57 修改

阅读量1.4k

点赞数 1

分类专栏：基础文章标签： sql 数据库

于 2023-01-29 22:20:56 首次发布

本文链接：https://blog.csdn.net/weixin_52130455/article/details/128709830

版权

基础专栏收录该内容

5 篇文章 0 订阅

订阅专栏

字段的 is not null 和 is null

无过滤不索引编辑using filesort 说明进行了手工排序！原因在于没有 where 作为过滤条件！编辑

1.3日志分析工具 mysqldumpslow

SQL的执行流程

七种join

但是mysql中不支持full outer join的语法

对于

代替如下，可以用left join 和 right join结合来实现

select * from tbl_1 a left join tbl_2 b on a.deptId=b.id
union
select * from tbl_1 a right join tbl_2 b on a.deptId=b.id

对于

select * from tbl_1 a left join tbl_2 b on a.deptId=b.id where b.id is null
union
select * from tbl_1 a right join tbl_2 b on a.deptId=b.id where a.deptId is null

索引

1、概念

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

下图就是一种可能的索引方式示例

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

优缺点

优势：

提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

2. Mysql 的索引

2.1 Btree 索引

真实的数据存在于叶子节点，非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项

2.2 B+tree 索引

B- 树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息； B+ 树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

3、索引的分类

3.1单值索引

一个索引质值包含但个列，一个表可以有多个单值索引

3.2唯一索引

索引列的值必须唯一，但允许为空值

3.3复合索引

一个索引包含了多个列

3.4基本语法

创建	CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除	DROP INDEX [indexName] ON mytable;
查看	SHOW INDEX FROM table_name\G
使用alter命令	ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL 。
	ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
	ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
	ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): 该语句指定了索引为 FULLTEXT ，用于全文索引。

4. 索引的创建时机

4.1 适合创建索引的情况

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

4.2 不适合创建索引的情况

表记录太少
经常增删改的表或者字段
Where 条件里用不到的字段不创建索引
过滤性不好的不适合建索引
重复且平均分配

Explain 性能分析

1. 概念

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

用法： Explain+SQL 语句。Explain 执行后返回的信息：

2.mysql常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件瓶颈：top,free,iostat和vmstat来查看系统的性能状态

3.Explain

3.1 id

select 查询的序列号 , 包含一组数字，表示查询中执行 select 子句或操作表的顺序。

id相同，执行顺序由上至下

id 不同，id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

有相同也有不同

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED

i d 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

3.2select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

3.3type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref 。

常见：

system > const > eq_ref > ref > range > index >ALL

（1）system

表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计

（2）const

表示通过索引一次就找到了 ,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快如将主键置于 where 列表中， MySQL 就能将该查询转换为一个常量。

（3）eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

（4）ref

非唯一性索引扫描，返回匹配某个单独值的所有行 . 本质上也是一种索引访问，它返回所有匹配某个单独值的行，

然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

没用索引前：

建立索引后：
（5）range

只检索给定范围的行 , 使用一个索引来选择行。 key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between 、 < 、 > 、 in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

（6）index

出现 index 是 sql 使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

（7）ALL

Full Table Scan，将遍历全表以找到匹配的行。

3.4possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出， 但不一 定被查询实际使用。

3.5key

实际使用的索引。如果为 NULL ，则没有使用索引。

查询中若使用了覆盖索引，则该索引仅出现在key列表中

3.6key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。

显示的是索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索得出的

3.7ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

3.8rows

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

3.8extra

（1）Using filesort

说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL 中无法利用索引完成的排序操作称为“ 文件排序 ” 。

出现 filesort 的情况：

优化后：order by字段最好和所建立的索引的包含列的个数和顺序一致

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

（2）Using temporary

使了用临时表保存中间结果 ,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

优化后：group by字段最好和所建立的索引的包含列的个数和顺序一致

（3）Using index

Using index 代表表示相应的 select 操作中使用了覆盖索引 (Covering Index) ，避免访问了表的数据行，效率不错！如果同时出现 using where ，表明索引被用来执行索引键值的查找 ; 如果没有同时出现 using where ，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组。

（4）Using where

表明使用了 where 过滤

（5）Using join buffer

使用了连接缓存

（6）impossible where

where 子句的值总是 false，不能用来获取任何元组。

（7）select tables optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在 innodb 中：

在 Myisam 中：

索引优化

单表优化案例

type：all，全表扫描，且有文件排序

优化1：为id，comment，views建立一个复合索引

type：range，但是文件类排序还存在，范围以后索引会失效
先排序id，遇到相同的id再排序comment，遇到相同的comment再排序views，由于comment处于中间位置，且条件是一个range，mysql无法利用索引再对后面的views进行检索

优化2：为id，views及建立一个复合索引

两表优化案例

left join查询，左表全有，一定要为右表建立索引
right也是一样的

全值匹配

三条语句

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

查询的字段按照顺序在索引中都可以匹配到

SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给你自动地优化。

最佳左前缀法则

查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用。

不要在索引列上做任何计算

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

1、在查询列上使用了函数

结论：等号左边无计算！

2、在查询列上做了转换

结论：等号右边无转换！

字符串类型不能失去单引号

索引列上不能有范围查询

建议：将可能做范围查询的字段的索引顺序放在最后

尽量使用覆盖索引

即查询列和索引列一致，不要写 select *!

使用不等于(!= 或者<>)的时候

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

字段的 is not null 和 is null

当字段允许为 Null 的条件下：

is not null 用不到索引，is null 可以用到索引

like 的前后模糊匹配

前缀不能出现模糊匹配！

如果非要用%*%查询，可以用覆盖索引，建的索引和查的字段个数、顺序最好一致

建立一个name,age的复合索引，下面的索引都不会失效

减少使用 or

使用 union all 或者 union 来替代

口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 * ；

不等空值还有 OR ，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍

小表驱动大表

小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id=B.id

当B的数据集小于A的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id=A.id)
等价于：
for select * from A
for select * from B where B.id=A.id

当A的数据集小于B的数据集时，用exists优于in

exists：将主查询的数据放到子查询做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留

排序分组优化

orderby 优化

无过滤不索引

using filesort 说明进行了手工排序！原因在于没有 where 作为过滤条件！

结论：无过滤，不索引。 where ， limt 都相当于一种过滤条件，所以才能使用上索引！

顺序错，必排序

首先为没有建立索引的字段排序，必然会触发filesort

where 两侧列的顺序可以变换，效果相同，但是 order by 列的顺序不能随便变换！

deptid 作为过滤条件的字段，无法使用索引，因此排序没法用上索引

方向反，必排序

如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。
如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的！

索引的选择

发现 using filesort 依然存在。

原因： empno 是范围查询，因此导致了索引失效，所以 name 字段无法使用索引排序。

所以，三个字段的符合索引，没有意义，因为 empno 和 name 字段只能选择其一！

解决：鱼与熊掌不可兼得，因此，要么选择 empno,要么选择 name

那么具体选择哪个索引呢
所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，使用 empno 字段的范围查询，过滤性更好

结论：当范围条件和 group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

using filesort

mysql 的排序算法

①双路排序

MySQL 4.1 之前是使用双路排序 , 字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

②单路排序

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO, 但是它会使用更多的空间，因为它把每一行都保存在内存中了。

③单路排序的问题

由于单路是后出的，总体而言好过双路。但是存在以下问题：

在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出 , 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O 。

结论：本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

如何优化

①增大 sort_butter_size 参数的设置

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整。

②增大 max_length_for_sort_data 参数的设置

mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）。

③减少 select 后面的查询的字段。

当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O ，但是用单路排序算法的风险会更大一些, 所以要提高 sort_buffer_size 。

使用覆盖索引

覆盖索引： SQL 只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据

group by

group by 使用索引的原则几乎跟 order by 一致，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直接使用索引。

截取查询分析

1、慢查询日志

1.1是什么

（1） MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time 值的 SQL ，则会被记录到慢查询日志中。

（2）具体指运行时间超过 long_query_time 值的 SQL ，则会被记录到慢查询日志中。 long_query_time 的默认值为10，意思是运行 10 秒以上的语句。

（3）由他来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过 5 秒钟，我们就算慢 SQL ，希望能收集超过5 秒的 sql ，结合之前 explain 进行全面分析。

1.2 怎么用

默认情况下， MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

（1）开启设置

SQL语句	描述	备注
SHOW VARIABLES LIKE '%slow_query_log%';	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF ，表示慢查询日志是禁用的
set global slow_query_log=1;	开启慢查询日志
SHOW VARIABLES LIKE 'long_query_time%';	查看慢查询设定阈值	单位秒
set long_query_time=1	设定慢查询阈值	单位秒

如果重启配置就会变为默认配置

（2）如永久生效需要修改配置文件 my.cnf 中 [mysqld] 下配置

[mysqld]

slow_query_log=1

slow_query_log_file=/var/lib/mysql/atguigu-slow.log

long_query_time=3

log_output=FILE

（3）运行查询时间长的 sql ，打开慢查询日志查看

1.3日志分析工具 mysqldumpslow

（1）查看mysqldumpslow 的帮助信息

（2）查看mysqldumpslow的帮助信息

得到返回记录集最多的 10 个 SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的 10 个 SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前 10 条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

show profile

利用 show profile 可以查看 sql 的执行周期！

开启 profile

查看 profile 是否开启： show variables like '%profiling%'

如果没有开启，可以执行 set profiling=1 开启！

使用 profile

执行 show prifiles 命令，可以查看最近的几次查询。

根据 Query_ID, 可以进一步执行 show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。

注意这些问题

mysql 的查询流程大致是：

mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析, 也就是说，在解析查询之前，服务器会先访问查询缓存 (query cache) ——它存储 SELECT 语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。 mysql 解析器将使用 mysql 语法规则验证和解析查询；预处理器则根据一些 mysql 规则进一步检查解析数是否合法。查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。然后，mysql 默认使用的 BTREE 索引，并且一个大致方向是 : 无论怎么折腾 sql ，至少在目前来说， mysql 最多只用到表中的一个索引。

批量数据脚本

编写随机函数

1、随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	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 $$

如果要删除函数：drop function rand_string;

2、随机产生部门编号

#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

创建存储过程

创建往 emp 表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
--set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

删除：drop PROCEDURE insert_emp;

调用存储过程