Mysql知识点总结

最新推荐文章于 2023-06-06 13:44:02 发布

少年白char

最新推荐文章于 2023-06-06 13:44:02 发布

阅读量288

点赞数 3

分类专栏： mysql

本文链接：https://blog.csdn.net/u011342224/article/details/98483100

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql 介绍

MySQL 是一种关系型数据库，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL，因此它的稳定性是有保障的。MySQL是开放源代码的，因此任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306

事务

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来哪些问题？

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

事务隔离级别有哪些?MySQL的默认隔离级别是？

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看。

MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

两者的对比：

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复：MyISAM 强调的是性能，每次查询具有原子性,其执行比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。
是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用乐观(optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
读/写分离
经典的数据库拆分方案，主库负责写，从库负责读；
垂直分区
根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。如下图所示，这样来说大家应该就更容易理解了。
垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

数据库基本设计规范

所有表必须使用Innodb存储引擎
没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）。

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

数据库和表的字符集统一使用UTF8
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集。
所有表和字段都需要添加注释
使用comment从句添加表和列的备注，从一开始就进行数据字典的维护
尽量控制单表数据量的大小，建议控制在500万以内。
500万并不是Mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

谨慎使用Mysql分区表
分区表在物理上表现为多个文件，在逻辑上表现为一个表；

谨慎选择分区键，跨分区查询效率可能更低；

建议采用物理分表的方式管理大数据。

尽量做到冷热数据分离，减小表的宽度
Mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。

减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO）；

更有效的利用缓存，避免读入无用的冷数据；

经常一起使用的列放到一个表中（避免更多的关联操作）。

禁止在表中建立预留字段
预留字段的命名很难做到见名识义。

预留字段无法确认存储的数据类型，所以无法选择合适的类型。

对预留字段类型的修改，会对表进行锁定。

禁止在数据库中存储图片，文件等大的二进制数据
通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。

通常存储于文件服务器，数据库只存储文件地址信息

禁止在线上做数据库压力测试
禁止从开发环境，测试环境直接连接生产环境数据库

数据库字段设计规范

优先选择符合存储需要的最小的数据类型
原因：

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。

方法：

1、将字符串转换成数字类型存储，如：将IP地址转换成整形数据
mysql提供了两个方法来处理ip地址

inet_aton 把ip转为无符号整型(4-8位)

inet_ntoa 把整型的ip转为地址

插入数据前，先用inet_aton把ip地址转为整型，可以节省空间，显示数据时，使用inet_ntoa把整型的ip地址转为地址显示即可。

2、对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储
原因：

无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295
VARCHAR(N)中的N代表的是字符数，而不是字节数，使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存。

避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据
1、建议把BLOB或是TEXT列分离到单独的扩展表中
Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

2、TEXT或BLOB类型只能使用前缀索引
因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的

避免使用ENUM类型
修改ENUM值需要使用ALTER语句

ENUM类型的ORDER BY操作效率低，需要额外操作

禁止使用数值作为ENUM的枚举值

尽可能把所有列定义为NOT NULL
原因：

索引NULL列需要额外的空间来保存，所以要占用更多的空间

进行比较和计算时要对NULL值做特别的处理

使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用4字节和INT相同，但比INT可读性高

超出TIMESTAMP取值范围的使用DATETIME类型存储

经常会有人用字符串存储日期型的数据（不正确的做法）

缺点1：无法用日期函数进行计算和比较

缺点2：用字符串存储日期要占用更多的空间

同财务相关的金额类数据必须使用decimal类型
非精准浮点：float,double

精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节

索引设计规范

限制每张表上的索引数量，建议单张表索引不超过5个
索引并不是越多越好！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

禁止给表中的每一列都建立单独的索引
5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。
每个Innodb表必须有个主键
Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的

不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）

不要使用UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）

主键建议使用自增ID值

常见索引列建议

出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
包含在ORDER BY、GROUP BY、DISTINCT中的字段
并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好
多表join的关联列

如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）
使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

索引SET规范

尽量避免使用外键约束
不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引

外键可用于保证数据的参照完整性，但建议在业务端实现

外键会影响父表和子表的写操作从而降低性能

数据库SQL开发规范

建议使用预编译语句进行数据库操作
预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题。

只传参数，比传递SQL语句更高效。

相同语句可以一次解析，多次使用，提高处理效率。

避免数据类型的隐式转换
隐式转换会导致索引失效如:

select name,phone from customer where id = ‘111’;
3. 充分利用表上已经存在的索引
避免使用双%号的查询条件。如：a like ‘%123%’，（如果无前置%,只有后置%，是可以用到列上的索引的）

一个SQL只能利用到复合索引中的一列进行范围查询。如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到。

在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，使用left join 或 not exists 来优化not in 操作，因为not in 也通常会使用索引失效。

数据库设计时，应该要对以后扩展进行考虑
程序连接不同的数据库使用不同的账号，进制跨库查询
为数据库迁移和分库分表留出余地

降低业务耦合度

避免权限过大而产生的安全风险

禁止使用SELECT * 必须使用SELECT <字段列表> 查询
原因：

消耗更多的CPU和IO以网络带宽资源

无法使用覆盖索引

可减少表结构变更带来的影响

禁止使用不含字段列表的INSERT语句
如：

insert into values (‘a’,‘b’,‘c’);
应使用：

insert into t(c1,c2,c3) values (‘a’,‘b’,‘c’);
8. 避免使用子查询，可以把子查询优化为join操作
通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

避免使用JOIN关联太多的表
对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。

在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，Mysql最多允许关联61个表，建议不超过5个。

减少同数据库的交互次数
数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。
对应同一列进行or判断时，使用in代替or
in 的值不要超过500个，in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。
禁止使用order by rand() 进行随机排序
order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)=‘20190101’
推荐：

