mysql

# mysql

## 学习目标
	一、为什么要学习数据库
	二、数据库的相关概念
		DBMS、DB、SQL
	三、数据库存储数据的特点
	四、初始MySQL
		MySQL产品的介绍
		MySQL产品的安装          ★
		MySQL服务的启动和停止     ★
		MySQL服务的登录和退出     ★
		MySQL的常见命令和语法规范
	五、DQL语言的学习   ★
		基础查询        ★
		条件查询  	   ★
		排序查询  	   ★
		常见函数        ★
		分组函数        ★              
		分组查询		   ★
		连接查询	 	★
		子查询       √                  
		分页查询       ★              
		union联合查询	√
	六、DML语言的学习    ★             
		插入语句
		修改语句
		删除语句
	七、DDL语言的学习  
		库和表的管理	 √
		常见数据类型介绍  √
		常见约束  	  √
	八、TCL语言的学习
		事务和事务处理                 
	九、视图的讲解           √
	十、变量
	十一、存储过程和函数   
	十二、流程控制结构  
	
## 为什么要学习mysql

程序与数据分离。

## 数据库的好处 （对比内存存储如List/Map）
	1. 持久化数据到本地，关机不影响数据
	2. 可以实现结构化查询，方便管理
	3. 可以扩容，内存存储受内存大小限制

##数据库相关概念
	1、DB：数据库，保存一组有组织的数据的容器
	2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
	3、SQL: 结构化查询语言，用于和DBMS通信的语言

### 命令

#### 查看服务器的版本
	方式一：登录到mysql服务端
	select version();
	方式二：没有登录到mysql服务端
	mysql --version
	或
	mysql --V

`CREATE DATABASE IF NOT EXISTS xxxx DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;`

一些命令
- show index from Mytable;
- alter table Mytable drop index `inx_test`;
- DROP INDEX `inx_test` on Mytable;
- show databases;
- use DATABASE;
- show tables;
- desc TABLE;
- source /tmp/aaa.sql;
- timestamp转long： UNIX_TIMESTAMP(column_timestamp)
- 查看所有数据库容量大小
```sql
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
```

```sql
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='TABLE_NAME'
order by table_rows desc, index_length desc;
```

- 建表语句
```sql
CREATE TABLE `life_mp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `title` varchar(100) NOT NULL COMMENT '标题',
  `key_words` varchar(256) DEFAULT NULL COMMENT '关键字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='公从号'
```

- 查看变量 show variables like 'lower_%'

- 修改表名
`alter table  table_name1 rename to table_name2;`

- 导出数据
mysql -u 数据库用户名 –p 数据库名称 < 导入的数据库文件
`mysqldump -uroot -pANpHWrzq600NZZaGbOqk eblog > /tmp/eblog0323.sql`
- 导入数据
`mysql -hh151 -uroot -pabc123 < D:\mysql\my-mysql-V0.1.0.sql`
- 修改自增值
`alter table zs_cn_granule_receipt_detail AUTO_INCREMENT=2500;`
-  修改密码
`mysqladmin -uroot -proot password new_password`
- 用SET PASSWORD命令来修改密码
`set password for root@localhost = password('123');`
- 修改字段
alter table COLUMNS_V2 modify column COLUMN_NAME varchar(256) character set utf8 NOT NULL COMMENT '列名';
- 修改表字符集 
ALTER TABLE TABLE_NAME DEFAULT CHARACTER SET=utf8;
- 联表查询
```sql
SELECT
	user_id,
	project_id,
	STATUS,
	(
		SELECT
			NAME
		FROM
			test_user
		WHERE
			id = pu.user_id
	) AS USER
FROM
	test_project_user pu
