史上最全的MySQL高可用架构之【主从复制】【故障转移】【读写分离】【负载均衡】

Mysql二进制日志

Mysql二进制日志**

• 记录了所有对mysql数据库的修改事件，包括增删改事件和对表结构的修改事件。

• 在binlog中记录的事件都是已经成功执行了的，回滚及错误日志不会记录，select和show这些不修改数据的记录不会记录。

binlog二进制日志记录的方式

基于段记录(SQL语句名)的格式(statement-based replication, SBR)

记录的是执行的语句

binlog_format=STATEMENT

在mysql5.4.1之前只存在这种复制模式，在mysql5.7前默认使用这种格式。

另外，无论在配置中使用哪种日志格式，在执行ddl操作时，在binlog**中都是用基于段的格式来记录日志的。

• 优点：

  • 日志记录量相对较小，节约磁盘及网络IO

  • 并不强制要求主从数据库的表定义完全相同

  • 相比于基于行的复制方式更为灵活，有利于定位问题发生

• 缺点：

  • 由于记录的是执行的语句，所以必须记录语句的上下文信息，确保在从服务器上正确执行，但是对于UUID(),user(),这样非确定性函数还是无法复制，可能造成mysql复制的注备服务器数据不一致而中断链路

  • 对于存储过程，触发器，自定义函数进行的修改也可能造成数据的不一致

  • 相比于基于行的复制方式在从上执行时需要更多的行锁

查看二进制日志格式

Show variables like ‘binlog_format’;

修改二进制日志格式

set session binlog_format=statement;

查看当前binlog编号和大小

show binary logs;

刷新binlog

flush logs

直接命令查看日志内容

mysqlbinlog mysql-bin.000001

基于行记录的格式(row-based replication, RBR)

binlog_format=ROW

使用基于行的row格式可以避免mysql复制中出现的主从不一致问题，mysql官方推荐这种格式，每一条数据的的修改都会有对应的日志。

• 优点：

  • 使用mysql主从复制更加安全，因为记录的是对行的更改，而不是执行整个sql语句，对于uuid(主上是什么，从上就是什么)或者user等不确定函数，存储过程，触发器等也可以避免数据不一致。

  • 对每一行数据的修改还比基于段的复制高效，主从复制延迟的主要原因是从服务器存放主服务器二进制日志的效率。

  • 其他优点，误操作而修改了数据库中的数据，同时又没有备份可以恢复时，我们就可以通过分析二进制日志，对日志中记录的数据库修改操作做反向处理的方式来达到数据恢复的目的。

  • 可以减少数据库锁的使用

• 缺点：

  • 记录日志量大，因为记录了每一行的数据操作，浪费磁盘IO

  • 要求主从数据库的表结构相同，否则可能会中断复制

  • 无法在从上单独执行触发器，因为它不是基于sql执行的

Binlog_row_image基于行记录日志时对sql列的记录方式

Mysql5.6后加了个参数： Binlog_row_image=[FULL| MINIMAL |NOBLOG]

FULL:默认，数据修改时记录所有列

更新id为2的name字段，并且前四次更新都是失败的，观察binlog是否会记录错误的日志，以及记录的字段个数。

观察到失败的更新并没有被记录，并且全部列id，name,sex都被记录了。

MINIMAL：只记录被修改的列，减小日志大小，减小磁盘io

更新id为1的name字段，观察日志记录的列

就记录了name一个字段，说明minimal有效，只记录被更新的字段

NOBLOB：跟FULL很像，如果没对blob或者text列的修改，则不记录blob或text列的修改

可以看到binlog日志跟FULL的情形很像，但是没有记录新增的text类型的remark字段。

常用命令

#查看行操作日志存放格式

show variables like ‘binlog_row_image’;

#查看binlog必须加-vv才能看得懂

mysqlbinlog –vv mysql-bin.000003

基于混合记录的格式(mixed-based replication, MBR)

binlog_format=MIXED

特点：

  根据sql语句由系统决定在基于段和基于行的日志格式中进行选择。一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

二进制日志格式的选择

  建议使用混合mixed或者row格式，如果是使用row格式建议使用binlog_row_image=minimal，减小磁盘io和日志大小

主从日志复制原理

• 主将变更写入二进制日志，注意查看是否开启二进制日志，运行中开启需要重启mysql服务

• 从I/O Thread读取主的二进制日志变更并写入到中继日志relay_log中

  如果该I/O thread追赶上了主服务器的日志，则进入sleep状态，直到主发送信号事件唤醒，中继日志relay_log格式跟binlog完全相同。

  复制方式由基于日志点的复制和基于GTID**的复制。

• SQL Thread在从上重放relay_log中的日志

  基于段是在从上重新执行sql，而基于行则是重新更新行数据

基于日志点复制配置步骤

• 在主DB服务器上建立复制账号

create user ‘repl’@’192.168.98.100’ identified by ‘123456’

• 授权给从服务器192.168.98.100’

grant replocation slave on ‘*’ to ‘repl’@’ 192.168.98.100’

配置主数据库服务器

• 启动mysql的二进制日志，并指定二进制日志名称，比如以mysql-bin开头的一系列文件，需要重启使得配置生效，注意写入二进制日志权限

  log_bin=mysql_bin

• 动态参数，可以set命令配置，只有在配置文件中修改，参数不会因为重启失效，这个server_id的值必须在整个复制集群中唯一

   server_id=100

配置从数据库服务器

log_bin =mysql_bin

server_id=101

• 中继日志，定义relay_log的位置和名称，这个参数的默认名字是我们主机名称，如果修改了主机名，重新启动从服务器的复制链路就会找不到原来的中继日志的错误，从而中断复制链路

  如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn

  relay_log=mysql-relay-bin

可选参数：

• 决定是否把sql线程存放的中继日志存放到从服务器本机的二进制文件中，后边如果想把从服务器当做其他从服务器的主服务器时，必须设置这个参数

  log_slave_update=on

• 可以保证从服务器只读

  read_only=on

从服务器的初始化

• 官方提供逻辑备份sql文件

• single-trabsaction 会对表加锁

• mysqldump导出数据时，当master-data这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。

  默认情况下master-data这个值是1 ，这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

msqldump –master-data=2 single-transaction

• 第二种方式

xtrabackup –slave-info

在从服务器启动复制链路

change master to master_host='192.168.98.99, 

master_user='repl', 

master_password=123456, 

master_port=3306, 

master_log_file=' mysql-bin.000001', 

 master_log_pos=120; 

操作步骤：

• 主服务器：192.168.98.99

