优化SQL语句的一般步骤
1 通过show status命令了解各种SQL的执行频率。
格式:mysql> show [session|global]status;
其中:session(默认)表示当前连接,
global表示自数据库启动至今
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%’;
mysql>show global status like ‘Com_%’;
参数说明:
Com_XXX表示每个XXX语句执行的次数如:
Com_select 执行select操作的次数,一次查询只累计加1
Com_update 执行update操作的次数
Com_insert 执行insert操作的次数,对批量插入只算一次。
Com_delete 执行delete操作的次数
只针对于InnoDB存储引擎的。
InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
InnoDB_rows_deleted 执行delete操作的次数
其他:
connections 连接mysql的数量
Uptime 服务器已经工作的秒数
Slow_queries:慢查询的次数
2 定位执行效率较低的SQL语句
1)explain select * from table where id=1000;
2)desc select * from table where id=1000;
3 通过EXPLAIN分析较低效SQL的执行计划
mysql> explain select count(*) from stu where name like "a%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu
type: range
possible_keys: name,ind_stu_name
key: name
key_len: 50
ref: NULL
rows: 8
Extra: Using where; Using index
1 row in set (0.00 sec)
每一列的简单解释
id: 1
select_type: SIMPLE 表示select的类型,常见的取值有SIMPLE()简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SESECT)等
table: stu 输出结果集的表
type: range 表示表的连接类型,性能有好到差:system(表仅一行)、const(只一行匹配)、eq_ref(对于前面的每一行使用主键和唯一)、ref(同eq_ref,但没有使用主键和唯一)、ref_or_null(同前面对null查询)、index_merge(索引合并优化)、unique_subquery(主键子查询)、index_subquery(非主键子查询)、range(表单中的范围查询)、index(都通过查询索引来得到数据)、all(通过全表扫描得到的数据)
possible_keys: name,ind_stu_name 表查询时可能使用的索引。
key: name 表示实际使用的索引。
key_len: 50 索引字段的长度
ref: NULL
rows: 8 扫描行的数量
Extra: Using where; Using index 执行情况的说明和描述
索引问题
索引是数据库优化中最常见也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的SQL性能问题。
索引的存储分类
MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独一的一个文件;InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。
MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,例如name字段,可以只取name的前4个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
mysql>create index ind_company2_name on company2(name(4));
其中company表名 ind_company2_name索引名
MySQL如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。
1、使用索引
(1)对于创建的多列索引,只要查询的条件中用到最左边的列,索引一般就会被使用。如下创建一个复合索引。
mysql>create index ind_sales2_com_mon onsales2(company_id,moneys);
然后按company_id进行查询,发现使用到了复合索引
mysql>explain select * from sales2 where company_id=2006\G
使用下面的查询就没有使用到复合索引。
mysql>explain select * from sales2 where moneys=1\G
(2) 使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用,如下:
mysql> explain select * from company2 where name like "%3"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
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 company2 where name like “3%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set (0.00 sec)
(3)如果对大的文本进行搜索,使用全文索引而不使用 like“%...%”.
(4)如果列名是索引,使用column_name is null将使用索引。如下
mysql> explain select * from company2 where name is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
2、存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如如果列key_part1均匀分布在1到100之间,查询时使用索引就不是很好
mysql>select * from table_name where key_part1>1 and key_part<90;
(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
(3)用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
mysql>show index from sales\G
*************************** 1. row ***************************
… …
key_name: ind_sales_year
seq_in_index:1
Column_name: year
… …
(4)如果不是索引列的第一部分,如下例子:可见虽然在money上面建有复合索引,但是由于money不是索引的第一列,那么在查询中这个索引也不会被MySQL采用。
mysql> explain select * from sales2 where moneys=1 \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)
(5)如果like是以%开始,可见虽然在name上面建有索引,但是由于where条件中like的值的“%”在第一位了,那么MySQL也会采用这个索引。
mysql> explain select * from company2 where name like‘%3’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
(6)如果列类型是字符串,但在查询时把一个数值型常量赋值给了一个字符型的列名name,那么虽然在name列上有索引,但是也没有用到。
mysql> explain select * from company2 where name=294\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
而下面的sql语句就可以正确使用索引
mysql> explain select * from company2 where name=‘294’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
3 查看索引使用情况
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救。
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)
两个简单实用的优化方法
分析表的语法如下:(检查一个或多个表是否有错误 )
mysql> 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.01 sec)
优化表的语法格式:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化。这个命令可以将表中的空间碎片进行合并,但是此命令只对MyISAM、BDB和InnoDB表起作用。
mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)
4 常用SQL的优化
1 大批量插入数据
当用load命令导入数据的时候,适当设置可以提高导入的速度。
对于MyISAM存储引擎的表,可以通过以下方式快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新,可以提高速度,注意:对InnoDB表无效。
没有使用打开或关闭MyISAM表非唯一索引:
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2 fields terminated by “,”;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
使用打开或关闭MyISAM表非唯一索引:
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enable keys;
Query OK,0 rows affected (12.25 sec)
以上对MyISAM表的数据导入,但对于InnoDB表并不能提高导入数据的效率
(1)针对于InnoDB类型表数据导入的优化
因为InnoDB表的按照主键顺序保存的,所以将导入的数据主键的顺序排列,可以有效地提高导入数据的效率。
使用test3.txt文本是按表film_test4主键存储顺序保存的
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt没有任何顺序的文本(效率慢了1.12倍)
mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
(2)关闭唯一性效验可以提高导入效率
在导入数据前先执行set unique_checks=0,关闭唯一性效验,在导入结束后执行set unique_checks=1,恢复唯一性效验,可以提高导入效率。
当unique_checks=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当unique_checks=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
(3)关闭自动提交可以提高导入效率
在导入数据前先执行set autocommit=0,关闭自动提交事务,在导入结束后执行set autocommit=1,恢复自动提交,可以提高导入效率。
当autocommit=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当autocommit=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
2 优化insert语句
尽量使用多个值表的insert语句,这样可以大大缩短客户与数据库的连接、关闭等损耗。
可以使用insert delayed(马上执行)语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。
可以增加bulk_insert_buffer_size 变量值的方法来提高速度,但是只对MyISAM表使用
当从一个文件中装载一个表时,使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。
3 优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗,则可以使用使用order by null来禁止排序:
如下没有使用order by null来禁止排序
mysql> explain select id,sum(moneys) from sales2 group by id\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;Using filesort
1 row in set (0.00 sec)
如下使用order by null的效果:
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)