mysql数据库初步认识

一、关系型数据库和非关系型数据库的区别：

关系型数据库模型是把复杂的数据结构归结为简单的二元关系，维护的是一张张二维表。

非关系数据库模型是用来缓解关系型数据库的并发负载的，缓解硬盘I/O进程压力，常用做缓存数据库，同时支持持久化存储

关系型数据库：

1.有固定的表结构

2.有标准的sql语句来进行管理

3.对数据的安全性一致性遵循ACID理论（支持事务）

4.常见的关系型数据库有：mysql/mariadb/oracle/sql server

非关系型数据库：

1.没有固定的表结构，非关系型数据库存储的数据类型丰富：字符串、图片型、文档类型

2.没有标准的查询语句，通过api接口进行管理

3.主要用于缓存数据库

4.常见的非关系型数据库有：redis、memcached、mongodb

二、mysql的存储引擎

存储引擎类型：

1.innodb存储引擎（默认引擎）：

支持事务处理，支持外键，支持并发控制

2. myisam存储引擎：
插入数据快，空间内存使用比较低,不支持事务
3.memory内存型引擎：
将数据存储到内存中，查询引用快速，但安全性不高
4.archive归档引擎：
只有insert和select操作，记录日志信息可以使用

相关sql语句：

mysql> create table t1(id int(5)) engine=innodb;  #使用innodb引擎创建表
mysql> show engines;    #查看引擎类型
mysql> show variables like '%storage_engine%';    #查看默认引擎

 三、事务控制语言（TCL）

事务处理遵循 ACID原理，以确保数据的一致性和可靠性。

ACID原理四大特性

1. Atomicity (原子性)

   原子性意味着事务中的所有操作要么全部成功完成，要么全部失败。事务不能部分成功。如果事务中的任何一个操作失败，整个事务都将被回滚。

2. Consistency (一致性)

   一致性保证事务完成后，数据库必须处于一致的状态。这意味着事务开始前和结束后，数据库都必须满足所有的约束条件。事务不能破坏数据库的完整性。

3. Isolation (隔离性)

   隔离性确保并发执行的事务彼此独立，不会互相干扰。这意味着一个事务不应该能看到另一个事务未提交的更改。数据库系统必须采取措施确保事务之间不会产生冲突。

4. Durability (持久性)

   持久性意味着一旦事务提交，它所做的更改就是永久的，即使系统出现故障也不会丢失。一旦事务提交，其效果将永久保存在数据库中。

常用命令

mysql> begin;       #开始一个事件
mysql> insert into my_table(id, name) values(1, 'Tian');
mysql> update my_table set name = 'Jack' where id = 1;

mysql> commit;      #如果一切顺利，提交事务

mysql> rollback;    #如果出现问题，回滚事务

四、sql语句

SQL语句类型

DDL    数据定义语句

如：create drop alter
DML    数据操纵语句

如：insert delete update
DCL    数据控制语句

如：grant
DQL    数据查询语句

如：select

 库、表操作

创建一个库：create database db1;

查看库：show databases;

进入这个库：use db1

创建表：create table t1(id int(5));

查看表：show tables;

查看表名表的字段（表结构）：desc t1;

表里面的内容（表记录）：select * from t1;

查看表的状态：show table status like 't1';

修改表：alter、update、drop

添加字段：alter table t1 add name varchar(10) after id;

删除字段：alter table t1 drop name;

修改字段：alter table t1 modify name varchar(11);

修改表名称：alter table t1 rename t2;

修改记录：update t1 set name='tian' where name='jack';

添加记录：insert into t1(name) value('jack');

删除记录：delete from t1 where name='tian';

各种查询相关：and、or、between...and、is null、order by(desc)、limit 0,5、group by

删除表：drop table t1;

删除库：drop database db1;

DDL一般后面要跟类型，如database、table、user等

对库、表结构、字段的操作使用DDL

对表记录的操作使用DML

数据类型

1.整型：int

-2,147,483,648——2,147,483,647

超出范围用bigint

2.浮点数类型：float、double

float(2,3)        #一共5位，小数占3位

3.字符串类型：char、varchar

char：定长字符串，存取速度快，占用空间大，长度范围0~255

varchar：可变字符串，存取速度慢，占用空间小，长度范围0~65535

4.枚举类型：enum

5.日期类型：year、date、time、datetime、timestamp

五、表的完整性约束

约束条件（键的类型）