• 从服务器：192.168.98.100

主服务器设置：

注意:这个值不能重复：server-uuid,克隆的虚拟机可能重复，要通过重命名自动生成新文件和编号。

从服务器设置：

change master to

  -> master_host='192.168.98.99',

  -> master_user='repl',

  -> master_password='123456',

  -> master_log_file='mysql-bin.000001',

  -> master_log_pos=120;

 

show slave status\G;

slave_IO_RUNNING=NO,查看日志：

tail /var/log/mysqld.log

设置从服务器的server-uuid后，重启从mysql。

验证同步：

发现同步的速度还是很快的，几乎是同一时刻的样子！

• 主创建表，从同步了

• 主加字段sex，从同步了

• 主加数据1，张三，男，从同步了

基于GTID点的复制

  从mysql5.6开始支持基于gtid的复制，跟基于日志点复制有很大差异。

什么是gtid

  GTID就是全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。

GTID=server_id:transaction_id

基于日志点的复制和基于GTID复制的区别：

• 基于日志点的复制的关键是：

  从主的哪个二进制日志的偏移量进行增量同步，如果指定错误会造成遗漏或重复。

• 基于GTID的复制：

  从服务器会告诉主服务器，已经在从服务器上已经执行完了哪些gtid值，然后主库会把从库未执行的事务gtid值发送给从库执行。同一个事务只在指定的从库上执行一次。

GTID复制步骤

1.在主DB服务器上建立复制账号**

注意一定不要手动在从服务器上建立相同账号。
在主DB服务器上建立复制账号

create user ‘repl’@’192.168.98.100’ identified by ‘123456’

授权给从服务器192.168.98.100’

grant replocation slave on ‘*’ to ‘repl’@’ 192.168.98.100’

查看下relp这个账号的授权信息

\#show grants for repl@'192.168.98.100';

2.配置主数据库服务器

bin_log=mysql-bin
server-id=100
gtid_mode=on

• 强制gtid’一致性，用于保证启动gtid后事务的安全

enforce-gtid-consiste=on

启动后以下命令或情况不能使用：

–create table …select

—在事务中使用create temporary table 建立临时表

—同时关联更新事务表和非事务表

• 在从上记录主传来的修改日志，在mysql5.7前，使用gtid模式必须用这个参数设置，基于5.7后就不需要了

log-slave-updates=on

• 主服务器/etc/my.cnf配置

3.配置从数据库服务器

注意主和从服务器都要配置成基于GTID的复制，否则会异常。

server_id=101

relay_log=relay_log

gtid_mode=on

enforce-gtid-consiste

Log-slave-updates=on

#建议

**read_only=on**【建议】

• 指定从服务器连接主服务器的信息存储方式，默认是文件中，可以通过这个配置记录在innodb表中

  master_info_repository=TABLE【建议】

• 指定中继日志信息的存储方式，同样可以记录在innodb类型的表中

  relay_log_info_repository=TABLE【建议】

  当出现数据库崩溃时，可以利用innodb事务引擎的特点对这两个表的信息进行崩溃恢复，以保证从服务器可以从正确的位置从新开始同步数据。

• 从服务器/etc/my.cnf配置

修改配置后，重启主从mysql服务！

service mysqld stop

service mysqld start

4.初始化从服务器数据

mysqldump –master-data=2 –single-transaction


Xtarbackup –salve-info

注意:此时备份的不是二进制文件名和偏移量了，记录备份的是最后的事务的GTID值

• 在主服务器上生成主服务器的数据脚本信息，用于在从服务器上执行保持同步

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p > all.sql

more all.sql

• 拷贝脚本all.sql到从服务器上

scp –p22 all.sql root@192.168.98.100:/root

• 在主服务器上执行all.sql恢复数据

  现在就完成了主从数据库的一致性操作，在从上恢复了主上的数据！

启动基于GTID的复制

STOP SLAVE;

change master to

  -> master_host='192.168.98.99',

  -> master_user='repl',

  -> master_password='123456',

-> master_auto_position=1;

START  SLAVE;

show slave status\G;

验证GTID复制链路是否正常运行：

• 首先在主库的T_user表插入一条数据

• 接着在从库观察相应表数据：

可以看到复制已经成功执行！

观察从mysql服务器状态，看到事务已经复制成功！

• 优点：

  • 相对于关于日志点的复制，GTID可以很方便进行故障转移，是因为GTID有全局唯一的事务ID，所以根据gtid就知道有哪些事务是没在从库执行的，当进行故障转移时，对多个从服务器无需根据主的日志偏移量进行同步了，由于我们启动了log_slave_updates=on,所以原来主库中传递过来的事务也会记录到新的主中。所以根据GTID，现在从库就可以知道从什么事务开始从新的主库开始同步数据。所以GTID复制增加了mysql复制安全性，在进行故障转移时尽量减少数据的丢失。

  • 基于GTID的复制，从库不会丢失主库上的任何修改，前提是在主库的二进制日志没有被删除的情况下。

• 缺点：

  • 故障处理比较复杂

  • 对执行的sql有一定的限制

选择复制模式要考虑的问题：

1 所用的mysql的版本，gtid是mysql5.6支持的版本

2 复制架构及主从切换的方式（推荐gtid）

3 所使用的高可用的管理组件

Mysql复制性能优化

  只有Mysql事务在主库执行完并记录到二进制日志中之后，从库才能从主库二进制日志中读取已经执行完的事务，并把这些事务保存到从库的中继日志中，然后从库的sql线程才能从中继日志中读取事件重放。

影响主从复制的延迟因素：

• 主库写入二进制日志的时间，从也差不多相应时间执行事务

  解决方法：控制事务的事务大小，分割大事务

• 二进制日志从主库传输到从库，并写入中继日志的时间

  解决方案：使用mixed日志格式 或 row格式设置set binlog_row_image=minimal;

• 默认情况下从库只有一个sql线程，在主上并发的修改在从上变成了串行

  解决方案：使用多线程复制（mysql5.6引入新功能，不过每个sql线程只能处理从库中的一个db库的sql重放，所以对于那种写操作只在一个db数据库中的情况，此功能就是鸡肋，甚至启动了多线程复制的性能会更差，好在mysql5.7改善了这个问题，在mysql5.7可以按照逻辑时钟的方式来分配sql线程，使得多线程复制变得更实用了。

多线程复制配置：

只需要四个步骤：

stop slave;

set global slave_parallel_type=’logical_block’;

set global salve_parallerl_workers=4;

start slave;

主从复制常见问题处理

  由于数据损坏或丢失所引起的主从复制错误

