mariadb 分割槽表 不能使用 ICP

資料庫工作筆記發表於2023-09-21

來源：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 為了想辦法繞過這個問題

我用了等號方式和函式方式，在函式方式的時候又踩了一個引數的坑！


最終經過一系列操作之後總算回到了正確的路上。



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2984931/，如需轉載，請註明出處，否則將追究法律責任。

相關文章