MySQL学习笔记

MySQL数据库管理与操作的综合性指南，详细介绍了SQL语句分类、数据库和表操作、索引、存储引擎、性能优化等多个方面的知识，涵盖了从基础使用到高级优化的各个层面。重点介绍了索引的数据结构、类型、使用方法以及优化技巧，对于理解和提高数据库性能具有重要指导意义。

139 2024-03-05
AIGC摘要学习笔记

暂无目录

MySQL
SQL语句分类

ddl defination
dml managment
dql query
dcl control

1.数据库相关

-- 显示所有数据库
show databases;
-- 创建数据库
create database test;
-- 创建数据库 指定默认字符集和默认排序规则
create database test default character set utf8mb4 collate utf8mb4_unicode_ci;
-- 删除数据库
drop database test;
-- 选择使用数据库
use test;

2.表相关

-- 创建表
create table test(
    column_name column_type comment 'comment',
    ....
);


-- 修改字段 
alter table test 
    change column column_name new_column_name new_column_type;
-- 修改字段 不包括字段名
alter table test 
    modify column column_name new_column_type;
-- 修改字段 增加列
ALTER TABLE employees
    ADD COLUMN birth_date DATE;
-- 修改字段 删除列
ALTER TABLE employees 
    DROP COLUMN birth_date;
-- 修改表名
ALTER TABLE employees 
    RENAME TO staff;

-- 删除表
drop table test;

-- 展示选择的库的全部表
show tables;

-- 主键，外键
ALTER TABLE employees
    ADD PRIMARY KEY (employee_id);

ALTER TABLE orders
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id);

3.数据相关

-- 插入数据
insert into test values(...),(...);
-- 指定字段插入多条数据
insert into test(columns...) values(...),(...);
-- 查询表的所有数据
select * from test;
-- 查询表的所有符合条件的数据
select * from test where (colum > [< = <= >= not in(..)]);
-- 使用聚合函数查询
select count()[max min avg] from test where ...;
-- 使用聚合函数 分组查询
select count()[max min avg] from test where ... group by cloumn_name having [condition];
-- 排序
select * from test order by column1 desc col;

4.用户权限相关

-- 创建本地用户
create user 'test'@'localhost' identified by '123456';
-- 创建可以外部访问的用户
create user 'wds'@'%' identified by '123456';
-- 修改用户密码
alter user 'wds'@'%' identified with mysql_native_password by '1234';

-- 展示某个所有权限
show grants for 'wds'@'%';
-- 授予用户权限
grant all on test.* to 'wds'@'%';
-- 移除用户权限
revoke all on test.* from 'wds'@'%';

5.函数

# str
-- 字符拼接
concat('str1','str2')
-- 字符小写
lower('str')
-- 字符大写
upper('str')
-- 字符左填充
lpad('str',spill_length,'spill str')
-- 字符右填充
rpad('str',spill_length,'spill str')
-- 字符去除空白
trim('  str  ')
-- 分割字符串
substring(str, startindex1, length)


# number
-- 向下取整
ceil(1.1)  -> 2
-- 向上取整
floor(1.1) -> 1
-- 四舍五入
round('2.345', 2) -> 2.35
-- 求余
mod(3,4) -> 3
-- 随机数 
rand()

# date
-- 当前时间
curdate()
-- 当前日期
curtime()
-- 当前时间和日期
now()

-- 获取日期的年月日
year(date)
month(date)
day(date)
-- 日期基础上加70天
date_add(now(),interval 70 day)
-- 计算两个日期相差多少天 date1 - date2
datediff(date1,date2)

6,流程控制函数

-- 如果condition为true返回第一个值，如果为false返回第二个值
if (condition,'ok','error')
-- 如果condition非null返回本身，如果为null返回第二个值
ifnull(condition,'Default')

-- 条件可换表达式
case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市'

7.约束

