sinat_16493273的博客

mysql使用系统库mysql的user表来存储用户信息。mysql.user表详细的记录了用户名，对应的允许连接的主机信息还有各种全局权限标识位。

mysql数据库备份分为物理备份和逻辑备份两种，物理备份指将数据库data下的数据文件进行直接copy备份，逻辑备份指通过备份数据库的逻辑结构和数据。也就是数据库的对象对应的create语句和数据表的insert语句。这里主要说逻辑备份。

二进制日志文件记录了数据库修改的事件，像表的修改，表数据的变更等。也包含潜在的可能修改数据的语句事件。如一些delete或update最后修改的数据行可能是0，也会被记录在binlog中(和日志格式也有一定关系，非row-based)。除此之外binlog还会记录语句的执行时间信息。

redo log是innodb引擎特有的记录重做日志，用来进行崩溃恢复和事务的持久性。

MySQL 主从复制主要用于实现高可用性和备份。在主从复制中，一个 MySQL 实例（称为主节点）将其数据更改复制到至少一个其他 MySQL 实例（称为从节点）上。主要借助于数据库二进制日志binlog进行数据的复制。主从数据库对应的操作系统、数据库版本要一致。

原因是因为limit m,n不是只取n行数据，而是扫描m+n行，然后将前m行丢弃返回客户端n行。这个也好理解，按相同年龄排序，如果多个人同岁，只取其中几个，选取哪几个这个对于整条语句来说是无序的，只满足年龄排序接口，如果要保证每次查询返回结果相同，最好加上对应唯一值进行排序。如果distinct和limit row_count同时在查询语句中，在获取到满足distinct（唯一行）的row_count行后会立即结束查询。如果order by的列在多行有相同的值，如果使用limit每次返回的行可能不同。

这里有一个参数max_length_for_sort_data，如果查询的数据行记录超过该值，则会采用sort_key, rowid模式，否则会采用sort_key, additional_fields模式。当涉及到大量数据时，对于 ORDER BY 操作，可以考虑为相应的列添加索引，如果不使用索引，mysql会使用filesort来进行排序。多列排序，第一列排序username满足使用索引条件，gender和username排序方向相反，无法使用索引，会进行依次filesort。为什么使用索引会快？

在常规业务开发(非高并发)进行表join操作是非常常见的，在表连接时候尽量使用小表驱动大表，这里的小表是指条件过滤后参加join条件的数据。它只返回两个表中匹配的行，即两个表中具有相同值的列的行。MRR处理过程是在根据索引获取到主键后不是立即进行回表查询，而是先将主键值放入到一个read_rnd_buffer中，然后对其进行排序，最后再有序的去聚簇索引检索行数据。2、将join buffer中的关联列值，批量的发送给引擎在被驱动表上通过MRR以最优的方式进行索引树搜索匹配，获取到对应的主键（rowId）。

前面文章介绍了InnoDB的索引结构是B+树。主键索引树上拥有所有行数据记录，使用索引可以提高数据库查询的性能和效率，加快数数据查询速度。

通过执行计划，你可以了解到 MySQL 是如何选择索引、连接表以及访问行的顺序的。当连接使用索引的所有部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，使用它。表示查询中执行的顺序，id相同从上到下按顺序执行，一般id越大优先级越高。会有三条执行计划信息，第一个查询是：PRIMARY，第二个查询是UNION，最后的结果集是UNION RESULT。1、如果判断可以使用索引进行条件过滤，走对应索引。一个索引可能左值匹配使用了部分，也可能是联合索引使用了其中的一部分。

优化器（Query Optimizer）：对解析后的sql语句不同的执行计划进行评估，选择合适的执行计划来提高数据库执行效率。这个时候一些执行计划的统计及耗时记录是有存储引擎来完成的，优化器会和具体执行引擎进行交互来选择效率高的执行计划。mysql存储引擎是插件式的模式，用户可以方便使用不同的存储引擎，常见存储引擎InnoDB、MyISAM、Memory等。分析器（Query Parser）：一条语句发送到服务端，首先会分析sql语句的语法是否正确，将整个语句解析成不同的部分，供后面执行使用。

前面的文章学习了索引、事务隔离级别和锁。这三者在不同组合下有不同的表现形式。1、这里说的锁侧重于行锁，首先还是列下锁的几种类型记录锁：X,REC_NOT_GAP间隙锁：X,GAP邻键锁：X插入意向锁：INSERT_INTENTION2、准备test表，其中id是主键，a列有唯一索引，b列普通索引，c列无索引。有以下值。

