MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在 Java 企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

 MySQL 5.7 版本权限表介绍如下。

• user。
   user 表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在 user 表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了 DELETE 权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。

• db。
   db 表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。

• tables_priv。
   tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。

• columns_priv。
   columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。

• proces_priv。
   columns_priv表指定存储过程权限。这里代表允许使用某个存储过程的权限。

• proxies_priv。
   利用 MySQL proxies_priv（模拟角色）实现类似用户组管理。角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。

 详细参考：一文看尽 MySQL 用户权限管理，真香！open in new window

 有三种格式，statement、row 和 mixed。

• statement 模式下，每一条会修改数据的 SQL 都会记录在 binlog 中。不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。由于 SQL 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row 级别下，不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如 alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用 statement 记录，当无法使用 statement 的时候使用 row。

 此外，新版的 MySQL 中对 row 级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

 主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的 ID 列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

 参考理解如下。

• MySQL 页完全指南——浅入深出页的原理open in new window。
• MySQL —— 数据页与 B+ 树索引open in new window。
• 行溢出问题open in new window。

 MySQL 锁按锁的粒度分，分为以下三类，各个类别下有不同的子类。

• 全局锁
  • 全局锁，锁主库时，不能更新；锁从库时，会主从延迟；当然备份也可以通过参数指定不加锁。
• 表级锁
  • 表锁
    • read
    • write
  • 元数据锁(meta data lock)
    • share_read_only
    • share_no_read_write
    • shared_read
    • shared_write
    • exclusive(与其他的 MDL 都互斥)
  • 意向锁: 意向锁之间不会互斥
    • 意向共享锁(IS)
    • 意向排他所(IX)
• 行级锁
  • 行锁
    • 共享锁(S)
    • 排他锁(X)
  • 间隙锁(Gap Lock)
  • 临键锁(Next-Key Lock)

 从锁的类别上来讲，有共享锁和排他锁。

• 共享锁：又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
• 排他锁：又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

 详细可参考 深入了解 MySQL 锁的机制open in new window。

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data 
from performance_schema.data_locks;

create databse mytest;
CREATE TABLE `city` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO mytest.city (name,age) VALUES
	 ('长沙3',18),
	 ('湖南4',30),
	 ('上海',22),
	 ('深圳',31);

begin;
select * from mytest.city c where id >= 3 lock in share mode;

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data 
from performance_schema.data_locks;

"object_schema","object_name","index_name","lock_type","lock_mode","lock_data"
mytest,city,,TABLE,IS,
mytest,city,PRIMARY,RECORD,"S,REC_NOT_GAP","3"
mytest,city,PRIMARY,RECORD,S,supremum pseudo-record
mytest,city,PRIMARY,RECORD,S,"4"

• 整数类型，包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节整数。任何整数类型都可以加上 UNSIGNED 属性，表示数据是无符号的，即非负整数。长度：整数类型可以被指定长度，例如：INT(11) 表示长度为 11 的 INT 类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和 UNSIGNED ZEROFILL 属性配合使用才有意义。例子：假定类型设定为 INT(5)，属性为 UNSIGNED ZEROFILL，如果用户插入的数据为 12 的话，那么数据库实际存储数据为 00012。
• 浮点类型，包括 FLOAT、DOUBLE、DECIMAL。DECIMAL 可以用于存储比 BIGINT 还大的整型，能存储精确的小数。而 FLOAT 和 DOUBLE 是有取值范围的，并支持使用标准的浮点进行近似计算。计算时 FLOAT 和 DOUBLE 相比 DECIMAL 效率更高一些，DECIMAL 可以理解成是用字符串进行处理。
• 字符串类型，包括 VARCHAR、CHAR、TEXT、BLOB。VARCHAR 用于存储可变长字符串，它比定长类型更节省空间。VARCHAR 使用额外 1 或 2 个字节存储字符串长度。列长度小于 255 字节时，使用 1 字节表示，否则使用 2 字节表示。VARCHAR 存储的内容超出设置的长度时，内容会被截断。CHAR 是定长的，根据定义的字符串长度分配足够的空间。CHAR 会根据需要使用空格进行填充方便比较。CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。CHAR 存储的内容超出设置的长度时，内容同样会被截断。
• 枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。有时可以使用 ENUM 代替常用的字符串类型。ENUM 存储非常紧凑，会把列表值压缩到一个或两个字节。ENUM 在内部存储时，其实存的是整数。尽量避免使用数字作为 ENUM 枚举的常量，因为容易混乱。排序是按照内部存储的整数。
• 日期和时间类型，尽量使用 timestamp，空间效率高于 datetime，用整数保存时间戳通常不方便处理。如果需要存储微秒，可以使用 bigint 存储。

