Mysql高性能

图片 https://www.jb51.net/article/151100.htm
Mysql 分层https://www.jianshu.com/p/327a36f08d13
https://blog.csdn.net/hanpeiyu1995/article/details/89969030

一、MySql架构
1、逻辑架构（Mysql分为Server层和插件式存储引擎层。不同的存储引擎共享一个Server层）

（1）最上面一层是客户端，不属于Mysql
（2）Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数。所有跨存储引擎的功能都在这一层实现，比如存储过程／触发器／试图等

（3）存储引擎层负责数据的存储和提取。其架构模式是插件式的。最常用的存储引擎是 InnoDB ，它从 MySQL 5.5.5 版本开始成为了默认存储引擎
1.3 事物
ACID 原子性、一致性、隔离性、持久性
1.3.1 隔离级别
未提交读

提交读
可重复读
可串行读
1.3.3 事物日志（记录物理页的修改）
https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html
帮助提高事物的效率，存储引擎在修改表数据的时只需要修改内存拷贝，再把改修改行为记录到持久在硬盘上的事物日志中，而不用每次都将修改的数据本身持久到磁盘。事物日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域的顺序IO，而不像随机IO需要在磁盘上多个地方移动磁头，所以要快的多。
1.4 多版本控制（MVCC）
MVCC是通过保存数据在某个时间点的快照来实现的。不管事物执行多久，每个事物看到的数据都是一致的。
InnoDB是通过每行记录后保存两个隐藏的列来实现的，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。不是真的时间，而是系统版本号。
保存这2个额外的系统版本号，使大多数读操作都可以不加锁。

1.5.1 InnoDB存储引擎
Mysql默认，被设计用来处理大量的短期事物。高性能和自动崩溃恢复。行级锁

1.5.2 MyISAM存储引擎
Mysql5.1版本及之前版本默认。提供了大量包括全文索引、压缩、空间函数等特性。不支持事物和行级锁（使用表锁），崩溃后无法安全恢复。

四、Schema与数据类型优化
4.1 选择优化的数据类型
A 更小的通常更好
更小的数据类型占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
B 简单就好
通常需要更少的CPU周期，如整形比字符操作代价低。尽量使用MYSQL内建的类型而不是字符串来存储日期和时间。另一个是用整形存储IP地址。
C 尽量避免NULL

Varchar
255个字节内需要额外多1个字节，否则使用2个字节
5.0版本开始会保留末尾空格
Varchar(5)和varchar(200)定义 ’Hello’ 在磁盘上存储空间开销是一样的，但varchar(200)会消耗更多的内存，，因为MYSQL通常分配固定大小的内存块来保存内部值。尤其是使用内存临时表排序或操作时会特别糟糕。利用磁盘临时表排序时也同样糟糕。

Char
会删除所有的末尾空格，适合字段都很接近字段平均值的字段

BLOB和TEXT类型
都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
MYSQL当做一个独立的对象处理。存储引擎在处理时会做特殊处理。
MYSQL进行排序与其他类型是不同的，只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一笑部分字符，则可以减少max_sort_length的配置，或者使用 Order by sustring(column, length)

使用枚举代替字符串类型
MYSQL会根据列表值的数量压缩到一个或者2个字节中，保存为整数，并且在表的 .frm文件中保存 数字–字符串 的映射关系。
排序是按照内部存储的整数而不是字符串进行排序。
数据变化时会比较麻烦

DATETIME和TIMESTEMP
IPV4地址最好不要使用varchar(15)存储，而是用无符号整数存储，MYSQL提供了INET_ATON()和IENT_NTON()函数在这2种表示方法的转换

4.2 Mysql schema设计陷阱
太多的列。会严重影响服务器的性能。
太多的关联，单个查询最好在12个表以内做关联。

4.3 范式和反范式

4.4
4.4…1缓存表和汇总表
每小时统计一次信息，如果需要100准确，可以统计当前小时数量+前面已统计的值。
4.4.3 计数器表
并发量大时，可以将某一计数器拆成随机100行计数，定时统计汇总。

4.5 加快ALTER TABLE操作的速度
操作对大表来说是个大问题。MYSQL执行大部分修改表结构操作的方法是用新的表结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能花费很长时间，如果内存不足而表又很大，而且很多索引的情况下尤其如此。

大部分ALTER BATLE操作将导致MYSQL服务终端。有2种技巧：一种是在不提供服务机器上执行，然后和提供服务的主库进行切换；一种是“影子拷贝”，用要求的表结构创建一张和原表无关的新表，然后通过重命名和删表操作交换2张表。

下面的语句会引起表重建，很慢
ALTER TABLE a modify colmn name TINYINT(3) not null default 5;

下面的语句不会引起表重建，很快

ALTER TABLE a ALTER colmn name set default 5;

五、创建高性能的索引
5.1 基础
在MYSQL中，存储引擎先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
5.1.1 索引的类型
索引是在存储引擎层而不是在服务器层实现的。
（1）B-Tree索引， InnoDB使用的是B+Tree索引，根据主数据主键引用被索引的行
（2）哈希索引，只有Memory引擎支持。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当某些索引值使用的非常频繁时，会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为，用户无法控制或者配置，但可以关闭改功能。
创建自定义哈希索引，如地址字段，可以使用CRC32函数将其转成整数冗余一个字段，查询时先根据该字段索引，在匹配地址值。如果数据量很大，哈希冲突会比较严重，可以实现一个64位函数返回整数，切记务使用SHA1()和MD5()函数生成很长的字符串，这样对性能影响很大。
（3）全文索引 InnoDb引擎5.6版本开始支持

5.2 索引的优点
1、索引大大减少了服务器需要扫描的数据量。
2、帮助服务器避免排序和临时表
3、可以将随机I/O变为顺序I/O
三星系统：将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找的排序顺序一致则获得二星；如果索引中的列包含了查询中需要的所有列则获得三星。

小表全表扫描最高效，中到大型表索引就非常有效。特大型表建立和使用索引的代价随之增长，需要使用其他技术（如分区、分表）

5.3 高性能的索引策略
5.3.1 索引列前面必须只有列，不允许有函数和表达式
5.3.2 前缀索引和索引选择性
需要索引很长的字符列，会让索引变的大且慢。一个策略是模拟哈希索引。或者使用对字段列前面部分字符索引。 如何找到最佳长度方法：（1）统计每个前缀出现的次数 （2）统计前缀总数占所有数据总数比重。
5.3.3 多列索引
5.3.4 选择合适的索引列顺序
建立a 和b 2个字段的索引，哪个放在前面。除非业务有很强的要求，否则查询到数据少的字段放前面，尽量过滤掉更多的数据。
Select count(distinct a)/count() a_per, count(distinct b)/count() b_per, count(*) from table
5.3.5 聚族索引
聚族索引并不是一种单独的索引类型，而是一种数据存储方式
数据行和相邻的键值紧凑的存储在一起，无法同时吧数据行存放在2个不同的地方，所以一个表只能有一个聚族索引。
一般主键是聚族索引类，否则会选取唯一且不为空的列为索引列，InnoDB会隐式顶一个主键。

5.3.6 覆盖索引
覆盖索引包含所有需要查询的字段值，查询只需要扫描索引而无需会标查询，能够极大的提高性能。 Extra: using index表示使用了覆盖索引
Using where 表示Mysql服务器将存储引擎返回行以后在进行where条件过滤，即已从存储引擎层加载数据到服务器层
5.3.7 使用索引扫描来做排序
只有当索引列的顺序和order by 子句的顺序完全一致，并且所有列的排序顺序都一样时，mysql才能够使用索引来对结果排序。（第一列条件为常量，则可以直接使用第二列排序）
5.3.9 冗余和重复索引
重复索引只字段顺序，类型完全一样
冗余索引 (A B)、 (A)和 (A B ID) 属于冗余索引，只要一个（A B）索引就可以了
Where A=5 order by ID 此时只有索引(A) 会使用索引排序，请注意。
5.3.11 索引与锁
索引可以让查询锁定更少的行。减少锁的开销和增加并发性

5.4.2 避免多个范围条件
对范围条件查询，MYSQL无法再使用范围列后的其他索引列了。 in可以。如果有(a,b)索引
Where a>1 and b>1 该语句做多只使用a字段索引，b字段谁不会走索引。
Where a in(2,3,4) and b>1 该语句a、b都会走索引

5.4.3 优化排序
使用文件排序对大数据量来说是非常耗时的，所以要尽量走覆盖索引排序查出主键，然后使用延迟加载limit后条数的数据

5.5.3 减少索引和数据的碎片
B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差的或者无序的方式存储在磁盘上。
如果索引叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则对范围查询、索引覆盖扫描等操作速度可能会降低很多倍。

表的数据存储也可能碎片化
行碎片（InnoDB不会出现）：数据行被存储为多个地方的多个片段中。即使从索引中访问一行记录也会导致性能下降
行间碎片：逻辑上顺序的页，或者在磁盘上不是顺序存储的。多全盘扫描和聚族索引的操作有很大影响
剩余空间碎片：数据页中有大量的空余空间。导致服务器读取大量不需要的数据，从而造成让费。

可以使用OPTIMIZE TABLE或者导入再导出的方式重新整理数据。或者使用 alter table table_name ENGING=不做任何操作来重建表
6.3 重构查询的方法
6.3.1 一个复杂的查询还是多个简单的查询
6.3.2 切分查询
如每次只查询1000条数据，或者批量增删改。而不需要一次性完成，导致占用很长的事物时间
如果一个大的语句一次性完成的话，可能需要一次锁住很多数据，占满整个事物日志、耗尽系统资源、阻塞很多小但重要的查询、同时还会增大MYSQL复制延迟。
6.3.3 分解关联查询
分解成对单个的查询

6.4 查询的基础

查询状态
Show full processlist命令的command列表示线程的当前状态
Sleep：线程正在等待客户端发送新的请求
Query：线程正在执行查询或者正在将结果发送给客户端
Locked：在MYSQL服务层，改线程正在等待表锁（InnoDB没有改状态）
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to temp table [on disk]：正在执行查询，并且将其结果集都复制到一个临时表中。如 group by、union或者文件排序
Sorting result：正在对结果集进行排序
Sending Data：这表示多种情况，可能在多个状态之间传送数据，或者生成结果集，或者向客户端返回数据

6.4.2 查询缓存
通过一个大小写敏感的哈希查找实现的。即使有一个字节不同也不会匹配缓存。

4.6.3 查询优化处理
查询优化器：MYSQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
SQL_NO_CACHE 不使用查询缓存。
IN()中的数据会先排序，然后通过二分查找的方式确定列表中的值是否满足条件，O(log n)的复杂度。Or的复杂度为 O(n) ，所以in的性能会好于or

STRANGHT_JOIN强制按照书写顺序执行表关联

排序优化
当不能使用索引生成排序结果的时候，MYSQL需要自己进行排序，如果数据量小则在内存中进行，数据量大需要使用磁盘。需要排序数据量小于“排序缓冲区”，MYSQL使用内存进行“快速排序”，如果内存不够排序，那么MYSQL会现将数据分块，对每个独立的块使用“快速排序”。并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。
使用的临时存储空间可能会比想象的要大的多。排序时每行数据都会分配一个足够长的定长空间来存放，这个定长空间必须足够长以容纳其中最长字符串。如varchar列需要定义的长度。使用UTF-8字符集，则会为每个字符预留3个字节。

关联查询需要排序：
一、如果order by 的字段都是第一张表，那么MYSQL在关联处理第一张表的时候就进行文件排序。EXPLAIN 命令的Extra字段为 “Using filesort”。
二、先将关联的结果存放在一个临时表中，然后在所有的关联结束后，在进行文件排序，Extra为 “Using temporary; Using filesort”