1.主库或者从库意外宕机引起的错误

  主库意外宕机，当主sync_bin_log没有设置为1时，当主意外宕机，有可能没有将最后的几个二进制日志事件由缓冲区刷新到磁盘进行永久存储。当主库重新启动后，从库连接到主库并再次去尝试读取相关的二进制事件，但是主库会告诉从库，在主库的二进制日志中并没有二进制偏移量代表的事件，就是因为主库在宕机时没及时把事件保存到二进制日志中。这时从库读取不到主库二进制日志的错误，造成主从链路中断。

解决办法：

• 使用跳过二进制日志事件

• 注入空事务的方式先恢复中断的复制链路

• 再使用其他办法来对比主服务器上的数据

  从库的意外宕机也会引起主从复制链路中断，由于从库的意外宕机可能会引起master_info文件没有及时同步到磁盘上，在master_info这个文件中记录了从库已经同步了的主库的二进制日志相关信息，这可能从库从主库重复获取了部分的二进制日志，这在基于日志点的复制时可能会造成主键冲突的问题，当然了基于sql段模式的复制中，可能对重复的一些数据更新，解决办法：

  使用跳过中继日志的方式来恢复主从链路，但是恢复后我们同样需要验证主从数据是否一致。

sync_bin_log解释

  参数sync_binlog=[N]表示每写缓冲多次就同步到磁盘。

  当sync_binlog=1，当使用InnoDB存储引擎时，在一个事务发出commit动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在Mysql数据库下次启动时，由于commit操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题，可以将innodb_support_xa设为1来解决，确保二进制日志和InnoDB存储引擎数据文件的同步。在滚回INnnoDB事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。

  从官方解释来看，innodb_support_xa的作用是分两类：

  第一，支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。

  第二，支持内部xa事务，说白了也就是说支持binlog与innodb redo log之间数据一致性。

2 主库的二进制日志损坏

  主库每次重启后都会重新生成一个二进制日志文件，老的二进制文件可能会由于主库的意外关闭而被破坏，只能在从库通过change master命令重新指定从库从主库同步二进制日志来同步，但是这样会丢失主库的一些更新，使得主库和从库的数据出现差异，所以接下来还是得修复丢失的数据，修复后还要对主从数据库的数据进行检验，看是否恢复了主从数据库的一致性，和主库的意外重启可能损坏主库的二进制日志一样，从库的意外重启也可能会引起从库保存的中继日志的损坏，不过只要主库的二进制日志没有删除，从库中继日志的损坏就很好处理了。我们通过change master命令指定从库的IO线程重新从损坏的位置再次同步主库的二进制日志，这样就可以安全恢复同步链路了。

  除了以上意外宕机引起的主从复制错误，还包括下边这些：

3 在从库上进行数据修改造成的主从复制错误（read_only**）**

  实际工作中很少有人记得在从库上设置这个参数，另外，即使设置了这个参数，在mysql5.7前拥有super权限的用户还是可以在从库修改数据，而主从复制的正常运行非常依赖于主从数据的一致性。如果出现了从备库上修改数据的情况，复制链路会轻易被中断。

  如果是业务应用在从服务器上进行了数据修改，很可能就会出现事务丢失的问题，所以我们必须认为决定是

  “保留主库上的数据还是保留从库上的数据？？？”

  在大多数情况下，一旦出现这样情况，我们只能从主库同步有差异的数据，这样从库的修改就会丢失

4 不唯一的server_id****或server_uuid

  比如多个从服务器使用相同的sever_uuid

5 max_allow_packet****设置引起的主从复制错误

  从服务器上最大允许的包的参数设置的不一致也会可能造成主从复制失败，主库可能会记录从库的一个过大的包，当从库获得这个二进制日志事件时，可能会碰到很多问题，如无限制报错，或重试，或者中继日志的损坏等。

Mysql复制无法解决的问题

• 无法分担主数据库的写负载

  在主从复制中，在主库上的二进制事件最终会在从服务器重放，所以写负载不会减少，如果想减少写负载，只能通过分库分表的方式处理！

• 单纯的主从复制无法进行自动故障转移和主从切换

• 主从复制不提供读写分离功能

Mysql高可用

什么是高可用

  高可用性H.A.(High Availability) 指的是通过尽量缩短因日常维护(计划性)和突然的系统崩溃(非计划)所导致的停机时间，以提高系统和应用的可用性。

  绝对的100%的高可用HA是不可能达到的，因为有很多原因都将会造成系统实际上的不可用，比如严重的主从延迟、主从复制中断、锁引起的大量阻塞，在这些情况下虽然服务器没有崩溃，但是对应用来说已经不能正常的使用数据库了，更不要说因为软硬件故障造成的服务器宕机这种情况了。

  通常使用服务的正常可用事件和全年时间的百分比来表示服务器高可用的程度。比如99999，

  表示99.999%的高可用性，也就是每年5.256的不可用时间。

（3652460）*(1-0.99999)=5.256

如何实现高可用

避免导致系统不可用的因素，减少系统不可用的时间。

导致服务器不可用的因素：

• 服务器的磁盘空间耗尽

备份或者各种查询日志突增导致的磁盘空间被占满。Mysql由于无法记录二进制日志，无法处理新的请求而产生的系统不可用的故障。尤其在虚拟机中部署的mysql更有可能产生这种故障

• 性能糟糕的sql

• 表结构和索引没有优化

• 主从数据不一致

• 人为的操作失误等等

解决办法：

• 建立完善的监控及报警系统

• 对备份数据进行恢复测试

虽然数据会备份，但是常忘记测试数据的可用性，防止因为备份文件损坏不可用。

• 正确配置数据库环境

  比如配置从服务器为只读的

• 对不需要的数据进行归档和清理

如何避免Mysql的单点故障

单点故障：指的是在一个系统中提供相同功能的组件只有一个，如果这个组件失效了，就会影响整个系统的功能的正常使用。组成应用系统的各个组件都可能成为单点。

解决办法：

1 SUN共享存储

2 DRDB磁盘镜像

3 pxc多写集群或NDB集群

4 mysql****主从复制

最重要的是解决主服务的单点问题：

• 主服务器切换后，如何通知应用新的主服务器的ip地址

• 如何检查mysql主服务器是否可用

• 如何处理从服务器和新的主服务器之间的复制关系

MMM架构

什么是MMM架构

  Multi_Master Replication Manager,就是mysql**多主复制管理器的简称，它是由一套perl语言开发的用于管理mysql**主主同步架构的工具集，主要作用是监控和管理mysql主主复制拓扑，并在当前的主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作。

