从前慢现在也慢

这个屌丝很懒，什么也没留下！

热门标签

From zero to hero —— MySQL

作者：从前慢现在也慢 | 2024-08-22 19:30:51

踩

1、MySQL语句

DDL

数据库操作


查询所有数据库
show databases;
 
查询当前数据库
select database();
 
创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
 
删除数据库
drop database [if exists] 数据库名;
 
切换数据库
use 数据库名;

表操作


查询当前数据库所有表
show tables;
 
查看指定表结构
desc 表名;
 
查询指定表的创建语句
show create table 表名;
 
创建表
/*
常见数据类型
tinyint，smallint，mediumint，int，bigint —— 区别在于字节数和表示范围
float，double —— 区别在于字节数和精度
decimal —— 精度要求高时使用
char，varchar —— 区别在于前者定长后者不定长
data，time，year，datatime，timestamp
*/
create table 表名(
    字段1 类型 [comment 字段1注释],
    字段2 类型 [comment 字段2注释],
    ......
    字段n 类型 [comment 字段n注释]
) [engine=存储引擎] [comment 表注释];
 
表添加字段
alter table 表名 add 字段名 类型 [comment 注释] [约束];
 
修改表字段数据类型
alter table 表名 modify 字段名 新数据类型;
 
修改表字段名和数据类型
alter table 表名 change 旧字段名 新字段名 类型 [comment 注释] [约束];
 
删除字段
alter table 表名 drop 字段名;
 
修改表名
alter table 表名 rename to 新表名;
 
删除表
drop table [if exists] 表名;
 
删除指定表并重新创建（数据会被清空）
truncate 表名;

DML


表中指定字段添加数据
--日期类型添加数据时，数据用单引号
--给全部字段添加数据时可以省略字段名的选择
--在values之后加【,(值1,值2,....)】可以批量添加数据
insert into 表名(字段名1,字段名2,....) values (值1,值2,....);
 
修改数据
update 表名 set 字段名1=值1,字段名2=值2,...... [where 条件];
 
删除数据
--不加条件会删除表中所有数据
delete from 表名 [where 条件];

DQL


--基本语法
select 
    字段列表 
from 
    表名列表 
 
[use index(索引名)] --建议使用某个索引进行查询（可能不用）
[ignore index(索引名)] --忽略指定索引
[force index(索引名)] --强制使用某个索引
 
where 
    条件列表
group by 
    分组字段列表
having 
    分组后条件列表
order by
    排序字段列表
limit
    起始索引,查询记录数
; 
 
--执行顺序
from -> where -> group by -> having -> select -> order by -> limit

查询语句中一些知识点：

as可以起别名，dinstinct可以去重，between...and...含左且含右，null值不参与聚合函数运算。

where进行分组前过滤，having进行分组后过滤，where不能对聚合函数进行判断，having可以。

asc升序，desc降序，默认升序。

DCL


查询用户
select * from mysql.user;
 
创建用户
create user '用户名'@'主机名' identified by '密码';
 
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
 
删除用户
drop user '用户名'@'主机名';
 
查询权限
show grants for '用户名'@'主机名';
 
授予权限
-- 权限列表：all，select，insert，update，delete，create，alter，drop
-- 可以用*代替数据库名.表名
grant 权限1,权限2,... on 数据库名.表名 to '用户名'@'主机名';

函数

约束

2、索引

索引结构

索引使用B+树，原因在于：

比起使用哈希，哈希不支持范围查询和排序操作。

比起使用红黑树/平衡二叉树，B+树的整体高度更低，检索速度更快；

比起使用B树，由于B+树非叶节点不存储数据，因此在检索时可以往内存中读入更多非叶节点，减少检索过程的磁盘IO次数，提高检索速度，并且B+树的叶子节点使用双向链表连接，检索范围内数据更快。

聚集索引和非聚集索引

聚集索引的叶子节点下的数据是对应行的数据；非聚集索引又称二级索引，其叶子节点下的数据是对应的聚集索引的key。

