MySQL8.0学习记录01 - 新特性以及基本信息

MySQL5.6 部分新特性

InnoDB 改进

• 可以创建 Full-Text Index，并通过MATCH…AGAINST语法使用

  CREATE TABLE t_text_test(
    	id int,
    	address varchar(1000),
    	remark varchar(500)
    );
    CREATE FULLTEXT INDEX idx_address ON t_text_test(address);
    insert into t_text_test values 
    	(1, 'InnoDB uses a unique document identifier referred to as the DOC_ID to map words in the full-text index to document records where the word appears.',''),
    	(2, 'To support proximity search, position information for each word is also stored, as a byte offset.','');
    	
    select * from  t_text_test where  MATCH(address) AGAINST('identifier')
  

• 一些ALTER TABLE操作可以在不复制表，不阻止对表的插入、更新和删除的情况下进行，或者同时进行。在线DDL功能建立在MySQL 5.5中的快速索引创建功能上，该功能优化了CREATE INDEX和DROP INDEX，以避免表复制行为。
• InnoDB表的优化器统计数据是以更可预测的时间间隔收集的，并且可以在服务器重新启动时持续存在，以提高计划的稳定性。也可以控制对InnoDB索引的采样量，以使优化器的统计数据更加准确，并改善查询执行计划。
• 只读事务优化，改善了临时查询和报告生成应用的性能和并发性。这些优化在实际应用中会自动应用，或者你可以指定START TRANSACTION READ ONLY来确保事务是只读的。
• Mysql服务器只读，–innodb-read-only
• 分区的最大数量增加到8192。这个数字包括表的所有分区和所有子分区。
• 查询和一些数据修改语句，支持明确选择一个或多个分区或子分区
• 分区锁修剪通过帮助消除不受这些语句影响的分区上的锁，大大提高了许多作用于有许多分区的表的DML和DDL语句的性能
• 排序缓冲区的大小为sort_buffer_size。如果N行的排序元素小到足以容纳在排序缓冲区中（如果指定了M+N行），服务器可以避免使用合并文件，而完全在内存中执行排序
• 优化器更有效地处理FROM子句中的子查询（也就是派生表）。在FROM子句中的子查询的具体化被推迟到查询执行过程中需要它们的内容时进行，这提高了性能。此外，在查询执行过程中，优化器可以向派生表添加索引，以加快从派生表中检索行的速度。

MySQL5.7 部分新特性

• Online ALTER TABLE。ALTER TABLE现在支持RENAME INDEX子句，可以重命名一个索引。

InnoDB 改进

• varchar长度增加，可以通过 in-place ALTER TABLE

  ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
  

• InnoDB临时表的元数据不再被存储到InnoDB系统表中。取而代之的是一个新的表，INNODB_TEMP_TABLE_INFO，为用户提供了一个活动临时表的快照。
• InnoDB现在支持MySQL支持的空间数据类型DATA_GEOMETRY
• 现在有一个单独的表空间用于所有非压缩的InnoDB临时表
• Online DDL 支持以下语句,减少了表的重建时间并允许并发的DML：

  OPTIMIZE TABLE
  ALTER TABLE ... FORCE
  ALTER TABLE ... ENGINE=INNODB (when run on an InnoDB table)
  

• InnoDB支持使用SPATIAL索引对空间数据类型进行索引
• InnoDB支持使用CREATE TABLESPACE语法创建普通表空间
• JSON support，新增了json类型，以及一系列function
• 系统和状态变量信息现在可以在Performance Schema表中获得，而不是使用INFORMATION_SCHEMA表来获得这些变量
• EXPLAIN可以用来获得在一个命名连接中执行的可解释语句的执行计划。

  EXPLAIN [options] FOR CONNECTION connection_id;
  

• 以前，一个表在触发事件（INSERT, UPDATE, DELETE）和操作时间（BEFORE, AFTER）的每个组合中最多只能有一个触发器。这个限制已经取消了，允许有多个触发器

MySQL8.0 部分新特性