MMM提供了什么功能

MMM监控Mysql主从复制的健康状况,主主复制的两种工作模式：

• 主动主动模式的主主复制，两个主同时对外提供服务

• 主动被动模式的主主复制，同一时间只有一个主数据库对外提供服务，另一个处于备用状态，MMM就是工作在这种主主模式拓扑中的架构。MMM中，一台主数据库对外提供服务，另一个主数据库只能对外提供查询服务，并且设置为readonly模式。MMM可以监控复制模式下的主从复制链路是否正常，主从是否存在延迟。当出现主从延迟或者主从复制链路中断时，会对主数据库进行故障转移，并且将相应的虚拟ip迁移到另一个新的主上，这样对前端应用来说不会受到主服务器宕机的影响。

  其次，MMM可以在活动的主库出现宕机或者是mysql服务出现故障时，在活动的主库和备用的主库之间进行故障转移和切换，并自动对MMM集群中存在的其他的从数据库对新的主数据库重新进行主从同步配置。这一步涉及很多内容，包括：

  • 如何找到从库对应的新的主库日志的日志同步点。

  • 如果存在多个从库出现数据不一致的情况如何处理

  MMM对于数据不一致的处理不安全，只是简单粗暴的找到主库的当前日志的日志点，然后使得所有从库对这个日志点进行同步，在一个繁忙的系统中使用MMM很有可能会对数据造成丢失的情况。

  最后，MMM对于集群中的每一个服务器都会提供一个虚拟IP，其中包括一个写的虚拟IP，和多个读的虚拟IP，写虚拟ip只能在两个主数据库服务器之间进行切换，而读虚拟ip则可以在集群中的所有的主从服务器上进行切换。

MMM架构拓扑图

  MMM架构是基于主主复制的架构建立的，MMM只能工作在主主复制的主被模式中，所以夏下图的备用主机用蓝色表示，在使用MMM时除了活动的主外，其他的数据库都要处于readonly状态。

  MMM复制管理工具在mysql主主复制的基础上增加了一个用于监控各个服务器状态的监控服务器，我们在下图用绿色表示。

  在进行mysql的安装时，我们要在MMM的集群中给每一台服务器上都安装一个监控代理软件，MMM监控服务器就是通过和每个mysql服务器上的监控代理软件交互来完成MMM集群中各个服务器状态的监控和角色迁移的。为了能部署MMM集群，我们处理部署mysql主主复制之外，还需要一些额外的资源。

MMM部署所需额外资源

MMM部署步骤

配置主主复制及主从同步集群

初始化数据

  复制主库192.168.98.99原始数据到备用主库192.168.98.100和从库192.168.98.101上。

互为主备

配置192.168.98.99和192.168.98.100互为主从关系

注意访问权限限制：

• 保证每个机器的

update mysql.user set host='192.168.98.%' where user='repl';

• 还要把192.168.98.100上的/etc/my.cnf的readonly关闭。

• 把192.168.98.99当成主，在备用主库192.168.98.100上执行

Stop slave;

change master to

  -> master_host='192.168.98.99',

  -> master_user='repl',

  -> master_password='123456',

-> master_auto_position=1;

START SLAVE;

• 把192.168.98.100当成主，在备用主库192.168.98.99上执行

Stop slave;

change master to

  -> master_host='192.168.98.100,

  -> master_user='repl',

  -> master_password='123456',

-> master_auto_position=1;

START SLAVE;

• 查看主从状态

分别在99和100上执行

show slave status\G;

可以发现99和100互为主从：

互为主从

• 设置101为99的从数据库

由于我的101是从99这台克隆的，所以我要改下server_id和my.cnf

server_id改为3.

99****的server_uuid

101****的server_uuid

这个值必须保证集群中全局唯一，所以我们修改下101的这个值。

在192.168.98.101上执行，使得101成为99的从，设置my.cnf为只读的。

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> change master to master_host='192.168.98.99',master_user='repl',master_password='123456',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

 

mysql>

• 重启101mysql,

service mysqld stop;

service mysqld start;

• 查看主从状态

show slave status\G;

可以看到99成了101的主数据库。

安装主从节点所需要的支持包

安装yum源

yum install wget

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

 

wget http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

rpm -ivh epel-release-latest-7.noarch.rpm

rpm -ivh remi-release-7.rpm

• 把enabled由0改为1

vim /etc/yum.repos.d/remi.repo

• 注释mirrorlist，放开baseurl

注意：做了这一步就会出现下边perl-module-compat-5-1-10的错误，

参考解决：

https://serverfault.com/questions/401504/munin-on-centos-6-missing-perl-module-compat-5-8-8

还是使用以下配置：

vim /etc/yum.repos.d/epel.repo

• 查看可以安装的mmm包

yum search mmm

• 在每一个服务器99+100+101上安装mmm的代理软件（看到安装了很多per依赖）

yum install mysql-mmm-agent.noarch -y 

• 在101这个从服务器，同时也是监控服务器安装监控服务依赖包

yum -y install mysql-mm* 

go into your /etc/yum.repos.d/epel.repo file and change:

    mirrorlist=http://mirrors.fedoraproject.org/mirrorlist?repo=epel-6&arch=$basearch

to

    mirrorlist=http://mirrors.fedoraproject.org/mirrorlist?repo=epel-7&arch=$basearch

 

yum clean all
 yum install munin –nogpgcheck

OK了！

• 在99建立mmm【监控账号】

grant replication client on *.* to 'mmm_monitor'@'192.168.98.%' identified by '123456';

• 在99建立一个【代理用户】为mmm代理服务，改变readonly模式或者说改变从服务器为主，主要作用进行故障转移和主从切换，需要权限比较大。

grant super,replication client,process on *.* to 'mmm_agent'@'192.168.98.%' identified by '123456';

• 99建立复制用户，这个用户在建立主从复制集群的时候已经建立好了就是repl

  至此，我们已经准备好了所有mmm集群需要的资源，接下来就可以对mmm集群进行具体的配置了。

安装及配置MMM工具集

配置mmm_common.conf,这个文件在集群的三个服务器上是一致的。

注意：三个服务器要有相同的网络接口地址比如ens33

拷贝这个文件到三个集群的节点中：

scp mmm_common.conf  root@192.168.98.100:/etc/mysql-mmm/  

scp mmm_common.conf root@192.168.98.101:/etc/mysql-mmm/

确认100和101的配置文件是否拷贝过来。

除了mmm_common.conf，除了这个通用文件之外，每一台的代理服务器上我们还需要编辑mmm_agent.conf文件，其中只有一行。

