高性能mysql

0.关键字

1.架构

1.框架图(第2页)

①.连接/线程处理、查缓存、解析器、优化器、存储引擎

⬑ MySQL框架图

2.事务

①.A原子性: 整个事务要么全部提交成功，要么全部失败返回
②.C一致性: 数据库总是从一个一致性的状态转换到另外一个一致性的状态 (即总账不变)
③.I隔离性: 一个事务所做的修改在最终提交以前，对其他的事务时不可见的
     事务在提交之前也可能回滚操作，要回滚的数据被读取到肯定会导致业务的出错
④.D持久性: 事务一旦提交，所作的修改会永远保存在数据库中

3.隔离级别(I隔离性)

3.1.脏读

3.2.不可重复读

3.3.幻读

3.4.汇总

脏读: 事务的修改在没有提交也会被其他事务可见
不可重复读: 两次执行同样的查询，可能会得到不一样的数据，加行锁解决
幻读: 两次执行同样的查询，会得到不同条数的记录，加表锁或间隙锁解决
事务分类: 根据上面问题的解决可以将事务分为4个级别
     未提交读(Read Uncommitted)/提交读(Read Committed)/可重复读(Repeatable Read)/可串行化(Serializable)
     SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; #设置事务的级别写在事务的最前面
隔离级别的理解: 设置隔离级别类似于告诉mysql获取数据之前要加锁，如果不加锁也能获取到数据但是有可能是不变量被破坏的中间态数据

3.5.脏写

①.任何隔离级别都可以避免脏写，下面是没有解决脏写的案例(可以理解为存钱)

4.mysql的锁

①.假设有主键id为 10/20/30/40/50..的一张表

5.概念

①.事务日志: 如果数据修改已经记录到事务日志并持久化，数据没有写到磁盘，此时系统崩溃，数据也不会丢失
②.行级锁: 是由存储引擎实现的，服务层完全不了解存储引擎中锁的实现
③.混用存储引擎: 服务层不管事务，所有在同一个事务中使用多个表且不同引擎是不可靠的
④.MVCC使用行版本号，来替换读写锁，版本号是一个数单调递增，插入时记录版本号，删除时用版本号作为删除标识，修改被定义为删除和插入
⑤.undo: 为事务回滚而记录的日志

2.基准测试

1.指标

TPS: 每秒执行的事务数
QPS: 每秒执行的查询数
响应时间: 比如说95%的响应时间时在5ms以内
并发性: 并发性并不是一个测试的结果，而是测试的一个属性。并发测试是为了测试不同并发下的性能
可扩展性: 加机器能否提升性能的能力

2.要记录的数据

①.cpu使用率、磁盘i/o、宽带
  show global status(mysql查看服务器的状态命令)

SHOW GLOBAL STATUS LIKE 'Threads_connected';  # 当前正在连接的客户端线程数
SHOW GLOBAL STATUS LIKE 'Com_______';         # 执行的增删改查的语句总数

4.数据类型&优化

0.类型

①.mongo可以对json里面的字段创建索引，而mysql需要从json里面提取字段另外创建一列(这里是一个约束)，在对该列创建索引
②.mongo对数组类型的字段创建索引时是对每个元素创建索引，而mysql是对集合整体创建索引

1.类型大小

①.类型更小更好
  占用更少的磁盘/内存/cpu缓存/需要的cpu处理周期也少(在进行排序的时候)

2.null

①.避免使用null
②.为null的字段需要更多存储空间，在mysql如果有一张表允许7个字段为null，存储的时候会在这张表的每一行前面存储一个位图占8bit，来标识这7个字段在这一行是否为null
③.可为null的字段被索引时，索引的每个节点需要一个额外的字节来标识该字段是否为null
  查询的时候不能用=NULL查询(因为NULL==NULL并不为true) 需要用IS NULL
  查询IS Null时explain显示的使用的是range(氛围扫描)不是ref(等值查找)
④.null不参与所有聚合函数运算 (count/max/min/avg/sum)

3.int(11)

①.中的11不是占用的bit位数，只是限定了交互的宽度，不够左边补0 int(1)和int(11)都是占用了32个bit位
②.只有int类型括号中的数字不是代表存储大小，char(1) bit(1)中的数字都是代表大小

