Python全栈（三）数据库优化之13.MySQL高级-主从复制和数据库总结

一、主从复制

1.目的和解决的问题：

目的：读写分离，一个数据库只负责读，一个只负责写。
解决的问题：

• 数据分布:随意停止或开始复制，并在不同地理位置分布数据备份；
• 负载均衡:降低单个服务器的压力；
• 故障切换:帮助应用程序避免单点失败；
• 升级测试:可以使用更高版本的MySQL作为从库。

2.基本原理：

如图

3.复制的三步骤：

（1）master将改变记录到二进制日志，这些记录过程叫做二进制日志事件binary log events；
（2）slave将master的binary log events拷贝到它的中继日志；
（3）slave重做中继日志中的事件,将改变应用到自己的数据库中。
MySQL复制是异步的且串行的。

4.复制的基本原则：

• 每个slave只有一个master；
• 每个slave只能有一个唯一的服务器ID；
• 每个master可以有多个salve。

5.一主一从常见配置：

• MySQL版本一致且后台服务可以运行；
• 主从主机可以相互通信；
• 主从配置都在[mysqld]结点下,都是小写。

主机（Windows系统）配置my.ini：

server_id=2 #[必须]主服务器唯一ID，不能与从机重复
log-bin=自己本地的路径/mysqlbin（例如mysql-bin） #[必须]启用二进制日志
log-err = 自己本地的路径/mysqlerr #[可选]启用错误日志

从机（Linux Ubuntu系统）配置mysqld.cnf(/etc/mysql/mysql.conf.d/mysqld.cnf)：

bind-address=0.0.0.0 #让任何IP地址连接
server_id=1 #[必须]主服务器唯一ID
log-bin=自己本地的路径/mysqlbin（例如/var/log/mysql/mysql-bin.log） #[可选]启用二进制日志

修改过配置文件之后，要重启MySQL服务：

service mysql restart

关闭Linux防火墙：

service iptables stop

关闭Windows防火墙：
关闭专用网络防火墙。
在Windows主机上建立账户并授权slave：

create user 'slave'@'从机数据库IP' identified with mysql_native_password by 'password';
#例如create user 'zhangsan'@'192.168.1.1' identified with mysql_native_password by '123456';

授权并查看状态：

grant replication slave on *.* to 'slave'@'从机数据库IP' identified by 'password';
#例如grant replication slave on *.* to 'zhangsan'@'192.168.1.1' identified by '123456';

show master status;

记录下File和position的值。

6.配置Linux从机

change master to master_host = '192.168.0.161',
master_user = 'jerry',
master_password = '123456',
master_log_file = 'binlog.000004',
master_log_pos= 908;

7.测试是否配置成功

start slave;  #启动从服务器复制功能

show slave status;

下面两个参数都是yes,则说明主从配置成功：
slave_io_running:yes；
slave_sql_running:yes。

主从复制总结：
当读数据和写数据都是对同一个数据库进行操作时，可能会遇到性能瓶颈，所以新增一个与原数据库相同的数据库作为从数据库，即备份，实现读写分离（主数据库写，从数据库读），既能提高读写效率，又能提高安全性，但是会产生一定延迟。

二、MySQL操作规范

1.命名规范

• 表名建议使用有业务意义的英文词汇，必要时可加数字和下划线，并以英文字母开头。
• 库、表、字段全部采用小写：
  MySQL在Linux下默认是区分大小写的，而在Windows下不区分大小写。因此，防止出现问题，建议都设置为小写。
• 避免用 MySQL 的保留字。
• 命名（包括表名、列名）禁止超过 30 个字符。
• 临时库、表名必须以tmp为前缀，并以日期为后缀，如：tmp_shop_info_20200120。
• 备份库、表必须以bak为前缀，并以日期为后缀，如：bak_shop_info_20200120。
• 索引命名：
  非唯一索引必须按照"idx_字段名称"或"idx_表名_字段名称"进行命名；
  唯一索引必须按照"uniq_字段名称"或"uniq_表名_字段名称"进行命名。

2.设计规范

• 主键：
  表必须有主键；
  不使用更新频繁的列做主键；
  尽量不选择字符串列做主键；
  不使用 UUID（不重复的字符串）、MD5 HASH 做主键；
  默认使用非空的唯一键。
• 如无特殊要求，建议都使用 InnoDB 引擎。
• 默认使用 utf8mb4 字符集，数据排序规则使用 utf8mb4_general_ci：
  utf8mb4 为万国码，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情。
• 所有表、字段都需要增加 comment 来描述此表、字段所表示的含义：
  如data_status TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效'。
• 尽可能不使用 TEXT、BLOB 类型：
  原因：会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用TEXT、BLOB类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。字段用多大就取多大，避免资源浪费和性能下降。
• 单表列数目建议小于30。

3.SQL语句规范

• 避免隐式转换：
  varchar要加引号。
• 尽量不使用select *,只select需要的字段：
  读取不需要的列会增加CPU、IO、NET消耗，并且不能有效的利用覆盖索引。使用SELECT *容易在增加或者删除字段后导致程序报错。
• 建议将子查询转换为关联查询。
• 建议应用程序捕获SQL异常，并有相应处理：
  可以避免数据库攻击。

4.行为规范

• 批量导入、导出数据必须提前通知DBA协助观察；
• 不在业务高峰期批量更新、查询数据库；
• 删除表或者库要求尽量先重命名rename、备份，观察几天，确定对业务没影响，再drop

三、数据库基础总结

1.数据类型

• 整数：tinyint、smallint、mediumint、int、bigint；
• 实数：float、double；
• 字符串：varchar、char、text、blob；
• 日期时间：timestamp、datetime。

2.列属性

