第21章 SQL优化

21.1　SQL优化基础概念

• 4．基数、选择性、回表

mysql> create table t1 (id int , c1 char(20), c2 char(20), c3 char(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'a', 'b' , 'c');
Query OK, 1 row affected (0.01 sec)
mysql> create index idx_c1 on t1 (c1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain format=json select * from t1 where c1 = 'a';

......

mysql> drop index idx_c1 on t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain format=json select * from t1 where c1 = 'a';
......

mysql> truncate table t1;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values (10, 'a', 'b','c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'b', 'b','c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'c', 'b','c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (10, 'd', 'b','c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (10, 'e', 'b','c');
Query OK, 1 row affected (0.01 sec)
mysql> explain format=json select * from t1 where c1 = 'a';
......
mysql> drop index idx_c1 on t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain format=json select * from t1 where c1 = 'a';
......

SELECT stat_value AS pages, index_name
　　, stat_value * @@innodb_page_size / 1024 / 1024 AS size
FROM mysql.innodb_index_stats
WHERE (table_name = 'sbtest1'
　　AND database_name = 'sbtest'
　　AND stat_description = 'Number of pages in the index'
　　AND stat_name = 'size')
GROUP BY index_name;


SELECT stat_value AS pages, index_name
, SUM(stat_value) * @@innodb_page_size / 1024 / 1024 AS size
FROM mysql.innodb_index_stats
WHERE (table_name LIKE 't#P%'
　　AND database_name = 'test'
　　AND stat_description = 'Number of pages in the index'
　　AND stat_name = 'size')
GROUP BY index_name;

21.2　MySQL中的Join算法

• 1．Nested-Loop Join Algorithm（嵌套循环Join算法）

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

• 2．Block Nested-Loop Join Algorithm（块嵌套循环Join算法，即BNL算法）

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

---

上一篇：第20章　InnoDB锁 | 下一篇：第26章　SQL语句执行慢真假难辨