MySQL 8.0索引新玩法

导读

MySQL 8.0开始支持倒序索引和不可见索引,和叶师傅一起来耍耍。

MySQL版本号

Server version:     8.0.1-dmr-log MyS  QL Community Server (GPL)

测试表结构

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u1` int(10) unsigned NOT NULL DEFAULT '0',
  `u2` int(10) unsigned NOT NULL DEFAULT '0',
  `u3` varchar(20) NOT NULL DEFAULT '',
  `u4` varchar(35) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `u1` (`u1` DESC,`u2`)
) ENGINE=InnoDB AUTO_INCREMENT=131054 DEFAULT CHARSET=utf8mb4

表统计信息:

yejr@imysql.com[yejr]> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 101970
 Avg_row_length: 87
    Data_length: 8929280
Max_data_length: 0
   Index_length: 3686400
      Data_free: 4194304
 Auto_increment: 131054
    Create_time: 2017-05-31 11:57:48
    Update_time: 2017-06-10 12:08:09
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

1、倒序索引(Descending Indexes)

case1、MySQL 8.0下利用倒序索引消除排序

# 查看执行计划
yejr@imysql.com[yejr]> desc select * from t1 where u1 <= 70 order by u1 desc,  u2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: u1
          key: u1
      key_len: 4
          ref: NULL
         rows: 27742
     filtered: 100.00
        Extra: Using index condition

# 清除status,方便后面看SQL执行代价
yejr@imysql.com[yejr]> flush status;

# 临时修改pager,将查询结果重定向到 /dev/null,而不是发送到标准输出设备
yejr@imysql.com[test]> pager cat - > /dev/null
PAGER set to 'cat - > /dev/null'

# 执行SQL
yejr@imysql.com[yejr]> select * from t1 where u1 <= 70 order by u1 desc,  u2;
16384 rows in set (0.05 sec)

# 查看SQL代价
yejr@imysql.com[yejr]> show status like 'handler%read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 16384 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

case2、MySQL 5.7下不支持倒序索引,有额外排序

# 执行计划中有 **Using filesort**
yejr@imysql.com[yejr]> desc select * from t1 where u1 <= 70 order by u1 desc,  u2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: u1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 102176
     filtered: 33.29
        Extra: Using where; Using filesort

yejr@imysql.com[yejr]> select * from t1 where u1 <= 70 order by u1 desc,  u2;
16383 rows in set (0.05 sec)

# 可以看到有大量的 read_rnd_next
yejr@zhishutang.com[yejr]> show status like 'handler%read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 1      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 102401 |
+-----------------------+--------+

case3、MySQL 5.7下不支持倒序索引,但强制指定索引

# 还是有 **Using filesort** 啊
yejr@imysql.com[yejr]> desc select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: u1
          key: u1
      key_len: 4
          ref: NULL
         rows: 34014
     filtered: 100.00
        Extra: Using index condition; Using filesort

yejr@imysql.com[yejr]> select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2;
16383 rows in set (0.08 sec)

# status瓶颈从 read_rnd_next 变成了 read_next

yejr@imysql.com[yejr]> show status like 'handler%read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 16384 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

关于 Handler_read_rnd、Handler_read_rnd_next 的解释 ,请看文档说明。

Handler_read_rnd

The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

关于倒序索引有几点说明:

• 只支持InnoDB引擎;
• 只支持B+TREE,不支持HASH、FULLTEXT、SPATIAL索引;
• change buffer无法支持有倒序的二级索引(secondary index);
• 正常正序索引支持的数据类型,倒序索引也都支持;
• 除了原生列之外,还支持虚拟列(VIRTUAL、STORED都支持);
• InnoDB全文索引的表中,FTS_DOC_ID列也不能设定为倒序;

2、不可见索引(INVISIBLE INDEX)

当我们发现冗余索引或者个别不再需要的索引时,首先想到的是直接删除。

但是直接删除索引肯定是有风险的,难免什么时候某个老业务又需要用到这个索引了,这时候invisible index就非常实用了。

这时候就可以把这个索引设置为 visible/invisible,修改索引的 visible 属性是可以in-place的,非常快。这相比删除、再次创建索引要快得多了。

一起来看看 invisible index的玩法:

# 设置 invisible
yejr@imysql.com[yejr]> alter table t1 alter index u1 INVISIBLE;

# 查看DDL
yejr@imysql.com[yejr]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
...
  KEY `u1` (`u1` DESC,`u2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=131054 DEFAULT CHARSET=utf8mb4

# 查看索引状态
yejr@imysql.com[yejr]> desc select * from t1 force index(u1) where u1 <= 70 order by u1 desc,  u2\G
ERROR 1176 (42000): Key 'u1' doesn't exist in table 't1'
yejr@imysql.com[yejr]> SELECT INDEX_NAME, IS_VISIBLE
    ->        FROM INFORMATION_SCHEMA.STATISTICS
    ->        WHERE table_name = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| PRIMARY    | YES        |
