当前位置：首页 > 数据库 > MySQL教程 > mariadb 分区表 不能使用 ICP > 正文

mariadb 分区表 不能使用 ICP

来源：这里教程网 时间：2026-03-01 17:27:09 作者：

来源：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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: s
         type: ref
possible_keys: idx_de3_n1
          key: idx_de3_n1
      key_len: 19
          ref: employees.t.dept_no
         rows: 164667
        Extra: Using index condition; Using where
2 rows in set (0.000 sec)

这里 Using index condition; Using where

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

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

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

Connection id:          81
Current database:       employees
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.8-MariaDB-log MariaDB Server
Protocol version:       10

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

root@maria3310.sock>[employees]>show create table emp31\G
*************************** 1. row ***************************
       Table: emp31
Create 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=utf8mb4
1 row in set (0.000 sec)


root@maria3310.sock>[employees]>show create table emp3\G
*************************** 1. row ***************************
       Table: emp3
Create 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: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: const
         rows: 9
        Extra: Using where
1 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: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: const
         rows: 1
        Extra: Using index condition
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: e
         type: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: employees.t.first_name
         rows: 1
        Extra: Using where
2 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1008
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: e
         type: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: employees.t.first_name
         rows: 1
        Extra: Using index condition
2 rows in set (0.000 sec)

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

Server version:         8.0.31 MySQL Community Server - GPL
Protocol 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: ALL
possible_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: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: employees.t.first_name
         rows: 1
     filtered: 11.11
        Extra: Using index condition
2 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: ALL
possible_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: ref
possible_keys: idx_emp3_n1
          key: idx_emp3_n1
      key_len: 58
          ref: employees.t.first_name
         rows: 1
     filtered: 11.11
        Extra: Using index condition
2 rows in set, 2 warnings (0.00 sec)

我又在

 5.7.36-log MySQL Community Server (GPL)

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

MySQL 还是非常好用！！

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

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

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

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

编辑推荐：

相关推荐

最新软件资讯

数据库使用中一定掉过的坑：大小写敏感
数据库使用中一定掉过的坑：大小写敏感

热文推荐

天极热推