• TINYTEXT。
• TEXT。
• MEDIUMTEXT。
• LONGTEXT。

• CHAR 和 VARCHAR 类型在存储和检索方面有所不同。
• CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255，当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。

 BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写的 BLOB。
 BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

• datetime：以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用 8 个字节得存储空间，datatime 类型与时区无关。
• timestamp：以时间戳格式存储，占用 4 个字节，范围小 1970-1-1 到 2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改 timestamp 列得值。
• date：（生日）占用得字节数比使用字符串、datatime、int 储存要少，使用 date 只需要 3 个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算。
• time：存储除年月日的时间数据。

注意：不要使用字符串类型来存储日期时间数据（通常比字符串占用得储存空间小，在进行查找过滤可以利用日期的函数，使用 int 存储日期时间不如使用timestamp 类型。

 NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。例如：salary DECIMAL(9,2)，在这个例子中，9(precision) 代表将被用于存储值的总的小数位数，而 2(scale) 代 表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在 salary 列中的值的范围是从 -9999999.99 到 9999999.99。

 字段类型优先级: 整形 > date,time > enum,char > varchar > blob,text。
 优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型。

 在 SELECT 语句的列比较中使用 =，<>，<=，<，>=，>，<<，>>，<=>，AND，OR 或 LIKE 运算符。

 NOW() 命令用于显示当前年份，月份，日期，小时，分钟和秒。
 CURRENT_DATE() 仅显示当前年份，月份和日期。

• CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
• FORMAT(X, D)- 格式化数字 X 到 D 有效数字。
• CURRDATE(), CURRTIME()- 返回当前日期或时间。
• NOW() – 将当前日期和时间作为一个值返回。
• MONTH()，DAY()，YEAR()，WEEK()，WEEKDAY() – 从日期值中提取给定数据。
• HOUR()，MINUTE()，SECOND() – 从时间值中提取给定数据。
• DATEDIFF(A，B) – 确定两个日期之间的差异，通常用于计算年龄。
• SUBTIMES(A，B) – 确定两次之间的差异。
• FROMDAYS(INT) – 将整数天数转换为日期值。

 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
 索引是一种数据结构，其是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+ 树。
 更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

• 主键索引: 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引；可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引。
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为 NULL 值。可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引；可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引。
• 全文索引： 是目前搜索引擎使用的一种关键技术。可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引。

• 快速访问数据表中的特定信息，提高检索速度。
• 创建唯一性索引，保证数据库表中每一行数据的唯一性。
• 加速表和表之间的连接。
• 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

 优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

 注意索引个数、组合索引列数量区别。
 具体答案查看官网解释，一个 innodb 引擎的表最多可以包含 64 个二级索引，加主键索引为 65 个，多列索引最多允许 16 列，见 官网说明open in new window。

 SHOW INDEX FROM tablename，tablename 使用具体的表名。

• 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
• 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

 索引虽好，但也不是无限制的使用，最好符合一下几个原则。

• 最左前缀匹配原则，组合索引非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like） 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4。 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。
• 较频繁作为查询条件的字段才去创建索引。
• 更新频繁字段不适合创建索引。
• 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)。
• 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
• 定义有外键的数据列一定要建立索引。
• 对于定义为 text、image 和 bit 的数据类型的列不要建立索引。
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
• 非空字段：应该指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。

 如果查询语句是select * from table where ID = 100，即主键查询的方式，则只需要搜索 ID 这棵 B+ 树。
 如果查询语句是select * from table where k = 1，即非主键的查询方式，则先搜索 k 索引树，得到 ID=100。再到 ID 索引树搜索一次，这个过程也被称为回表。

• 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键。
• 主键不允许为空值，唯一索引列允许空值。
• 一个表只能有一个主键，但是可以有多个唯一索引。
• 主键可以被其他表引用为外键，唯一索引列不可以。
• 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构。

· 对于查询操作来说：普通索引和唯一索引的性能相近，都是从索引树中进行查询。
· 对于更新操作来说：唯一索引要比普通索引执行的慢，因为唯一索引需要先将数据读取到内存中，再在内存中进行数据的唯一效验，所以执行起来要比普通索引更慢。

 索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash 索引，B+ 树索引等，而我们经常使用的 InnoDB 存储引擎的默认索引实现为：B+ 树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree 索引。

• 在 B 树中，你可以将键和值存放在内部节点和叶子节点；但在 B+ 树中，内部节点都是键，没有值，叶子节点同时存放键和值。
• B+ 树的叶子节点有一条链相连，而 B 树的叶子节点各自独立。

 使用 B 树的好处：
 B 树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得 B 树在特定数据重复多次查询的场景中更加高效。
 使用 B+ 树的好处：
 由于 B+ 树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+ 树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+ 树只需要使用 O(logN) 时间找到最小的一个节点，然后通过链进行 O(N) 的顺序遍历即可。而 B 树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

• 存储结构（主索引／辅助索引）：InnoDB 的数据文件本身就是主索引文件，而 MyISAM 的主索引和数据是分开的；InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址，而 MyISAM 的辅助索引和主索引没有多大区别；InnoDB 是聚簇索引（仅限主键，非主键索引也是非聚簇索引），数据挂在主键索引之下。
• 锁：MyISAM 使用的是表锁；InnoDB 使用行锁。
• 事务：MyISAM 没有事务支持和 MVCC；InnoDB 支持事务和 MVCC。
• 全文索引：MyISAM 支持 FULLTEXT 类型的全文索引；InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好。
• 主键：MyISAM 允许没有任何索引和主键的表存在，索引都是保存行的地址；InnoDB 如果没有设定主键或非空唯一索引，就会自动生成一个 6 字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值。
• 外键：MyISAM 不支持；InnoDB 支持。

 可参考下面资料加深理解： 聚簇索引和非聚簇索引open in new window 。
MyISAM 和 InnoDB 索引的区别open in new window。

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam 通过 key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因。

 建议使用聚集索引的场合为：

• 此列包含有限数目的不同值。
• 查询的结果返回一个区间的值。
• 查询的结果返回某值相同的大量结果集。
• 当事务要搜索排序的时候。

 建议使用非聚集索引的场合为：

• 此列包含了大量数目不同的值。
• 查询的结束返回的是少量的结果集。
• order by 子句中使用了该列(应该是与 order by 排序逻辑有关系，非聚集索引排序时加载数据少，且无唯一校验逻辑，排序效率高些)。

 参考： 聚簇索引和非聚簇索引以及何时使用open in new window。
什么时候用聚集索引，什么时候用非聚集索引？open in new window。

实现原理：
 索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
 索引的原理很简单，就是把无序的数据变成有序的查询：

• 把创建了索引的列的内容进行排序。
• 对排序结果生成倒排表。
• 在倒排表内容上拼上数据地址链。
• 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

优化：
 B+ 树，经过优化的 B+ 树，主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。
 具体优化详见：MySQL 对 B+ 树插入逻辑的优化open in new window。

