MySQL相关知识点整理

写日志

0：每隔一秒把 log buffer 中的数据刷新到os buffer，并调用 fsync() 写入到log文件

1：提交事务的时候，把logbuffer中的数据刷到os buffer，并调用文件系统的写入操作，将缓存刷新到磁盘

2：提交事务的时候，将数据写入 os buffer，然后每隔1s 调用文件系统的flush操作

sync_binlog=0 ： 事务提交后，不会立即将binlog cache中的日志信息刷到磁盘，二十依靠操作系统机制将缓存落盘，风险很大

sync_binlog=1 ： 事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存

sync_binlog=n ：每写n次操作系统缓冲就执行一次刷新操作

数据库中每一个表最后面会隐藏三个列

可重复读 的read view 生成时在第一次select 语句执行的时候

不可重复读 的read view 生成实际是每次select 语句的时候

锁的分类

锁的使用场景

锁的算法（Innodb）

知识点

查询信息

等待信息

执行时间

执行状态

锁的状态

等等等等

更小的数据类型，因为分配内存空间是通过数据类型分配的

数据类型越简单越好，能用数字就不要用字符串

尽量避免NULL，对于mysql来说很难优化，因为允许null的列使得索引、索引统计和值的比较更加复杂

尽量用满足需求的最小长度

varchar在mysql5.6之前变更长度，或者从255一下变到255以上时，会导致锁表

尽量避免 BLOB 和TEXT 的使用，两者分别采用二进制和字符串的方式存储

当表中 存在TEXT或者很大的varchar的时候，
可以考虑将这个字段和表中的其他字段做拆分处理，这样其它表中查找的性能会高一点

使用枚举类型替代字符串

特殊类型数据存储方式，如IP的转换函数  inet_aton/inet_ntoa

小表 join 大表

相同类型的数据会聚集在一个文件中

查找的时候根据创建条件判断在那个文件中，不需要扫描所有数据

分而治之

创建语法  partition by

应用场景

分区表限制

分区表类型

分区表注意事项

id 相同：执行顺序从上到下

id 不同：id 越大，优先级越高，先被执行

id 有相同的，有不同的：相同的视为一组，不同组id越大越先执行

优先级顺序（最好到最坏）：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

all:全表扫描

index：全索引扫描这个比all的效率要好

range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描

index_subquery：利用索引来关联子查询，不再扫描全表

unique_subquery: 该连接类型类似与index_subquery,使用的是唯一索引

index_merge：在查询过程中需要多个索引组合使用

ref：使用了非唯一性索引进行数据的查找

eq_ref ：使用唯一性索引进行数据查找

const：这个表至多有一个匹配行

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

主要关注如下几个即可： system > const > ref > range > index > ALL

using index：使用了索引覆盖

using index condition：使用了索引下推

using filesourt：使用了临时空间排序，没有用索引排序

a、大大减少了服务器需要扫描的数据量

b、帮助服务器避免排序和临时表（索引已经排好序了）

c、将随机io变成顺序io

1、主键索引

2、唯一索引

3、普通索引

4、全文索引（一般是针对varchar或者text字段的）

5、组合索引

1、回表（普通索引）才会有回表的情况
     根据普通索引找到子节点中存储的主键，然后根据这个主键再到聚集索引定位记录

2、覆盖索引
     select id,name from user where name='a';   id 主键索引   name普通索引
     explain的输出结果Extra字段为Using index时，能够触发索引覆盖

3、最左匹配

4、索引下推（组合索引） ————— 先在索引层面做一次判断，进行一次过滤  
     SELECT * from user where name like '陈%' and age=20   index(name,age)
     5.6 之前是查询到 "陈%"后就回表，mysql 5.6后 过滤掉age后 再回表，这样就会提前过滤掉一部分数据

全值匹配

匹配最左前缀

匹配列前缀

匹配范围值

精确匹配某一列并范围匹配另外一列

只访问索引的查询

当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

使用前缀索引

使用索引扫描来排序————— 因为索引已经排好序了

union all,in,or都能够使用索引，但是推荐使用in—— 执行计划基本一样，效率也差不多，但是in更好一点

范围列可以用到索引
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

强制类型转换会全表扫描

更新十分频繁，数据区分度不高的字段上不宜建立索引

创建索引的列，不允许为null，可能会得到不符合预期的结果

当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

能使用limit的时候尽量使用limit

单表索引建议控制在5个以内

单索引字段数不允许超过5个（组合索引）

命令：show status like 'Handler_read%';

like 以%开头的情况

索引列使用了计算，比如 where age + 10 =  30 不走索引，但是 where age = 30-10 走索引

索引列使用了函数会导致索引失效，比如 where concat(name,'abc') = 'xxxxabc' 索引失效

索引列和=后面的值存在类型转换的情况

1、master 写日志到binlog

2、slave会在一定时间间隔内探测master中的日志有无发生变化

3、如果改变、则开启一个IO Thread 请求master 二进制事件

4、收到请求后，master 会为每一个IO Thread 启动一个dump 线程，用于发送二进制事件（顺序读）

5、slave 收到二进制文件之后会保存 到中继日志（relay log）  （顺序写）

6、slave 将启动 SQL Thread 从relay-log中的日志，在本地重放同步数据，使得保持一致（随机写）

7、IO Thread 和 SQL Thread 进入休眠状态、等待下一次被唤醒

1、配置各自服务的my.conf 文件

2、在master中，授权对应的slave （grant 命令），允许主从复制的IP地址等

3、在slave中，配置连接信息，从master中进行数据拉去，通过 change master to ..... 进行配置

4、show slave status 查看slave状态（Slave_IO_Running和Slave_SQL_Running状态都是YES的时候就正常了）

5、启动slave ，命令start slave

1、先分别配置两个主从，master1和slave1、master2和slave2，步骤同上一样

2、在master1中执行change master to... (省略的信息master2中的Host，user，pwd等)

3、同样在master2中执行change master to ... (省略的信息master1中的Host，user，pwd等)

change master to master_host='192.168.85.11',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;

添加配置
gtid_mode=on
enforce-gtid-consistency=true

从库执行如下命令即可

1、主从复制都是单线程操作

2、master 读完后向 slave 中继日志（relay-log）写入的整个过程都是顺序读写操作，所以效率取决于设备和带宽等外界因素

3、在slave中，SQL 从relay-log 重放日志的时候 DML 和 DDL 的IO 操作都是 随机的，所以成本很高

4、另外 SQL Thread 也是单线程的，所以当主库的并发较高时，Slave 的SQL Thread 的压力就会增大、会产生锁等待

5、大事务

6、锁资源的抢占

1、分库架构、分散压力

2、读写分离、主写从读

3、加入缓存、如redis，减少数据库的读压力

4、数据库分散在不同的机器

5、提升硬件设备

6、组提交

7、mysql 5.6开始就使用MTS 并行复制技术解决延时问题

8、mysql5.7的并行复制策略

怎么做的？？？

通过 show master status / show slave status 查看 position 是否一致

reset master、reset slave 重置后重新配置