现在我们已经完成了所有数据库节点的配置，接下来我们配置监控节点在101上，监控节点的/etc/mysql-mmm下的文件多了监控节点的文件：

这里我们编辑mmm_mon.conf

这里我们完成了mmm的配置过程，接下来启动！

运行MMM监控服务

在启动101的监控服务之前，需要启动集群中所有(99+100+101)的代理

systemctl start mysql-mmm-agent.service

启动完代理，我们就可以在监控节点上启动监控服务

systemctl start mysql-mmm-monitor.service

在监控节点上我们使用mmm_control show来查看监控集群的状态

可以使用ip addr来查看主机IP和虚拟ip

测试

做故障转移和主从切换测试，方法是关闭集群99节点，/etc/init.d/mysqld stop

再次查看监控节点监控状态：

mmm_control show

我们来确定下db3-101的主从复制是不是根据db2-100来做的，

在db3上执行

show slave status\G;

MMM工具的优点：

• 使用Perl脚本语言开发及完全开源

• 使用虚拟ip方法，使得服务器角色的变更对前端应用透明

• mmm提供了从服务器的延迟监控

• MMM提供了主数据库故障转移后从服务器对新主的重新同步功能，

• 很容易对发生故障的主数据（已变成从）库恢复后重新上线，重新启动后它的从角色不会变

MMM工具的缺点：

• 发布时间比较早不支持mysql新的复制功能，有一些小bug，也不支持基于GTID的复制方式，只能使用基于日志点的复制方式。

• 对mysql5.6后提供多线程复制技术不支持。

• Mmm工具虽然在每个从服务器上提供了读VIP，但是并不能对多个读VIP做负载均衡操作。做负载需要借助其他LVS等工具，负载管理复杂。

• 在进行主从切换时，容易造成数据丢失。

• MMM监控服务存在单点故障，需要开发MMM监控服务的监控程序

MHA架构

  Mha(master high Avaliability )，是由perl脚本开发的，用于管理mysql主从复制或者实现mysql高可用的一套相对比较成熟的工具套装。从名称可以看出，MHA主要关注的是mysql集群的主DB，其主要功能是在mysql中主从复制架构下完成故障切换和在众多的从服务器中自动选举出新的从服务器，并将其他的从服务器和新选出的主数据库进行同步切换，在mysql的切换过程中，MHA可以做到完成高效的主从切换。基本可以保证在30s内完成所有的切换操作。并且在切换的过程中可以最大程度的保证数据的一致性。以避免丢失的事务，达到真正意义上的高可用。

MHA提供的功能

• 监控主数据库服务器是否可用

  对于主从复制的高可来说，其实主要也就是解决主数据库的单点问题。

• 当主DB不可用时，MHA可以从多个从服务器中选举出新的主DB服务器

  在MHA自动故障切换的过程中，MHA会尝试从宕机的主DB上保存二进制日志并最大程度保证事务的不丢失，但这并不是总是可行的，例如当主DB的硬件或者网络发生故障，无法进行ssh访问的话，那么MHA就无法保存二进制日志，只能进行故障转移，这时有可能会丢失最新的一部分数据。

  通过使用mysql5.5之后的半同步复制功能，可以大大降低数据丢失的风险，MHA可以与半同步复制结合起来，如果只有一个从服务器已经收到了最新的二进制日志，那么MHA就可以将最新的二进制日志用于其他的所有从DB上。因此可以保证所有节点的数据的一致性。

• 提供了主从切换和故障转移功能

  可以自动地将从服务器对新的主DB进行复制，但是MHA进行主从复制和故障切换的过程和MMM存在很大差异。

MHA如何进行主从切换

  使用mha工具时，对主DB进行监控时，如果发现主DB不可访问，会自从进行故障转移和主从切换操作，大致的切换过程主要由以下几个步骤来完成：

• 首先MHA在运行的过程中会自动的识别并对集群中的主DB进行监控，当发现主DB出现故障时会尝试从出现故障的主DB中使用ssh的方式保存最后的二进制日志，这一点是和MMM最大的不同，MMM不会去尝试保存最新的二进制日志，所以比MHA丢失数据的风险更大，但是这一步不是一定成功的，当主DB硬件本身不能访问时就不能保存最新二进制日志

• 第二步，从多个可用从服务器中识别出含有最新更新的那个从服务器，并把这个从服务器作为备选的主服务器来使用，也就是说在多个从服务器中，会把和原来的主DB最为接近的那个从服务器作为新的主DB来使用，当然，如何选举出新的主DB还跟我们的配置有一定关系。我们可以人为设置一些服务器不参与选举，这一点也跟MMM由很大不同，在MMM中只会使用原来主DB的主备DB作为新的主DB使用， 但是由于mysql的主从复制工作方式并不能保证主备服务器的数据就是最新的。这也是MHA优于MMM的特性。

• 下一步，在备选主DB和其他从DB之间同步差异二进制数据

  这个备选主DB就是之前选举的最新的从，这里选举为备选主后跟其他从DB同步差异数据，保证了各个从服务器的数据是一致的。

• 下一步，备选主从原主DB上保存的二进制日志

  这一步的前提是能够从原主上成功保存二进制日志。如果这一步中出现重复的主键等错误会使得MHA停止进行故障转移。

• 下一步，提升备选主DB为新的主DB服务器，同时进行虚拟IP切换

• 迁移集群中的其他从DB作为新主DB的从服务器

这样就完成了整个故障转移和主从复制过程！

MHA演示架构

这个MHA拓扑图是基于GTID的复制，注意MMM不支持GTID复制。

MHA配置步骤

  MHA的很多操作都是需要使用ssh命令来实现的，比如故障转移过程中的保存原主服务器二进制日志，配置虚拟IP地址等。所以我们要配置MHA管理工具，就需要先配置集群中所有主机的SSH免认证登陆，否则就无法实现自动故障转移，MHA监控服务也无法启动。

• 安装MHA-node软件包

需要在集群中的所有服务器上进行安装。

• 安装MHA-manager软件包

这个只需要在监控服务器上安装即可

• 安装安装MHA所需perl支持依赖

yum -y install perl-Config-Tiny.noarch per-Time-HiRes.x85_64 perl-Parallerl-ForkManager perl-Log-Dispatch-Perl.noarch perl-DBD-MySQL ncftp

• 建立主从复制集群

MHA可以同时支持日志点复制和基于GTID复制，推荐GTID复制

• 配置MHA管理节点

• 配置验证

masterha_check_ssh :检测ssh免认证配置是否正确