primary key        主键，唯一标识记录，不可为空
foreign key         外键，实现表与表之间的关联
null                     空
not null               非空
unique key         唯一标识
auto_increment  自动增长
default                设置默认值
unsigned            无符号，取正数

 用法

create table t1(id(int) primary key auto_increment,name varchar(10) unique key not null);
alter table t1 add math float(5,2) unique key default 100 not null;    #位置随便换 

六、常用SQL语句

复制表

create table t2 like t1;    #复制表结构记录
create table t1(select id,name from t2)    #复制某几个字段记录

更改密码的四种方法

mysqladmin -u'$user' -p'$passwd' password '$passwd2'
update mysql.user set authentication_string=password('$passwd') where User='$user';
alter user '$user'@'localhost' identified by '$passwd';
set password for '$user'@'localhost' = password('$passwd');

权限相关

grant $权限列表 on $库.$表 to '$user'@'localhost';     #权限之间逗号隔开，所有库表用*.*表示
revoke $权限列表 on $库.$表 from '$user'@'localhost';  #移除权限
show grants for '$user'@'localhost';    #查看用户权限
#或
select * from (mysql.user|mysql.db|mysql.tables_priv|columns_priv);    #针对用户、库、表、数据列级别的权限查看

七、mysql日志

日志类型

1.错误日志 ：log-error=/var/log/mysqld.log
2.二进制日志（binary log）：

server-id=1

log-bin=/var/log/mysql-bin/mylog
3.通用查询日志：只记录select
4.中继日志 （relay log）： 用于记录主服务器的binlog日志内容
5.慢查询日志 （slow log）：用于指导优化，

slow_query_log=1         #开启

slow_query_log_file=/var/log/mysql-slow/slow.log         #定义慢查询日志位置

long_query_time=3         #单位秒

binlog 日志的格式

row     记录所有的数据的更改
statement    记录sql语句，但是遇到某些命令，可能在binlog日志恢复数据的时候出现不了
mixed  混合模式

日志相关命令

linux系统中命令：

mysqlbinlog mylog.000001 -v     #查看binlog日志命令

sql语句：

flush logs    #截断日志
reset master    #删除所有二进制日志
purge binary logs to 'mylog.000004'     #删除编号1、2、3的binlog
set sql_log_bin=0/1    #临时关闭/开启binlog
set global binlog_format='MIXED';    #修改binlog记录格式为mixed
set global binlog_format='ROW';      #修改binlog记录格式为row

八、mysql备份

按备份状态区分，可分为热备、冷备、温备

1.热备：在线备份
2.冷备：需要关闭数据库
3.温备：针对myisam的备份，备份时候只可读不可写

按备份类型区分，可分为逻辑备份和物理备份

逻辑备份：
1.备份sql语句
2.恢复速度慢
3.占用空间小
4.I/O负载大
5.备份方式有mysqldump

物理备份：
1.备份物理文件
2.恢复速度快
3.占用空间大
4.I/O负载大
5.备份方式有ibbackup、mysqlbackup、xtrabackup

 物理备份

#制作备份
innobackupex --user=root --password='123' /xtrabackup    #完整备份
innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/前一天    #增量备份
innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/第一天    #差异备份

#加载备份
innobackupex --apply-log /xtrabackup    #完整备份
innobackupex --apply-log --redo-only /xtrabackup/第一天 incremental-dir=/xtrabackup/后一天  #增量备份，依次加载
innobackupex --apply-log --redo-only /xtrabackup/第一天 incremental-dir=/xtrabackup/某一天
#差异备份，对特定某天加载

#恢复数据
innobackupex --copy-back /xtrabackup/第一天    #完整、增量、差异备份

逻辑备份

#备份数据
mysqldump -uroot -p123 -h 192.168.89.140 -P 3306 db1 t1 t2 > t1t2.bak   #备份db1库的t1、t2表
mysqldump -uroot -p123 -h 192.168.89.140 -P 3306 -B db1 db2 > db1db2.bak #备份db1、db2库
mysqldump -uroot -p123 -h 192.168.89.140 -P 3306 -A > all.bak    #备份所有库
mysqldump -uroot -p123 -h 192.168.89.140 -P 3306 -d db1 t1 >db1_t1.sql #只导出表结构

#恢复数据
mysql -uroot -p123 < db1db2.bak
mysql -uroot -p123 db1 < t1t2.bak
mysql> set sql_log_bin=0;   #临时关闭binlog
mysql> source t1t2.bak;     #恢复数据
mysql -uroot -p123 -D db2 < db1_t1.sql    #恢复表结构

