MySQL分区、主从复制，数据库优化

mysql8优化专题：

MySQL数据库单表最大可支持数据没有明确限制，业界流传500万，阿里手册提出单表行数超过500w或者单表容量数据超过2GB，才推荐进行分库分表。

1、慢查询开启

 -查看慢查询开启状态：show VARIABLES like 'slow_query%';
 -开启慢查询记录日志(重启失效)：set global slow_query_log='ON';
 -设置慢查询sql执行记录时间(S)：set global long_query_time=2;
 -永久生效配置：到mysql安装目录下打开my.ini(linux为my.cnf)配置文件添加：
 在my.ini配置文件添加如下配置应该在[mysqld]之下，否则会报错
 #开启慢查询
 slow_query_log=ON
 #最长执行时间 (查询的最长时间，超过了这个时间则记录到日志中)
 long_query_time=2
 添加完成后重启数据库：mysqld restart

2、数据库的优化策略

 1)优化sql和索引
 2)加缓存，memcache和redis等
 3)以上两点做了后还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用第三方工具，推荐360的atlas，其他的要么效率不高，要么没人维护
 4)如果以上做了之后还是慢，那么先试试mysql自带的分区表，无需更改代码，但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区
 5)如果以上都做了，那就先做垂直拆分，其实就是根据模块的耦合度，将一个大的服务分为若干小服务，从而根据小服务的业务拆分数据库字段
 6)最后才是水平拆分，针对数据量大的表，这一步最麻烦，也最能考验技术水平，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带shardingkey，将数据定位到限定的表上去查，而不是扫描全部的表

3、索引相关优化以及注意问题

 1)应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
 2)值分布很稀少的字段不适合建立索引，例如“性别”这种只有两三个值的字段
 3)字符只创建前缀索引
 4)字符字段最好不要做主键
 5)不用外键，由程序保证约束
 6)尽量不用UNIQUE，由程序保证约束
 7)使用多列索引时，注意索引内字段顺序和查询条件中的字段顺序，同时删除不必要的单列索引
 8)不要使用子查询，子查询时MYSQL会去创建临时表，查询完毕后再删除这些临时表，所以这里会多一个创建和销毁临时表的过程
 9)LIKE关键字匹配’%’开头的字符串,不会使用索引.
 10)OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
 11)使用多列索引必须满足最左匹配.

4、mysql文件类型

 -frm文件：表结构文件
 -ibd文件：innodDB中，索引和数据都在同一个文件.ibdata(你的执行结果可能是.MYD索引文件和.MYI数据文件，这是MyIsam存储引擎，对应着innodDB的ibd文件),因为order这张表分为5个区，所以有5个这样的文件
 -par文件：你执行的结果中可能有.par文件，也可能没有。从MySQL5.7.6开始，不再创建.par分区定义文件，分区定义存储在内部数据字典中。

5、mysql支持的分区方式

 -RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。使用较多
 -LIST分区：类似于按照RANGE分区，区别在于LIST分区是基于列值匹配的一个离散值集合中的某个值来进行选择。使用一般
 -HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的的这些行的列值进行计算，这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。使用频率较多。
 -KEY分区：类似于按照HASH分区，区别在于KEY分区只支持计算一列或者多列，且MySQL服务器提供其自身的hash函数，必须有一列或多列包含整数值。使用频率一般

6、MySQL分库分表策略

 分为proxy模式和client模式

7、MySQL分区