masterha_check_repl:检测集群中复制链路是否正确

只有在以上两个检查没有问题时，就可以启动并测试MHA服务

配置演示：

查看三台服务器的gtid模式是否开启：

必须三台都开启gtid模式，否则无法启动！

接下来我们配置基于GTID的复制架构！

我们先建立复制用户repl，之前我们建立过。

查看repl的权限：

实际环境中如果新建的从DB跟主DB有差异的话，则需要初始化从DB。

然后，在100从服务器上通过change master命令启动复制链路：

同样在101上执行change master命令，启动复制链路：

现在基础的主从复制环境搭建完成了，下来进行MHA的配置：

首先，要在集群中建立ssh的免认证登陆：

在每台机器上都执行以下命令：

ssh-keygen -t rsa --一路回车

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.98.99

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.98.100

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.98.101

验证：

三台机器相互的ssh免认证已经配置完成，接下来在三台机器上安装MHA的node软件包：

在三台机器上安装perl支持：

yum install per-DBD-MySQL ncftp per-DBI.x86

scp mha4mysql-node-0.56-0.el6.noarch.rpm root@192.168.98.99:/root

scp mha4mysql-node-0.56-0.el6.noarch.rpm root@192.168.98.101:/root

然后三台机器上上安装node软件包：

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

在从服务器101上安装manager软件包：

rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

MHA配置

MHA的配置跟MMM配置不一样，MHA的配置只需要在管理节点配置就可以，为了保存MHA的配置文件，我们新建一个目录：/etc/mha

mkdir –p /etc/mha

另外由于MHA在实际工作中可能需要从失败的主服务器下载一些二进制日志文件，我们为MHA建立一个工作目录/home/mysql_mha

mkdir –p /home/mysql_mha

新建mha的配置文件

vim mysql_mha.conf

首先定义一个user选项值为mha，密码是123456，接着我们在99主服务器上创建这个用户，就可以自动复制到100和101两个数据库中了

切换到99服务器：

 grant all privileges on *.* to mha@'192.168.98.%' identified by '123456';

下一步配置的是管理用户的工作目录：

接着配置管理需要的log目录：

接着配置远程服务器的工作目录：

在节点99和100建立/home/mysql_mha

mkdir -p /home/mysql_mha

接着配置ssh用户，我们这里使用root，不需要密码，因为免登录了

接着是复制用户及密码

接着配置manager线程去检查主DB是否连通的ping操作时间间隔

接着配置master_binlog_dir,可以在主节点查看：

拷贝/var/lib/mysql/到配置文件：

注意：如果要使用mha工具，还有点需要注意，集群中所有参与主服务器选举的DB，最好把它们的binlog配置到相同的位置，这样主从切换后就不用修改配置文件了。

配置一个可选参数master_ip_failover_script：指定一个脚本，主要作用是在完成主从切换后，把主的VIP绑定到新选举的主服务器上，如果不提供这个脚本，那么MHA无法提供VIp的漂移，只能借助第三方如keepalived来实现VIp漂移。

master_ip_failover_script=/user/bin/master_ip_failover

[server default]
#这个user是我们需要在数据库中建立的,用于mha主从管理的数据库用户
user=mha
password=123456
manager_workdir=/home/mysql_mha
manager_log=/home/mysql_mha/manager.log
remote_workdir=/home/mysql_mha
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/user/bin/master_ip_failover
#通过多个网络路径检测master节点是否可用，如果网络出现短时抖动，这种方式可以避免错误的
#切换，建议在配置文件中加上这个脚本，安装完mha后，这个脚本已经存在/usr/bin下，使用简单，只需要指定一些ip
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.98.99 -s 192.168.98.100 -s 192.168.98.101
#告知MHA集群主机信息
[server1]
hostname=192.168.98.99
candidate_master=1
[server2]
hostname=192.168.98.100
candidate_master=1
[server3]
#101既是从又是我们的监控服务器，所以禁止它参与master选举，不让其作为主使用
hostname=192.168.98.101
no_master=1

• master_ip_failover脚本

/usr/bin/master_ip_failover脚本,注意配置以下两个标黄框中的内容，配置VIP和网卡

• 检测mha的配置是否正确：

masterha_check_ssh –conf=/etc/mha/mysql_mha.conf

测试通过会显示：

All SSH connection tests passed successfully

• 检测基础复制环境：

master_check_repl –conf=/etc/mha/mysql_mha.conf

如果OK的话，会显示：

MySQL Replication Health is OK.

• 启动MHA

nohup masterha_manager --conf=/etc/mha/mysql_mha.conf

由于MHA不会配置主服务器的虚拟IP，所以需要我们手动在当前主服务器上配置VIP，在之后迁移过程中，会把VIP迁移到新的主DB服务器上。

ifconfig ens33:1 192.168.398.50/24

• 测试MHA

下面测试MHA的故障转移和切换VIP到新的主服务器上去

停止99mysql实例，查看99的ip addr的VIP还存在不，然后看100和101上是否存在！

可以看到99的VIP迁移到了100上。

再查看101从服务器的主现在是谁，可以看到是100了，100成为了新的主DB！

MHA的优点和不足

优点：

• 同样是由perl语言开发的开源工具

• 可以支持基于GTID的复制模式

• MHA在进行故障转移时更不易丢失数据

• 同一个监控节点可以监控多个主从集群

不足：

• 需要编写脚本或利用第三方工具来实现VIP的配置

• MHA启动后只会对主数据库进行监控

• 需要基于ssh免认证配置，存在安全隐患

• 没有提供从服务器的负载均衡功能

读写分离

写负载无法分担，读负载可以分担

基于程序实现的读写分离

优点：

• 由开发人员控制什么样查询在从库中执行，什么样查询在主库，因此比较灵活。

• 由于程序直接连接数据库，跟单台查询的性能损耗是一样的，所以性能损耗比较少。

缺点：

• 增加了开发工作量，使得程序代码更加复杂。

• 人为控制，容易出现错误

基于中间件实现读写分离

Mysql-proxy

  Mysql提供的中间件，性能和稳定性有一定问题，但使用这个中间件不但能解决读写分离的问题，而且可以对多个从实现负载均衡，从功能上很强大，但是性能和稳定性不可靠，不建议使用。

maxScale

  maxScale是MariaDB公司提供的中间件，是由mysql创始人创建的mysql的一个分支版本，所以是mysql的亲兄弟，所以maxScale不但可以在MariaDB上使用，而且可以在mysql上使用。最主要是这个工具免费，并且性能和稳定较好，而且跟mysql proxy一样不但可以提供读写分离的功能，而且实现多个从的读负载均衡功能。

