一个 sql 语句在 MySQL 中的执行流程

MySQL分为Server层 和存储引擎层,为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server层的功能，把真实存取数据的功能划分为存储引擎层的功能

myIsam 和 InnoDB的区别（InnoDB 支持事务，外键，崩溃后恢复(redolog日志), 聚簇索引，InnoDB 行级锁，myIsam 表级锁, 全文索引）

共享锁和排他锁也叫读锁和写锁, 写锁相比于读锁有更高的优先级, 因此一个写锁请求可能会插入到读锁队列的前面

锁的弊端, 加锁需要消耗资源, 锁的各种操作, 包括获得锁, 检查锁是否解除, 释放锁等, 都会增加系统的开销, 如果系统花费大量时间来管理锁, 而不是存取数据, 那么系统的性能可能会受此影响

表锁: 表锁是Mysql中最基本的锁, 并且是开销最小的锁, 它会锁定整张表, 一个用户在对表进行写操作( 插入, 更新, 删除 ) 前需要获得写锁, 这会阻塞其他用户对该表的所有读写操作, 只有在没有写锁的情况下, 其他的用户才能获得读锁, 读锁是互相不阻塞的

行级锁可以最大程度的支持并发处理 ( 同时也带来了最大的锁开销 )

事务是一组原子性的SQL查询, 如果数据库引擎能够成功地对数据库应用该组查询的全部语句, 那么就执行该组查询, 事务内的语句要么全部执行成功, 要么全部执行失败

默认情况下，如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交

redis采用单线程串行方式执行事务,可以避免锁的开销,事务冲突等问题,但是其吞吐量上限是单个CPU核的吞吐量, 不能很好的应用CPU的多核性能,redis也不支持交互式的多语句事务

事务隔离主要是为了处理并发执行事务时的各种临界条件

隔离级别, SQL定义了四种隔离级别, 较低级别的隔离通常可以执行更高的并发

在并发情况下, 事务与事务之间可能相互影响, 造成幻读, 脏读, 脏写, 写倾斜, 读写不一致等问题, 事务的隔离就是用来解决此类问题, 保存事务的ACID性, 但同时事务隔离本身也会有开销, 影响数据库的性能, 可串行化隔离等级能最大程度的保证数据库的读写一致性,但会使事务串行执行,导致数据库的吞吐量被限制在单个CPU核上,无法发挥CPU的多核性能, 降低事务的处理能力,

show table status like 'table_name' 命令查看数据表的相关信息show table status查看数据库所有表信息

show full processlist 命令查看线程级相关信息

show global variables查看MySQL全局配置命令

show engine innodb status查看当前innodb引擎的状态信息

查询mysql系统用户表select * from mysql.user \G

show variables显示mysql配置信息 show variables like %keyword%搜索某个配置可以这样

show status查看mysql当前会话的变量信息, show global status查看全局的变量信息

MyISAM引擎最典型的性能问题是表锁问题, 如果发现所有的查询都长期处于 Locked 状态, 那么表锁可能就是最大的罪魁祸首

MySql可以为整数指定宽度, 例如INT(11), 对大多数应用这是没有意义的 : 它不会限制值的合法范围, 只是规定了MySql的一些交互工具用来显示字符的个数, 对于存储和计算来说, INT(1) 和 INT(20) 是相同的

货币可选用decimal类型来存储

varchar可用来存储可变长度的字段, 它需要1或2个额外字节记录字符串的长度, 由于是可变长度的所以update时可能使得行变得比原来更长, 这导致需要做额外的工作

char用来存储定长的字段, 当存储的数据长度不够时会填充空格以达到指定长度

使用varchar(5)和varchar(200)存储hello的空间开销是一样的, 那么使用更短的列有什么优势呢, 更长的列会消耗更多的内存, 因为MySql通常会分配固定大小的内存来保存内部值

什么是索引, 索引是用于快速找到记录的一种数据结构

聚簇索引并不是一种单独的索引, 而是一种数据存储方式, InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行, 当表有聚簇索引时, 它的数据行实际存放在索引的叶子页中, 一个表只能有一个聚簇索引

聚簇索引的优点, 1: 数据访问更快, 聚簇索引将索引和数据保存在同一个B-Tree中,因此获取数据通常比非聚簇索引快, 2: 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引中, 二级索引访问需要两次索引查找, 因为二级索引叶子节点保存的不是行指针而是行的主键, 这种查找方式通常也叫回表, 如何避免回表可采用覆盖索引的方式