-创建表的时候进行分区
CREATE TABLE tb_tr (
	id INT COMMENT "ID编号", 
	name VARCHAR(50) COMMENT "名称", 
	purchased DATE COMMENT "购买时间",
	PRIMARY KEY (`id`, `purchased`) USING BTREE COMMENT "主键",
	INDEX `idx_id`(id) COMMENT "索引-id",
	INDEX `idx_name`(name) COMMENT "索引-名称",
	INDEX `idx_purchased`(purchased) COMMENT "索引-购买"
 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic 
COMMENT '购买日志'
PARTITION BY RANGE( YEAR(purchased) ) (
	PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
	PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
	PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
	PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
	PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
	PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB,
	PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);
-对已有表进行分区
ALTER TABLE tb_tr PARTITION BY RANGE (id)(
  PARTITION p5 VALUES less than (5),
  PARTITION p8 VALUES less than (8)
);
-查询已经创建的表分区
SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema = '数据库名' AND table_name = "表名";
-删除分区
ALTER TABLE tb_tr DROP PARTITION p4,p5,p6,p7;
-添加分区
ALTER TABLE tb_tr ADD PARTITION (
  PARTITION p7 VALUES LESS THAN (2010),
  PARTITION p6 VALUES LESS THAN MAXVALUE
);
-将一个分区重新规划为两个分区
ALTER TABLE tb_tr REORGANIZE PARTITION pmax INTO (
PARTITION p7 VALUES LESS THAN (2025) ENGINE = INNODB,
PARTITION n1 VALUES LESS THAN (MAXVALUE) ENGINE = INNODB
);
-MySQL的hash分区
CREATE TABLE clients (
	id INT,
	fname VARCHAR(30),
	lname VARCHAR(30),
	signed DATE
)
PARTITION BY HASH( MONTH(signed) )
--hash分区分为两种，一种是hash，另一种是线性hash，如PARTITION BY LINEAR HASH( MONTH(signed) )
PARTITIONS 12;
-将分区表从12个分区变为8个分区
ALTER TABLE clients COALESCE PARTITION 4;
-分区后的一些增删改查
DELETE FROM employees PARTITION (p0, p1) WHERE fname LIKE 'j%';
UPDATE employees PARTITION (p2) SET store_id = 2 WHERE fname = 'Jill';
SELECT * FROM employees PARTITION (p2);
INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);

8、MySQL主从复制原理

MySQL的主从复制中主要有三个线程：master(binlog dump thread)、slave(I/O thread,SQL thread),Master一条线程和slave中的两条线程。

  1) Master主库在事务提交时，会把数据变更记录在二进制文件Binlog中
  2) 从库读取主库的二进制文件Binlog，写入到从库的中继日志Relay Log
  3) slave重做中继日志中的事件，将改变反映它自己的数据

9、主从复制具体操作

1. 准备两台服务器，并且安装好MySQL，并确保他们对应的3306端口是开启的
2. 主库配置：
   修改配置文件/etc/my.cnf(windows环境应该是my.ini)

#mysql服务id，保证整个集群环境中唯一，取值范围：1-（2^32-1），默认为1
[mysqld]
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

重启MySQL服务器

systemctl restart mysqld

登录MySQL，创建远程连接的账号，并授予主从复制权限

 #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务，%是全部ip
 CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
 #为'itcast'@'%'用户分配主从复制权限
 GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

通过指令，查看二进制日志坐标

show master status;

3. 从库配置：
   修改配置文件/etc/my.cnf

#MySQL服务id，保证整个集群环境中唯一，和主库不一致即可
[mysqld]
server-id=2
#是否只读，1代表只读，0代表读写
read-only=1

重新启动MySQL服务

systemctl restart mysqld

登录MySQL，设置主库信息配置

#SOURCE_HOST：主库ip地址，对应MASTER_HOST
#SOURCE_USER：连接主库用户名，对应MASTER_USER
#SOURCE_PASSWORD：连接主库的密码，对应MASTER_PASSWORD
#SOURCE_LOG_FILE：binlog日志文件名，对应MASTER_LOG_FILE
#SOURCE_LOG_POS：binlog日志文件位置，对应MASTER_LOG_POS
#这个是8.0.23版本的语法
CHANGE REPLICATION SOURCE TO SOURCE_HOST='XXX.XXX',SOURCE_USER='XXX',SOURCE_PASSWORD='XXX',SOURCE_LOG_FILE='XXX',SOURCE_LOG_POS=XXX;
#8.0.23版本之前的语法如下
CHANGE MASTER TO MASTER_HOST='XXX',MASTER_USER='XXX',MASTER_PASSWORD='XXX',MASTER_LOG_FILE='XXX',MASTER_LOG_POS=XXX;

开启同步操作

start replica;#8.0.22之后
start slave;  #8.0.22之前

查看主从同步状态

show replica status;  #8.0.22之后
show slave status;    #8.0.22之前