• Atomic DDL. 原子 DDL 语句将与 DDL 操作相关的数据字典更新、存储引擎操作和二进制日志写入合并为一个单一的原子事务
• InnoDB临时表在共享临时表空间中被创建
• InnoDB支持NOWAIT和SKIP LOCKED选项与SELECT … FOR SHARE和SELECT … FOR UPDATE锁定读取语句。NOWAIT使语句在请求的行被其他事务锁定时立即返回。SKIP LOCKED从结果集中删除锁定的行
• mysql系统表和数据字典表现在在MySQL数据目录下名为mysql.ibd的单一InnoDB表空间文件中创建。以前，这些表是在mysql数据库目录下的单个InnoDB表空间文件中创建的
• ALTER TABLESPACE … 重命名表空间
• 支持ALGORITHM=INSTANT的操作只修改数据字典中的元数据。在表上没有元数据锁，表的数据不受影响，使得这些操作是瞬时的。如果没有明确指定，ALGORITHM=INSTANT会被支持它的操作默认使用
• 在MySQL 8.0.16中引入的innodb_spin_wait_pause_multiplier变量为自旋锁轮询的持续时间提供了更大的控制，当线程等待获取一个mutex或rw-lock时，会出现这种情况。可以对延迟进行更精细的调整
• 从MySQL 8.0.19开始，支持为生成直方图统计的目的对InnoDB数据进行有效采样
• 为了提高需要访问表和行资源的锁队的操作的并发性，system mutex被sharded latches取代
• 从MySQL 8.0.21开始，atomic DDL支持 CREATE TABLE … SELECT
• 从MySQL 8.0.28开始，InnoDB支持ALTER TABLE … 使用ALGORITHM=INSTANT的RENAME COLUMN操作。操作是即时的，表的数据不受影响
• 默认字符集已经从latin1改为utf8mb4
• 可以使用表达式对BLOB, TEXT, GEOMETRY, 和JSON类型添加默认值

优化器