4.decimal

①.decimal(5,2)代表-999.99~999.99
②.decimal只是代表一直存储格式，在实际的运算中还是会转换成double
③.建议使用bigint代替decimal，避免浮点计算不精确和decimal精确计算代价高的问题

5.varchar

①.用于存储可变长的字符串
②.如果一个行占用的空间增长，而且页内没有足够的空间时，则需要分裂页
③.适用于最大长度比平均长度大很多/列更新很少(即不会产生碎片)

6.char

①.存储定长的字符串，不会产生碎片
②.适合存储很短的或者所有长度都接近的字符串
③.char(1)如果用varchar来存储时需要两个字节，另外一个字节用来记录长度

7.binary/varbinary

①.用于存储二进制字符串，二进制字符串存储的是字节码而不是字符
②.binary(n)填充用的\0，char(n)填充是用空格
  在检索时char(n)做比较时会去掉填充空格 binary(n)不会去掉填充\0(即有填充和没填充是两个二进制字符)
  所以如果时binary(8)类型 需要用SELECT * FROM t WHERE col = ‘abc\0\0\0\0\0’;
③.用二进制字符比较的优势有 /大小写敏感 /每次比较的单位更多，字符每次只比较一个字节

8.blob/text

①.用于存储很大的数据字符串类型，分别采用二进制和字符方式存储
②.当blob/text过大时，innodb会使用指针指向外部存储区域来进行存储实际数据
③.不能用blob/text列全长度的字符串进行索引，可以使用前缀索引或者计算哈希做索引

9.enum

①.使用枚举来代替字符串类型，在表的列中存储的是整数，在表的.frm文件中保存 {数字:字符} 的映射关系查找表
②.如果要添加枚举只需在末尾添加元素且mysql5.1版本以上，否则只能alter table

10.时间

①.使用datetime 存在时差/存储空间更大/可读性好
②.使用时间戳 只能到2038-01-19/可读性差
③.推荐使用时间戳，因为有更高的空间效率
④.可以使用bigint存储微妙级别的时间戳

11.bit

①.mysql把bit当成字符串类型，而不是数字类型

SELECT * FROM t WHERE b = 170;          # ❌ 不一定正确
SELECT * FROM t WHERE b = b'10101010';  # ✅ 推荐
SELECT * FROM t WHERE b + 0 = 170;      # ✅ 可行: 强转为数字再比较

12.范式

①.范式，每个数据只会出现1次非冗余的，需要更新的数据少所以快/缺点是通常需要关联查找(加重了对索引的依赖)
②.反范式，信息是冗余的，优缺点和范式相反
③.实际使用是混用的

13.缓存/汇总表

①.主表用innoDB，用MyISAM做缓存表的引擎可以得到更小的索引占用空间，而且可以全文收索
②.重建MyISAM缓存表可以使用影子表来实现，在重建完成以后原子的重命名影子表和原表，这样后续的请求就打到新数据上了

14.计数表

①.加100行数据提高并发
②.加时间主键可以周期性计数，避免表太大周期性根据时间合并历史数据

5.索引

1.数据库引擎MyISAM/innoDB

①.MyISAM可以没有主键索引，所有的b+tree最后都是对应的行物理地址
②.innoDB中则必须有主键(主键索引都是聚簇索引)，所有行数据都挂在主键索引的b+tree的叶子上，每个叶子页上有个指针指向下一个叶子页
  二级索引的叶子上是主键，所以主键不应用太大的数据结构，防止二级索引过大，查找是通过二级索引找到主键再去主键索引上找行数据(回表)(如果索引覆盖了就不需要回表)

2.聚簇索引

①.概念: 聚簇索引是一种数据存储方式，b+树是聚簇索引的一种实现方式(大多数的数据库系统都用b+树来实现聚簇索引)
  还可以用LSM Tree/跳表实现
②.优点(都体现在主键读上)
  +最大限度的减少了io的调用次数
  +索引和数据在同一个b+tree上数据访问更快
③.缺点(都体现在写上)
  /当新行需要插入到某个已满的页时会导致页分裂操作
  /插入速度严重依赖于插入顺序(需要尽可能的使用单调递增的聚簇键来插入新行)
  /二级索引的叶子节点包含了主键，导致二级索引变大
  /二级索引访问需要二次主键索引查找(回表)