LIMIT 3; 
```
```text
-- update column according to another one in another table 【start】
--  -- ok
-- update n inner join ns on n.bid=ns.id set n.bname=ns.title
--  -- ok
-- update n set n.bname=(select ns.title from ns where ns.id=n.bid)
-- update column according to another one in another table 【end】
```

-- 备份表和数据到新表中
` create table db_sample.new_table as  select * from db_sample.old_table where 1=1; `

### 用户与权限

创建用户

```sql
CREATE USER 'john'@'192.168.0.%' IDENTIFIED BY "123";
CREATE USER 'john2'@'%' IDENTIFIED BY "123";
```

分配权限（创建权限时若用户不存在就创建）

```sql
-- 若指定 WITH GRANT OPTION，则joe可以再给别的用户分配权限
grant all privileges on test.* to 'joe'@'%' identified by '123' [WITH GRANT OPTION];
flush privileges;
```

priv代表权限select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限

删除用户

```sql
drop user joe
drop user tommy@'%'
drop user john@'192.168.0.%'
```

###

如何设置合理的连接池连接数？

show variables like '%max_connections%';
show status like 'Threads_connected';

###

show full processlist
#当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
#当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
#锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
# list locked tables
show open tables where in_use>0;

##### 服务启动、重启等命令
- 启动mysql
systemctl start mariadb.service 或者 systemctl start mysqld.service
- 结束
systemctl stop mariadb.service 或者 systemctl stop mysqld.service
- 重启
systemctl restart mariadb.service 或者 systemctl restart mysqld.service
- 开机自启
systemctl enable mariadb.service 或者 systemctl enable mysqld.service

#### id自增用完了怎么办

数据表定义的自增 ID，如果达到上限之后，再申请下一个 ID 的时候，获得到的值将保持不变，即会报一个主键冲突异常，如下

// Duplicate entry 'xxx' for key 'PRIMARY'

```text
面试官:"那自增主键达到最大值了，用完了怎么办？"   
你:"这问题没遇到过，因为自增主键我们用bigint类型，一般达不到最大值，我们就分库分表了，所以不曾遇见过！"
```

如下为测试过程：

```sql
CREATE TABLE `test_id` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'OTHER' COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into test_id (name) values(UUID());
insert into test_id (name) values(UUID());
insert into test_id (name) values(UUID());
```

报的错误：
```sql
[Err] 1062 - Duplicate entry '127' for key 'PRIMARY'
```

若无自增id，mysql会提供一个row_id字段，见<https://blog.csdn.net/weixin_39531780/article/details/111280772>

### 执行引擎

InnoDB MyISAM

### 事务

事务：不可分割的操作。

ACID

原子性（Atomicity）   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency） 事务前后，数据库的完整性约束没有被破坏 。
隔离性（Isolation）   多个并发事务之间要相互隔离，不能被其他事务的操作数据所干扰。
持久性（Durability）  事务后，对数据库的所有更新将被保存到数据库，不能回滚。

关键字：脏读 幻读 事务级别

数据库并发问题：

现有两个事务T1，T2。
1、脏读（一个事务读取了另一个事务修改但未提交的记录的值）

T1将某条记录的age值从20修改为30
    T2读取了T1更新后的值30
    T1回滚，age值恢复为20
    T2读取的30就是一个无效的值

2、不可重复读（事务T1多次读取同一数据，事务T2 在事务T1多次读取的过程中，对数据作了更新并提交，导致事务T1多次读取同一数据时，结果 不一致。不可重复读的重点是修改）

T1读取了age值为20
    T2将age修改为30
    T1再次读取age的值为30，两次读取不一致

3、幻读（第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。幻读的重点在于新增或者删除）

T1读取了student表中的一部分数据
    T2向student表中插入了新的记录
    T1读取student表时，多出了一些行

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

不可重复读：银行做活动 事务a查询某地区余额1000以下送一包餐巾纸 生成名单 事务b小明余额500，存了1000，变成1500 事务a查询1000到2000送一桶油 生成名单 这样小明收到了2个礼品

幻读：银行做活动 事务a查询某地区余额1000以下送一包餐巾纸 生成名单 事务b新增了一个新用户小明，并存款500，事务a查询1000到2000送一桶油 生成名单 这样小明没有收到礼物，而同事注册的小李存了1500却收到了一桶油

#### MVCC控制读写并发，快照隔离

MVCC 是英文Multi-Version Concurrency Control 多版本并发控制。

<https://www.jianshu.com/p/31168a101215>

### 四种隔离级别设置

| 隔离级别     |  说明    |  会脏读？    |   会不可重复读？   |    会幻读？  |
| ---------------- | ---- | ---- | ---- | ---- |
| Read uncommitted | 读未提交 | 会 | 会 | 会 |
| Read committed | 读已提交 | 不 | 会 | 会 |
| Repeatable read | 可重复读 | 不 | 不 | 会 |
| Serializable | 串行化 | 不 | 不 | 不 |

select @@tx_isolation; 查询当前事务隔离级别
show variables like 'transaction_isolation';

select @@global.tx_isolation; 查询全局事务隔离级别
show variables like 'tx_isolation'

set transaction isolation level read committed;  设置当前事务隔离级别
set transaction isolation level read uncommitted;  设置当前事务隔离级别

set global transaction isolation level read committed;  设置全局事务隔离级别

隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写

start transaction;
commit;
rollback;

大多数数据库的默认隔离级别为: Read Commited,如Sql Server , Oracle.

少数数据库默认的隔离级别为Repeatable Read, 如MySQL InnoDB存储引擎

通过以下语句可以查询当前有多少事务正在运行。
select * from information_schema.innodb_trx;

#### mysql测试字段名是否大小写敏感

```sql
-- https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

