mysql原理和优化使用 > mysql查询优化
mysql 提高查询性能 - 延迟关联


    延迟关联在解决一个大表分页查询非常有用. 下面是几个应用实例

    下面一个采集表的结构 ,我们要实现一个很简单的功能按照d排序每一页10条从这个表查询数据.当页码越来越大的时候sql执行的时间也会越来也长.

SELECT SQL_NO_CACHE * FROM caiji  ORDER BY id LIMIT 40000,10

目前我的数据库有6w条数据,执行上面语句平均时间在0.1秒左右.

在学习了sql索引覆盖后我们可以利用索引覆盖先查询出符合条件的id(主键) 然后在用id(主键) 去查询对应的数据,这个就是文章的标题延迟关联技术.

我们可以使用下面语句提高sql执行效率. 下面语句在我电脑上测试平均执行时间在0.004秒.提示效果还是相等明显.

SELECT SQL_NO_CACHE * FROM caiji RIGHT JOIN (SELECT id FROM caiji  LIMIT 40000,10) AS t ON t.id = caiji.id

下面是表结构

 CREATE TABLE `caiji` (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
    `source_id` varchar(20) DEFAULT NULL COMMENT '来源-字符串 xl -新浪 tx-腾讯',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
    `tag` varchar(100) DEFAULT '0' COMMENT '分类',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    `title` varchar(255) DEFAULT NULL COMMENT '帖子标题',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
    `content` text NOT NULL COMMENT '帖子内容',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
     /*其他字段...... */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
    PRIMARY KEY (`id`)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
) ENGINE=MyISAM AUTO_INCREMENT=62937 DEFAULT CHARSET=utf8



下面记录一些使用延迟关联可能出现的问题: 

EXPLAIN SELECT SQL_NO_CACHE * FROM employees   LIMIT 250000,100
#mysql5.8 不支持在in 子查询中使用limit语句, This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
SELECT SQL_NO_CACHE * FROM employees WHERE emp_no IN  ( SELECT emp_no FROM employees LIMIT 250000,100 )

#这里不能使用left join 
SELECT SQL_NO_CACHE * ,t.emp_no AS t_emp_no FROM employees LEFT JOIN (SELECT emp_no FROM employees LIMIT 250000,100) AS t ON t.emp_no = employees.emp_no

#可以使用right join 
EXPLAIN SELECT SQL_NO_CACHE * FROM employees RIGHT JOIN (SELECT emp_no FROM employees LIMIT 250000,100) AS t ON employees.emp_no = t.emp_no 

#可以使用inner join 
EXPLAIN SELECT SQL_NO_CACHE * FROM employees INNER JOIN (SELECT emp_no FROM employees LIMIT 250000,100) AS t ON employees.emp_no = t.emp_no 

SELECT * FROM `salaries`  LIMIT  2500000,10

SELECT s.* FROM `salaries` AS s RIGHT JOIN (SELECT emp_no ,from_date FROM salaries LIMIT  2500000,10) AS t ON  t.emp_no = s.emp_no AND t.from_date = s.from_date