mysql数据库sql优化_mysql数据库SQL优化

1.了解sql的执行频率

show global status like 'Com_%';

show global status like 'Innodb_%';

show global status like 'Connections';

show global status like 'Uptime';

show global status like 'Slow_queries';

2.定位低效的sql语句

慢查询

show processlist；

3.通过explain分析低效sql执行计划

mysql索引中包含null，oracle索引不包含null

(1)select_type:

simple：简单表，不使用表连接或者子查询

primary：主查询，即外层的查询

union：union连接中的第二个或者后面的查询语句

subquery：子查询中的第一个select

type：

all：

index：索引全扫描

range：索引范围扫描

ref：非唯一索引等值查询

eq_ref:唯一索引等值查询

extra:

using index :通过索引就能拿到数据

using where：表示优化器需要通过索引返回表查询数据

using filesort: 做了排序操作，(通过索引排序不算在内)

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

mysql数据版本

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| version       | 5.1.73 |

+---------------+--------+

explain select * from film where rating>9;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL |  949 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

explain select title from film ;

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

|  1 | SIMPLE      | film  | index | NULL          | idx_title | 767     | NULL |  949 | Using index |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

explain select * from payment where customer_id>=300 and customer_id<=350;

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+

| id | select_type | table   | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+

|  1 | SIMPLE      | payment | range | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL | 1349 | Using where |

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+

explain select * from payment where customer_id=350;

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra |

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

|  1 | SIMPLE      | payment | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   23 |       |

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

explain select * from film a,film_text b where a.film_id=b.film_id;

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

|  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL             |  949 |             |

|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.a.film_id |    1 | Using where |

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

explain select * from (select * from customer where email='AARON.SELBY@sakilacustomer.org') a;

+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+

| id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows | Extra |

+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+

|  1 | PRIMARY     | | system | NULL          | NULL     | NULL    | NULL |    1 |       |

|  2 | DERIVED     | customer   | const  | uk_email      | uk_email | 153     |      |    1 |       |

+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+

type：(还有一些其他值)

ref_or_null：与ref类似，区别在于条件中包含对null的查询

index_merge:索引合并优化

unique_subquery:in后面是一个查询主键字段的子查询

index_subquery:in后面是查询非唯一索引字段的子查询

(2).explain extended详解

explain select sum(amount) from customer c,payment b where 1=1 and c.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org';

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | Extra       |

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

|  1 | SIMPLE      | c     | const | PRIMARY,uk_email   | uk_email           | 153     | const |    1 | Using index |

|  1 | SIMPLE      | b     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   28 |             |

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

explain extended select sum(amount) from customer c,payment b where 1=1 and c.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org';

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

|  1 | SIMPLE      | c     | const | PRIMARY,uk_email   | uk_email           | 153     | const |    1 |   100.00 | Using index |

|  1 | SIMPLE      | b     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   28 |   100.00 |             |

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `c` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = '77') and ('JANE.BENNETT@sakilacustomer.org' = 'JANE.BENNETT@sakilacustomer.org'))

(3)explain partitions 显示分区使用信息

4.通过show profile分析sql

select @@profiling;

+-------------+

| @@profiling |

+-------------+

|           0 |

+-------------+

set profiling=1;

show profiles；

show profile for query 8；

+--------------------+----------+

| Status             | Duration |

+--------------------+----------+

| starting           | 0.000062 |

| Opening tables     | 0.000044 |

| System lock        | 0.000004 |

| Table lock         | 0.000007 |

| init               | 0.000012 |

| optimizing         | 0.000007 |

| statistics         | 0.000013 |

| preparing          | 0.000007 |

| executing          | 0.000004 |

| Sending data       | 0.003732 |

| end                | 0.000079 |

| query end          | 0.000007 |

| freeing items      | 0.000025 |

| logging slow query | 0.000003 |

| cleaning up        | 0.000002 |

+--------------------+----------+

15 rows in set (0.00 sec)

注：Sending data状态表示MYSQL线程开始访问数据并把结果返回给客户端(所以包含io时间)；

select state,sum(duration) as total_r,

ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION)

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID=@query_id

),2) AS Pct_R,

count(*) as calls,

sum(duration)/count(*) as "r/call"

from information_schema.profiling

where query_id=@query_id

group by state

order by total_r desc;

show profile cpu for query 8;

MySQL5.6可以通过trace分析优化器如何选择执行计划

set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;

set optimizer_trace_max_mem_size=1000000;

select * from table_name;

select * from information_schema.optimizer_trace\G

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