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)
测试样例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/