Tag: 索引

这周收到一个 sentry 报警，如下 SQL 查询超时了。 select * from order_info where uid = 5837661 order by id asc limit 1 执行show create table order_info 发现这个表其实是有加索引的 CREATE TABLE `order_info` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `uid` int(11) unsigned,   `order_status` tinyint(3) DEFAULT NULL,   … 省略其它字段和索引   PRIMARY KEY (`id`),   KEY `idx_uid_stat` (`uid`,`order_status`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 理论上执行上述 SQL 会命中 idx_uid_stat 这个索引，但实际执行 explain 查看 explain select * from order_info where uid = 5837661 order by id asc limit 1 可以看到它的 possible_keys（此 SQL 可能涉及到的索引） 是 idx_uid_stat，但实际上（key）用的却是全表扫描 我们知道 MySQL 是基于成本来选择是基于全表扫描还是选择某个索引来执行最终的执行计划的，所以看起来是全表扫描的成本小于基于 idx_uid_stat 索引执行的成本，不过我的第一感觉很奇怪，这条 SQL 虽然是回表，但它的 limit 是 1，也就是说只选择了满足 uid = 5837661 中的其中一条语句，就算回表也只回一条记录，这种成本几乎可以忽略不计，优化器怎么会选择全表扫描呢。 为了查看 MySQL 优化器为啥选择了全表扫描，我打开了 optimizer_trace 来一探究竟 画外音：在MySQL 5.6 及之后的版本中，我们可以使用 optimizer… Continue reading MySQL 不走索引的一种情况

什么是降序索引 大家可能对索引比较熟悉，而对降序索引比较陌生，事实上降序索引是索引的子集。 我们通常使用下面的语句来创建一个索引： create index idx_t1_bcd on t1(b,c,d); 上面sql的意思是在t1表中，针对b,c,d三个字段创建一个联合索引。 但是大家不知道的是，上面这个sql实际上和下面的这个sql是等价的： create index idx_t1_bcd on t1(b asc,c asc,d asc); asc表示的是升序，使用这种语法创建出来的索引叫做升序索引。也就是我们平时在创建索引的时候，创建的都是升序索引。 可能你会想到，在创建的索引的时候，可以针对字段设置asc，那是不是也可以设置desc呢？ 当然是可以的，比如下面三个语句： create index idx_t1_bcd on t1(b desc,c desc,d desc); create index idx_t1_bcd on t1(b asc,c desc,d desc); create index idx_t1_bcd on t1(b asc,c asc,d desc); 这种语法在mysql中也是支持的，使用这种语法创建出来的索引就叫降序索引，关键问题是：在Mysql8.0之前仅仅只是语法层面的支持，底层并没有真正支持。 我们分别使用Mysql7、Mysql8两个版本来举例子说明一下： 在Mysql7、Mysql8中分别创建一个表，有a,b,c,d,e五个字段： create table t1 ( a int primary… Continue reading MySQL降序索引

一、简介 ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。 二、原理 为了理解ICP是如何工作的，我们先了解下没有使用ICP的情况下，MySQL是如何查询的： 存储引擎读取索引记录； 根据索引中的主键值，定位并读取完整的行记录； 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。 使用ICP的情况下，查询过程如下： 读取索引记录（不是完整的行记录）； 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录； 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）； 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。 三、实践 先创建一张表，并插入记录 CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT COMMENT “主键”, name varchar(32) COMMENT “姓名”, city varchar(32) COMMENT “城市”, age int(11) COMMENT “年龄”, primary key(id), key idx_name_city(name, city) )engine=InnoDB default charset=utf8; insert into user(name, city, age) values(“ZhaoDa”, “BeiJing”,… Continue reading MySQL索引下推

这里主要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储结构不能是 HASH，HASH 不支持前缀索引。 先看下面这两行示例数据： 你是中国人吗？是的是的是的是的是的是的是的是的是的是的 确定是中国人？是的是的是的是的是的是的是的是的是的是的 这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？ 大致有以下 3 种方法： 拿整个串的数据来做索引 这种方法来的最简单直观，但是会造成索引空间极大的浪费。重复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源消耗。 将字符拆开，将一部分做索引 把数据前面几个字符和剩余的部分字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建立索引。如果排除掉表结构更改这块影响，那这种方法无疑是最好的。 把前面 6 个字符截取出来的子串做一个索引 能否不拆分字段，又能避免太多重复值的冗余？我们今天讨论一下前缀索引。 前缀索引 前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。 MySQL 基本上大部分存储引擎都支持前缀索引，目前只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/VARCHAR、TEXT/BLOB、BINARY/VARBINARY。 字符类型基于前缀字符长度，f1(10) 指的前 10 个字符； 二进制类型基于字节大小，f1(10) 指的是前 10 个字节长度； TEXT/BLOB 类型只支持前缀索引，不支持整个字段建索引。 举个简单例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的普通二级索引，另外一个是基于字段r1的前缀索引。 <localhost|mysql>show create table t1\G *************************** 1. row *************************** Table: t1 Create… Continue reading MYSQL前缀索引