-- 非空约束
not null
-- 唯一约束
unique
-- 主键约束
primary key
-- 默认约束
default
-- 检查约束 （8.0.16+）
check
-- 外键约束
foreign key
    -- 约束 删除/更新行为
    -- NO ACTION 默认 RESTRICT 不允许有对应外键记录的记录更新/删除
    -- CASCADE 有对应外键记录的记录更新/删除 执行对应的 更新/删除
    -- SET NULL 有对应外键记录的记录删除时 设置为null（前提是允许设置为null）
    -- SET DEFAULT 有对应外键记录的记录删除时 对应列 设置为默认值（Innodb不支持）
alter table ? add constraint f_name reference table(foreignkey)
    on update CASCADE
    on delete CASCADE;

8.多表查询

-- 隐式内连接
select * from table1,table2 where conditions
-- 显式内连接
select * from table1 [inner] join table2 on  conditions
-- 左外连接 会包含全部左表的记录
left join
-- 右外连接 会包含全部右表的记录
right join
-- 自连接
-- 连接同一张表 （场景：员工表查询对应管理者，管理者信息也在员工表中）

-- 联合查询 
-- 合并两次查询结果 不加all去除重复项 （查询的列数和类型要相同）
slect * from emp where age > 20
union [all]
slect * from emp where salary > 20k;

-- 子查询
    -- 标量子查询
    select * from emp where dept_id = (select id from dept where name = '销售部');
    select * from emp where entrydate > (select entrydate from emp where name = '方东白');
    
    -- 列子查询 可用操作符（in 、not in、any、some、all）
    select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
    -- all 的用法 （都需要满足） any/some （满足一个）
    select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );
    
    -- 行子查询
    select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
    
    -- 表子查询
    select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
    select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

9.存储引擎

Mysql的体系结构

从上至下：（客户端，各种开发SDK）、连接层、服务层、引擎层（可插拔）、存储层

image-20240813204415759

注意不同层实现的功能

引擎介绍

存储引擎是存储数据、建立索引、更新/查询数据等的实现方式

存储引擎没有好坏之分，重要的是用到合适的 场景

# 查看表锁使用的引擎
show create table user;

CREATE TABLE `user` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

# 在创建表的时候没有选择引擎，默认就innodb
# 从mysql5.5开始，innodb作为默认引擎，5.5之前默认是MyISAM

存储引擎是基于表的,不是基于库的,所以存储引擎有时候会称为表的类型

-- 查询数据库支持的存储引擎，以及各个引擎的特点。
SHOW ENGINES;

MySQL 5.7

image-20240813205426895

MySQL 8： 多了两个主从集群的引擎

image-20240813210934490

如图存储引擎可以决定:事务 锁 约束等功能的支持性，这将成为选择引擎的重要依据。

-- 创建表的时候指定存储引擎
CREATE TABLE test_myisam(
    id int,
    name varchar(10)
)ENGINE = MyISAM;

desc test_myisam;
show create table test_myisam;

InnoDB

兼顾高性能和高可靠性的通用存储引擎

特点：

  • DML操作遵循ACID特性，支持事务；
  • 行级锁，提高并发访问性能；
  • 支持外键约束，保证数据库的完整性和正确性。

存储格式：

  • idb类型，每个表一个文件，包括结构、数据、索引等信息；
  • 5版本还有frm，存储结构和索引信息。

innodb引擎的表结构，索引，数据由三个部分组成，在早期版本结构单独为一个文件，后来合并到idb文件中。

合并开关 innodb_file_per_table (默认开启)

# 查看mysql系统变量
show variables like 'innodb_file_per_table';
select @@innodb_file_per_table;

拓展操作

从idb文件中提取表结构sdi文件

# ibd2sdi命令从mysql8引入 对于5版本无法使用
ibd2sdi table.idb

存储结构

image-20240813213136102

MyISAM

旧版默认，查询性能较高

特点：

  • 不支持事务，不支持外键；
  • 支持表锁，不支持行锁；
  • 访问速度快。

存储格式：

  • MYD 结构；
  • MYI 数据；
  • sdi 索引。

Memory

存储在内存中，硬件限制，数据容易丢失，可做临时表或缓存使用

