mysql資料庫SQL最佳化2

SQL最佳化2
測試樣例wget />
ICP: 5.6 Index Condition Pushdown(ICP)
應用場景：where條件中其中幾個欄位，在聯合索引的範圍內；
explain select * from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G
mysql> show index from rental;
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| rental |          0 | PRIMARY             |            1 | rental_id    | A         |       16298 |     NULL | NULL   |      | BTREE      |         |
| rental |          1 | idx_rental_date     |            1 | rental_date  | A         |       16298 |     NULL | NULL   |      | BTREE      |         |
| rental |          1 | idx_rental_date     |            2 | inventory_id | A         |       16298 |     NULL | NULL   |      | BTREE      |         |
| rental |          1 | idx_rental_date     |            3 | customer_id  | A         |       16298 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

查詢改寫
mysql> explain select * from actor where last_name like '%NI%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a,actor b where a.actor_id=b.actor_id;
+----+-------------+------------+--------+---------------+---------------------+---------+------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys | key                 | key_len | ref        | rows | Extra                    |
+----+-------------+------------+--------+---------------+---------------------+---------+------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL                | NULL    | NULL       |    4 |                          |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY             | 2       | a.actor_id |    1 |                          |
|  2 | DERIVED     | actor      | index  | NULL          | idx_actor_last_name | 137     | NULL       |  200 | Using where; Using index |
+----+-------------+------------+--------+---------------+---------------------+---------+------------+------+--------------------------+
注：innodb表上的二級索引實際上儲存索引欄位和主鍵欄位；

mysql> explain select * from customer where customer_id not in (select customer_id from payment);
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type        | table    | type           | possible_keys      | key                | key_len | ref  | rows | Extra       |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+
|  1 | PRIMARY            | customer | ALL            | NULL               | NULL               | NULL    | NULL |  541 | Using where |
|  2 | DEPENDENT SUBQUERY | payment  | index_subquery | idx_fk_customer_id | idx_fk_customer_id | 2       | func |   12 | Using index |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null;
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref                  | rows | Extra                   |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL               | NULL               | NULL    | NULL                 |  541 |                         |
|  1 | SIMPLE      | b     | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.a.customer_id |   12 | Using where; Not exists |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------------------+
2 rows in set (0.00 sec)
mysql5.5以下版本（包括5.5）子查詢不如關聯查詢（join)

mysql> explain select film_id,description from film order by title limit 50,5;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1028 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
mysql> explain select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id=b.film_id;
+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+
| id | select_type | table      | type   | possible_keys | key       | key_len | ref       | rows | Extra       |
+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL      | NULL    | NULL      |   55 | NULL        |
|  1 | PRIMARY     | a          | eq_ref | PRIMARY       | PRIMARY   | 2       | b.film_id |    1 | NULL        |
|  2 | DERIVED     | film       | index  | NULL          | idx_title | 767     | NULL      | 1028 | Using index |
+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+



注：資料型別出現隱式轉換的時候也不會使用索引

檢視索引使用情況
mysql> show status like 'Handler_read_key';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_read_key | 13    |
+------------------+-------+
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的效能改善不高；
mysql> show status like 'Handler_read_rnd_next';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 1323  |
+-----------------------+-------+
Handler_read_rnd_next的含義是在資料檔案中讀下一行的請求數，如果正進行大量表掃描，該值會很高；

定期分析、檢查、最佳化
analyze（myisam 相當於myisamchk -a，innodb）本語句用於分析和儲存表的關鍵字分佈，分析的結果將可以使得系統得到準確的統計資訊，使得sql能夠生成正確的執行計劃；
check（myisam，innodb） 檢查表的作用是檢查一個或多個表是否有錯誤；
optimize（myisam,innodb) 整理表空間碎片
alter table payment engine=innodb; 收回多餘的表空間
注意：以上四種操作對錶鎖定，資料不繁忙的時候操作；

批次插入資料
myisam
alter table tab_name disable keys;(非唯一索引）(差6倍）
load data
alter table tab_name enable keys;
bulk_insert_buffer_size

innodb
1.按主鍵順序插入（1倍）
2.set unique_checks=0
3.set autocommit=0

all
多行插入
不進記憶體
load=20insert

排序
max_length_for_sort_data
sort_buffer_size

group by
預設情況下，mysql對所有group by是有排序的如下圖，如果想避免排序可以order by null
mysql> explain select payment_date,sum(amount) from payment group by payment_date;
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key              | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | payment | index | NULL          | idx_payment_date | 15      | NULL | 15123 | Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select payment_date,sum(amount) from payment group by payment_date order by payment_date;
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key              | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | payment | index | NULL          | idx_payment_date | 15      | NULL | 15123 | Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)