在InnoDB中主键为什么要尽量选用自增的数字而不是uuid, 因为自增数字主键是有序的, 插入数据时可以按顺序插入, 而uuid是无序的插入数据时不能按顺序直接写入磁盘, 还会造成页分裂, 而页分裂会造成行移动和碎片化, 另外二级索引中会存储主键,uuid相比自增主键占用更多的空间, 这会使单个页能存储的记录数变少,对索引进行扫描时造成更多的磁盘I/O

mysql的原则总是希望在一个数据页内存储尽可能多的记录数,减少磁盘I/O

在Linux中MySQL配置文件一般在/etc/my.cnf或者/etc/mysql/my.cnf

查找MySQL配置文件

which mysql
在mysql命令行模式下执行如下命令
--verbose --help |grep -A 1 'Default options'

MySQL并不会像我们一样去操作行数据，而是抽象出来一个一个的数据页概念，每个数据页的大小默认是 16KB, 即使我们只需要访问一个页的一条记录, 那也需要先把整个页的数据加载到内存中, 通常mysql的预读机制还会导致获取目标页的相邻页

InnoDB中页的大小一般为 16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中

MySQL的所有增删改操作都是在 Buffer Pool 中执行的

缓冲池中默认的缓存页大小和在磁盘上默认的页大小是一样的，一般是16KB。

MySQL在执行增删改时首先会定位到这条数据所在的数据页，然后会将数据所在的数据页加载到 Buffer Pool 中 , 然后在缓冲池中进行具体得操作

Buffer Pool 一次只能允许一个线程来操作，因为MySQL 为了保证数据的一致性，操作的时候必须给缓存池加锁，一次只能有一个线程获取到锁。 因为所有操作都是在内存中进行所以效率很高, 但是串行执行会阻塞其它线程， 因此Buffer Pool 是可以有多个的，可以通过 MySQL的配置文件来配置

如果一张表里有很多大字段, 最好是把它们组合起来单独存到一个列里面, 比如说用XML 或者 JSON格式存储, 这让所有大字段共享一个扩展存储空间, 这比每个字段用自己的页要好,

在MySQL的设定中，单行数据最大能存储65535 byte 当列的类型为VARCHAR、 VARBINARY、 BLOB、TEXT时， 当列长度达到768byte后，会将该列的前768byte当作prefix存放在行中，多出来的数据溢出存放到溢出页中，然后通过一个偏移量指针将两者关联起来，这就是行溢出机制。

配置大量内存的原因不是因为可以在内存中保存大量数据, 而是避免磁盘I/O, 因为磁盘I/O比在内存中访问数据要慢得多

快速导出表, 如果报错的话可以执行这个命令 show variables like "secure_file_priv"; 查看数据允许导出的目录

SELECT * INTO OUTFILE '/var/lib/mysql-files/posts.txt'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;

innotop是mysql监控工具, 可以帮助查看mysql当前的状态信息

MySQL数据表中为什么要避免字段为空, 因为可为空的列除了本身数据占用的空间外, 还需要额外的空间来记录当前可为空的字段是否为空, 数据行用二进制位来标记每个可为空的字段,为空用1表示,不为空用0表示

MySQL中被删除的记录并不会立即从磁盘中清除, 只是在行记录上打上了一个删除标记 delete_mask ,类似于逻辑删除这种处理方式。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉

字符串索引在B+树中的排序规则, 不是通过对字符串进行哈希运算后的哈希值进行排序的, 而是按字符的顺序逐个比较字符的大小进行排序的, 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小, 如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小, 以此类推, 这样做使得 SELECT * FROM person_info WHERE name LIKE 'As%'这样的查询也能运用到索引, 但只给出后缀或者中间的某个字符串，是不能应用到索引的

MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句 ,如果我们并不想为包含GROUP BY子句的查询进行排序，需要我们显式的写上ORDER BY NULL

redo log日志中有一个checkpointer(检查点)指针指向我们需要恢复的点,恢复时只需要将mysql关机前没有刷新到磁盘上的脏页复制到内存中就行了,不需要直接修改磁盘上的数据,刷新到磁盘的操作由专门刷新脏页的独立线程来完成

适当的索引可以加速读取查询, 但索引都会减慢写入速度 ( 因为每次写入数据时, 需要更新索引 )

binlog日志的三种复制方式