mysql/mariadb
2022-04-10 17:16:33 0 举报AI智能生成
登录查看完整内容
mysql数据库相关知识
作者其他创作
大纲/内容
1、通过慢查日志等定位那些执行效率较低的SQL语句
2、通过监控工具监控慢sql:druid连接池
1、定位慢sql
type
rows
Extra
keys
2、explain 分析SQL的执行计划
了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”
3、show profile 分析
4、trace
优化索引
优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
改用其他实现方式：ES、数仓等
数据碎片处理
5、确定问题并采用相应的措施
sql优化
（1）使用可存下数据的最小的数据类型。
（2）使用简单地数据类型，int要比varchar类型在mysql处理上更简单。
（3）尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值。
（4）尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率。
选择合适的数据类型
1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到
表的设计合理化，符合三大范式（3NF)
表设计优化
MySql有能力处理更多的并发的时候， 建议调大这个值，相应给服务器带来更高的负载(CPU/IO/内存)
max_connections 可以设置最大并发连接数
show status like 'threads_connected';
看当前连接数
show status like \"max_used_connections\";
最大使用连接数
连接数(connection)配置
是内存中的一块存储区域，其存储了用户的SQL文本以及相关的查询结果。通常情况下，用户下次查询时，如果所使用的SQL文本是相同的，并且自从上次查询后，相关的纪录没有被更新过，此时数据库就直接采用缓存中的内容。从内存中读取要比从硬盘上速度要快好几百倍
查询缓存
一是所采用的SQL语句是相同的。每次查询的语句不一样，肯定不能用到缓存。比如语句里带当前秒数 where ctime > xxx
二是表数据没有改过。没有改过结构，没有update，insert
三：客户端与服务器的默认字符集得一样
使用条件
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_type
默认是32M，太小了，可调到128M或者256M。 可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载
在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉
query_cache_size
SHOW VARIABLES LIKE '%query_cache%';
查看查询缓存的设置
查询缓存(query_cache)配置
SHOW VARIABLES LIKE '%tmp_table_size%';
临时表缓存(tmp_table_size)配置
索引缓冲区（key_buffer_size）配置
参数优化
CPU并不是越多越好，之前看到网上的分析有说很多的查询都是单CPU的，增加CPU数量并不能提高性能。
cpu
一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。
网络
机械磁盘 or SSD（当然是SSD更快）；单个大磁盘 or 多个小磁盘组合使用（单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取
存储
硬件优化
sharding-jdbc
分库分表
读写分离
es
redis
缓存层
其他
数据库优化
执行student.length次
指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)
如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists
exists
只执行一次
确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快
select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)
in
exists与in什么场景用
select * from student where a=xxx and b=xxx and c=xxxselect * from student where b=xxx and c=xxxselect * from student where a=xxx and b=xxx
建立abc的复合索引
建立bc的复合索引
解决方法
如何建立索引
会回表查询d列的值
sql语句区别
EXPLAIN SELECT a FROM test_index2;
使用a索引
select a 使用的是哪个索引
使用a_b_c 索引
select abc from table group by a
数据库索引，分别给abc， a 创建索引
SELECT * FROM test_index3 WHERE a='a' AND b ='b';
SELECT * FROM test_index3 WHERE a='a' AND c ='c';
SELECT * FROM test_index3 WHERE a='a' AND b ='b' AND c='c';
sql
创建表
创建索引的最佳方式
如何创建最佳的索引
索引相关
效果图
插入数据
方式一：使用if
方式二：使用case when
解决
示例
行列转换
1、使用索引
建立索引可以使查询速度得到提升，我们首先应该考虑在where及order by，group by涉及的列上建立索引。
2、借助explain（查询优化神器）选择更好的索引和优化查询语句
SQL 的 Explain 通过图形化或基于文本的方式详细说明了 SQL 语句的每个部分是如何执行以及何时执行的，以及执行效果。通过
对选择更好的索引列，或者对耗时久的SQL语句进行优化达到对查询速度的优化。
3、任何地方都不要使用SELECT * FROM语句。
4、不要在索引列做运算或者使用函数
5、查询尽可能使用limit来减少返回的行数
6、使用查询缓存，并将尽量多的内存分配给MYSQL做缓存
SQL查询语句优化
实现数据库的读写分离，从而改善数据库的负载压力
主从复制，读写分离，负载均衡
在主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。
垂直拆分
根据一列或者多列数据的值把数据行放到两个独立的表中。
水平拆分
分表
分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。
分库
分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但是数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能。实现比较简单，包括水平分区和垂直分区。
分区
数据库分表、分区、分库
使用缓存
当数据库访问量过大时，怎么办
面试
是数据库的一个ANSI/ISO标准。它定义了一种语言（SQL）以及数据库的行为（事务、隔离级别等）
sql92
select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引
覆盖索引
先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树.
回表
确保每列保持原子性
第一范式
确保表中的每列都和主键相关
第二范式
第三范式
三大范式
内连接（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校对规则，会忽略字符字段最后的空格。
原因
例如查询table表中user（varchar类型）的字段
举例
（1）使用mysql的LIKE查询关键字， SELECT * FROM TABLE WHERE user LIKE 'abc '；
方法一
（2）使用BINARY关键字， SELECT * FROM TABLE WHERE user = BINARY'abc '；
分析：将查询的字段强转为二进制合适后进行查询匹配
方法二
（3）使用LENGTH函数，SELECT * FROM TABLE WHERE user = ‘abc ’ AND LENGTH(user) = LENGTH('abc ')；
分析：在查询条件中增加varchar字段限制条件LENGTH长度的限制，因此对于末尾的空格可以查询出。
方法三
以上三种方法均可以将末尾有空格的varchar字段的记录查询出。
数据存在空格
1、字符串类型的值后面有空格也可以查询出来
建表
删除老师名字相同的行
操作
DELETE FROM lesson WHERE id NOT IN (SELECT MIN(id)FROM lesson GROUP BY teacher);
效果
不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。
DELETE FROM lesson WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) id FROM lesson GROUP BY teacher)t);
select的结果再通过一个中间表select多一次，就可以避免这个错误
2、删除表中重复的记录（按某个条件删除）
select * from t_dict;
所有的字段
增加不存在的字段
3、sql语句中查询用as新增不存在的字段
LIMIT 1000000
查询更新时间最新的一条
使用limit固定字段排序
实现分组排序并取组内第一条数据
自连接
4、Mysql取出每个分组中最新的记录
distinct
group by
row_number() over (partition by <用于分组的字段名> order by <用于组内排序的字段名>)
row_num
计算任务的总数量
5、sql中去重
1、在同一事务内先后对同一条数据进行插入和更新操作；
2、多台服务器操作同一数据库；
3、瞬时出现高并发现象；
问题出现环境
在高并发的情况下，Spring事物造成数据库死锁，后续操作超时抛出异常。
Mysql数据库采用InnoDB模式，默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。
原因分析
SELECT * FROM information_schema.innodb_trx\\G;
kill 线程id
查找到为提交事务的数据，kill掉此线程即可
SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';
增加锁等待时间，即增大下面配置项参数值，单位为秒（s）
解决方案
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
6、事务超时报错
实际遇到的问题
1.可以在sql语句后携带分号，实现多语句执行。
2.可以执行批处理，同时发出多个SQL语句。
作用
allowMultiQueries=true
是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true\t
useUnicode=true
当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk\t
characterEncoding=UTF-8
MySQL在高版本需要指明是否进行SSL连接
useSSL=false
serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
驱动才会帮你批量执行SQL，另外这个选项对INSERT/UPDATE/DELETE都有效
SHOW VARIABLES LIKE 'max_allowed_packet';
SELECT 67108864/1024/1024;
受到max_allowed_packet 参数的限制
实现高性能的批量操作
rewriteBatchedStatements=true
MySql链接url参数的设置
system clear
客户端清屏
check
检查表或视图的有无错误
analyze
用于收集优化器统计信息、和tuning相关
SHOW FULL TABLES FROM xxx
查看表的类型
命令
查询所有数据库的总大小
查看所有数据库各容量大小
查看所有数据库各表容量大小
查看指定数据库容量大小
查看mysql库各表容量大小
查看mysql数据库容量大小
开启慢查询日志，可以让mysql或者mariadb记录执行时超过指定时间的sql语句。
简介
配置/etc/my.cnf
参数说明
开启慢查询日志设置
systemctl restart mysql
重启数据库
show variables like '%quer%';
查看配置是否成功
select sleep(3)
select * from slow_log\\G;
测试
配置慢查询sql
have_profiling:只读变量，用于控制是否有系统变量开启或关闭 profiling
profiling：开启或关系 SQ L语句剖析功能
profiling_history_size：设置保留 profiling 的数据，默认是 15条，范围为 0~100，0 表示将禁用 profiling
show variables like '%profil%';
查看 profiling 系统变量
help show profile
获取 profiling 的帮助信息
SET profiling = 1
启用profile功能
show profiles;
测试验证
show profile for query 4;
通过查询ID(query_id)查看每个操作具体的资源消耗
show profile all for query 4;
查看所有
show profile CPU for query 4;
查看cpu
show profile MEMORY for query 4;
查看内存memory
查看不同资源开销
查看开销
通过查询information_schema.PROFILING查询相关信息
利用 profile 分析 SQL 语句的执行过程
显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。
含义
show full processlist
show processlist
使用
方式
①.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语句，是判断问题语句的一个重要依据
列含义解释
SELECT * FROM information_schema.PROCESSLIST WHERE info IS NOT NULL
查询正在执行的sql线程
常用方式
查看数据库线程连接(20210316)
在使用mysql运行某些语句时，会因数据量太大而导致死锁，没有反映。这个时候，就需要kill掉某个正在消耗资源的query语句即可
why
每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。
SHOW PROCESSLIST;
查看进程
终止与给定的thread_id有关的连接
KILL CONNECTION
会终止连接当前正在执行的语句，但是会保持连接的原状
KILL QUERY
KILL [CONNECTION | QUERY] thread_id
语法
kill 进程号
客户端
kill
trx_state: 事务状态，一般为RUNNING
trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
trx_mysql_thread_id: MySQL的线程ID，用于kill
trx_query: 事务中的sql
列
查看线程
kill线程
select * from information_schema.innodb_trx\\G
数据库查看未提交的事务（20210318）
（1）每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；
（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；
产生原因
Data_free列的值就是碎片大小
SHOW TABLE STATUS LIKE '表名';
查看某个表的碎片大小
列出所有已经产生碎片的表
查看表碎片大小
alter table 表名 engine=InnoDB
InnoDB表
用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。
optimize table 表名
MyISAM表
清除表碎片
清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行
建议
清除表空间碎片（20210318）
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 ;
备份表结构和数据
备份表
运用
主从复制
指定库和表
hint
复合分片
complex
标准分片
standard
inline
precise 精准分片
分库分表策略
分库分表算法
分布式id
主键唯一
分布式事务
跨库join
产生的问题
解决的问题
默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中
配置
查看
my.cnf
错误日志配置
服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
事件调度器运行一个事件时产生的信息
在从服务器上启动从服务器进程时产生的信息
错误日志记录信息
flush logs
数据库管理员可以删除很长时间之前的错误日志
mysql5.5.7之前
mv mysql.err mysql.err_20220320
重命名文件
刷新日志
cat mysql.err
查看日志
服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的
mysql5.5.7之后
删除错误日志
错误日志
查询日志在MySQL中被称为general log(通用日志)，查询日志里面记录了数据库执行的所有命令
参数general_log用来控制开启、关闭MySQL查询日志
参数general_log_file用来控制查询日志的位置
FILE : 表示日志存储在文件中
TABLE : 表示日志存储在mysql库中的general_log表中
NONE : 表示不记录日志，即使general_log设置为ON， 如果log_output设置为NONE，也不会记录查询日志
参数log_output控制着查询日志的存储方式
参数
show variables like '%general_log%';
查看是否开启
show variables like 'log_output';
查看存储方式
配置my.cnf
重启mysql实例
在配置文件中设置
set global general_log=1
set global log_output='table';
通过命令设置（短暂时间生效，重启之后失效）
开启
开启并存储到表中
查询、更新、删除sql
select * from mysql.general_log\\G
查看日志表general_log
delete from general_log;
报错
SET GLOBAL general_log = 'OFF';
关闭
RENAME TABLE mysql.general_log TO mysql.general_log2;
重命名表为临时表
DELETE FROM mysql.general_log2;
删除表中内容
OPTIMIZE TABLE general_log2;
清理表
RENAME TABLE mysql.general_log2 TO mysql.general_log;
重命名表为原来的表
SET GLOBAL general_log = 'ON';
关闭查询日志，重命名表即可
删除mysql.general_log内容
set global general_log=0
通过命令设置（即时生效，不需重启），重启数据库实例又会恢复为原值。
设置
会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的
查询日志
慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。
show variables like \"%slow%\";
slow_query_log: 慢查询开关，表示是否打开慢查询日志
long_query_time: 慢查询指定时间设置，表示\"多长时间的查询\"被认定为\"慢查询\"，单位是秒(s)，默认是10s
log_queries_not_using_indexes: 表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询记录中，OFF表示不记录，ON表示记录。
slow_query_log_file: 当使用文件存储慢查询日志时(log_output设置为\"FILE\"或者\
log_throttle_queries_not_using_indexes: MySQL5.6.5版本新引入的参数，用来限制没有使用索引的语句每分钟记录到慢查询日志中的次数。在生产环境中，有可能有很多没有使用索引的语句，可能会导致慢查询日志快速增长。
set global slow_query_log='ON';
开启慢查询
show variables like 'long_query_time';
set long_query_time=0.01
慢查询时间设置
set global log_output = FILE;
select sleep(2);
cat /data/mysql/zwb-slow.log
文件
show variables like \"log_output\";
select sleep(1);
select * from slow_log\\G
表
慢查询内容
慢查询日志
用于记录 数据修改后的记录，顺序记录
当buffer pool中的dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
buffer pool中的数据直接flush到disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；
假设修改 tba 表中 id=2的行数据，把Name='B' 修改为Name = 'B2' ，那么redo日志就会用来存放Name='B2'的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。
内存中的日志缓冲(redo log buffer)，该部分日志是易失性的
是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高(磁盘的顺序写性能逼内存的写性能差不了太多)
组成
redo log 文件的个数，命名方式如：ib_logfile0，iblogfile1... iblogfilen。默认2个，最大100个。
innodb_log_files_in_group
文件设置大小，默认值为 48M，最大值为512G，注意最大值指的是整个 redo log系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。
innodb_log_file_size
文件存放路径
innodb_log_group_home_dir
Redo Log 缓存区，默认8M，可设置1-8M。延迟事务日志写入磁盘，把redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit参数的设置，再把日志从buffer 中flush 到磁盘中。
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
show variables like 'sync_binlog';
sync_binlog
重做日志redo
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
提供回滚和多个行版本控制(MVCC)。
innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。
undo log的存储方式
show global variables like '%undo%';
回滚日志undo
事务日志(Redo log)
主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。
恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。
binlog对于事务存储引擎的崩溃恢复也有非常重要的作用
binlog的作用
为了管理所有的binlog文件，MySQL额外创建了一个base-name.index文件，它按顺序记录了MySQL使用的所有binlog文件。
binlog index文件
show global variables like 'log_bin';
打开binlog日志
log_bin=ON
binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_basename=/var/lib/mysql/mysql-bin
指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
log_bin_index=/var/lib/mysql/mysql-bin.index
方式一
log-bin=/var/lib/mysql/mysql-bin
方式二
配置文件开启：my.cnf
/var/lib/mysql
查看文件
binlog的开启
记录的是数据库上执行的原生SQL语句
STATEMENT
基于行的复制，也就是基于数据的复制，基于行的更改
ROW
MIXED也是MySQL默认使用的二进制日志记录方式，但MIXED格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到UUID()、USER()、CURRENT_USER()、ROW_COUNT()等无法确定的函数。
MIXED
binlog格式
限定单个binlog文件的大小（默认1G）
max_binlog_size
当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K
binlog_cache_size
sync_binlog=0
sync_binlog=1
sync_binlog=N
在MySQL 5.7之前版本默认情况下，二进制日志并不是在每次写的时候同步的磁盘
binlog-do-db&binlog-ignore-db
binlog_format参数十分重要，用来设置二进制日志的记录格式
binlog-format
默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建。
log_bin_trust_function_creators
binlog的相关参数
二进制日志（binary log）
是复制过程中产生的日志
标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
max_relay_log_size
定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn
relay_log
定义relay_log的位置和名称
relay_log_index
relay_log_info_file
是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_purge
relay_log_recovery
中继日志
日志（20210320）
一系列逻辑相关的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消
事务概念
事务是数据库的逻辑工作单位，不可分割，事务中包含的各操作要么都做，要么都不做
原子性（Atomicity，或称不可分割性）
从一个一致性状态变到另一个一致性状态
一致性（Consistency）
事务与事务之间是隔离的，并发执行的各个事务之间不能互相干扰
隔离性（Isolation，又称独立性）
一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的，不能回滚
持久性（Durability）
事务的特征
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
事务操作命令
insert into city(name) values('武汉市');
新增数据
演示案例准备工作
select @@tx_isolation
查看当前会话隔离级别
select @@global.tx_isolation;
SHOW VARIABLES LIKE 'transaction_isolation';
查看系统当前隔离级别
查询
set session transaction isolation level READ UNCOMMITTED;
设置当前会话隔离级别
set global transaction isolation level READ UNCOMMITTED;
设置系统当前隔离级别
隔离级别命令操作
一个事务读到了另一个未提交事务修改过的数据
脏读
问题
会话1
会话2
读未提交（Read uncommitted）
一个事务能读到另一个已经提交的事务修改过的数据
不可重复读重点在于update和delete
不可重复读
读提交（read committed）
一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。
幻读的重点在于insert
幻读
测试1（此问题数据库已解决）
ALTER TABLE `ds`.`city` CHANGE `id` `id` INT(10) NOT NULL;
修改数据库主键id
测试2
可重复读（repeatable read）（mysql默认级别）
强制事务串行执行
串行化（Serializable）
四种隔离级别
事务的隔离级别
通过维护数据历史版本（版本链），从而解决并发访问情况下的读一致性问题
快照读
select ... lock in share mode
select ... for update
insert
update
delete
当前读（读取最新的数据）
写写之间相互阻塞
读写，写读、读读之间可以并发
解决问题
所有事务直接读取数据库的最新值
读未提交
每次读取数据前都生成一个ReadView (m_ids列表)
读已提交
在事务开始后第一次读取数据时生成一个ReadView（m_ids列表）
可重复读
所有请求都会加锁，同步执行
串行化
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
前提
6字节的事务ID(DB_TRX_ID)字段
指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。
7字节的回滚指针(DB_ROLL_PTR)字段
6字节的DB_ROW_ID字段
三个字段
insert 操作中产生的undo log，因为insert操作记录只对当前事务本身可见，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行purge操作。
insert undo log
update 或 delete 操作中产生的 undo log。 因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。
update undo log
undo log
如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。
如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。
read view
实现
Multi Version Concurrency Control的简称)，代表多版本并发控制
mvcc
事务(20210308)
计算索引长度可以判断执行计划中的复合索引真正生效的是哪些字段。
说明
EXPLAIN SELECT * FROM test_index_length WHERE id = 1; -- key_len 4
EXPLAIN SELECT * FROM test_index_length WHERE NAME = 'aa'; -- key_len 20
EXPLAIN SELECT * FROM test_index_length WHERE addr = 'bb'; -- key_len 10*2+2+1
EXPLAIN SELECT * FROM test_index_length WHERE name='a' AND addr='b'; -- key_len 10*2+10*2+2(varchar字段建立索引需要2字节)+1(NULL1字节)=43
EXPLAIN SELECT * FROM test_index_length WHERE last_name='a' AND addr='b'; -- key_len 23+23=46
EXPLAIN select * FROM test_index_length WHERE date1= '2017-02-15'; -- key_len 3+1
EXPLAIN select * FROM test_index_length WHERE time1= '10:00:00'; -- key_len 3+1
EXPLAIN select * FROM test_index_length WHERE timestamp1= '2017-02-15 11:00:00'; -- key_len 4
EXPLAIN select * FROM test_index_length WHERE datetime1= '2017-02-15 12:00:00'; -- key_len 5+1
计算
1.所有的索引字段，如果没有设置not null，则需要加一个字节。
3.对于变长字段varchar(n)，则有n个字符+两个字节。
总结
MySQL 索引长度计算
会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立hash索引
hash索引
b树索引
根据底层数据结构划分
单个字段组成的索引
单值索引
多个字段组成的索引
复合索引/组合索引或多值索引
根据索引字段个数划分
根据主键来组织数据的，所以每张表都必须有主键索引
主键索引只能有一个
不能为null同时必须保证唯一性
建表时如果没有指定主键索引，则会自动生成一个隐藏的字段作为主键索引。
主键索引
如果不是主键索引，则就可以称之为非主键索引，又可以称之为辅助索引或者二级索引
辅助索引
根据是否是在主键上建立的索引进行划分
Innodb的主键索引，非叶子节点存储的是索引指针，叶子节点存储的是既有索引也有数据，是典型的聚簇索引（这里可以发现，索引和数据的存储顺序是强相关的。因此是典型的聚簇索引）
聚簇索引
MyISAM中索引和数据文件分开存储，B+Tree的叶子节点存储的是数据存放的地址，而不是具体的数据，是典型的非聚簇索引
数据可以在磁盘上随便找地方存，索引也可以在磁盘上随便找地方存，只要叶子节点记录对了数据存放地址就行
索引存储顺序和数据存储关系毫无关联，是典型的非聚簇索引，另外Inndob里的辅助索引也是非聚簇索引
非聚簇索引
根据数据与索引的存储关联性划分
不允许具有索引值相同的行，从而禁止重复的索引或键值
唯一索引
全文索引
其他分类
innodb存储引擎
索引分类
数据
索引创建
案例准备
查看key这一列的值，如果为NULL，说明没有使用索引。
explain关键字
分析工具
类型转换，字符串不加单引号（隐式类型转换
like中以%开头
索引列使用了函数
or语句前后没有同时使用索引
where中在索引字段上使用not，<>，!=
where中索引列有运算
场景
索引失效的场景
数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
频繁更新的字段不要使用索引
where 子句里对索引列使用不等于（<>），使用索引效果一般
什么情况下不推荐使用索引
EXPLAIN SELECT * FROM test WHERE c2='22' AND c3='33' AND c4='44';
回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低
与回表查询相对应
最左前缀匹配规则
索引生效
4）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。
9）对于经常存取的列避免建立索引
索引建立的原则（2021-05-24）
索引(20210305)
函数(Function)
存储过程
视图
触发器
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
锁粒度
乐观锁
悲观锁
加锁机制
分类
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
模式
对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
行为
set autocommit =0;
show variables like 'autocommit';
默认提交设置为false
示例准备
执行查询语句（SELECT）前，会自动给涉及的所有表加读锁
在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁
默认加锁
当前读锁之间只能进行读，不能进行写操作
当前读锁只能读当前锁定的表，不能读未被锁定的表
当表加了读锁并且表不存在空闲块的时候（删除或者更新表中间的记录会导致空闲块，OPTIMIZE TABLE可以清除空闲块），MYISAM默认允许其他线程从表尾插入。可以通过改变系统变量concurrent_insert（并发插入）的值来控制并发插入的行为
可以插入操作的原因
另一个事务的update操作被阻塞，直到解锁操作
lock tables test_mysiam_lock read local;
读锁
当前事务可以读写操作，其他事务读写都阻塞
lock tables test_mysiam_lock write ;
写锁
加锁
unlock tables;
解锁
锁表的时候加了LOCAL关键字表示允许走并发插入的逻辑，具体是否可以并发插入还需要看是否满足concurrent_insert指定的条件，只有手动锁表的时候才需要指定LOCAL关键字。
注意
显式锁
NEVER(0): 不允许并发插入
AUTO(1): 表里没有空行时允许从表尾插入（默认）
ALWAYS(2): 任何时候都允许并发插入
值范围
SHOW VARIABLES LIKE 'concurrent%';
并发锁
锁定等待时间
show status like '%table_locks_waited%';
show status like '%table_locks_immediate%';
查询表级锁争用情况
MyISAM表锁
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
官方文档
多个事务对于同一数据可以共享一把锁，都能访问到数据库，但是只能读不能修改；
可以对同一条数据加共享锁
共享锁不影响另一个事务读取数据
共享锁情况下不能修改数据
结论
select * from test_innodb_lock where id =1 lock in share mode;
rollback/commit;
释放
S Lock（共享锁）
排它锁不能与其他锁并存，如一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的锁（包括共享锁和排它锁），只有当前获取了排它锁的事务可以对数据进行读取和修改（此时其他事务要读取数据可从快照获取）
delete update insert 默认加排他锁
排他锁情况下其他事务无法进行修改操作，可以进行读操作
select * from test_innodb_lock where id =1 for update ;
InnoDB的行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件进行数据检索，Innodb才使用行级锁。否则，将使用表锁（锁住索引的所有记录）。
id作为主键
X Lock（排他锁）
是 InnoDB 引擎默认的行锁算法.
划分区间
锁住的区间
select * from test_innodb_lock where id>5 and id<9 for update;
临键锁 Next-Key locks
在上述检索条件下，如果没有命中记录，则退化成Gap锁，锁住数据不存在的区间（左开右开）
效果一
效果二
select * from test_innodb_lock where id >3 and id<7 for update;
间隙锁 Gap
id=3这条记录
select * from test_innodb_lock where id=3 for update;
记录锁 Record Lock
行锁的算法
InnoDB的行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件进行数据检索，Innodb才使用行级锁。否则，将使用表锁（锁住索引的所有记录）。<br>
行锁
表示事务准备给数据行加入共享锁，也就是说一个数据行在加共享锁之前必须先取得该表的IS锁
意向共享锁
表示事务准备给数据行加入排它锁，也就是说一个数据行加排它锁之前必须先取得该表的IX锁。
意向排他锁
意向锁是InnoDB数据操作之前自动加的，不需要用户干预
当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速的返回，告知该表不能启用表锁（也就是会锁住对应会话），提高了加锁的效率。
意义
表锁
加 X 锁 避免了数据的脏读
加 S 锁 避免了数据的不可重复读
加上 Next Key 避免了数据的幻读
正是通过上述不同类型的锁，完成了事务隔离
锁总结
innodb锁
锁（20210327）
高级
字段(包括表和实体)不能用is_开头
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) 修改用户
每张表包含8个必要字段()
gmt_ 代表默认 gmt 时区，加上created 可以明确的表述 一个时间的完整定义， create time 字面意思里面看不到时区，具体什么时区是看不到的
创建时间更新时间
物流规范
表规范
对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
1、from
对虚表VT1进行ON筛选，只有那些符合join-condition的行才会被记录在虚表VT2中
2、on
3、join
对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
4、where
根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
5、group by
对表VT5进行cube或者rollup操作，产生表VT6
6、cube |rollup
对虚拟表VT6应用having过滤，只有符合having-condition的记录才会被 插入到虚拟表VT7中。
7、having
执行select操作，选择指定的列，插入到虚拟表VT8中。
8、select
对VT8中的记录进行去重。产生虚拟表VT9.
9、distinct
将虚拟表VT9中的记录按照order_by_list进行排序操作，产生虚拟表VT10.
10、order by
11、limit
语句执行顺序
分支主题
1、MySQL默认情况下是否区分大小写，使用show Variables like '%table_names'查看lower_case_table_names的值，0代表区分，1代表不区分
2、mysql对于类型为varchar数据默认不区分大小写，但如果该字段以“*_bin”编码的话会使mysql对其区分大小写。
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、mysql对于表名的策略与varchar类型数据相同。即：默认不区分大小写，但如果该表是以“*_bin”编码的话会使mysql对其区分大小写。
5、如果按照第一项查看lower_case_table_names的值为0，但需要让mysql默认不区分大小写的话，需要在mysql配置文件中添加参数并重启mysql数据库
mysql 对表大小写敏感问题
创建时间
更新时间
创建时间和更新时间添加默认值
增加一列固定值
查询时为数据库添加一列数据库不存在的字段
特殊语法
create table
drop table
alter table
table 表
create view
drop view
view 视图
create index
drop index
index 索引
create procedure
drop procedure
procedure 存储过程
create trigger
drop trigger
trigger 触发器
drop schema
schema
create domain
alter domain
drop domain
domain
数据定义
select
插入
删除
更新
数据操作
GRANT ALL ON *.* TO 'canal'@'%';
授予用户访问权限
FLUSH PRIVILEGES;
刷新权限 权限更新后刷新才会起作用
grant
拒绝用户访问
deny
解除用户访问权限
revoke
CREATE USER 'canal' IDENTIFIED BY '123456';
创建用户
SELECT * FROM USER WHERE `user` = 'canal';
查看 canal的权限
create
数据控制
提交当前事务
commit
回滚当前事务
rollback
原子性
一致性
隔离性
持久性
设置事务隔离级别
SHOW GRANTS FOR root@localhost;
查看隔离级别
set transaction
事务的特性
事务控制
declare
explain
open
fetch
close
prepare
execute
describe
程序化SQL
declare @id
set @id
set
select @id
局部变量
wait_timeout
SHOW VARIABLES LIKE '%time%';
查询超时时间
子主题
show
sql语句常用关键字
mysql5.5版本之后，默认innodb引擎
（1）灾难恢复性比较好；
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键；
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
特点
建库建表
用来记录该库的默认字符集编码和字符集排序规则用的
db.opt
innodb的表结构文件
test_innodb.frm
innodb的表数据文件
test_innodb.ibd
文件目录
存储引擎的索引和数据是在一起
innodb
在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务
（1）不支持事务；
（2）不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
（3）对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；
（4）默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
（5）支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
（6）数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；
数据文件
test_mysiam.MYD
索引文件
test_mysiam.MYI
表结构文件
test_mysiam.frm
Myisam存储引擎索引和数据是分开
mysiam
1、由于锁粒度的不同，InnoDB比MyISAM支持更高的并发；
2、InnoDB为行级锁，MyISAM为表级锁，所以InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
3、在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案；
4、查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；
5、SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要朱行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；
InnoDB和MyISAM的对比
1、使用场景是否需要事务支持；
2、是否需要支持高并发，InnoDB的并发度远高于MyISAM；
3、是否需要支持外键；
4、是否需要支持在线热备；
5、高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
6、索引，不同存储引擎的索引并不太一样；
如何选择合适的存储引擎
show engines\\G
支持的存储引擎
show variables like '%storage_engine%';
当前默认的存储引擎
show create table 表名;
看某个表用了什么引擎
存储引擎（20210318）
1字节
大小
范围（有符号）
范围（无符号）
tinyint
2字节
smallint
3字节
MEDIUMINT
4字节
int/integer
bit[(m)]
8字节
bigint
float
double
DECIMAL数据类型用于在数据库中存储精确的数值。经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据
释义
P是表示有效数字数的精度。 P范围为1〜65。
D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。
在这种情况下，列不包含小数部分或小数点。
column_name DECIMAL(P);
在这种情况下，P的默认值为10。
column_name DECIMAL;
经常使用DECIMAL数据类型的货币数据，如价格，工资，账户余额等。如果要设计一个处理货币数据的数据库，则可参考以下语法
如果您要遵守公认会计原则(GAAP)规则，则货币栏必须至少包含4位小数，以确保舍入值不超过$0.01。 在这种情况下，应该定义具有4位小数的列，如下所示
DECIMAL数据类型和货币数据
数字
date
时间范围：‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ ，不支持时区，8字节存储
datetime
time
时间范围是：‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC ，自动时区转化，实际存储毫秒数，4字节存储
timestamp
year
日期和时间
text
char
varchar
tinyblob
tinytext
blob
mediumblob
mediumtext
longblob
longtext
字符串
enum枚举
set集合
数据类型
获取不在本角色下的用户
案例1：mapper/custom/UserRoleMapperExtend.xml:135
案例
案例准备sql
SQL执行的顺序的标识
iner join/left join
从上到下
执行顺序
id相同
子查询
越大的越先执行
id不同
子查询+join连接
越大的越先执行，如果相同，则从上到下执行
id相同，也有不同
id
示查询中每个select子句的类型
SIMPLE\t\t简单的select查询，查询中不包含子查询或者UNION
PRIMARY\t\t查询中若包含任何复杂的子部分，最外层查询标记
SUBQUERY\t在SELECT或WHERE列表中包含了子查询
DERIVED\t\t在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表\t
UNION RESULT\t从UNION表获取结果的SELECT
类型
select_type
table
表示MySQL在表中找到所需行的方式，又称“访问类型”。
非索引范围查找也是走全表
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
如
增加索引
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行，索引扫描范围
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
增加角色扩展表
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
possible_keys
key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
key_len
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
ref
表示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在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
EXPLAIN SELECT * FROM USER WHERE id=1 OR id=2;
EXPLAIN SELECT * FROM USER WHERE id=1 UNION ALL SELECT * FROM USER WHERE id=2;
EXPLAIN SELECT * FROM tenant t WHERE t.`tenant_code`='annto' UNION ALL SELECT * FROM tenant t WHERE t.`tenant_name` LIKE 'annto%';
EXPLAIN SELECT * FROM tenant t WHERE t.`tenant_name` LIKE 'annto' OR t.`tenant_code`='annto';
or与union all的区别
session级的优化器参数
直接一次查询出所有结果
使用多线程合并分页的结果
for循环遍历单个更新
jdbc-url需要开启批量操作
拼接
foreah
xml
使用批量一次性更新
使用Lists.partition方法对集合进行切分，分多次批量更新
for循环遍历单个插入
使用批量一次性插入
在批量插入3000条的数据量的情况下，性能提升在8到10倍左右
使用Lists.partition方法对集合进行切分，分多次批量插入
批量数据优化（20210522）
优化
NOW() 存储到datetime字段类型中
sysdate() 存储到datetime字段类型中
curdate() 存储到date字段类型中
curtime() 存储到time字段类型中
获取现在的时间
date 是 DATE 或 DATETIME 的起始值。
expr 是一个字符串，用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位，例如 DAY，HOUR 等
时间的减法运算
返回值是相差的天数，不能定位到小时、分钟和秒。
DATEDIFF
有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。
TIMESTAMPDIFF
时间差函数
https://www.cnblogs.com/airen123/p/11015200.html
时间转换为字符串
字符串转换为时间
时间转换
日期时间函数
将group by产生的同一个分组中的值连接起来，返回一个字符串结果
功能
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
使用group_concat()和group by显示相同名字的人的id号：
将上面的id号从小到大排序，且用'_'作为分隔符
group_concat
字符串连接函数
lower(String)
upper(String)
字符串大小写
字符串截取
注意：长度n可以是小于或等于string字符串长度的值，此时lpad或者rpad的作用都是从左进行字符串截取而非填充，直到长度为n。也就是说lpad和rpad函数最强约束条件是长度参数n。
填充函数
trim(String)
ltrim(String)，rtrim(String)
trim(substring from string)
去除空格
重复字符串
字符串函数
abs(x)
绝对值函数
取模函数
四舍五入函数
位数截断函数
rand()
随机函数
最值函数
数学函数
case when ...then...else...end
对库存中不同库存类型的进行横向展示（数据库不同的库存类型是分行显示）
使用场景
case ...when...then...else...end
case语句
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值
NVL（表达式1，表达式2）（mysql不支持，mariadb支持）
空值转换函数
流程控制之条件判断函数
类型转换函数
对某个单据占用汇总求和
求和
sum
统计某列的最大值
最大值
max
去除重复数据
最小值
min
统计数量
计数
count
求平均值
avg
聚合函数
其它实用函数
数据库函数
不带where条件：左表的全部，右表符合条件的记录，如果没有，则显示为空
1、以中间表的查询结果作为结果集：查询角色下所有的用户
2、以中间表的查询结果作为结果集：查询用户下所有的角色
left join
不带where条件：包括左表中的所有记录和右表中联结字段相等的记录
1、判断同一个租户下所有的应用是否有相同的角色名称
inner join
和inner join 相同
join
不带where条件： 返回包括右表中的所有记录和左表中联结字段相等的记录，如果没有，则显示为空
与left join用法一样
right join
用于合并两个或多个 SELECT 语句的结果集，不会消去表中重复行。
SELECT column_name FROM table1UNION ALLSELECT column_name FROM table2
用法
合并的sql语句不能使用order by 关键字
根据编码或者名称查询租户
union all
用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
union all +distinct（union all 的去重结果）
SELECT column_name FROM table1UNIONSELECT column_name FROM table2
1、UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同
注意事项
取第一前缀和第二前缀都为常数时，常数项不能重复
当需要取两个不同字段但是相同条件结果集的并集时可以使用
https://dev.mysql.com/doc/refman/8.0/en/union.html
参考官方语法
可能结果1
可能结果2
可能结果3
如果在每个结果集后面跟上limit的话 union 不知道到底取哪些结果
如果要对单个选择使用ORDER BY或LIMIT，则必须用括号将选择内容括起来。
使用限制
union
对某个字段或多个字段进行分组
查询在线实例数
已做分库分表，只能单表查询
汇总结果为0的数据需要过滤掉（type=1占用，type=2释放）
需要分页查询
表结构：stc_inv_occupy_dtl_bill
根据来源单据号查询占用值不为0的汇总记录(库存中心-2020-11-30)
分组统计：求和(根据不同的条件使用不同的求和结果进行累加)
用户拥有租户的哪些应用
中间表去重
group by
限制返回结果的数目时使用
初始记录行的偏移量是 0(而不是 1)
第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目
分页查询数据
限制返回的条数：如1
1.从数据表中读取第N条数据添加到数据集中
2.重复第一步直到 N = 10000 + 10
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 10 条数据
逻辑
探究
分页查询10条数据
mysql分页
oracle分页
mybatisplus分页工具类 IPage
mybatis分页
mysql的limit起始坐标是从0开始，后面接查询数量
普通分页查询(176w的数据量)
偏移offset较小时，直接使用limit
通过主键id查询
使用join
子查询的分页
偏移offset较大时，直接使用limit
优化（2021-05-24）
limit
而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件。
原来存在主键4 把4更新成了后面的的数据
前面的id不存在，则直接插入前面的数据
插入的id存在，则更新成后面的数据
insert语句中未包含主键，执行插入操作
实验一：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含主键
前面插入的不存在，则直接插入
前面插入的唯一索引列数据存在，则更新
insert语句中未包含唯一索引，执行插入操作
实验二：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含唯一索引
实验三
唯一索引
租户应用默认表
当用户有多个租户时，设置一个默认租户
INSERT INTO ... ON DUPLICATE KEY UPDATE
使用多层for循环插入数据
insert into
模糊查询
当需要进行模糊查询时，使用（一般使用左模糊，禁止全模糊查询（全模糊不走索引，全表扫描，性能慢））
like
用于返回唯一不同的值
SELECT DISTINCT 列名称 FROM 表名称
语法格式
只能在select语句中使用
必须在所有字段前面
如果有多个字段需要去重，则会对多个字段进行组合去重，即所有字段的数据重复才会被去重
对单个字段去重
对多个字段去重
查看去重字段有多少个值
用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
SELECT … FROM table WHERE EXISTS (subquery)
中间表插入数据时，判断插入的编码在其他表中是否存在，如果不存在，则不插入，防止脏数据的产生
exists/not exists
允许我们在 WHERE 子句中规定多个值。
SELECT * FROM USER WHERE id IN (SELECT user_id FROM user_role );
普通用法
SELECT * FROM USER WHERE id IN (SELECT user_id FROM(SELECT user_id FROM user_role)temp );
增加一层temp用法
SELECT * FROM USER u WHERE EXISTS(SELECT 1 FROM user_role ur WHERE ur.user_id=u.id)
变化1：使用exists查询代替in查询
SELECT DISTINCT u.* FROM USER u INNER JOIN user_role ur ON u.`id`=ur.user_id;
变化2：使用连接查询inner join 代替in查询
in/not in
判断是否为空
如：把不在本角色下的用户查询出来
is null
用于计算条件列表并返回多个可能结果表达式之一
初始化数据
方式一：if
方式二：case when
需求：所有数学课程成绩 大于 语文课程成绩的学生的学号
分页查询分区库存
库存中心：获取不同分区的销售库存
case when else end
一个语句的查询结果作为参数作为插入
在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息
根据年龄段汇总
带roll up 的汇总
汇总列显示总计
with rollup（2021-0106）
replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。
插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
replace into（2021-0106）
会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
insert ignore into table(name) select name from table2
只插入109的数据（100-108数据库存在，109数据库不存在）
insert ignore into（2021-0107）
匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\' 或 '\' 之后的位置。
^
匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\' 或 '\' 之前的位置。
$
匹配除 \"\\" 之外的任何单个字符。要匹配包括 '\' 在内的任何字符，请使用像 '[.\]' 的模式。
.
字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 \"plain\" 中的 'a'。
[...]\t
负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 \"plain\" 中的'p'。
[^...]
匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 \"z\" 或 \"food\"。'(z|f)ood' 则匹配 \"zood\" 或 \"food\"。
p1|p2|p3\t
匹配前面的子表达式零次或多次。例如，zo* 能匹配 \"z\" 以及 \"zoo\
*
匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 \"zo\" 以及 \"zoo\"，但不能匹配 \"z\
+
n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 \"Bob\" 中的 'o'，但是能匹配 \"food\" 中的两个 o。
{n}\t
m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
正则模式
使用 REGEXP 操作符来进行正则表达式匹配（20210222）
关键字语法
mysql/mariadb
职业：本科
0 条评论
回复 删除
下一页
