mysql 索引系列、读写分离、主从复制

mysql server组成

数据库连接池：采用连接池，减少频繁的开关连接
连接器-->分析器-->优化器-->执行器
连接器：控制用户的连接
分析器：词法分析，语法分析
优化器：优化sql语句，规定执行流程
执行器：sql语句的实际执行组件

存储引擎：存放具体数据文件

选择的存储引擎不同，数据存放的位置不同，不同的文件格式。默认：innodb

innodb、mylsam：磁盘

memory：内存

查询多使用 mylsam，增删改多使用 innodb。

 

 

索引

存储引擎的数据文件和索引存放位置不同，所以分为聚簇索引和非聚簇索引
聚簇索引：数据和索引放在一起（innodb）
非聚簇索引：数据、索引都有单独的文件（mylsam）
  .frm存放的是表结构、ibd存放的是数据和索引

默认所有的数据文件放到表空间中，不会有单独的ibd文件，需要自行设置

索引文件的结构（实现原理）：
hash：散列表，对值取hash值，取模。hash冲突解决：让高位参与运算（扰动函数）
二叉树：左子树必须小于根节点，右子树大于根节点。二分查找。缺点：深度无法控制，插入时性能低。
BTree： 非叶子节点二元组[key,data],即非叶子节点也会存储数据。
B+Tree：只有叶子节点才存储数据，叶子节点带顺序索引。

拓展：AVL树，自旋树。二叉树的缺点：很容易发展成一个链表，右边的节点一直增长。所以才会有AVL树

 

索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

 

常用索引

主键索引、唯一索引、普通索引、组合索引、全文索引

普通索引：用表中的普通列构建的索引，没有任何限制（覆盖索引 见下方讲解）

全文索引(FULLTEXT)：用大文本对象的列构建的索引（下一部分会讲解）

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值（最左匹配匹配）

 

覆盖索引：表A，主键a，字段b(增加索引),字段c 。 语句select id from A where b = N , 使用了覆盖索引，b字段的索引中存放了主键a的值，可以直接返回。但如果是*的话，就需要进行回表了。

或者表A，字段d，字段h  ，语句 select d from A ,没用覆盖索引。对 d 增加索引，即实现了覆盖索引

官网解释覆盖索引：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快

 

最左匹配：表A ，字段c，字段d，字段h。组合索引 d,h 。语句 select * from A where h=N,不会使用。

select * from A where d = N, 使用组合索引。即d,h索引 最左边的 d 必须先匹配到

 

索引下推：索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引

例如表A ，字段a，字段b，字段c，联合索引 b，c。

select * from A where b = N and c > 20  一般会使用联合索引，将 符合条件 b = N 的数据查出来，然后回表查询数据，再进行 c > 20的数据筛选。但是索引下推之后就： 符合条件的 b=N，联合索引，所以 再从c 中找出 > 20 的，不符合条件的不要，再进行回表。

 

*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低

解决：可以只取a和b的前几个字符作为索引

例如：ALTER TABLE 'table_name' ADD INDEX index_name(a(4),b（3))；

 

InnoDB 是通过B+Tree结构对主键创建索引，如果没有主键，会选择唯一键，如果都没有，会自动生成一个6位用户不可见的id键。主键或唯一键创建索引，在节点的叶子节点存储数据。

回表

当对非主键或唯一键创建索引时，例如对 name键创建索引，则该索引叶子节点存放的是主键或唯一键的值，需要回到上面再进行一次查找返回数据。

 

索引何时失效

    （1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；

    （2）like未使用最左前缀，where A like '%China'；

    （3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；

    （4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）

    （5）如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；

    （6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

 

优化小细节：

1. 不要对主键使用表达式  select * from table where id+1=5; 表达式不会使用索引
2. 尽量使用主键查询，而不是其它索引，主键查询不会触发回表
3. 尽量使用前缀索引，例如一个字段很长，只使用前几个字符作为索引
4. 查询时，尽量使用最左匹配，例如where 和 order by 的字段。 order by 后面的字段不要一个 asc 一个 desc
5. 能使用in 就不要使用 or ，能使用 union all 不使用 union  ,union 会进行distinct  ： SELECT country FROM Websites  UNION ALL SELECT country FROM apps ORDER BY country，
6. 更新频繁、字段区分度不高的不建议建立索引
7. 创建索引的列，不允许为null
8. 当需要进行表连接的时候，最好不要超过3张表
9. 如果明确知道只有一条数据返回，limit 1 能提高效率。limit  限制输出
10. 单表索引控制在5个内
11. 创建索引的时候避免：过早优化，对系统不了解的情况下进行优化

迁移数据时先将索引关闭，迁移完成后再打开。

 

join 优化

1. 尽量使用index 列
2. 使用 left join 或者 right  join  ，而不是 join on，强制控制表的查询顺序。
3. join 的时候，小表 loop 大表。例如 A表数据比较多， 则  b left join A, 或者  A  right  join B

 

索引监控

Handler_read_first  通过 index 获取数据的次数

Handler_read_key  读取索引第一个条目的次数

Handler_read_last  读取索引最后一个条目的次数

Handler_read_prev  通过索引读取下一条数据的次数

Handler_read_prev 通过索引读取上一条数据的次数

Handler_read_rnd  从固定未知读取数据的次数

Handler_read_rnd_next  从数据节点读取吓一跳数据的次数

 

CBO 基于成本的查询优化

RBO 基于规则的查询优化

查询优化

1. 查询时使用： explain 查询语句
2. show profile;
3. limit 第一个参数值不宜过大
4. 多表查询时不要使用*，而是指定具体需要返回的列名。尽量使用别名
5. 查询时排除不需要的列，而不是在应用中再获取前多少条
6. 将外连接转换为内连接，内连接比外连接效率高。内连接：关键字：inner join，外连接 left join、right join
7. 能用一个表达式完成的不要用两个。例如 a != 4 ,而不是 a > 4 or a <4

 

mysql 一页大小 16K

 

 

binlog 和 redo log

为什么了有了binlog还要有redo log

redo log的两阶段

 

ACID

A :原子性  ， C：一致性 ， I ： 隔离性 ， D ： 持久性。

原子性 undolog 实现，隔离性通过锁机制实现，持久性 通过redolog实现，一致性其它三者。

mysql默认事务隔离级别：可重复读 

 

锁机制

mylsam 默认表锁

innodb 默认行锁

innodb 的行锁模式及加锁方法

共享锁（读共享锁）、排它锁（写独占锁）

innodb 默认 对 update、delete、insert 自动给涉及的数据加上排它锁。select语句默认不会加任何锁类型。

innodb 只有通过索引条件检索数据，才使用行锁，否则，将使用表锁。

排它锁： select * from table where id = 1 for update;

 

mysql 主从复制和读写分离

 

mysql复制原理

1.master服务器将数据的改变记录二进制binlog日志，当master的数据发生改变时，将其改变写入二进制日志。

2.slave服务器会在一定时间间隔对master二进制日志进行探测是否改变，发生改变，则开启一个I/O Thread请求master二进制事件。

3.同时主节点为每个I/O线程启动一个dump线程，向其发送二进制事件。并保存至从节点本地的中继日志中，从节点将启动mysql线程从中继日志读取二进制日志。在本地重放，使得其数据和主节点的保持一致。最后I/O Thread 和sqlThread 将进入睡眠状态，等待下一次被唤醒。

就是说，主库会生成两个线程，一个I/O线程，一个sql线程。I/O线程会取请求主库的binlog，并将得到的binlog写道本地的relay-log（中继）中。主库会生成一个log dump线程，给从库I/O线程传binlog。sql 线程会读取relay log文件的日志，并解析成sql语句逐一执行。

注意：

1.master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能，通常为了数据安全考虑，slave也开启binlog功能）