特点：

  • 内存存储介质；
  • Hash索引。

存储格式：

  • sdi 索引文件。

如何选择存储引擎

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

image-20240813213944227

10.索引

索引是帮助Mysql高效获取数据的数据结构

在进行SQL优化时，很大程度上都是围绕着索引进行优化，需要重点关注。

词汇：

  • 全表扫描 （无索引，或者索引没有生效，性能最低）
  • 回表查询 （通过二级索引查询到值后，再到聚集索引中继续查询记录数据）

索引的优缺点

image-20240814202642393

索引数据结构

  • MySQL的索引由存储引擎实现，不同的存储引擎有不同的结构；

image-20240814202940289image-20240814202953402

数据结构的对比

  • 二叉树：不平衡，有序数据导致性能降低；只能有两个子节点，数据量高的时候树深度太大，效率降低。
  • 平衡二叉树：绝对平衡，维护平衡的成本高。
  • 红黑树：相对平衡，维护成本相对低一些；也是二叉，存在深度问题。
  • B树（多路平衡查找树）：数据散落在节点上，范围查找效率不高。
  • B+树：树干做索引，叶子节点存放数据，数据呈线性，单向链表。

树阶，在B树B+树中，代表每个节点最多有几个指针。

MySQL中的B+树索引

​ 在MySQL中，对B+树做了一些优化，对叶子节点的单向链表增加了指针，变为了双向链表环提高了范围查询的性能。

MySQL中的Hash索引

​ Hash索引只支持等值匹配，不支持范围查询，不能够使用索引进行排序操作。且只支持Memory引擎。

为什么MySQL的Innodb选择了B+树作为索引数据结构？

<img src="https://www.reboots.top/api/file/e2cc65d7d45f4702bf6ea88711ea3808_0.7.jpg" alt="image-20240814210437339" style="zoom: 33%;" />

索引分类

image-20240814210651176

image-20240814210838049

只有聚集索引直接存放数据，非聚集索引存储的是聚集索引的索引值，非聚集索引需要查询聚集索引（回表查询）才能得到具体数据。

索引操作：

-- 创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...);
-- 查询表索引
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX index_name ON table_name;

-- 查看系统状态信息
show global status like 'Com_______';

| Com_commit    | 11    |
| Com_delete    | 2     |
| Com_insert    | 17    |
| Com_select    | 22885 |
| Com_update    | 35    |

-- 通过分析结果可以得出系统是查询业务居多，还是更新业务居多，对于业务要分情况优化。

慢查询

  • 如何开启慢查询
slow_query_log=1   # 开启慢查询
long_query_time=2  # 慢查询时长
  • 开启后如何查看
/var/lib/mysql/localhost-slow.log
  • 性能分析
select @@have_profiling;  	# 查看是否支持
set profiling = 1;			# 开启
select @@profiling;  		# 查看开启状态
show profiles;				# 查看每一条sql的耗时
show profile for {{query_id}};				# 查看每一条sql的耗时
show profile cpu for query {{query_id}};	# 查看每一条sql的耗时

索引操作

show create drop ...

准备工作

CREATE TABLE `tb_user` (
  `id` bigint NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `perfession` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`name`)
)

查看表的索引

show index from tb_user;
# 纵向展示
show index from tb_user \G;

创建索引

索引命名一般为：

idx_表名_索引名

# 创建一个常规索引
create index idx_user_name on tb_user(name);
# 创建一个唯一索引
create unique index idx_user_phone on tb_user(phone);
# 创建一个联合索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);

# 也可以使用alter 语法添加索引
ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);
...
# 也可以创建表的时候添加索引
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
...

删除索引

drop index idx_user_email on tb_user;

执行频次 - 数据库是否适合优化

要对数据库进行优化，首先要明确数据库的业务场景：

对于查询多的库，考虑优化；

对应修改多的库，考虑不优化。

如何知道查询多还是增删改多？

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 18    |
| Com_delete    | 10    |
| Com_insert    | 1235  |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 84244 |
| Com_signal    | 0     |
| Com_update    | 413   |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.05 sec)

