MySQL 常见面试题

正文
参考资料

正文

1 UNION ALL 与 UNION 的区别

UNION和UNION ALL关键字都是将两个结果集合并为一个。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

而UNION ALL只是简单的将两个结果合并后就返回。

由于UNION需要排序去重，所以 UNION ALL 的效率比 UNION 好很多。

2 TRUNCATE 与 DELETE 区别

TRUNCATE 是DDL语句，而 DELETE 是DML语句。

TRUNCATE 是先把整张表drop调，然后重建该表。而 DELETE 是一行一行的删除，所以 TRUNCATE 的速度肯定比 DELETE 速度快。

TRUNCATE 不可以回滚，DELETE 可以。

TRUNCATE 执行结果只是返回0 rows affected，可以解释为没有返回结果。

TRUNCATE 会重置水平线（自增长列起始位），DELETE 不会。

TRUNCATE 只能清理整张表，DELETE 可以按照条件删除。

一般情景下，TRUNCATE性能比DELETE好一点。

3 TIMESTAMP 与 DATETIME 的区别

相同点：

TIMESTAMP 列的显示格式与 DATETIME 列相同。显示列宽固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。

不同点：

TIMESTAMP

4个字节存储，时间范围：1970-01-01 08:00:01~2038-01-19 11:14:07。

值以UTC格式保存，涉及时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区。

DATETIME

8个字节存储，时间范围：1000-10-01 00:00:00~9999-12-31 23:59:59。

实际格式存储，与时区无关。

4 什么是联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

5 为什么要使用联合索引

减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。

覆盖索引：对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化手段之一。

效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql select from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W * 10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w * 10% * 10% * 10%=1w，效率得到明显提升。

6 MySQL 联合索引最左匹配原则

在 MySQL 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

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的查询优化器会帮你优化成索引可以识别的形式。

7 什么是聚集和非聚集索引

聚集索引就是以主键创建的索引。

非聚集索引就是以非主键创建的索引。

8 什么是覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引页中就能够取得（如果不是聚集索引，叶子节点存储的是主键+列值，最终还是要回表，也就是要通过主键再查找一次），避免了查到索引后，再做回表操作，减少I/O提高效率。

可以结合第10个问题更容易理解。

9 什么是前缀索引

前缀索引就是对文本的前几个字符（具体是几个字符在创建索引时指定）创建索引，这样创建起来的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前缀索引，也不能把它们用作覆盖索引。

创建前缀索引的语法：

ALTER TABLE table_name ADD
KEY(column_name(prefix_length))

10 InnoDB 与 MyISAM 索引存储结构的区别

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，所以必须有主键，如果没有显示定义，自动为生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

InnoDB的辅助索引（Secondary Index，也就是非主键索引）存储的只是主键列和索引列，如果主键定义的比较大，其他索引也将很大。

MyISAM引擎使用B+Tree作为索引结构，索引文件叶节点的data域存放的是数据记录的地址，指向数据文件中对应的值，每个节点只有该索引列的值。

MyISAM主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，辅助索引可以重复，（由于MyISAM辅助索引在叶子节点上存储的是数据记录的地址，和主键索引一样，所以不需要再遍历一次主键索引）。

简单的说：

主索引的区别：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

11 为什么尽量选择单调递增数值类型的主键

InnoDB中数据记录本身被存于主索引（B+树）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的结点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。

如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引结点的后续位置，当一页写满，就会自动开辟一个新的页，这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插入到现有索引页的中间某个位置，此时MySQL不得不为了将新记录查到合适位置而移动元素，甚至目标页可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

简单的说：

索引树只能定位到某一页，每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。

12 建表时，int 后面的长度的意义

int占多少个字节，已经是固定的了，长度代表了显示的最大宽度。如果不够会用0在左边填充，但必须搭配zerofill使用。也就是说，int的长度并不影响数据的存储精度，长度只和显示有关。

13 SHOW INDEX 结果字段代表什么意

SHOW INDEX from table_name

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
user	0	PRIMARY	1	id	A	669				BTREE
user	1	username	1	username	A	268			YES	BTREE

解说

Table：

表名。

Non_unique：

0：该索引不含重复值。

1：该索引可含有重复值。

Key_name：

索引名称，如果是主键索引，名称总是为PRIMARY。

Seq_in_index：

该列在索引中的序号，从 1 开始。例如：存在联合索引 idx_a_b_c (a,b,c)，则 a 的 Seq_in_index=1，b=2，c=3。

Column_name：

列名。

Collation：

索引的排列顺序：A（ascending），D (descending)，NULL (not sorted)。

Cardinality：

一个衡量该索引的唯一程度的值，可以使用ANALYZE TABLE（INNODB）或者 myisamchk -a（MyISAM）更新该值。

如果表记录太少，该字段的意义不大。一般情况下，该值越大，索引效率越高。

Sub_part：

对于前缀索引，用于索引的字符个数。如果整个字段都加上了索引，则显示为NULL。

Null：

YES：该列允许NULL值。

''：该列不允许NULL值。

Index_type：

索引类型，包括(BTREE, FULLTEXT, HASH, RTREE)。

14 MySQL高效分页

存在SQL：SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的问题最大：取出N+M行，丢弃前N行，返回 N ~ N+M 行的记录，如果N值非常大，效率极差（表记录1500w，N=10000000,M=30 需要9秒）。

解决办法：SQL：SELECT id FROM ttl_product_info WHERE id > N LIMIT M，id 列是索引列，id > N属于 range 级别，效率自然高，然后从位置开始取30条记录，效率极高（表记录1500w，N=10000000,M=30，需要0.9毫秒）。

当然想要实现上述效果的前提是：

id是唯一索引，而且单调递增。

N 的值是上一次查询的记录的最后一条id，（需要前端保存一下，不能直接用传统的方法获得）

不支持跨页查询，只能按照第1，2，3，4页这样查询逐页查询。

15 LIKE问题

如何解决like '%字符串%'时索引失效？

LIKE问题：like 以通配符开头 ('%abc…')，mysql索引失效会变成全表扫描的操作。

罪魁祸首是%，不是LIKE，LIKE 条件是 type = range 级别

%xxx%：全表扫描

%xxx：全表扫描

xxx%：range

解决办法：

使用覆盖索引，可以由 ALL 变为INDEX，为啥呢？覆盖索引之后就能使用索引进行全表扫描。这里要注意一下，使用符合索引的时候，命中一个字段就可以，不用全部命中。

16 索引类型

https://zhuanlan.zhihu.com/p/344773786

https://zhuanlan.zhihu.com/p/394429932

https://zhuanlan.zhihu.com/p/78982303

https://zhuanlan.zhihu.com/p/313422544

https://zhuanlan.zhihu.com/p/410715538

MySQL 支持多种索引类型，以下是一些常见的索引类型。

按数据结构分类：

B-Tree索引(B+树)：B-Tree（平衡多路查找树）是 MySQL 最常用的索引类型，几乎所有的存储引擎都支持这种索引。 Mysql官方叫做B-Tree索引，但采用的是B+树数据结构。它适用于全值匹配和范围查找。在 InnoDB、Memory、MyISAM 和 Archive 等存储引擎中都可以使用。
哈希索引（Hash Index）：哈希索引基于哈希算法，适用于等值查询。目前只有 MEMORY 存储引擎支持哈希索引。
空间索引（Spatial Index）：空间索引（R-Tree）基于 R-Tree 数据结构，是一种专门用于处理地理空间数据的索引。
位图索引（Bitmap Index）：位图索引是对数据进行位图标记的一种索引，适用于数据在取值范围上比较集中的情况。主要用于高维数据和大规模数据的处理。它使用位图数据结构来表示索引数据。MySQL 的 Archive 存储引擎支持位图索引。
全文索引（Full-Text Index）：全文索引是专门用于全文搜索的索引。它基于倒排索引实现，提供了一种高效的搜索方式，用于在大量的文本数据中进行关键词搜索。全文索引主要用于 MyISAM 和 InnoDB 存储引擎。
复合索引（Composite Index）：复合索引是由多个列组成的索引，它可以同时满足多个查询条件，从而提高查询效率。在设计和使用时需要谨慎，以避免过度冗余和性能下降。复合索引在 InnoDB、Memory、MyISAM 等存储引擎中都支持。
前缀索引（Prefix Index）：前缀索引是一种基于列前缀的索引类型，它可以减小索引的大小，从而提高查询效率。但需要注意的是，前缀索引可能会降低查询的覆盖率。前缀索引在 InnoDB、Memory、MyISAM 等存储引擎中都支持。
唯一索引（Unique Index）：唯一索引确保索引列的值在表中的唯一性。它允许列中的值为空（NULL）。唯一索引在 InnoDB、Memory、MyISAM 等存储引擎中都支持。

