MYSQL架构和性能(读后感)

 记录了面试重点，最底层得东西，持续更新

一.Mysql复制

    因为mysql的事务性的要求，所以不能简单的通过增加数据库服务器来分担数据库的读写负载

        mysql的复制功能提供分担读负载,进行水平扩展，增加多个备库同时为高可用和灾难复制以及备份提供更多的选择

        mysql的复制是通过记录主库的二进制日志来存放到备库而完成的，所以是异步的，所以同一时间但主备库可能不同

   

解决了问题:

1>实现在不同服务器上的数据分布，不需要太多的带宽，但是使用基与行的复制在进行大批量的更改时，会给带宽带来一定的压力，所以应该分批进行

2>实现数据读取的负载均衡，需要其他组件配合完成，（利用DNS轮询的方式把程序的读连接到不同备份数据库或者使用lvs,haproxy这样的代理方式）    

3>非共享架构，同样的数据分布在多台服务器上，增加了数据的安全性，利用备库的备份来减少主库负载，复制并不能代替备份

4>实现数据库高可用和故障切换（方便进行数据库高可用架构的部署，避免mysql单点失败）

5>实现数据库在线升级

复制分为:

1.基与sql语句的复制(SBR)

     二进制日志格式使用的是statement格式

   优点:生成的日志量少，节约网络传输i/o,并不强制要求主从数据库的表定义完全相同(大表进行修改，先从服务器修改，在进行主从切换，减少大表的停机时间)，相比于基与行的复制方式更为灵活

  缺点:1>对于非确定性事件，无法确保主从复制数据的一致性(uuid())

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

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

 

2.基与行的复制（对主从数据的一致性更加有保证）

        二进制日志格式使用的是基与行的日志格式

        优点 : 1>可以应用于任何sql的复制包括非确定函数，存储过程等

2>可以减少数据库锁的使用

       缺点: 1>要求主从数据库的表结构相同，负责可能中断复制

             2>无法在从上单独执行触发器

             

3混合格式

      根据实际内容切换

 

     2. mysql日志:

  mysql日志分为: mysql服务层日志和mysql存储引擎层日志

Mysql服务层日志: 二进制日志 慢查日志 通用日志

Mysql存储引擎层日志: innodb中的 重做日志和回滚日志

二进制日志:记录了所有对mysql数据库的修改成功事件，包括增删该查事件和对表结构的修改成功事件(可以通过binlog进行查看)

二进制日志格式:

 段的格式 binlog_format=STATEMENT，行的格式 binlog_format=Row,混合日志格式  binlog_format=MIXED

 

1>.段的格式

Set  binlog_format=STATEMENT;

Show variables like’binlog_format’;

优点:日志记录量相对较小，节约磁盘及网络I/0

缺点:必须要记录上下文信息，清楚的看到sql语句，保证语句在从服务器上执行结果和在主服务器上相同，但是对于特定的函数如UUID(),user(),这样非确定的函数无法复制，可能造成mysql复制的主备服务器数据不一致

2>行的格式:

  可以避免MYSQL复制中出现主从不一致问题

  增删改查操作所修改的数据行信息，修改一行就会每行信息

  例如:同一sql语句修改10000条数据的情况下，基与段的日志格式只会记录这个sql语句，而基与行的日志会有10000记录分别记录每一行的数据修改

优点: 使mysql主从复制更加安全,对每一行修改比基与段的复制高效，降低主从复制的延迟时间，如果误操作时候还可以通过对日志记录的数据修改操作反向处理方式来达到恢复数据的目的

缺点： 记录日志量较大

        可以修改 binlog_row_image=[FULL|MINIMAL|NOBLOB];

 

3>.混合日志格式：

       根据sql语句由系统决定基与段和基与行的日志格式进行选择，数据量的大小由所执行的sql语句决定

 

总结:

1、什么是主从复制（技术文）：

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

 

3.MYSQL主从复制的原理:

1>主数据库有个bin_log二进制文件，记录了所有sql语句

2>从读取主的二进制日志变更并写入到relay_log中(基与日志点的复制，基与GTID的复制)

3>让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可

具体由三个线程操作:

从库上生成两个线程，一个I/O线程，一个SQL线程，I/O线程请求主库的bin_log，并将得到的bin_log日志写入relay_log(中继日志)文件中，主库会生成一个log_dunp线程，用来给从库I/O线程传bin_log，SQL线程会读取relay_log的日志（1.基与sql段的日志是在从库上重新执行记录的sql   2.基与行的日志则是在库上直接应用对数据库行的修改）并执行

4. MYSQL主从复制用途(好处或者说为什么做主从):

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

    

5.  主从复制的必要条件

主:

1>主库开启binlog日志（设置log-bin参数)log_bin=mysql-bin.xxx

2>主从server-id不同

3>设置格式：binlog_format=mixed;

  从:开启二进制日志(一般的时候也开启，如果数据丢了可以用来恢复或者从再连一个从)和relay日志功能

    从库服务器能够连通主库

1>开启 server-id=?

2>log_bin=mysql-bin

3>relay-log=mysql-relay

4>binlog-format=mixed

  连接:检查show master status;

  1>授权  grant replication client,replication slave on *.* to rep1@'222.222.222.%' identified by 'rep1';

  2>从上执行:

      change master to 

      master_host='192.1222.12.22',

      master_user='rep1',

     master_password='rep1',

     master_log_file='mysql-bin.00001',(二进制日志)在主上 show master status显示的日志

    master_log_pos=348;(文件的偏移数开始读)

3>从上检查看是否连接  show slave status;  启动 start slave;

                   

6. 主从复制可能存在问题:

   1.主库宕机后，数据可能丢失2.从库只有sql Thread，主库写压力大，复制可能延迟

 解决办法: 半同步复制--解决数据丢失的问题

            并行复制--解决从库复制延迟的问题

7. 如果做主从后主服务器挂了怎么办？

   设置主从同步或双主同步的话证明两台服务器日志模式都是bin-log模式，如果其中有一台服务器意外宕机，此时会导致同步中断。
     恢复方法很简单：进入主机服务器（正常机器），在bin-log中找到出错时最接近错误标记的一个position，然后将宕机的服务器数据库执行change master，将master_log_file和master_log_pos重新指定一下就可以了

 8.为什么读写分离？

        只能在主上进行写操作，而读操作主从上都可以，所以减少读库上的压力，使之更有效的进行写操作时，索引进行读写分离

     1>读写分离的两种方式: 由程序实现读写分离  由中间件来实现读写分离

        由程序实现读写分离:

          优点:由开发人员控制什么样的查询在从库执行(有些实时查询不能再从库上执行)因此比较灵活，由程序直接连接数据库，所以性能损耗比较少

          缺点:增加了开发的工作，使程序代码更加复杂，人为控制，容易出错

       由中间件: mysql-proxy （一直没有发布正式版本）和maxScale工具

             Mysql-proxy:不仅可以解决读写分离的问题，还可以对多的从库实现负载均衡，但性能和稳定性一般

             maxScale:性能损耗相对小，不仅可以解决读写分离的问题，还可以对多的从库实现负载均衡

           优点:由中间件根据查询语法分析，自动完成读写分离，对程序透明，对于已有程序不用做任何调整

           缺点:由于增加了中间层，所以对查询效率由损耗(高并发时候会体现)，对于延迟敏感业务无法自动再主库执行

8. 读写分离和负载均衡的区别

   读写分离解决的是如何再复制集群的不同角色执行不同的sql语句

   读的负载均衡主要解决的是具有相同角色的数据，如何共同分担相同的负载

二. 索引

1. 索引特点

   提高查询速度   提高排序速度  提高分组统计

   2.MYSQL的索引是靠存储引擎层实现的

B-tree索引的特点:使用B+树的结构来存储数据的，每个叶子节点都包含指向下一个叶子节点的指针，这样方便叶子节点之间的遍历，能够加快数据的查询速度，

因为是顺序存储所以更适合进行范围查找

       MYSIAM索引再叶子节点是通过数据的物理位置来进行引用行的（索引指向行在磁盘上的位置）

 innodb索引是通过主键来引用B-tree的行的，次级索引指向对主键的引用

 

   在什么情况下可以用到B树索引

1. 全值匹配的查询  order_sn=’123123’

2. 匹配最左前缀的查询  联合索引的第一列符合查询条件，这个索引被用到，第二列的话不可以用到

3. 匹配列前缀查询  order_sn like ‘1233%’

4. 匹配范围值的查询   order_sn>’1233’ and order_sn<’123’

5. 精确匹配做前列并范围匹配另外一列

6. 至访问索引的查询

     B树索引的限制:

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

2. 使用索引时不能跳过索引中的列(联合索引中间一列跳过)

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

4. 如果查询中有某个列的范围查询，则其右边所以列都无法使用索引

 

Hash索引（innodb也可以使用，但是不是我们建立的，而是根据b树索引使用情况自行建立的）

特点:Hash索引是基于Hash表实现的，只有查询条件精确匹配(等值查询)，Hash索引中的所有列时，才能够使用到Hash索引

   对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码(所以支持全局匹配)，Hash索引中存储的就是Hash码

  Hash索引的限制:

     Hash索引必须进行二次查找，也就是说通过索引拿到数据位置必须回到表中取值(不过innodb被频繁访问的行都是缓存在内存中，所以比较快)

     Hash索引无法用于排序

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

     Hash索引无法利用前缀索引

3. 为什么使用索引 ？

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

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

 索引可以把随机I/O变为顺序I/O，可以充分发挥磁盘I/O性能

      索引是不是越多越好?

  索引会增加写操作的成本(innodb引入了插入缓存，把多次插入变成一次)

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

4. 聚簇索引和非聚簇索引的区别

  聚簇索引的既存储索引值又存储行数据，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块。
聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。
当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

缺点:如果碰到不规则数据插入时，可能会造成频繁的页分裂

5. 理想的索引

    1.查询频繁 2.区别度高  3.长度小  4.尽量能覆盖常用查询字段

   例如: 区分度高:100万用户性别基本是男女各50万，区分度就低

         索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度(占用内存多)

6. 索引碎片与维护:

   在长期的数据更改过程，索引文件和数据文件，都将产生空洞，形成碎片，我们可以通过nop操作（不产生对数据实质影响的操作）来修改表，比如 :表的引擎为innodb可以

Alter table xxx engine innodb, optimize table 表名 也可以修复

注意:修复表的数据和索引碎片会把所有数据文件重新整理一遍，使之对齐，这个过程如果表的行数比较大，也是非常耗费资源的操作 索引不能频繁修复，如果update操作很频繁，也是可以按周/月来修复

 7.主键、外键和索引的区别

• 定义
  主键--唯一标识一条记录，不能有重复的，不允许为空
  外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
  索引--该字段没有重复值，但可以有一个空值
• 作用
  主键--用来保证数据完整性
  外键--用来和其他表建立联系用的
  索引--是提高查询排序的速度
• 个数
  主键--主键只能有一个
  外键--一个表可以有多个外键
  索引--一个表可以有多个唯一索引

三 SQL语句优化

1. sql语句的时间花费在哪里

      等待时间  执行时间

      这两个时间并非孤立的，如果单条语句执行的快了，对其他语句的锁定也就少了，所以来分析如何降低执行时间

2. 使用慢查询日志获取有性能问题得sql语句(日志存储和数据存储分开)

  slow_query_log 启动停止记录  //set global slow_query_log=on;/set @@global.slow_query_log=1

  slow_query_log_file =filename指定慢查询日志得存储路径和文件

  long_query_time   指定记录慢查询日志sql执行时间得伐值（默认10s=>0.001s）

Log_queries_not_using_indexes 是否记录未使用索引得sql

min_examined_row_limit=1000记录那些由于查找多余1千次而引发的慢查询

