三层体系结构
连接层：
通信协议处理、线程处理、账号认证、安全检查等
SQL层：
权限判断、查询解析、优化器&缓存、查询执行&返回等
存储引擎层：
存储引擎(基于磁盘)，InnoDB,MyISAM,TokuDB,存储引擎API接口，请求日志，错误日志，二进制日志，中级日志
建立连接过程：
等待建立新连接，判断thread cache
判断host权限，判断max_connections
账号密码认证
判断user resource (max_user_connections等)
连接MySQL的方式有多种
最通用：TCP/IP
高效/安全：Unix socket
Name pipe
Share memory
连接层
mysql -h 10.1.1.x -uroot -p"123" -P 3306
Warning:Using a password on the command line interface can be insecure.
ERROR 1129(HY000):Host '10.1.1.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
恶意连接错误次数太多被拒，可以修改max_connect_error大小
ERROR 1045(28000):Access denied for user 'root'@'10.1.1.x' (using password:YES)
密码错误被拒
ERROR 1862(HY000):Your password has expired.To log in you must change it using a client that supports expired passwords
密码过期
关于timeout
wait_timeout，关闭非交互连接(程序端)之间等待的秒数，通过jdbc等程序连接的是非交互会话
interactive_timeout，关闭交互连接(客户端)之间等待的秒数，通过mysql cli客户端连接的是交互会话
SQL层处理流程图
存储层，文件目录结构
5.7.22，8.0.16上用tree命令
配置文件
/etc/my.cnf、/etc/mysql/my.cnf、/usr/local/mysql/etc/my.cnf、~/.my.cnf
数据文件
frm、MYI、MYD、ibd、ibdata*、ib_logfile*
日志文件
error log、general log、binary log、relay log、slow query log
my.cnf文件内容分类
[client] 所有客户端工具(mysql/mysqladmin/mysqldump/mysqlshow等)全局选项
[mysql] mysql客户端的相关选项，账号、密码、socket、字符集、auto-rehash等
[mysqldump] mysqldump相关选项
[mysqld_multi] 不同版本的mysqld并存管理方法
[mysqladmin] mysqladmin相关选项
[mysqld_safe] 5.7起已不再使用
[mysqld] mysql server端相关选项
MySQL8.0之前，不幸误删除.frm文件怎么自救
用mysqlfrm工具恢复
在MySQL运行时，误删除slow query log，怎么办
执行flush logs；或flush slow logs;
删除文件/临时表/临时文件
已删除，但空间未释放的文件，通过lsof可见deleted标记，在tmpdir下
运行状态下，vi打开log文件后保存，也会生成deleted文件
SQL查询中需要用到额外排序、分组时，会生成临时表；若内存放不下，则会变成磁盘临时文件
binlog disk cache在tmpdir下ML开头，也是标记为deleted
InnoDB启动过程中生成的临时文件在tmpdir下以ib开头，同样标记deleted
8.0起，新增选项internal_tmp_mem_storage_engine=TempTable
建议：
创建适当的索引，减少额外排序，分组
适当调大tmp_table_size和max_heap_table_size
减少大事务以及避免binlog cache过大，或适当调大binlog_cache_size
多用短连接，因为链接断开后，一些session级内存(例如binlog cache)会及时释放，减少binlog disk cache机会
内存结构
全局
只分配一次
全局共享
连接/会话
针对每个会话/线程分配
按需动态分配，查询结束后释放
用于处理(缓冲、中转)查询结果
每个会话的缓冲区大小都不一样
多用短连接，因为连接断开后，一些session级内存会及时释放
引擎层：
innodb buffer，innodb log buffer。key buffer，myisam_sort_buffer_size
mysql server层：
query cache，table(def) cache，thread cache，mdl cache
连接/会话层：
net/read/join/sort/bulk insert buffer，tmp/heap table，binlog cache ...
mysqld进程消耗内存估算 = global buffers + all thread buffers
全局分配内存global buffers(类似SGA) = innodb buffer pool + innodb log buffer + key buffer + query cache + table cache + thread cache
会话/线程级分配内存all thread buffers(类似PGA) = max_threads * (read buffer + read rnd buffer + sort buffer + join buffer + tmp table + binlog cache)
两个容易被设置很大的内存选项，都是session级，tmp_table_size不限制MEMORY表最大容量，如果执行SQL产生临时表超过tmp_table_size或max_heap_table_size，则会产生基于磁盘的临时表。这2个选项特别容易分配较大，若有需要，可临时调大，不要修改全局值。怎么确认tmp_table_size、max_heap_table_size选项值设置的太大或太小？默认为16M，一般64M足够
SQL_MODE
ONLY_FULL_GROUP_BY，SELECT中的列也必须是聚合列
STRICT_TRANS_TABLES，限制数据类型，数据长度；写数据失败时，当条SQL会失败
NO_ZERO_IN_DATE，不允许0000-00-00格式的日期时间值
NO_ZERO_DATE，同上
ERROR_FOR_DIVISION_BY_ZERO，不允许除以0
NO_ENGINE_SUBSTITUTION，CREATE、ALTER TABLE时若未指定引擎，则选择默认引擎
从Oracle等其他数据库迁移过来，可以采用TRADITIONAL模式
关于huge page
使用大页是为了提高内存管理效率
RHEL6,OEL6,SLES11,UEK2起默认启用
透明大页可以动态调整，无需重启即可生效
类似innodb data page概念
但启用透明大页可能反而导致MySQL(TokuDB)更容易发生内存泄漏，OOM等问题
查看是否关闭
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag
plugin管理
查看plugin-dir
mysqladmin var | grep plugin_dir
安装plugin
install plugin rpl_semi_sync_master soname 'semisync_master.so';
删除plugin
uninstall plugin rpl_semi_sync_master;
SELECT SQL WHERE 提取过程
Index Key (>=first key AND <= last key)
Index Filter
Table Filter
PFS & SYS Schema 使用
体系结构
选用适当长度的数据类型，特别关注隐式类型转换
必要的话，启用thread pool，调低timeout
关闭query cache，尽量避免适用MyISAM，session级buffer勿过高
虚拟列是个好东西，尤其是结合JSON类型&函数
再用MySQL5.7以前的版本都是在给自己添堵