按物理存储分类：

聚簇索引（Clustered Index）：聚簇索引将数据按照索引的顺序物理存储，即数据存储和索引是紧密关联的。也就是说，一个表只有一个聚簇索引。它通常与主键相关联。InnoDB 存储引擎默认使用聚簇索引。聚簇索引不是一种索引，而是一种数据存储组织方式。
非聚簇索引：非聚簇索引与数据的物理存储顺序无关，它可以根据索引列的值来组织数据。非聚簇索引可以包含多个列，并且一个表可以有多个非聚簇索引。非聚簇索引的查询效率比聚簇索引低，因为需要先通过二级索引找到主键，再通过主键找到数据。MyISAM 和 InnoDB 存储引擎都支持二级索引。

按字段特性分类：

主键索引（PRIMARY KEY）：主键索引是建立在主键上的索引，一张数据表只能有一个主键索引。主键索引的索引列值不允许为空，且必须唯一。通常在创建表时与表一起创建。
唯一索引（UNIQUE）：唯一索引是建立在UNIQUE字段上的索引，一张表可以有多个唯一索引。唯一索引的索引列值不允许为空，但允许有一个NULL值。
普通索引（INDEX）：普通索引是建立在普通字段上的索引，没有主键和唯一索引的限制，可以重复。
全文索引（FULLTEXT）：全文索引是一种特殊的索引类型，主要用于文本搜索和自然语言查询。它基于倒排索引实现，适用于大量的文本数据的查询操作。

按字段个数分类：

单列索引：单列索引是建立在单个列上的索引，它只能用于查询该列的数据。
联合索引（复合索引、组合索引）：联合索引是建立在多个列上的索引，它用于查询多个列的数据。联合索引可以提高多个列同时查询的效率。

按查询实现分类：

覆盖索引（Covering Index）：覆盖索引是一种特殊类型的索引，它包含了需要查询的列数据，因此无需回表查询。这可以提高查询性能，特别是对于只查询少量数据的查询。
前缀索引：前缀索引是一种基于列前缀的索引类型，它可以减小索引的大小，从而提高查询效率。但需要注意的是，前缀索引可能会降低查询的覆盖率。
函数索引：函数索引是一种特殊类型的索引，它允许在索引列上使用函数或表达式。函数索引可以提高特定函数查询的效率。

17 索引失效

MySQL 中的索引可能会失效的几种情况如下：

没有查询条件，或者查询条件没有建立索引：这种情况下，查询无法利用索引，会导致全表扫描，从而使得查询效率降低。
在查询条件上没有使用引导列：如果查询条件中没有使用引导列，那么索引也无法发挥作用。
在带有复合索引的列上查询不是第一列的数据，也不会使用索引。
最简单的，如果使用索引后比不使用索引的效率还差，那么 MySQL 就不会使用索引。
估计全表扫描会比索引快，索引也会失效。
查询的数量是大表的大部分，应该是30％以上：在大数据表中，如果查询的数量只占表的一小部分，那么索引的效率可能会降低。
索引本身失效：例如，在非唯一索引列上使用聚合函数或者使用不匹配的查询条件等，都可能导致索引失效。
查询条件使用函数在索引列上：如果查询条件中使用了函数，那么在索引列上使用该函数时，索引也会失效。
隐式转换导致索引失效：由于表的字段定义与查询条件中的数据类型不符，可能会导致隐式转换，从而使得索引失效。
如果 where 条件的列参与了计算，那么也不会使用索引
在索引列上使用“IS NULL”或“IS NOT NULL”操作：在索引列上使用这些操作也会导致索引失效。
如果 SQL 中使用了 OR 条件，OR 前的条件列有索引，而后面的列没有索引的话，那么涉及到的索引都不会使用。
在索引字段上使用“not”，“<>”，“!=”，“or”等等：在索引字段上使用这些操作符进行查询时，索引也会失效。
索引列使用了 like ，并且 % 位于第一个字符，则不会使用索引。
在 order by 操作中，排序的列同时也在 where 语句中，将不会使用索引。

18 SQL优化步骤

https://ibaiyang.github.io/blog/mysql/2021/09/18/图解-MySQL-优化.html

查看服务器状态信息

show status

