优化SQL语句的一般步骤

dickrong

　　优化SQL语句的一般步骤                                            

• 　　通过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:慢查询的次数

• 　　定位执行效率较低的SQL语句
  

explain select * from table where id=1000;desc select * from table where id=1000;

• 　　通过EXPLAIN分析较低效SQL的执行计划
  

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

　　每一列的简单解释

http://common.cnblogs.com/images/copycode.gif

id: 1select_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: NULLrows: 8 扫描行的数量Extra: Using where; Using index 执行情况的说明和描述

• 　　查看索引使用情况
  

　　如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。
　　Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

http://common.cnblogs.com/images/copycode.gif

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)
　　两个简单实用的优化方法                                                                 

• 　　分析表的语法如下:(检查一个或多个表是否有错误)
  

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

　　优化表的语法格式：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]　　如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

http://common.cnblogs.com/images/copycode.gif

mysql> optimize table sales;　　
+--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | sakila.sales | optimize | status | OK | +--------------+----------+----------+----------+ 1 row in set (0.05 sec)
　　常用SQL的优化                                                                           

• 　　大批量插入数据
  

　　当用load命令导入数据的时候，适当设置可以提高导入的速度。
　　对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS　　
loading the data
　　
ALTER TABLE tbl_name ENABLE KEYS
　　DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。

http://common.cnblogs.com/images/copycode.gif

--没有使用打开或关闭MyISAM表非唯一索引：mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;　　
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表并不能提高导入数据的效率

http://common.cnblogs.com/images/copycode.gif

　　（1）针对于InnoDB类型表数据导入的优化
　　因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。

http://common.cnblogs.com/images/copycode.gif

--使用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

http://common.cnblogs.com/images/copycode.gif

　　（2）关闭唯一性效验可以提高导入效率
　　在导入数据前先执行set unique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。

http://common.cnblogs.com/images/copycode.gif

--当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

http://common.cnblogs.com/images/copycode.gif

　　（3）关闭自动提交可以提高导入效率
　　在导入数据前先执行set autocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。

http://common.cnblogs.com/images/copycode.gif

--当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

http://common.cnblogs.com/images/copycode.gif

• 　　优化insert语句
  

　　尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。
　　可以使用insert delayed（马上执行）语句得到更高的效率。
　　将索引文件和数据文件分别存放不同的磁盘上。
　　可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。

• 　　优化group by语句
  

　　如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order by null来禁止排序：
　　如下没有使用order by null来禁止排序

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

　　如下使用order by null的效果：

http://common.cnblogs.com/images/copycode.gif

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)

http://common.cnblogs.com/images/copycode.gif

• 　　优化嵌套查询
  

　　下面是采用嵌套查询的效果（可以使用更有效的链接查询（Join）替代）。

http://common.cnblogs.com/images/copycode.gif

mysql> explain select * from sales2 where company_id not in(select id　　
from company2)\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)
*************************** 2. row *************************** id: 2 select_type: SIMPLE　　
table: company2
　　
type: index_subquery
　　
possible_keys: ind_company2_id
　　
key: ind_company2_id
　　
key_len: 5 ref: func
　　
rows: 2 Extra: Using index 1 row in set (0.00 sec)

http://common.cnblogs.com/images/copycode.gif

　　下面是使用更有效的链接查询（Join）

http://common.cnblogs.com/images/copycode.gif

mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null\G　　
*************************** 1. row ***************************id: 1 select_type: SIMPLE
　　
table: sales2
　　
type: ALL possible_keys: ind_sales2_companyid_moneys
　　
key: ind_sales2_companyid_moneys
　　
key_len: 5 ref: count rows: 1 Extra: Using where 1 row in set (0.00 sec)
　　
*************************** 2. row *************************** id: 2 select_type: SIMPLE
　　
table: company2
　　
type: index_subquery
　　
possible_keys: ind_company2_id
　　
key: ind_company2_id
　　
key_len: 5 ref: func
　　
rows: 1 Extra:
　　
1 row in set (0.00 sec)

http://common.cnblogs.com/images/copycode.gif

　　从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）子所以更有效率一些，是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
　　数据库优化                                                                                   

• 　　优化表的类型
  

　　在MySQL中，可以使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。

http://common.cnblogs.com/images/copycode.gif

mysql> select * from duck_cust procedure analyse()\G　　
*************************** 1. row *************************** Field_name: sakila.duch_cust.cust_num
　　
Min_value: 1 Max_value: 6 Min_length: 1 Max_length: 1 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 3.5000 Std: 1.7078 Optimal_fieldtype: ENUM(‘1’,‘2’,‘3’,‘4’) NOT NULL *************************** 2. row *************************** ……

http://common.cnblogs.com/images/copycode.gif

• 　　大存储量解决
  

　　1.分库分表
　　2.分区
　　主要目的:
　　1.减少表的记录数
　　2.减小对操作系统的负担压力

• 　　中间表
  

　　中间表的产生:
　　1.view 视图
　　2.重新生成一个新表
　　Mysql服务器优化                                                                        

• 　　myisam读锁定
  

　　1.
lock table t1 read　　2.开启另一个mysql连接终端,接着去尝试:
select * from t1　　3.再insert、update和delete t1这张表，你会发现所有的数据都停留在终端上没有真正的去操作
　　4.读锁定对我们在做备份大量数据时非常有用.
mysqldump -uroot -p123 test >test.sql

• 　　myisam写锁定
  

　　1.
lock table t1 write　　2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上，只有等第一个终端操作完毕,第二个终端才能真正执行.
　　3.可见表的写锁定比读锁定更严格
　　4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的.
　　Mysql服务器优化                                                                        

• 　　二进制日志
  

　　1.log-bin=mysql-bin
　　查看bin-log日志:
mysql> show binary logs;　　查看最后一个bin-log日志:
mysql> show master status;

• 　　慢查询日志
  

　　开户和设置慢查询时间:
vi /etc/my.cnflog_slow_queries=slow.loglong_query_time=5　　慢查询次数:
mysql> show global status like "%quer%"

• 　　socket问题
  

　　mysql socket无法登录
　　1. 有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了.
[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost　　
Welcome to the MySQL monitor. Commands end with ; or \g.
　　
Your MySQL connection id is 34Server version: 5.0.77-log Source distribution
　　
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
　　
mysql>
　　这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的

• 　　root密码丢失
  

http://common.cnblogs.com/images/copycode.gif

root密码丢失破解1.service mysqld stop2. mysqld_safe --skip-grant-tables --user=mysql &　　
//跳过授权表mysql.user和mysql.db这些表3. mysql -uroot4. set password=password("wei");
　　
//用这一条语句结果报错,就是因为加了--skip-grant-tables4. mysql>update user set password=password("wei") where user='root' and host='localhost';5. mysql> set password for root@localhost=password("wei");6. mysql> set password=password("wei");
　　
//和第五步一样,都可能成功修改密码