mysql总结

mysql要点

数据流向图

1.每个请求需要一个线程处理，线程从线程池里获取。
2.查询缓存有额外开销，8.0开始已不推荐使用。
3.查询优化会生成一个查询计划explain,可以自己通过explain来查看使用计划。
4.常用mysql存储引擎innodb,myisam,memory。可以为不同的表设置不同的存储引擎。默认引擎是innodb。innodb支持事务，myisam不支持事务。

缓冲池

innodb

1.innodb默认页大小是16k。
2.聚簇索引（主键索引），索引和数据放在一起。二级索引，索引和对应主键放在一起。联合索引，按左到右的顺序查询。
3.一个页面最少存两条记录。
4.redo日志，保证事务的提交，保证了事务的原子性和持久性。
5.undo日志，保证事务的回滚，保证了事务的一致性。
6.mvcc
7.加锁语句
8.s锁（共享锁），x锁（排他锁）
LOCK TABLES t READ ： InnoDB 存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ： InnoDB 存储引擎会对表 t 加表级别的 X锁 。
SELECT … LOCK IN SHARE MODE 加共享锁，有锁定范围行单位。
SELECT/delete/update for update 加排他锁，锁定范围行单位。
9.AUTO_INCREMENT会自动加auto-inc锁
10.插入缓冲（针对的是非唯一的二级索引的优化，缓冲池中没有不直接插入，而是插入到insert buffer中定时合并，changebuffer是升级版，可配置），两次写（一页16k，操作系统写一页是4k，解决部分写成功的异常，checksum校验不通过，做不到幂等性），自适应哈希（可以只对热点数据进行哈希，提高性能，默认开启），异步io(innodb_use_native_aio一般都要开启，提高性能),刷新邻接页（innodb_flush_neighbors，ssd就不要开启了），预读。
11.checkpoint 宕机后只需恢复checkpoint之后的事务。lsn标记位置。
12.innodb_io_capacity表示磁盘io吞吐量，可以根据磁盘io能力调高，如果换成ssd可以更高一些，可以提高性能。刷新脏页自适应调整的参数也可以配置开启，undo页每次回收数量也可以配置。
13.二进制日志和redolog的区别,一个是mysql上层，一个是innodb底层，一个是sql语句逻辑修改，一个是每个页的物理修改，重做日志保证512字节一个块，和磁盘一致才不需要双写，理论上4k的页面也有部分写入的风险。

myisam

1.myisam将数据和索引分开保存。主键索引和二级索引其实都是二级索引。
2.全文索引innodb和myisam都支持。
3.表锁
4.不支持事务
5.不支持外键
6.保存行数

性能优化

1.调整缓冲池大小，innodb_buffer_pool_size越大越好，可以设置为可用内存的百分70-80。
2.max_connection一般1000个也就够了，如果还有性能问题或者出现too many connection排查其他地方,可以尝试修改wait_timeout,连接闲置后的关闭时间。
3.手动预热innodb_buffer_Pool缓冲池。
4.子查询需要建立临时表，速度很慢，可以用join代替。
5.数据库服务器可以考虑开启swap，要不然内存满了直接卡死，或者如果有oomkiller会误杀进程。
6.mysql一般使用binglog做增量备份，主从模式复制也是通过binglog来同步。
7.mysql默认编码是latin1,最长字符就是1个字节，utf-8是1-3字节，字段长度当然是选越短越好。
8.innode_lock_wait_timeout可设置锁超时释放的时间。
9.innodb_flush_log_at_trx_commit设置redolog提交刷盘的间隔，0表示每隔一秒写盘，1表示立即刷盘，2表示操作系统调度每隔1秒更新redolog.
10.innodb_support_xa开启可以保证binlog和redolog不一致情况下的事务保证，binlog先写入再写入redolog，有可能redolog还没写入就断电的情况。
11.可配置慢查询日志开启。
12.注意innodeb表里有没有带计数器，没有的话不要使用select count(*) from table
13.where代替having，因为having会查询所有后过滤。
14.explain 可以执行update和select。
15.内存够用的话可以配置每个线程的各种内存配置，per_thread_buffers和全局内存配置global_buffers，读取操作频繁还可以开启query_cache.
16.innode_page_size可以配置，默认16k是比较优的大小。写频繁的使用16k，读频繁可以考虑8k的
17.读写分离，可以通过客户端来实现，也可以通过代理实现，代理可以通过4层代理lvs或者haproxy，当然还可以直接实现应用层代理，但是需要注意数据一致性问题，一般情况都不是强一致的。
18.innodb_read_io_threads和innodb_write_io_threads可配置读写线程数量。innodb_purge_threads可开启用来回收undo页面，关闭就交由master_thread处理。脏页刷新线程也可以独立配置，这样主线程压力更小了，只需要保证异常刷盘保证事务的一些操作。
19.意向锁的用途：表级别的锁，意向锁之间不互斥，可以方便判断是否需要等待，否则如果有行锁需要扫描所有行。
20.三种行锁，记录锁锁住行，间隙锁，锁住范围不包括行本身，next-key锁，锁范围包含行本身，主键索引如果是复合索引，使用了range搜索那可能会用到next-key锁，正常单键不会。

explain详解：