④.如果表没有什么数据需要聚集，可以定义一个代理键作为主键
⑤.主键顺序插入导致数据总是插入最右边的叶子页，成为热点页(产生页的latch锁竞争)
  主键为AUTO_INCREMENT 分发id时会导致自增锁竞争，自增锁竞争可以通过更改innodb_autoinc_lock_mode配置解决

3.b-tree/b+tree

①.b-tree在非叶子节点上存有数据，所以节点上的key就更少，树的高度就更高
②.b+tree只在叶子节点上存有数据，所以树的高度就更低，io次数就更少
③.一个节点在一个页面上，一个页面的大小是16kB
④.容量:如果一行数据大小1k，一个叶子上的数据是16行
  非叶子节点上挂了16kB/(8B指针+8B假设主键位bigint)=1K的子叶子，高度为3的b+tree树上就挂了(1K^2)*16≈1600万行的数据 (注:1B=8bit)
  理解这里的关键点是父节点的每一个数据的指针都是指向子节点在磁盘中的位置(即页码)
  b+数查找一行的方式是先把索引的父节点加载到内存里面，去对比数据(可能是用二分法)找到子节点在磁盘中的位置，以此类推 (也有可能节点在内存里面有缓存)
  因为磁盘的速度会远慢于内存的速度，所以时间复杂度基本上就是树的高度决定的
⑤.b+tree适用于全值键、键值范围、键值前缀查找

4.hast索引

①.先通过索引字段计算出槽(即hash值)，通过槽找到行指针，最后通过行指针找到实际数据
②.mysql只有memory引擎支持哈希索引
③.哈希索引包含了哈希值和行指针
④.哈希索引无法应用于排序，范围查询，前缀查询，索引覆盖
⑤.哈希索引ref等值查找的时间复杂度是O(1)

5.自定义的哈希索引

①.意思就是把比较长的字符计算出他的crc32然后用这个值作为字段创建b+tree索引，在查找是先计算出crc32在使用索引查找
②.需要在表中维护哈希值，可以通过触发器来实现 (触发器即有某个操作时会自动触发对应的操作)
③.可以用FNV64()作为哈希函数，减少哈希冲突

6.前缀索引

①.对应blob、text或者很长的varchar有必要选择前缀索引
②.选择性: 不重复的索引值计数(也称基数)/数据表的总行数，选择性的倒数表示一个索引值平均对应多少行数据，唯一索引的选择性是1 ③.诀窍在于需要选择足够长的前缀来保证较高的选择性，同时又不能太长(节省空间)
④.如果数据分布不均匀，就会出现某个特殊的索引值对应很多行数据，出现查询很慢的情况

7.联合索引

①.如果不考虑排序和分组时，将选择性最高的列放在前面通常时很好的
②.也需要通过查询频率来调整索引列的顺序
③.选择性是一个数据分布平均的值，数据分布不均的特殊情况下的可能会需要使用其他顺序的联合索引

8.全文索引

9.空间数据索引

10.分形树索引

11.如何发挥索引的优势

①.索引的优点，减少了服务器需要扫描的数据量，避免排序和临时表，将随机io变成顺序io(这一点对于机械硬盘性能影响是很大的)
②.独立的列，是指查找的字段不能是表达式的一部分，例如where id+1=5 (这样写mysql是先计算id+1的值在和5比较 走的是全表扫描)
③.在使用5.0版本之前使用or需要改成union才会同时使用两个单列索引进行扫描然后将结果合并，否则会扫全表

# mysql在5.0之前不能使用多个单列索引
# 假设一张表users有两个索引name email
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com'; #  sql在5.0之前会使用一个索引+全表扫描(排查前面索引找到的结果)
SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE email = 'alice@example.com';  #会生成一个临时表把使用两个索引查到的结果插入到表里面 最后去重，如果想避免适用临时表可以使用UNION ALL(没有去重操作)

④.索引覆盖
  指的是索引包含了所有需要查询的字段信息，无需回表
  可以通过延迟关联优化，使得第一阶段的sql语句里面使用索引覆盖找到所有主键，然后一次性回表找所有行，这种优化的效果与第一个sql语句能 过滤掉信息占比 成正比(173页) (todo:)
