MySql优化指南_MySQL教程

模板酷站 2012年10月22日

推荐：MySQL为什么会索引失效？索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 2.对于多列索引，不是使用的第一部分，则不会使

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

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子句中字段不该创建索引

索引的类型：

索引的使用

建立索引 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(列名,..); 联合主键

分享：MySql大批量导入数据优化对于MyISAM： a.alter table table_name disable keys b.loading data c.alter table table_name enable keys 对于Innodb： a.将要导入的数据按照主键排序 b.set unique_checks=0,关闭唯一性校验 c.set autocommit=0,关闭自动提交

查看原文