如果先使用非聚集索引找到key，再利用key使用聚集索引找到对应数据，这个过程称为回表查询。

聚集索引必须要有，二级索引可以没有也可以存在多个。

如果存在主键，主键索引就是聚集索引；如果不存在主键，使用第一个唯一索引作为聚集索引；如果不存在主键且没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚集索引。

索引的使用

最左前缀法则

最左前缀法则指的是如果创建了联合索引，那么当执行select语句时，select查找的条件里要含有联合索引的第一个字段（出现即可，不必写在第一个），并且如果联合索引里的某个字段没有作为查找条件，那么该字段后面的其他索引字段都会失效。

通过explain语句执行结果的key_len判断联合索引中某个索引字段是否生效。

范围查询

联合索引中，查询条件如果出现范围查询，那么该条件对应的索引字段后面的剩余索引字段失效。

索引列运算

单列索引中，如果对索引字段进行函数运算操作后，索引失效。如：


-- 使用substring函数，phone列的索引失效
explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

联合索引或单列索引中，如果一个字段是字符串类型，但是查询时字段值不加单引号，那么该字段对应的索引失效。如果为联合索引，只会导致对应的某个联合索引字段失效。

模糊查询

如果仅是尾部进行模糊查询，索引依旧生效，如果头部模糊查询，索引失效。

or连接条件

查询时，条件使用了or关键字，如果有某个字段没有创建索引，那么所有的索引都失效。

覆盖索引

使用覆盖索引就是避免回表查询，我们知道，索引分为聚集索引和非聚集索引，并且非聚集索引叶子节点的数据是聚集索引的key，如果查询的字段恰好是索引字段和key，那么在非聚集索引的叶子节点就有这三个数据信息，此时就可以不用回到聚集索引找到整行数据。

前缀索引

如果为某个字段创建索引，但是该字段的数据长度较长，可以截取该字段数据的某部分作为索引。


创建前缀索引
create index 索引名 on 表名(字段名(前缀长度))

3、SQL语句性能优化

SQL语句性能查看

SQL语句执行频率

查看SQL语句执行频率了解数据库以查询为主，还是增删改为主，为数据库优化提供参考依据。


查看insert/delete/select/update执行频率
show [global/session] status like 'Com_______';

慢查询日志

慢查询日志用于记录所有执行时间超过指定时间的SQL语句。


查看慢查询日志是否开启
-- 如果未开启需要在/etc/my.cnf中开启
-- 配置以下信息：
-- slow_query_log=1
-- long_query_time=时间（单位：秒）
show variables like 'slow_query_log';
 
配置后重启数据库
systemctl restart mysqld;

profile详情

profile能够展示SQL语句执行时时间都花费在哪去，基于给出的信息做出SQL优化。


查看数据库是否支持profile操作
select @@having_profiling;
 
查看profile是否开启
select @@profiling;
 
开启profil
set profiling=1;
 
查看每一条已经执行完成的SQL语句耗时（duration）和查询ID（query_id）
show profiles;
 
查看指定query_id的SQL语句详细执行耗时
show profile for query query_id;
 
查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;

explain★

查看select类型的SQL语句执行情况。


直接在select语句之前加上explain
explain select 字段名 from 表名 where 条件;

select语句优化

select语句进行优化主要是索引使用是否得当，在进行select查询时尽量满足索引使用要求。

insert语句优化

insert语句优化可以在插入数据时进行批量插入，因为MySQL默认开启事务，每条语句对应一个事务，一条一条插入数据事务开启关闭多次，批量插入只会开启关闭一次事务；

或者是在插入多条数据时手动控制事务；

在插入数据时，主键顺序插入效率也会高于乱序插入，因为数据插入会引起B+树的变动，顺序插入B+树调整更快。使用自增主键AUTO_INCREAMENT。

update语句优化

update语句优化也是设计索引，执行修改语句时innoDB会对对应索引进行行锁，但是如果没有索引或索引失效，锁会变为表锁，因此执行update语句时尽量使用索引。

4、视图

5、存储过程

