MySQL提升课程 全面讲解MySQL架构设计

GTID是全局事务ID，其保证为每个在master上提交的事务在复制集群中可以生产一个唯一ID。GTID的生成策略是source_id(也就是server的uuid，在auto.conf文件里面可以看到):transaction_id(自增序列)。

[auto]
server-uuid=67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026

在基于GTID的复制中，slave会告诉master，slave已经执行事务的 GTID，master也会告诉slave，slave未执行事务的GTID。同一个事务只会在指定的从库执行一次。

基于GTID复制的优点是：

1.可以很方便的进行故障转移，记录master最后事务的GTID值。比如master：A，slave：B,C。当A挂了后，B执行了所有A传过来的事务。当C连接到B后，在自己的binlog找到最后一次A传过来的GTID。然后C将这个GTID发送给B，B获取到这个GTID，就开始从这个GTID的下一个GTID开始发送事务给C。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。

2.slave不会丢失master的任何修改(开启了log_slave_updates)

基于GTID复制的缺点：
1.不支持非事务引擎。
2.故障处理比较复杂，需要注入空事务。
3.不支持sql_slave_skip_counter(一般用这个来跳过基于binlog主从复制出现的问题。)
4.对执行的sql有一定的限制。
5.为了保证事务的安全性，create table ... select无法使用。不能使用create temporary table创建临时表。不能使用关联更新事务表和非事务表。

[mysqld]
bin_log =mysql-bin #开启二进制日志
server-id=1 #设置server-id

gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on

[mysqld]
bin_log =mysql-bin #开启二进制日志
server-id=1 #设置server-id

gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on

change master to master_host='127.0.0.1', master_port=3306, master_user='rpl', master_password='123', MASTER_AUTO_POSITION=1;

如何配置多线程复制
stop slave;
set gloab slave_parallel_type='logical_clock';  #设置逻辑时钟
set global slave_parallel_workers=4 #设置并发线程
start slave;
mysql> show processlist;    #查看当前mysql的运行线程

   把一个数据库拆分成多个数据库
   把多个数据库分配在不同的数据库实例节点上
   把同一个数据库中的某一个表分配在不同的数据库实例上名字一样构相同的表
   把同一个数据库中的某一个表分配成多个不同名字的表结构相同的表（水平拆分）
   把同一个数据库中的某一个表根据业务不同分配成多个不同名字结构也不相同的表（垂直拆分）

    如何选择分区键
        分区键要能尽量避免跨分区查询的发生。例如一个博客表，如果采用每一篇博客的主键ID来进行分区，查询某个用户的博客时则要跨多个分区去汇总数据，性能会比分区前更慢。所以可以考虑使用用户ID来进行分区，则只需要在一个分片中进行查询
        分区键要能尽量使各个分片中的数据平均。分片就是为了减轻数据库的负载，如果分片后许多查询和更新都集中在一个分片上那分片就意义不大了
    如何存储无需分片的表
        每个分片中存储一份相同的数据。这种方法适合数据量小的表，可以提升关联查询的效率。
        使用额外的节点统一存储。
    如何在节点上部署分片
        每个分片使用单一数据库，并且数据库名也相同
        将多个分片表存储在一个数据库中，并在表名上加入分片号后缀
        在一个节点中部署多个数据库，每个数据库包含一个分片
    如何分配分片中的数据
        按分区键的Hash值取模来分配分片数据
        按分区键的范围来分配分区数据
        利用分区键和分片的映射表来分配分片数据
   如何生成全局唯一ID
        使用auto_increment_increment和auto_increment_offset参数
        使用全局节点来生成ID
        在Redis等缓存服务器中创建全局ID

31：如何获取有性能问题的SQL

1.通过用户反馈获取存在性能问题的SQL.
2.通过慢查日志获取存在性能的SQL.
  启动慢查日志
  slow_query_log=on
  set global slow_query_log=on;
  slow_query_log_file 指定慢查日志存储路径及文件
  默认情况保存在mysql的数据目录中，最好日志存储和数据存储分开。
  long_query_time
  指定记录慢查日志SQL执行时间的阀值，单位为秒，默认值为10秒。精确到微秒,
  如果为一毫秒这个值为 0.001 。
  记录的语句包括
  1.查询语句
  2.数据修改语句
  3.已经回滚的SQL
  log_queries_not_using_indexes 是否记录未使用索引的SQL

