查询性能优化

USE sakila;

SELECT SQL_NO_CACHE count(*)
FROM film_actor;

SHOW STATUS LIKE 'Last_query_cost'; (1)

-- 内关联查询 ----------------------------------------------------
SELECT
  tbl1.col1,
  tbl2.col2
FROM tbl1
  INNER JOIN tbl2 USING (col3)
WHERE tbl1.col1 IN (5, 6);

-- 用伪代码来解释 MySQL 关联执行的策略则是如下：
outer_iter = iteratro over tbl1 WHERE col1 IN (5, 6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iteratro over tbl2 WHERE col3 = outer_row.col3
    inner_row  = inner_iter.next
    while inner_row
        output [outer_row.col1, inner_row.col2]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

-- 左外关联查询 --------------------------------------------------

SELECT
  tbl1.col1,
  tbl2.col2
FROM tbl1
  LEFT OUTER JOIN tbl2 USING (col3)
WHERE tbl1.col1 IN (5, 6);

-- 用伪代码来解释 MySQL 关联执行的策略则是如下：
outer_iter = iteratro over tbl1 WHERE col1 IN (5, 6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iteratro over tbl2 WHERE col3 = outer_row.col3
    inner_row  = inner_iter.next
    if inner_row
        while inner_row
            output [outer_row.col1, inner_row.col2]
            inner_row = inner_iter.next
        end
    else
        output [outer_row.col1, NULL]
    end
    outer_row = outer_iter.next
end

USE sakila;

EXPLAIN
SELECT
  film.film_id,
  film.title,
  film.release_year,
  actor.actor_id,
  actor.first_name,
  actor.last_name
FROM film
  INNER JOIN film_actor USING (film_id)
  INNER JOIN actor USING (actor_id) \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 27
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.film_id
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

USE sakila;

EXPLAIN
SELECT STRAIGHT_JOIN
  film.film_id,
  film.title,
  film.release_year,
  actor.actor_id,
  actor.first_name,
  actor.last_name
FROM film
  INNER JOIN film_actor USING (film_id)
  INNER JOIN actor USING (actor_id) \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 5
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL

USE sakila;

EXPLAIN
SELECT
  film_id,
  language_id
FROM film
WHERE NOT EXISTS(
    SELECT *
    FROM film_actor
    WHERE film_actor.film_id = film.film_id);

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_language_id
      key_len: 1
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where; Using index (2)
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY  (1)
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 5
     filtered: 100.00
        Extra: Using index  (3)


-- 使用左外链接“优化”后的 SQL
EXPLAIN
SELECT
  film.film_id,
  film.language_id
FROM film
  LEFT OUTER JOIN film_actor USING (film_id)
WHERE film_actor.film_id IS NULL \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_language_id
      key_len: 1
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index (2)
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE  (1)
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 5
     filtered: 100.00
        Extra: Using where; Not exists; Using index (3)

-- 书中没有建表语句，根据上下文 SQL 创建
DROP TABLE IF EXISTS city;
CREATE TABLE city (
  id   INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = MyISAM;

-- 没有优化的 SQL，需要扫描大多数行
SELECT COUNT(*)
FROM city
WHERE id > 5;

-- 优化后的 SQL，只需要扫描少量的行
SELECT (SELECT COUNT(*)
        FROM city) - COUNT(*)
FROM city
WHERE id <= 5;

DROP TABLE IF EXISTS items;
CREATE TABLE items (
  id    INTEGER AUTO_INCREMENT PRIMARY KEY,
  color VARCHAR(50)
);

SELECT
  SUM(IF(color = 'blue', 1, 0)) AS blue,
  SUM(IF(color = 'red', 1, 0))  AS red
FROM items;

SELECT
  COUNT(color = 'blue' OR NULL) AS blue,
  COUNT(color = 'red' OR NULL)  AS red
FROM items;

SET @rownum := 0;
SELECT
  actor_id,
  @rownum := @rownum + 1 AS rownum
FROM actor
LIMIT 3;

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;

SELECT
  actor_id,
  @curr_cnt := cnt                                          AS cnt,
  @rank     := if(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
  @prev_cnt := @curr_cnt                                    AS dummy
FROM (
   SELECT
     actor_id,
     COUNT(*) AS cnt
   FROM film_actor
   GROUP BY actor_id
   ORDER BY cnt DESC
   LIMIT 10
) AS der;

-- 根据上下文推断的建表语句
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
  id          INTEGER AUTO_INCREMENT PRIMARY KEY,
  lastupdated TIMESTAMP
);

-- 常规做法
UPDATE tbl SET tbl.lastupdated = NOW() WHERE id = 1;
SELECT lastupdated FROM tbl WHERE id = 1;

-- 使用变量，无须访问数据表，更高效
UPDATE tbl SET tbl.lastupdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

SET @rownum := 0;
SELECT
  actor_id,
  @rownum AS rownum
FROM actor
WHERE (@rownum := @rownum + 1) <= 1;

SET @rownum := 0;
SELECT
  actor_id,
  first_name,
  @rownum AS rownum
FROM actor
WHERE @rownum <= 1
ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);

-- 建表语句是根据上下文推断的
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS users_archived;
CREATE TABLE users_archived (
  id INTEGER AUTO_INCREMENT PRIMARY KEY
);

-- 查询用户，热门用户中查不出来则查归档用户
SELECT
  greatest(@found := -1, id) AS id,
  'users'                    AS which_tbl
FROM users
WHERE id = 1

UNION ALL

SELECT
  id,
  'users_archived' AS which_tbl
FROM users_archived
WHERE id = 1 AND @found IS NULL

UNION ALL
-- 将变量充值，避免影响下次查询
SELECT
  1,
  'reset'
FROM dual
WHERE (@found := NULL) IS NOT NULL;