mysql笔记上（mysql技术内幕）
1、命令汇总
show ENGINES
-- 查看支持的存储引擎
show tables;
-- 查看所有表和视图
show VARIABLES like 'innodb_version'
-- 查看innodb引擎版本
show VARIABLES like 'innodb_%io_threads%'
-- 查看 io线程个数
show engine innodb status
-- 查看引擎状态
show VARIABLES like 'innodb_buffer_pool_size'
-- 查看缓冲池大小
show VARIABLES like 'innodb_buffer_pool_instances'
-- 查看缓冲池的个数
show variables like 'log_error'
-- 查看错误日志地址
show variables like 'long_query_time'
-- 慢查询时间设置
show variables like 'slow_query%'
-- 查看慢查询日志是否开启
-- 查看慢查询日志文件
show variables like 'general_log%'
-- 查看查询日志
show variables like '%binlog%'
-- 查看binlog日志相关信息
show variables like '%binlog_format%'
-- 查看binlog日志格式
show variables like 'log_bin'
-- 查看binlog是否开启
show master logs;
-- 查看所有binlog日志列表
-- my.cnf 中 datadir的位置为binlog日志所在位置
show table status like 'user'
-- 查看表行格式 Row_format
select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='test' and table_name='user'
-- 查看约束
show variables like '%partition%'
select * from information_schema.PARTITIONS where table_schema='test' and table_name='user'
explain PARTITIONS select * from sm_user__user_info where id >1000 and id<10000
-- 查看分区信息
2、知识点
mysql单表数据量超过500w或者文件大小大于2G（要结合机器配置等因素） 主要原因在于无法将索引放入内存，导致磁盘io增多，查询效率下降，此时需要考虑分库分表
聚簇索引和非聚簇索引的区别:博客
Share nothing和share everything的区别:博客
3、mysql体系结构
Connectors:客户端连接 Connection Pool:连接池 SQL Interface:sql接口 Parser:sql解析器 Optimizer:sql优化器
Cache & Buffers:缓冲池 Management Service :管理服务和工具组件 Pluggable Storage Engines:插件式存储引擎
File system:文件系统
MySQL查询执行路径
客户端发送一条查询给服务器；
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端
4、存储引擎
存储引擎是底层物理结构的实现，是基于表的
Innodb:innodb 使用多版本并发控制MVCC来实现高并发，使用插入缓冲、二次写、预读、自适应hash，文件格式为idb
Myisam:Myisam不支持锁、事务、外键，采用非聚集索引，文件格式为myi和myd
ndb:集群引擎
memeroy:内存引擎，使用hash索引
5、innodb线程模型
innodb采用多线程模型
核心线程master thread，内部由多个循环组成，将缓冲池里的数据异步刷新到磁盘，保证数据一致性
io read分别应对io请求，主要有write、read、insert、buffer、log
purge thread，事务提交后 undo log需要被回收 使用purge thread进行回收
page clean thread，将脏页的刷新操作放在单独的线程内
6、缓冲池
缓冲池用于应对弥补磁盘速度
缓冲池内的数据类型:索引页、数据页、undo页、插入缓冲、自适应hash，数据页和索引页 占比较大的一部分
缓冲池可以配置多个，每个页根据hash值分配到不同的缓冲池内
缓冲池是通过LRU算法进行管理的，默认页的大小16KB，不是最传统的LRU，最新查询的页不是放在最前端，而是放在中间部分
7、checkpoint技术
缩短数据库的恢复时间；缓冲池不够用时，将脏页刷新到磁盘；重做日志不可用时，刷新脏页。
8、关键特性
插入缓冲；自适应hash；两次写；异步io；刷新邻接页
9、mysql文件
参数文件:分为静态参数、动态，动态参数可以通过set global | session进行修改
日志文件
二进制文件binlog:记录数据库所有的更改，用户故障恢复、复制，my.cnf 中 datadir的位置为binlog日志所在位置
错误日志:记录数据库 启动、运行、关闭是遇到的错误
查询日志:记录所有的查询语句
慢查询日志:记录查询的比较慢的sql语句
- 按照时间排的top 5个SQL语句
- mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log
- 按照时间排序且含有'like'的top 5个SQL语句
- mysqldumpslow -s t -t 3 -g "like" /var/lib/mysql/slow-query.log
套接字文件:客户端连接，show variables like 'socket'
pid文件:存放进程号，show variables like 'pid_file'
表结构文件(包括视图):frm文件
10、表结构
索引组织表，判断是否有非空的唯一字段 ，没有自动创建6个字节的
innodb逻辑存储结构：表空间-段-区-页
段：数据段、索引段、回滚段
区：1MB，引擎一次性从磁盘中读取4-5个区，一个区中连续的64个页
页：数据页、undo页、系统页、事务数据页等
行：每个页存放的记录，16KB
Innodb数据页结构 :博客
11、约束
主键约束、唯一性约束、外键约束、非空约束、默认约束
12、分区
range、list、Hash、key