慢查日志中记录的内容：

第一行记录了：

用户信息，线程ID号 用户信息 sbtest ，线程ID为 17

第二行 ：记录了查询时间

第三行 ：锁的时间

第四行 ： 返回的记录行数

第五行： 扫描的行数

第六行 ： 执行的时间

第七行 ： 执行的语句

常用慢查询日志分析工具

1.mysqldumpslow

汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中指定的顺序输出。

mysqldumpslow –s r –t 10 slow.log

-s order (c,t,l,r,at,al,ar)

c: 总的次数

t:总的时间

l：锁的时间

r: 总数据行

at,al,ar: t,l,r 的平均数

at 总时间 /总次数

-t top 指定取前几条作为结果输出

2.pt-query-digest

pt-query-digest –explain -h=127.0.0.1,u=root,p=root slow.log>slow.report

可以包括执行计划。

3.实时获取性能的问题的SQL

select id,user,host,db,command ,time,state,info from information_schema.processlist where time>60;

查询服务器中查询时间超过60秒的SQL.

31：查询速度为什么会慢

SQL请求处理步骤
客户端发送SQL请求给MySQL服务器
MySQL服务器会在查询缓存中进行检查，查看是否可以在查询缓存中命中
服务端会对SQL进行解析、预处理再由优化器生成对应的执行计划
根据执行计划，调用存储引擎中的API来查询数据
将查询的数据返回给客户端
32：查询缓存

如果查询缓存开关是打开的会优先对缓存中检查：
这个检查是对大小写敏感的hash查找实现：so，只能进行全值匹配查找。
如果在缓存中命中查询结果，会进行角色的权限认证，然后跳过后面的步骤把数据返回给客户端。
1. 查询的SQL要和缓存中的完全一致，所以命中并不容易。
2. 如果缓存中的数据是正确的，需要每次修改表的时候进行缓存的维护。
3. 而且进行缓存中查找的同时会对表加锁，所以对读写频繁的应用，查询缓存很可能降低查询效率（不建议开启查询缓存）

查询缓存相关参数
query_cache_type：
0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存。
例：select SQL_CACHE user_name from users where user_id = ‘100’;
query_cache_size：
表示查询缓存大小，也就是分配内存大小给查询缓存，分配大小为1024整数倍；设置为0表示不缓存
query_cache_limit ：
控制缓存查询结果的最大值
MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。
在 query_cache_type 打开的情况下，如果你不想使用缓存，需要使用sql_no_cache关键字
例：select sql_no_cache id,name from tableName;
query_cache_wlock_invalidate：
如果一个表被加锁是否允许直接从缓存中读取结果，默认为FALSE。
query_cache_min_res_unit：
查询缓存中存放的最小内存大小，默认4k；

SQL转变为执行计划
1:解析SQL
语法解析阶段是通过关键字对mysql进行语法解析并生成一颗对应的解析树
此阶段，使用MySQL语法规则验证和解析查询：检查语法是否使用的正确的关键字和关键字的位置是否正确
2:预处理
预处理阶段进一步的验证检查解析树是否合法
此阶段检查查询中所涉及的表和数据列是否存在及名字或者别名是否有存在歧义
3:优化SQL执行计划
查询优化器生成查询计划
影响查询计划生成的因素

1. 统计信息不准确
2. 执行计划中的成本估算并不等于实际的执行计划成本
3. mysql服务器层并不知道哪些页面在内存中，哪些页面在磁盘中，哪些页面顺序读，哪些页面要随机读
4. mysql所认为的最优可能和我们所认为的最优并不一致
5. mysql基于其成本模型选择最优的执行计划
6. mysql不会考虑到其他的并发查询
7. 有时候也会基于一些固定的规则来生成执行计划
8. mysql不会考虑不受其控制的成本，如：存储过程和用户自定义的函数

