mysql数据库SQL优化2

SQL优化2
测试样例wget http://downloads.mysql.com/docs/sakila-db.zip

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29620572/viewspace-1809391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29620572/viewspace-1809391/