存储过程是一系列SQL语句的集合，如果服务器中某个接口业务需要编写多条SQL语句，每次执行SQL语句都要访问数据库，效率低，使用存储过程则只需要访问一次数据库，提高执行效率。

变量

变量有三类：系统变量、用户自定义变量和局部变量。


查看所有系统变量
show [session/global] variables [like 模糊查询];
 
查看指定系统变量的值
select @@[session/global] 系统变量名;
 
设置系统变量
set [session/global] 系统变量名=值;
 
-- ---------------------------------------------------
定义用户自定义变量
set @自定义变量名 =/:= 值;
 
使用用户自定义变量
select @用户自定义变量名 from 表名;
 
-- ---------------------------------------------------
声明局部变量（只能在存储过程内使用）
declare 变量名 数据类型 [default ...];
 
局部变量赋值
set 变量名 =/:= 值;

逻辑语句

存储过程中可以使用if，case，while，repeat，loop等流程控制语句。

6、触发器

触发器是在insert/update/delete语句之前或之后执行预先定义的SQL语句的工具，可以用于日志记录、数据校验等操作。

7、锁

数据作为计算机中的共享资源，为了保证数据并发访问的一致性、有效性，需要使用锁这种机制。锁分为三类：全局锁、表级锁和行级锁。


查看意向锁及行锁加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

全局锁

使用全局锁会锁住数据库中所有表。

加锁后整个数据库处于只读状态。在主库进行全局锁，那么不能更新数据，在从库进行全局锁，那么不能执行主库同步过来的二进制日志binlog，产生主从延迟。

应用场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性。


加全局锁
flush tables with read lock;
 
数据备份
mysqldump -u 用户名 -p 密码 数据库名 > 备份存储位置
 
释放锁
unlock tables;

表级锁

使用表级锁会锁住某一张数据表。表级锁的锁定粒度大，发生锁冲突的概率最高，并发度最低。

表级锁进一步可分为三类：表锁，元数据锁和意向锁。

表锁

表锁又分为表共享读锁和表独占写锁。其中，一个客户端对某张表添加读锁不会阻塞其他客户端对这张表的读操作，但是会阻塞写操作；而一个客户端如果添加了写锁，那么会阻塞其他客户端的读写操作。


添加读锁/写锁
lock tables 表名 read/write;
 
释放锁
unlock tables;

元数据锁

元数据锁是系统自动控制的，不用手动编写SQL语句添加，当访问一张表时会自动加上，事务结束时释放。元数据锁的主要作用是避免DML语句和DDL语句发生冲突，即避免在一个事务中，执行了插入语句，而这时表的结构发生改变导致插入失败。

意向锁

意向锁的作用是为了避免执行DML语句时，行锁和表锁之间发生冲突，如果不使用意向锁，那么在添加表锁时需要对每行数据检查是否有行锁，而意向锁可以减少行锁的检查。

当一个客户端执行DML语句时，会给对应的行数据添加行锁，同时也会给对应表添加意向锁，当其他客户端需要添加表锁时，可以直接根据意向锁来判断是否可以添加表锁，而不用一行一行检查是否有行锁。

行级锁

使用行级锁会锁住对应行数据，锁定粒度最小，发生锁冲突概率最低，并发度最高。在InnoDB中，数据是基于索引组织的，行级锁是通过对索引上的索引项加索来实现的，而不是对记录加索。

行级锁分为三类：行锁，间隙锁和临键锁。

行锁

锁定单个行记录的锁，防止其他事务对该条数据进行update或delete。

行锁分为共享锁和排它锁。共享锁允许其他事务读，但不允许修改；排它锁不允许其他事务读和改。

其中，执行insert，update，delete时自动添加排它锁，但是，如果SQL语句的执行条件没有使用索引，会导致行锁变为表锁。

select默认不加行锁，需要手动添加。


添加共享锁
select语句 lock in share mode;
 
添加排它锁
select语句 for update;

间隙锁

锁定索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，避免产生幻读。

间隙锁可以共存，一个事务添加间隙锁后另一个事务也可以添加间隙锁，但是不能添加数据。

临键锁

行锁和间隙锁组合，同时锁住数据和间隙。

8、日志

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。事务提交后，修改记录会存在该日志文件中。

修改数据库数据时，是先将磁盘数据读入内存，在内存中修改，当有一定量的数据时再一次性写回磁盘中，但是，当将脏页数据存入磁盘时如果发生错误，可以根据日志中存储的操作记录恢复数据。

重做日志机制可以避免事务提交成功但是数据没有持久化在磁盘上。

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚（保证事务原子性）和MVCC（多版本并发控制）。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log在事务执行时产生，事务提交时，并不会立即删除undo log，这些日志还可能用于MVCC。

bin log

二进制日志记录了所有DDL和DML语句，但是不包括DQL语句。

二进制日志用于整个数据库数据恢复和MySQL主从复制。

使用二进制日志实现主从复制后可以进行读写分离，减去数据库服务器压力。

9、MVCC

全称Multi-Version Concurrency Control，多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，MVCC的具体实现，依赖于数据库记录中的三个隐式字段、undo log日志和readView。

隐式字段

一张表除了有自己设置的字段，InnoDB还会自动添加三个隐式字段。

版本链

假设有一行数据原始记录为：

以下事务并发访问，

该记录的undo log生成得到版本链：

readView

ReadView（读视图）是快照读SQL执行时MVCC读取数据的依据，记录并维护系统当前活跃的事务（未提交的事务）的ID。

readView有四个核心字段：

readView依据这四个核心字段制定了版本链的访问规则：

不同的隔离级别，readView生成时机不同。

RC中在事务每一次执行快照读生成（每一次执行快照读都生成readView带来了不可重复读问题）；

RR中在第一次执行快照读生成readView，后续复用这个版本的readView。

复用上述例子，事务5执行时，在RC下生成了两个版本的readView：

之后利用readView规则与版本链中每一个版本从新到旧进行比较，选择最先匹配成功的版本中的数据作为读取到的数据。

10、事务

四大特性

ACID。

A：原子性，atomicity，事物是不可分割的最小操作单元，要么全部成功，要么全部失败。

C：一致性，consistency，事务完成时，必须使所有数据都保持一致状态。

I：隔离性，isolation，事务在不受外部并发操作影响的环境下独立运行。

D：持久性，durability，事务一旦提交或回滚，则对数据库中的数据改变是永久的。

并发事务问题

脏读：一个事务读到另一个事务还没提交的数据。

不可重复读：一个事务先后读取同一条数据，但是两次读取到的数据不同。

幻读：一个事务读取数据时没有读取到，插入时又发现数据已经存在。

事务隔离级别

事务相关SQL语句


查看事务提交方式
select @@autocommit;
 
修改事务提交方式
-- 0表示不自动提交，1表示自动提交
set @@autocommit=0;
 
开启事务
begin; 或者 start transaction;
 
提交事务
commit;
 
回滚事务
rollback;
 
查看事务隔离级别
select @@transaction_isolation;
 
设置事务隔离级别
set [session/global] transaction isolation level 
read uncommitted/read committed/repeated read/serializable

事务原理

使用redo log和undo log保证事务的原子性、一致性、持久性。

使用锁和MVCC保证事务的隔离性。

10、存储引擎

常用存储引擎

InnoDB：DML操作遵循ACID，支持事务；行级锁，支持外键约束。

MyISAM：不支持事务；不支持外键；不支持行锁，支持表锁。

11、分库分表

将数据分散存储，缓解单一数据库的存储压力和性能问题。使用MyCAT进行分库分表。

拆分策略

垂直拆分：把一张表的的多个字段拆分为多张表。

水平拆分：把一张表的数据分到多张表，每张表的字段一样。

分片规则

范围分片：根据数据序号决定属于哪一个分片。

取模分片：数据序号取模节点数量。

一致性hash分片：相同哈希值放到相同分区表中。

枚举分片：配置可能的枚举值，枚举值相同的数据放到同一分片。比如枚举值为各个省份，省份相同的数据放到同一分片。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/1017699