| u1         | NO         |
| u1         | NO         |
+------------+------------+

yejr@imysql.com[yejr]> show index from t1;
+-------+------------+----------+-------------+-------------+---------+
| Table | Non_unique | Key_name | Column_name | Cardinality | Visible |
+-------+------------+----------+-------------+-------------+---------+
| t1    | 0          | PRIMARY  | id          |      101970 | YES     |
| t1    | 1          | u1       | u1          |          24 | NO      |
| t1    | 1          | u1       | u2          |          24 | NO      |
+-------+------------+----------+-------------+-------------+---------+

# 执行SQL时即便force index也不可用
yejr@imysql.com[yejr]>select * from t1 force index(u1) where u1 <= 70 order by u1 desc,  u2\G
ERROR 1176 (42000): Key 'u1' doesn't exist in table 't1'

关于invisible index的几点补充:

• 即便是 invisible,也还要保持索引的更新;
• 主键或被选中作为聚集索引的唯一索引(这种称为隐含的聚集索引),都不能 invisible;
• 任何存储引擎都支持,不仅限于InnoDB;

最后,我要再次安利下MySQL 8.0版本,在这个版本中,有几个重大(激动人心的)新特性:

• 新增User Roles特性;
• 数据字典采用InnoDB表存储,不再使用frm文件了(MyISAM退出历史舞台进入倒计时~);
• 在线执行SET GLOBAL xx修改某个 option 后,可以实现该修改持久化(太实用了,拯救健忘症重度患者);
• InnoDB表的 AUTO_INCREMENT 值也可以实现持久化(重启后不再发生变化);
• InnoDB的REDO、UNDO LOG也支持加密;
• InnoDB表支持NOWAIT、SKIP LOCKED语法;
• 支持倒序索引、不可见索引;
• Optimizer、P_S、JSON、GIS功能持续增强;
• 可想而知的是,对Group Replicatioin也会重点增强;

其他新特性不一一列出,我粗略的看了下,这些不都是在向Oracle学习嘛,哈哈。

延伸阅读

FAQ系列 | B+树索引和哈希索引的区别

导读

在MySQL里常用的索引数据结构有B+树索引和哈希索引两种,我们来看下这两种索引数据结构的区别及其不同的应用建议。

二者区别

备注:先说下,在MySQL文档里,实际上是把B+树索引写成了BTREE,例如像下面这样的写法:

CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘’,
detail varchar(255) not null default ‘’,
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE此处 uname 列只创建了最左12个字符长度的部分索引
)engine=InnoDB;

一个经典的B+树索引数据结构见下图:
20160106B树索引
(图片源自网络)

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。

在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。

因此,B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下,xfs文件系统比ext3/ext4效率高很多的原因之一就是,它的文件及目录索引结构全部采用B+树索引,而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构,因此在高I/O压力下,其IOPS能力不如xfs。

详细可参见:

https://en.wikipedia.org/wiki/Ext4
https://en.wikipedia.org/wiki/XFS

哈希索引的示意图则是这样的:
20160106哈希索引
(图片源自网络)

简单地说,哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。

从上面的图来看,B+树索引和哈希索引的明显区别是:

  • 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;
  • 从示意图中也能看到,如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
  • 同理,哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);
  • 哈希索引也不支持多列联合索引的最左匹配规则
  • B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题

后记

在MySQL中,只有HEAP/MEMORY引擎表才能显式支持哈希索引(NDB也支持,但这个不常用),InnoDB引擎的自适应哈希索引(adaptive hash index)不在此列,因为这不是创建索引时可指定的。

还需要注意到:HEAP/MEMORY引擎表在mysql实例重启后,数据会丢失。

通常,B+树索引结构适用于绝大多数场景,像下面这种场景用哈希索引才更有优势:

在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引

例如这种SQL:
SELECT … FROM t WHERE C1 = ?; — 仅等值查询

在大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

FAQ系列 | MySQL索引之主键索引

导读

在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别?

上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。

1、主键索引

主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则:

  1. 采用一个没有业务用途的自增属性列作为主键;
  2. 主键字段值总是不更新,只有新增或者删除两种操作;
  3. 不选择会动态更新的类型,比如当前时间戳等。

这么做的好处有几点:

  1. 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了;可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”;
  2. 业务数据有变更时,不修改主键值,物理存储位置发生变化的概率降低了,innodb page中产生碎片的概率也降低了。

MyISAM表因为是堆组织表,主键类型设计方面就可以不用这么讲究了。

2、辅助索引

辅助索引,就是我们常规所指的索引,原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引非唯一索引

唯一索引其实应该叫做唯一性约束,它的作用是避免一列或多列值存在重复,是一种约束性索引。

3、主键索引和辅助索引的区别

在MyISAM引擎中,唯一索引除了key值允许存在NULL外,其余的和主键索引没有本质性区别。也就是说,在MyISAM引擎中,不允许存在NULL值的唯一索引,本质上和主键索引是一回事