log_slow-slave-statement 记录由slave所产生的慢查询

 

  记录符合条件得sql,包括查询语句，数据修改语句，已经回滚得sql，

慢查询日志分析工具(mysqldumpslow):汇总除查询条件外其他完全相同得sql，并将分析结构按照参数中所指向得顺序输出

    

3. SQL语句为什么会慢? 查询缓慢和解决方式（explain、慢查询日志、show profile等）

explain:解释语句的执行计划

id:查询的序号 

select_type:查询类型（简单查询 联合查询和子查询）

type:

system > const > eq_ref > ref > range > index > ALL

是指查询的方式(all:意味着从第一行全表扫描 index:扫描所有的索引节点 range:范围 const:更快 ref:通过索引快速引用到位置 eq_ref:是指通过索引列，直接引用某一行数据，常用于连接查询 contst system null 这三个分别指查询优化器到常量级别，甚至不需要查询时间，一般按照主键来查询，易出现const,system或者直接查询某个表达式，不经过表时，出现NULL)

possible_key:可能用到的索引

key:使用的索引

key_len:索引键用了多长

rows：估计扫描的行数

rel：显示索引的那一列被使用了，如果可能，是一个常量const。

Extra:额外信息

例如:  using index 是指用到了索引覆盖，效率非常高

也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。 
注意： 
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select * 
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

using where 是指光靠索引定位不了，还用where判断一下

using temporary 使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by 

using filesort:mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”      例如:由于索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”

当 select sum(shop_price)from goods group by cat_id(这句话，用到了临时表和文件排序)

(1) MYSQL服务器处理查询请求得整个过程

      1>客户端发送SQL请求给服务器

      2>服务器检查是否可以再查询缓存中命中该SQL（首先给缓存加锁，对于一个读写频繁得系统使用查询缓存可能降低查询处理得效率）

      3>服务器端进行SQL解析，预处理，再由优化器生成对应得执行计划

         语法解析节点:是通过关键字对mysql语句进行解析，并生成一课对应得“解析树”,MYSQL解析器对MYSQL语法规则进行验证

       预处理阶段是根据MYSQL规则进一步检查解析树是否合法

         

       4>根据执行计划，调用存储引擎API来查询数据

       5>将结果返回给客户端

4. 如何确定查询处理各个阶段所消耗得时间

使用profile/performance_schema(5.6以后)

    Set profiling =1;  启动profile  这是一个session级得配置,影响范围是可控的

    执行查询

    Show profiles 查看每个查询所消耗得总时间得信息

    Show profile for query N; 查询每个阶段所消耗得时间

   例如 Sending data  时间大   添加索引

 

starting：开始

checking permissions：检查权限

Opening tables：打开表

init ： 初始化

System lock ：系统锁

optimizing ： 优化

statistics ： 统计

preparing ：准备

executing ：执行

Sending data ：发送数据

Sorting result ：排序

end ：结束

query end ：查询 结束

closing tables ： 关闭表 ／去除TMP 表

freeing items ： 释放物品

cleaning up ：清理

总结:

 

1、慢查询 （分析出现出问题的sql）

2、Explain （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）

3、Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）

 

 

 

5. 对大表进行修改

1. 分批修改2.主从库切换修改  

3.在主服务器上建立新表，然后在老表进行数据同步，在老表建立一系列触发器，在老表得修改同步到新表，当同步后加上排它锁，删除老表，重命名新表

 四.limit的优化专题

 例如:limit 1000000,5       

limit取数据的时候会取10000005行数据，然后把前1000000行数据删除，这样就会导致效率极低

优化的办法:1.从业务上解决 (规避这个问题)

                 不允许翻过100页，以百度为例，一般翻页到70页左右

                 2.不用offert

                 例如  where id>1000000 limit 3;(可以利用索引)

                  3.使用索引覆盖和延迟关联的技巧

               优化的思路:只查索引，不查数据 得到id，再用id去查具体条目，这种技巧就说延迟关联

          select  * from a inner join (select id from a limit 1000000,3) as tmp on a.id=tmo.id;