Variable_name         Vaule
"Aborted_clients"	"2993"
"Aborted_connects"	"52449"
"Binlog_cache_disk_use"	"273"
"Binlog_cache_use"	"2248435"
"Binlog_stmt_cache_disk_use"	"0"
"Binlog_stmt_cache_use"	"1602"
"Bytes_received"	"234"
"Bytes_sent"	"115"
"Com_admin_commands"	"0"
"Com_assign_to_keycache"	"0"
"Com_alter_db"	"0"
"Com_alter_db_upgrade"	"0"
"Com_alter_event"	"0"
"Com_alter_function"	"0"
"Com_alter_instance"	"0"
"Com_alter_procedure"	"0"
"Com_alter_server"	"0"
"Com_alter_table"	"0"
"Com_alter_tablespace"	"0"
"Com_alter_user"	"0"
"Com_analyze"	"0"
"Com_begin"	"0"
"Com_binlog"	"0"
"Com_call_procedure"	"0"
"Com_change_db"	"1"
"Com_change_master"	"0"
"Com_change_repl_filter"	"0"
"Com_check"	"0"
"Com_checksum"	"0"
"Com_commit"	"0"
"Com_create_db"	"0"
"Com_create_event"	"0"
"Com_create_function"	"0"
"Com_create_index"	"0"
"Com_create_procedure"	"0"
"Com_create_server"	"0"
"Com_create_table"	"0"
"Com_create_trigger"	"0"
"Com_create_udf"	"0"
"Com_create_user"	"0"
"Com_create_view"	"0"
"Com_dealloc_sql"	"0"
"Com_delete"	"0"
"Com_delete_multi"	"0"
"Com_do"	"0"
"Com_drop_db"	"0"
"Com_drop_event"	"0"
"Com_drop_function"	"0"
"Com_drop_index"	"0"
"Com_drop_procedure"	"0"
"Com_drop_server"	"0"
"Com_drop_table"	"0"
"Com_drop_trigger"	"0"
"Com_drop_user"	"0"
"Com_drop_view"	"0"
"Com_empty_query"	"0"
"Com_execute_sql"	"0"
"Com_explain_other"	"0"
"Com_flush"	"0"
"Com_get_diagnostics"	"0"
"Com_grant"	"0"
"Com_ha_close"	"0"
"Com_ha_open"	"0"
"Com_ha_read"	"0"
"Com_help"	"0"
"Com_insert"	"0"
"Com_insert_select"	"0"
"Com_install_plugin"	"0"
"Com_kill"	"0"
"Com_load"	"0"
"Com_lock_tables"	"0"
"Com_optimize"	"0"
"Com_preload_keys"	"0"
"Com_prepare_sql"	"0"
"Com_purge"	"0"
"Com_purge_before_date"	"0"
"Com_release_savepoint"	"0"
"Com_rename_table"	"0"
"Com_rename_user"	"0"
"Com_repair"	"0"
"Com_replace"	"0"
"Com_replace_select"	"0"
"Com_reset"	"0"
"Com_resignal"	"0"
"Com_revoke"	"0"
"Com_revoke_all"	"0"
"Com_rollback"	"0"
"Com_rollback_to_savepoint"	"0"
"Com_savepoint"	"0"
"Com_select"	"0"
"Com_set_option"	"1"
"Com_signal"	"0"
"Com_show_binlog_events"	"0"
"Com_show_binlogs"	"0"
"Com_show_charsets"	"0"
"Com_show_collations"	"0"
"Com_show_create_db"	"0"
"Com_show_create_event"	"0"
"Com_show_create_func"	"0"
"Com_show_create_proc"	"0"
"Com_show_create_table"	"0"
"Com_show_create_trigger"	"0"
"Com_show_databases"	"0"
"Com_show_engine_logs"	"0"
"Com_show_engine_mutex"	"0"
"Com_show_engine_status"	"0"
"Com_show_events"	"0"
"Com_show_errors"	"0"
"Com_show_fields"	"0"
"Com_show_function_code"	"0"
"Com_show_function_status"	"0"
"Com_show_grants"	"0"
"Com_show_keys"	"0"
"Com_show_master_status"	"0"
"Com_show_open_tables"	"0"
"Com_show_plugins"	"0"
"Com_show_privileges"	"0"
"Com_show_procedure_code"	"0"
"Com_show_procedure_status"	"0"
"Com_show_processlist"	"0"
"Com_show_profile"	"0"
"Com_show_profiles"	"0"
"Com_show_relaylog_events"	"0"
"Com_show_slave_hosts"	"0"
"Com_show_slave_status"	"0"
"Com_show_status"	"1"
"Com_show_storage_engines"	"0"
"Com_show_table_status"	"0"
"Com_show_tables"	"0"
"Com_show_triggers"	"0"
"Com_show_variables"	"0"
"Com_show_warnings"	"0"
"Com_show_create_user"	"0"
"Com_shutdown"	"0"
"Com_slave_start"	"0"
"Com_slave_stop"	"0"
"Com_group_replication_start"	"0"
"Com_group_replication_stop"	"0"
"Com_stmt_execute"	"0"
"Com_stmt_close"	"0"
"Com_stmt_fetch"	"0"
"Com_stmt_prepare"	"0"
"Com_stmt_reset"	"0"
"Com_stmt_send_long_data"	"0"
"Com_truncate"	"0"
"Com_uninstall_plugin"	"0"
"Com_unlock_tables"	"0"
"Com_update"	"0"
"Com_update_multi"	"0"
"Com_xa_commit"	"0"
"Com_xa_end"	"0"
"Com_xa_prepare"	"0"
"Com_xa_recover"	"0"
"Com_xa_rollback"	"0"
"Com_xa_start"	"0"
"Com_stmt_reprepare"	"0"
"Compression"	"OFF"
"Connection_errors_accept"	"0"
"Connection_errors_internal"	"0"
"Connection_errors_max_connections"	"0"
"Connection_errors_peer_address"	"39"
"Connection_errors_select"	"0"
"Connection_errors_tcpwrap"	"0"
"Connections"	"1319439"
"Created_tmp_disk_tables"	"0"
"Created_tmp_files"	"458"
"Created_tmp_tables"	"0"
"Delayed_errors"	"0"
"Delayed_insert_threads"	"0"
"Delayed_writes"	"0"
"Flush_commands"	"1"
"Handler_commit"	"0"
"Handler_delete"	"0"
"Handler_discover"	"0"
"Handler_external_lock"	"0"
"Handler_mrr_init"	"0"
"Handler_prepare"	"0"
"Handler_read_first"	"0"
"Handler_read_key"	"0"
"Handler_read_last"	"0"
"Handler_read_next"	"0"
"Handler_read_prev"	"0"
"Handler_read_rnd"	"0"
"Handler_read_rnd_next"	"0"
"Handler_rollback"	"0"
"Handler_savepoint"	"0"
"Handler_savepoint_rollback"	"0"
"Handler_update"	"0"
"Handler_write"	"0"
"Innodb_buffer_pool_dump_status"	"Dumping of buffer pool not started"
"Innodb_buffer_pool_load_status"	"Buffer pool(s) load completed at 240426 10:13:42"
"Innodb_buffer_pool_resize_status"	""
"Innodb_buffer_pool_pages_data"	"31141"
"Innodb_buffer_pool_bytes_data"	"510214144"
"Innodb_buffer_pool_pages_dirty"	"0"
"Innodb_buffer_pool_bytes_dirty"	"0"
"Innodb_buffer_pool_pages_flushed"	"8459097"
"Innodb_buffer_pool_pages_free"	"1024"
"Innodb_buffer_pool_pages_misc"	"599"
"Innodb_buffer_pool_pages_total"	"32764"
"Innodb_buffer_pool_read_ahead_rnd"	"0"
"Innodb_buffer_pool_read_ahead"	"475607"
"Innodb_buffer_pool_read_ahead_evicted"	"29186"
"Innodb_buffer_pool_read_requests"	"16465086766"
"Innodb_buffer_pool_reads"	"2390924"
"Innodb_buffer_pool_wait_free"	"0"
"Innodb_buffer_pool_write_requests"	"204469949"
"Innodb_data_fsyncs"	"6841561"
"Innodb_data_pending_fsyncs"	"0"
"Innodb_data_pending_reads"	"0"
"Innodb_data_pending_writes"	"0"
"Innodb_data_read"	"47215628800"
"Innodb_data_reads"	"2886352"
"Innodb_data_writes"	"13770035"
"Innodb_data_written"	"269915130880"
"Innodb_dblwr_pages_written"	"7509005"
"Innodb_dblwr_writes"	"614224"
"Innodb_log_waits"	"0"
"Innodb_log_write_requests"	"7954402"
"Innodb_log_writes"	"3831056"
"Innodb_os_log_fsyncs"	"4663547"
"Innodb_os_log_pending_fsyncs"	"0"
"Innodb_os_log_pending_writes"	"0"
"Innodb_os_log_written"	"7860517376"
"Innodb_page_size"	"16384"
"Innodb_pages_created"	"44319"
"Innodb_pages_read"	"2881690"
"Innodb_pages_written"	"8459541"
"Innodb_row_lock_current_waits"	"0"
"Innodb_row_lock_time"	"9658"
"Innodb_row_lock_time_avg"	"2"
"Innodb_row_lock_time_max"	"320"
"Innodb_row_lock_waits"	"3599"
"Innodb_rows_deleted"	"1380406"
"Innodb_rows_inserted"	"114621233"
"Innodb_rows_read"	"30383542645"
"Innodb_rows_updated"	"4014853"
"Innodb_num_open_files"	"512"
"Innodb_truncated_status_writes"	"0"
"Innodb_available_undo_logs"	"128"
"Key_blocks_not_flushed"	"0"
"Key_blocks_unused"	"107169"
"Key_blocks_used"	"30"
"Key_read_requests"	"49197"
"Key_reads"	"1560"
"Key_write_requests"	"4790"
"Key_writes"	"4790"
"Last_query_cost"	"0.000000"
"Last_query_partial_plans"	"0"
"Locked_connects"	"0"
"Max_execution_time_exceeded"	"0"
"Max_execution_time_set"	"0"
"Max_execution_time_set_failed"	"0"
"Max_used_connections"	"262"
"Max_used_connections_time"	"2024-05-07 11:01:01"
"Not_flushed_delayed_rows"	"0"
"Ongoing_anonymous_transaction_count"	"0"
"Open_files"	"8"
"Open_streams"	"0"
"Open_table_definitions"	"400"
"Open_tables"	"512"
"Opened_files"	"132072"
"Opened_table_definitions"	"0"
"Opened_tables"	"0"
"Performance_schema_accounts_lost"	"0"
"Performance_schema_cond_classes_lost"	"0"
"Performance_schema_cond_instances_lost"	"0"
"Performance_schema_digest_lost"	"0"
"Performance_schema_file_classes_lost"	"0"
"Performance_schema_file_handles_lost"	"0"
"Performance_schema_file_instances_lost"	"63"
"Performance_schema_hosts_lost"	"0"
"Performance_schema_index_stat_lost"	"0"
"Performance_schema_locker_lost"	"0"
"Performance_schema_memory_classes_lost"	"0"
"Performance_schema_metadata_lock_lost"	"0"
"Performance_schema_mutex_classes_lost"	"0"
"Performance_schema_mutex_instances_lost"	"0"
"Performance_schema_nested_statement_lost"	"0"
"Performance_schema_prepared_statements_lost"	"0"
"Performance_schema_program_lost"	"0"
"Performance_schema_rwlock_classes_lost"	"0"
"Performance_schema_rwlock_instances_lost"	"0"
"Performance_schema_session_connect_attrs_lost"	"0"
"Performance_schema_socket_classes_lost"	"0"
"Performance_schema_socket_instances_lost"	"0"
"Performance_schema_stage_classes_lost"	"0"
"Performance_schema_statement_classes_lost"	"0"
"Performance_schema_table_handles_lost"	"0"
"Performance_schema_table_instances_lost"	"32388"
"Performance_schema_table_lock_stat_lost"	"0"
"Performance_schema_thread_classes_lost"	"0"
"Performance_schema_thread_instances_lost"	"0"
"Performance_schema_users_lost"	"0"
"Prepared_stmt_count"	"5"
"Qcache_free_blocks"	"1"
"Qcache_free_memory"	"67091584"
"Qcache_hits"	"0"
"Qcache_inserts"	"0"
"Qcache_lowmem_prunes"	"0"
"Qcache_not_cached"	"43704804"
"Qcache_queries_in_cache"	"0"
"Qcache_total_blocks"	"1"
"Queries"	"152755085"
"Questions"	"3"
"Rsa_public_key"	"-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA3OHuVCWw7xsaUrd9wV+v
renfJY2KUSmXvim7GiFn/nS9914Tgp49OljgxHsIlSE8gaVpHObuaG7DRjolq/he
vbK5XjX53rHuumu5uxXhq52lXHvNfqnvPNWzxcpYMFVEh60OgThKawMRdZ7VWO+E
a2aMyK3Ox9MwYBfoldlNnec75WNAx/6HaC3rJWp7BJ4CiJaCKT1m31ue02aIi7ng
8PsUOmEjT9bq26rhsP0dwIG0l6It2pCsvii3WPzM1llyXEhxY8GBzqVtRkmfZWuu
VzLJp6WSGxoySGKDsIB6C53kQsUjHHx+rrqv+QnH6VR0ptaQJ1h4GcrTH/L9U3lx
nwIDAQAB
-----END PUBLIC KEY-----
"
"Select_full_join"	"0"
"Select_full_range_join"	"0"
"Select_range"	"0"
"Select_range_check"	"0"
"Select_scan"	"0"
"Slave_open_temp_tables"	"0"
"Slow_launch_threads"	"0"
"Slow_queries"	"0"
"Sort_merge_passes"	"0"
"Sort_range"	"0"
"Sort_rows"	"0"
"Sort_scan"	"0"
"Ssl_accept_renegotiates"	"0"
"Ssl_accepts"	"445"
"Ssl_callback_cache_hits"	"0"
"Ssl_cipher"	""
"Ssl_cipher_list"	""
"Ssl_client_connects"	"0"
"Ssl_connect_renegotiates"	"0"
"Ssl_ctx_verify_depth"	"18446744073709551615"
"Ssl_ctx_verify_mode"	"5"
"Ssl_default_timeout"	"0"
"Ssl_finished_accepts"	"301"
"Ssl_finished_connects"	"0"
"Ssl_server_not_after"	"Jul 13 05:23:52 2031 GMT"
"Ssl_server_not_before"	"Jul 15 05:23:52 2021 GMT"
"Ssl_session_cache_hits"	"0"
"Ssl_session_cache_misses"	"174"
"Ssl_session_cache_mode"	"SERVER"
"Ssl_session_cache_overflows"	"0"
"Ssl_session_cache_size"	"128"
"Ssl_session_cache_timeouts"	"0"
"Ssl_sessions_reused"	"0"
"Ssl_used_session_cache_entries"	"42"
"Ssl_verify_depth"	"0"
"Ssl_verify_mode"	"0"
"Ssl_version"	""
"Table_locks_immediate"	"11296"
"Table_locks_waited"	"0"
"Table_open_cache_hits"	"0"
"Table_open_cache_misses"	"0"
"Table_open_cache_overflows"	"0"
"Tc_log_max_pages_used"	"0"
"Tc_log_page_size"	"0"
"Tc_log_page_waits"	"0"
"Threads_cached"	"29"
"Threads_connected"	"167"
"Threads_created"	"303"
"Threads_running"	"1"
"Uptime"	"2617138"
"Uptime_since_flush_status"	"2617138"