where create_time >= ‘20190101’ and create_time < ‘20190102’
14. 在明显不会有重复值时使用UNION ALL 而不是UNION
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作

UNION ALL 不会再对结果集进行去重操作

拆分复杂的大SQL为多个小SQL
大SQL逻辑上比较复杂，需要占用大量CPU进行计算的SQL

MySQL中，一个SQL只能使用一个CPU进行计算

SQL拆分后可以通过并行执行来提高处理效率

1、LIMIT 语句

分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。

SELECT * 
FROM   operation 
WHERE  type = 'SQLStats' 
       AND name = 'SlowLog' 
ORDER  BY create_time 
LIMIT  1000, 10;

好吧，可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？

要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：

SELECT   * 
FROM     operation 
WHERE    type = 'SQLStats' 
AND      name = 'SlowLog' 
AND      create_time > '2017-03-16 14:00:00' 
ORDER BY create_time limit 10;

在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。

2.隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句：

ql> explain extended SELECT * 
     > FROM   my_balance b 
     > WHERE  b.bpn = 14000000123 
     >       AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

其中字段 bpn 的定义为 varchar(20)，MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。

上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。

关联更新、删除

虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

比如下面 UPDATE 语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。

UPDATE operation o 
SET    status = 'applying' 
WHERE  o.id IN (SELECT id 
                FROM   (SELECT o.id, 
                               o.status 
                        FROM   operation o 
                        WHERE  o.group = 123 
                               AND o.status NOT IN ( 'done' ) 
                        ORDER  BY o.parent, 
                                  o.id 
                        LIMIT  1) t);

重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。

UPDATE operation o 
       JOIN  (SELECT o.id, 
                            o.status 
                     FROM   operation o 
                     WHERE  o.group = 123 
                            AND o.status NOT IN ( 'done' ) 
                     ORDER  BY o.parent, 
                               o.id 
                     LIMIT  1) t
         ON o.id = t.id 
SET    status = 'applying'

混合排序

SELECT * 
FROM   my_order o 
       INNER JOIN my_appraise a ON a.orderid = o.id 
ORDER  BY a.is_reply ASC, 
          a.appraise_time DESC 
LIMIT  0, 20

由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。

SELECT * 
FROM   ((SELECT *
         FROM   my_order o 
                INNER JOIN my_appraise a 
                        ON a.orderid = o.id 
                           AND is_reply = 0 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20) 
        UNION ALL 
        (SELECT *
         FROM   my_order o 
                INNER JOIN my_appraise a 
                        ON a.orderid = o.id 
                           AND is_reply = 1 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)) t 
ORDER  BY  is_reply ASC, 
          appraisetime DESC 
LIMIT  20;

EXISTS语句

SELECT *
FROM   my_neighbor n 
       LEFT JOIN my_neighbor_apply sra 
              ON n.id = sra.neighbor_id 
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND EXISTS(SELECT 1 
                  FROM   message_info m 
                  WHERE  n.id = m.neighbor_id 
                         AND m.inuser = 'xxx') 
       AND n.topic_type <> 5

去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

