摘要:
mysql列存储引擎-POC-问题定位-索引相关
没有任何索引:
DDL:
CREATE TABLE `employees_noindex` (
`employee_id` int(11) NOT NULL DEFAULT '1',
`employee_name` varchar(50) NOT NULL,
`employee_sex` varchar(10) DEFAULT '男',
`hire_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`employee_mgr` int(11) DEFAULT NULL,
`employee_salary` float DEFAULT '3000',
`department_id` int(11) DEFAULT NULL
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4;
查询结果:
mysql> select
-> a.employee_id,
-> b.employee_name,
-> get_value(b.employee_id)
-> from
-> employees_noindex a
-> inner join employees_noindex b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
+-------------+---------------+--------------------------+
| employee_id | employee_name | get_value(b.employee_id) |
+-------------+---------------+--------------------------+
| 1 | David Tian | 3000 |
+-------------+---------------+--------------------------+
1 row in set, 1 warning (0.00 sec)
只保留主键索引:
DDL:
CREATE TABLE `employees_prindex` (
`employee_id` int(11) NOT NULL DEFAULT '1',
`employee_name` varchar(50) NOT NULL,
`employee_sex` varchar(10) DEFAULT '男',
`hire_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`employee_mgr` int(11) DEFAULT NULL,
`employee_salary` float DEFAULT '3000',
`department_id` int(11) DEFAULT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4;
查询结果:
select
a.employee_id,
get_value(b.employee_id)
from
employees_prindex a
left join employees_prindex b on
a.employee_id = b.employee_id
and b.employee_name = 'David Tian';
mysql> select
-> a.employee_id,
-> get_value(b.employee_id)
-> from
-> employees_prindex a
-> left join employees_prindex b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
+-------------+--------------------------+
| employee_id | get_value(b.employee_id) |
+-------------+--------------------------+
| 1 | NULL |
+-------------+--------------------------+
1 row in set, 1 warning (0.01 sec)
将调用自定义函数移到ON谓词后:
select
a.employee_id,
b.employee_id
from
employees_prindex a
left join employees_prindex b on
a.employee_id = b.employee_id
and get_value(b.employee_id) = 3000
and b.employee_name = 'David Tian';
mysql> select
-> a.employee_id,
-> b.employee_id
-> from
-> employees_prindex a
-> left join employees_prindex b on
-> a.employee_id = b.employee_id
-> and get_value(b.employee_id) = 3000
-> and b.employee_name = 'David Tian';
+-------------+-------------+
| employee_id | employee_id |
+-------------+-------------+
| 1 | NULL |
+-------------+-------------+
1 row in set, 1 warning (0.00 sec)