客户端连接和断开：如 Aborted_clients 和 Aborted_connects。
网络传输：如 Bytes_received 和 Bytes_sent。
SQL 命令统计：如 Com_delete、Com_insert、Com_select 和 Com_update，这些显示了执行的各类 SQL 命令的次数。
连接统计：如 Connections、Threads_connected、Threads_created 和 Threads_running。
InnoDB 存储引擎状态：如 Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、Innodb_row_lock_time_avg 等，这些提供了 InnoDB 特定的状态信息。
查询统计：如 Queries 和 Slow_queries。
服务器运行时间：如 Uptime。

如果 Slow_queries 的值很高，你可能需要查看慢查询日志来确定哪些查询需要优化。如果 Threads_created 的值持续增长，而 Threads_cached 的值保持较低，你可能需要增加 thread_cache_size 的值来缓存更多的线程，从而减少线程创建和销毁的开销。

查看并开启慢查询日志

show variables like “%slow%”;

set global slow_query_log=’ON’;

"log_slow_admin_statements"	"OFF"
"log_slow_slave_statements"	"OFF"
"slow_launch_time"	"2"
"slow_query_log"	"ON"
"slow_query_log_file"	"/www/server/data/mysql-slow.log"

slow_query_log_file 文件内容类似：

# Time: 2024-05-26T10:00:01.234567Z  
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    123  
# Query_time: 5.000234  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 1000000  
SET timestamp=1690492801;  
SELECT * FROM my_big_table WHERE some_column = 'value';  
  
# Time: 2024-05-26T10:10:02.345678Z  
# User@Host: user1[user1] @ somehost [192.168.1.100]  Id:    456  
# Query_time: 3.500098  Lock_time: 0.000034 Rows_sent: 500  Rows_examined: 500000  
SET timestamp=1690493402;  
UPDATE my_other_table SET column1='new_value' WHERE id > 1000;  
  
# ... (其他慢查询记录) ...

