高性能 MySql 阅读笔记

1.优化子查询包含in()的sql语句

user表　　

CREATE TABLE `user` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　mid_user表

CREATE TABLE `mid_user` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　使用in时的查询语句

SELECT * FROM `user` WHERE ID in(SELECT user_id FROM mid_user)

　　使用EXISTS改写sql语句

SELECT
	*
FROM
	`user`
WHERE
	EXISTS (
		SELECT
			mid_user.id
		FROM
			mid_user
		WHERE
			`user`.ID = mid_user.user_id
	)

　　EXISTS 会对外表user进行循环查询匹配，它不在乎后面的内表子查询的返回值是什么，只在乎有没有存在返回值，存在返回值，则条件为真，该条数据匹配成功，加入查询结果集中；如果没有返回值，条件为假，丢弃该条数据。

COUNT()的作用

　　是一个特殊的函数有两种非常不同的作用 1 它可以统计某个列值的数量 2 也可以统计行数

在统计列值的时候要求列值是非空的

COUNT() 的另一个作用是统计结果集的函数当MYSQL确认括号呢的表达式值不可能为空时实际上就是在统计行数最简单的就是当我们使用COUNT(*) 的时候这种情况下通配符 * 并不会像我们猜想的那样扩展成所有的列实际上他会忽略所有的列而直接统计所有的行数

　　我们发现一个最常见的错误就是,在括号内指定了一个列却希望统计结果集的行数如果希望知道的是结果集行数最好使用count(*) 这样写意义清晰性能也会很好

优化LIMIT分页

在偏移量非常大的时候,例如可能是LIMIT 1000,20这样的查询,这时 MYSQL 需要查询 1020 条记录然后只返回最后 20 条,前面 1000 条记录都将被抛弃,这样的代价非常高如果所有的页面被访问的频率都相同那么这样的查询平均需要访问半个表的数据要优化这种查询 1.要么是在分页中限制分页的数量 2要么时优化偏移量的性能

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列然后根据需要做的一次关联操作再返回所需要的列对于偏移量很大的时候这样做的效率会提升非常大考虑下面的查询

mysql>SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

　　如果这个表非常大那么这个查询最好改成下面的样子

mysql>SELETC film.film_id,film.description
           FROM sakila.film
                INNER JOIN (
                     SELECT film_id from sakila.film
                     ORDER BY  title LIMIT 50,5
                     ) AS lim USING(film_id);

　　这里的 " 延迟关联 " 将大大提升查询效率他让MYSQL 扫描尽可能少的页面获取需要访问的记录后再根据关联列回原表查询需要的所有列这个技术也可以用于有有虎啊关联查询中的LIMIT子句

有时页可以将LIMIT查询转换为已知位置的查询让MYSQL通过范围扫描获取到对应的结果例如如果在一个位置列上有索引并且预先计算出了边界值上面的查询就可以改写为

mysql> SELECT film_id,description FROM sakila.film
            WHERE positopn BETWEEN 50 AND 54 ORDER BY position;