• 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
• 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

 阿里巴巴甚至不建议 MySQL 用 text 类型字段，见为什么阿里巴巴不建议 MySQL 使用 Text 类型？open in new window。

 详细可参考MySQL 中索引失效的常见场景与规避方法open in new window。

• 通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
• 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的 INSERT，DELETE，UPDATE 将为此多付出4，5 次的磁盘 I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询（INDEX RANGE SCAN）适用于两种情况:

1. 基于一个范围的检索，一般查询返回结果集小于表中记录数的 30%。
2. 基于非唯一性索引的检索。

 关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加、修改、删除、都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO,，会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）。
2. 然后删除其中无用数据（此过程需要不到两分钟）。
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

 不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。
 举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行 select age from employee where age < 20 的查询时，在索引的叶子节点上，已经包含了age 信息，不会再次进行回表查询。

• MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
• MySQL 使用索引时需要索引有序，假设现在建立了 "name，age，school" 的联合索引，那么索引的排序为: 先按照 name 排序，如果 name 相同，则按照 age 排序，如果 age 的值也相等，则按照 school 进行排序。
• 当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

• 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销。
• 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一。
• 索引列越多，通过索引筛选出的数据越少。

 顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。
 最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询 （>、<、between、like） 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。
 = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式

• or MySQL 5 以下索引会失效，高版本时大部分场景 or 也是会失效。
• 对于多列索引，不是使用的第一部分(第一个),则不会使用索引（非绝对，使用覆盖索引时也会用）。
• 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
• 如果 MySQL 估计使用全表扫描要比使用索引快,则不使用索引。

• 合理的建立索引能够加速数据读取效率，不合理的建立索引会拖慢数据库的响应速度。
• 索引越多，更新数据的速度越慢。

 不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如 where，order by。
 例子：

SELECT id, title, content, cat_id FROM article WHERE cat_id = 1;
上面这个语句，你在 id/title/content 上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键 cat_id 上放置一个索引，那作用就相当大了。

 我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 REVERSE() 函数来创建一个函数索引，查询脚本如：select * from t where reverse(f) like reverse(’%abc’)。

• 索引：一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。
• 唯一索引：普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
• 主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。
• 联合索引：可以覆盖多个数据列，如像 INDEX(columnA, columnB) 索引，这就是联合索引。

 性能影响：
 索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

 LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

 ％ 对应于 0 个或更多字符，_只是 LIKE 语句中的一个字符。

 UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令。
 FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令。

 在 MySQL 中，使用以下代码查询显示前 50 行：SELECT * FROM LIMIT 0,50。

 参考文章理解 深入解析 MySQL 的 EXPLAINopen in new window。

 '123'=123 为 true。实际比较是隐式转换为 123.0 = 123.0 比较了。
 'a'=1 为 false，因为所有的字符转数字都会转为 0。

 DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用。SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a。

 参考 MySQL 慢查询open in new window

 事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
 或者这样理解：事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。

 见 1 MySQL 架构与历史 的 4 事务 小节。

 在缺省模式下，MySQL 是 autocommit 模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，MySQL 是不支持事务的。
 是如果你的 MySQL 表类型是使用 InnoDB Tables 或 BDB tables 的话，你的 MySQL 就可以使用事务处理,使用 SETAUTOCOMMIT=0 就可以使 MySQL 允许在非 autocommit 模式，在非 autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的更改。

 见 1 MySQL 架构与历史 的 4.1 隔离级别 小节。

 见 1 MySQL 架构与历史 的 4.1 隔离级别 小节。

 见 MySQL 事务隔离级别会产生的并发问题及解决办法open in new window。

 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
 要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

 参考 MySQL 隔离级别与锁的关系open in new window

 共有 5 种类型的存储引擎：

• MyISAM。
• Heap。
• Merge。
• INNODB。
• ISAM。

 每个 MyISAM 表格以三种格式存储在磁盘上：