时间戳 (# Time:): 记录慢查询发生的时间。
用户与主机信息 (# User@Host:): 执行查询的用户名和主机信息。
查询ID (Id:): 数据库的会话ID。
查询时间 (Query_time:): 查询执行的总时间（秒）。
锁定时间 (Lock_time:): 查询在等待表锁上的时间（秒）。
发送的行数 (Rows_sent:): 查询结果返回给客户端的行数。
检查的行数 (Rows_examined:): 查询期间从表中读取的行数。
SET timestamp: 设置当前的时间戳，这通常用于复制和日志记录。
查询语句: 实际的 SQL 查询语句。

查看当前正在进行的线程，包括线程的状态、是否锁表等

show processlist

+----+------+-----------------+------+---------+------+----------+------------------+  
| Id | User | Host            | db   | Command | Time | State    | Info             |  
+----+------+-----------------+------+---------+------+----------+------------------+  
|  3| root | localhost       | NULL | Query   |    0 | System lock | SHOW PROCESSLIST |  
|  5| user1| somehost:33060  | db1  | Sleep   |  120 |          | NULL             |  
|  8| user2| anotherhost:5678 | db2  | Query   |   30 | Sending data | SELECT * FROM mytable WHERE ... |  
|  9| user3| localhost       | db3  | Query   |    5 | System lock | LOCK TABLES myothertable WRITE |  
+----+------+-----------------+------+---------+------+----------+------------------+

Id: 每个连接的唯一标识符。
User: 执行查询的 MySQL 用户名。
Host: 用户连接的来源主机和端口（如果有的话）。
db: 当前连接的默认数据库（如果没有选择数据库，则为 NULL）。
Command: 当前正在执行的命令类型（如 Query、Sleep、Connect 等）。
Time: 查询已经执行的时间（以秒为单位）。
State: 查询的当前状态（如 Sending data、System lock、Waiting for table metadata lock 等）。
Info: 查询语句（如果可用并且长度不超过 max_allowed_packet 的值）。

第一行显示了执行 SHOW PROCESSLIST 命令的查询本身。
第二行显示了一个处于 Sleep 状态的连接，可能是一个空闲的连接，等待下一个查询。
第三行显示了一个正在执行查询的连接，查询已经执行了 30 秒，并且正在发送数据给客户端。
第四行显示了一个正在锁定表的连接，它可能正在等待其他查询释放锁。

实例：

id           User       Host                     db         Command  Time   Statue  info  
"1319358"	"xiaoxiao"	"196.128.0.102:63323"	""	"Sleep"	"1185"	""	""
"1319363"	"xiaoxiao"	"196.128.0.102:63327"	"xiaoxiao"	"Sleep"	"1173"	""	""
"1319367"	"xiaoxiao"	"196.128.0.102:63335"	"xiaoxiao"	"Sleep"	"1159"	""	""
"1319984"	"xiaoxiao"	"196.128.0.102:62590"	"xiaoxiao"	"Sleep"	"26"	""	""
"1320003"	"xiaoxiao"	"196.128.0.102:63113"	"xiaoxiao"	"Query"	"0"	"starting"	"show processlist"

可以根据这些信息来判断数据库的健康状况、查找性能瓶颈或诊断其他相关问题。例如，你可以查看长时间运行的查询、查找阻塞的锁或确定是否有太多空闲的连接等。

查看索引的使用情况

show status like ‘Handler_read%’;

+-------------------+-------+  
| Variable_name     | Value |  
+-------------------+-------+  
| Handler_read_first | 123   |  
| Handler_read_key   | 56789 |  
| Handler_read_last  | 45    |  
| Handler_read_next  | 34567 |  
| Handler_read_prev  | 78    |  
| Handler_read_rnd   | 987   |  
| Handler_read_rnd_next | 12345 |  
+-------------------+-------+

Handler_read_first：表示按照索引顺序读取的第一行的次数。
Handler_read_key：表示按照键进行读取的次数。
Handler_read_last：表示按照索引顺序读取的最后一个行的次数。
Handler_read_next：表示按照键顺序读取下一行的次数。
Handler_read_prev：表示按照键顺序读取上一行的次数。
Handler_read_rnd：表示按照随机键顺序读取行的次数（通常比按照顺序或键读取更慢）。
Handler_read_rnd_next：表示在表中进行随机读取的行数（例如，在 JOIN 操作中，或者在未使用索引的 WHERE 子句后）。

这些状态值可以帮助你理解查询是如何执行的，特别是当涉及到 InnoDB 表的索引使用情况时。例如，如果你看到 Handler_read_rnd 或 Handler_read_rnd_next 的值非常高，这可能表明你的查询没有有效地使用索引，可能需要重新考虑索引策略或查询优化。

查看执行计划

explain sql语句

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using where |  
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

id: 查询的标识符。
select_type: 查询的类型（例如 SIMPLE 表示不包含子查询或 UNION）。
table: 查询涉及的表。
type: 连接类型。在这个例子中，ALL 表示全表扫描，因为 name 列上没有索引。
possible_keys: 可能使用的索引（但在这个例子中，没有）。
key: 实际使用的索引（在这个例子中，没有使用索引）。
key_len: 使用的索引的长度（在这个例子中，没有使用索引）。
ref: 哪些列或常量被用作索引查找的引用。
rows: MySQL 估计必须检查的行数。
filtered: 表示返回结果的行占需要检查的行的百分比。
Extra: 包含 MySQL 解决查询的详细信息。

为了提高查询性能，你可能会考虑在 name 列上添加索引。但请注意，在添加索引之前，你应该仔细考虑查询的实际需求和数据库的整体性能，因为索引虽然可以提高查询速度，但也会增加插入、更新和删除的开销。

分析表

ANALYZE TABLE 是 MySQL 中的一个命令，用于更新表的统计信息，这些统计信息对于查询优化器非常重要，因为它帮助优化器决定如何最好地执行查询。当你对表进行了大量的插入、删除或更新操作后，表的统计信息可能会变得不准确，这时使用 ANALYZE TABLE 可以重新收集这些信息。

analyze table 表名;

Table               Op         Msg_type     Msg_text
"xx.common_config"	"analyze"	"status"	"OK"

注意：在某些情况下，如使用 InnoDB 存储引擎时，MySQL 会自动更新统计信息，但 ANALYZE TABLE 命令仍然可以强制重新计算这些统计信息，以确保它们是最新的。此外，对于 MyISAM 表，ANALYZE TABLE 还可以用来优化和整理表的物理存储，但 InnoDB 表通常不需要这样的优化。

检查表

CHECK TABLE 是 MySQL 中的一个命令，主要用于检查 MyISAM 存储引擎的表是否有错误，并尝试修复它们（对于 MyISAM 表）。对于 InnoDB 存储引擎，CHECK TABLE 仍然可以执行，但它主要是一个无操作（no-op），因为 InnoDB 会在内部自动进行一致性检查。

check table 表名;

CHECK TABLE my_myisam_table;

Table                   Op         Msg_type     Msg_text
"xx.my_myisam_table"	"check"	"status"	"OK"

OK：表没有问题。
Table is marked as crashed：表已损坏。这通常是由于突然的数据库服务器关闭或磁盘空间问题造成的。
其他可能的错误消息，指示表的具体问题。

如果表有问题，并且你希望 MySQL 尝试自动修复它（对于 MyISAM 表），你可以使用 REPAIR TABLE 命令：

REPAIR TABLE my_myisam_table;

但是，请注意，在修复表之前，最好先备份数据，以防修复过程中发生数据丢失。

对于 InnoDB 表，虽然 CHECK TABLE 命令可以执行，但它通常只会告诉你表是 OK 的，因为 InnoDB 存储引擎会自动维护表的一致性和完整性。如果你怀疑 InnoDB 表有问题，你可能需要使用更复杂的工具或命令，如 SHOW ENGINE INNODB STATUS 来获取更详细的诊断信息。

最后，请确保在执行任何可能影响数据完整性的操作之前都备份了数据。

优化表

optimize table 表名;

OPTIMIZE TABLE 是 MySQL 中的一个命令，用于优化和整理表中的数据存储。这个命令主要对 MyISAM、InnoDB 和 ARCHIVE 存储引擎的表有效，但具体行为可能因存储引擎而异。

MyISAM 表

对于 MyISAM 存储引擎的表，OPTIMIZE TABLE 的主要作用是：

回收空间：如果表中有很多已删除的行或进行了大量的更新操作，这些操作可能会留下未使用的空间。 OPTIMIZE TABLE 会重新组织表的数据和索引，回收这些未使用的空间。
重新组织数据和索引：这会减少表的碎片，从而提高某些查询的性能。

InnoDB 表

对于 InnoDB 存储引擎的表，OPTIMIZE TABLE 的行为略有不同：

重建表和索引：InnoDB 也会重新组织数据和索引，但主要目的是为了提高性能或解决某些特定的问题（如碎片过多）。
回收空间：与 MyISAM 类似，InnoDB 也会回收未使用的空间，但这个过程可能需要更长的时间，并且可能需要更多的磁盘空间（因为 InnoDB 需要先创建一个新的表结构，然后将数据复制过去）。

使用示例，假设我们有一个名为 my_table 的表，我们可以使用以下命令来优化它：

OPTIMIZE TABLE my_table;

执行上述命令后，MySQL 会开始优化 my_table。根据表的大小和存储引擎，这个操作可能需要一些时间。你可以使用 SHOW PROCESSLIST; 命令来查看优化操作的进度。

注意事项

备份数据：在执行任何可能影响数据完整性的操作之前，都应该先备份数据。虽然 OPTIMIZE TABLE 通常是一个安全的操作，但最好还是谨慎行事。
锁定表：在执行 OPTIMIZE TABLE 时，表会被锁定，直到优化完成。这意味着其他用户将无法访问该表，直到优化完成。因此，最好在低流量时段执行此操作。
检查优化结果：执行完 OPTIMIZE TABLE 后，你可以使用 SHOW TABLE STATUS LIKE ‘my_table’; 命令来查看表的当前状态，包括数据长度、索引长度等信息，以验证优化是否成功。
注意碎片问题：虽然 OPTIMIZE TABLE 可以减少碎片，但频繁的插入和删除操作仍然会导致碎片的产生。如果碎片问题严重影响了性能，你可能需要考虑更频繁地执行 OPTIMIZE TABLE，或者重新评估你的数据模型和查询策略。

Table                    Op         Msg_type     Msg_text
"xx.xx_migration"	"optimize"	"note"	"Table does not support optimize, doing recreate + analyze instead"
"xx.xx_migration"	"optimize"	"status"	"OK"

常见锁类型

MySQL中有多种类型的锁，这些锁主要用于控制并发操作对数据库的访问，以避免数据不一致和其他并发问题。以下是MySQL中常见的一些锁类型：

共享锁（Shared Lock）：也被称为读锁（Read Lock）。当一个事务持有一个共享锁时，其他事务也可以持有共享锁，但其他事务不能获取写锁。当一个事务读取数据时，它可以使用共享锁来防止其他事务同时修改这些数据。
排他锁（Exclusive Lock）：也被称为写锁（Write Lock）。当一个事务持有一个排他锁时，其他事务不能获取任何类型的锁，包括共享锁和排他锁。只有拥有排他锁的事务才能修改数据。
意向共享锁（Intent Shared Lock）：这种锁表示事务打算在读取数据时使用共享锁。如果一个事务持有意向共享锁，其他事务也可以获取共享锁，但不能获取排他锁。
意向排他锁（Intent Exclusive Lock）：这种锁表示事务打算在修改数据时使用排他锁。如果一个事务持有意向排他锁，其他事务不能获取共享锁或意向共享锁，但可以获取排他锁。
乐观锁（Optimistic Lock）：这种锁假设在大多数情况下，多个事务不会同时尝试修改同一行数据。因此，它不会立即锁定数据，而是在提交事务时检查是否有其他事务已经修改了数据。如果其他事务已经修改了数据，则事务会失败并回滚。
悲观锁（Pessimistic Lock）：与乐观锁相反，悲观锁假设在大多数情况下，多个事务会尝试修改同一行数据。因此，它会立即锁定数据，防止其他事务修改数据。
间隙锁（Gap Lock）：在InnoDB中，为了实现唯一索引或主键的并发控制，引入了间隙锁。当事务对某个范围进行查询时，为了防止其他事务插入新的记录到这个范围，事务会持有这个范围的间隙锁，从而保护这个范围的数据。

这些锁在MySQL的InnoDB存储引擎中实现。另外，MySQL还提供了多种其他的锁定机制，如页面锁、行级锁等，以满足不同的并发控制需求。

间隙锁

MySQL中的间隙锁（Gap Lock）是一种锁机制，它防止其他事务插入新的记录到特定的范围，从而保护该范围内的数据不被其他事务修改。

在MySQL的InnoDB存储引擎中，为了实现唯一索引或主键的并发控制，引入了间隙锁。当事务对某个范围进行查询时，为了防止其他事务插入新的记录到这个范围，事务会持有这个范围的间隙锁，从而保护这个范围的数据。这样可以避免幻读（Phantom Read）和不可重复读（Non-repeatable Read）的问题。

间隙锁与排他锁（Exclusive Lock）和共享锁（Shared Lock）不同。排他锁会锁定整行数据，而共享锁允许其他事务同时读取数据，但不能修改数据。而间隙锁则是锁定一个范围，防止其他事务插入新的记录到这个范围。

间隙锁的实现是通过在索引上维护一个锁定位图（Locking Bitmap）来实现的。当事务对某个范围进行查询时，会将这个范围的锁定位图标记为锁定状态，表示其他事务不能插入新的记录到这个范围。当事务提交时，会将这个范围的锁定位图标记为解锁状态。

间隙锁可以与排他锁和共享锁一起使用，以实现更细粒度的并发控制。在使用间隙锁时，需要注意避免死锁和其他并发问题。

锁定粒度

按锁定对象的粒度划分，有以下类型锁：

行锁（Row Lock）：行锁是针对数据库中的某一行数据进行锁定，防止其他事务对该行数据进行修改。行锁是最细粒度的锁，可以保证数据的并发一致性。
页面锁（Page Lock）：页面锁是针对数据库中的某一页数据进行锁定，防止其他事务对该页数据进行修改。页面锁的粒度较粗，相比行锁对并发访问的影响较大。页面锁是一种较低级别的锁，它锁定的是物理数据库页。当使用页面锁时，可以防止其他事务修改被锁定的页，但不会阻止查询操作。页面锁主要用于一些低并发的情况，因为它的粒度较大，对并发访问的影响较大。
表锁（Table Lock）：表锁是针对整个表进行锁定，防止其他事务对整个表进行修改。表锁的粒度最粗，对并发访问的影响最大。
事务锁（Transaction Lock）：事务锁是一种内部锁，用于保护事务的并发执行。事务锁会锁定事务涉及的所有数据，确保在事务执行期间，其他事务不能对这些数据进行修改。事务锁与行锁、表锁等不同，它不是用户直接控制的，而是由数据库系统自动管理。

这些锁类型的主要区别在于锁定对象的粒度不同。行锁是最细粒度的锁，页面锁和表锁的粒度较粗，而事务锁是一种内部锁，不直接由用户控制。

在并发控制中，根据不同的业务需求和并发情况选择合适的锁类型是非常重要的。行锁适用于高并发读写的情况，页面锁和表锁适用于读操作较多、写操作较少的情况，而事务锁则用于保护事务的并发执行。

需要注意的是，不同的锁类型可能会产生死锁或其他并发问题，因此在使用时需要谨慎处理，避免过度锁定或冲突。

参阅：https://blog.csdn.net/alexdamiao/article/details/52049993 、 https://www.cnblogs.com/luyucheng/p/6297752.html 、 https://blog.csdn.net/tangkund3218/article/details/47704527

共享锁与乐观锁的区别

共享锁（Shared Lock，简称S锁）与乐观锁在多个方面存在显著的区别，以下是对两者区别的详细解释：

定义与性质：

共享锁：又称为读锁，是一种数据锁，用于不更改或不更新数据的操作（只读操作），如SELECT语句。当一个事务为数据加上共享锁后，其他事务只能对该数据加共享锁，而不能加排他锁（写锁），直到所有的共享锁释放之后其他事务才能对其进行加持写锁。
乐观锁：在操作数据时持乐观态度，认为其他线程不会修改数据，因此不会锁定数据。但在更新数据时会用版本号或者CAS（Compare and Swap）算法判断数据在本次操作过程中是否被更改，如果被更改，则修改失败。

实现方式：

共享锁：由数据库系统直接实现，通过SQL语句（如SELECT … LOCK IN SHARE MODE）来显式地请求和释放。
乐观锁：通常不是数据库系统直接提供的，而是需要开发者在应用层面自行实现。常见的实现方式包括版本号机制和CAS算法。

适用场景：

共享锁：适用于需要并发读取数据，但不修改数据的场景。在共享锁被持有期间，其他事务可以进行读操作，但不能进行写操作。
乐观锁：适用于需要频繁读取数据，但较少更新数据的场景。乐观锁虽然不锁定数据，但在更新数据时需要进行版本号的比较，以判断数据是否被其他事务修改过。

性能影响：

共享锁：在数据被共享锁锁定期间，其他事务无法对其进行写操作，因此可能会影响写操作的并发性能。
乐观锁：虽然不直接锁定数据，但在更新数据时需要检查版本号，这可能会增加一些额外的计算开销。但在更新频率较低的场景下，其整体性能通常较好。

错误处理：

共享锁：当发生死锁等问题时，数据库管理系统通常会自动检测和解决。
乐观锁：在更新数据时如果发现版本号不一致，则需要回滚操作并重新尝试，这可能会增加一些额外的错误处理逻辑。

总结来说，共享锁和乐观锁在定义、性质、实现方式、适用场景、性能影响和错误处理等方面都存在显著的区别。开发者在选择使用哪种锁时需要根据具体的业务场景和需求进行权衡和选择。

22 事务隔离级别

参阅 https://www.cnblogs.com/huanongying/p/7021555.html

事务的基本要素

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

隔离级别

MySQL中事务的隔离级别包括以下四种：

读未提交（Read Uncommitted）：这是事务隔离级别的最低级别。在这种隔离级别下，一个事务可以看到其他未提交事务的变动。这种隔离级别会导致很多问题，如脏读（Dirty Read），即一个事务读取了另一个未提交事务的数据。
读提交（Read Committed）：这是事务隔离级别的第二个级别。在这个隔离级别下，一个事务只能看到其他事务已经提交的变动。这种隔离级别解决了脏读问题，但可能会出现不可重复读（Non-repeatable Read），即在同一事务中，多次读取同一数据返回的结果有所不同。
可重复读（Repeatable Read）：这是事务隔离级别的第三个级别。在这个隔离级别下，同一事务中多次读取同一数据会返回相同的结果，除非本事务自己改变它。这种隔离级别可以解决不可重复读的问题，但仍可能会产生幻读（Phantom Read）现象，即在一个事务内部无法看到其他事务插入的新记录。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）； insert、update和delete会更新版本号，是当前读（当前版本）。
串行化（Serializable）：这是事务隔离级别的最高级别。在这个隔离级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读的问题。但是这种隔离级别效率低下，因为事务通常需要等待前一个事务完成，才能继续执行。

需要注意的是，这些隔离级别是层层递增的，即串行化的隔离级别最高，而读未提交的隔离级别最低。不同的隔离级别可以解决不同的问题，但也会影响数据库的并发性能。在实际应用中，需要根据具体情况选择合适的隔离级别。

事务隔离级别                    脏读     不可重复读     幻读
读未提交（read-uncommitted）     是     是     是
不可重复读（read-committed）     否     是     是
可重复读（repeatable-read）     否     否     是
串行化（serializable）          否     否     否

mysql默认的事务隔离级别为可重复读（Repeatable Read）

获取事务隔离级别：

mysql> select @@tx_isolation;

设置事务隔离级别：

mysql> set session transaction isolation level read uncommitted;

mysql> set session transaction isolation level read committed;

mysql> set session transaction isolation level repeatable read;

mysql> set session transaction isolation level serializable;

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

幻读解读

在MySQL的事务中，幻读（Phantom Read）是一种并发控制问题，是指在事务内部执行相同的查询语句，却返回了不同的结果。在可重复读（Repeatable Read）隔离级别下，事务保持一致性的一个关键假设是，多次相同的查询会返回相同的结果。然而，幻读破坏了这一假设，因为在事务执行过程中，其他事务可以插入新的记录，从而在再次执行查询时返回新的记录，导致结果集发生变化。

举个例子来说明幻读现象：假设有一个表，其中包含职工的工资信息。同时开启两个事务，事务A和事务B。在事务A中，一个职工的工资被修改为1000元。在事务B中，有一个查询语句获取工资大于500元的职工信息，该查询语句返回了事务A修改前的职工信息。当事务A提交后，事务B再次执行相同的查询语句，返回的结果集发生了变化，这就是幻读现象。

为了解决幻读问题，可以将事务的隔离级别提高到串行化（Serializable）级别，这样可以确保事务的执行是串行的，避免了幻读和其他并发控制问题。但是，这种隔离级别的并发性能较低，因为事务通常需要等待前一个事务完成才能继续执行。因此，在实际应用中，需要权衡隔离级别和并发性能，选择合适的隔离级别来解决幻读问题。

解决办法参阅 https://baijiahao.baidu.com/s?id=1759957900969624056

方法一：采用锁机制

在MySQL中，可以使用锁机制解决幻读问题。锁机制可以将一些资源标记为独占状态，防止其他事务对该资源进行修改，从而确保数据的一致性。

MySQL中提供了两种锁机制：行锁和表锁。行锁可以锁定指定行，而表锁可以锁定整个表。如果只需要保护某一行不被其他事务修改，可以采用行锁；如果需要保护整个表不被修改，可以采用表锁。

在使用锁机制时，需要注意以下几点：

尽量缩小锁的范围，避免对整个表进行锁定；
锁定的时间应该尽量短，以免影响其他事务的执行；
避免死锁的产生。

方法二：采用MVCC机制

MySQL中还提供了MVCC（多版本并发控制）机制，可以有效地解决幻读问题。MVCC机制会为每个事务创建一个可见性视图，用于记录每个事务可以看到的数据版本。当一个事务查询数据时，会根据自己的可见性视图获取相应的数据版本，从而避免幻读问题的出现。

在使用MVCC机制时，需要注意以下几点：

尽量使用InnoDB存储引擎，因为MyISAM不支持MVCC；
在事务中执行多个查询时，要使用相同的一致性级别，以保证数据的一致性；
避免长时间的事务执行，以免占用过多的资源。

幻读解决案例

下面我们将通过一个实际的案例来说明如何使用锁机制和MVCC机制解决幻读问题。

假设我们有一个名为orders的表，包含如下字段：

id：订单ID
user_id：用户ID
amount：订单金额

现在我们有两个并发事务，分别为事务A和事务B。事务A要插入一条记录，事务B要查询所有用户的订单总金额。如果不采取任何措施，就可能出现幻读问题。

一、使用锁机制解决幻读问题

我们可以在事务A执行插入操作时，对整个表进行排它锁定。这样一来，事务B就无法执行查询操作，直到事务A释放锁。

以下是示例代码：

-- 事务A
BEGIN;
SELECT * FROM orders WHERE user_id = 1 FOR UPDATE;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;

-- 事务B
BEGIN;
SELECT SUM(amount) FROM orders;
COMMIT;

在事务A执行SELECT * FROM orders WHERE user_id = 1 FOR UPDATE;语句时，会对orders表进行排它锁定。这样一来，事务B执行查询操作时就无法读取数据，直到事务A释放锁。这样就可以避免幻读问题的出现。

二、使用MVCC机制解决幻读问题

我们可以在事务B执行查询操作时，指定一致性级别为“可重复读”，这样就可以避免事务A对数据的修改对事务B造成干扰。

以下是示例代码：

-- 事务A
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT SUM(amount) FROM orders;
COMMIT;

在事务B执行查询操作时，通过SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;语句设置一致性级别为“可重复读”。这样一来，事务B就能够读取到之前的数据版本，而不受事务A的修改影响，从而避免幻读问题的出现。

23 MySQL 数据库设计总结

参阅 https://cloud.tencent.com/developer/article/1004367

规则1：一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。

注意：MyISAM存储引擎 B-tree索引有一个很大的限制：参与一个索引的所有字段的长度之和不能超过1000字节。另外MyISAM数据和索引是分开，而InnoDB的数据存储是按聚簇(cluster)索引有序排列的，主键是默认的聚簇(cluster)索引，因此MyISAM虽然在一般情况下，查询性能比InnoDB高，但InnoDB的以主键为条件的查询性能是非常高的。

规则2：命名规则。

数据库和表名应尽可能和所服务的业务模块名一致
服务与同一个子模块的一类表应尽量以子模块名(或部分单词)为前缀或后缀
表名应尽量包含与所存放数据对应的单词
字段名称也应尽量保持和实际数据相对应
联合索引名称应尽量包含所有索引键字段名或缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致，并尽量包含一个类似idx的前缀或后缀，以表明期对象类型是索引。
约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表明各自的关系

规则3：数据库字段类型定义

经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段，如用TIMESTAMP(4个字节，最小值1970-01-01 00:00:00)代替Datetime（8个字节，最小值1001-01-01 00:00:00）,通过整型替代浮点型和字符型
变长字段使用varchar，不要使用char
对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中

规则4：业务逻辑执行过程必须读到的表中必须要有初始的值。避免业务读出为负或无穷大的值导致程序失败

规则5：并不需要一定遵守范式理论，适度的冗余，让Query尽量减少Join

规则6：访问频率较低的大字段拆分出数据表。有些大字段占用空间多，访问频率较其他字段明显要少很多，这种情况进行拆分，频繁的查询中就不需要读取大字段，造成IO资源的浪费。

规则7：大表可以考虑水平拆分。大表影响查询效率，根据业务特性有很多拆分方式，像根据时间递增的数据，可以根据时间来分。以id划分的数据，可根据id%数据库个数的方式来拆分。

一、数据库索引

规则8：业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的，业务不需要的不要建索引，不允许在联合索引（或主键）中存在多于的字段。特别是该字段根本不会在条件语句中出现。

规则9：唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引，不能唯一确定一条记录，为了提高查询效率建普通索引

规则10：业务使用的表，有些记录数很少，甚至只有一条记录，为了约束的需要，也要建立索引或者设置主键。

规则11：对于取值不能重复，经常作为查询条件的字段，应该建唯一索引(主键默认唯一索引)，并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。

规则12：对于经常查询的字段，其值不唯一，也应该考虑建立普通索引，查询语句中该字段条件置于第一个位置，对联合索引处理的方法同样。

规则13：业务通过不唯一索引访问数据时，需要考虑通过该索引值返回的记录稠密度，原则上可能的稠密度最大不能高于0.2，如果稠密度太大，则不合适建立索引了。

当通过这个索引查找得到的数据量占到表内所有数据的20%以上时，则需要考虑建立该索引的代价，同时由于索引扫描产生的都是随机I/O，生其效率比全表顺序扫描的顺序I/O低很多。数据库系统优化query的时候有可能不会用到这个索引。

规则14：需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

注意：索引的顺势不正确也可能导致严重的后果。

规则15：表中的多个字段查询作为查询条件，不含有其他索引，并且字段联合值不重复，可以在这多个字段上建唯一的联合索引，假设索引字段为 (a1,a2,…an),则查询条件(a1 op val1,a2 op val2,…am op valm)m<=n,可以用到索引，查询条件中字段的位置与索引中的字段位置是一致的。

规则16：联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))