慢查询日志 - 怎么优化

要想优化，首先要明确要优化什么？

在MySQL中查询的性能指标就是耗时了，

我们需要找到耗时较长的SQL（慢SQL）然后针对性的优化。

如何找到慢SQL？

通过开启MySQL的慢查询日志（默认关闭），对应超过指定时间的SQL都会记录到日志中。

查看是否开启慢查询日志

show variables like 'slow_query_log';

如何开启慢查询日志

方法一：全局变量设置 （重启失效）
将 slow_query_log 全局变量设置为“ON”状态

set global slow_query_log='ON';

设置慢查询日志存放的位置

set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

set global long_query_time=1;

方法二：配置文件设置 （持久生效）
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
# slow_query_log = 1
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

Profiles - 不止慢SQL需要优化

对于慢查询筛选出来的sql，一般都需要取优化，但是对于一些简单的查询，虽然进进不到日志里，但是性能差，达不到慢SQL的时间，这时候就需要手动查看SQL的耗时了

profile详情

可以粗略展示各个阶段所花费的时间

通过show profiles 可以查看sql执行的时间都花费到了哪里。但是需要数据库支持：

# 查看是否支持 测试 5.7.36 和 8.0.26 都支持
select @@have_profiling;
# 查看是否打开
select @@profiling;

# 使用方式：
# 对当前会话启用
set profiling = 1;
# 执行SQL
....
# 显示开启后的所有统计信息
show profiles

show profile for query query_id;
show profile cpu for query query_id;

Explian SQL执行计划

通过explian，可以查看sql的具体执行计划

# 使用方式
explian select count(*) from tb_sku;
desc select count(*) from tb_sku;

执行计划字段解释

  • id： 执行的顺序，相同的id就从上到下执行，id越大越先执行。
  • select_type: 查询类型
    • SIMPLE 简单表，不适用连接或子查询
    • PRIMARY 主查询，即外层的查询
    • UNION union操作中的第二个或者后面的查询
    • SUBQUERY select、where后面的子查询
  • type 表示连接类型 (从上到下，性能由好到差)
    • NULL 没查询表
    • system 查询了系统表
    • const 查询主键或者唯一索引
    • eq_ref 通常仅在连接MySQL操作中使用
    • ref 查询非唯一的索引
    • range 对特定范围的数据遍历
    • index 遍历索引树
    • all 遍历整张表
  • possible_key 可能用到的索引，可能会有多个
  • key 实际使用的索引 NULL为没有用到索引
  • key_len 索引的长度（Byte）不影响精度的情况下越短越好
  • rows 预估查询行数
  • filtered 查询行数占读取行数的百分比（越大越好）
  • Extra 额外信息

11.索引的使用

如何使用索引来提高我们sql的执行效率呢？

优化条件查询

# id为主键索引 耗时极短
select * from tb_sku where id = 1 \G;
1 row in set (0.00 sec)
# 没有索引的情况 花费十八秒 非常慢
select * from tb_sku where sn = '100000003145001' \G;
1 row in set (18.37 sec)

# 为SN创建索引  在数据量非常大的情况下创建索引是非常耗时的
create unique index idx_sku_sn on tb_sku(sn);
 
# 再次查询，验证效果
select * from tb_sku where sn = '100000003145001' \G;
1 row in set (0.01 sec)

索引的失效情况

1 不满足最左前缀法则

在创建联合索引的情况下，索引是有顺序的，而这个顺序可能会导致索引失效。

example：

| Table   | Non_unique | Key_name             | Seq_in_index | Column_name |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | 
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | 
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      |

在这种情况下进行条件查询，必须出现最左边的字段，相邻右边的字段才能够使用索引。这称为最左前缀法则

该法则规定，在使用联合索引进行查询时，查询条件必须从索引的最左列开始，并且不能跳过任何列。

age status 失效

profession status profession生效 status 失效

profession 生效

profession age 生效

2 范围查询

按照索引中的顺序，如果某个字段使用了大于或者小于的范围查询，那么它右侧的所有字段都不能使用索引。