数据库锁是用来在并发时控制不同资源的访问策略。锁的分类可以从不同的角度划分有很多种不同的锁。1、按功能划分锁按功能划分大致分为两种共享锁(Shared Locks)和排它锁(Exclusive Locks)。共享锁也称为S锁、读锁。排它锁也称为写锁，X锁。共享锁允许事务读取一行数据。排它锁允许事务更新或删除一行记录。共享锁多个事务可以同时获得，但是一个事务如果想获取行上的排它锁必须要等待其它事务所有锁释放，包括共享锁和排它锁。2、按控制粒度范围划分。

MVCC（multiversion concurrency control）多版本并发控制的意思。InnoDB是一个多版本存储引擎。会保留多个行记录修改的历史版本来支持事务的并发特性和回滚。MVCC是一种提高事务并发的一种技术，事务的一致性非锁定读（Consistent Nonlocking Reads）就是通过MVCC来实现。一致性非锁定读意思InnoDB使用多版本控制向查询提供数据库在某个时间点的快照数据。查询查看在该时间点之前提交的事务所做的更改。整个过程是无锁的。

事务的目的是保证数据库的数据一致性和完整性和隔离性。在关系型数据库中，事务是基于ACID模型的。在实际应用中，我们通常使用事务来执行一组对数据库的操作，以保证数据的正确性和完整性。这样数据就不会被破坏，结果也不会因软件崩溃和硬件故障等异常情况而扭曲。例如，在银行转账操作中，我们需要保证两个账户之间的转账操作是一个事务，要么同时成功，要么同时失败回滚。

在介绍索引的文章已经知道。InnoDB的表数据被拆分成不同的数据页上，默认一个数据页大小是16kb，分布在聚簇索引的叶子节点上。被挂在B+树上。一条行记录除了要保存每列具体数据值还会有一些标识位信息。另外对于超长数据存储也有特殊处理。那么具体到一行数据多个列数据是怎么组织的呢？InnoDB存储引擎提供4种行格式: REDUNDANT, COMPACT, DYNAMIC和COMPRESSED。

数据库索引用于提高查询性能和数据访问效率。索引可以加速数据的查找和筛选，减少查询的时间复杂度。数据库索引有很多类型，这里不展开也不比较，只介绍最常见一种索引结构B+树索引。mysql中InnoDB引擎默认使用的就是BTREE索引。

一个表有多个列组成。一个表最大允许4096个列，这已经很大了，实际情况单表使用的列远小于该值。每一个列定义有列名、类型和必要的约束组成。列名最好与业务相关有意义，尽量不要使用关键字。列类型：列类型前面有篇文章介绍数据类型，常见有整数型、浮点数、日期和字符类型。可以根据实际数据进行定义。对于字符串类型可以使用CHARACTER SET来指定当前列的字符集，如果没有明确指定则默认使用数据库的默认字符集。常见的字符集有utf8 和 utf8mb4。非空约束。

INSTR()和locate()方法意义相同，只不过两个参数顺序是相反的。hex十六进制转换：hex(n),hex(str)。获取字符串长度使用char_length()函数，length()函数获取字节长度，如果是汉字，字节长度和数据库字符集有关系。substr()、substring()两个函数作用相同。

只是两个表示的时间稍微有些差别，now()表示当前语句开始执行时间点，在一个语句中多次通过now()获取时间是相同的，sysdate()而是当前时点的时间，是一直在增加的。EXTRACT(unit FROM date)其中，unit表示要提取的时间单位，可以是MONTH、DAY、HOUR、MINUTE等，文章最后有unit的可选值列表。前面在一些时间函数中，如EXTRACT，TIMESTAMPDIFF，DATE_ADD中有unit时间单位参数，下面是一些时间单位的值选项说明。分别获取日期和时间部分。

2、所占空间不同，后面的+3个字节是表示可选的小数部分长度，这里datetime占5个字节是从5.6.4版本开始的，以前的版本占8个字节。timestamp是一个时间戳，从1970年到现在的秒数，一直在增加，为什么到2038年就到期了，因为达到一个4字节整数最大值。D可以不指定，默认是0，如decimal(5)就是表示5位整数，同样M也可以省略，默认是10。INT：4 字节，范围为 -2,147,483,648 到 2,147,483,647。默认情况下都是有符号整数，第一位是符号位。