SELECT *
FROM   my_neighbor n 
       INNER JOIN message_info m 
               ON n.id = m.neighbor_id 
                  AND m.inuser = 'xxx' 
       LEFT JOIN my_neighbor_apply sra 
              ON n.id = sra.neighbor_id 
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND n.topic_type <> 5

提前缩小范围

SELECT * 
FROM   my_order o 
       LEFT JOIN my_userinfo u 
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p 
              ON o.pid = p.pid 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDER  BY o.selltime DESC 
LIMIT  0, 15

该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。
由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下，执行时间缩小为1毫秒左右。

SELECT * 
FROM (
SELECT * 
FROM   my_order o 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDER  BY o.selltime DESC 
LIMIT  0, 15
) o 
     LEFT JOIN my_userinfo u 
              ON o.uid = u.uid 
     LEFT JOIN my_productinfo p 
              ON o.pid = p.pid 
ORDER BY  o.selltime DESC
limit 0, 15

再检查执行计划：子查询物化后（select_type=DERIVED)参与 JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及 LIMIT 子句后，实际执行时间变得很小。

中间结果集下推

SELECT    a.*, 
          c.allocated 
FROM      ( 
              SELECT   resourceid 
              FROM     my_distribute d 
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a 
LEFT JOIN 
          ( 
              SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
              FROM     my_resources 
                   GROUP BY resourcesid) c 
ON        a.resourceid = c.resourcesid

那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。

其实对于子查询 c，左连接最后结果集只关心能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。


SELECT    a.*, 
          c.allocated 
FROM      ( 
                   SELECT   resourceid 
                   FROM     my_distribute d 
                   WHERE    isdelete = 0 
                   AND      cusmanagercode = '1234567' 
                   ORDER BY salecode limit 20) a 
LEFT JOIN 
          ( 
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
                   FROM     my_resources r, 
                            ( 
                                     SELECT   resourceid 
                                     FROM     my_distribute d 
                                     WHERE    isdelete = 0 
                                     AND      cusmanagercode = '1234567' 
                                     ORDER BY salecode limit 20) a 
                   WHERE    r.resourcesid = a.resourcesid 
                   GROUP BY resourcesid) c 
ON        a.resourceid = c.resourcesid

但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用 WITH 语句再次重写：

WITH a AS 
( 
         SELECT   resourceid 
         FROM     my_distribute d 
         WHERE    isdelete = 0 
         AND      cusmanagercode = '1234567' 
         ORDER BY salecode limit 20)
SELECT    a.*, 
          c.allocated 
FROM      a 
LEFT JOIN 
          ( 
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
                   FROM     my_resources r, 
                            a 
                   WHERE    r.resourcesid = a.resourcesid 
                   GROUP BY resourcesid) c 
ON        a.resourceid = c.resourcesid

为了对下面列举的一些优化进行测试，下面针对已有的一张表进行说明。

表名：order_history

描述：某个业务的订单历史表

主要字段：unsigned int id，tinyint(4) int type

字段情况：该表一共37个字段，不包含text等大型数据，最大为varchar(500)，id字段为索引，且为递增。

数据量：5709294

MySQL版本：5.7.16 线下找一张百万级的测试表可不容易，如果需要自己测试的话，可以写shell脚本什么的插入数据进行测试。以下的 sql 所有语句执行的环境没有发生改变，下面是基本测试结果：

select
 count(*) 
from
 orders_history;

返回结果：5709294

三次查询时间分别为：

8903 ms

8323 ms

8401 ms

一般分页查询
一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

第一个参数指定第一个返回记录行的偏移量，注意从 0开始

第二个参数指定返回记录行的最大数目

如果只给定一个参数：它表示返回最大的记录行数目

第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行

初始记录行的偏移量是 0(而不是 1)

下面是一个应用实例：

select
 * 
from
 orders_history 
where
 type=8
 limit 1000,10;

该条语句将会从表 orders_history 中查询 offset:1000开始之后的10条数据，也就是第1001条到第1010条数据（ 1001<=id<=1010）。

数据表中的记录默认使用主键（一般为id）排序，上面的结果相当于：

select
 * 
from
 orders_history 
where
 type=8
 order 
by
 id limit 10000,10;

三次查询时间分别为：

3040 ms

3063 ms

3018 ms

针对这种查询方式，下面测试查询记录量对时间的影响：

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

三次查询时间如下：

查询1条记录：3072ms 3092ms 3002ms

查询10条记录：3081ms 3077ms 3032ms

查询100条记录：3118ms 3200ms 3128ms

查询1000条记录：3412ms 3468ms 3394ms

查询10000条记录：3749ms 3802ms 3696ms

另外我还做了十来次查询，从查询时间来看，基本可以确定，在查询记录量低于100时，查询时间基本没有差距，随着查询记录量越来越大，所花费的时间也会越来越多。

针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下：

查询100偏移：25ms 24ms 24ms

查询1000偏移：78ms 76ms 77ms

查询10000偏移：3092ms 3212ms 3128ms

查询100000偏移：3878ms 3812ms 3798ms

查询1000000偏移：14608ms 14062ms 14700ms

随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

使用子查询优化
这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100;
select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下：

第1条语句：3674ms

第2条语句：1315ms

第3条语句：1327ms

第4条语句：3710ms

针对上面的查询需要注意：

比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍

比较第2条语句和第3条语句：速度相差几十毫秒

比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍

这种方式相较于原始一般的查询方法，将会增快数倍。

使用 id 限定优化
这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

查询时间：15ms 12ms 9ms

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

还可以有另外一种写法：

select * from orders_history where id >= 1000001 limit 100;

当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from orders_history where id in(select order_id from trade_2 where goods = 'pen')limit 100;

这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

使用临时表优化
这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

关于数据表的id说明
一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

使用先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *；

MySQL联合索引

1、联合索引是两个或更多个列上的索引。对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

关于JOIN优化

尽量使用inner join，避免left join：
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

深入分析索引页暨计算索引树高度

一些术语
在深入了解索引以及叶子节点结构之前，我们先提前了解一些专业术语：B+Tree, ROOT, leaf和level，为了接下来更好的掌握InnoDB的索引。

B+Tree

InnoDB用B+Tree构造它的索引（MyISAM也是一样的），当数据不能完全加载到内存中，需要从磁盘读取时，这时候B+Tree结构是非常有效的。它能保证访问任何数据的效率，查找索引过程中每次读取事实上就是一次IO，整个查找过程主要与索引树的高度有关。

ROOT

每一个索引树都是从一个ROOT页开始，它的位置是固定的，永远被保存在InnoDB的数据字典中，ROOT页就是访问索引树的起点。索引树可能只有一个ROOT页，也可能有成百上千个页，这时候就是多级树，并且树的高度超过1。

leaf

索引树的每个页都与叶子（leaf）页或者非叶子（non-leaf）页相关联。叶子页包含实际的行数据，非叶子页只包含指向非叶子页或者叶子页的指针。索引树是平衡的，B+Tree中的B是Balance的意思，而不是Binary。所以，索引树的所有分支有相同的深度。

level

InnoDB索引树中每个页都有一个level值，其中：叶子页level=0，从叶子页往ROOT页，level值递增。ROOT页的level值加1就是树的深度（例如叶子页level=0；ROOT页level=1，那么索引树高度为2）。那些既不是叶子页，也不是ROOT页的页被称为内部（internal）页。

page directory

即页目录（就跟树目录的原理差不多），它是一个大小为2个字节的指向4~8个记录的指针，它的作用是为了改进遍历一个索引页的性能。如果没有页目录，即使二分法查询，如果是拥有大概1000个记录的非叶子页，最多需要近10次的比较（2^10≈1000），并且索引页有多少级，这样的比较要成倍增加。

有了页目录后，我们就可以先用二分法从页目录中找到目标KEY所在的目录，然后通过页目录这个指针，找到目标KEY所在的只有4~8个记录的数组中。我们假设每个页目录平均指向5个记录，那么，1000个记录的非叶子页，需要200个页目录，二分法查找只需要8次（2^8=256），整个遍历过程少了20%的开销。

mysql 自带主从复制

MySQL支持三种复制方式：基于行(Row)的复制、基于语句(Statement)的复制和混合类型(Mixed)的复制。

基于语句的复制早在3.23版本中就存在，而基于行的复制方式在5.1版本中才被加进来。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。

混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后，如果是从一个高吞吐量的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。

mysql主备复制实现分成三个步骤：

master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看）；

slave将master的binary log events拷贝到它的中继日志(relay log)；

slave重做中继日志中的事件，将改变反映它自己的数据。

以上只是概述，实际上每一步都很复杂：
第一步是在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

下一步，备库将主库的二进制日志复制到其本地的中继日志中。首先，备库会启动一个工作线程。称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump）线程，这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。

备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中，它对我们稍后提到的场景非常有用。

这里采用的mysql的版本号是5.5.51，安装配置可以参考《 Linux(CentOS)中常用软件安装，使用及异常——MySQL, VmTools》。

权限配置
mysql>GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root@'%' IDENTIFIED BY 'root';
复制账户事实上只需要有主库上的REPLICATION SLAVE权限，并不一定需要每一端服务器都有REPLICATION CLIENT权限，那么为什么我们要把这两种权限给主/备库都赋予呢？这有两个原因：

用来监控和管理复制的账号需要REPLICATION CLIENT权限，并且针对这两种目的使用同一个账号更加容易。

如果在主库上建立了账号，然后从主库将数据克隆到备库上时，备库也就设置好了——变成主库所需要的配置。这样后续有需要可以方便地交换主备库的角色。
如果无脑式配置可以：

主备库配置
关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！
备注：文中采用的案例中主备库都有5个schema：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| canal_test         |
| mysql              |
| performance_schema |
| test               |
+--------------------+

主库的/etc/my.cnf配置(主机host:xx.xx.xx.73)

[mysqld]
log-bin=mysql-bin
server-id=1

备库上也需要在/ect/my.cnf进行配置(备机host:xx.xx.xx.60)

[mysqld]
log-bin=mysql-bin
server-id=2
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1

server_id 是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置 bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

启动slave
接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='xx.xx.xx.73',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='xxxx',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=0;
    ```
MASTER_LOG_POS的值为0，因为它是日志的开始位置。
你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：
```sql
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: xx.xx.xx.73
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
 
1 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行：

mysql> start slave;
运行show slave status查看输出结果：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xx.xx.xx.73
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 2395
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2395
              Relay_Log_Space: 409
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

在这里主要是看:

               Slave_IO_Running=Yes
               Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

（如果此时Slave_SQL_Running=No，可以参考下一节“异常情况处理”进行解决）

你可查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接(Binlog Dump)：
在master上输入show processlist\G;

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 30
   User: root
   Host: localhost
     db: canal_test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 33
   User: root
   Host: xx.xx.xx.xx:49005
     db: NULL
Command: Binlog Dump
   Time: 33
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
同样，在备库也可以看到两个线程，一个是I/O线程，一个是SQL线程（Connect）：

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: xx.xx.xx.60:62159
     db: NULL
Command: Binlog Dump
   Time: 67811
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 14
   User: root
   Host: localhost
     db: canal_test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 3. row ***************************
     Id: 19
   User: root
   Host: xx.xx.xx.60:62390
     db: NULL
Command: Sleep
   Time: 187
  State: 
   Info: NULL
*************************** 4. row ***************************
     Id: 20
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 64
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 21
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 64
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

异常情况处理
在上一小节中在start slave之后进行show slave status就出现了想要的结果——“Slave_SQL_Running=Yes”.但是有些时候，却不是这样的：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xx.xx.xx.73
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 2172
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'canal_test'; database exists' on query. Default database: 'canal_test'. Query: 'create database canal_test'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 2474
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'canal_test'; database exists' on query. Default database: 'canal_test'. Query: 'create database canal_test'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

可以看到Slave_SQL_Running=No，那么该怎么解决呢？

解决方案1
程序可能在slave上进行了写操作，也可能是slave机器重启后事务回滚造成的。
如果是事务回滚造成的，可以：

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

最后通过show slave status进行查看。

解决方案2
首先停掉slave服务：

mysql> slave stop;
到master上查看主机状态：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     2395 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后到slave服务器上执行手动同步：

mysql> change master to 
    -> master_host='xx.xx.xx.73',
    -> master_user='root',
    -> master_password='xxxx',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=2395;
mysql> slave start;

案例测试
在master上的Schema Name: canal_test中有一个perosn的表，表结构如下：

mysql> describe person;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

表中有一条记录：

mysql> select * from person;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  2 | zzh2 |   21 | m    |
+----+------+------+------+

（注意此时slave中的数据是一样的）
往master上插入一条数据，之后查看：

mysql> insert into person values(1,'zzh',22,'m');
mysql> select * from person;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | zzh  |   22 | m    |
|  2 | zzh2 |   21 | m    |
+----+------+------+------+

可以看到master中成功插入了一条数据，之后可以同样在slave中输入select * from person来查看，如果结果master和slave相同，那么恭喜你主备复制已经成功了。

少年白char

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql知识点总结

mysql 介绍MySQL 是一种关系型数据库，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL，因此它的稳定性是有保障的。MySQL是开放源代码的，因此任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306事务原子性：...
复制链接

扫一扫