MySQL
MySql数据库
客户端启动：mysql -uroot -p123456 -P 3306 -h 127.0.0.1
-cmd窗口下执行
-tcp协议
-navicat
-pymysql（用python写的符合他们通信协议的客户端）
服务端启动：mysqld 加载了配置文件（找了一些路径）
字段：表的标题
记录：文件中的一行内容
库：文件夹
表：文件
-- 在sql中是注释
# 设置密码
update mysql.user set password=password("123") where user="root" and host="localhost";
flush privileges;
# 忘记密码
（1）先关闭mysqld服务端
（2）重新启动：mysqld --skip-grant-tables
(3) 启动客户端：mysql -u
update mysql.user set password=password("") where user="root" and host="localhost";
flush privileges;
（3）重新启动mysqld
一、数据库三范式
第一范式：确保数据库中的每个字段都是原子性的，不可再分（消除数据的重复和冗余）
第二范式：在满足第一范式的基础上，数据库中的每个非主键列完全依赖于主键（确保数据的一致性和完整性）
第三范式：在满足第二范式的基础上，确保数据库中的每个非主键列之间没有传递依赖关系。传递依赖是指一个非主键列依赖于另一个非主键列，而不是直接依赖于主键（消除数据不一致性问题）
二、E-R模型
实体之间有三种关系，分别为一对一、一对多、多对多
在建表时一对多关联字段写在多的一方，多对多时一般另外创建一个表建立关联
三、数据库安装
共有三种安装方式：
1、包安装
#安装 mysql-server，会自动安装客户端包
[root@rocky86 ~]# yum install -y mysql-server
#mysql5.7包安装默认有初始密码
cat /var/log/mysqld.log | grep password
2、二进制包安装
#首先安装依赖
[root@rocky86 ~]# yum -y install libaio numactl-libs ncurses-compat-libs
#创建组和用户
[root@c7 ~]# groupadd -r mysql
[root@c7 ~]# useradd -r -g mysql -s/sbin/nologin mysql
#下载包
[root@rocky86 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
#解压至指定目录，这个目录只能写 /usr/local/
[root@rocky86 ~]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@rocky86 ~]# cd /usr/local/
[root@rocky86 local]# ln -s mysql-8.0.20-linux-glibc2.12-x86_64/ mysql
[root@rocky86 local]# chown -R root.root mysql/
#创建环境变量
[root@rocky86 local]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@rocky86 local]# . /etc/profile.d/mysql.sh
[root@rocky86 local]# echo $PATH/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
#创建主配置文件
[root@rocky86 local]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
#创建数据目录
[root@rocky86 local]# mkdir -pv /data/mysql
mkdir: created directory '/data'
mkdir: created directory '/data/mysql'
#初始化,本地root用户空密码
#如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看
[root@rocky86 local]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
#加启动脚本
[root@rocky86 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@rocky86 local]# chkconfig --add mysqld
#启动服务
[root@rocky86 local]# systemctl start mysqld.service
3、源码编译安装
#安装依赖
[root@c7 ~]# yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel
libcurl-devel libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-
devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper
#创建用户
[root@c7 ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql
#创建目录，修改权限
[root@c7 ~]# mkdir -pv /data/mysql
[root@c7 ~]# chown mysql.mysql /data/mysql
#下载源码
[root@c7 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.51.tar.gz
[root@c7 ~]# tar xf mysql-5.6.51.tar.gz
[root@c7 ~]# cd mysql-5.6.51
[root@c7 ~]# cmake . -DCMAKE_INSTALL_PREFIX=/apps/mysql -
DMYSQL_DATADIR=/data/mysql/ -DSYSCONFDIR=/etc/ -DMYSQL_USER=mysql -
DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -
DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -
DWITHOUT_MROONGA_STORAGE_ENGINE=1 -DWITH_DEBUG=0 -DWITH_READLINE=1 -
DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DENABLED_LOCAL_INFILE=1 -
DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -
DDEFAULT_COLLATION=utf8_general_ci
[root@c7 ~]# make -j 2 && make install
#配置环境变量
[root@c7 ~]# echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@c7 ~]# . /etc/profile.d/mysql.sh
#初始化
[root@c7 ~]# cd /apps/mysql/
[root@c7 mysql]# ./scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
#生成配置文件
[root@c7 mysql]# cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnf
#生成服务脚本
[root@c7 mysql]# cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
#加开机启动项
[root@c7 mysql]# chkconfig --add mysqld
#启动服务
[root@c7 mysql]# service mysqld start
MySQL多实例（测试时使用，生产不建议使用）
#安装MySQL后
#创建相关目录
[root@rocky86 ~]# mkdir -pv
/mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@rocky86 ~]# chown -R mysql.mysql /mysql/
#生成三个实例的初始数据
[root@rocky86 ~]# mysql_install_db --user=mysql --datadir=/mysql/3306/data
[root@rocky86 ~]# mysql_install_db --user=mysql --datadir=/mysql/3307/data
[root@rocky86 ~]# mysql_install_db --user=mysql --datadir=/mysql/3308/data
#创建三个配置文件
[root@rocky86 ~]# vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid
[root@rocky86 ~]# sed 's/3306/3307/' /mysql/3306/etc/my.cnf >
/mysql/3307/etc/my.cnf
[root@rocky86 ~]# sed 's/3306/3308/' /mysql/3306/etc/my.cnf >
/mysql/3308/etc/my.cnf
#配置启动脚本
[root@rocky86 ~]# vim /mysql/3306/bin/mysqld
#!/bin/bash
PORT=3306
USER="root"
PWD="Magedu-m52"
CMD_PATH="/usr/bin"
BASE_DIR="/mysql"
SOCKET="${BASE_DIR}/${PORT}/socket/mysql.sock"
mysql_start(){
if [ ! -e "$SOCKET" ];then
echo "Starting MySQL..."
${CMD_PATH}/mysqld_safe --defaults-file=${BASE_DIR}/${PORT}/etc/my.cnf
&>/dev/null &
else
echo "MySQL is running..."
exit
fi
}
mysql_stop(){
if [ ! -e "$SOCKET" ];then
echo "MySQL is stoped..."
exit
else
echo "Stop MySQL..."
${CMD_PATH}/mysqladmin -u ${USER} -p${PWD} -S ${SOCKET} shutdown
fi
}
mysql_restart(){
echo "Starting MySQL..."
mysql_stop
sleep 2
mysql_start
}
case $1 in
start)
mysql_start
;;
stop)
mysql_stop
;;
restart)
mysql_restart
;;
*)
echo "Usage: ${BASE_DIR}/${PORT}/bin/mysqld {start|stop|restart}"
esac
#加可执行权限
[root@rocky86 ~]# chmod +x /mysql/3306/bin/mysqld
#启动
[root@rocky86 ~]# /mysql/3306/bin/mysqld
Usage: /mysql/3306/bin/mysqld {start|stop|restart}
[root@rocky86 ~]# /mysql/3306/bin/mysqld start
Starting MySQL...
[root@rocky86 ~]# mysql -S /mysql/3306/socket/mysql.sock
#停止
[root@rocky86 ~]# /mysql/3306/bin/mysqld stop
Stop MySQL...
#其它实例按此配置即可
#加开机启动项
[root@rocky86 ~]# vim /etc/rc.d/rc.local
for i in {3306..3308};do
/mysql/$i/bin/mysqld start;
done
[root@rocky86 ~]# chmod a+x /etc/rc.d/rc.local
四、数据库系统变量及用户管理
查看系统变量: SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path';
修改系统变量: SET GLOBAL max_connections = 100;
查：select Host,User from user;
增：create user test@'10.0.0.0/255.255.255.0' identified by '123456';
改：RENAME USER 'USERNAME'@'HOST' TO 'USERNAME'@'HOST';
SET PASSWORD FOR 'user'@'host' = PASSWORD('password');
删：DROP USER 'USERNAME'@'HOST'
查看权限：SHOW GRANTS FOR 'user'@'host';
授予权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' WITH GRANT OPTION;
取消权限：REVOKE ALL ON *.* FROM 'testuser'@'172.16.0.%';
工具：
mysqladmin [OPTIONS] command command....
-f|--force #删库时不确认
-C|--compress #启用压缩
-h|--host=name #指定服务器地址
-u|--user=name #指定用户名
-p|--password[=name] #指定连接服务器的密码
-P|--port=N #指定连接端口，默认3306
-l|--local #在本地执行，语句不写入binlog
-b|--no-beep #执行出错时不发出告警音
-s|--silent #如果无法连接，则静默退出
-c|--count=N #总共执行多少次，配合-i选项使用
-i|--sleep=N #持续执行命令，间隔N秒执行一次
-S|--socket=name #指定连接时使用的socket文件
-w|--wait[=#] #如果连接失败，是否等待重试，如果指定了具体数字，则表示重试几次
--plugin-dir=name #客户端查件目录
--print-defaults #显示运行参数，
--defaults-file=path #从指定文件中读取选项
--connect-timeout=N #指定连接超时时长
--shutdown-timeout=N #指定关机超时时长
--protocol=name #指定连接方式 (tcp|socket|pipe|memory)
--ssl #使用ssl安全功能
#命令
create databasename #创建新的数据库
debug #开启调试模式，将调试信息写入log
drop databasename #删除指定数据库
extended-status #显示扩展状态
flush-all-statistics #刷新所有统计表
flush-all-status #刷新状态和统计信息
flush-client-statistics #刷新客户端统计信息
flush-hosts #刷新所有缓存的主机
flush-index-statistics #刷新索引统计信息
flush-logs #刷新所有日志
flush-privileges #刷新访问权限
flush-binary-log #刷新二进制日志
flush-engine-log #刷新引擎日志
flush-error-log #刷新错误日志，开一个新日志文件
flush-general-log #刷新执行日志
flush-relay-log #刷新中继日志
flush-slow-log #刷新慢查询日志
flush-status #清除状态变量
flush-table-statistics #清除表统计信息
flush-tables #刷新所有表，会强制关闭己打开的表
flush-threads #刷新线程缓存
flush-user-statistics #刷新用户统计信息
flush-user-resources #刷新用户资源
kill id,id,... #关闭指定的线程
password [new-password] #修改密码
old-password [new-password] #修改密码时指定旧密码
ping #心跳检测
processlist #显示活动线程列表
reload #刷新授权信息
refresh #刷新所有数据表，重新打开日志文件
shutdown #关闭服务
status #简短显示服务端状态
start-slave #开启主从同步
stop-slave #停止主从同步
五、SQL语句：
1、库操作
增加：create database db1 charset utf8mb4;
查看：show databases; 查看所有库
show create database db1; 查看db1
改：alter database db1 charset gbk;更改编码格式
删：drop database db1;
2、表操作
增: use db1;切换文件夹
create table db1.t1(id int,name char);在db1下创建数据表
删：drop table tt1;
truncate t2；清空整张表
alter table tt1 drop age;
改：alter table t1 rename tt1;改表名
alter table tt1 modify name char(10);修改属性的类型
alter table tt1 change name mingzi char(3);改字段名
alter table tt1 add age;
update 表 set 字段=值 where 条件;
查：show tables;查看所有表名
show create table db1.t1;查看刚刚创建表的命令
desc t1;查看创建成功的表的结构
详细操作：
修改表
alter table t1 rename tt1;改表名
alter table tt1 modify name char(10);修改属性的类型
alter table tt1 change name mingzi char(3)，change age AGE char(3);改属性名
alter table t1 add gender char(4);
alter table t1 add gender char(4) first;在首部添加
alter table t1 add level int after ID;在id后添加
alter table tt1 add age;删除属性
create table t2 select user,host,password from mysql.user;复制表
基本类型：
整型：
tinyint
smallint
int 建议用int
bigint
浮点型：
float(m,n)总长度m，小数位n
double(m，n) n最多30，m最多255
decimal(65，30)
日期类型：
year 年
time 时：分：秒
date 年:月:日 （1000-01-01/9999-12-31）
datetime 年：月：日 时：分：秒 1000-01-01 00:00:00/9999-12-31 23:59:59
timestamp 年：月：日 时：分：秒 1970-01-01 00:00:00/2037
# 注意：timestamp应该勇于记录更新时间
字符类型：
char(n)定长 不够自动加空字符补全
varchar(n)可变长 建议用varchar
枚举类型和集合类型：
枚举类型enum("a","b","c","d") 多选1
集合类型set("a","b","c","d") 多选
约束条件：
not null不为空
not null default 111 默认值为111
unique单列唯一的，不能重复
unique（ip，port）多列唯一
not null 和 unique的联合======>会被识别为主键
not null unique 不为空且唯一
主键primary key 不允许为空且唯一
primary key(id,name)联合主键
auto_increnmen自增长 通常在主键后边
表之间的三种关系
一对多
在多的里添加外键
foreign key(dep_id) references dep(id)
dep_id对应另一个表中的id，id有的值dep_id才能用
多对多
另外建一个表，在这个表中添加外键
将其他两个表的主键
级联字段：
on delete cascade 级联删除 两个关联表删除一个表的某一行，另一个表包含该行主键的行也自动删除
on update cascade 级联更新
一对一
c_id int unique,
foreign key(c_id) references customer(id) on delete cascade on update cascade
3、记录操作
增：insert t2 values(1,"egon");
insert t2 values(2,"tom"),(3,"lxx"),(4,"hxx");
insert t2(id) values(5),(6);
insert into user select user,password from mysql.user;
删：delete from t2 where id=444;删除一行
改：update t2 set name="lxx",id=444 where id=4;
查：select * from t2;
select name from t2 where id=3;
单表查询：
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
distinct去除重复
先from，再where，再group，再having
可以进行四则运算 +-*/
concat拼接
select name ,concat(salary*12,"$") from emp;
select name ,concat_ws(":","annual_salary",salary*12,'$') as 年薪 from emp;第一个必须是分隔符
where字句：
比较运算符：
> < >= <= != <>
not
name is null
between 80 and 100
or
id in (3,5,7)
like模糊匹配
% 代表任意个字符
_ 代表一个字符
正则表达式：
name regexp 'n$'
例：
select * from emp where id >= 3 and id <= 5;
select * from emp where id between 3 and 5;
select * from emp where id not between 3 and 5;
select * from emp where id=3 or id=5 or id=7;
select * from emp where id in (3,5,7);
select * from emp where id not in (3,5,7);
select * from emp where id=3 or id=5 or id=7;
select * from emp where name like 'jin%';
select * from emp where name like 'jin___';
select * from emp where name regexp 'n$';
mysql> select * from emp where post_comment is not null;
Empty set (0.00 sec)
mysql> update emp set post_comment='' where id=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp where post_comment is not null;
分组：
在where语句之后
分完组之后只能看到分组的字段以及聚合的结果
max()
min()
avg()
sum()
count()
select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id;
order by：
select * from emp order by salary;默认升序 asc
降序 desc
limit：
select * from emp limit 0,5; 从1开始读5条
多表查询：
1、把多张物理表合并成一张虚拟表，进行过后续查询
2、子查询：从一张表中查询出结果，用该结果作为查询下一张表的过滤条件
内连接
select * from emp,dep where emp.dep_id=dep.id;
select dep.name,emp.name from emp inner join dep on emp.dep_id=dep.id
where dep.name = "技术";
左连接、右连接
#======>左链接：在内链接的基础上保留左表的记录
select * from emp left join dep on emp.dep_id=dep.id;
#======>右链接：在内链接的基础上保留右表的记录
select * from emp right join dep on emp.dep_id=dep.id;
全外连接：
select * from emp full join dep on emp.dep_id=dep.id
例：
多张表可以一直连
select * from emp
inner join dep
on emp.dep_id = dep.id
inner join dep as t1
on t1.id = dep.id;
子查询：
从一张表中查询出结果，用该结果作为查询下一张表的过滤条件
#查询平均年龄在25岁以上的部门名
select * from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);
in和not in
not in不支持null
all和any
any后也跟子查询语句 任何一个 any必须和比较运算符一起使用 =any等价于 in
in后可以跟子查询语句，也可以跟值 in（值1，值2，值3）
all后也跟子查询语句 所有 all必须和比较运算符一起使用 <>all等价于not in
exists
后面只能跟子查询
in直接将子查询语句结果搞出来
exists每一行都执行一次子查
select a.* from A a where exists(select 1 from B b where a.id=b.id)
exists查询是A表驱动B表（A表小于B表）
in子查询是B表驱动A表（B表小于A表）
无论哪个表大，用not exists都比not in 要快。not exists可以用索引
六、其他知识：
视图：
将查出来的虚拟表存储起来，修改视图修改的是被连接的表
creat view 表名 as 查询语句
触发器：
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
# 增=》insert
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
sql语句;
end
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
sql语句;
end
# 删除=》delete
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
sql语句;
end
create trigger tri_after_delete_t1 after delete on t1 for each row
begin
sql语句;
end
# 例如
insert into tt1 values(1,"egon",'male');
delimiter //
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
insert into tt2 values(NEW.name);
end //
delimiter ;
insert into tt1 values(2,"tom",'female');
事务：
事务就是将几条命令作为一个整体提交，要么几条命令同时执行成功，要么有一条失败其他命令也回滚到原来
特性：
原子性：事务不可分割
一致性：事务的执行的前后数据的完整性保持一致
隔离性：事务执行过程中，不应该受到其他事务的干扰
持久性：事务一旦结束，数据就会持久到数据库
start transaction; 开启事务
update user1 set balance=800 where id=1;
update user1 set balance=1100 where id=2;
update user1 set balance=1100 where id=3;
# 回滚
rollback;
# 真正提交到数据库内
commit;
事务的redo和undo
事务有四个特性：原子性、一致性、隔离性、持久性，在事务中，要么全部执行，要么全部不执行。
事务的原子性和隔离性由锁机制实现，而一致性和持久性则由undo(恢复日志)日志redo(重做日志)日志实现。
redo记录的是数据库尚未完成的操作，数据库的每次操作都会先记录到redo日志中，当数据库出现故障时，导致数据未能及时更新到数据文件中，数据库重启后，会重新把数据更新到数据文件中，redo记录的是物理日志，也就是磁盘数据页的修改。
当事务commit提交时，innodb引擎先将 redo log buffer 写入到 redo log file 进行持久化，待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化)，在持久化一个数据页之前，先将内存中相应的日志页持久化。
为什么需要先写redo log buffer和redo log file，不直接持久化到磁盘？
直接写入磁盘会导致严重的性能问题，InnoDB在磁盘中存储的基本单元是页，每次修改可能只变更了一页的几个字节，但是落盘时需要刷新整页的数据，浪费资源；并且一个事务可能修改了多页中的数据，页之间不连续，会产生随机IO，性能更差。
再写入到redo log buffer后，不会直接写入redo log file，需要等待操作系统调用fsync()，才会刷到磁盘。
undo用于记录更改前的一份copy，在操作出错时，可以用于回滚，它记录的是逻辑日志，即SQL语句，当我们执行一条insert命令时，undo log会记录相反的delete语句。
实现方式通过两个隐藏列trx_id（最近一次提交事务的ID）和roll_pointer（上个版本的地址），建立一个版本链。并在事务中读取的时候生成一个ReadView（读视图），在Read Committed隔离级别下，每次读取都会生成一个读视图，而在Repeatable Read隔离级别下，只会在第一次读取时生成一个读视图。
二者的区别：
redo记录的是物理日志，undo记录的是逻辑日志
redo用于恢复，undo用于回滚
redo是已提交的事务，undo是未提交的事务
存储过程：
存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调用它的名字可以执行其内部的一堆sql
程序与数据库结合使用的三种方式：
#方式一：
MySQL：存储过程
程序：调用存储过程
#方式二：
MySQL：
程序：纯SQL语句
#方式三：
MySQL:
程序：类和对象，即ORM（本质还是纯SQL语句）
# 创建无参存储过程
delimiter $$
create procedure p1()
begin
select * from emp;
end $$
delimiter ;
call p1();
# 创建有参存储过程 in：只能接收值 out：返回值
delimiter $$
create procedure p2(
in n int,
out res int
)
begin
select * from emp where id > n;
set res=1;
end $$
delimiter ;
import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123',database='db4',charset='utf8mb4')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
# cursor.execute("insert into user(name,pwd) values('egon','123'),('tom','456'),('jack','111');")
# sql="insert into user(name,pwd) values('%s','%s');" %('lili','123')
# cursor.execute(sql)
# %s不要加引号
# cursor.execute("insert into user(name,pwd) values(%s,%s);",('kkk','123'))
username = input("username>>>: ").strip()
password = input("password>>>: ").strip()
# sql = "select * from user where name='%s' and pwd='%s'" %(username,password)
# select * from user where name='egon' -- hello' and pwd='%s'
# select * from user where name='xxx' or 1=1 -- hello' and pwd='%s';
# rows=cursor.execute(sql)
rows=cursor.execute("select * from user where name=%s and pwd=%s",(username,password))
if rows:
print('ok')
else:
print('no')
conn.commit()
cursor.close()
七、存储引擎及索引
存储引擎
数据库常用的存储引擎有InnoDB及MyISAM
1、InnoDB
InnoDB是行级锁，支持事务，可以进行热备份，读写阻塞与事务隔离级别有关，支持多并发版本控制，缓存索引和数据，对数据一致性要求高
2、MyISAM
MyISAM是表级锁，不支持事务，读写相互阻塞，不支持MVCC（多版本并发控制），无法进行热备， 只缓存索引，适合读多写少的需求
3、行级锁、表级锁、叶级锁
行级锁:粒度最细,只针对当前操作加锁,可大大减少数据库操作冲突,但加锁开销大.分为共享锁和排它锁 ，其特点: 开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度最高
表级锁:粒度最大,对当前操作的整张表加锁,实现简单,资源消耗少，其特点: 开销小,加锁快,不会出现死锁,锁定粒度大,锁冲突概率高,并发低
页级锁:介于行锁和表锁中间,表锁速度快,冲突多,行锁冲突少,速度慢,所以有页锁,一次锁定相邻的一组记录，其特点: 开销和加锁时间介于行锁和表锁之间,会出现死锁,锁定粒度介于行锁和表锁之间,并发一般
4、多并发版本控制
通过创建数据的多个版本，并为每个事务提供适当的版本来实现并发访问数据而不会相互干扰的目的。MVCC提供了较高的并发性能和隔离级别，减少了锁冲突和阻塞，提高了数据库的吞吐量。
MVCC的核心思想是为每个事务创建一个独立的数据视图，该视图反映了在事务开始时数据库的一致状态。每个事务在执行读操作时，只能看到在其开始之前已经提交的版本。这种方式下，不同事务之间的读写操作可以并发进行，而不会产生不可重复读、脏读或幻读等并发问题。
5、事务隔离级别
共有四种隔离级别：分别是读未提交、读已提交、可重复读、串行化
读未提交：一个事务可以读取另一个未提交事务的数据
读已提交：一个事务只能读取已经提交的数据。
可重复读：一个事务在执行过程中多次读取相同的数据时，能够保证数据不会被其他事务修改。
串行化：事务会按顺序一个接一个地执行，不允许并发执行。安全级别最高，但是并发性能较低。
| 读未提交 | 读已提交 | 可重复读 | 串行化 |
|---|---|---|---|
| 脏读、幻读、不可重复读 | 幻读、不可重复读 | 幻读 | 无 |
脏读：事务可以读取另一个事务未提交的数据。
不可重复读：事务A首先读取了一条 数 据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，这就 是 所谓的不可重复读了。
幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的 M 条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。
MySQL的是rr(可重复读)
Django的是rc(不可重复读)
6、乐观锁及悲观锁
悲观锁：假定会发⽣并发冲突，屏蔽⼀切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到 提 交 事务。实 现⽅式：使⽤数据库中的锁机制，分为共享锁和排它锁。简单点来说，就是很悲观，总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。
乐观锁：假设不会发⽣并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通 过version的 ⽅式来进⾏锁定。实现⽅式：版本号机制或CAS算法实现。简单点来说，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
7、共享锁及排它锁
共享锁，又称之为读锁，简称S锁，当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。
排它锁，又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
共享锁只能兼容共享锁，不兼容排它锁，并且，排它锁互斥共享锁和其它排它锁。
索引
为什么要用索引？
目的是为了优化查询速度，但是一张表一旦创建了索引，会降低写速度
什么是索引？
索引是mysql数据库的一种数据结构，在mysql中称之为key
索引的数据结构：
二叉树，树中节点的度不大于2的有序树，它是一种最简单且最重要的树，可能会退化为链表。
红黑树，红黑树（Red Black Tree） 是一种自平衡二叉查找树，是在进行插入和删除操作时通过特定操作保持二 叉查找树的平衡，从而获得较高的查找性能。
b树，也是一种自平衡树，B树将一个节点的大小设置为每个数据页(Page，也 可以称为块，block)的大小，一般是16KB，B树中是将数据和索引放在一起的，以减少IO次数， 加快查询速度，一个节点能放多少数据，通常取决于一条数据占用的空间大小。
b+树(多路平衡搜索树)，io次数和树的高度有关（只有叶子节点存储数据），MySQL多用B+树
MySQL数据库索引有以下几种：主键索引、唯一索引、单值索引、复合索引
1、主键(PRIMARY KEY)
创建主键索引语法: alter table 表名 add primary key (字段);
删除主键索引语法: alter table 表名 drop primary key;
2、唯一索引(UNIQUE)
创建唯一索引语法: alter table表名add unique 索引名(字段); 或create unique index 索引名on表名(字段);
删除唯一索引语法: drop index 索引名 on表名;
3、单值索引(INDEX)
创建单值索引: alter table 表名 add index 索引名(字段); 或create index 索引名 on 表名(字段);
删除单值索引: drop index 索引名on表名;
4、复合索引(FULLTEXT)
创建复合索引: create index 索引名 on 表名(字段1,字段2); 或alter table表名 add index 索引名(字段,字段2);
删除复合索引: drop index 索引名 on 表名;
5、索引优化
最佳左前缀匹配原则：如果索引了多例，要遵循最左前缀原则，查询从最左前列开始并且 不跳过索引中的列；不在索引列上做任何计算、函数操作，会导致索引失效从而转向全表扫描；MySQL在使用不等于和is not null无法使用索引；like以通配符开头会使索引失效导致全表扫描；使用or连接或范 围查询索引失效；
内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。
左外连接时，左表（驱动表）会全表扫描，所以右边大表字段最好加上索引。
右连接同理，我们最好保证被驱动表上的字段建立索引。 排序优化：降序或者升序要统一
八、数据库备份与恢复
数据库备份分为增量备份及差异备份，二者的前提都是先实现完全备份
增量备份：
差异备份：
备份使用的是mysqldump工具
mysqldump
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
#选项说明
-uroot #MySQL用户名
-p123456 #MySQL密码
-A #备份所有数据库
-F #刷新日志
-E #导出事件
-R #导出存储过程以及自定义函数
--triggers #导出触发器
--single-transaction #以开启事务的方式备份数据
--master-data=2 #备份日志信息
--source-data=2 #备份日志信息
--flush-privileges #导出后刷新权限
--default-character-set=utf8 #设置字符集
--hex-blob #使用十六进制转储二进制
九、数据库日志
数据库日志共有五种，分别是事务日志、错误日志、通用日志、慢查询日志、二进制日志
1、事务日志
事务日志又分为redo和undo，详情可以查看上文的事务
2、错误日志
错误日志主要记录的内容有启动和关闭过程中输出的错误信息，运行时产生的错误，在主从复制架构中的从服务器上启动从服务器线程时产生的信息，错误日志的位置可以通过以下命令查看
mysql> select @@log_error;
+---------------------------+
| @@log_error |
+---------------------------+
| /var/log/mysql/mysqld.log |
+---------------------------+
3、通用日志
通用日志主要记录的是对数据库的操作，如启动和关闭数据库，增删改查语句等。通常情况下，通用日志是关闭的，需要手动去配置
#相关配置项
#general_log=0|1 是否开启通用日志
#general_log_file=/path/log_file 日志文件
#log_output=FILE|TABLE|NONE 记录到文件中还是记录到表中如果记录到表中，具体表为mysql.general_log
4、慢查询日志
慢查询日志是用来记录执行时间超过指定时间的查询语句，通过慢查询语句，可以查出一些效率慢的语句，进行改进，和通用日志一样，默认也是关闭的
#相关配置项
#slow_query_log=0|1 是否开启记录慢查询日志
#log_slow_queries=0|1 同上，MariaDB 10.0/MySQL 5.6.1 版本后已删除
#slow_query_log_file=/path/log_file 日志文件路径
#long_query_time=N 对慢查询的定义，默认10S，也就是说一条SQL查询语
句，执行时长超过10S，将会被记录
#log_queries_not_using_indexes=0|1 只要查询语句中没有用到索引，或是全表扫描，都会记
录，不管查询时长是否达到阀值
#log_slow_rate_limit=N 查询多少次才记录，MariaDB 特有配置项，默认值为1
5、二进制日志
二进制日志也可叫作变更日志，是 MySQL 中非常重要的日志。主要用 于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，但不包含查询操作语句，因为查询语句并 不会改变数据库中的数据。
事务日志和二进制日志的区别：
事务日志可以看作是在线日志，二进制日志可以看作是离线日志
事务日志记录事务执行的过程，包括提交和未提交，二进制日志记录只记提交的过程
事务日志只支持 InnoDB 存储引擎，二进制支持 InnoDB 和 MyISAM 存储引擎
二进制日志有三种记录模式：
Statement：基于语句的记录模式，日志中会记录原生执行的 SQL 语句，对于某些函数或变量，不 会替换。
Row：基于行的记录模式，会将 SQL 语句中的变量和函数进行替换后再记录。
Mixed：混合记录模式，在此模式下，MySQL 会根据具体的 SQL 语句来分析采用哪种模式记录日志。
相关配置如下：
sql_log_bin=1|0 #是否开启二进制日志，可动态修改，是系统变量，而非服务器选项
log_bin=/path/file_name #是否开启二进制日志，两项都要是开启状态才表示开启，此项是服务器选项，指定的是日志文件路径
log_bin_basename=/path/log_file_name #binlog文件前缀
log_bin_index=/path/file_name #索引文件路径
binlog_format=STATEMENT|ROW|MIXED #log文件格式
max_binlog_size=1073741824 #单文件大小，默认1G，超过大小会自动生成新的文件,重启服务也会生成新文件
binlog_cache_size=4m #二进制日志缓冲区大小，每个连接独占大小
max_binlog_cache_size=512m #二进制日志缓冲区总大小，多个连接共享
sync_binlog=1|0 #二进制日志落盘规则，1表示实时写，0 表示先缓存，再批量写磁盘文件
expire_logs_days=N #二进制文件自动保存的天数，超出会被删除，默认0，表示不删除
十、数据库集群
1、主从复制
原理：两日志三线程，主节点会开启一个线程 binlog dump 线程负责推送二进制日志到从节点，从节点开启两个线程:I/O线程负责接收主节点推送过来的二进制日志，并写入本地的中继日志 SQL线程负责从本地的中继日志导入SQL语句到数据库
配置：
#修改配置文件
[mysqld]
log_bin=/data/logbin/mysql-bin #启用二进制日志
server-id=177 #为当前节点设置全局唯一ID
#创建有复制权限的用户账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
#从节点：
#修改配置文件
[mysqld]
server-id=183 #指定server-id
read-only #只读模式
log-bin=/data/mysql/logbin/mysql-bin #指定二进制文件路径
#使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.177', MASTER_USER='repluser',
MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=157;
#查看从节点状态
mysql> show slave status\G
#启动同步
mysql> start slave;
常见问题：
数据损坏或丢失：
1、slave节点数据损坏或丢失，直接重置数据库，重新连接同步即可
2、master节点数据损坏或丢失，可以通过日志进行恢复，但建议使用MHA架构
同步出现延迟：
主从同步出现延迟的原因主要有：网络延迟、负载不一致、数据量过大、有大事务锁等
处理建议：网络延迟时可以选择使用同步复制，数据量过大时可以选择开启白名单，先将重要的数据复制过去，还可以将大事务拆分成小事务，减少锁
2、数据库分表
垂直切分
垂直分表：把常用的，不常用的，字段很长的拆出来
垂直拆分是指表数据列的拆分，把一张列比较多的表拆分成多张表。表的记录并不多，但是字段却很长，表占 用空间很大，检索表的时候需要执行大量的IO,严重降低了性能。这时候需要把大的字段拆分到另外一个表， 并且该表与原表是一 对一的关系.
优点:
1、数据库的拆分简单明了，拆分规则明确
2、应用程序模块清晰明确，整合容易
3、数据维护方便易行，容易定位
缺点:
1、部分表关联无法再数据库级别完成，需要在程序中完成
2、对于访问极其频繁且数据量超大的表仍然存在性能瓶颈，不一定满足需求
3、事务处理相对更为复杂
4、切分达到一定程度后。扩展性会遇到限制 -5过度切分可能会带来系统过度复杂而难以维护
水平切分
水平分表:一条记录一条记录切断分出来
水平拆分是指数据表行的拆分，表的行数超过了200万时，就会变慢，这时候可以把一张表的数据拆成多张表来存放
优点:
1、表关联基本能够在数据库端全部完成
2、不会存在某些超大型数据量和高负载的表遇到瓶颈的问题
3、应用程序端整体架构改动相对较少
4、事物处理相对简单
5、只要切分规则能定义好，基本上较难遇到扩展性限制
缺点:
1、切分规则相对更为复杂，很难抽象出一个能满足整个数据库的切分规则
2、后期数据的维护难度有所增加，人为手工定位数据更为困难
3、应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难
3、MyCat读写分离
工作原理：和代理类似，主要就是拦截用户发过来的SQL语句，对SQL语句进行一些特定的分析，将SQL发往后端真正的数据库，并对返回的结果进行一些处理，最后返回给用户。
配置：
#先实现主从
#安装java
[root@rocky8 ~]# yum install -y java
#下载MyCat
[root@rocky8 ~]# wget http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
#解压
[root@rocky8 ~]# tar xf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps
#写环境变量
[root@rocky8 ~]# vim /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
[root@rocky8 ~]# source /etc/profile.d/mycat.sh
#启动
[root@rocky8 ~]# mycat start
#在后端数据库创建MyCat连接账号
#在master节点上创建账号并授权，该帐号会被同步到 slave 节点
mysql> create user 'mycater'@'10.0.0.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON db1.* TO 'mycater'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#修改sever.xml配置连接数据库账号
....
#修改 schema.xml 实现读写分离策略
<writeHost host="host1" url="10.0.0.177:3306" user="mycater"
password="123456">
<readHost host="host2" url="10.0.0.183:3306" user="mycater"
password="123456" />
#重启mycat
4、MHA
工作原理：
1、利用 SELECT 1 As Value 做 master 健康监测
2、一旦 master 宕机，先看主节点SSH能否连接，主节点的 SSH 能连接，mha将二进制日志保存至从节点并应用。主节点的 SSH 不能连接，对比从库之间的 relaylog 的差异，应用差异的中继日志（relay log）到其他的 slave
3、选举新的 Master：若设定权重优先被提升为master ，但是默认情况下如果一个 slave 落 后 master 二进制日志超过 100M 的relay logs，即使有权重，也会失效，如果设置 check_repl_delay=0，即使落后很多日志，也强制选择其为新主 如果从库数据之间有差异，最接近于 Master 的 slave 成为新主 如果所有从库数据都一致,按照配置文件顺序最前面的当新主
4、提升新的master。VIP漂移。发送报警。其他的 slave 连接新的 master 进行复制。新的主节点只 读 状态失效。
5、MHA 是一次性的高可用性解决方案，Manager 会自动退出
配置：
#先实现主从
#先在mha-manager上安装manager包和node包
[root@mha-manager ~]# wget https://github.com/yoshinorim/mha4mysqlmanager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]# wget https://github.com/yoshinorim/mha4mysqlnode/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#所有MySQL节点上安装node包
#所有节点实现ssh-key免密登录
#在 mha-manager 节点创建相关配置文件
#配置告警脚本及VIP漂移脚本
十一、数据库压力测试
数据库压力测试的常用工具有mysqlslap，此工具为官方提供的压力测试工具
参数：
mysqlslap [OPTIONS]
#常用选项
-?|--help #显示帮助
-V|--version #显示版本信息
-h|--host=name #服务器地址
-p|--password[=name] #连接密码
-P|--port=N #端口
-a|--auto-generate-sql #自行生成测试表和数据，测试结束后会被删除
-x|--number-char-cols=N #自动生成的测试表中包含多少个字符类型的列，默认1
-y|--number-int-cols=N #自动生成的测试表中包含多少个数字类型的列，默认1
-q|--query=sql #执行自定义SQL语句
-S|--socket=name #指定连接使用的socket文件
-c|--concurrency=N #表示并发量，即模拟多少个客户端同时执行select，可指定多个值，#以逗号或者--delimiter参数指定值做为分隔符,如--concurrency=100,200,500
-i|--iterations=N #测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
-e|--engine=name #要测试的引擎，可以有多个，用分隔符隔开。例如 --engines=myisam,innodb
-C|--compress #如果服务器和客户端都支持压缩，则压缩信息
--print-defaults #在终端输出默认选项和参数
--only-print #只打印测试语句而不实际执行
--commint=N #多少次DML后提交一次事务
--detach=N #执行N条语句后断开重连
--no-drop #测试完成后不删除测试库
--create-schema=name #自定义测度库名称
--number-of-queries=N #总的测试查询次数(并发客户数乘以每客户查询次数)
--auto-generate-sql-add-autoincrement #在测试数据表中增加 auto_increment 列，从mysql 5.1.18版本开始支持
--auto-generate-sql-load-type=name #测试类型 mixed|update|write|key, 默认为mixed