联合索引中的字段应尽量满足过滤数据从多到少的顺序，也就是说差异最大的字段应该房子第一个字段
建立索引尽量与SQL语句的条件顺序一致，使SQL语句尽量以整个索引为条件，尽量避免以索引的一部分(特别是首个条件与索引的首个字段不一致时)作为查询的条件
Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40为条件可以用到此联合索引；而这些语句where b=10,where c=221,where b>=12 and c=2则无法用到这个联合索引。
当需要查询的数据库字段全部在索引中体现时，数据库可以直接查询索引得到查询信息无须对整个表进行扫描(这就是所谓的key-only)，能大大的提高查询效率。
当a，ab，abc与其他表字段关联查询时可以用到索引
当a，ab，abc顺序而不是b，c，bc，ac为顺序执行Order by或者group不要时可以用到索引
以下情况时，进行表扫描然后排序可能比使用联合索引更加有效
a.表已经按照索引组织好了
b.被查询的数据站所有数据的很多比例。

规则17：重要业务访问数据表时。但不能通过索引访问数据时，应该确保顺序访问的记录数目是有限的，原则上不得多于10。

二、Query语句与应用系统优化

规则18：合理构造Query语句

Insert语句中，根据测试，批量一次插入1000条时效率最高，多于1000条时，要拆分，多次进行同样的插入，应该合并批量进行。注意query语句的长度要小于mysqld的参数 max_allowed_packet
查询条件中各种逻辑操作符性能顺序是and，or，in，因此在查询条件中应该尽量避免使用在大集合中使用in
永远用小结果集驱动大记录集，因为在mysql中，只有Nested Join一种Join方式，就是说mysql的join是通过嵌套循环来实现的。通过小结果集驱动大记录集这个原则来减少嵌套循环的循环次数，以减少IO总量及CPU运算次数
尽量优化Nested Join内层循环。
只取需要的columns，尽量不要使用select *
仅仅使用最有效的过滤字段，where 字句中的过滤条件少为好
尽量避免复杂的Join和子查询
Mysql在并发这块做得并不是太好，当并发量太高的时候，整体性能会急剧下降，这主要与Mysql内部资源的争用锁定控制有关，MyIsam用表锁，InnoDB好一些用行锁。

规则19：应用系统的优化

合理使用cache，对于变化较少的部分活跃数据通过应用层的cache缓存到内存中，对性能的提升是成数量级的。
对重复执行相同的query进行合并，减少IO次数。
事务相关性最小原则

24 获取数据表随机10条

应用中我们经常会遇到在 user 表随机调取 10 条数据来展示的情况，简述你如何实现该功能。

参阅 https://www.cnblogs.com/riasky/p/3367558.html https://www.jb51.net/article/48801.htm

SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 10;

这样获取的是连续的，下面这样来获取不连续的10条。

SELECT * 
FROM `table` AS t1 
LEFT JOIN (
    SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id 
    from `table` limit 50) AS t2 
ON t1.id=t2.id
ORDER BY t1.id LIMIT 10;

解释：

SELECT ROUND(RAND() * 
    ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))
    +(SELECT MIN(id) FROM `table`)
) AS id 
from `table` limit 50)

这样会获取50个随机数字，然后ON t1.id=t2.id会挑选出不大于50行的随机数据，然后取10条就好了。

25 MyISAM与InnoDB的区别

参阅 https://blog.csdn.net/chajinglong/article/details/56666771

1、存储结构

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

2、存储空间

MyISAM：可被压缩，存储空间较小。

InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小

3、事务处理

MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不支持外键、不提供事务支持。 InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能。

SELECT、UPDATE、INSERT、Delete操作

如果执行大量的SELECT，MyISAM是更好的选择。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。 DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。而MyISAM则是重新建立表。在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

4、表的具体行数

MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出该值。

InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where后，myisam和innodb处理的方式都一样。

5、全文索引

MyISAM：支持 FULLTEXT类型的全文索引。不支持中文。

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

6、表锁差异

MyISAM：只支持表级锁，只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

一般来说：

MyISAM适合：

做很多count 的计算；
插入不频繁，查询非常频繁；
没有事务。

InnoDB适合：

可靠性要求比较高，或者要求事务；
表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

总结

两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁。而MyISAM不支持，所以MyISAM往往就容易被人认为只适合在小项目中使用。

作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，如果数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是首选。

原因如下：

1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

3、经常隔1、2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。

4、从接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。

5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb，但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。

26 怎么排查数据库死锁

数据库如果出现了死锁，你怎么排查，怎么判断出现了死锁？

参阅 https://www.cnblogs.com/huanyou/p/5775965.html

数据库死锁的检查方法

一、数据库死锁的现象

程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。

二、死锁的原理

当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。

三、死锁的定位方法

Oracle数据库

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

1）用dba用户执行以下语句

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。

2）用dba用户执行以下语句，可以查看到被死锁的语句。

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

四、死锁的解决方法

一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

1）查找死锁的进程：

sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：

select pro.spid from v$session ses,
v$process pro where ses.sid=XX and 
ses.paddr=pro.addr;

其中sid用死锁的sid替换:

exit 
ps -ef|grep spid

其中spid是这个进程的进程号，kill掉这个Oracle进程。

MySQL数据库

查看死锁：

1、查看正在进行中的事务
SELECT FROM information schema.INNODB TRX
2、查看正在锁的事务
SELECT FROM INFORMATION SCHEMA.INNODB LOCKS;
3、查看等待锁的事务
SELECT FROM INFORMATION SCHEMA.INNODB LOCK WAITS;
4、查询是否锁表
SHOW OPEN TABLES where In use 0;
在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。
5、查看最近死锁的日志
show engine innodb status

解除死锁：

等待超时：让其中一个事务等待一段时间后再尝试，以希望其他事务会在这段时间内完成。这通常不是最佳解决方案，因为它会引入延迟，并且不一定会成功。
优化查询：通过优化查询，降低死锁发生的可能性，例如通过合理使用索引、减少事务中锁的范围等。
调整事务顺序：如果可能的话，可以通过按照相同的顺序访问资源来降低死锁的风险。
终止一个事务：您可以手动终止其中一个事务，以解除死锁。通常选择终止最小程度影响系统的事务。

其他方式，最简单粗暴的方式，那就是找到进程id之后，直接干掉。

// 查看当前正在进行中的进程
show processlist
// 也可以使用
SELECT FROM information schema.INNODB TRX;
// 杀掉进程对应的进程id
kill id
// 验证(k后再看是否还有锁)
SHOW OPEN TABLES where In use 0;

27 B树、B-树、B+树、B*树都是什么

参阅 https://blog.csdn.net/qq_23217629/article/details/52510485、 https://blog.junphp.com/details/630.jsp

如何把一张表数据快速导入另一张表

在MySQL中，你可以使用INSERT INTO ... SELECT语句来将一个表中的数据快速导入到另一个表中。

1、INSERT INTO方式

INSERT INTO table2 (column1, column2, column3) SELECT column1, column2, column3 FROM table1;

2、INSERT INTO

INSERT INTO new_table SELECT * FROM old_table;

以下是一个示例：

INSERT INTO table2 (column1, column2, column3, ...)  
SELECT column1, column2, column3, ...  
FROM table1  
WHERE condition;

在上面的示例中，table2是目标表，即要将数据导入的表，而table1是源表，即包含要导入的数据的表。 column1, column2, column3, …是要导入的列的名称，可以根据实际情况进行更改。 WHERE condition是可选的，用于筛选要导入的数据。

请注意，在执行此操作之前，你需要确保目标表的结构与源表的结构匹配，即目标表中的列与源表中的列对应，并且数据类型也匹配。如果目标表中的列数比源表中的列数少，则只需在INSERT INTO语句中指定目标表中存在的列。

使用CREATE TABLE AS SELECT方式：

CREATE TABLE table2 AS SELECT column1, column2, column3 FROM table1;

如何把大量数据一次写入数据表

1、使用INSERT INTO语句的批量插入功能。例如：

INSERT INTO table_name (column1, column2, column3, ...)  
VALUES   
    (value1, value2, value3, ...),  
    (value1, value2, value3, ...),  
    ...  
    (value1, value2, value3, ...);

其中，每个VALUES后面跟随多个值的括号，每个括号内表示一条数据的值，用逗号分隔。通过这种方式，你可以一次性插入多条数据。

2、使用LOAD DATA INFILE语句批量插入数据。这种方法适用于从文件中读取数据并插入到MySQL表中。例如，假设你有一个名为data.csv的文件，包含以下数据：Doe,John,25。你可以使用以下LOAD DATA INFILE语句将数据插入到名为users的表中：

LOAD DATA INFILE 'data.csv' INTO TABLE users  
FIELDS TERMINATED BY ','   
ENCLOSED BY '"'  
LINES TERMINATED BY '\n'  
IGNORE 1 ROWS;

这将把文件data.csv中的数据插入到users表中。请注意，这个例子假设文件中的每一行都有多个字段，并且每个字段都用逗号分隔，而且第一行包含了列名。如果你的文件结构和数据不同，你可能需要调整这个命令。

参考资料

SQL面试题，快问快答！ https://mp.weixin.qq.com/s/I8IcSbLV7tor8L84x19A4g

Mysql联合索引最左匹配原则 https://segmentfault.com/a/1190000015416513

数据库为什么要用B+树结构 https://blog.csdn.net/bigtree_3721/article/details/73151472

正文