正文

mysql高级学习笔记(代码片段)

hesorchen  hesorchen  2022-12-15  694

关键词：

文章目录

MySQL基础篇学习笔记

SQL性能下降的原因

查询语句写的烂
索引失败
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置（缓冲、线程数等）

SQL的执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。可以理解为“排好序的快速查找数据结构”。

除了数据以外，数据库还维护着一个满足特定查找的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存在磁盘中。

索引的优劣势

优势：

类似于图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
索引可以大大提高查询速度，但是会降低更新表的速度，如进行INSERT、UPDATE、DELETE。因为更新表时，不仅要保存数据，还要改索引。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立更优秀的索引。

索引的分类

单列索引：单列索引即一个索引只包含单个列。
组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
唯一索引：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

索引的创建

语法：

CREATE INDEX index_name ON table_name (column_name(length))
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

实例：

CREATE INDEX idx1 ON t1(id);# 单列索引
CREATE UNIQUE INDEX idx2 ON t1(nam(10));# 唯一索引
CREATE UNIQUE INDEX idx3 ON t1(id,nam(10));# 组合索引

索引结构

1. BTree索引
2. Hash索引
3. full-text全文索引
4. R-Tree索引

判断是否需要创建索引

需要创建索引的情况：

主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
where条件里用不到的字段不创建索引
单列、组合索引的选择问题
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段

不需要创建索引的情况：

表记录太少
经常增删改的字段。【索引增加查询速度，降低更新速度】
如果某个字段包含太多重复的内容，那么建索引不会有太大的效果。【索引的选择性：索引列中不同值/表中总记录数，越接近1，索引效率越高】

EXPLAIN

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

作用：可以分析出以下信息

表的读取顺序
数据读取操作的操作类型
哪些索引可以被使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

使用：

Explain + SQL语句

返回一张表，表头信息依次为：

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

EXPLAIN之id

主要用于分析表的读取顺序。

三种情况：

id全相同，执行顺序由上至下
id全不同，如果是子查询，id的序号会递增。先执行子查询，也就是id值越大的优先级越高，越先被执行。
id有相同也有不同，id相同的被分为一组，同一组内由上至下执行，不同组按照id从大到小执行。【derived_x表示该表来自于id为x的操作产生的临时表】

EXPLAIN之select_type

主要用于区别普通查询、联合查询、子查询等的复杂查询。

共有六种情况：

SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
PRIMARY：查询中若有子查询，PRIMARY是最外层查询。
SUBQUERY：子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里。
UNION:若第二个select出现在UNION之后，则被标记为UNION。
UNION RESULT：从UION表获取结果的SELECT。

实例参考博客：MySQL高级——Explain信息中select_type字段解释

EXPLAIN之table

说明当前这行数据是关于哪张表的。

EXPLAIN之type

显示查询使用了何种类型：
从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref级别。

共有七种情况：

system：表中只有一行数据（系统表）的情况，基本上不会出现，可以忽略不计。
const：表示通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快，如主键默认具有唯一索引，MySQL就能将该查询转换为一个常量。【场景：命中主键或唯一索引、被连接的部分是一个常量值(const)】
eq_ref：唯一性索引扫描，多表连接时，对于前表中的每一行（row），对应后表只有一行被扫描，这类扫描的速度也非常的快。【场景：联表查询、命中主键或唯一索引】
ref：非唯一性索引扫描，后表使用了普通非唯一索引，对于前表的每一行(row)，后表可能有多于一行的数据被扫描。ref每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。【场景：联表查询、普通非唯一索引】
range：range比较好理解，它是索引上的范围查询，它会在索引上扫码特定范围内的值。【场景：>、>=、between and等】
index：需要扫描索引上的全部数据。
ALL：不建索引，全表扫描。

实例参考博客：Mysql Explain之type详解

EXPLAIN之possible_keys

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

EXPLAIN之key

实际使用的索引，如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

EXPLAIN之key_len

key_len说明了查找时用到的索引长度，可以根据长度，推测多维索引用到了几维。

EXPLAIN之ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
ref指某个表“=”号后的东西。

EXPLAIN之rows

mysql认为必须要逐行去检查和判断的记录的条数。

EXPLAIN之Extra

此字段里存放的是额外的一些重要信息，放到其他字段里面都不太适合，比如排序规则、是否用了临时表、是否使用了覆盖索引等。

比较重要的共有以下几种情况：

Using filesort：MySQL有两种方式可以生成有序的结果:通过排序操作或者使用索引。当Extra中出现了Using filesort 说明使用了前者，但注意虽然叫filesort但并不是一定就是用了磁盘文件来进行排序，也可能是在内存里完成的。当出现排序，可以通过添加合适的索引来改进性能，用索引来为查询结果排序。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index：表示相应的select操作中使用了覆盖索引，避免了回表，减少IO操作，提高查询效率。
Using where ：表示使用了where过滤
Using join buffer ：表示使用了连接缓存
impossible where：where条件总是FALSE。

索引优化

理想情况：索引全值匹配。

索引失效的情况

违反最左前缀法则：如果索引有多列，要遵守最左前缀法则。即查询从索引的最左前列开始并且不跳过索引中的列。
在索引列上做任何操作：如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描。
索引范围条件右边的列：索引范围条件右边的索引列会失效。
尽量使用覆盖索引：只访问索引查询（索引列和查询列一致），减少select*。
使用不等于（!=、<>）：mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）。
like以通配符开头（’%abc’）：索引失效，%放右边索引生效。
字符串不加单引号索引失效：发生了隐式类型转换，同2。
or连接：同3。
order by：索引有两个作用：排序和查找。如果order by后的规则导致索引失效，那么会产生Using filesort。
group by：分组之前必排序（排序同order by），同9。

小表驱动大表

涉及in的条件查询，可以用exists改写，保证小表驱动大表能提高效率（两表都建好索引的前提下）。

参考博客：MySQL（七）｜MySQL中In与Exists的区别（1）

索引优化order by

适当增大sort_buffer_size参数的设置
适当增大max_length_for_sort_data参数的设置
建好合适的索引。索引有两个功能：排序和查找。建好合适的索引可以避免Using filesort提高效率。

索引优化group by

group by的实质是先排序然后进行分组，遵照索引建的最佳左前缀。

对于order by的优化都可以照搬。

能写在where里的条件别写在having里。

慢查询日志

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

默认未开启，需要手动开启。

慢查询日志的查看：

SHOW VARIABLES LIKE '%slow_query_log%';

慢查询日志的开启：

SET GLOBAL slow_query_log = 1;

long_query_time的查看：

SHOW VARIABLES LIKE '%long_query_time%';

long_query_time的修改：

SET GLOBAL long_query_time = 1;

还可以结合日志分析工具：mysqldumpslow进行分析。

Show Profile

Show Profile是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。

默认未开启，使用前需要手动开启。

查看：

SHOW VARIABLES LIKE 'profiling';

慢查询日志的开启：

SET GLOBAL profiling = 1;

查看结果：

show profiles;

诊断具体的SQL：

show profile cpu,block io for query 查看结果中涉及的SQL数字id;

全局查询日志

全局查询日志用于保存所有的sql执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

开启全局日志，开启后会记录所有SQL

set global general_log =1

记录SQL到系统表general_log中

set global log_output='table'

查看记录的SQL

select * from mysql.general_log

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

从对数据操作的类型角度分为：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成时，它会阻断其他写锁和读锁。

读锁会阻塞写，但是不会阻塞读。而写锁两者都会阻塞。

MySQL常用引擎有MyISAM和InnoDB，而InnoDB是mysql默认的引擎。MyISAM不支持行锁，而InnoDB支持行锁和表锁。

表锁

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

查看表上加过的锁：

SHOW OPEN tABLES;

手动增加表锁：

LOCK TABLE 表名 read【write】;

解锁：

UNLOCK TABLES;

MyISAM不适合做以写操作为主的表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁：锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION) ；二是采用了行级锁。

MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

for update

如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务可以读取，但不能进行更新和插入操作。

-- A用户对id=1的记录进行加锁
select * from user where id=1 for update;

-- B用户无法对该记录进行操作
update user set count=10 where id=1;

-- A用户commit以后则B用户可以对该记录进行操作

行锁的实现需要注意：

行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
两个事务不能锁同一个索引。
insert，delete，update在事务中都会自动默认加上排它锁。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内并不存在的记录，叫做间隙（GAP）。

InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁。

-- 用户A
update user set count=8 where id>2 and id<6

-- 用户B
update user set count=10 where id=5; # 间隙锁，用户A commit之前无法更新

建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度

参考博客：

mysql学习笔记_10_mysql高级操作（下）(代码片段)

MySQL高级操作（下）五、MySQL预处理语句1、设置预处理stmt，传递一个数据作为where的判断条件preparestmtfrom“select*fromtable_namewhereid>?”;2、设置一个变量set@i=1;3、执行预处理语句executestmtusing@i;4、删除预处理指... 查看详情

学习笔记mysql数据库高级版-索引优化慢查询锁机制等(代码片段)

本文是尚硅谷周阳（阳哥）老师的MySQL高级篇视频的学习笔记。由于视频比较老，所以在高版本的MySQL中索引的地方做了优化，和视频的内容不完全一样，不过大体一致。从第四节锁机制开始的部分还没有整理... 查看详情

clickhouse-尚硅谷（14.高级-materializemysql引擎）学习笔记(代码片段)

上一篇：（13.高级-物化视图）学习笔记下一篇：（15.高级-常见问题排查）学习笔记文章目录1概述1.1特点1.2使用细则2案例实操2.1MySQL开启binlog和GTID模式2.2准备MySQL表和数据2.3开启ClickHouse物化引擎2.4创建复... 查看详情

mysql高级学习笔记(代码片段)

目录1、MySQL数据库逻辑架构。（1）网络连接层。（2）服务层（MySQLServer）。 1）连接池（ConnectionPool）。 2）系统管理和控制工具（ManagementServices&Utilities）。 3）SQL接口࿰... 查看详情

mysql高级学习笔记（1-15）(代码片段)

MyISAM和InnoDB对比MyISAMInnoDB主外键不支持支持事务不支持支持行表锁表锁，操作时即使操作一条记录也会锁住一整张表，不适合高并发的操作行锁，操作时只锁住某一行，不会影响到其他行，适合高并发缓存只... 查看详情

clickhouse-尚硅谷（14.高级-materializemysql引擎）学习笔记(代码片段)

mysql高级笔记-02(代码片段)

文章目录MySQL高级-笔记-02六.应用优化6-1使用连接池6-2减少对MySQL的访问6-3负载均衡七.Mysql中查询缓存优化7-1概述7-2操作流程7-3查询缓存配置7-4开启查询缓存7-5查询缓存SELECT选项7-6查询缓存失效的情况八.Mysql内存管理及优化8-1内存... 查看详情

mysql高级笔记-01(代码片段)

文章目录MySQL高级-笔记-01一.MySQL的体系结构1-1Mysql的体系结构概览1-2连接层1-3服务层1-4引擎层1-5存储层二.存储引擎2-1存储引擎概述2-2MylSAM与InnoDB（1）InnoDB（2）MylSAM2-3存储引擎的选择三.索引3-1什么是索引3-2索引优... 查看详情

mybatis学习笔记(代码片段)

...优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。Mybatis避免了几乎所有的查看详情

mysql学习笔记六(代码片段)

MySQL学习笔记六文章目录MySQL学习笔记六一、简介二、MySQLNULL值处理三、MySQL正则表达式四、总结一、简介时隔多日没有学习MySQL数据库了，今天重新开始继续学习MySQL数据库的有关内容，并重新开启MySQL的新的征程。二、My... 查看详情

mysql高级内容学习总结(代码片段)

创建索引create[unique]indexindexnameontablename(columnname(length))altertablenameadd[unique]index[indexname]on(columnname(length))‘删除‘dropindex[indexname]ontablename‘显示表的所有索引‘showindexfromtablename‘该语句添加一查看详情

clickhouse-尚硅谷（11.高级-查询优化）学习笔记(代码片段)

上一篇：（10.高级-语法优化规则）学习笔记下一篇：（12.高级-数据一致性）学习笔记文章目录1单表查询1.1Prewhere替代where1.2数据采样1.3列裁剪与分区裁剪1.4orderby结合where、limit1.5避免构建虚拟列1.6uniqCombined... 查看详情

clickhouse-尚硅谷（13.高级-物化视图）学习笔记(代码片段)

上一篇：（12.高级-数据一致性）学习笔记下一篇：（14.高级-MaterializeMySQL引擎）学习笔记文章目录1概述1.1物化视图与普通视图的区别1.2优缺点1.3基本语法2案例实操2.1准备测试用表和数据2.2创建物化视图2.3... 查看详情

mysql基础学习笔记(代码片段)

本文主要分为四部分来写；是学习mysql数据库基础的学习笔记1、MYSQL基础（1到6）2、性能优化（7-11）3、MYSQL锁机制（12-14）4、主从复制这里前面几章主要是mysql的基础实际操作我基础写完之后单独写一... 查看详情

《golang高级编程》学习笔记(代码片段)

一、数组、字符串、切片1、数组定义方式：vara[3]int//定义长度为3的int型数组,元素全部为0varb=[...]int1,2,3//定义长度为3的int型数组,元素为1,2,3varc=[...]int2:3,1:2//定义长度为3的int型数组,元素为0,2,3vard=[...]int1,2,4:5,6//定义... 查看详情

clickhouse-尚硅谷（9.高级-建表优化）学习笔记(代码片段)

上一篇：（8.高级-Explain查看执行计划）学习笔记下一篇：（10.高级-语法优化规则）学习笔记文章目录1数据类型1.1时间字段的类型1.2空值存储类型2分区和索引3表参数4写入和删除优化5常见配置5.1CPU资源5.2... 查看详情

unix环境高级编程学习笔记(代码片段)

目录图1-3ls命令简单实现图1-4从标准输入读，并向标准输出写图1.5跟图1.4功能差不多一样图1.6打印进程ID图1.7UNIX系统的进程控制功能简单的程序说明图1-3ls命令简单实现#include"apue.h"#include<dirent.h>intmain(intargc,char*argv[]... 查看详情

大数据高级开发工程师——spark学习笔记(代码片段)

文章目录Spark内存计算框架SparkSQLSparkSQL架构设计1.SparkSQL的架构设计实现2.Catalyst执行过程SQL解析阶段Parser绑定逻辑计划Analyzer逻辑优化阶段Optimizer生成可执行的物理计划阶段PhysicalPlan3.代码生成阶段生成代码与sql解析引擎的区别Tun... 查看详情