319 lines
16 KiB
Markdown
319 lines
16 KiB
Markdown
---
|
||
title: MySQL技术详解
|
||
categories:
|
||
- 概念
|
||
date: 2023-04-18 19:07:33
|
||
---
|
||
|
||
# MySQL技术详解
|
||
|
||
## 一、数据和数据库
|
||
|
||
数据：可以是数字、文字、图片、视频等
|
||
|
||
数据库：存储数据的仓库，易共享、易拓展、高并发、支持事务（要么做完、要么不做）
|
||
|
||
```
|
||
DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。
|
||
DML包括：
|
||
SELECT：用于检索数据；
|
||
INSERT：用于增加数据到数据库；
|
||
UPDATE：用于从数据库中修改现存的数据
|
||
DELETE：用于从数据库中删除数据。
|
||
|
||
DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。
|
||
DDL包括：DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令：
|
||
CREATE TABLE：创建表
|
||
ALTER TABLE
|
||
DROP TABLE：删除表
|
||
CREATE INDEX
|
||
DROP INDEX
|
||
|
||
DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。
|
||
DCL包括：
|
||
ALTER PASSWORD
|
||
GRANT
|
||
REVOKE
|
||
CREATE SYNONYM
|
||
```
|
||
|
||
|
||
|
||
## 二、数据库类型
|
||
|
||
### **关系型数据库 ：二维表和表之间的联系组成**
|
||
|
||
关系型：二维表模型，如下。
|
||
|
||
| | |
|
||
| ---- | ---- |
|
||
| | |
|
||
|
||
**记录**：一行数据就叫记录
|
||
|
||
**字段**：列名叫字段
|
||
|
||
### **非关系型数据库 ：强调Key-Value和文档数据库**
|
||
|
||
Redis、MongoDB等
|
||
|
||
## 三、MySQL约束类型
|
||
|
||
| 约束类型 | 作用 |
|
||
| --------------------------------- | ------------------------------------------------------------ |
|
||
| 主键约束 primary key | 唯一约束+非空约束，每个表最多只允许一个主键，创建完成之后会默认创建一个唯一索引 |
|
||
| 外键约束 foreign key | 外键约束是保证一个或两个表之间的参照完整性，外键基于两个表的两个字段的参照关系 |
|
||
| 唯一约束 unique | 列或者列组合不能重复，保证数据的唯一性 |
|
||
| 非空约束not null 和默认值 default | 可以确保不为空或者空的时候为什么值 |
|
||
|
||
## 四、MySQL索引
|
||
|
||
**定义**：**索引是存储引擎用于快速找到记录的一种数据结构**。举例说明：如果查找一本书中的某个特定主题，一般会先看书的目录（类似索引），找到对应页面。在MySQL，存储引擎采用类似的方法使用索引，高效获取查找的数据。
|
||
|
||
表的存储由数据和索引组成，如果没有索引就要从磁盘上读每一个数据页，如果有则只需查找索引页（类似于目录）
|
||
|
||
### 索引分类
|
||
|
||
**普通索引：**一个索引只包含单个列，一个表可以有多个单列索引
|
||
|
||
**唯一索引：**索引的列的值必须唯一，但允许有空值
|
||
|
||
**复合索引：**一个索引包含多个列
|
||
|
||
数据库保存的数据是存储在磁盘上，**查找数据时需要将磁盘中的数据加载到内存中**，在介绍索引的实现之前
|
||
|
||
### 建立索引的原则
|
||
|
||
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)的索引，那么只需要修改原来的索引即可。
|
||
|
||
**explain详解**
|
||
|
||
| 列 | 作用 |
|
||
| ------------- | ------------------------------------------------------------ |
|
||
| id | id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。id越大执行优先级越高 |
|
||
| select_type | 表示是简单还是复杂的查询 |
|
||
| table | 正在访问哪个表，当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1, 2>，1和2表示参与 union 的 select 行id。 |
|
||
| partitions | |
|
||
| type | MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：null(直接查询索引，无需查询数据) > system > const > eq_ref > ref > range > index > ALL |
|
||
| possible_keys | 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 |
|
||
| key | 这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。 |
|
||
| key_len | 这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 |
|
||
| ref | 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film. id） |
|
||
| rows | 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。 |
|
||
| filtered | |
|
||
| Extra | **Using index：**查询的列被索引覆盖，并且where筛选条件是索引的前导列(最左侧索引)，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高。<br />**Using where：**查询的列未被索引覆盖，where筛选条件非索引的前导列。<br />**Using where Using index：**查询的列被索引覆盖，并且where筛选条件是索引列之一但不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据, Using index代表select用到了覆盖索引。<br />**NULL：**查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引。<br />**Using index condition：**与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；<br />**Using temporary：**mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。<br />**Using filesort：**mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。 |
|
||
|
||
### 操作索引
|
||
|
||
```
|
||
###创建普通索引
|
||
CREATE INDEX indexName ON table_name (column_name)
|
||
|
||
###后增加索引
|
||
ALTER table tableName ADD INDEX indexName(columnName)
|
||
|
||
###创建表的时候增加索引
|
||
CREATE TABLE mytable(
|
||
ID INT NOT NULL,
|
||
username VARCHAR(16) NOT NULL,
|
||
INDEX [indexName] (username(length))
|
||
);
|
||
|
||
###删除索引
|
||
drop index [indexName] ON mytable;
|
||
|
||
###创建唯一索引
|
||
CREATE UNIQUE INDEX indexName ON mytable(username(length))
|
||
|
||
###后增加唯一索引c
|
||
ALTER table mytable ADD UNIQUE [indexName] (username(length))
|
||
|
||
###创建表时指定
|
||
CREATE TABLE mytable(
|
||
ID INT NOT NULL,
|
||
username VARCHAR(16) NOT NULL,
|
||
UNIQUE [indexName] (username(length))
|
||
);
|
||
|
||
###显示索引
|
||
SHOW INDEX FROM table_name\G
|
||
```
|
||
|
||
|
||
|
||
## 五、MySQL锁
|
||
|
||
当事务对某个数据对象进行操作前，先向系统发出加锁请求，在该事务释放锁之前，其他事物不能对此数据对象进行更新操作
|
||
|
||
## 六、MySQL的存储引擎
|
||
|
||
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式，存储引擎针对数据表的，oracle 和SQL Server等数据库只有一种存储引擎，而MySQL提供插件式的存储引擎
|
||
|
||
## 七、事务
|
||
|
||
事务主要用于处理操作量大，复杂度高的数据，比如脚本，比如银行转账，从一个账号扣款并使另外一个账号增款，这两个操作妖魔都执行，要么都不执行。
|
||
|
||
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
|
||
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
|
||
- 事务用来管理 insert,update,delete 语句
|
||
|
||
一般来说，事务是必须满足4个条件（ACID）：原子性（**A**tomicity，或称不可分割性）、一致性（**C**onsistency）、隔离性（**I**solation，又称独立性）、持久性（**D**urability）。
|
||
|
||
- **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
|
||
- **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
|
||
- **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
|
||
- **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
|
||
|
||
### 操作事务
|
||
|
||
```
|
||
BEGIN 开始一个事务
|
||
ROLLBACK 事务回滚
|
||
COMMIT 事务确认
|
||
```
|
||
|
||
## 八、复制
|
||
|
||
## 九、备份
|
||
|
||
**mysqldump**
|
||
|
||
```
|
||
mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名 #备份整个数据库
|
||
mysqldump -u 用户名 -p 数据库名 表1 表2 > /备份路径/备份文件名 #备份数据表
|
||
--databases 库1，库2 #备份多个数据库
|
||
--all-databases #备份所有数据库
|
||
|
||
mysql 数据库 < 备份文件
|
||
```
|
||
|
||
**mysql-binlog日志备份**
|
||
|
||
二进制日志(log-bin日志)：所有对数据库状态更改的操作(create、drop、update等)
|
||
|
||
```
|
||
vim /etc/my.cnf
|
||
log-bin=mysql-bin #开启二进制日志
|
||
|
||
mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD HH:MM:SS’ 二进制日志 | mysql -u root -p #按照时间恢复
|
||
```
|
||
|
||
## 十、MySQL运维
|
||
|
||
### 常识
|
||
|
||
服务：mysqld 端口：3306
|
||
|
||
主配置文件：/etc/my.cnf 初始化脚本：mysql_install_db
|
||
|
||
启动命令：musqld_safe 数据目录：/var/lib/mysql
|
||
|
||
日志文件：/var/log/mysql.log
|
||
|
||
套接字文件：/var/lib/mysql/mysql.sock
|
||
|
||
进程文件：/var/run/mysqld/mysqld.pid
|
||
|
||
### 常用命令
|
||
|
||
**操作用户**
|
||
|
||
```
|
||
###创建用户
|
||
create user 用户名@远程主机 indentified by ‘密码’;
|
||
%:代替可代替主机位，代表任意远程主机
|
||
|
||
###修改用户密码
|
||
alter user 'root'@'localhost' identified with mysql_native_password by '密码';
|
||
set password=password(‘密码’); #为自己修改密码
|
||
set password for 用户名@‘%’ =password(‘新密码’); #root为其他人修改密码
|
||
|
||
###授权
|
||
select user from mysql.user; #查看用户
|
||
grant all on 数据库.表名 to 用户名@'%'; #授权
|
||
revoke all on 数据库.表名 to 用户名@'%'; #取消授权
|
||
|
||
###设置root 用户远程登录
|
||
update user set host='%' where user='root';
|
||
```
|
||
|
||
**操作数据库**
|
||
|
||
```
|
||
###创建数据库
|
||
create database 数据库名; #创建数据库
|
||
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
|
||
|
||
###删除数据库
|
||
drop database 数据库名; #删除数据库
|
||
|
||
###查看所有数据库
|
||
show databases; #查看所有数据库
|
||
```
|
||
|
||
**操作数据表**
|
||
|
||
```
|
||
###创建数据表
|
||
create table 表名 (
|
||
字段;
|
||
字段
|
||
)ENGINE=InnoDB DEFAULT CHARSET=utf8; #指定使用的引擎和字符编码
|
||
|
||
###删除数据表
|
||
drop table 表名; #删除数据表
|
||
alter tabe 表名 drop 字段名; #删除表的字段
|
||
|
||
###删除外键约束
|
||
alter table tableName drop foreign key keyName;
|
||
|
||
###查看数据表
|
||
describe 表名; #查看数据表
|
||
SHOW COLUMNS FROM 表名 #查看表结构
|
||
|
||
###修改表的存储引擎
|
||
alter table tableName engine=myisam;
|
||
|
||
###修改表名
|
||
alter table 旧表名 rename 新表名 #给表改名
|
||
alter table 表名 change name 表名 字段名 char(50) not null defult; #给字段改名
|
||
|
||
###修改字段
|
||
alter table tableName modify name1 type1 first|after name2; #修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
|
||
alter table 表名 add 字段名 字段属性; #增加字段
|
||
```
|
||
|
||
**操作数据**
|
||
|
||
```
|
||
###插入数据
|
||
insert into 表名 (字段1,字段2) values (1,2,3),(4,5,6); #插入多个数据
|
||
insert into 表1 (字段1,字段2) select 字段1,字段2 from 表2; #复制数据
|
||
|
||
###删除数据
|
||
delete from 表名 where 字段=值; #删除单条数据
|
||
|
||
###修改数据
|
||
update 表名 set 字段=数值 where 字段=值; #修改表中数据
|
||
```
|
||
|
||
**查询操作**
|
||
|
||
```
|
||
select 字段1，字段2 from 表名 where 字段[=> and or]数值 limit 数值 offset 偏移量
|
||
group by #通过某个字段对数据分组
|
||
order by #通过某个字段排序
|
||
JOIN 按照功能大致分为如下三类：
|
||
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
|
||
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
|
||
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
|
||
select 表1/2.字段 from 表1 inner join 表2 on 表1.字段=表2.字段
|
||
```
|
||
|
||
**显示数据表的属性信息 SHOW COLUMNS FROM 数据表 **
|
||
|
||
**显示数据表的详细索引信息 SHOW INDEX FROM 数据表** |