explain参数详解

1 id select_id 每个select子句的标识id
2 select_type None select语句的类型
3 table table_name 当前表名
4 partitions partitions 匹配的分区
5 type access_type 当前表内访问方式 join type
6 possible_keys possible_keys 可能使用到的索引
7 key key 经过优化器评估最终使用的索引
8 key_len key_length 使用到的索引长度
9 ref ref 引用到的上一个表的列
10 rows rows rows_examined，要得到最终记录索要扫描经过的记录数
11 filtered filtered 按表条件过滤行的百分比
12 Extra None 额外的信息说明

id

SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序。

select_type

select_type语句类型有以下几种:
1 SIMPLE None 简单的SELECT语句（不包括UNION操作或子查询操作）
2 PRIMARY None PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
3 UNION None UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）
4 DEPENDENT UNION dependent(true) DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
5 UNIOIN RESULT union_result UNION RESULT：UNION操作的结果，id值通常为NULL
6 SUBQUERY None SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）
7 DEPENDENT SUBQUERY dependent(true) DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
8 DERIVED None DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
9 MATERIALIZED materialized_form_subquery MATERIALIZED：被物化的子查询
10 UNCACHEABLE SUBQUERY cacheable(false) UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
11 UNCACHEABLE UNION cacheable(false) UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table

当前表名

partitions

匹配的分区

type

当前表内访问方式

性能排序：
1 system 表中只有一行
2 const 单表中最多有一个匹配行，primary key 或者 unique index的检索
3 eq_ref 多表连接中被驱动表的连接列上有primary key或者unique index的检索
4 ref 与eq_ref类似，但不是使用primary key或者unique index，而是普通索引。也可以是单表上non-unique索引检索
5 fulltext 使用FULLTEXT索引执行连接
6 ref_or_null 与ref类似，区别在于条件中包含对NULL的查询
7 index_merge 索引合并优化，利用一个表里的N个索引查询,key_len表示这些索引键的和最长长度。
8 unique_subquery in的后面是一个查询primary key\unique字段的子查询
9 index_subquery in的后面是一个查询普通index字段的子查询
10 range 单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref列会变为null
11 index 等于ALL。它有两种情况：(1)覆盖索引 (2)用索引的顺序做一个全表扫描。
12 all 全表扫描

possible_keys

提示使用哪个索引会在该表中找到行

key

MYSQL使用的索引

key_len

key_len 说明
key_len: 4 // INT NOT NULL
key_len: 5 // INT DEFAULT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NOT NULL CHARSET=utf8

key_len大小的计算规则:
a、一般地，key_len 等于索引列类型字节长度，例如int类型为4-bytes，bigint为8-bytes；
b、如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90-bytes；
c、若该列类型定义时允许NULL，其key_len还需要再加 1-bytes；
d、若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2-bytes;

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows_examined，要得到最终记录索要扫描经过的记录数，这个数越小越好。

filterrd

按表条件过滤行的百分比

Extra

Extra是对执行计划的额外说明，包含重要信息。

1 const row not found 所要查询的表为空
2 Distinct mysql 正在查询distinct值，因此当它每查到一个distinct值之后就会停止当前组的搜索，去查询下一个值
3 Impossible WHERE where条件总为false，表里没有满足条件的记录
4 Impossible WHERE noticed after reading const tables 在优化器评估了const表之后，发现where条件均不满足
5 no matching row in const table 当前join的表为const表，不能匹配
6 Not exists 优化器发现内表记录不可能满足where条件
7 Select tables optimized away 在没有group by子句时，对于MyISAM的select count(*)操作，或者当对于min(),max()的操作可以利用索引优化，优化器发现只会返回一行。
8 Using filesort 使用filesort来进行order by操作
9 Using index 覆盖索引
10 Using index for group-by 对于group by列或者distinct列，可以利用索引检索出数据，而不需要去表里查数据、分组、排序、去重等等
11 Using join buffer 之前的表连接在nested loop之后放进join buffer，再来和本表进行join。适用于本表的访问type为range，index或all
12 Using sort_union,using union,using intersect index_merge的三种情况
13 Using temporary 使用了临时表来存储中间结果集，适用于group by，distinct，或order by列为不同表的列。
14 Using where 在存储引擎层检索出记录后，在server利用where条件进行过滤，并返回给客户端

其他

1.覆盖索引，联合索引里就可以读出需要的所有值。
2.内连接，左连接，右连接，全连接。
3.explain查询计划详解：
4.事务acid:
5.隔离级别：读未提交，读已提交（解决脏读问题），可重复读（解部分解决幻读问题），序列化读；（脏读，不可重复读，丢失更新，幻读）
6.视图（create view … as …，select语句组成的虚拟表），存储函数(create function,可提供参数，有返回值)，存储过程（create procedure可提供参数，可返回多表），触发器（create trigger,可以监控update delete insert语句做出对应执行语句），事件（create event,可指定时间执行）。
7.外键约束
8.字段最长65535字节，行最大长度也是65535。
9.char指定长度，小于这个长度的会自动用空格补充，varchar可变长度。
10.wait-for graph等待图可以直接进行死锁检测回滚。

sharding sphere分片

水平拆分，支持直接库调用和代理服务运行。