⑤.使用索引扫描排序
  索引扫描排序是先找到最小值，然后按照叶子节点形成的链表去取数据直到最大值
  需要索引覆盖，如果不能索引覆盖会一条一条的去回表查一次对应行
  这就相当于n次的b+上查找+随机io时间复杂度O(nlgn)会比全表O(n)扫描还慢O(n)
⑥.冗余索引
  为了应对不同的情况而加的多个索引
  新增索引会导致增删改的操作速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候
  缓存被索引页占满后，更多的数据页/索引页只能频繁从磁盘读取，造成I/O增加、性能下降
  可以查询每个索引使用的频率

12.维护

①.查看选择性: show index from table; //查看索引的基数(列去重后的行数) 命令会导致统计信息的更新可能会导致服务器额外的压力
  通过参数innodb_stats_on_metadata可以关闭更新，如果关闭数据统计后分布发生很大变化，会导致一些性能很差的执行计划
②.行碎片: 数据行被存储在多个地方的多个片段中，导致从索引中访问一行也需要多次io
③.行间碎片: 逻辑上的顺序页或者行在磁盘上不是顺序存储的(大量的增删改操作导致)
  对全表扫描和聚簇索引扫描有很大的影响，因为这些操作原本只需要磁盘探头的顺序移动
④.剩余空间碎片: 指页面内有大量的剩余空间，会导致读取大量不需要的数据
⑤.alter table engine=cur_engine; //修改表引擎为当前引擎 可以使得表整理碎片，需要考虑的是表数据是否达到了稳定的状态，否则还会产生大量碎片

6.查询

1.查询的时间消耗

①.时间消耗

②.56两步是嵌套发生的
③.实操
  其实这个意义并不大，因为几乎99%的时间消耗都发生在executing执行阶段，而需要优化的时间就在这里
  所以sql优化更多的是使用EXPLAIN+慢日志+performance_schema

SHOW VARIABLES LIKE 'profiling';  # 查看当前会话的sql耗时统计有没有被打开
SET profiling = 1;                # 打开耗时统计
SHOW PROFILES;                    # 查看耗时已经被统计的sql
show profile cpu for query 2;     # 查看上面的第二条sql在不同阶段的时间消耗

2.优化数据访问

①.可以优化的项目
  limit限制返回数据的行数
  多表关联时只返回需要的列
  select取出需要的行(取出全部行可以简化开发)
  避免查询相同的数据，比如说很多地方需要用户头像的url，可以先全部查出创建一个缓存
②.慢日志

vim /etc/my.cnf
slow_query_log=1   # 慢日志开关
long_query_time=2  # >2s记录

SHOW VARIABLES LIKE 'slow_query_log_file';  # mysql查询慢日志路径

  Query_time:响应时间
  Lock_time:锁等待时间(包含在Query_time)
  Rows_examined:扫描行数
  Rows_sent:返回行数
③.可以计算一个sql需要多少次顺序和随机io*具体硬件一次io的时间可以估算出sql需要执行的时间，看下和实际时间是否相近
④.where的三种方式 (EXPLAIN的Extra输出)
  where 使用where来过滤不匹配数据，引擎完成
  using index使用索引过滤，在服务层完成
  using where从表中放回数据，然后在服务层过滤
⑤.切分查询，如果一个大的语句一次性完成的话，会需要锁住很多的数据、占满整个事务日志、耗尽系统资源、
  阻塞很多小但重要的查询，所以需要切分查询，将压力分散到一个比较长的时间上
⑥.查缓存，缓存时通过对sql执行一个大小写敏感的hash实现的

3.查询mysql连接的状态

①.show full processlist; //
②.sleep
③.query正在执行查询或者在将结果发送给cli
④.locked在等存储引擎级别的锁
⑤.analyzing and statistics收集存储引擎的统计信息，生成查询的执行计划
⑥.copying to tmp table [on disk]线程正在执行查询，并且将其结果都复制到临时表，一般这种状态是在做group/文件排序/union，[on disk]表示将内存临时表放入磁盘
⑦.sorting result对结果集进行排序 (在内存中)
⑧.sending data线程在多个状态之间传数据，或者在向cli发送数据