一、主从复制
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
查看所有数据库:
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特性:
(2)事务操作
开启事务:
begin ;
start transaction ;
提交事务:
commit ;
回滚事务:
rollback ;
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)方式
(3)适用场景
表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据;
分区表的数据更容易维护,可以对独立的分区进行独立的操作;
分区表的数据可以分布在不同的机器上,从而高效使用资源;
可以使用分区表来避免某些特殊的瓶颈;
可以备份和恢复独立的分区。
5.主从复制
(1)原理
在主库上把数据更改记录到二进制日志;
从库将主库的日志复制到自己的中继日志;
从库读取中继日志中的事件,将其重放到从库数据中。
(2)解决的问题
6*.SQL查询的安全方案
使用预处理语句防止SQL注入;
写入数据库的数据要进行特殊字符的转义;
查询错误信息不要返回给用户,将错误记录到日志。