MySQL可以优化的SQL类型

1. mysql会重新定义标的关联顺序
2. 将外链接转换成内连接
3. 使用等价变换原则
4. 可以利用索引对count(),min(),max()进行优化。如最小值 btree索引第一个数据
5. 将表达式转化为一个常数
6. 子查询优化：把子查询转换成关系查询
7. 会提前终止查询：如发现不成立的条件，如属性设置为正数，查询条件是负数
8. 对in()进行优化，会对in中的数据进行排序，然后进行二分查找

如何确定查询各个阶段所消耗的时间
profile
Profiling是从 mysql5.0.3版本以后才开放的。
启动profile之后，所有查询包括错误的语句都会记录在内。
profile是一个session级别的配置，关闭会话或者set profiling=0 就关闭了。（如果将profiling_history_size参数设置为0，同样具有关闭MySQL的profiling效果。）
show profiles
show profile for query N 查询每个阶段所消耗的时间
5.5之后使用performance_schma
---------------------

32:特定sql语句的优化

  对大表的数据更新和删除进行批量操作同时连续操作需要中间暂停间隔
  如何修改大表的数据结构
    对大表的字段和字段的宽度进行变更时候都会锁表，同时无法解决数据库延迟的问题
    可以通过以下操作来完成大表的表结构变更
       新建一张同样的表结构
       对老表进行数据同步
       对老表进行存储过程和触发器的同步
       对新表重命名
     以上三个步骤可以通过一个工具完成
      pt-online-schema-change
      --alter="modify c varchar(150) not null default''"
      --user=root --password=PassWord D=testDataBaseName,t=tesTableName
      --charset=utf-8 --execute

      如何优化not in和<>查询
      #原始的SQL语句
      SELECT
      customer_id,
      first_name,
      last_name,
      email
      FROM
      customer
      WHERE
      customer_id NOT IN (
      SELECT
      customer_id
      FROM
      payment
      )
      
      #优化后的SQL语句
      SELECT
      a.customer_id,
      a,
      first_name,
      a.last_name,
      a.email
      FROM
      customer a
      LEFT JOIN payment b ON a.customer_id = b.customer_id
      WHERE
      b.customer_id IS NULL

      使用汇总表的方法进行优化
      #统计商品的评论数（若有上亿条记录，执行起来非常慢进行全表扫描）[优化前的SQL]
      select count(*) from product_comment where product_id=999;
      #汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用
      create table product_comment_cnt(product_id int,cnt int);
      #统计商品的评论数[优化后的SQL]
      #查询出每个商品截止到前一天的累计评论数+当天的评论数
      select sum(cnt) from(
      select cnt from product_comment_cnt where product_id=999
      union all
      select count(*) from product_comment where product_id=999
      and timestr>DATE(NOW())
      ) a

33：B-tree索引

B-tree索引能够加快数据的查询速度（MySQL使用B+Tree）
   因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
B+Tree 索引更适合进行范围查找
​ 是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，
并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，
进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，
相比之下B+Tree效率更高。

什么情况下可以使用B-tree索引
  全值匹配的查询
  order_sn = “987654321”
  匹配最左前缀的查询
  匹配列前缀查询
  order_sn like “9876”
  匹配范围值的查询
  order_sn > ‘987654321’ and order_sn < ‘98765444111’
  精确匹配左前列并范围匹配另外一列
  只访问索引的查询
B-tree索引的使用限制
  如果不是按照索引最左列开始查找，则无法使用索引
  使用索引是不能跳过索引中的列（三个组合索引的情况）
  Not in 和 <> 操作无法使用索引
  如果查询中有某个列的范围查询，则其右边所有列都无法使用索引