使用中间件读写分离的优缺点

  目前许多数据库中间件都可以完成数据分离的读写功能，根据我们使用的软件的不同，其性能和功能特点也会有不一样的地方。

优点：

  由中间件根据查询语法分析，自动完成读写分离，例如select会发送到从DB，非select则全部送到主DB处理，但是存储过程无法分辨，只能由主DB完成。由于存储过程很大一部分都是查询处理，无疑会加大主DB的负载，所以虽然中间件使用很方便，但是也会付出一定代价。

  使用它主要是因为对程序透明，对已有程序代码不用做任何修改，大大节约实现读写分离的时间和难度。

缺点：

Ø 不在大并发，高负载的情况，中间件的缺点是几乎不会暴漏的，首先增加中间层后，程序会通过中间层来连接到数据库服务器，所以查询的效率十分依赖于中间层的处理能力。特别是中间层还有处理一些权限认证，sql语法分析这样的工作。

严重影响查询性能！以qps为例几乎可以降低50%以上的处理能力！

Ø 由于读写分离是通过中间层实现的，而中间层是无法区分哪些查询是对主从延迟敏感的，哪些是不敏感的，所以无法把延迟敏感的查询自动放到主库上执行查询，而实现这个功能就要在查询中实现一些查询提示关键字，如果这么做就不得不对我们的程序进行修改，这样就失去了上边提到的对程序透明的优点。

读写分离和负载均衡

Ø 读写分离主要解决的是如何在复制集群的不同角色上，去执行不同的sql语句的问题

Ø 读的负载均衡主要解决的是具有相同角色的数据库如何共同分担相同的负载的问题。例如同样是读请求，如何分担到多台从数据库上去执行查询。

ü 一种方式是程序轮询的方式，这种方式在我们每次增加数据库的数量后，需要修改程序的配置以适应数据库数量的变化。不是很灵活。

ü 另一种办法是利用一些软件来解决，比如软件LVS，Haproxy，MaxScale，硬件F5.

不管使用哪种方式，基准测试很重要。

MaxScale演示

MaxScale的插件

Authentication认证插件

  提供数据库用户登陆，认证功能，为了能验证用户连接，maxscale会从后端数据库读取mysql.User表中的信息，并将此表信息缓存到maxScale的服务器端。当用户连接进来后会根据缓存的信息来判断是否通过验证。如果当前缓存无此用户，则会从后端服务器更新用户信息之后再进行用户验证。

Protocal协议插件

  负责maxScale和外部系统之间的接口协议，主要包括客户端和maxScale的接口，MaxScale到后端数据库的接口这两方面，所以目前主要提供2个协议插件：

  一个是mysql客户端协议插件，主要用于客户端应用程序通过maxScale连接到mysql，因此客户端完全可以把MaxScale作为一台mysql数据库来使用。对于原来连接mysql的程序，除了要修改连接地址之外不用做任何的更改。

另一个是mysql服务器端协议插件，主要用于maxScale连接后端数据库来使用。

Router路由插件

  其决定了把前端用户的请求如何发送给后端的数据库，我们所要实现的读负载均衡和读写分离，主要就是靠这个模块来实现的。目前我们主要使用到两种路由模块：

Readconnroute：用来实现多台服务器的负载均衡的

Readwritesplit：用来实现读写分离的

Monitor监控插件

  用于maxscale对后端数据库进行实时监控，以便于maxScale可以将前端请求发送到正确的后端数据库中，正确的数据库指的是可以正常对外提供服务的数据库，我们也可以通过这个模块实现对主从延迟的监控

Filter&Logging日志和过滤插件

  提供了简单的数据库防火墙的功能，可以对某些sql进行过滤和改写，可以进行简单的sql容错和语句转换

MaxScale使用介绍

ü 安装

1：下载安装包

https://downloads.mariadb.com/enterprise/fre6-c9jr/mariadb-maxscale/1.3.0/rhel/6x86_64/maxscale-1.3.0-1.rhel6.x86_64.rpm

这个安装包的下载需要有一个mariadb的注册账号，注册即可

2：下载maxscale需要的软件依赖库

yum install libaio.x86_64 libaio-devel.x86_64 nocacom-server.x86_64 –y

3: rpm –ivh maxscale-1.3.0-1.rhel6.x86_64.rpm

配置

上图为我们的基础环境

在复制集群的主数据库99上建立一个监控模块使用的账号：

create user scalemon@’192.168.98.%’ identified by ‘123456’;

