引言

在 MySQL 5.7.23 之前的版本中，评估类似 DDL（数据定义语言）的操作时需要谨慎。这些操作可能会被评估为瞬间操作，但实际执行时可能需要很长时间，这会导致超出维护窗口，甚至更大的故障。

问题模拟

5.7.22 版本

考虑以下建表语句： sql CREATE TABLE testmy ( id INT(11) DEFAULT NULL, a VARCHAR(24) DEFAULT NULL COMMENT 'test1', KEY `a` (a) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 请注意，字段 a 包含了一个索引，这是触发此问题的一个必要条件。 表数据量： sql mysql> SELECT COUNT() FROM testmy; +----------+ | count() | +----------+ | 262144 | +----------+ 1 row in set (5.17 sec) 执行以下 DDL 语句： sql ALTER TABLE testmy MODIFY a VARCHAR(30) COMMENT 'test1111'; 此 DDL 语句主要完成： 将字段 a 的长度从 24 个字符扩展到 30 个字符 更新字段 a 的注释 按照常规，此 DDL 应该只修改元数据，因此应该瞬间完成，但实际上在 5.7.22 版本中，此语句会重建索引 a，耗时如下： sql mysql> ALTER TABLE testmy MODIFY a VARCHAR(30) COMMENT 'test1'; Query OK, 0 rows affected (2.50 sec) Records: 0 Duplicates: 0 Warnings: 0 显然，索引的重建导致了如此高的耗时。而在 MySQL 5.7 的新版本或 MySQL 8.0 中，测试此语句时可以瞬间完成。

官方文档说明

MySQL 官方文档指出，对于 VARCHAR 类型，只要字符集字节数乘以字符数量不超过 256，那么扩展 VARCHAR 字段的长度只修改元数据，不会重建索引。

问题分析

既然不符合官方文档的说明，那么这个问题肯定是某种 Bug 导致的。 在 MySQL 中执行 DDL 操作时，需要比较更改部分和现有数据字典中表定义的差异，然后根据这些差异定义操作方式，再根据操作方式判断哪种 DDL 方式比较合适。有关定义操作方式的部分来自函数 fill_alter_inplace_info，而函数中会根据新表的索引和老表的索引字段的长度判断是否需要删除索引和新建索引，代码体现如下： c++ / Step through all keys of the old table and search matching new keys. / for (table_key = table->key_info; table_key < table_key_end; table_key++) { // Skip renamed keys. if (table_key->flags & HA_KEY_RENAMED) continue; new_key = find_key_cs(table_key->name, ha_alter_info->key_info_buffer, new_key_end); // 在新的定义中是否包含这个索引 if (new_key == NULL) { // Matching new key not found. This means the key should be dropped. ha_alter_info->add_dropped_key(table_key); // 加入到 drop key buffer } else if (has_index_def_changed(ha_alter_info, table_key, new_key)) { // Matching new key found but key def has changed. // This means the key should be dropped and recreated. ha_alter_info->add_dropped_key(table_key); ha_alter_info->add_created_key(new_key); } } 对于列 a 的 VARCHAR 字段，索引定义的唯一差异是字段长度，但由于字符集字节数乘以字符数量没有超过 256，因此不应该重建索引。

修复

此问题已在 MySQL 5.7.23 中修复。在修复版本中，代码已更新，以便在扩展 VARCHAR 字段的长度时正确处理索引。

影响

此问题可能会导致在评估 DDL 操作时出现意外的结果，从而导致维护窗口超出或更大的故障。

建议

对于 MySQL 5.7.23 之前的版本，在评估类似 DDL 操作时需要谨慎。最好在非生产环境中测试此类操作，以避免对生产系统造成意外的影响。 建议升级到 MySQL 5.7.23 或更高版本，以解决此问题。

参考

[MySQL Bug 76261]([MySQL 5.7 Reference Manual - ALTER TABLE Syntax](

---

mySQL的索引功能

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。 索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。 每修改数据记录，索引就必须刷新一次。 为了在某种程度上弥补这一缺陷，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。 这个选项的作用是暂时制止 MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。 在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。 另外，索引还会在硬盘上占用相当大的空间。 因此应该只为最经常查询和最经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。 从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但 MySQL 把同一个数据表里的索引总数限制为16个。 1．InnoDB 数据表的索引与 InnoDB数据表相比，在 InnoDB 数据表上，索引对 InnoDB 数据表的重要性要大得多。 在 InnoDB 数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。 “数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。 这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE 命令以及 INSERT、UPDATE 和 DELETE 命令。 出于效率方面的考虑，InnoDB 数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。 显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。 2．限制如果 WHERE 子句的查询条件里有不等号（WHERE coloum !=），MySQL 将无法使用索引。 类似地，如果 WHERE 子句的查询条件里使用了函数（WHERE DAY（column）=），MySQL 也将无法使用索引。 在 JOIN 操作中（需要从多个数据表提取数据时），MySQL 只有在主键和外键的数据类型相同时才能使用索引。 如果 WHERE 子句的查询条件里使用比较操作符 LIKE 和 REGEXP，MySQL 只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。 比如说，如果查询条件是 LIKE abc%‘，MySQL 将使用索引；如果查询条件是 LIKE %abc’，MySQL 将不使用索引。 在 ORDER BY 操作中，MySQL 只有在排序条件不是一个查询条件表达式的情况下才使用索引。 （虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快 ORDER BY 方面也没什么作用）。 如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。 比如说，如果某个数据列里包含的净是些诸如 “0/1” 或 “Y/N” 等值，就没有必要为它创建一个索引。 1．普通索引普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。 因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。 只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。 2．唯一索引普通索引允许被索引的数据列包含重复的值。 比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。 这么做的好处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL 会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL 将拒绝插入那条新记录。 也就是说，唯一索引可以保证数据记录的唯一性。 事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。 3．主索引在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。 主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。 4．外键索引如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。 5．复合索引索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。 这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。 如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。 不过，这种用法仅适用于在复合索引中排列在前的数据列组合。 比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。 在为 CHAR 和 VARCHAR 类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数（这个数字必须小于这个字段所允许的最大字符个数）。 这么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。 在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15 个字符已经足以把搜索范围缩小到很少的几条数据记录了。 在为 BLOB 和 TEXT 类型的数据列创建索引时，必须对索引的长度做出限制；MySQL 所允许的最大索引全文索引文本字段上的普通索引只能加快对出现在字段内容最前面的字符串（也就是字段内容开头的字符）进行检索操作。 如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。 这种检索往往以的形式出现，这对 MySQL 来说很复杂，如果需要处理的数据量很大，响应时间就会很长。 这类场合正是全文索引（full-textindex）可以大显身手的地方。 在生成这种类型的索引时，MySQL 将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。 全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：ALTER TABLE tablename ADD FULLTEXT（column1，column2）有了全文索引，就可以用 SELECT 查询命令去检索那些包含着一个或多个给定单词的数据记录了。 下面是这类查询命令的基本语法：SELECT * FROM tablenameWHERE MATCH (column1,column2) AGAINST(word1,word2,word3)上面这条命令将把 column1 和 column2 字段里有 word1、word2 和 word3 的数据记录全部查询出来。 注解：InnoDB 数据表不支持全文索引。 只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。 如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。 只有当数据库里的记录超过了 1000 条、数据总量也超过了 MySQL 服务器上的内存总量时，数据库的性能测试结果才有意义。 在不确定应该在哪些数据列上创建索引的时候，人们从 EXPLAIN SELECT 命令那里往往可以获得一些帮助。 这其实只是简单地给一条普通的 SELECT 命令加一个 EXPLAIN 关键字作为前缀而已。 有了这个关键字，MySQL 将不是去执行那条 SELECT 命令，而是去对它进行分析。 MySQL 将以表格的形式把查询的执行过程和用到的索引等信息列出来。 在 EXPLAIN 命令的输出结果里，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。 type列指定了本数据表与其它数据表之间的关联关系（JOIN）。 在各种类型的关联关系当中，效率最高的是 system，然后依次是 const、eq_ref、ref、range、index 和 All（All 的意思是：对应于上一级数据表里的每一条记录，这个数据表里的所有记录都必须被读取一遍——这种情况往往可以用一索引来避免）。 possible_keys 数据列给出了 MySQL 在搜索数据记录时可选用的各个索引。 key 数据列是 MySQL 实际选用的索引，这个索引按字节计算的长度在 key_len 数据列里给出。 比如说，对于一个 INTEGER 数据列的索引，这个字节长度将是4。 如果用到了复合索引，在 key_len 数据列里还可以看到 MySQL 具体使用了它的哪些部分。 作为一般规律，key_len 数据列里的值越小越好。 ref 数据列给出了关联关系中另一个数据表里的数据列的名字。 row 数据列是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。 row 数据列里的所有数字的乘积可以大致了解这个查询需要处理多少组合。 最后，extra 数据列提供了与 JOIN 操作有关的更多信息，比如说，如果 MySQL 在执行这个查询时必须创建一个临时数据表，就会在 extra 列看到 usingtemporary 字样。

pgsql的主键存储方式

PostgreSQL的稳定性极强，Innodb等索引在崩溃，断电之类的灾难场景下 抗击打能力有了长足进步，然而很多 MqSQL用户 都遇到过 Server级的数据库丢失的场景 -- MySQL系统库是 MyISAM，相比之下，PG数据库这方面要更好一些。 任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以位置双曲线甚至对数曲线，到 顶峰之后不在下降，而MySQL明显出现一个波峰后下滑（5.5版本 之后，在企业级版本中有个插件可以改善很多，不过需要付费）。 PG多年来在 GIS（地理信息）领域处于优势地位，因为它有丰富的几何类型，PG有大量字典，数组，bitmap等数据类型，相比之下 MySQL就差很多， Instagram就是因为 PG的空间数据库 扩展 POSTGIS远远强于 MySQL的 my spatial 而采用 PgSQL的。 PG的“无锁定”特性非常突出，甚至包括 vacuum这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。 PG可以使用函数 和 条件索引，这使得 PG数据库的调优非常灵活， MySQL就没有这个功能，条件索引在 web应用中 很重要。 PG有极其强悍的 SQL编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里叫Window函数），还可以用多种语言来写存储过程，对于 R的支持也很好。 这一点MySQL就差很多，很多分析功能都不支持，腾讯内部的存储主要是 MySQL，但是数据分析主要是 Hadoop+ PgSQL。 PG的有多种集群架构可以选择，plproxy可以之hi语句级的镜像或分片，slony可以进行字段级的同步配置，standby 可以构建 WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便。 一般关系型数据库字符串有长度限制 8k 左右，无限长 TEXT类型的功能受限，只能作为外部大数据访问。 而 PG 的 TEXT 类型 可以直接访问且无长度限制， SQL语法内置 正则表达式，可以索引，还可以全文检索，或使用 xml xpath。 用 PG的话，文档数据库都可以省了。 PgSQL对于 numa 架构的支持比 MySQL强一些，比 MySQL对于读的性能更好一些， PgSQL提交可以完全异步提交，而 MySQL的内存表不够实用（因为表锁的原因）。 pgsql除了存储正常的数据类型外，还支持存储array，不管是一维数组还是多维数组均支持。 json和jsonb，相比使用 text存储要高效很多。 json和 jsonb在更高的层面上看起来几乎是一样的，但是存储实现上是不同的。 json存储完的文本，json列会每次都解析存储的值，它不支持索引，但 可以为创建表达式索引。 jsonb存储的二进制格式，避免了重新解析数据结构。 它支持索引，这意味着 可以不使用指定索引就能查询任何路径。 当我们比较写入数据速度时，由于数据存储 的方式的原因，jsonb会比 json 稍微的慢一点。 json列会每次都 解析存储的值，这意味着键的顺序要和输入的 时候一样。 但是 jsonb不同，以二进制格式存储且不保证键的顺序。 因此如果有软件需要依赖键的顺序，jsonb可能不是最佳选择。 使用 jsonb的优势还在于可以轻易的整合关系型数据和非关系型 数据 ，PostgreSQL对于 mongodb这类数据库是一个不小的威胁，毕竟如果一个表中只有一列数据的类型是半结构化的，没有必要为了迁就它而整个表的设计都采用 schemaless的结构。 1. CPU限制PGSQL没有CPU核心数限制，有多少CPU核就用多少MySQL能用128核CPU，超过128核用不上 2. 配置文件参数PGSQL一共有255个参数，用到的大概是80个，参数比较稳定，用上个大版本配置文件也可以启动当前大版本数据库 MySQL一共有707个参数，用到的大概是180个，参数不断增加，就算小版本也会增加参数，大版本之间会有部分参数不兼容情况3. 第三方工具依赖情况PGSQL只有高可用集群需要依靠第三方中间件，例如：patroni+etcd、repmgr MySQL大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，学习成本高，高可用集群也需要第三方中间件，官方MGR集群还没成熟4. 高可用主从复制底层原理PGSQL物理流复制，属于物理复制，跟SQL Server镜像/AlwaysOn一样，严格一致，没有任何可能导致不一致，性能和可靠性上，物理复制完胜逻辑复制，维护简单 MySQL主从复制，属于逻辑复制，（sql_log_bin、binlog_format等参数设置不正确都会导致主从不一致）大事务并行复制效率低，对于重要业务，需要依赖 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比较和修复主从一致主从复制出错严重时候需要重搭主从MySQL的逻辑复制并不阻止两个不一致的数据库建立复制关系5. 从库只读状态PGSQL系统自动设置从库默认只读，不需要人工介入，维护简单 MySQL从库需要手动设置参数super_read_only=on，让从库设置为只读，super_read_only参数有bug，链接：版本分支PGSQL只有社区版，没有其他任何分支版本，PGSQL官方统一开发，统一维护，社区版有所有功能，不像SQL Server和MySQL有标准版、企业版、经典版、社区版、开发版、web版之分国内外还有一些基于PGSQL做二次开发的数据库厂商，例如：Enterprise DB、瀚高数据库等等，当然这些只是二次开发并不算独立分支 MySQL由于历史原因，分裂为三个分支版本，MariaDB分支、Percona分支 、Oracle官方分支，发展到目前为止各个分支基本互相不兼容Oracle官方分支还有版本之分，分为标准版、企业版、经典版、社区版7. SQL特性支持PGSQLSQL特性支持情况支持94种，SQL语法支持最完善，例如：支持公用表表达式（WITH查询） MySQLSQL特性支持情况支持36种，SQL语法支持比较弱，例如：不支持公用表表达式（WITH查询） 关于SQL特性支持情况的对比，可以参考：主从复制安全性PGSQL同步流复制、强同步（remote apply)、高安全，不会丢数据PGSQL同步流复制：所有从库宕机，主库会罢工，主库无法自动切换为异步流复制（异步模式），需要通过增加从库数量来解决，一般生产环境至少有两个从库手动解决：在PG主库修改参数synchronous_standby_names =，并执行命令： pgctl reload ，把主库切换为异步模式主从数据完全一致是高可用切换的第一前提，所以PGSQL选择主库罢工也是可以理解 MySQL增强半同步复制 ，mysql5.7版本增强半同步才能保证主从复制时候不丢数据mysql5.7半同步复制相关参数：参数rpl_semi_sync_master_wait_for_slave_count 等待至少多少个从库接收到binlog，主库才提交事务，一般设置为1，性能最高参数rpl_semi_sync_master_timeout 等待多少毫秒，从库无回应自动切换为异步模式，一般设置为无限大，不让主库自动切换为异步模式所有从库宕机，主库会罢工，因为无法收到任何从库的应答包手动解决：在MySQL主库修改参数rpl_semi_sync_master_wait_for_slave_count=09. 多字段统计信息PGSQL支持多字段统计信息 MySQL不支持多字段统计信息10. 索引类型PGSQL多种索引类型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表达式索引） MySQLbtree 索引，全文索引（低效），表达式索引(需要建虚拟列)，hash 索引只在内存表11. 物理表连接算法PGSQL支持nested-loop join 、hash join 、merge join MySQL只支持nested-loop join12. 子查询和视图性能PGSQL子查询，视图优化，性能比较高 MySQL视图谓词条件下推限制多，子查询上拉限制多13. 执行计划即时编译PGSQL支持JIT执行计划即时编译，使用LLVM编译器 MySQL不支持执行计划即时编译14. 并行查询PGSQL并行查询（多种并行查询优化方法），并行查询一般多见于商业数据库，是重量级功能 MySQL有限，只支持主键并行查询15. 物化视图PGSQL支持物化视图 MySQL不支持物化视图16. 插件功能PGSQL支持插件功能，可以丰富PGSQL的功能，GIS地理插件，时序数据库插件， 向量化执行插件等等 MySQL不支持插件功能17. check约束PGSQL支持check约束 MySQL不支持check约束，可以写check约束，但存储引擎会忽略它的作用，因此check约束并不起作用（mariadb 支持）18. gpu 加速SQLPGSQL可以使用gpu 加速SQL的执行速度 MySQL不支持gpu 加速SQL 的执行速度 19. 数据类型PGSQL数据类型丰富，如 ltree，hstore，数组类型，ip类型，text类型，有了text类型不再需要varchar，text类型字段最大存储1GB MySQL数据类型不够丰富20. 跨库查询PGSQL不支持跨库查询，这个跟Oracle 12C以前一样 MySQL可以跨库查询21. 备份还原PGSQL备份还原非常简单，时点还原操作比SQL Server还要简单，完整备份+wal归档备份（增量）假如有一个三节点的PGSQL主从集群，可以随便在其中一个节点做完整备份和wal归档备份 MySQL备份还原相对不太简单，完整备份+binlog备份（增量）完整备份需要percona的XtraBackup工具做物理备份，MySQL本身不支持物理备份时点还原操作步骤繁琐复杂22. 性能视图PGSQL需要安装pg_stat_statements插件，pg_stat_statements插件提供了丰富的性能视图：如：等待事件，系统统计信息等不好的地方是，安装插件需要重启数据库，并且需要收集性能信息的数据库需要执行一个命令：create extension pg_stat_statements命令否则不会收集任何性能信息，比较麻烦 MySQL自带PS库，默认很多功能没有打开，而且打开PS库的性能视图功能对性能有影响（如：内存占用导致OOM bug）23. 安装方式PGSQL有各个平台的包rpm包，deb包等等，相比MySQL缺少了二进制包，一般用源码编译安装，安装时间会长一些，执行命令多一些 MySQL有各个平台的包rpm包，deb包等等，源码编译安装、二进制包安装，一般用二进制包安装，方便快捷24. DDL操作PGSQL加字段、可变长字段类型长度改大不会锁表，所有的DDL操作都不需要借助第三方工具，并且跟商业数据库一样，DDL操作可以回滚，保证事务一致性 MySQL由于大部分DDL操作都会锁表，例如加字段、可变长字段类型长度改大，所以需要借助percona-toolkit里面的pt-online-schema-change工具去完成操作将影响减少到最低，特别是对大表进行DDL操作DDL操作不能回滚25. 大版本发布速度PGSQLPGSQL每年一个大版本发布，大版本发布的第二年就可以上生产环境，版本迭代速度很快PGSQL 9.6正式版推出时间：2016年PGSQL 10 正式版推出时间：2017年PGSQL 11 正式版推出时间：2018年PGSQL 12 正式版推出时间：2019年 MySQLMySQL的大版本发布一般是2年~3年，一般大版本发布后的第二年才可以上生产环境，避免有坑，版本发布速度比较慢MySQL5.5正式版推出时间：2010年MySQL5.6正式版推出时间：2013年MySQL5.7正式版推出时间：2015年MySQL8.0正式版推出时间：2018年26. returning语法PGSQL支持returning语法，returning clause 支持 DML 返回 Resultset，减少一次 Client <-> DB Server 交互 MySQL不支持returning语法27. 内部架构PGSQL多进程架构，并发连接数不能太多，跟Oracle一样，既然跟Oracle一样，那么很多优化方法也是相通的，例如：开启大页内存 MySQL多线程架构，虽然多线程架构，但是官方有限制连接数，原因是系统的并发度是有限的，线程数太多，反而系统的处理能力下降，随着连接数上升，反而性能下降一般同时只能处理200 ~300个数据库连接28. 聚集索引PGSQL不支持聚集索引，PGSQL本身的MVCC的实现机制所导致 MySQL支持聚集索引29. 空闲事务终结功能PGSQL通过设置 idle_in_transaction_session_timeout 参数来终止空闲事务，比如：应用代码中忘记关闭已开启的事务，PGSQL会自动查杀这种类型的会话事务 MySQL不支持终止空闲事务功能30. 应付超大数据量PGSQL不能应付超大数据量，由于PGSQL本身的MVCC设计问题，需要废品回收，只能期待后面的大版本做优化 MySQL不能应付超大数据量，MySQL自身架构的问题31. 分布式演进PGSQLHTAP数据库：cockroachDB、腾讯Tbase分片集群：Postgres-XC、Postgres-XL MySQLHTAP数据库：TiDB分片集群： 各种各样的中间件，不一一列举32. 数据库的文件名和命名规律PGSQLPGSQL在这方面做的比较不好，DBA不能在操作系统层面（停库状态下）看清楚数据库的文件名和命名规律，文件的数量，文件的大小一旦操作系统发生文件丢失或硬盘损坏，非常不利于恢复，因为连名字都不知道PGSQL表数据物理文件的命名/存放规律是： 在一个表空间下面，如果没有建表空间默认在默认表空间也就是base文件夹下，例如：/data/base//3599base：默认表空间pg_default所在的物理文件夹：表所在数据库的oid3599：就是表对象的oid，当然，一个表的大小超出1GB之后会再生成多个物理文件，还有表的fsm文件和vm文件，所以一个大表实际会有多个物理文件由于PGSQL的数据文件布局内容太多，大家可以查阅相关资料当然这也不能全怪PGSQL，作为一个DBA，时刻做好数据库备份和容灾才是正道，做介质恢复一般是万不得已的情况下才会做 MySQL数据库名就是文件夹名，数据库文件夹下就是表数据文件，但是要注意表名和数据库名不能有特殊字符或使用中文名，每个表都有对应的frm文件和ibd文件，存储元数据和表/索引数据，清晰明了，做介质恢复或者表空间传输都很方便33. 权限设计PGSQLPGSQL在权限设计这块是比较坑爹，抛开实例权限和表空间权限，PGSQL的权限层次有点像SQL Server，db=》schema=》object要说权限，这里要说一下Oracle，用Oracle来类比在ORACLE 12C之前，实例与数据库是一对一，也就是说一个实例只能有一个数据库，不像MySQL和SQL Server一个实例可以有多个数据库，并且可以随意跨库查询而PGSQL不能跨库查询的原因也是这样，PGSQL允许建多个数据库，跟ORACLE类比就是有多个实例（之前说的实例与数据库是一对一）一个数据库相当于一个实例，因为PGSQL允许有多个实例，所以PGSQL单实例不叫一个实例，叫集簇（cluster），集簇这个概念可以查阅PGSQL的相关资料PGSQL里面一个实例/数据库下面的schema相当于数据库，所以这个schema的概念对应MySQL的database注意点：正因为是一个数据库相当于一个实例，PGSQL允许有多个实例/数据库，所以数据库之间是互相逻辑隔离的，导致的问题是，不能一次对一个PGSQL集簇下面的所有数据库做操作必须要逐个逐个数据库去操作，例如上面说到的安装pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有数据库都做性能收集的话，需要逐个数据库去执行加载命令又例如跨库查询需要dblink插件或fdw插件，两个数据库之间做查询相当于两个实例之间做查询，已经跨越了实例了，所以需要dblink插件或fdw插件，所以道理非常简单权限操作也是一样逐个数据库去操作，还有一个就是PGSQL虽然像SQL Server的权限层次结构db=》schema=》object，但是实际会比SQL Server要复杂一些，还有就是新建的表还要另外授权在PGSQL里面，角色和用户是一样的，对新手用户来说有时候会傻傻分不清，也不知道怎么去用角色，所以PGSQL在权限设计这一块确实比较坑爹 MySQL使用mysql库下面的5个权限表去做权限映射，简单清晰，唯一问题是缺少权限角色user表db表host表tables_priv表columns_priv表1. 架构对比Mysql：多线程PostgreSql：多进程多线程架构和多进程架构之间没有绝对的好坏，例如oracle在unix上是多进程架构，在windows上是多线程架构。 2. 对存储过程及事务的支持能力MySql对于无事务的MyISAM表，采用表锁定，一个长时间运行的查询很可能会长时间的阻碍，而PostgreSQL不会尊在这种问题。 PostgreSQL支持存储过程，要比MySql好，具备本地缓存执行计划的能力。 3. 稳定性及性能高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySql 明显出现一个波峰后下滑（5.5版本后Mysql企业版有优化，需要付费）MySql的InnoDB引擎，可以充分优化利用系统的所有内存，超大内存下PG对内存使用的不那么充分（需要根据内存情况合理分配）。 4. 高可用InnoDB的基于回滚实现的 MVCC 机制，对于 PG 新老数据一起放的基于 XID 的 MVCC机制，是占优的。 新老数据一起存放，需要定时触发 VACUUM，会带来多余的 IO 和数据库对象加锁开销，引起数据库整理的并发能力下降。 而且 VACUUM 清理不及时，还可能会引发数据膨胀 5. 数据同步方式：Mysql到现在也是异步复制，pgsql可以做到同步、异步、半同步复制。 Mysql同步是基于binlog复制，属于逻辑复制，类似于oracle golden gate，是基于stream的复制，做到同步很困难，这种方式更加适合异步复制；Pgsql的同是基于wal，属于物理复制，可以做到同步复制。 同时，pgsql还提供stream复制。 Mysql的复制可以用多级从库，但是在9.2之前，PgSql不能用从库带从库。 Pgsql的主从复制属于物理复制，相对于Mysql基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。 6. 权限控制对比MySql允许自定义一套不同的数据级、表级和列的权限，运行指定基于主机的权限Mysql的merge表提供了 一个独特管理多个表的方法。 myisampack可以对只读表进行压缩，以后仍然可以直接访问该表中的行。 7. SQL语句支持能力PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，例如分析函数（Oracle的叫法，PG里叫window函数）支持用多种语言来写存储过程，对于R的支持也很好。 这一点上Mysql就差的很远，很多分析功能都不支持。 PgSql对表名大小写的处理，只有在Sql语句中，表明加双引号，才区分大小写。 在Sql的标准实现上要比Mysql完善，而且功能实现比较严谨。 对表连接支持比较完整，优化器的功能比较完整，支持的索引类型很多，复杂查询能力较强。 Mysql采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结果设计存在约束；Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦数据量大，性能就非常的差。 PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL还支持正则表达式查询，MySql不支持。 8. 数据类型支持能力PostgreSQL可以更方便的使用UDF（用户定义函数）进行扩展。 有丰富的几何类型，实际上不止集合类型，PG有大量的字典、数组、bitmap等数据类型，因此PG多年来在 GIS 领域处于优势地位。 相比之下Mysql就差很多，instagram就是因为PG的空间数据扩展 PostGIS远远强于 MySql的 my spatial 而采用 PgSql的。 Mysql中的空间数据类型有4种，分别是 CEOMETRY、POINT、LINESTRING、POLYGON，其空间索引只能在存储引擎为 MyiSam的表中创建，用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。 创建空间索引的列，必须将其声明为NOT NULL。 不同的存储亲情有差别。 MyISAM和InnoDB 都支持 spatial extensions，但差别在于：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。 pgsql对json支持比较好，还有很逆天的fdw功能，就是把别的数据库中的表当自己的用。 pgsql的字段类型支持的多，有很多mysql没有的类型，但是实际中有时候用到。 一半关系型数据库的字符串长度8k左右，无限长的 TEXT 类型的功能受限，只能作为外部带数据访问。 而 PG 的 TEXT 类型可以直接访问，SQL 语法内置正则表达式，可以索引，还可以全文检索，或使用 xml xpath。 用 PG 的话，文档数据库都可以省了。 postgresql 有函数，用于报表、统计很方便PG支持 R-Trees这样可扩展的索引类型，可以方便的处理一些特殊数据。 PG可以使用函数和条件所以，使得数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。 9. 如可过程容错能力大批量数据入库，PostgreSql要求所有的数据必须完全满足要求，有一条错误，整个数据入库过程失败。 MySql无此问题。 10. 表组织方式pgsql用继承的方式实现分区表，让分区表的使用不方便且性能差，这点比不上mysql。 pg主表采用堆表存放，MySQL采用索引组织表，能够支持比MySql更大的数据量。 MySql分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到成千上万后的处理性能差异很大。 11. 开发结构对于web应用来所，mysql 5.6 的内置 MC API 功能很好用，PgSQL差一些。 PG的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和 PGSQL的 MVCC 实现有关系。 好文要顶 关注我 收藏该文  茄子777粉丝 - 0 关注 - 0+加关注00« 上一篇： 多线程中的wait与join» 下一篇： 负载均衡相关posted @ 2022-11-02 16:20茄子777阅读(55)评论(0)编辑收藏举报刷新评论刷新页面返回顶部登录后才能查看或发表评论，立即 登录 或者 逛逛 博客园首页【推荐】阿里云新人特惠，爆款云服务器2核4G低至0.46元/天【推荐】双十一同价！腾讯云云服务器抢先购，低至4.2元/月编辑推荐：· 一个有趣的 nginx HTTP 400 响应问题分析· 谁说没有GC调优？只改一行代码就让程序不再占用内存· 为什么标准库的模板变量都是 inline 的· 如何优雅的使用 EFCore· 在 C# 中使用 Halcon 开发视觉检测程序阅读排行：· Entity Framework Core 7中高效地进行批量数据插入· 除了 filter 还有什么置灰网站的方式？· 快速绘制流程图「GitHub 热点速览 v.22.47」· 使用7和C#11打造最快的序列化程序-以MemoryPack为例· 私藏！资深数据专家SQL效率优化技巧 ⛵