MySQL bit类型增加索引后查询结果不正确案例浅析

04815

昨天同事遇到的一个案例，这里简单描述一下：一个表里面有一个bit类型的字段，同事在优化相关SQL的过程中，给这个表的bit类型的字段新增了一个索引，然后测试验证 时，居然发现SQL语句执行结果跟不加索引不一样。加了索引后，SQL语句没有查询出一条记录，删除索引后，SQL语句就能查询出几十条记录。下面我们构造一个简单 的例子，重现一下这个案例

我们先创建表student_attend，初始化一些数据。这篇文章的测试环境为MySQL 8.0.35社区版。

<span style="font-weight: bold;line-height: 26px">CREATE</span> <span style="font-weight: bold;line-height: 26px">TABLE</span> <span style="line-height: 26px">`student_attend`</span> (<br>  <span style="line-height: 26px">`id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">NOT</span> <span style="line-height: 26px">NULL</span> AUTO_INCREMENT <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'自增编号'</span>,<br>  <span style="line-height: 26px">`std_id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">DEFAULT</span> <span style="line-height: 26px">NULL</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'学号'</span>,<br>  <span style="line-height: 26px">`class_id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">DEFAULT</span> <span style="line-height: 26px">NULL</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'课程编号'</span>,<br>  <span style="line-height: 26px">`is_attend`</span> <span style="line-height: 26px">bit</span>(<span style="line-height: 26px">1</span>) <span style="font-weight: bold;line-height: 26px">DEFAULT</span> b<span style="line-height: 26px">'1'</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'是否缺陷考勤'</span>,<br>  PRIMARY <span style="font-weight: bold;line-height: 26px">KEY</span> (<span style="line-height: 26px">`id`</span>)<br>) <span style="font-weight: bold;line-height: 26px">ENGINE</span>=<span style="font-weight: bold;line-height: 26px">InnoDB</span>;<br><br><br><span style="font-weight: bold;line-height: 26px">insert</span> <span style="font-weight: bold;line-height: 26px">into</span> student_attend(std_id, class_id, is_attend)<br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">3</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">4</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">5</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">6</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1002</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1002</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1003</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1003</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual;<br><br>
<span style="font-weight: bold;line-height: 26px">CREATE</span> <span style="font-weight: bold;line-height: 26px">TABLE</span> <span style="line-height: 26px">`student_attend`</span> (<br>  <span style="line-height: 26px">`id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">NOT</span> <span style="line-height: 26px">NULL</span> AUTO_INCREMENT <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'自增编号'</span>,<br>  <span style="line-height: 26px">`std_id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">DEFAULT</span> <span style="line-height: 26px">NULL</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'学号'</span>,<br>  <span style="line-height: 26px">`class_id`</span> <span style="line-height: 26px">int</span> <span style="font-weight: bold;line-height: 26px">DEFAULT</span> <span style="line-height: 26px">NULL</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'课程编号'</span>,<br>  <span style="line-height: 26px">`is_attend`</span> <span style="line-height: 26px">bit</span>(<span style="line-height: 26px">1</span>) <span style="font-weight: bold;line-height: 26px">DEFAULT</span> b<span style="line-height: 26px">'1'</span> <span style="font-weight: bold;line-height: 26px">COMMENT</span> <span style="line-height: 26px">'是否缺陷考勤'</span>,<br>  PRIMARY <span style="font-weight: bold;line-height: 26px">KEY</span> (<span style="line-height: 26px">`id`</span>)<br>) <span style="font-weight: bold;line-height: 26px">ENGINE</span>=<span style="font-weight: bold;line-height: 26px">InnoDB</span>;<br><br><br><span style="font-weight: bold;line-height: 26px">insert</span> <span style="font-weight: bold;line-height: 26px">into</span> student_attend(std_id, class_id, is_attend)<br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">3</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">4</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">5</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1001</span>, <span style="line-height: 26px">6</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1002</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1002</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1003</span>, <span style="line-height: 26px">1</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual <span style="font-weight: bold;line-height: 26px">union</span> <span style="font-weight: bold;line-height: 26px">all</span><br><span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">1003</span>, <span style="line-height: 26px">2</span>, <span style="line-height: 26px">0</span> <span style="font-weight: bold;line-height: 26px">from</span> dual;<br><br>
CREATE TABLE `student_attend` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  `std_id` int DEFAULT NULL COMMENT '学号',
  `class_id` int DEFAULT NULL COMMENT '课程编号',
  `is_attend` bit(1) DEFAULT b'1' COMMENT '是否缺陷考勤',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


insert into student_attend(std_id, class_id, is_attend)
select 1001, 1, 1 from dual union all
select 1001, 2, 0 from dual union all
select 1001, 3, 1 from dual union all
select 1001, 4, 1 from dual union all
select 1001, 5, 1 from dual union all
select 1001, 6, 0 from dual union all
select 1002, 1, 1 from dual union all
select 1002, 2, 1 from dual union all
select 1003, 1, 0 from dual union all
select 1003, 2, 0 from dual;

如下所示，假设我们要查询is_attend=1的所有学生信息，那么可以有下面三种写法

mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-style: italic;line-height: 26px">#遇到问题的SQL写法</span><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br><br>
mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-style: italic;line-height: 26px">#遇到问题的SQL写法</span><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br><br>
mysql> select * from student_attend where is_attend=1;
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend=b'1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

#遇到问题的SQL写法
mysql> select * from student_attend where is_attend='1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> 

接下来，我们在字段is_attend上创建索引ix_student_attend_n1,如下所示

<span style="font-weight: bold;line-height: 26px">create</span> <span style="font-weight: bold;line-height: 26px">index</span> ix_student_attend_n1 <span style="font-weight: bold;line-height: 26px">on</span> student_attend(is_attend);<br>
<span style="font-weight: bold;line-height: 26px">create</span> <span style="font-weight: bold;line-height: 26px">index</span> ix_student_attend_n1 <span style="font-weight: bold;line-height: 26px">on</span> student_attend(is_attend);<br>
create index ix_student_attend_n1 on student_attend(is_attend);

然后我们继续测试验证，就能出现我前文所说的情况,如需所示，最后一个SQL，它的返回记录数为0.

mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>Empty <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>
mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>Empty <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>
mysql> select * from student_attend where is_attend=1;
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend=b'1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> select * from student_attend where is_attend='1';
Empty set (0.00 sec)

mysql> 

其实第一次见到这种情况的时候，我还是有点震惊的，因为在我的观念中，索引只会影响执行计划，不会影响查询结果，但是现在的情况是 索引的存在影响了SQL的查询结果。那么为什么会出现这种情况呢？

首先看了一下执行计划，如下所示，从执行计划看，它既没有走全表扫描也没有走索引，仅仅有”message”: “no matching row in const table”提示，如果仅仅分析 执行计划，我们得不到更多的有用信息

mysql> <span style="font-weight: bold;line-height: 26px">explain</span><br>    -> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>1 row in <span style="font-weight: bold;line-height: 26px">set</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">warning</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>mysql> <span style="font-weight: bold;line-height: 26px">explain</span> <span style="font-weight: bold;line-height: 26px">format</span>=<span style="font-weight: bold;line-height: 26px">json</span><br>    -> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>\G<br>*************************** <span style="line-height: 26px">1.</span> <span style="font-weight: bold;line-height: 26px">row</span> ***************************<br><span style="font-weight: bold;line-height: 26px">EXPLAIN</span>: {<br>  <span style="line-height: 26px">"query_block"</span>: {<br>    <span style="line-height: 26px">"select_id"</span>: <span style="line-height: 26px">1</span>,<br>    <span style="line-height: 26px">"message"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>  } <span style="font-style: italic;line-height: 26px">/* query_block */</span><br>}<br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">warning</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">show</span> <span style="font-weight: bold;line-height: 26px">warnings</span>\G<br>*************************** <span style="line-height: 26px">1.</span> <span style="font-weight: bold;line-height: 26px">row</span> ***************************<br>  <span style="font-weight: bold;line-height: 26px">Level</span>: Note<br>   Code: <span style="line-height: 26px">1003</span><br>Message: <span style="font-style: italic;line-height: 26px">/* select#1 */</span> <span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`std_id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`std_id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`class_id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`class_id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`is_attend`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`is_attend`</span> <span style="font-weight: bold;line-height: 26px">from</span> <span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span> <span style="font-weight: bold;line-height: 26px">where</span> (<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`is_attend`</span> = <span style="line-height: 26px">'1'</span>)<br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>
mysql> <span style="font-weight: bold;line-height: 26px">explain</span><br>    -> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |<br>+<span style="font-style: italic;line-height: 26px">----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+</span><br>1 row in <span style="font-weight: bold;line-height: 26px">set</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">warning</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>mysql> <span style="font-weight: bold;line-height: 26px">explain</span> <span style="font-weight: bold;line-height: 26px">format</span>=<span style="font-weight: bold;line-height: 26px">json</span><br>    -> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>\G<br>*************************** <span style="line-height: 26px">1.</span> <span style="font-weight: bold;line-height: 26px">row</span> ***************************<br><span style="font-weight: bold;line-height: 26px">EXPLAIN</span>: {<br>  <span style="line-height: 26px">"query_block"</span>: {<br>    <span style="line-height: 26px">"select_id"</span>: <span style="line-height: 26px">1</span>,<br>    <span style="line-height: 26px">"message"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>  } <span style="font-style: italic;line-height: 26px">/* query_block */</span><br>}<br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span>, <span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">warning</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">show</span> <span style="font-weight: bold;line-height: 26px">warnings</span>\G<br>*************************** <span style="line-height: 26px">1.</span> <span style="font-weight: bold;line-height: 26px">row</span> ***************************<br>  <span style="font-weight: bold;line-height: 26px">Level</span>: Note<br>   Code: <span style="line-height: 26px">1003</span><br>Message: <span style="font-style: italic;line-height: 26px">/* select#1 */</span> <span style="font-weight: bold;line-height: 26px">select</span> <span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`std_id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`std_id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`class_id`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`class_id`</span>,<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`is_attend`</span> <span style="font-weight: bold;line-height: 26px">AS</span> <span style="line-height: 26px">`is_attend`</span> <span style="font-weight: bold;line-height: 26px">from</span> <span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span> <span style="font-weight: bold;line-height: 26px">where</span> (<span style="line-height: 26px">`kerry`</span>.<span style="line-height: 26px">`student_attend`</span>.<span style="line-height: 26px">`is_attend`</span> = <span style="line-height: 26px">'1'</span>)<br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <br>
mysql> explain
    -> select * from student_attend where is_attend='1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> explain format=json
    -> select * from student_attend where is_attend='1'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "message": "no matching row in const table"
  } /* query_block */
}
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `kerry`.`student_attend`.`id` AS `id`,`kerry`.`student_attend`.`std_id` AS `std_id`,`kerry`.`student_attend`.`class_id` AS `class_id`,`kerry`.`student_attend`.`is_attend` AS `is_attend` from `kerry`.`student_attend` where (`kerry`.`student_attend`.`is_attend` = '1')
1 row in set (0.00 sec)

