一名数据库爱好者的专栏

os: centos 7.6db: mysql 8.0版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # yum list installed |grep -i mysqlmysql-community-client.x86_64 8.0.25-1.el7 @mysql80-communitymysql-community-client-plugins.x8

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。Record Locks锁定一个记录上的索引，而不是记录本身。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。Gap Locks锁定索

https://developer.aliyun.com/article/772913?spm=a2c6h.12873581.0.0.3a1c51benXmtux

os: centos 7.4db: mysql 8.0version# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # mysqlroot@localhost 08:03:03 [test]> select version();+-----------+| version() |+-----------+| 8.0.20 |+-----------+1 row in set (0.00 sec

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is u

mysql 5.7 的 create index 就已经是 online了。create index idx_index_name on table_name(column_name) ;5.78.0参考:

os: centos 7.4db: mysql 5.7.28root@localhost 15:45:40 [test]> select * from information_schema.innodb_trx;Empty set (0.00 sec)root@localhost 15:55:03 [test]> select * from information_schema.innodb_locks;Empty set, 1 warning (0.00 sec)root@l

mysql 收集表的统计信息比较个性查询 information_schema.tablesselect * from information_schema.TABLES tawhere 1=1 and ta.table_name = 'tmp_t0';show table statusshow table status like '%tmp_t0%';anlyze tableanalyze table tmp_t0;重启 mysql 实例...

create table max_rows 的限制https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.htmlhttps://dev.mysql.com/doc/refman/8.0/en/table-size-limit.htmlhttps://dev.mysql.com/doc/refman/8.0/en/column-count-limit.htmlhttps://dev.mysql.com/doc/refman/8.0/en/cr.

os: centos 7.4.1708db: mysql 8.0.20create tablemysql> show create table part_range\G*************************** 1. row *************************** Table: part_rangeCreate Table: CREATE TABLE `part_range` ( `id` int NOT NULL, `fname` varc

os: centos 7.4.1708db: mysql 8.0.20版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i mysql80mysql-community-client.x86_64 8.0.20-1.el7 @mysql80-communitymysql-community-co

参考:https://www.sohu.com/a/346770497_427741

os: centos 7.4.1708db: mysql 8.0.20版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i mysql80mysql-community-client.x86_64 8.0.20-1.el7 @mysql80-communitymysql-community-co

数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。聚集索引(clustered index)B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。MySQL InnoDB 类型的表必须明确声明一个主键。辅助索引(secondary index)辅助索引的叶子节点...

二叉树基本结构，指每个节点最多含有两个子树的树结构。特点：二叉树的每个节点至多有两个子节点二叉树左边节点的值小于右边节点的值，且小于根节点的值。平衡二叉树(AVL tree)由二叉树演变而来二叉树随着深度增加时，查询的成本就会加大。为了提高查询性能，出现了平衡二叉树。特点：平衡二叉树左右两个子树的高度差的绝对值不大于1，且左右两个子树都必须是平衡二叉树。平衡二叉树经常需要...

os: centos 7.4.1708db: mysql 8.0.19版本[root@node2 ~]# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core)[root@node2 ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.You...

os: centos 7.4.1708db: mysql 8.0.19任何数据库都需要对锁进行监控，尤其是长时间的锁，mysql 也不例外。mysql 通过 show full processlist、show engine innodb status 可以查看到。其实通过 information_schema.innodb_trx 更容易监控到锁的情况。版本[root@node2 ~...

os: centos 7.4db: mysql 8.0.18ERROR 1074 (42000): Column length too big for column ‘c0’ (max = 16383); use BLOB or TEXT insteadERROR 1118 (42000): Row size too large. The maximum row size for the u...

创建表CREATE TABLE `NewTable` ( `Column1` varchar(100) DEFAULT NULL, `Column2` varchar(100) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4表的备注信息-- ALTER TABLE peiybdb.NewTable COMMENT='tes...