Mysql

查询优化

Explain

type

ALL

全表扫描

Index

索引表全表扫描，其性能不会比ALL高，因为它实际还是会回表查数据的，每在索引表中查一条索引就回主表查一次数据
除非是排序情况，因为聚集索引是按照主键排序的，而其他索引是根据索引的列排序的。如果是需要列排序，Index才会比ALL快，因为免除了一次排序。
什么时候会出现index情况？一般需要查询的列都处在索引中时，会走index，因为要查的数据都在索引中，因此直接在索引中查即可，不需要回主表查，但如果不存在排序的话，效率跟ALL没区别

range

这种情况会计算出一个索引范围，只查询这个范围内的索引（只会在这个范围内回表查）。
范围计算的规则是基于最左匹配的，遇到不在索引中的，或者范围查询时，范围限定就此终结，如果后面的列仍在索引中，其会起到过滤的作用，虽然不会减小索引的范围，但能够阻止多余的回表查询。
举个例子：

  1. index(a,b,c)
  2. where a = 1 and b > 3 and c=4

以上只有a，b能缩小索引范围，而c虽然不能缩小要查询的索引的范围，但当扫描索引c!=4的记录时将不会进行回表查数据，而回表是主要性能瓶颈，因此c同样很重要

Ref

查找条件使用索引但使用的不是唯一索引或主键索引，因此可能会存在多个记录。但范围很小，其与range的区别如下：
range：

where a = 1 and b > 3

对应的索引为a = 1 and b = 4，a = 1 and b = 5，a = 1 and b = 6……， 其中每条都可能对应着多个主键记录
ref：

where a = 1 and b = 3

对应的索引为a = 1 and b = 3，其对应的主键记录可能有多条

对于非唯一索引，其存储结构为跟着多条主键所在页号

Ref-eq

其与Ref的区别为，结果集只有一条，即对应的主键记录只存在一条，通常是使用了主键和唯一索引，主要在表连接的时候出现，如使用主键进行连接的情况。单表一边会出现const
Mysql体系 - 图1

const

最好的情况，如果where后存在主键或者唯一索引的等值查询，将能够定位到这条唯一的记录，只会查一次

where id = 1

额外

对于where查询，不需要根据索引中列顺序来，因为优化器会自动优化顺序，使用索引

where a=1 and b=1
where b=1 and a=1

以上两个没区别，因此对于索引(a,b)无论哪个语句都能使用索引
因此，where中的列的顺序是无关的，因为无论怎么换都不影响，我们进行最左匹配原则的时候的最左匹配看的是索引中的列的顺序。

where a = 1 and b > 10
where b > 10 and a = 1

对于索引(a,b)以上都能使用到a和b，但对于索引(b,a)，则只能使用到索引b，因为b是范围查询

索引

数据结构

B+树

结构

插入节点

删除节点

索引原理

logn

索引的数据结构是B+树，而树的查找效率是logn，要比顺序查找的效率n快的多，因此在通过主键（聚集索引）查询数据时，效率是最快的。对于非主键的条件查询，使用辅助索引帮助优化查询效率。

顺序IO

由于索引只包含索引列信息，不包含数据信息，因此索引树大小是较小的，在现代计算机硬件条件下，基本所有索引都是可以全部加载在内存中的，因此可以近似看作在查找索引首地址时是一次随机IO操作，之后将所有索引信息读取到内存中是多次顺序IO操作。

索引的查找操作是在内存中进行的，速度很快。

随机IO

如果要查询的列不全部在辅助索引中，则在匹配到索引列后，要根据索引列记录的主键再通过聚集索引进行一次查找，这种操作叫做回表，回表操作是随机IO操作，是索引的性能瓶颈。

因此，这里可以总结一下索引的作用：

  1. 索引拥有logn的匹配速度
  2. 索引的加载可以看作是一次随机IO+多次顺序IO
  3. 索引的匹配是在内存中进行的
  4. 根据索引确定数据地址后只需要IO很少一部分数据

设计规范