mysql> 

那么我们使用trace跟踪分析一下优化器如何选择执行计划。看看其详细执行过程，如下所示

<br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE=<span style="line-height: 26px">"enabled=on"</span>,END_MARKERS_IN_JSON=<span style="font-weight: bold;line-height: 26px">on</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE_MAX_MEM_SIZE=<span style="line-height: 26px">1000000</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>Empty <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SELECT</span> * <span style="font-weight: bold;line-height: 26px">FROM</span> INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;<br>*************************** 1. row ***************************<br>                            QUERY: <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span><br>                            <span style="font-weight: bold;line-height: 26px">TRACE</span>: {<br>  <span style="line-height: 26px">"steps"</span>: [<br>    {<br>      <span style="line-height: 26px">"join_preparation"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"expanded_query"</span>: <span style="line-height: 26px">"/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_preparation */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_optimization"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"condition_processing"</span>: {<br>              <span style="line-height: 26px">"condition"</span>: <span style="line-height: 26px">"WHERE"</span>,<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"steps"</span>: [<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"equality_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"constant_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"trivial_condition_removal"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>            } <span style="font-style: italic;line-height: 26px">/* condition_processing */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"substitute_generated_columns"</span>: {<br>            } <span style="font-style: italic;line-height: 26px">/* substitute_generated_columns */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"table_dependencies"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"row_may_be_null"</span>: <span style="line-height: 26px">false</span>,<br>                <span style="line-height: 26px">"map_bit"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"depends_on_map_bits"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* depends_on_map_bits */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* table_dependencies */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"ref_optimizer_key_uses"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"field"</span>: <span style="line-height: 26px">"is_attend"</span>,<br>                <span style="line-height: 26px">"equals"</span>: <span style="line-height: 26px">"'1'"</span>,<br>                <span style="line-height: 26px">"null_rejecting"</span>: <span style="line-height: 26px">true</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* ref_optimizer_key_uses */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"rows_estimation"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"range_analysis"</span>: {<br>                  <span style="line-height: 26px">"table_scan"</span>: {<br>                    <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">10</span>,<br>                    <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">3.35</span><br>                  } <span style="font-style: italic;line-height: 26px">/* table_scan */</span>,<br>                  <span style="line-height: 26px">"potential_range_indexes"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"index"</span>: <span style="line-height: 26px">"PRIMARY"</span>,<br>                      <span style="line-height: 26px">"usable"</span>: <span style="line-height: 26px">false</span>,<br>                      <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"not_applicable"</span><br>                    },<br>                    {<br>                      <span style="line-height: 26px">"index"</span>: <span style="line-height: 26px">"ix_student_attend_n1"</span>,<br>                      <span style="line-height: 26px">"usable"</span>: <span style="line-height: 26px">true</span>,<br>                      <span style="line-height: 26px">"key_parts"</span>: [<br>                        <span style="line-height: 26px">"is_attend"</span>,<br>                        <span style="line-height: 26px">"id"</span><br>                      ] <span style="font-style: italic;line-height: 26px">/* key_parts */</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* potential_range_indexes */</span>,<br>                  <span style="line-height: 26px">"setup_range_conditions"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"impossible_condition"</span>: {<br>                        <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"value_out_of_range"</span><br>                      } <span style="font-style: italic;line-height: 26px">/* impossible_condition */</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* setup_range_conditions */</span>,<br>                  <span style="line-height: 26px">"impossible_range"</span>: <span style="line-height: 26px">true</span><br>                } <span style="font-style: italic;line-height: 26px">/* range_analysis */</span>,<br>                <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"impossible_where_condition"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* rows_estimation */</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span>,<br>        <span style="line-height: 26px">"empty_result"</span>: {<br>          <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>        } <span style="font-style: italic;line-height: 26px">/* empty_result */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_optimization */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_execution"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_execution */</span><br>    }<br>  ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>}<br>MISSING_BYTES_BEYOND_MAX_MEM_SIZE: <span style="line-height: 26px">0</span><br>          INSUFFICIENT_PRIVILEGES: <span style="line-height: 26px">0</span><br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-weight: bold;line-height: 26px">ERROR</span>: <br><span style="font-weight: bold;line-height: 26px">No</span> <span style="font-weight: bold;line-height: 26px">query</span> specified<br><br>mysql> <br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> optimizer_trace=<span style="line-height: 26px">"enabled=off"</span>;<br>Query OK, 0 rows affected (0.01 sec)<br><br>
<br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE=<span style="line-height: 26px">"enabled=on"</span>,END_MARKERS_IN_JSON=<span style="font-weight: bold;line-height: 26px">on</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE_MAX_MEM_SIZE=<span style="line-height: 26px">1000000</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>Empty <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SELECT</span> * <span style="font-weight: bold;line-height: 26px">FROM</span> INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;<br>*************************** 1. row ***************************<br>                            QUERY: <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span><br>                            <span style="font-weight: bold;line-height: 26px">TRACE</span>: {<br>  <span style="line-height: 26px">"steps"</span>: [<br>    {<br>      <span style="line-height: 26px">"join_preparation"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"expanded_query"</span>: <span style="line-height: 26px">"/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_preparation */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_optimization"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"condition_processing"</span>: {<br>              <span style="line-height: 26px">"condition"</span>: <span style="line-height: 26px">"WHERE"</span>,<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"steps"</span>: [<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"equality_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"constant_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"trivial_condition_removal"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>            } <span style="font-style: italic;line-height: 26px">/* condition_processing */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"substitute_generated_columns"</span>: {<br>            } <span style="font-style: italic;line-height: 26px">/* substitute_generated_columns */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"table_dependencies"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"row_may_be_null"</span>: <span style="line-height: 26px">false</span>,<br>                <span style="line-height: 26px">"map_bit"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"depends_on_map_bits"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* depends_on_map_bits */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* table_dependencies */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"ref_optimizer_key_uses"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"field"</span>: <span style="line-height: 26px">"is_attend"</span>,<br>                <span style="line-height: 26px">"equals"</span>: <span style="line-height: 26px">"'1'"</span>,<br>                <span style="line-height: 26px">"null_rejecting"</span>: <span style="line-height: 26px">true</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* ref_optimizer_key_uses */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"rows_estimation"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"range_analysis"</span>: {<br>                  <span style="line-height: 26px">"table_scan"</span>: {<br>                    <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">10</span>,<br>                    <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">3.35</span><br>                  } <span style="font-style: italic;line-height: 26px">/* table_scan */</span>,<br>                  <span style="line-height: 26px">"potential_range_indexes"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"index"</span>: <span style="line-height: 26px">"PRIMARY"</span>,<br>                      <span style="line-height: 26px">"usable"</span>: <span style="line-height: 26px">false</span>,<br>                      <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"not_applicable"</span><br>                    },<br>                    {<br>                      <span style="line-height: 26px">"index"</span>: <span style="line-height: 26px">"ix_student_attend_n1"</span>,<br>                      <span style="line-height: 26px">"usable"</span>: <span style="line-height: 26px">true</span>,<br>                      <span style="line-height: 26px">"key_parts"</span>: [<br>                        <span style="line-height: 26px">"is_attend"</span>,<br>                        <span style="line-height: 26px">"id"</span><br>                      ] <span style="font-style: italic;line-height: 26px">/* key_parts */</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* potential_range_indexes */</span>,<br>                  <span style="line-height: 26px">"setup_range_conditions"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"impossible_condition"</span>: {<br>                        <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"value_out_of_range"</span><br>                      } <span style="font-style: italic;line-height: 26px">/* impossible_condition */</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* setup_range_conditions */</span>,<br>                  <span style="line-height: 26px">"impossible_range"</span>: <span style="line-height: 26px">true</span><br>                } <span style="font-style: italic;line-height: 26px">/* range_analysis */</span>,<br>                <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"impossible_where_condition"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* rows_estimation */</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span>,<br>        <span style="line-height: 26px">"empty_result"</span>: {<br>          <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>        } <span style="font-style: italic;line-height: 26px">/* empty_result */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_optimization */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_execution"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_execution */</span><br>    }<br>  ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>}<br>MISSING_BYTES_BEYOND_MAX_MEM_SIZE: <span style="line-height: 26px">0</span><br>          INSUFFICIENT_PRIVILEGES: <span style="line-height: 26px">0</span><br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-weight: bold;line-height: 26px">ERROR</span>: <br><span style="font-weight: bold;line-height: 26px">No</span> <span style="font-weight: bold;line-height: 26px">query</span> specified<br><br>mysql> <br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> optimizer_trace=<span style="line-height: 26px">"enabled=off"</span>;<br>Query OK, 0 rows affected (0.01 sec)<br><br>

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student_attend where is_attend='1';
Empty set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
                            QUERY: select * from student_attend where is_attend='1'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student_attend`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`student_attend`",
                "field": "is_attend",
                "equals": "'1'",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student_attend`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10,
                    "cost": 3.35
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "ix_student_attend_n1",
                      "usable": true,
                      "key_parts": [
                        "is_attend",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                    {
                      "impossible_condition": {
                        "cause": "value_out_of_range"
                      } /* impossible_condition */
                    }
                  ] /* setup_range_conditions */,
                  "impossible_range": true
                } /* range_analysis */,
                "rows": 0,
                "cause": "impossible_where_condition"
              }
            ] /* rows_estimation */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 
mysql> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.01 sec)

从trace的详细信息看，这个过程中发生了隐式转换：下面这个过程就是发生了类型转换

由于发生类型转换过程中(字符串转换为bit类型)遇到了数据截断错误(从value_out_of_range等信息就可以看出),如下截图所示

而优化器应该是根据一定的逻辑判断，得到这个值不存在索引中，从而就判断没有匹配的记录，直接返回空的结果集了，根本不去走扫描全表或走索引查找等操作。

        <span style="line-height: 26px">"empty_result"</span>: {<br>          <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>        } /* empty_result */<br>
        <span style="line-height: 26px">"empty_result"</span>: {<br>          <span style="line-height: 26px">"cause"</span>: <span style="line-height: 26px">"no matching row in const table"</span><br>        } /* empty_result */<br>
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */

当然这里仅仅是根据trace的信息做的一个判断，如有错误或不谨慎的地方，敬请谅解。毕竟没有深入分析过源码。

那么为什么没有索引的话，SQL语句的结果就是正确的呢？ 难道没有发生类型转换吗？ 难度没有发生数据截断错误吗？那么我们就继续trace跟踪分析看看，如下所示

mysql> <span style="font-weight: bold;line-height: 26px">drop</span> <span style="font-weight: bold;line-height: 26px">index</span> ix_student_attend_n1 <span style="font-weight: bold;line-height: 26px">on</span> student_attend;<br>Query OK, 0 rows affected (0.03 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE=<span style="line-height: 26px">"enabled=on"</span>,END_MARKERS_IN_JSON=<span style="font-weight: bold;line-height: 26px">on</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE_MAX_MEM_SIZE=<span style="line-height: 26px">1000000</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SELECT</span> * <span style="font-weight: bold;line-height: 26px">FROM</span> INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;<br>*************************** 1. row ***************************<br>                            QUERY: <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span><br>                            <span style="font-weight: bold;line-height: 26px">TRACE</span>: {<br>  <span style="line-height: 26px">"steps"</span>: [<br>    {<br>      <span style="line-height: 26px">"join_preparation"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"expanded_query"</span>: <span style="line-height: 26px">"/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_preparation */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_optimization"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"condition_processing"</span>: {<br>              <span style="line-height: 26px">"condition"</span>: <span style="line-height: 26px">"WHERE"</span>,<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"steps"</span>: [<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"equality_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"constant_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"trivial_condition_removal"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>            } <span style="font-style: italic;line-height: 26px">/* condition_processing */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"substitute_generated_columns"</span>: {<br>            } <span style="font-style: italic;line-height: 26px">/* substitute_generated_columns */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"table_dependencies"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"row_may_be_null"</span>: <span style="line-height: 26px">false</span>,<br>                <span style="line-height: 26px">"map_bit"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"depends_on_map_bits"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* depends_on_map_bits */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* table_dependencies */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"ref_optimizer_key_uses"</span>: [<br>            ] <span style="font-style: italic;line-height: 26px">/* ref_optimizer_key_uses */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"rows_estimation"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"table_scan"</span>: {<br>                  <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">10</span>,<br>                  <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">0.25</span><br>                } <span style="font-style: italic;line-height: 26px">/* table_scan */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* rows_estimation */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"considered_execution_plans"</span>: [<br>              {<br>                <span style="line-height: 26px">"plan_prefix"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* plan_prefix */</span>,<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"best_access_path"</span>: {<br>                  <span style="line-height: 26px">"considered_access_paths"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"rows_to_scan"</span>: <span style="line-height: 26px">10</span>,<br>                      <span style="line-height: 26px">"access_type"</span>: <span style="line-height: 26px">"scan"</span>,<br>                      <span style="line-height: 26px">"resulting_rows"</span>: <span style="line-height: 26px">10</span>,<br>                      <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">1.25</span>,<br>                      <span style="line-height: 26px">"chosen"</span>: <span style="line-height: 26px">true</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* considered_access_paths */</span><br>                } <span style="font-style: italic;line-height: 26px">/* best_access_path */</span>,<br>                <span style="line-height: 26px">"condition_filtering_pct"</span>: <span style="line-height: 26px">100</span>,<br>                <span style="line-height: 26px">"rows_for_plan"</span>: <span style="line-height: 26px">10</span>,<br>                <span style="line-height: 26px">"cost_for_plan"</span>: <span style="line-height: 26px">1.25</span>,<br>                <span style="line-height: 26px">"chosen"</span>: <span style="line-height: 26px">true</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* considered_execution_plans */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"attaching_conditions_to_tables"</span>: {<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"attached_conditions_computation"</span>: [<br>              ] <span style="font-style: italic;line-height: 26px">/* attached_conditions_computation */</span>,<br>              <span style="line-height: 26px">"attached_conditions_summary"</span>: [<br>                {<br>                  <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                  <span style="line-height: 26px">"attached"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* attached_conditions_summary */</span><br>            } <span style="font-style: italic;line-height: 26px">/* attaching_conditions_to_tables */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"finalizing_table_conditions"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"original_table_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>                <span style="line-height: 26px">"final_table_condition   "</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* finalizing_table_conditions */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"refine_plan"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* refine_plan */</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_optimization */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_execution"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_execution */</span><br>    }<br>  ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>}<br>MISSING_BYTES_BEYOND_MAX_MEM_SIZE: <span style="line-height: 26px">0</span><br>          INSUFFICIENT_PRIVILEGES: <span style="line-height: 26px">0</span><br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-weight: bold;line-height: 26px">ERROR</span>: <br><span style="font-weight: bold;line-height: 26px">No</span> <span style="font-weight: bold;line-height: 26px">query</span> specified<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> optimizer_trace=<span style="line-height: 26px">"enabled=off"</span>;<br>Query OK, 0 rows affected (0.00 sec)<br>
mysql> <span style="font-weight: bold;line-height: 26px">drop</span> <span style="font-weight: bold;line-height: 26px">index</span> ix_student_attend_n1 <span style="font-weight: bold;line-height: 26px">on</span> student_attend;<br>Query OK, 0 rows affected (0.03 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE=<span style="line-height: 26px">"enabled=on"</span>,END_MARKERS_IN_JSON=<span style="font-weight: bold;line-height: 26px">on</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> OPTIMIZER_TRACE_MAX_MEM_SIZE=<span style="line-height: 26px">1000000</span>;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span>;<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>| id | std_id | class_id | is_attend            |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>|  1 |   1001 |        1 | 0x01                 |<br>|  3 |   1001 |        3 | 0x01                 |<br>|  4 |   1001 |        4 | 0x01                 |<br>|  5 |   1001 |        5 | 0x01                 |<br>|  7 |   1002 |        1 | 0x01                 |<br>|  8 |   1002 |        2 | 0x01                 |<br>+<span style="font-style: italic;line-height: 26px">----+--------+----------+----------------------+</span><br>6 rows in <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SELECT</span> * <span style="font-weight: bold;line-height: 26px">FROM</span> INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;<br>*************************** 1. row ***************************<br>                            QUERY: <span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">'1'</span><br>                            <span style="font-weight: bold;line-height: 26px">TRACE</span>: {<br>  <span style="line-height: 26px">"steps"</span>: [<br>    {<br>      <span style="line-height: 26px">"join_preparation"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"expanded_query"</span>: <span style="line-height: 26px">"/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_preparation */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_optimization"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>          {<br>            <span style="line-height: 26px">"condition_processing"</span>: {<br>              <span style="line-height: 26px">"condition"</span>: <span style="line-height: 26px">"WHERE"</span>,<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"steps"</span>: [<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"equality_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"constant_propagation"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                },<br>                {<br>                  <span style="line-height: 26px">"transformation"</span>: <span style="line-height: 26px">"trivial_condition_removal"</span>,<br>                  <span style="line-height: 26px">"resulting_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>            } <span style="font-style: italic;line-height: 26px">/* condition_processing */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"substitute_generated_columns"</span>: {<br>            } <span style="font-style: italic;line-height: 26px">/* substitute_generated_columns */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"table_dependencies"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"row_may_be_null"</span>: <span style="line-height: 26px">false</span>,<br>                <span style="line-height: 26px">"map_bit"</span>: <span style="line-height: 26px">0</span>,<br>                <span style="line-height: 26px">"depends_on_map_bits"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* depends_on_map_bits */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* table_dependencies */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"ref_optimizer_key_uses"</span>: [<br>            ] <span style="font-style: italic;line-height: 26px">/* ref_optimizer_key_uses */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"rows_estimation"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"table_scan"</span>: {<br>                  <span style="line-height: 26px">"rows"</span>: <span style="line-height: 26px">10</span>,<br>                  <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">0.25</span><br>                } <span style="font-style: italic;line-height: 26px">/* table_scan */</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* rows_estimation */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"considered_execution_plans"</span>: [<br>              {<br>                <span style="line-height: 26px">"plan_prefix"</span>: [<br>                ] <span style="font-style: italic;line-height: 26px">/* plan_prefix */</span>,<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"best_access_path"</span>: {<br>                  <span style="line-height: 26px">"considered_access_paths"</span>: [<br>                    {<br>                      <span style="line-height: 26px">"rows_to_scan"</span>: <span style="line-height: 26px">10</span>,<br>                      <span style="line-height: 26px">"access_type"</span>: <span style="line-height: 26px">"scan"</span>,<br>                      <span style="line-height: 26px">"resulting_rows"</span>: <span style="line-height: 26px">10</span>,<br>                      <span style="line-height: 26px">"cost"</span>: <span style="line-height: 26px">1.25</span>,<br>                      <span style="line-height: 26px">"chosen"</span>: <span style="line-height: 26px">true</span><br>                    }<br>                  ] <span style="font-style: italic;line-height: 26px">/* considered_access_paths */</span><br>                } <span style="font-style: italic;line-height: 26px">/* best_access_path */</span>,<br>                <span style="line-height: 26px">"condition_filtering_pct"</span>: <span style="line-height: 26px">100</span>,<br>                <span style="line-height: 26px">"rows_for_plan"</span>: <span style="line-height: 26px">10</span>,<br>                <span style="line-height: 26px">"cost_for_plan"</span>: <span style="line-height: 26px">1.25</span>,<br>                <span style="line-height: 26px">"chosen"</span>: <span style="line-height: 26px">true</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* considered_execution_plans */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"attaching_conditions_to_tables"</span>: {<br>              <span style="line-height: 26px">"original_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>              <span style="line-height: 26px">"attached_conditions_computation"</span>: [<br>              ] <span style="font-style: italic;line-height: 26px">/* attached_conditions_computation */</span>,<br>              <span style="line-height: 26px">"attached_conditions_summary"</span>: [<br>                {<br>                  <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                  <span style="line-height: 26px">"attached"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>                }<br>              ] <span style="font-style: italic;line-height: 26px">/* attached_conditions_summary */</span><br>            } <span style="font-style: italic;line-height: 26px">/* attaching_conditions_to_tables */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"finalizing_table_conditions"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span>,<br>                <span style="line-height: 26px">"original_table_condition"</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span>,<br>                <span style="line-height: 26px">"final_table_condition   "</span>: <span style="line-height: 26px">"(`student_attend`.`is_attend` = '1')"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* finalizing_table_conditions */</span><br>          },<br>          {<br>            <span style="line-height: 26px">"refine_plan"</span>: [<br>              {<br>                <span style="line-height: 26px">"table"</span>: <span style="line-height: 26px">"`student_attend`"</span><br>              }<br>            ] <span style="font-style: italic;line-height: 26px">/* refine_plan */</span><br>          }<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_optimization */</span><br>    },<br>    {<br>      <span style="line-height: 26px">"join_execution"</span>: {<br>        <span style="line-height: 26px">"select#"</span>: <span style="line-height: 26px">1</span>,<br>        <span style="line-height: 26px">"steps"</span>: [<br>        ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>      } <span style="font-style: italic;line-height: 26px">/* join_execution */</span><br>    }<br>  ] <span style="font-style: italic;line-height: 26px">/* steps */</span><br>}<br>MISSING_BYTES_BEYOND_MAX_MEM_SIZE: <span style="line-height: 26px">0</span><br>          INSUFFICIENT_PRIVILEGES: <span style="line-height: 26px">0</span><br><span style="line-height: 26px">1</span> <span style="font-weight: bold;line-height: 26px">row</span> <span style="font-weight: bold;line-height: 26px">in</span> <span style="font-weight: bold;line-height: 26px">set</span> (<span style="line-height: 26px">0.00</span> sec)<br><br><span style="font-weight: bold;line-height: 26px">ERROR</span>: <br><span style="font-weight: bold;line-height: 26px">No</span> <span style="font-weight: bold;line-height: 26px">query</span> specified<br><br>mysql> <span style="font-weight: bold;line-height: 26px">SET</span> optimizer_trace=<span style="line-height: 26px">"enabled=off"</span>;<br>Query OK, 0 rows affected (0.00 sec)<br>
mysql> drop index ix_student_attend_n1 on student_attend;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student_attend where is_attend='1';
+----+--------+----------+----------------------+
| id | std_id | class_id | is_attend            |
+----+--------+----------+----------------------+
|  1 |   1001 |        1 | 0x01                 |
|  3 |   1001 |        3 | 0x01                 |
|  4 |   1001 |        4 | 0x01                 |
|  5 |   1001 |        5 | 0x01                 |
|  7 |   1002 |        1 | 0x01                 |
|  8 |   1002 |        2 | 0x01                 |
+----+--------+----------+----------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
                            QUERY: select * from student_attend where is_attend='1'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student_attend`.`id` AS `id`,`student_attend`.`std_id` AS `std_id`,`student_attend`.`class_id` AS `class_id`,`student_attend`.`is_attend` AS `is_attend` from `student_attend` where (`student_attend`.`is_attend` = '1')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student_attend`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student_attend`",
                "table_scan": {
                  "rows": 10,
                  "cost": 0.25
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`student_attend`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 1.25,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 1.25,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`student_attend`.`is_attend` = '1')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`student_attend`",
                  "attached": "(`student_attend`.`is_attend` = '1')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`student_attend`",
                "original_table_condition": "(`student_attend`.`is_attend` = '1')",
                "final_table_condition   ": "(`student_attend`.`is_attend` = '1')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`student_attend`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)

从上面trace信息来看，似乎执行计划先进行全表扫描，然后过滤记录，输出信息里面没有value_out_of_range这类信息，似乎没有发生数据截断。具体步骤跟之前的trace信息有很大不同。具体只看到了下面这些信息，但是更多信息我也看不出来。不清楚底层到底做了啥。

小结

关于bit类型的字段，我们写SQL的时候，不要使用字符串，避免发生隐式类型转换。正确的写法应该是下面这种方式

<span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>或<br><span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>
<span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=b<span style="line-height: 26px">'1'</span>;<br>或<br><span style="font-weight: bold;line-height: 26px">select</span> * <span style="font-weight: bold;line-height: 26px">from</span> student_attend <span style="font-weight: bold;line-height: 26px">where</span> is_attend=<span style="line-height: 26px">1</span>;<br>
select * from student_attend where is_attend=b'1';
或
select * from student_attend where is_attend=1;

DBA在给bit类型创建索引的时候也必须谨慎处理，跟开发和Support人员多协商沟通，告知他们可能出现这种情况，因为你可能没法控制开发人员写出这样的SQL。

展开阅读全文
© 版权声明
本站所有资源来自于网络，仅供学习与参考，请勿用于商业用途，否则产生的一切后果将由您(转载者)自己承担！
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
THE END
MySql数据库编程开发
支持一下吧
点赞15 分享
6cefee6820d8cc05的头像-牛翰网
相关推荐
评论 抢沙发
头像
请文明发言！
提交
头像

昵称

取消
昵称

请填写用户信息：

  • 昵称(必填)
  • 邮箱(必填)
表情
[aoman][baiyan][bishi][bizui][cahan][ciya][dabing][daku][deyi][doge][fadai][fanu][fendou][ganga][guzhang][haixiu][hanxiao][zuohengheng][zhuakuang][zhouma][zhemo][zhayanjian][zaijian][yun][youhengheng][yiwen][yinxian][xu][xieyanxiao][xiaoku][xiaojiujie][xia][wunai][wozuimei][weixiao][weiqu][tuosai][tu][touxiao][tiaopi][shui][se][saorao][qiudale][qinqin][qiaoda][piezui][penxue][nanguo][liulei][liuhan][lenghan][leiben][kun][kuaikule][ku][koubi][kelian][keai][jingya][jingxi][jingkong][jie][huaixiao][haqian][aini][OK][qiang][quantou][shengli][woshou][gouyin][baoquan][aixin][bangbangtang][xiaoyanger][xigua][hexie][pijiu][lanqiu][juhua][hecai][haobang][caidao][baojin][chi][dan][kulou][shuai][shouqiang][yangtuo][youling]
代码

请输入代码：

确认
快捷回复
谢谢你的分享，我从中学到了很多！
教程很好用，谢谢！
好东西，学习一下！
楼主听话，快到碗里来！
路过一下，我只是来打酱油的！
水帖美如花，养护靠大家！

    暂无评论内容

S/4 HANA 中的 Email Template

postgis_raster 从 2.x 升级至 3.x

webstorm破解激活2024最新永久激活码教程(含win+mac)

解决vs2022 工具箱中不显示 Devexpress控件的问题

chatGPT前端流式输出js实现三种方法—fetch、SSE、websocket

springcloud/springboot集成NACOS 做注册和配置中心以及nacos源码分析

小米盒子3增强版root破解教程（MDZ-18-AA型号）

KMS在线激活Win11/10/ 8/ 7和Office/Visio/Project 2021~2010

阿里二面：Kafka中如何保证消息的顺序性？这周被问到两次了

NavicatPremium16破解详解

protobuf

go数据类型-sync.map

Python中的函数未定义的错误

Go切片是值传递还是引用传递？

gRPC with JWT

github.com/json-iterator/go 详细教程

Go 的函数，方法和接口

【matplotlib基础】–刻度

用go封装和实现扫码登录

掌握Go类型内嵌：设计模式与架构的新视角

领导：谁再用 Redis 实现过期订单关闭，立马滚蛋！

RocketMQ源码详解(消息存储、Consumer)

【老马】加密机加解密系列汇总篇(共计13篇)

Java-Stream API

wso2~apim_metrics的配置与二次开发

RocketMQ源码详解(NameServer、Producer)

【老马】离线版金融敏感信息加解密组件开源项目 encryption-local

SpringBoot3整合AI

wso2~对接外部认证系统keycloak

敏感词过滤 + 限流

iNeuOS工业互联网操作系统，高效采集数据配置与应用

ElasticSearch8 – 基础概念和映射

让运维无忧，实战解析巡检报告功能实现方案

Apache SeaTunnel 社区 3 月月报

天翼云入选“2023年度数据要素价值创新标杆示范案例”！

.net中优秀依赖注入框架Autofac看一篇就够了

环境的搭建

Apache SeaTunnel 及 Web 功能部署指南(小白版)

直播预约丨《袋鼠云大数据实操指南》No.1：从理论到实践，离线开发全流程解析

CloudCanal x Hive 构建高效的实时数仓

标签云
C++ReactVueOracleJSsqlNETnode.js人工智能JavaScriptphpASP.NET计算机视觉正则表达式json前端鸿蒙开发AngularVue3C语言QtCoreServertypescript数据nodenodejs安装文件字符串图片npm数据库SQLServer函数报错uniapp人脸识别MariaDBmysql配置Excel组件上传API微信小程序数组java下载查询