mysql/mariadb

1、定位慢sql

2、explain 分析SQL的执行计划

3、show profile 分析

4、trace

5、确定问题并采用相应的措施

选择合适的数据类型

表的设计合理化，符合三大范式（3NF)

连接数(connection)配置

查询缓存(query_cache)配置

临时表缓存(tmp_table_size)配置

索引缓冲区（key_buffer_size）配置

cpu

网络

存储

分库分表

读写分离

缓存层

执行student.length次

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)

如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

只执行一次

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快

select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,

select * from student where a=xxx and b=xxx and c=xxx select * from student where b=xxx and c=xxx select * from student where a=xxx and b=xxx

解决方法

select a,b,c,d from student where a=xxx and b=xxx and c=xxx

select a,b,c from student where a=xxx and b=xxx and c=xxx

select  a 使用的是哪个索引

select abc from table group by a

sql

创建表

创建索引的最佳方式

创建表

插入数据

解决

  1、使用索引

    建立索引可以使查询速度得到提升，我们首先应该考虑在where及order by，group by涉及的列上建立索引。

   2、借助explain（查询优化神器）选择更好的索引和优化查询语句

    SQL 的 Explain 通过图形化或基于文本的方式详细说明了 SQL 语句的每个部分是如何执行以及何时执行的，以及执行效果。通过

对选择更好的索引列，或者对耗时久的SQL语句进行优化达到对查询速度的优化。

   3、任何地方都不要使用SELECT * FROM语句。

   4、不要在索引列做运算或者使用函数

   5、查询尽可能使用limit来减少返回的行数

   6、使用查询缓存，并将尽量多的内存分配给MYSQL做缓存

实现数据库的读写分离，从而改善数据库的负载压力

分表

分库

分区

es

redis

确保每列保持原子性

确保表中的每列都和主键相关

确保每列都和主键列直接相关,而不是间接相关

内连接（Inner Join）：只连接匹配的行

左外连接（Left Outer Join或Left Join）：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。

右外连接（Right Outer Join或Right Join）： 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。

全外连接（Full Outer Join或Full Join）： 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行

mysql提供了char和varchar两种字段类型，但若查询条件where中包含char或varchar的限制条件，
那么会忽略char或varchar字段记录中末尾的空格字符。

mysql中字符串比较的时候使用的是PADSPACE校对规则，会忽略字符字段最后的空格。

举例

方法一

方法二

方法三

以上三种方法均可以将末尾有空格的varchar字段的记录查询出。

删除老师名字相同的行

DELETE FROM lesson WHERE id NOT IN (SELECT MIN(id)FROM lesson GROUP BY teacher);

不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。

select的结果再通过一个中间表select多一次，就可以避免这个错误

select * from t_dict;

select *, 123 as addcloumn from t_dict;

使用limit固定字段排序

distinct

group by

row_num

问题出现环境

原因分析

解决方案

allowMultiQueries=true

useUnicode=true

characterEncoding=UTF-8

useSSL=false

serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull

rewriteBatchedStatements=true

开启慢查询日志，可以让mysql或者mariadb记录执行时超过指定时间的sql语句。

配置/etc/my.cnf

参数说明

systemctl restart mysql

show variables like '%quer%';

select sleep(3)

select * from slow_log\G;

show variables like '%profil%';

help show profile

SET profiling = 1

show profiles;

show profile for query 4;

查看所有

查看cpu

查看内存memory

查看不同资源开销

含义

使用

①.id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

②.user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

③.host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

④.db列，显示这个进程目前连接的是哪个数据库

⑤.command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

⑥.time列，显示这个状态持续的时间，单位是秒

⑦.state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

⑧.info列，显示这个sql语句，是判断问题语句的一个重要依据

查询正在执行的sql线程

在使用mysql运行某些语句时，会因数据量太大而导致死锁，没有反映。这个时候，就需要kill掉某个正在消耗资源的query语句即可

每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。

SHOW PROCESSLIST;

语法

示例

列

查看线程

kill线程

（1）每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；

（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

查看某个表的碎片大小

列出所有已经产生碎片的表

InnoDB表

MyISAM表

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行

CREATE TABLE device_coordinate_config_0330 AS SELECT * FROM device_coordinate_config WHERE 1=2;

CREATE TABLE device_coordinate_config_0330 AS SELECT * FROM device_coordinate_config ;

指定库和表

复合分片

标准分片

分布式id

默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中

my.cnf

服务器启动和关闭过程中的信息

服务器运行过程中的错误信息

事件调度器运行一个事件时产生的信息

在从服务器上启动从服务器进程时产生的信息

mysql5.5.7之前

mysql5.5.7之后

查询日志在MySQL中被称为general log(通用日志)，查询日志里面记录了数据库执行的所有命令

参数general_log用来控制开启、关闭MySQL查询日志

参数general_log_file用来控制查询日志的位置

参数log_output控制着查询日志的存储方式

查看是否开启

查看存储方式

开启

操作

删除mysql.general_log内容

关闭

会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的

慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。

slow_query_log: 慢查询开关，表示是否打开慢查询日志

long_query_time: 慢查询指定时间设置，表示"多长时间的查询"被认定为"慢查询"，单位是秒(s)，默认是10s

log_queries_not_using_indexes: 表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询记录中，OFF表示不记录，ON表示记录。

slow_query_log_file: 当使用文件存储慢查询日志时(log_output设置为"FILE"或者"FILE,TABLE"时)，制定慢查询日志存储在哪个文件中，默认的文件名是"主机名-slow.log"，存储目录为数据目录

log_throttle_queries_not_using_indexes: MySQL5.6.5版本新引入的参数，用来限制没有使用索引的语句每分钟记录到慢查询日志中的次数。在生产环境中，有可能有很多没有使用索引的语句，可能会导致慢查询日志快速增长。

开启慢查询

慢查询时间设置

文件

表

简介

作用

组成

参数

简介

作用

undo log的存储方式

参数

主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。

恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

binlog对于事务存储引擎的崩溃恢复也有非常重要的作用

为了管理所有的binlog文件，MySQL额外创建了一个base-name.index文件，它按顺序记录了MySQL使用的所有binlog文件。

查看是否开启

配置文件开启：my.cnf

查看文件

STATEMENT

ROW

MIXED

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db&binlog-ignore-db

binlog-format

log_bin_trust_function_creators

是复制过程中产生的日志

max_relay_log_size

relay_log

relay_log_index

relay_log_info_file

relay_log_purge

relay_log_recovery

事务是数据库的逻辑工作单位，不可分割，事务中包含的各操作要么都做，要么都不做 

从一个一致性状态变到另一个一致性状态

事务与事务之间是隔离的，并发执行的各个事务之间不能互相干扰

一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的，不能回滚

建表

新增数据

新增数据

查询

设置

设置

读未提交（Read uncommitted）

读提交（read committed）

可重复读（repeatable read）（mysql默认级别）

串行化（Serializable）

概念

解决问题

前提

三个字段

实现

说明

示例

innodb存储引擎

案例准备

分析工具

场景

数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引

频繁更新的字段不要使用索引

字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引

where 子句里对索引列使用不等于（<>），使用索引效果一般

建表

覆盖索引

最左前缀匹配规则

1） 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3）尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

9）对于经常存取的列避免建立索引

锁粒度

加锁机制

模式

行为

加锁

并发锁

查询表级锁争用情况

官方文档

案例准备

行锁

表锁

锁总结

id int(20) 自增长主键

remark varchar(500) 备注

delete_flag smallint(2) 数据状态

create_time timestamp 创建时间

update_time timestamp 更新时间

version int(11) 数据版本

create_user_code varchar(64) 创建用户

update_user_code varchar(64) 修改用户

gmt_ 代表默认 gmt 时区，加上created 可以明确的表述 一个时间的完整定义， create time 字面意思里面看不到时区，具体什么时区是看不到的

分支主题

分支主题

create table

drop table

alter table

create view

drop view

create index

drop index

create procedure

drop procedure

create trigger

drop trigger

drop schema

create domain

alter domain

drop domain

查询

插入

删除

更新

授予用户访问权限

刷新权限 权限更新后刷新才会起作用

拒绝用户访问

解除用户访问权限

创建用户

查看 canal的权限

提交当前事务

回滚当前事务

设置事务隔离级别

查看隔离级别

declare @id

set @id

select @id

SHOW VARIABLES LIKE '%time%';

mysql5.5版本之后，默认innodb引擎

特点

使用

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务

特点

使用

show engines\G

show variables like '%storage_engine%';

show create table 表名;

tinyint

smallint

MEDIUMINT

int/integer

bit[(m)]

bigint

float

double

DECIMAL(P,D)

date

datetime

time

timestamp

year

text

char

varchar

tinyblob

tinytext

blob

text

mediumblob

mediumtext

longblob

longtext

获取不在本角色下的用户

含义

分类

含义

类型

含义

举例

含义

说明

类型

含义

举例

含义

举例

含义

含义

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Using index：仅使用索引树中的信息从表中检索列信息，而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据)。 当查询仅使用作为单个索引的一部分的列时，可以使用此策略

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

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

Impossible where：这个值强调了where语句会导致没有符合条件的行。

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

Using index for group-by 类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

Impossible HAVING: The HAVING clause is always false and cannot select any rows.(HAVING子句总是为false，不能选择任何行)

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

直接一次查询出所有结果

使用多线程合并分页的结果

for循环遍历单个更新

使用批量一次性更新

使用Lists.partition方法对集合进行切分，分多次批量更新

for循环遍历单个插入

使用批量一次性插入

使用Lists.partition方法对集合进行切分，分多次批量插入

NOW() 存储到datetime字段类型中

sysdate() 存储到datetime字段类型中

curdate() 存储到date字段类型中

curtime() 存储到time字段类型中

DATE_SUB(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

参数

DATEDIFF

TIMESTAMPDIFF

时间转换为字符串

字符串转换为时间

concat(s1,s2,s3,...,sN)

concat_ws(sep,s1,s2,s3,...,sN)

group_concat

lower(String)

upper(String)

left(String,x)

right(String,x)

lpad(string,n,pad)

rpad(string,n,pad)

注意：长度n可以是小于或等于string字符串长度的值，此时lpad或者rpad的作用都是从左进行字符串截取而非填充，直到长度为n。也就是说lpad和rpad函数最强约束条件是长度参数n。

trim(String)

ltrim(String)，rtrim(String)

trim(substring from string)

repeat(String ,count)

abs(x)

mod(x,y)

round(x,y)

truncate(x,y)

rand()

least(v1,v2,v3,…,vn)

greatest(v1,v2,v3,…,vn)

含义

使用场景：可以使用if(expr,1,0) 和sum求和函数一起使用进行计数

case when ...then...else...end

case ...when...then...else...end

NVL（表达式1，表达式2）（mysql不支持，mariadb支持）

求和

最大值

最小值

计数

求平均值

不带where条件：左表的全部，右表符合条件的记录，如果没有，则显示为空

1、以中间表的查询结果作为结果集：查询角色下所有的用户

2、以中间表的查询结果作为结果集：查询用户下所有的角色

不带where条件：包括左表中的所有记录和右表中联结字段相等的记录

1、判断同一个租户下所有的应用是否有相同的角色名称

不带where条件： 返回包括右表中的所有记录和左表中联结字段相等的记录，如果没有，则显示为空

与left join用法一样

用于合并两个或多个 SELECT 语句的结果集，不会消去表中重复行。

SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2

合并的sql语句不能使用order by 关键字

根据编码或者名称查询租户

用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

union all  +distinct（union all 的去重结果）

SELECT column_name FROM table1 UNION SELECT column_name FROM table2

1、UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

3、如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

当需要取两个不同字段但是相同条件结果集的并集时可以使用

https://dev.mysql.com/doc/refman/8.0/en/union.html

如果要对单个选择使用ORDER BY或LIMIT，则必须用括号将选择内容括起来。

对某个字段或多个字段进行分组

分组统计：求和(根据不同的条件使用不同的求和结果进行累加)

中间表去重

限制返回结果的数目时使用

语法

分页查询数据

限制返回的条数：如1

select * from table_name limit 10000,10 

逻辑

优化

mysql的limit起始坐标是从0开始，后面接查询数量

偏移offset较小时，直接使用limit

偏移offset较大时，直接使用limit

而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果不重复，则执行新纪录插入操作。
另外，ON DUPLICATE KEY UPDATE不能写where条件。

实验一：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含主键

实验二：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含唯一索引

实验三

当用户有多个租户时，设置一个默认租户

插入数据

使用多层for循环插入数据

模糊查询

当需要进行模糊查询时，使用（一般使用左模糊，禁止全模糊查询（全模糊不走索引，全表扫描，性能慢））

用于返回唯一不同的值

SELECT DISTINCT 列名称 FROM 表名称

只能在select语句中使用

必须在所有字段前面

如果有多个字段需要去重，则会对多个字段进行组合去重，即所有字段的数据重复才会被去重

对单个字段去重

对多个字段去重

查看去重字段有多少个值

用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

SELECT … FROM table WHERE EXISTS (subquery)

查询用户表,但是用户的id必须在用户角色表存在

中间表插入数据时，判断插入的编码在其他表中是否存在，如果不存在，则不插入，防止脏数据的产生

允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

普通用法

增加一层temp用法

变化1：使用exists查询代替in查询

变化2：使用连接查询inner join 代替in查询

判断是否为空

如：把不在本角色下的用户查询出来

用于计算条件列表并返回多个可能结果表达式之一

建表

初始化数据

需求：所有数学课程成绩 大于 语文课程成绩的学生的学号

库存中心：获取不同分区的销售库存

在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息

建表

初始化数据

测试

replace into 首先尝试插入数据到表中，
1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2. 否则，直接插入新数据。

插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

建表

测试

会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。
这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

insert ignore into table(name) select name from table2

只插入109的数据（100-108数据库存在，109数据库不存在）

^

$

.

[...]

[^...]

p1|p2|p3

*

+

{n}

{n,m}