1. "frm" 文件存储表定义。
2. 数据文件具有 ".MYD"（MYData）扩展名。
3. 索引文件具有 ".MYI"（MYIndex）扩展名。

 InnoDB 引擎的 4 大特性：

• 插入缓冲（insert buffer）。
• 二次写（double write）。
• 自适应哈希索引（ahi）。
• 预读（read ahead）。

• Record lock：单个行记录上的锁。
• Gap lock：间隙锁，锁定一个范围，不包括记录本身。
• Next-key lock：record + gap 锁定一个范围，包含记录本身。

 MyISAM：

• 不支持事务，但是每次查询都是原子的；
• 支持表级锁，即每次操作是对整个表加锁；
• 存储表的总行数；
• 一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

 InnoDb：

• 支持 ACID 的事务，支持事务的四种隔离级别；
• 支持行级锁及外键约束：因此可以支持写并发；
• 不存储总行数：
• 一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
• 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+ 树结构，文件的大调整。

 InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。
 InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
 MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
 InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。  MyISAM 索引和数据分开存储的，索引占用空间小，因此适合大数据量的排序、全部扫描、count(不能带 where) 之类的操作。

 它用来压缩 MyISAM 表，这减少了磁盘或内存使用。

 在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT，BLOB 等字段，以适应不同长度的数据类型。
 MyISAM Static 在受损情况下更容易恢复。

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。

• 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份。
• 负载均衡：降低单个服务器的压力。
• 高可用和故障切换：帮助应用程序避免单点失败。
• 升级测试：可以用更高版本的MySQL作为从库。

• 在主库上把数据更改记录到二进制日志。
• 从库将主库的日志复制到自己的中继日志。
• 从库读取中继日志的事件，将其重放到从库数据中。

 如详细介绍下如下几点。

• MySQL 整体架构。
• MySQL 查询语句执行流程。
• MySQL 更新语句执行流程。包括 redo log、bin log 两阶段提交。
• 待补充，如主从。
   详细可参考 MySQL 架构原理(详解)open in new window。

 参考 MySQL 一条查询 SQL 语句的完整执行流程open in new window

 见 一条 Update 语句的执行过程是怎样的？open in new window。

 参考 详解 MySQL 中的 Buffer Pool，深入底层带你搞懂它open in new window。

 可以详细问 AI，以下回答的都比较简单。
 见 InnoDB 引擎如何管理 Page 页open in new window。

## 核心概念
**MVCC** (Multi-Version Concurrency Control) 是 InnoDB 实现事务隔离的机制，通过数据多版本实现：
- **读不加锁**：非阻塞读
- **写加锁**：保证数据一致性

## 核心实现

### 1. 隐藏字段
每行记录包含：
- `DB_TRX_ID`：最近修改该行的事务ID
- `DB_ROLL_PTR`：指向Undo Log的回滚指针
- `DB_ROW_ID`：隐含自增ID（无主键时生成）

### 2. Undo Log
- 存储数据的历史版本
- 构成版本链（通过`DB_ROLL_PTR`串联）

### 3. ReadView
事务执行快照读时生成，包含：
- `trx_ids`：当前活跃事务ID集合
- `low_limit_id`：当前最大事务ID+1
- `up_limit_id`：活跃事务最小ID
- `creator_trx_id`：创建该ReadView的事务ID

## 可见性判断规则
事务访问记录时，按版本链顺序检查：
1. `DB_TRX_ID` == `creator_trx_id` → 当前事务修改，可见
2. `DB_TRX_ID` < `up_limit_id` → 事务已提交，可见
3. `DB_TRX_ID` >= `low_limit_id` → 未来事务修改，不可见
4. 检查`trx_ids`集合：
   - 存在 → 事务未提交，不可见
   - 不存在 → 事务已提交，可见

## 隔离级别差异
| 隔离级别        | MVCC应用场景             |
| --------------- | ------------------------ |
| READ COMMITTED  | 每次读生成新ReadView     |
| REPEATABLE READ | 第一次读生成ReadView复用 |