而在InnoDB引擎中,主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引,而唯一索引和普通辅助索引间除了唯一性约束外,在存储上没本质区别

从查询性能上来说,在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程InnoDB表基于普通索引的查找代价更高,因为每次检索到结果后,还需要至少再多检索一次才能确认是否还有更多符合条件的结果,主键索引和唯一索引就不需要这么做了。

经过测试,对100万行数据的MyISAM做随机检索(整数类型),主键和唯一索引的效率基本一样,普通索引的检索效率则慢了30%以上。换成InnoDB表的话,唯一索引比主键索引效率约慢9%,普通索引比主键索引约慢了50%以上。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

FAQ系列 | MySQL索引之聚集索引

导读

在MySQL里,聚集索引和非聚集索引分别是什么意思,有什么区别?

在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)。

也有人把聚集索引称为聚簇索引。

当然了,聚集索引的概念不是MySQL里特有的,其他数据库系统也同样有。

简言之,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序,而非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。

我们先来看看两种存储形式的不同之处:
简单说,IOT表里数据物理存储顺序和主键索引的顺序一致,所以如果新增数据是离散的,会导致数据块趋于离散,而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。
IOT表相比HOT表的优势是:

  • 范围查询效率更高;
  • 数据频繁更新(聚集索引本身不更新)时,更不容易产生碎片;
  • 特别适合有一小部分热点数据频繁读写的场景;
  • 通过主键访问数据时快速可达;

IOT表的不足则有:

  • 数据变化如果是离散为主的话,那么效率会比HOT表差;

HOT表的不足有:

  • 索引回表读开销很大;
  • 大部分数据读取时随机的,无法保证被顺序读取,开销大;

每张InnoDB表只能创建一个聚集索引,聚集索引可以由一列或多列组成。

上面说过,InnoDB是聚集索引组织表,它的聚集索引选择规则是这样的:

  1. 首先选择显式定义的主键索引做为聚集索引;
  2. 如果没有,则选择第一个不允许NULL的唯一索引;
  3. 还是没有的话,就采用InnoDB引擎内置的ROWID作为聚集索引;

我们来看看InnoDB主键索引的示意图:

图片来自高性能MySQL

可以看到,在这个索引结构的叶子节点中,节点key值是主键的值,而节点的value则存储其余列数据,以及额外的ROWID、rollback pointer、trx id等信息。

结合这个图,以及上面所述,我们可以知道:在InnoDB表中,其聚集索引相当于整张表,而整张表也是聚集索引。主键必然是聚集索引,而聚集索引则未必是主键

MyISAM是堆组织表,它没有聚集索引的概念。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的。

先直接说几个结论吧:

1、关于索引对写入速度的影响:
a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%;
b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;

因此,InnoDB表最好总是有一个自增列做主键。

2、关于提交频率对写入速度的影响(以表中只有自增列做主键的场景,一次写入数据30万行数据为例):

a、等待全部数据写入完成后,最后再执行commit提交的效率最高;
b、每10万行提交一次,相对一次性提交,约慢了1.17%;
c、每1万行提交一次,相对一次性提交,约慢了3.01%;
d、每1千行提交一次,相对一次性提交,约慢了23.38%;
e、每100行提交一次,相对一次性提交,约慢了24.44%;
f、每10行提交一次,相对一次性提交,约慢了92.78%;
g、每行提交一次,相对一次性提交,约慢了546.78%,也就是慢了5倍;

因此,最好是等待所有事务结束后再批量提交,而不是每执行完一个SQL就提交一次。
曾经有一次对比测试mysqldump启用extended-insert和未启用导出的SQL脚本,后者比前者慢了不止5倍。
重要:这个建议并不是绝对成立的,要看具体的场景。如果是一个高并发的在线业务,就需要尽快提交事务,避免锁范围被扩大。但如果是在非高并发的业务场景,尤其是做数据批量导入的场景下,就建议采用批量提交的方式。

下面是详细的测试案例过程,有兴趣的同学可以看看:

DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT ‘0’,
`c2` int(11) NOT NULL DEFAULT ‘0’,
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;

CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;

SET AUTOCOMMIT = 0;

WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 达到每 COMMITRATE 频率时提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);
END IF;

END WHILE;

/* 最终再提交一次,确保成功 */
COMMIT;
SELECT ‘ALL COMMIT;’;

END; $$$

#测试调用
call insert_mytab(300000, 1); — 每次一提交
call insert_mytab(300000, 10); — 每10次一提交
call insert_mytab(300000, 100); — 每100次一提交
call insert_mytab(300000, 1000); — 每1千次一提交
call insert_mytab(300000, 10000); — 每1万次提交
call insert_mytab(300000, 100000); — 每10万次一提交
call insert_mytab(300000, 0); — 一次性提交

测试耗时结果对比:
mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert