高性能MYSQL

5.5以前没有线程池

可重复读--指事务1先加了读锁，导致事务2无法update和delete；或者先加了写锁，导致其他事务无法select
幻读--insert没法加锁导致的，InnoDB使用行锁+间隙锁解决

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

 

Memory引擎
每行的长度是固定的，即使指定VARCHAR，会转为CHAR存储

临时表
在执行查询需要临时表保持中间结果，Memory引擎，太大或者含有BLOB或者TEXT就使用MyISAM引擎
连接断开临时表就自动删除

varchar 变长，varchar(10)需要11个字节，varchar(1000)1002个字节，存储长度信息
适合：更新少（意味着不会碎片化），使用了utf8这样的变长编码，最大长度比平均长度大很多

4.1以前是一样的

char 定长 自动删除末尾的空格
适合：所有值都比较接近，例如密码的md5值 经常更新的 非常短的（不用存储长度信息）

ENUM
排序的时候按照建表里的顺序，不是按字母排序。
可以用FIELD，但无法利用索引消除排序

？？建议使用TIMESTAMP,比DATETIME空间效率高

 

ALTER TABLE对大表来说是昂贵的操作

对开发者隐藏复杂性的应用通常不能很好地扩展

不要保存NULL

商品秒杀，要求高的并发更新性能。增加多行数据，使用RAND函数选择一个随机的slot更新

取舍----更快地读，更慢地写

Modify Column导致表重建
Alter Column直接修改.frm文件，有可能在不必要的时候也重建

如果索引很长的字符列，可以加一个列，并作为索引，保存其hash值，或者使用前缀索引（无法Order by和Group by），计算合适的前缀长度，但是只看平均选择性是不够的，数据分布不均匀的时候就很糟糕

选值的前几位建索引就可以
多列索引
选择性高的列放前面

如果不是按照主键的顺序插入（页分裂，占用更多的空间，存储不连续），optimize table可以重新组织表数据

二级索引需要两次索引访问
InnoDB的自适应哈希索引，一次索引访问

InnoDB的二级索引存储的是主键值，缺点是占用空间大，优点是减少行移动或者页分裂时二级索引的维护工作

 

排序
sort_buffer_size 决定是内存排序还是磁盘临时表排序（先将数据分块，对每个块快速排序，结果存放在磁盘上，最后合并），统称文件排序
两路外部排序
多路外部排序：适用于数据量大
每7个分片做一个归并，最终分片数达到15时，做最后一次归并。这两个值都写死在代码中了

max_length_for_sort_data
两次传输排序模式
优点：只需行指针和需要排序的字段，排序缓冲区容纳尽可能多的数据进行排序
缺点：排序后需要回表，大量的随机IO

单次传输排序模式
用空间换时间

关联查询
Using filesort 如果Order by的列来自第一个表，在关联处理前先排好序
Using temporary;Using filesort 将关联后的结果集存到临时表，然后文件排序

查询优化器的提示

DELAYED 将插入的行数据放到缓冲区，空闲时批量写入， 客户端无需等待。LAST_INSERT_ID无法正常获取

STRAIGHT_JOIN 表太多的时候可以减少优化器的搜索空间

SQL_BUFFER_RESULT 服务端缓存，好处是无须客户端消耗太多内存，尽快释放表锁

优化需要三管齐下：不做、少做、快速地做
不做：缓存
快速做：用索引，减少锁

查询缓存作用不大

关注top里的虚拟内存

5%或者2G作为操作系统内存

read-aroud write

当数据可以放在内存或者从磁盘以足够快的速度读取，CPU可能出现瓶颈
磁盘IO瓶颈---工作所需的数据超过内存容量
网络IO瓶颈---大量的查询和低延迟的要求

增加内存是解决随机IO读取的最好方法

顺序 随机
内存读：500W/s 25W/s
磁盘读：50W/s 100/s

缓存命中率高，意味着CPU使用率高

 

EXPLAIN
TYPE列 index:使用索引排序

索引不能覆盖查询所需的全部列，需要回表查询对应的行数据，这是随机IO,比顺序全表扫描慢

Mysql子查询很糟糕，WHERE条件中的IN里的子查询，使用关联查询或者IN（ID1,ID2）

LSM树

page默认16K，至少存储两条记录，BLOB Page超过768字节的指向行溢出页,compress和dynamic的格式采用完全溢出

varchar 不能超过255 2KB

Group by 优化
走索引
默认会导致排序，使用Order by Null 不进行排序

 

 

 

 

1).Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
2).Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave
的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；
3).Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的
bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；
4).Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

修改表结构，一般创建新的空表，从旧表查出所有数据插入新表，删除旧表，很耗时
只修改.fm文件，不能保证准确性，谨慎
删除索引，再导入数据，重建索引

 

乐观锁

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

 

转载于:https://www.cnblogs.com/jasonChung/p/6911110.html