2.slave开启两个线程：IO线程和sql线程。其中I/O线程负责读取master的binlog内容到中继日志relay log中。sql线程负责从relay log日志里读出binlog内容，并更新到slave数据库里。这样就能保证slave数据和master数据一致。

3.mysql复制至少需要两个mysql的服务，当然mysql服务可以在不同服务器上，也可以在同一台上。

4.mysql复制最好确保master和 slave服务器 版本相同。如不能满足，那么保证master版本低于slave版本。

5.master和slave节点时间需同步。

 

具体步骤：

1.从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user、password、ip、port），并且让从库知道，二进制日志的起点位置（file名postion号）；start slave

2.从库的IO线程和主库的dump线程建立连接

3.从库根据change master to 语句提供的file名和position 号，IO线程向主库发起binlog的请求

4.主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程

5.从库IO线程接收到binlog events，并存到本地relay-log 中，传送过来的信息，会记录到master.info中

6.从库sql线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay会自动被清理purge

 

mysql主从形式

1、一主一从

2、主主复制

3、一主多从

4、多主一从

5.联级复制

步骤：

1. 创建数据库 master、slave 执行 create database msb;  use msb;  msb:自己起名

2.在主(node1)服务器进行如下配置：

修改配置文件，执行以下命令打开mysql配置文件

vi /etc/my.cnf

在mysqld 模块中添加如下配置信息

log-bin=master-bin #二进制文件名

binlog-format=Row #二进制日志格式，有row、statement、mixed三种格式，row指的是把改变的内容复制过去，而不是把命令从服务器执行一遍。statement指的是在主服务器上执行的sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，效率比较高。mixed指的是默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

server-id=1  #要求各个服务器的id必须不一样

binlog-do-db=msb #同步的数据库名称

 

3.配置从服务器登录主服务器的账号授权

--授权操作

set global validate_password_policy=0;

set global validate_password_length=1;

grant replication slave on *.* to 'root'@'%' identified by '123456';

--刷新权限

flush privileges;

4.从服务器的配置

#修改配置文件，执行以下命令打开mysql配置文件

vi /etc/my.cnf

#在mysqld模块中添加如下配置信息

log-bin=master-bin #二进制的文件名

binlog-format=Row #二进制文件的格式

server-id=2  #服务器的id

5.重启主服务器的mysqld服务

#重启mysql服务

service mysqld restart

#登录mysql数据库

mysql -uroot -p

#查看master状态

show master status;

6.重启从服务器并进行相关配置

#重启mysql服务

service mysqld restart

#登录mysql数据库

mysql -uroot -p

#连接主服务器

change master to

master_host='masterIp',master_user='root',master_password='1234556',master_port=3306,master_log_file='master-bin.001',master_log_pos=154;

#启动slave

start slave

#查看slave 的状态

show slave status\G (注意没有分号)

7.此时可以在主服务器进行相关的数据添加删除工作，在从服务器看相关的状态

mysql主从同步延迟分析 （5.7版本之前存在）

   mysql的主从复制都是单线程的操作，主库对所有DDL和DML 产生的日志写进binlog，由于binlog是顺序写，所以效率很高。slave的sql Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多。另一方面，由于slq Thread也是单线程的，当主库的并发比较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

解决方案：

1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力主库高，保护主库。

3.服务的基础架构在业务和mysql之间加入memecache或者redis的cache层。降低mysql的读压力。

4.不同业务的mysql物理上放在不同机器，分散压力。

5.使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然小。

6.使用更加强劲的硬件设备。

MySQL 5.7新特性：并行复制原理（MTS）MySQL 5.7版本后，复制延迟问题永不存在

https://blog.csdn.net/A_man_only/article/details/84257702

 

Mysql 读写分离

生产不推荐 mysql-proxy ，性能不高

 

amoeba  -- 阿里巴巴

具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果

主要解决：

1.降低数据切分带来的复杂多数据库结构

2.提供切分规则，并降低数据切分规则给应用带来的影响

3.降低db与客户端的连接数

4.读写分离