第18章 SQL 优化

mysql> show  status like 'Com_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_assign_to_keycache      | 0     |
| Com_alter_db                | 0     |
| Com_alter_db_upgrade        | 0     |
| Com_alter_event             | 0     |
| Com_alter_function          | 0     |
| Com_alter_instance          | 0     |
| Com_alter_procedure         | 0     |
| Com_alter_server            | 0     |
| Com_alter_table             | 0     |
.......

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 5     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 2055  |
+-----------------------+-------+
6 rows in set (0.00 sec)

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 

mysql> analyze table sales;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.sales | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED 
| CHANGED} 

mysql> check table sales;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> create view sales_view3 as select * from sales3;
Query OK, 0 rows affected (0.00 sec)

mysql> check table sales_view3;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| sakila.sales_view3 | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> drop table sales3;
Query OK, 0 rows affected (0.00 sec)

mysql> check table sales_view3\G;
*************************** 1. row ***************************
Table: sakila.sales_view3
Op: check
Msg_type: error
Msg_text: View 'sakila.sales_view3' references invalid table(s) or column(s) or function(s) 
or definer/invoker of view lack rights to use them
1 row in set (0.00 sec)

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> explain select id,sum(moneys) from sales2 group by id\G;
# 此处的id是没有设置索引的，因为如果设置索引默认就是已经排好序的了
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select id,sum(moneys) from sales2 group by id order by null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary
1 row in set (0.00 sec)

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2；
--对不同的关键字使用 ORDER BY：

mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_id
key: ind_sales2_id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec).

mysql> explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales2 
type: ALL
possible_keys: NULL 
key: NULL 
key_len: NULL
ref: NULL 
rows: 1000 
Extra: Using where
1 row in set (0.00 sec).

mysql> explain select * from sales2 where id > 0 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: ind_sales2_id
key: NULL 
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from sales2 force index (ind_sales2_id) where id > 0 
\G;
*************************** 1. row *************************** 
id: 1
select_type: SIMPLE 
table: sales2 
type: range
possible_keys: ind_sales2_id 
key: ind_sales2_id  
key_len: 5
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec).