MYSQL的深入学习--优化步骤

MySql优化的一般步骤

1.通过show status 命令了解各种sql的执行效率

　　SHOW STATUS提供msyql服务器的状态信息

　　一般情况下，我们只需要了解以”Com”开头的指令

　　show session status like 'Com%'：显示当前的连接的统计结果

　　show global status like 'Com%' ：显示自数据库上次启动至今的统计结果

　　注：默认是session级别的

　　其中Com_XXX表示XXX语句所执行的次数。

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

　　另外，还有几个参数需要注意下：

　　show status like 'Connections';// 试图连接MySQL服务器的次数

　　show status like 'Uptime';//服务器工作的时间（单位秒）

　　show status like 'Slow_queries';//慢查询的次数 (默认是10秒中就当做是慢查询，如下图所示)

　　a) 如何查询mysql的慢查询时间

　　　　Show variables like 'long_query_time';

　　b) 修改mysql 慢查询时间

　　　　set long_query_time=2;//如果查询时间超过2秒就算作是慢查询

2. 定位执行效率较低的SQL语句（dql出现问题的概率较dml的大）

　　问题是：如何在一个项目中，找到慢查询的select语句？

　　答案：mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件，默认情况下，慢查询记录是不开启的。

　　开启慢查询记录的步骤：

　　打开 my.ini ,找到 [mysqld] 在其下面添加

　　long_query_time = 2

　　log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里，可以为空，系统会给一个缺省的文件

例子：我们数据表中有1千万条的数据量

　　DQL语句：SELECT * FROM order_copy WHERE id=12345;

　　

　　查询耗时：19s>2s，所以mysql会将该条select语句记录到慢查询日志中

　　SELECT * FROM order_copy WHERE id=12345的执行时间：

　　添加索引前：19s

　　添加索引后：0.08s

3.通过explain分析低效率的SQL语句的执行情况

　　使用explain分析该dql语句：

EXPLAIN SELECT * FROM order_copy WHERE id=12345
会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

注意：要尽量避免让type的结果为all，extra的结果为：using filesort

MySQL explain功能展示的各种信息的详细解释如下：

id： MySQL Query Optimizer 选定的执行计划中查询的序列号。

select_type： 所使用的查询类型，主要有以下这几种查询类型。

• • DEPENDENT SUBQUERY：子查询内层的第一个select，依赖于外部查询的结果集。
  • DEPENDENT UNION：子查询中的union，且为union中从第二个select开始的后面所有的select，同样依赖于外部查询的结果集。
  • PRIMARY：子查询中的最外层查询，注意并不是主键查询。
  • SIMPLE：除子查询或者union之外的其他查询。
  • SUBQUERY：子查询内层查询的第一个select，结果不依赖于外部查询的结果集。
  • UNCACHETABLE SUBQUERY：结果集无法缓存的子查询。
  • UNION：union语句中第二个select开始后面的所有select，第一个select为primary。
  • UNION RESULT：union中的合并结果。

table：显示这一步所访问的数据库中的表的名称。

type：告诉我们队表使用的访问方式，主要包含如下几种类型。

• • all：全表扫描。
  • const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
  • eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
  • fulltext：进行全文索引检索。
  • index：全索引扫描。
  • index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据。
  • index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
  • rang：索引范围扫描。
  • ref：join语句中呗驱动表索引引用的查询。
  • ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
  • system：系统表，表中只有一行数据。
  • unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束。

possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就是显示成null，这项内容对优化索引时的调整非常重要。

key： MySQL Query Optimizer 从possible_keys中选择使用的索引。

key_len：被选中使用索引的索引键长度。

ref：列出是通过常量（const），还是某个表的某个字段（如果是jion）来过了（通过key）的。

rows：MySQL Query Optimizer 通过系统收集的统计信息估算出来的结果集记录条数。

extra：查询中每一步实现的额外细节信息，主要会有以下内容

distinct：查找distinct值，当mysql找到第一条匹配的结果时，将停止该值的查询，转为后面其他值的查询。

full scan on null key： 子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。

impossible where noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。

no tables：query语句中使用from dual 或不包含任何from子句。

not exists：在某些左连接中，MySQL Query Optimizer通过改变原有的Query的组成而使用的优化方法，可以部分减少数据访问次数。

range checked for each record (index map:N)：通过MySql官方手册的描述，当MySQL Query Optimizer没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySql检查是否可以使用 range 或index_merge访问方法来索引行。

select tables optimized away：当我们使用某些聚合函数来访问存在的索引的某个字段时，MySQL Query Optimizer会通过索引直接一次定位到所需的数据行完成整个查询，当然，前提是在Query中不能有group by操作。

using filesort：当Query中包含order by操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer不得不选择相应的排序算法来实现。

using index：所需数据只需在index即可全部获得，不需要在到表中取数据。

using index for group-by：数据访问和using index一样，所需数据只须要读取索引，当Query中使用group by 或 distinct子句时，如果分组字段也在索引中，信息就会显示这个。

using temporary：当MySQL在某些操作中必须使用临时表时，在extra信息中就会出现这个。主要常见于group by 和order by等操作中。

using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有须要的数据，则会出现using where信息。

using where with pushed condition：这是一个仅仅在NDBCluster存储引擎中才会出现的信息，而且还需要通过打开condition pushdown优化功能才可能被使用。控制参数为 engine_condition_pushdown

4.确定问题并采取相应的优化措施

常用的优化措施是添加索引。添加索引，我们不用加内存，不用改程序，不用调sql，只要执行个正确的'create index'，查询速度就可能提高百倍千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。
例如：给字段id添加索引：

ALTER TABLE order_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时： 428秒（7分钟）

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因为给id添加了索引，才使得rows的结果为1

但是索引并不是可以随便添加的，以下几种情况需牢记在心：

较频繁的作为查询条件字段应该创建索引
　　 select * from order_copy where id = $id

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
　　 select * from order_copy where sex='女'

更新非常频繁的字段不适合创建索引
　　 select * from order_copy where order_state='未付款'

不会出现在WHERE子句中字段不该创建索引
索引的类型：

PRIMARY 索引     =>    在主键上自动创建
INDEX 索引        =>    就是普通索引
UNIQUE 索引      =>    相当于INDEX + Unique
FULLTEXT         =>    只在MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多， 在全英文网站用多(英文词独立). 中文数据不常用，意义不大 国内全文索引通常 使用 sphinx 来完成.
索引的使用

建立索引

 

create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
alter table table_name ADD INDEX [index_name] (index_col_name,...)

添加主键(索引)

ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); //主键可以是联合主键

删除索引

DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;

删除主键(索引)比较特别:

alter table t_b drop primary key;

查询索引(均可)

show index from table_name;
show keys from table_name;
desc table_Name;

 

原文出处：http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html