grant replication slave,replication client on *.* to scalemon[scalemon@’192.168.98.%’

在复制集群的主数据库99上建立一个路由模块使用的账号，读写mysql的账号信息：

create user maxscale@’192.168.98.%’ identified by ‘123456’;

grant  select on mysql.* to maxscale@’192.168.98.%’;

安装完maxscale之后会在/etc目录下生成一些默认的maxcale的配置文件

ls –l maxscale*

配置文件修改

vim maxscale.cnf

#指定了maxscale线程的数量，默认1，根据实际调整，不要太多

threads=1

maxscale集群中每一个服务器都要有一个server段来标识

[server1]

type=server

address=192.168.98.99

port=3306

protocol=MySQLBackend

 

[server2]

type=server

address=192.168.98.100

port=3306

protocol=MySQLBackend

 

[server3]

type=server

address=192.168.98.101

port=3306

protocol=MySQLBackend

还有监控模块的配置

[MySQL monitor]

type=monitor

module=mysqlmon

servers=server1,server2,server3

user=scalemon

passwd=123456

#监控的时间间隔，单位毫秒，改为1s一次

monitor_interva;=1000

 

#这个模块可以不用配置，在读写分离那里配置,这里删除掉

[Read-Only Service]

type=service

router=readconnroute

servers=server1

user=myuser

passwd=mypass

#是否只在slave进行读负载

router_options=slave

 

 

[Read-Write Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=maxscale

passwd=123456

#最大可以使用从服务器百分比

max_salve_connections=100%

#当延迟大于多少秒之后，从服务器不参与读写分离工作中来，这样可以把延迟大的从排除出集群

max_salve_replication_lag=60

 

#由于我们没有使用只读服务，这里只读监听也删除

[Read-Only Service]

 

 

#读写分离监听

[Read-Write Listener]

type=listener

service=Read-write Service

protocol;=MySQLlient

port=4006

ü 启动maxscale服务

#maxscale –conf=/etc/maxcale.cnf

ü 登陆管理界面

maxadmin –user=admin –password=mariadb

lister servers

查看缓存的用户

show dbuser “Read-Write Servie”

Mysql优化

Mysql的索引类型

BTree索引

• Btree索引是以B+树结构存储数据

• Btree索引能够加快数据的查询速度

• Btree索引更适合进行范围查找

什么情况下可以用到BTree索引？

• 全值匹配的查询

• 匹配最左前缀的查询

  比如联合索引的第一列使用到了Btree索引，就可以。但是联合索引第二列使用到btree则不生效。

• 匹配列前缀查询

  指的是匹配某一列的开头部分：order_sn like ‘xxx%’

• 匹配范围值的查询

• 精确匹配左前列并范围匹配另外一列

  比如定义了一个order_sn和order_date的联合索引来说，第一列order_sn是精确匹配，第二列order_date是范围查询,则联合索引btree也可以生效

• 只访问索引的查询

  查询只需要返回索引（覆盖索引），无需返回数据行的查询。

Btree索引的使用限制

• 如果不是按照索引最左列开始查找，则无法使用索引

  比如order_sn和order_date建立的联合索引，如果索引列的顺序是先订单号，后订单日期，即订单号是最左列。而如果我们在查询中只通过订单日期作为查询条件，就无法使用到这个联合索引。

• 使用索引时不能跳过索引中的列

  比如我们在订单日期+订单人姓名+订单人电话建立联合索引，如果我们在查找的时候只使用到订单日期和订单人电话，那么我们就只能使用到订单日期这一列来进行查询过滤，

  而无法使用到下单人电话这一列，这是因为我们的查询条件跳过了索引中下单人姓名这一列。

• Not in和<>操作无法使用索引

• 如果查询中使用了索引中某个列的范围查找，则在索引中右边所有列都无法使用索引。

Hash索引

• Hash索引时基于hash表来实现的，只有查询条件精确匹配hash索引中所有列时，才能使用到

• 对于hash索引的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

Hash索引的限制

• Hash索引必须进行二次查找

  Hash索引中并没有保存字段值，所以必须选找到对应行，在对行的数据进行读取。

• Hash索引无法用于排序

• Hash索引不支持部分索引查找也不支持范围查找

• Hash索引不适合用在选择性区分度查的列

• Hash索引中hash码的计算可能存在hash冲突

为什么要使用索引？

1 索引大大减少存储引擎需要扫描的数据量

索引比数据行大小小的多，所以通过索引查找效率高，速度块

2 索引帮助我们进行排序以避免使用临时表

因为btree索引是按照键值的顺序进行存放的，所以可以利用btree进行排序，减少IO消耗。

3 索引可以把随机IO变为顺序IO

索引是不是越多越好？

索引可以加快查找速度，但也会带来性能消耗。

• 索引会增加写操作的成本

解决导入数据速度慢的办法不是增加索引，而是删除索引。

• 太多的索引会增加查询优化器的选择时间

Mysql的查询优化器会根据索引统计信息和查询条件为查询选择合适的索引，如果有多个索引可以使用，则增加mysql的分析时间影响效率。

索引优化策略

• 索引列上不能使用表达式或函数

• 前缀索引的建立和索引列的选择性（区分度）

  前缀索引可能会导致索引区分度降低！！！

• 避免给表的每一列都加索引，这忽略了索引对性能造成的影响

  好的优化方式建立多个列的联合索引

联合索引

要关注一下如何选择联合索引列的顺序？

Ø 经常使用到的列优先放最左

Ø 选择性高的列优先放最左

Ø 宽度小的列优先最左，宽度越小IO越小

覆盖索引

  Btree索引可以进行排序分组等等，也可以直接获取我们想要的数据，btree索引的叶子节点上存储了索引的关键字的值，如果我们可以根据索引的关键字直接获取索引查询中所需要的数据，这样就没必要读取数据行的信息了，这种包含了所有需要查询字段全部值索引，称为覆盖索引。

Ø 可以优化缓存，减少磁盘IO操作

Ø 可以减少随机IO，随机IO操作变为顺序IO操作

Ø 可以避免对INnoDB主键索引的二次查询

Ø 可以避免对MyISAM表进行系统调用

无法使用覆盖索引的情况

Ø 存储引擎不支持覆盖索引

Ø 查询中使用了太多列

Ø 使用了双%%的like查询

使用索引来优化查询

使用索引扫描来优化排序

Ø 索引的列顺序和order by子句的顺序完全一致

Ø 索引中所有列的方向（升序降序）和orderBY子句完全一致

Ø Order by中的字段全部在关联表中的第一张表中

使用btree索引模拟hash索引优化查询

Ø 只能处理键值的全值匹配查找

Ø 所使用的hash函数决定着索引键的大小

利用索引优化锁

Ø 索引可以减少锁定的行数

Ø 索引可以加快处理速度，同时也加快了锁的释放

索引的维护和优化

Ø 删除重复和冗余的索引

pt-duplicate-key-checker h=127.0.0.1

Ø 查找未被使用过的索引

Ø 更新索引统计信息及减少索引碎片

SQL查询优化

如何获取有性能问题的sql？

Ø 通过用户反馈获取存在性能的sql

Ø 通过慢查询日志获取存在性能问题的sql

Ø 实时获取存在性能问题的sql

实时查询：利用information数据库下的processlist表的time字段可以查询time超过多少秒的sql语句。

特定场景下sql优化

Ø 大表的数据修改，最好分批处理

Ø 如果修改大表的表结构

  对表中的列字段类型进行修改，改变字段的宽度时还是会锁表，无法解决主从数据库延迟的问题。办法是：

  可以在主上建立新表，把老表的数据导入到新表中，然后再老表上建立一系列触发器，这样对老表数据的修改就可以同步更新到新表中，当数据同步后在老表加一个排它锁，重新命名新表为老表的名字，删除老表。工具：pt-online-schema-change

后记

学海无涯，学富五车方能神驰九州！
ps：最近在GitChat上发布了一篇关于呼叫中心和AI结合的粗浅文章，大家有兴趣的话可以去看一下。

其他优质文章：
《成就一亿技术人，我在CSDN的这九年》
《计算机如何做减法？10个程序员9个不知道！！！》
《高级编程语言学习概论》
《互联网三高架构之高并发和高性能的理解》
《《跟任何人都能聊得来》读书笔记》
《这十年里的迷茫路口》
《一个码农的那五年》
《搞技术的总要做点高逼格的事情，那些lowB操作留给新人练手吧！》
《教你搭建一套自己的SVN服务器》
欢迎下方留言跟我一起交流,让我知道您来过~