#表结构恢复数据方式
mysql> show variables like "secure_file_priv";    #查看安全目录
mkdir /sql && chown mysql.mysql /sql && echo "secure_file_priv=/sql" >> /etc/my.cnf && systemctl restart mysqld    #修改安全目录位置
mysql> select * from db1.t1 into outfile '/sql/t1.data';    #导出数据
mysql> load data infile '/sql/t1.data' into table db2.t1;    #导入数据

#binlog日志恢复数据方式
mysqlbinlog mylog.000002 -v    #查看binlog日志
mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p'qf123'    #根据开始结束位置进行数据恢复

 九、主从同步

gtid主从同步

master：

vim /etc/my.cnf
server-id=1
log-bin=mylog
gtid_mode=ON
enforce_gtid_consistency=1

mysql> create user slave@'%' identified by '123';
mysql> grant repilication slave,reload,super on *.* to slave@'%';

slave：

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

mysql> \e
change master to 
master_host='',
master_user='',
master_password='',
master_auto_position=1;
mysql> ;
mysql> start slave;
mysql> show slave status;

如果遇到错误，重新编写

mysql> stop slave;
mysql> reset master;
mysql> reset slave;
mysql> \e
change master to 
master_host='',
master_user='',
master_password='',
master_auto_position=1;
mysql> ;
mysql> start slave;
mysql> show slave status;

 binlog日志主从

master：

vim /etc/my.cnf
server-id=1
log-bin=mylog

mysql> show master status;

slave：

mysql> \e
change master to 
master_host=' ',
master_user=' ',
master_password=' ',
master_log_file=' ',
master_log_pos= ;
mysql> ;
mysql> start slave;
mysql> show slave status;

十、索引相关

索引作为一种数据结构，其用途是用于提升检索数据的效率。

索引的分类

- 普通索引（INDEX）：索引列值可重复
- 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL
- 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
- 全文索引（FULL TEXT）：给每个字段创建索引

 相关命令

create table t1(id int auto_increment,name varchar(11),sex enum('m','f'),index idindex(id));    #创建普通索引
create table t1(id int auto_increment,name varchar(11),sex enum('m','f'),unique index idindex(id));    #创建唯一索引
alter table t1 add index idindex(id);    #额外添加索引
create index idindex on t2(id);
alter table t1 drop index idindex;       #删除索引
show index from t1;                      #查看索引

索引失效的原因？

1.数据类型不匹配：如果查询中使用了索引列但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较
2.数据更新频繁：索引是为了提高查询性能而创建的，但在数据更新频繁的情况下，索引会导致插入、更新和删除操作的性能下降
3.索引中断、模糊查询

 导致SQL执行慢的原因

1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2.没有索引或者索引失效.
3.数据过多

十一、mysql的锁机制

根据对数据操作的粒度区分，可分为全局锁、表锁、行锁

根据对数据操作的类型区分，可分为读锁（共享锁/S锁）、写锁（排他锁/X锁）

读锁：对表加读锁后，自己和其他线程只能读取该表，不能对其修改。

写锁：当前用户对锁定表的查询、更新、插入操作都可以执行，其他用户对锁定表的查询被阻塞，需要等待锁被释放

相关sql语句

lock table t1 read/write;    #给t1表上读/写锁
unlock tables;    #解锁
show open tables where In_use>0;    #查看哪些表格被上锁

十二、mysql优化方案

1.修改引擎和字符集

永久修改

vim /etc/my.cnf
default_storage_engine=myisam
character_set_server=utf8

临时修改

create table t1(id int(3)) engine=myisam character set=utf8;

2.开启慢查询 

vim /etc/my.cnf
slow_query_log=1         #开启慢查询
slow_query_log_file=/var/log/mysql-slow/slow.log         #定义慢查询日志位置
long_query_time=3         #单位秒

show variables like '%query%';     #查看慢查询日志是否开启

3.创建索引

4.最大连接数等

vim /etc/my.cnf
max_connections=1024
connect_timeout=5
wait_timeout=10
max_connect_error=10     #flush hosts解锁

5.设置缓存池大小

vim /etc/my.cnf
innodb-buffer-pool-size=2G

6.对mysql各种监控项进行监控

tps qps iops
磁盘空间    du -sh
连接数     mysqladmin status -uroot -p123
cpu使用率  top
主从同步状态  show slave status;
主从延迟时间  show slave status;         #seconds_behind_master
进程、端口    ps aux    netstat -lntp