规避方法：

在业务允许的情况下使用<= 或者 >= 不会影响索引。

3 对索引使用左或者左右模糊匹配

select * from tb_user where name like '%zhang';
select * from tb_user where name like '%zhang%';

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

4 对索引使用函数

select * from tb_user where length(name)=6;

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

5 对索引进行表达式计算

explain select * from tb_user where id + 1 = 10;

仍然因为索引保存的是索引字段的原始值

6 WHERE 子句中的 OR

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

7 对于字符串不适用引号

select * from tb_user where phone = 17799990015；

8 对于当前的数据分布不走索引会更快

因为非聚簇索引要回表，所以不是用了索引都能够优化查询速度

所以MySQL在某些情况下会放弃索引

SQL提示

对于存在多个索引的情况，MySQL的优化器会帮我们决定使用哪个索引，但是这个索引不一定是最优解，我们可以通过使用SQL提示的方式告诉优化器要我想要使用哪个索引。

# 提示MySQL使用或者不使用某个索引
select * from tb_user use index(idx_user_pro) where profession = '软件工程'；
select * from tb_user ignore index(idx user pro) where profession = '软件工程"；

但是也存在MySQL不适用我们推荐的索引的情况，手动指定的索引速度性能不够高，MySQL也会放弃提示中的索引。

# 强制使用某个索引
select * from tb_user force index(idx user pro) where profession = '软件工程"；

覆盖索引

image-20240817172313981

对于非聚簇索引中，存储索引值外还存储了主键，如果查询的字段没有其他额外字段那么只查询该索引就能覆盖到结果集，就不需要进行回表操作，性能也是非常高的。

前缀索引

在对一些数据结构比较大的字段创建索引时，如果不经任何截取，全文都作为索引数据，会浪费大量资源，降低性能。

解决方案：

# 只对前面多长度n的数据建立索引
create index idx_xxxx_on table_name(column(n));

选择性

选择性是指，在表中某个字段的不重复数据占总表记录数的比值，如果没有重复则为1，1的性能是最好的。

对于截取的数据长度，要考虑数据选择性的值，从而选择一个合适的长度创建索引。

单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

image-20240817184035634

SQL优化

Insert操作优化

  1. 批量插入，减少连接开支与事务开支。
  2. 手动提交事务，执行多次insert后手动提交事务。
  3. 主键顺序插入。
  4. 大量插入数据，使用load命令。

从文件中load数据

这里的文件不是sql数据，格式比较自由。

#客户端连接服务端时，加上参数-local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/data_file' into table tb_user fields terminated by ',' lines terminated by '\n';

主键最佳实践

  • 满足业务需求的情况下，尽量降低主键的长度。（减少对B+Tree的上层索引空间的占用，以及降低页排列频率）

  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。（避免页重新排列）

  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。（可能会导致页重新排列）

  • 业务操作时，避免对主键的修改。（可能会导致页重新排列）

order by优化

对于排序操作有两类：

  • using filesort： 全表扫描后，一个个排序。 （要尽可能避免该情况发生）
  • using index：直接使用索引顺序返回。

如何避免 filesort

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)

group by优化

group by和条件查询类似，一般添加联合索引就可以。

需要注意：

  • 在分组操作时，可以通过索引来提高效率。
  • 分组操作时，索引的使用也是满足最左前缀法则的

limit 优化

当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记
录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化 

select * from tb_sku order by id limit 9000000,10;
10 rows in set (15.06 sec)

select * from tb_sku s,(select id from tb_sku order by id limit 9000000,10),temp where s.id = temp.id;
10 rows in set (11.83 sec)

count优化

在MyISAM引擎中对于记录数有单独存储，但是不能加条件。

在innodb中就没有这个值了。

在日常使用中，通常都有逻辑删除字段的条件，数据多了，两者都一样慢。

解决方案：

自己使用缓存Redis或者其他工具，单独维护计数

性能比较

count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)

update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

因此我们需要规避查询条件没有索引的情况，避免锁表。

目录

暂无目录