-- 测试字段名是否大小写敏感
show variables like 'lower_case_table_names';
-- lower_case_table_names=0（默认）区分大小写
-- lower_case_table_names=1表示不区分大小写

-- 如下是测试lower_case_table_names=1的情况
drop table if EXISTS __test_sen;
CREATE TABLE `__test_sen` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `non_sen_value` varchar(64),
  `sen_value` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `__test_sen` (non_sen_value, sen_value) values('ns-value-a', 's-value-a');
insert into `__test_sen` (non_sen_value, sen_value) values('ns-VALUE-b', 's-VALUE-b');
select * from __test_sen;
-- 如下两行说明表名称不区别大小写（两次查询结果相同）
select * from __TEST_SEN where non_sen_value='ns-value-a';
select * from __test_sen where non_sen_value='ns-value-a';
-- 如下两行说明字段名称不区别大小写（两次查询结果相同）
select * from __test_sen where non_sen_value='ns-value-a';
select * from __test_sen where NON_SEN_VALUE='ns-value-a';
-- 如下两行说明值不区别大小写（两次查询结果相同）
select * from __test_sen where non_sen_value='ns-value-b';
select * from __test_sen where non_sen_value='ns-VALUE-b';
-- 【区分大小写的方式一】建议此方法（注意建表语句中的 COLLATE utf8mb4_bin ）
-- 如下两行说明值区别大小写（第一次有结果，第二次无结果）
select * from __test_sen where sen_value='s-value-a';
select * from __test_sen where sen_value='s-value-b';
-- 【区分大小写的方式二】（使用binary()）
-- 如下两行说明值区别大小写（第一次无结果，第二次有结果）
select * from __test_sen where binary(non_sen_value)='ns-value-b';
select * from __test_sen where binary(non_sen_value)='ns-VALUE-b';
```

## mysql调优

### 性能监控

#### profile

<https://dev.mysql.com/doc/refman/5.7/en/show.html>

```sql
set profiling=1;
show profile;
show profiles;
select * from table_1 limit 100;
-- 查看历史执行的sql列表及耗时
show profiles;
-- 查看最新执行的sql的执行耗时
show profile;
-- 查看show profiles结果中query_id为2的sql的执行耗时
show profile for query 2;
show profile all;
show profile cpu;
```

#### performance_schema

```sql
show variables like 'performance_schema';
show databases;
use performance_schema;
show tables;
show tables like '%waits%';
update setup_instruments set enabled='YES', timed='YES' where name like '%wait%';
update setup_consumers set enabled='YES' where name like '%wait%';
select * from events_waits_current;
-- 查看有多少个客户端
show processlist;
```

### schema与数据类型优化

#### 数据类型的优化

在满足需求的情况下选更小的数据类型。（比如数字不要都选择int，相应地使用tinyint/smallint）。减少存储空间的占用，减少io操作。

时间类型就用date/datetime/timestamp，别用varchar。

避免NULL

##### 数字类型
tinyint   1字节
smallint  2字节
mediumint 3字节
int       4字节
bigint    8字节

##### 字符串
char 数据长度固定 最大255，检索效率比varchar高，会删除数据末尾的空格，适用于长度固定的场景，如uuid/md5
varchar 数据长度不固定的情况，如备注/文章内容；多字节字符，如汉字/日文
text 存储大数据的字符串
clob
blob

##### 时间类型

datetime  8个字节，与时区无关，精确到毫秒，可保存时间范围大 9999-12-31 23:59:59
timestamp 4个字节，依赖数据库设置的时区，精确到秒，范围（1970-01-01~2038-01-19）
date      3个字节，范围（1000-01-01~9999-12-31）

##### 用枚举来替代字符串

兼具varchar和int的优点。
使用int存储，但是可以像varchar一样like。
限制数据内容。（类似excel的数据有效性）

```
create table tbl_enum(e enum('MALE', 'FEMALE', 'UNKNOWN') not null);
insert into tbl_enum(e) values('MALE'),('FEMALE'),('UNKNOWN');
select e from tbl_enum;
select e + 1 from tbl_enum;
select e,e-1,e+0,e+1 from tbl_enum;
-- enum类型可以进行concat，但进行+0操作时就变成索引了
select concat(e, '-aa'), concat(e+0, '-aa') from tbl_enum;
-- 使用字符串模糊查询
select e from tbl_enum where e like 'FEMA%';
-- 使用字符串查询
select e from tbl_enum where e = 'FEMALE';
-- 使用索引查询
select e from tbl_enum where e = 1;
```

##### 特殊类型 ip地址

不建议用varchar(15)，用bigint
-- ip转bigint
select INET_ATON('192.168.0.111');
-- bigint转ip
select INET_NTOA(3232235631);

#### 三范式 与 反范式

第一范式(1NF)：数据表的每一列都要保持它的原子特性，即列不能再被分割。
第二范式(2NF)：属性必须完全依赖于主键。
第三范式(3NF)：所有的非主属性不依赖于其他的非主属性

范式的目的：减小数据的冗余性，提高效率。
范式的缺点：通常需要关联查询。

反范式：
优点：所有的数据都在同一张表中，可以避免关联查询。
可以设计高效的索引。
缺点：冗余较多，删除数据时会删除不需要删除的字段。

##### 主键的选择

- 代理主键 与业务无关的，无意义的数字或字符串
- 自然主键 事物属性中的自然唯一标识，如人的身份证号

推荐使用代理主键：不与业务耦合，维护更容易；不暴漏太多信息。

##### 字符集

仅有ASCII时，可考虑latin1

##### 存储引擎的选择

##### 适当的数据冗余

避免join操作只为获取较少字段

##### 适当拆分

把不必要的字段移到另一表中。

分表。分库。

### 执行计划

见文件 mysql-执行计划.md

<https://dev.mysql.com/doc/refman/5.7/en/explain.html>

explain select * from tbl_xxxe;

### 通过索引进行优化

#### hash index (hash索引)

只能等值查找
得把数据全部加载到内存

## 查询优化

## 工具

- dbeaver
- navicat
- MySQL Workbench
  * https://downloads.mysql.com/archives/workbench/
    - https://downloads.mysql.com/archives/get/p/8/file/mysql-workbench-community-8.0.27-winx64.msi
  * <https://aka.ms/vs/17/release/vc_redist.x64.exe> 不需要重启电脑，尽管系统提示要重启电脑

## 相关资料

- <https://dev.mysql.com/doc/refman/5.7/en/>
- 《MySQL技术内幕》