有关mysql的优化

做事情要逻辑要清楚：

1.首先为什么要优化：

2.如何优化：

步骤一、发现问题

1.借助工具：mysqldumpslow【mysql自带的工具】

看下面的图片：

slow.log是mysql根据sql语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。

mysqldumpslow 可以接受的参数有：

命令：mysqldumpslow -s c -t 10

用法：修改my.cnf文件配置：

如果要修改默认值则long_query_time=5设定为5s

如果要记录所有的sql语句，log-long-format:

2.对找到的语句进行索引分析：explain【为什么只分析索引，因为对于速度来说，索引是最重要的】

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
用法，在select语句前加上explain就可以了：

如：explainselect surname,first_name form a,b  where  a.id=b.id
分析结果形式如下：

mysql> explain SELECT * FROM  whisper  WHERE to_id = 6696 AND del = 0  AND whisper=0 ORDER BY `send_time` DESCLIMIT 4;

select_type  SELECT 的类型，可能会有以下几种：  

SIMPLE - 简单的  SELECT （没有使用  UNION 或子查询 PRIMARY - 最外层的  SELECT UNION - 第二层，在 SELECT 之后使用了  UNION DEPENDENT UNION -  UNION 语句中的第二个  SELECT，依赖于外部子查 SUBQUERY - 子查询中的第一个  SELECT DEPENDENT SUBQUERY - 子查询中的第一个  SUBQUERY 依赖于外部的子查 DERIVED - 派生表  SELECT（ FROM 子句中的子查询）

请看这里的详细说明：http://www.wzzjla.com/Html/201211/648.html

步骤二、解决问题：

下面总结总结下mysql查询优化的一般技巧：这只是针对查询

方案一、建索引：建立索引目的：避免全表扫描，提高速率

建立索引的原则：

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
	A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
	B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
	C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
	D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
	E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大

推荐一篇写的很好的文章：索引的认识【深入】

http://blog.chinaunix.net/uid-20377663-id-3016500.html

这里既是困惑很久的问题，今天总是得以明白：

1、索引分单列索引和组合索引
　　单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
　　组合索引：即一个索包含多个列。

 

2，复合索引
　　比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
　 　如 果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率， 但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀 特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

方案二、使用查询缓存：这个是任何地方都惯用的手法（包括系统架构层面的【使用memcached】,代码层面的【js前端对象】等等）

操作：修改my.ini设置是否查询缓存【mysql4.0.1以上版本才支持】

query_cache_type=1//设置默认使用查询缓存

设置了这个属性后mysql在执行select语句之前，都会在它的缓存区查询是否存在相同的select语句被执行过。如果有并且没有过期，则直接取查询结果返回客户端，【但是注意mysql查询缓存区分大小写，而且不自动去除空格】

1.    SELECT * from TABLE1
3.    SELECT * FROM TABLE1

上面两句是完全不同的select

mysql5.0提供了一种可以临时关闭缓存的方法：

selectsql_no_cache field1,field2 from table1;//这样就不会走缓存，而是直接去查数据库

query_cache_type = 2//默认关闭缓存

select sql_cache * from table1;//临时开启缓存

方案三、mysql自动优化：

索引虽然能提高查询速度，可是有时候索引反而减低性能：比如：

create tale sales(

id int(10) unsigned not null auto_increment,

name varchar(100) not null,

price float not null,

sale_date date not null,

primary key(id),

index(name),

index(sale_date)

);

如果这张表存了百万条数据，我们查询2013到2014年的平均价格

select avg(price)

from sales

where sale_date between '2013-01-01' and '2014-12-31'

如果商品数量非常多，差不多占了sales表记录的一半或更多，那么使用sale_date字段上索引来计算平均数就有些慢，因为如果使用索引，则要对索引进行排序。当满足条件的记录非常多的时候，速度就变慢，还不如全表扫描，所以mysql会自动根据满足条件的数据占比自动决定是否使用索引进行查询

方案四：基于索引的排序：【建立复合索引】

MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。

因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。我们可以看看如下的SQL语句:

1.    SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对SALES表建立复合索引:

1.    ALTER TABLE SALES 

DROP INDEX NAME, 

ADD INDEX (NAME, SALE_DATE)

这样再使用上述的SELECT语句进行查询时速度就会大副提升。

但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。

方案五：使用索引选择来提高性能【也就是hint】：

select 语句除了正常的使用外，mysql还为我们提供了很多可以增强查询性能的选项。如果上面的sql_no_cach和sql_cache，其实还有下面的这些：

1.straight_join:强制连接顺序：当多个表进行查询时，我们不关心先连那个表，后连那个表，这一切由mysql内部通过计算评估决定，

a.    SELECT TABLE1.FIELD1, TABLE2.FIELD2 

FROM TABLE1 ,TABLE2 WHERE …

如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:
b.   SELECT TABLE1.FIELD1, TABLE2.FIELD2 

FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序

2.干预索引：在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引

a.限制使用索引的范围

 SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …

b.限制不使用索引的范围

 SELECT * FROM TABLE1IGNORE INDEX (FIELD1, FIELD2) …

c:强迫使用某一个索引

 SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …//只使用建立在FIELD1上的索引，而不使用其它字段上的索引

3.使用临时表提供查询性能

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，

这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。

SELECTSQL_BUFFER_RESULT* FROM TABLE1 WHERE …

:和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT

这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。

SELECTSQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

方案六.优化sql;

下面是where子句的一些注意点：

a.不要给值可能为null的列建立索引

：如果某列存在空值，即使该列建索引也不会提高性能，而且如果是多列索引的话还把别的列给害了

所以：任何在where字句中使用is null 或is not null的语句优化器是不允许使用索引的

b.最好不要使用联接列'aaaa'||'bbbb'【联接列使得索引失效】

假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)， 查询一个叫比尔.克林顿(Bill Cliton)的职工。

　　下面是一个采用联接查询的SQL语句，

select * from employss where first_name||''||last_name='Beill Cliton';【last_name创建的索引没有使用】

select * from employss where first_name ='Beill'and last_name='Cliton';【last_name创建的索引可以使用】

c. 带通配符(%)最好不要出现在搜索词首。

　　同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

select * from employee where last_name like '%cliton%';【这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引】

在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。

select * from employee where last_name like 'c%';【通配符出现在字符串其他位置时，优化器就能利用索引】

d.order by 

d1.后面的语句中的列一定最好要  建立索引，

d2.后面的语句最好不要有表达式

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式

e.尽量不使用not 

... where not (status ='VALID')

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例:

... where status<>'INVALID';

对这个查询，可以改写为不使用NOT:

select * from employee where salary<3000or salary>3000;

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。

其它：

1.SELECT子句中避免使用 ‘ * ‘： 

2.删除重复记录： 

最高效的删除重复记录方法 ( 因为使用了ROWID)例子： 
DELETE  FROM  EMP E  WHERE  E.ROWID > 

(SELECT MIN(X.ROWID) 
FROM  EMP X  

WHERE  X.EMP_NO = E.EMP_NO); 

3.用TRUNCATE替代DELETE： 

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML) 

4.尽量多使用COMMIT： 

5.使用表的别名(Alias)： 
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,a减少解析的时间并减少那些由Column歧义引起的语法错误. 

6.用索引提高效率： 

原理：ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 

优点：提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 

缺点：但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.： 

ALTER  INDEX <INDEXNAME> REBUILD <TABLESPACENAME> 

7.避免在索引列上使用计算． 
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描． 
举例: 
低效： SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
高效: SELECT … FROM DEPT WHERE SAL > 25000/12; 

以下就是项目中的列子：尽量不使用or,  宁可适用union`