• 自增auto_increment；
• 默认值default；
• 非空not null；
• 零填充zerofill；
• 无符号unsigned。

3.数据库操作

连接数据库：

mysql -uroot -p #更安全
或者
mysql -u root -proot

退出数据库：

exit
--或者quit

查看所有数据库：

show databases;

创建数据库：

#创建数据库
create database 数据库名字 charset = utf8;
#查看创建数据库的命令
show create database 数据库名字;
#使用数据库
use mydatabase;

4.数据库表操作

查看当前数据库中所有表：

show tables;

创建表：

create table 数据表名字(字段 类型 约束[,字段 类型 约束]);

查看表：

desc demo1;

查看表的创建语句：

show create table salary;

5.DML数据库管理语言

新增：

#全列插入：
insert [into] 表名 values(...);
#部分插入：
insert into 表名(列1 ,...) values(值1 ,...);

修改：

update 表名 SET 字段1=新值,字段2=新值[where 条件];
update 表名 SET 字段1=新值,字段2=新值;

删除：

delete from 表名 [where条件];
delete from 表名; #清空表中的数据

查询：
where子句：=、>、<
逻辑运算符：and、or、not
模糊查询：%、_
范围查询：in、between…and…
空判断：is null、is not null
聚合函数：count、max、min、sum、avg
分组：group by
排序：asc、desc
分页：limit

四、MySQL-视图、索引、事务、引擎总结

1.视图

（1）作用：
视图是虚拟的表，只包含动态检索数据的查询，不包含数据；
简化操作,隐藏细节，保护数据；
对视图的更新会作用于基表，一般不更新。

（2）视图操作
定义视图：

create view 视图名称 as select语句;

查看视图：

show tables;

使用视图：

select * from v_stu_score;

删除视图：

drop view 视图名称;

2.索引

（1）索引类型：
唯一索引：具有唯一性约束，不允许为空；
主键索引：特殊的唯一索引，不允许有空值；
单值索引：单列；
复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
（2）索引操作：
创建索引：

create [unique] index 索引名称 on 表名(字段名称(长度));

查看索引：

show index from 表名;

删除索引：

drop index 索引名称 on 表名;

（3）索引对性能的影响：

• 大大减少服务器需要扫描的数据量；
• 帮助服务器避免排序和临时表；
• 将随机I/O变顺序I/O；
• 大大提高查询速度，降低写的速度，占用磁盘。

（4）索引的使用场景：

• 对于非常小的表，大部分情况下全表扫描效率更高；
• 中到大型表，索引非常有效；
• 特大型的表，建立和使用索引的代价将随之增长,可以使用分区技术来解决。

（5）唯一索引与主键索引的区别：

• 一个表只能有一个主键索引，可以有很多个唯一索引；
• 主键索引一定是唯一索引，唯-索引不是主键索引；
• 主键可以与外键构成参照完整性约束，防止数据不一致。

（6）不建立索引的情况：

• 频繁更新的字段不适合建立素引；
• where条件里面用不到的字段不创建索引；
• 表记录太少，当表中数据量超过三百万条数据，可以考虑建立素引；
• 数据重复且平均的表字段，比如性别、国籍。

3.事务

（1）ACID特性：

• A原子性
• C一致性
• I隔离性
• D持久性

（2）事务操作
开启事务：

begin;
start transaction;

提交事务：

commit;

回滚事务：

rollback;

4.存储引擎

（1）InnoDB：
支持事务，支持行级锁，支持外键。
（2）MyISAM：
不支持事务和表级锁，不支持外键。
（3）CSV：
以文本形式存储，不支持索引和自增。
（４）Memory：
存储在内存中，服务重启后数据丢失。
（５）选择：
根据事务、崩溃恢复、备份三个特性选择。

五、MySQL优化总结

1.分析慢SQL方法

（1）记录慢查询日志

• 开启慢查询日志set global slow_ query_ log = 1;
• 设置阙值，默认是10秒set global long_ query_ time = 3;
• 查看多少条慢SQLshow global status like "%slow_queries%';

（2）使用explain
用法：explain + sql
（3）使用show profile

• set profiling = 1;
• show profiles;
• show profile cpu,block io for query 临时表ID;

2.SQL语句优化

• 选择正确的存储引擎；
• 优化字段的数据类型；
• 为搜索字段添加索引；
• 避免使用select *，从数据库里读出越多的数据，查询就会变得越慢；
• 尽可能使用NOT NULL。

3.SQL注释

4.分区

（1）原理
分区的主要目的是将数据按照一个较粗的粒度分在不同的表中, 这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便。
对用户而言,分区表是-一个独立的逻辑表，但是底层MySQL将其分成多个物理子表，这对用户来说是透明的，每一个 分区表都会使用一个独立的表文件。
（2）方式

• RANGE分区
• LIST分区
• HASH分区

（3）适用场景

• 表非常大，无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据；
• 分区表的数据更容易维护，可以对独立的分区进行独立的操作；
• 分区表的数据可以分布在不同的机器上,从而高效使用资源；
• 可以使用分区表来避免某些特殊的瓶颈；
• 可以备份和恢复独立的分区。

5.主从复制

（1）原理

• 在主库上把数据更改记录到二进制日志；
• 从库将主库的日志复制到自己的中继日志；
• 从库读取中继日志中的事件,将其重放到从库数据中。

（2）解决的问题

• 数据分布；
• 负载均衡；
• 故障切换；
• 升级测试。

6*.SQL查询的安全方案

• 使用预处理语句防止SQL注入；
• 写入数据库的数据要进行特殊字符的转义；
• 查询错误信息不要返回给用户，将错误记录到日志。

转载：https://blog.csdn.net/CUFEECR/article/details/104053466