## 面试回答要点
1. **核心组件**：版本链 + ReadView + Undo Log
2. **核心优势**：读不阻塞写，写不阻塞读
3. **典型问题**：RC级别不可重复读、RR级别幻读（需间隙锁配合）
4. **实现代价**：需要维护版本链，增加Undo Log空间

 参考 MVCC 多版本并发控制open in new window。

 见 全解 MySQL 之主从篇open in new window

• 参考 五分钟搞懂 MySQL 索引下推open in new window。

 见 一文带你了解 MySQL 之 Adaptive Hash Indexopen in new window

• 参考 事务的四大特性，以及实现原理open in new window。

1. 自适应哈希。
2. 两次写。
3. 插入缓冲。

 参考 InnoDB 三大特性open in new window。

 参考 MySQL 数据库——锁-行级锁open in new window。

 参考 mysql8.0 查看锁信息open in new window。

 主要理解下全字段排序和 rowid 排序。

 要点如下，可参考文章 排序原理open in new window。

1. 全字段排序。

• 流程为 主键 ID 索引->找主键索引数据->加载全量数据到 sort buffer->sort buffer 排序->返回结果集。
• sort buffer 容量不足，会使用临时文件辅助排序。
• 查看执行明细信息。

2. rowid 排序。

• 多了次回表
• 排序过程占用更小的内存。
• 阈值是根据配置 show variables like '%max_length_for_sort_data%' 决定，大于这个就会使用 rowid 排序。

 查看执行明细信息步骤如下，注意 filesort_summary 与 SQL 有关系，详细指令不在这里深入了。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select * from employees.dept_emp e ORDER BY to_date  LIMIT 1000;

/* 查看 OPTIMIZER_TRACE 输出，里面有不同的属性描述相应信息 */
select * FROM information_schema.OPTIMIZER_TRACE;

SET optimizer_trace='enabled=off'; 

 参考 MySQL 一行记录是怎么存储的？open in new window。弄明白原理后，以下几个面试题也能回答了。
 可以查看视频会详细很多 MySQL 一行记录是怎么存储的？open in new window。

• MySQL 的 NULL 值是怎么存放的？
• MySQL 是怎么知道 varchar(n) 实际占用的大小？
• varchar(n) 中的 n 最大取值为多少？
• 行溢出后，MySQL 是怎么处理的？

 它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

• 设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• MySQL 库主从读写分离。
• 找规律分表，减少单表中的数据量提高查询速度。
• 添加缓存机制，比如 memcached、redis 等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的 SQL。比如 SELECT * FROM TABEL改为SELECT field_1, field_2, field_3 FROM TABLE。

 Check 限制，它在数据库表格里被定义，用来限制输入该列的值。
 触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

• 优化 shema、SQL 语句 + 索引。
• 第二加缓存，memcached, redis。
• 主从复制，读写分离。
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统。
• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的 sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，SQL 中尽量带 sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

 超大的分页一般从两个方向上来解决:

• 数据库层面： 这也是我们主要集中关注的(虽然收效没那么大),类似于 select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要 load 1000000 数据然后基本上全部丢弃,只取 10 条当然比较慢. 当时我们可以修改为 select * from table where id in (select id from table where age > 20 limit 1000000,10)。这样虽然也 load 了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果 ID 连续的话,我们还可以select * from table where id > 1000000 limit 10，效率也是不错的，优化的可能性有许多种,但是核心思想都一样,就是减少 load 的数据
• 需求的角度：减少这种需求。主要是不做类似的需求(直接跳转到几百万页之后的具体某一页。只允许逐页查看或者按照给定的路线走，这样可预测，可缓存)以及防止 ID 泄漏且连续被人恶意攻击。

 null 值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

 密码散列，盐，用户身份证号等固定长度的字符串应该使用 char 而不是 varchar 来存储，这样可以节省空间且提高检索效率。

 一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