设计原则

  1. 尽可能的减少回表操作。
  2. 尽可能的减少要扫描的记录数。
  3. 尽可能的避免额外的排序。

三星索引

  1. 第三星
    对于要查询的所有字段，全都添加到索引中，这里不考虑顺序。
    索引中包含全部的要查询内容，可以完全避免回表查询，将过滤掉绝大部分的随机读访问，对性能的提升很明显。但会导致索引的增大，极端情况与表记录完全一致
  2. 第一星
    根据where中谓词，最小化要扫描的索引片。将范围查询的索引字段放在索引的后面，等值的放在前面。要扫描的索引记录少即代表了可能回表的次数的减少
  3. 第二星
    利用索引的有序性避免排序。对于where a = 1 and b = 1 order by c的语句，索引(a,b,c)中对于a = 1 and b = 1的记录已经是按照c进行排序了，因此这时查询可以不需要再进行额外的排序直接返回。

由于目前排序的成本越来越低，在索引设计时，优先满足第三星，第一星为准。

注意事项

  1. 索引包含的列不要过多，极端情况下包含所有的列信息，造成巨大的索引表，会极大的影响性能。
  2. 索引字段不要太长，索引越长，占空间越大，内存中能容纳的索引数量越少，每个内存页加载的索引数量页越少，查找性能越低。
  3. 根据索引回表必定存在随机IO，在数据小的情况下反而不如全表顺序扫描
  4. 索引数量不要太多，数据的修改需要同步维护索引，带来额外的维护成本。要遵循二八原则，只对真正需要索引优化的地方使用索引。

锁

共享锁

S锁，也叫读锁，在查询时加的一种锁，读锁与读锁之间不会阻塞。

select ... lock in share mode

排他锁

X锁，也叫写锁，写锁之间以及写锁与读锁之间无法共存，会彼此阻塞。

select ... for update

行级锁

通过给索引上锁，由于记录更新时需要同步更新索引，而由于索引被锁，因此可以产生阻塞的作用。因此行级锁的使用必须是使用索引才行。

记录锁

对单独的一条记录加锁；当能够根据唯一索引确定到单独的一条记录时，只会进行加记录锁。

insert into (...) values (...) where id = 1;
insert into (...) values (...) where id = 2;

对于以上并发执行时，不会产生阻塞，因为两条语句均通过主键id确定的唯一一条记录，因此只会加记录锁，不会进行阻塞操作。

间隙锁

锁定一个范围，但不含记录本身。

insert into (...) values (...) where id > 2;
insert into (...) values (...) where id = 3;
insert into (...) values (...) where id = 1;

对于以上两条语句，第一条语句会将所有id>2这个范围进行加锁，因此第二条语句会被阻塞。

第三条语句不在范围锁的范围内，因此不会被阻塞。

Next-Key锁

记录锁+间隙锁。

create table test(a int , b int , primary key(a) , key(b))
insert into test select 1,1
insert into test select 3,1
insert into test select 5,3
insert into test select 7,6
insert into test select 10,8

对于select * from test where b = 3 for update而言，聚集索引a和辅助索引b均需要进行加锁锁定。

由于b=3对应的记录的主键a=5，因此主键索引加的是记录锁，即a=5的记录锁。

辅助索引b的上下索引值分别为1和6，因此其范围锁为（1，3）（3，6）两个范围锁。

因此对以下的语句有

insert into test select 4,2 
insert into test select 5,6
insert into test select 6,5
insert into test select 8,6
  1. b = 2∈(1,3)因此会被阻塞
  2. a=5被阻塞
  3. b=5∈(3,6)因此会被阻塞
  4. 不会被阻塞

幻读

select ... where a > 2 for update

上面的语句将手动加范围X锁，要插入时需要获取X锁，因此会被阻塞，因此可以阻止幻读。

在实际的使用时，默认使用可重复读的隔离级别，读取的数据均为事物开始时的数据，因此不会产生幻读。

锁升级

其他数据库的锁加载记录上，当记录很多时，可以存在大量的锁对象，开销很大，因此此时会锁升级为表级锁，降低锁对象带来的开销。

