社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  Python

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

cupyter • 5 年前 • 266 次点击  

一、主从复制

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 #[可选]启用错误日志
  • 1
  • 2
  • 3

从机(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) #[可选]启用二进制日志
  • 1
  • 2
  • 3

修改过配置文件之后,要重启MySQL服务:

service mysql restart
  • 1

关闭Linux防火墙:

service iptables stop
  • 1

关闭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';
  • 1
  • 2

授权并查看状态:

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;
  • 1
  • 2
  • 3
  • 4

记录下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;
  • 1
  • 2
  • 3
  • 4
  • 5

7.测试是否配置成功

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

show slave status;
  • 1
  • 2
  • 3

下面两个参数都是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
  • 1
  • 2
  • 3

退出数据库:

exit
--或者quit
  • 1
  • 2

查看所有数据库:

show databases;
  • 1

创建数据库:

#创建数据库
create database 数据库名字 charset = utf8;
#查看创建数据库的命令
show create database 数据库名字;
#使用数据库
use mydatabase;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

4.数据库表操作

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

show tables;
  • 1

创建表:

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

查看表:

desc demo1;
  • 1

查看表的创建语句:

show create table salary;
  • 1

5.DML数据库管理语言

新增:




    
#全列插入:
insert [into] 表名 values(...);
#部分插入:
insert into 表名(1 ,...) values(1 ,...);
  • 1
  • 2
  • 3
  • 4

修改:

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

删除:

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

查询:
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语句;
  • 1

查看视图:

show tables;
  • 1

使用视图:

select * from v_stu_score;
  • 1

删除视图:

drop view 视图名称;
  • 1

2.索引

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

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

查看索引:

show index from 表名;
  • 1

删除索引:

drop index 索引名称 on 表名;
  • 1

(3)索引对性能的影响:

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

(4)索引的使用场景:

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

(5)唯一索引与主键索引的区别:

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

(6)不建立索引的情况:

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

3.事务

(1)ACID特性:

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

(2)事务操作
开启事务:

begin;
start transaction;
  • 1
  • 2

提交事务:

commit;
  • 1

回滚事务:

rollback;
  • 1

4.存储引擎

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

五、MySQL优化总结

1.分析慢SQL方法

(1)记录慢查询日志

  • 开启慢查询日志 set global slow_ query_ log = 1;
  • 设置阙值,默认是10秒 set global long_ query_ time = 3;
  • 查看多少条慢SQL show 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注释

符号 举例
# SELECT * FROM USER WHERE NAME = ‘1aa1’ #and age = 22
–空格 SELECT * FROM USER WHERE NAME = ‘1aa1’ – and age = 22
/**/ SELECT * FROM USER WHERE NAME = ‘1aa1’ / and age = 22 /

4.分区

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

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

(3)适用场景

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

5.主从复制

(1)原理

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

(2)解决的问题

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

6*.SQL查询的安全方案

  • 使用预处理语句防止SQL注入;
  • 写入数据库的数据要进行特殊字符的转义;
  • 查询错误信息不要返回给用户,将错误记录到日志。
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/53442
 
266 次点击