• 需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
• 将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
• 增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
• 增加冗余字段：设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

 当 cpu 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
 如果是 mysqld 造成的，show processlist，看看里面跑的 session 情况，是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。
 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 SQL、改内存参数)之后，再重新跑这些 SQL。
 也有可能是每个 SQL 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

 如果数据库引擎用的是 innodb，那么 id 为 6，因为 innodb 表把自增主键的最大 id 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 id 丢失。
 如果数据库引擎用的是 myisam，那么 id 为 8，因为 myisam 表把自增主键记录到数据文件里面，所以重启数据库，自增主键的最大 id 也不会丢失。

• 数据分布不均匀。分析：使用 EXPLAIN 命令查看查询计划，检查是否使用了索引以及扫描的行数。
• 缓存影响。注意 MySQL8 移除了缓存。
• 锁竞争。分析：使检查是否有其他事务正在访问相同的数据，导致锁竞争。
• 磁盘 I/O，即数据分布在磁盘的不同位置，可能导致更多的磁盘 I/O 操作。分析：检查磁盘 I/O 情况，确认是否有大量的磁盘读取操作。
• 统计信息不准确，其可能导致优化器选择不合适的执行计划。分析：使用 ANALYZE TABLE 命令更新表的统计信息，然后再次测试查询速度。
• 查询计划不同。分析：使用 EXPLAIN 命令查看两个查询的执行计划，确认是否存在差异。
• 数据碎片，如果表中有大量的数据碎片，可能导致查询速度变慢。分析：使用 OPTIMIZE TABLE 命令优化表，减少数据碎片。
• 硬件资源，即性能瓶颈。
• 查询语句本身的性能问题。

1. 为什么不能使用自增 ID？
    主要是分布式环境下存在问题如单点故障、拓展性差、性能瓶颈、数据迁移困难。
2. 为什么不能使用 UUID？
    UUDI 存在问题如存储空间大、索引效率低、查询性能差、可读性差。
3. 雪花算法（Snowflake）。

• 优点：分布式唯一性、时间有序、高性能、存储空间小。
• 缺点：时钟回拨、机器 ID 分配问题、扩展性问题。

4. 其他分布式 ID 解决方案。

• 数据库分段发号。
• Redis 生成 ID。
• Leaf 算法（美团-双 Buffer 方案）。
• UUID 变种（基于时间戳有序）。

 以下是一些文档，可参考理解方案明细。

• 分布式 ID|源码解析美团 Leaf 双 Buffer 优化方案open in new window。

 可以从以下一些点说明。

• 使用 explain 解析执行计划，优化执行计划(关注关键指标：如 rows（扫描行数）、filter（过滤条件）、cost（预估成本）等)。
• 优化查询语句，如减少查询字段、减少子查询、优化 where 条件、分页优化(使用索引)、使用窗口函数、避免使用 or、!= 查询，适当使用前缀索引、避免列上函数运算等。
• 索引优化，如创建合适的索引、避免过多索引、使用复合索引、使用覆盖索引、利用索引扫描排序。
• 表结构优化，如数据类型优化、分库分表、使用历史表。
• 数据库配置优化，如调整内存参数、并发优化、日志优化，升级硬件。
• 优化 Join 操作，如减少 Join 数量，小表驱动大表、使用 exists 替代 in(某些场景更高效)、避免 join 太多的表。
• 批量操作优化。
• 缓存优化，如查询缓存和应用层缓存。
• 定期维护，如重建索引、删除历史数据。
• 工具辅助，如慢查询日志、性能监控工具。
• 分布式与读写分离。
• 避免锁竞争。

 如果覆盖索引比全表扫描快，MySQL 会优先使用索引，而不是全表扫描，即使联合索引没有最左列。

 如果覆盖索引比全表扫描快，MySQL 会优先使用索引，而不是全表扫描，即使联合索引没有最左列。例举以下内容，DeepSeek 回答了更多其他内容。