innodb的行级锁并不加载记录上，而是记录在内存页上，通过位图记录，因此多个锁和单个锁带来的开销是相同的，因此不存在锁升级的情况。

事物

ACID

  • 原子性：由redo提供
  • 一致性：由undo提供
  • 隔离性：由锁提供
  • 持久性：由redo提供

隔离级别

  1. 读未提交：当前事物所作的任何操作都随时可被其他事物看见，对于中间状态和失败事物的数据如果被其他事物读取，即读到了脏数据，因此会产生脏读。
  2. 读已提交：当前事物提交后，其他事物即可看到。在其他事物第一次与第二次查询同一条记录的期间，记录被事物更改且被提交，从而导致其他事物单个事物内两次查询到的记录不同，造成不可重复读
  3. 可重复读：当事物开始时，事物对数据加锁，其他事物无法更改数据，但是可以插入新数据(锁只能加在已有记录上,没有的记录没法加锁)，因此可能造成幻读。但在mysql中使用了MVCC机制，事物开始后只能看到当前版本下的数据，从而避免了幻读。
  4. 序列化：完全串行化操作。

控制语句

  • start transaction | begin : 准备开启事物（真正开启是在之后的第一次访问数据库时才开启的）
  • commit ：提交事物
  • rollback ： 回滚事物
  • savepoint id ：保存回滚点，设置id名称
  • release savepoint id ： 删除id回滚点
  • rollback to [savepoint] id ：回滚至id回滚点
  • set transaction ：设置事物隔离级别

innodb的事物支持

redo

innodb专属，属于引擎日志，为事物提供原子性和持久性。事物在提交前，必须将操作全部写入到redo日志中后才能提交完成事物。redo是物理日志。redo日志的持久化由配置参数innodb_flush_log_at_trx_commit控制：

  • 0：每秒一次将日志持久化到日志文件中，由专门的线程进行操作。
  • 1：每次事物提交时将日志持久化到文件中
  • 2：事物提交时将日志写入操作系统文件缓存中，由OS决定何时刷新到磁盘上

当mysql进行在执行事物过程中发生宕机时，有以下几种情形：

  • 对于已经成功提交的事物，没有影响
  • 对于事物执行但没来得及提交的，redo中记录了其操作，根据redo进行重新执行。

redo的写入是跟随事物的进行持续写入redo日志缓冲区，且为顺序写入，缓冲区同步磁盘时以512字节为单位原子性的写入磁盘（磁盘扇区为512字节，可以保证原子性的写入）

undo

innodb专属，属于引擎日志，为事物提供一致性支持。 undo用于记录事物执行之前的状态，当事物执行失败时会根据undo内容进行回滚操作。undo是逻辑日志。undo不存在与日志文件中，而是存在与数据库的一个特殊的undo segment中

undo产生的同时也会产生对于的redo。

undo还提供着MVCC的功能。当事物读取记录时，如果当前记录被其他事物锁定占用，当前事物可以通过undo查询之前版本的信息，从而无需等待，实现无锁定读。

当事物提交时，undo不会被马上删除，因为可能存在其他事物仍需要undo读取之前版本的信息。因此undo会被放入一个列表中等待专门的线程清理。

update流程

1、从磁盘读取记录放到内存。
2、记录undo log 日志。
3、记录redo log (预提交状态,会刷盘)
4、修改内存中的记录。
5、记录binlog,写入文件缓存或者刷盘
6、提交事务，写入redo log (commit状态)