34:hash索引

  基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，
  存储引擎都会对所有的索引列的值计算一个哈希码，哈希码是一个较小的值，
  并且不同键值的行计算出来的哈希码不一样，哈希索引将所有的哈希码存储在索引中，
  同时在哈希表中保存指向每个数据行的指针。

  mysql> select * from testhash;

  +-------+-----------+
  | fname | lname     |
  +-------+-----------+
  | Arjen | Lentz     |
  | Baron | Schwartz  |
  | Peter | Zaitsev   |
  | Vadim | Tkachenko |
  +-------+-----------+
  4 rows in set (0.00 sec)

  假设索引使用假想的哈希函数f(),它返回下面的值：
  f('Arjen')=2323
  f('Baron')=7437
  f('Peter')=8784
  f('Vadim')=2458

  则哈希索引的数据结构如下：
  槽：        值：
  2323        指向第1行的指针
  2458        指向第4行的指针
  7437        指向第2行的指针
  8784        指向第3行的指针
  哈希索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快

  哈希索引限制
      A：hash索引必须进行二次查找,哈希索引只包含哈希值和行指针，而不存储字段值，
      所以不能使用索引中的值来避免读取行（即不能使用哈希索引来做覆盖索引扫描），不过，访问内存中的行的速度很快
      （因为memory引擎的数据都保存在内存里），所以大部分情况下这一点对性能的影响并不明显。

      B：hash索引无法用于排序,哈希索引数据并不是按照索引列的值顺序存储的，所以也就无法用于排序

      C：哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引的全部列值内容来计算哈希值的.如:数据列(a,b)上建立哈希索引,如果只查询数据列a,则无法使用该索引

      D：哈希索引只支持等值比较查询，如：=,in(),<=>(注意，<>和<=>是不同的操作)，不支持任何范围查询（必须给定具体的where条件值来计算hash值，所以不支持范围查询）
      E：hash索引中hash码的计算可能存在hash冲突


35:为什么要使用索引
    索引大大减少了存储引擎需要扫描的数据量
    索引可以帮助我们进行排序以避免使用临时表
    索引可以把随机I/o变成顺序I/o
36:索引是不是越多越好

• 索引会增加写操作的成本
• 太多的索引会增加查询优化器的选择时间

37:索引细节

     索引优化策略

      索引列上不能使用表达式或函数
前缀索引和索引列的选择性
     索引的选择性是不重复的索引值和表的记录数的比值
联合索引如何使用索引列的顺序
     经常会被使用到的列优先
     选择性高的列优先
     宽度小的列优先
38：覆盖索引

通常开发人员会根据查询的where条件来创建合适的索引，
但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。
如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，
也就是说这种索引包含（亦称覆盖）
所有需要查询的字段的值，我们称这种索引为覆盖索引。

覆盖索引
  优点：
  1、可以优化缓存，减少磁盘IO操作
  2、可以减少随机IO，使随机IO操作变为顺序IO操作
  3、可以避免对Innodb主键索引的二次查询
  4、可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况
  1、存储引擎不支持覆盖索引
  2、查询中使用了太多的列
  3、使用了双%号的like查询

覆盖索引

如果一个索引包含（或者覆盖）所有需要查询的字段的值，则称之为覆盖索引，优点如下：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，mysql就会极大的减少数据访问量
• 因为索引是按照列值顺序存储（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。 

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using index信息，示例如下，KEY last_name (first_name) USING BTREE：

mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

在first_name、last_name列上添加一个索引KEY last_name(first_name,last_name) USING BTREE，查询如下所示：

mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: const
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询，不过可以更进一步优化InnoDB。InnoDB的二级索引的叶子结点都包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些“额外”的主键列来覆盖查询，示例如下，KEY last_name(last_name) USING BTREE：

mysql> EXPLAIN SELECT first_name,last_name FROM people WHERE last_name LIKE 'zha
%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: range
possible_keys: last_name
          key: last_name
      key_len: 152
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

虽然上述示例中索引的列不包含first_name，但也能够用于对first_name做覆盖查询。

39：使用索引优化查询

  使用索引扫描来优化排序,通过排序操作,按着索引顺序扫描数据

注意事项：
    1、索引的列顺序和order by子句的顺序完全一致
    2、索引中所有列的方向(升序，降序)和order by子句完全一样
    3、order by中的字段全部在关联表中的第一张表中

利用索引优化