来源：SQL开发与优化

其中有如下文章：

现有一个SQL 假设刚开始没有 KEY `idx_de3` (`dept_no`,`from_date`) 这个索引


root@maria3310.sock>[employees]>desc select * ,(select count(to_date) from dept_emp5 s force index(idx_de3_n1)  where s.dept_no = t.dept_no and from_date >='1900-01-03' and from_date <= '1985-01-01') s from t_group t \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 10        Extra:*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: s         type: refpossible_keys: idx_de3_n1          key: idx_de3_n1      key_len: 19          ref: employees.t.dept_no         rows: 164667        Extra: Using index condition; Using where2 rows in set (0.000 sec)

这里 Using index condition; Using where

其实已经用了ICP ,这是我在重现过程中的不严谨导致的。

但是实际优化的数据库只有using where 而没有 Using index condition;

如果使用了 ICP 那就没有后面的一堆事情了。

Connection id:          81Current database:       employeesCurrent user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server:                 MariaDBServer version:         10.6.8-MariaDB-log MariaDB ServerProtocol version:       10

生产库版本是10.4.22 我在10.6.8 里也重现

root@maria3310.sock>[employees]>show create table emp31\G*************************** 1. row ***************************       Table: emp31Create Table: CREATE TABLE `emp31` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  `n1` varchar(10) DEFAULT NULL,  `n2` varchar(10) DEFAULT NULL,  PRIMARY KEY (`emp_no`,`birth_date`),  KEY `idx_emp3_n1` (`first_name`,`birth_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.000 sec)
root@maria3310.sock>[employees]>show create table emp3\G*************************** 1. row ***************************       Table: emp3Create Table: CREATE TABLE `emp3` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  `n1` varchar(10) DEFAULT NULL,  `n2` varchar(10) DEFAULT NULL,  PRIMARY KEY (`emp_no`,`birth_date`),  KEY `idx_emp3_n1` (`first_name`,`birth_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY RANGE (to_days(`birth_date`))(PARTITION `p202102` VALUES LESS THAN (738215) ENGINE = InnoDB, PARTITION `p202103` VALUES LESS THAN (738246) ENGINE = InnoDB, PARTITION `p202104` VALUES LESS THAN (738276) ENGINE = InnoDB, PARTITION `p202105` VALUES LESS THAN (738307) ENGINE = InnoDB, PARTITION `p202106` VALUES LESS THAN (738337) ENGINE = InnoDB, PARTITION `p202107` VALUES LESS THAN (738368) ENGINE = InnoDB, PARTITION `p202108` VALUES LESS THAN (738399) ENGINE = InnoDB, PARTITION `p202109` VALUES LESS THAN (738429) ENGINE = InnoDB, PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)1 row in set (0.000 sec)

我创建了如上两个表一个是分区表 emp3，一个非分区表emp31

root@maria3310.sock>[employees]>desc select * from emp3 where first_name='Chirstian' and birth_date like '1954%'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: emp3         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: const         rows: 9        Extra: Using where1 row in set (0.001 sec)
root@maria3310.sock>[employees]>desc select * from emp31 where first_name='Chirstian' and birth_date like '1954%'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: emp31         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: const         rows: 1        Extra: Using index condition1 row in set (0.000 sec)

如上所示，非分区表是有 Extra: Using index condition 而分区表没有！！

root@maria3310.sock>[employees]>desc select t.* ,(select e.hire_date from emp3 e where e.first_name = t.first_name and  birth_date like '1954%' limit 1  ) c from emp31 t limit 10 \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1000        Extra:*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: e         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: employees.t.first_name         rows: 1        Extra: Using where2 rows in set (0.001 sec)
root@maria3310.sock>[employees]>desc select t.* ,(select e.hire_date from emp31 e where e.first_name = t.first_name and  birth_date like '1954%' limit 1  ) c from emp3 t limit 10 \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1008        Extra:*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: e         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: employees.t.first_name         rows: 1        Extra: Using index condition2 rows in set (0.000 sec)

我们在用子查询验证下，还是重现了非分区表不能使用ICP 这个特性

Server version:         8.0.31 MySQL Community Server - GPLProtocol version:       10
root@mysql3306.sock>[employees]>desc select t.* ,(select e.hire_date from emp3 e where e.first_name = t.first_name and  birth_date like '1954%' limit 1  ) c from emp31 t limit 10 \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1000     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: e   partitions: p202102,p202103,p202104,p202105,p202106,p202107,p202108,p202109,pmax         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: employees.t.first_name         rows: 1     filtered: 11.11        Extra: Using index condition2 rows in set, 2 warnings (0.00 sec)
root@mysql3306.sock>[employees]>desc select t.* ,(select e.hire_date from emp31 e where e.first_name = t.first_name and  birth_date like '1954%' limit 1  ) c from emp3 t limit 10 \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t   partitions: p202102,p202103,p202104,p202105,p202106,p202107,p202108,p202109,pmax         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1000     filtered: 100.00        Extra: NULL*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: e   partitions: NULL         type: refpossible_keys: idx_emp3_n1          key: idx_emp3_n1      key_len: 58          ref: employees.t.first_name         rows: 1     filtered: 11.11        Extra: Using index condition2 rows in set, 2 warnings (0.00 sec)

我又在

 5.7.36-log MySQL Community Server (GPL)

下面的版本里做了实验，还是可以使用ICP

MySQL 还是非常好用！！

这样经过昨天和今天的文章，总算把这个问题搞懂了。

根本原因是分区表不能使用ICP 为了想办法绕过这个问题

我用了等号方式和函数方式，在函数方式的时候又踩了一个参数的坑！

最终经过一系列操作之后总算回到了正确的路上。

mariadb 分区表 不能使用 ICP

mariadb 分区表不能使用 ICP