• 不可见索引。一个不可见的索引根本不被优化器使用，但在其他方面被正常维护.
• 降序索引。索引定义中的DESC不再被忽略，而是导致按降序存储键值。以前，索引可以按相反的顺序进行扫描，但会影响性能。降序索引可以按正向顺序扫描，这更有效率。降序索引也使优化器有可能在最有效的扫描顺序中混合了某些列的升序和其他列的降序时使用多列索引
• 函数式索引。支持表达式而不仅仅是列。
• 在MySQL 8.0.14及以后的版本中，WHERE条件中琐碎的常数字面量会在准备阶段移除，比如"AND 1=1";也会简化外链接中琐碎的条件，比如

  	SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2
  	/* 会重写为*/
  	SELECT * FROM t1 LEFT JOIN t2 WHERE condition_1 AND condition_2
  	```
  

• 常数折叠。处理常数与列类型比较时，边界条件的优化，比如 对于TINYINT UNSIGNED的列c，c < 255优化为 1，c >= 255 优化为c = 255
• 使用半联接策略来改善IN 或者EXISTS 子查询的执行,使用反链接优化NOT IN 或NOT EXISTS 等
• 在上下文阶段将任何不完整的SQL谓语重写，比如 where value,value是列名，重写为where value <>0, 所以一定要使用完整谓语
• Common table expressions。可以通过with 命名临时结果集：

   WITH
     cte1 AS (SELECT a, b FROM table1),
     cte2 AS (SELECT c, d FROM table2)
   SELECT b, d FROM cte1 JOIN cte2
   WHERE cte1.a = cte2.c;
  

• 窗口函数。可以查看窗口中的数据的序号、第一行、最后一行以及第N行等等
• 横向派生表（Lateral derived tables）。如下from字句中max_sale_customer临时表里面用到了前一个临时表max_sale的字段：

      SELECT
      salesperson.name,
      max_sale.amount,
      max_sale_customer.customer_name
    FROM
      salesperson,
      -- calculate maximum size, cache it in transient derived table max_sale
      LATERAL
      (SELECT MAX(amount) AS amount
        FROM all_sales
        WHERE all_sales.salesperson_id = salesperson.id)
      AS max_sale,
      -- find customer, reusing cached maximum size
      LATERAL
      (SELECT customer_name
        FROM all_sales
        WHERE all_sales.salesperson_id = salesperson.id
        AND all_sales.amount =
            -- the cached maximum size
            max_sale.amount)
      AS max_sale_customer;
  

• 单表DELETE语句支持使用表别名
• 正则表达式支持已使用International Components for Unicode（ICU）重新实现，它提供完整的Unicode支持，并且是多字节安全的。REGEXP_INSTR()、REGEXP_REPLACE()和REGEXP_SUBSTR()函数可分别用于查找匹配位置和执行子串替换和提取
• 函数CAST()和CONVERT()现在支持转换为DOUBLE、FLOAT和REAL类型
• 哈希连接的优化。 从MySQL 8.0.18开始，只要连接中的每一对表至少包括一个等价连接条件，并且没有索引适用于任何连接条件，就会使用散列连接。散列连接在大多数情况下比块嵌套循环算法更有效率
• MySQL 8.0.18引入了EXPLAIN ANALYZE，它运行一条语句并产生EXPLAIN输出，以及关于优化器的预期与实际执行的匹配程度的时间和额外的、基于迭代器的信息
• Query cast injection。在8.0.18及以后的版本中，MySQL在参数的数据类型和预期的数据类型不匹配的表达式和条件中，将cast注入查询项目树，这对查询结果或执行速度没有影响。初步看下来好像是解决了以前的隐式转换问题
• Row and column aliases with ON DUPLICATE KEY UPDATE。插入的时候可以引用旧的值或新的值

    INSERT INTO t SET a=9,b=5
        ON DUPLICATE KEY UPDATE a=VALUES(a)+VALUES(b);
    INSERT INTO t SET a=9,b=5 AS new
        ON DUPLICATE KEY UPDATE a=new.a+new.b;
    
    INSERT INTO t VALUES(9,5) AS new
        ON DUPLICATE KEY UPDATE a=new.a+new.b;
    
    INSERT INTO t SET a=9,b=5 AS new(m,n)
        ON DUPLICATE KEY UPDATE a=m+n;
    
    INSERT INTO t VALUES(9,5) AS new(m,n)
        ON DUPLICATE KEY UPDATE a=m+n;
  

• SQL standard explicit table clause and table value constructor. 根据SQL标准，增加了表值构造器和显式表子句

  TABLE t1 UNION TABLE t2 is equivalent to SELECT * FROM t1 UNION SELECT * FROM t2
  CREATE TABLE t2 TABLE t1 is equivalent to CREATE TABLE t2 SELECT * FROM t1
  SELECT a FROM t1 WHERE b > ANY (TABLE t2) is equivalent to SELECT a FROM t1 WHERE b > ANY (SELECT * FROM t2).
  

• Optimizer hints for FORCE INDEX, IGNORE INDEX。 提供了更多的hint

    GROUP_INDEX: Equivalent to FORCE INDEX FOR GROUP BY
  
    NO_GROUP_INDEX: Equivalent to IGNORE INDEX FOR GROUP BY
    
    JOIN_INDEX: Equivalent to FORCE INDEX FOR JOIN
    
    NO_JOIN_INDEX: Equivalent to IGNORE INDEX FOR JOIN
    
    ORDER_INDEX: Equivalent to FORCE INDEX FOR ORDER BY
    
    NO_ORDER_INDEX: Equivalent to IGNORE INDEX FOR ORDER BY
    
    INDEX: Same as GROUP_INDEX plus JOIN_INDEX plus ORDER_INDEX; equivalent to FORCE INDEX with no modifier
    
    NO_INDEX: Same as NO_GROUP_INDEX plus NO_JOIN_INDEX plus NO_ORDER_INDEX; equivalent to IGNORE INDEX with no modifier
  

• Derived condition pushdown optimization。派生条件下推优化，比如SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant ，会优化为SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt
• Non-locking reads on MySQL grant tables。以前为了MySQL授予表进行并发的DML和DDL操作，读操作会加行锁，现在读操作不再加锁
• MySQL 8.0.29增强了对XA事务的支持，这些事务一旦准备好，就不再与原始连接相连。这意味着它们可以被另一个连接提交或回滚，并且当前会话可以立即开始另一个事务。
• CREATE FUNCTION、CREATE PROCEDURE、CREATE TRIGGER，支持 IF NOT EXISTS

限制

• 每个表最多支持64个索引
• 每个索引可以由1到16个列或列的一部分组成
• InnoDB表的最大索引宽度是767字节或3072字节
• MyISAM表的最大索引宽度是1000字节