1、在第一步、第二步、第三步执行时据库崩溃：因为这个时候数据还没有发生任何变化，所以没有任何影响，不需要做任何操作。
2、在第四步修改内存中的记录时数据库崩溃：因为此时事务没有commit，所以这里要进行数据回滚，所以这里会通过undo log进行数据回滚。
3、第五步写入binlog时数据库崩溃：这里和第四步一样的逻辑，此时事务没有commit，所以这里要进行数据回滚，会通过undo log进行数据回滚。
4、执行第六步事务提交时数据库崩溃：如果数据库在这个阶段崩溃，那其实事务还是没有提交成功，但是这里并不能像之前一样对数据进行回滚，因为在提交事务前,binlog可能成功写入磁盘了，所以这里要根据两种情况来做决定。

  • 如果binlog存在事务记录：那么就“认为”事务已经提交了，这里可以根据redo log对数据进行重做。其实你应该有疑问，其实这个阶段发生崩溃了，最终的事务是没提交成功的,这里应该对数据进行回滚。 这里主要的一个考虑是因为binlog已经成功写入了，而binlog写入后，那么依赖于binlog的其它扩展业务（比如：从库已经同步了日志进行数据的变更）数据就已经产生了，如果这里进行数据回滚，那么势必就会造成主从数据的不一致。
  • binlog不存在事务记录，那么这种情况事务还未提交成功，所以会对数据进行回滚。

日志

binlog

作用

记录了所有修改操作的日志，所有引擎均存在该日志。其作用如下：

  1. 备份恢复
  2. 主从复制传输
  3. 监听审计

持久化

对于事物引擎，在事物未提交之前，该日志会写入到缓冲区中，当事物提交后，日志从缓冲区写入文件。缓冲区由配置binlog_cache_size决定，该缓冲区基于会话，每个会话均会分配一个缓冲区，过大会导致内存浪费，过小时会写入临时文件影响性能。

格式
  • row：物理格式日志，日志量大，可以用于binlog监听
  • statement：逻辑语句格式，日志量小，但对于一些sql函数在恢复时无法恢复原值
  • mixed：混合模式，日志量中，此时默认使用statement，对于一些函数语句等，会使用row

主从

主从复制是基于binlog的，对于slave节点而言，从主节点复制来的binlog不会写入到自己的binlog中，因此需要开启log-slave-update来同步写入slave的binlog，这样slave也可以挂其他从节点了。

errlog

mysql过程中的错误、警告信息的记录

general log

所有操作日志均会被记录，包括查询操作。因此日志量会非常庞大，默认关闭状态。

slow log

设定慢SQL时间阈值，当sql执行时间超过阈值，则记录语句，默认10秒。

配置

  • only_full_group_by：对于select中的列、having或者order by的列，如果不在group by中，则必须使用聚合函数进行聚合。
  • pt-config-diff工具比较进程中的配置和配置文件中的配置差异。 | 参数 | 说明 | | —- | —- | | max_connections | 最大连接数 | | interactive_timeout | 交互连接超时时间 | | wait_timeout | 非交互连接超时时间 | | max_allowed_packet | 可接收数据包大小 | | sync_binlog | 每写多少次缓冲向磁盘同步一次binlog | | sort_buffer_size | 每个会话使用的排序缓冲区大小 | | join_buffer_size | 每个会话使用的连接缓冲区大小 | | innodb_flush_log_at_trx_commit | redo的磁盘刷新策略 | | innodb_buffer_pool_size | innodb缓冲池大小，应为os的75% | | innodb_buffer_pool_instances | innodb缓冲池实例个数，均分总大小 | | innodb_file_per_table | 每个表独立使用一个表空间 | | binlog_format | binlog格式：row|statement|mixed | | binlog_row_image | binlog_row格式记录形式：full|minimal(只记录更改行)|noblob | | binlog_cache_size | 每个会话未提交事物的binlog日志缓冲大小 | | log_slave_update | slave节点同步写binlog | | slow_query_log | 开启慢查询日志 | | long_query_time | 慢查询时间阈值 |

用户

创建用户：create user@ip identified by password

授权： grant select ... on db to user@ip

回收权限：revoke select ... on db from user@ip

存储引擎

MYISAM

  • 非事物
  • 堆表存储，存储的是物理地址，查询块
  • 表级锁
  • 支持B索引、空间索引、全文索引

适用不使用事物，读操作远大于写操作的场景

INNODB

  • 事物型引擎
  • 主键聚集存储
  • 行级锁及MVCC
  • 支持B索引、自适应hash索引、全文索引、空间索引

架构及分布

监控管理

版本区别