• where 条件没有索引。
• where 条件索引失效。如 like、使用函数、数据分布不均、不符合最左原则等。

 主要有如下几点原因。

1. 性能问题。会多额外的数据一致性校验查询。
2. 并发问题。外键约束会启用行级锁，主表写入时会进入阻塞。并发时可能会导致更新风暴。
3. 级联删除问题。多层级联删除会让数据变得不可控，触发器也严格被禁用。
4. 数据耦合和迁移问题。数据库层面数据关系产生耦合，数据迁移维护困难。

 参考 MySQL 默认隔离级别是 RR，为什么阿里等大厂会改成 RC？open in new window。

 如果有 null 值，还会导致数据丢失，因为 null 做任何操作都是未知。

 参考 为什么推荐使用 BigDecimal 而不是 doubleopen in new window。
 实际个人开发交易系统时，都是用的带精度的 double 类型处理，因为 BigDecimal 太慢了。

 可以通过全量备份和 binlog 日志恢复数据。

 略，参考以下理解，要点需要了解执行流程。

• 面试官：MySQL查询200g的大表，如果本机内存只有100G，会发生什么？open in new window。
• 大规模数据查询是否会内存溢出open in new window。

 略，参考以下理解，要点是根据统一信息等会生成多个执行计划，从中选择最优的。

• 优化器是如何选择最优执行计划的open in new window。

 略，参考以下理解。

• 阿里面试：为什么 MySQL 不建议使用 Delete 删除数据？open in new window。
• 为什么大厂的线上数据库禁止使用 delete 语句open in new window。

 不是，有多种情况，参考 面试被问懵了：MySQL 自增主键一定是连续的吗？open in new window。

 略，即分页查询优化。

 略。核心就是范围查询，B 树和红黑树节点不在同一层，需要遍历父节点后找相邻节点，范围查询效率不高。
 思考：跳表也支持范围查询，Redis 是这么勇的。

 略，参考。

• sql 执行流程概述open in new window。

MySQL 面试核心知识点精简版
一、基础架构
三层架构：

连接层（连接池、认证）

服务层（解析器、优化器、执行器）

存储引擎层（InnoDB/MyISAM）

SQL执行流程：

连接→解析→优化→执行→返回

二、存储引擎（InnoDB核心）
核心特性：

事务支持（ACID）

行级锁

MVCC多版本并发控制

内存结构：

Buffer Pool（数据页缓存）

Change Buffer（非唯一索引更新优化）

Log Buffer（redo日志缓冲）

磁盘结构：

表空间（.ibd文件）

双写缓冲（防页断裂）

Redo Log（持久性保障）

三、索引原理
B+树特点：

非叶子节点只存键值

叶子节点链表连接

3-4层可存百万级数据

索引类型：

聚簇索引（主键组织数据）

二级索引（需回表查询）

索引优化：

最左前缀原则

覆盖索引

索引下推

四、事务机制
ACID实现：

原子性：Undo Log

持久性：Redo Log

隔离性：锁+MVCC

一致性：前三者共同保证

隔离级别：

读未提交 → 串行化（重点掌握RC和RR）

MVCC原理：

版本链（Undo Log）

ReadView（trx_ids列表）

五、锁机制
锁类型：

行锁（Record Lock）

间隙锁（Gap Lock）

临键锁（Next-Key Lock）

死锁处理：

等待超时

死锁检测（默认）

六、性能优化
EXPLAIN关键列：

type（访问类型）

key（使用索引）

rows（预估行数）

Extra（额外信息）

慢查询分析：

开启慢查询日志

pt-query-digest分析

 详见 deepseek。
 快速记忆口诀。"一架构二引擎，三索四事五锁六优"（架构→引擎→索引→事务→锁→优化）。

• B 站-架构师徐庶-面试攻略(讲得很好)open in new window
• B 站-百里谈技术-Java 热门面试场景题open in new window。