Mysql应用优化整理教程一

Mysql应用优化整理教程一

时间：2010-7-25

一、MYSQL性能优化经验

1. 为查询缓存优化查询

示例：

//查询缓存不起作用

SELECT USERNMAE FROM USER WHERE DATES >= CURDATE()

//开启查询缓存

SELECT USERNAME FROM USER WHERE DATES > ‘2010-07-09’

说明：SQL语句的条件不能是变化的。如果是变化的话MYSQL的查询缓存就不起作用了。

2. 只有一条记录的时候记得加上limit 1

// SELECT 1 FROM USER WHERE COUNTRY=’CHINA’ LIMIT 1

3. 为搜索字段建立索引

// SELECT USERNAME FROM USER WHERE COUNTRY LIKE  ‘a%’

最左前缀是能够用到索引的。

4. 对于有JOIN表的时候要使用相同的类型并将其置为索引

5. 千万不要这样写order by rand()性能是最差的！

//千万不要这样做

$r = mysql_query(“select username from user order by rand() limit 1”)

//这样效率更高

$r = mysql_query(“select count(*) from user”)

$d = mysql_fetch_row($r);

$rand = mt_rand(0,$d[0]-1)

$r = mysql_query(“select username from user limit $rand,1”)

6. 避免使用SELECT *

注意如果你这样写的话要是数据很多的话会让查询变慢而且WEB服务器与数据库服务器之间的网络传输将成为你的一个新的负载。

7. 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

8. 使用ENUM而不是VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

9. 尽可能的使用NOT NULL

10． 使用prepared statements语句

这样可以保护你的程序不会受到“SQL注入式”攻击。在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

11. 固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”

表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了

结论就是尽可能不要使用这种变长的字段类型像varchar text blob这样的字段

12. 拆分大的DELETE或INSERT语句

如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。

While(1) {

    Mysql_query(“delete from logs where log_date<=’2009-11-01’  limit 1000”);

    If(mysql_affected_rows() ==0) {

        Break;

}

//每次都要休息一会

Usleep(50000);

}

 

13. 尽可能使你的列越小越好

这样的好处就是减少对硬盘的访问。比如你如果能用smallint的就不要用int了。如果不需要记录时间的话就使用DATE了。这样的好处就是有好的扩展空间了。像我们现在搞的这个修改表语句

Alter table真的要花好多时间的。

 

 

二、group by 排序问题

写的顺序：select ... from... where.... group by... having... order by..
执行顺序：from... where...group by... having.... select ... order by...

解决方案：select * from (select * from `test` order by `date` desc) `temp` group by category_id order by `date` desc

使用子查询！

 

三、主键要选择最佳数据类型

Integer类型是主键的最佳选择。

ENUM及SET   作为主键是不太提倡的，比较适合存储一些像人的性别什么的字段。

STRING类型   尽可能避免使用呀/！它会浪费很多的空间的。如果你是使用MYISAM类型的话更慢。

UUID        要去掉破折号或使用UNHEX()把UUID转为16字节的数并且存在binary(16)的列。可使用HEX来获取16进制的值。

创建表的SQL语句

CREATE TABLE `e` (

  `id` binary(16) default NULL,

  `a` char(12) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

以这个类型保存当主键吧！

insert into e(id,a) values(UNHEX('1233455432234554343'),'a');

查询的语句

select HEX(id) from e;

 

四、MYSQL查询区分大小写

默认是不区分大小写的。要区分的话可以这样做

select * from students where binary name like 'a%'
select * from students where binary name like 'A%'

五、通过使用for index了解MYSQL查询的性能优化

1. MYSQL默认是会把相同的两个SQL语句执行的结果进行一次缓冲的。如果你不想从缓冲区中提取结果集的话可以这样写SQL语句

SELECT SQL_NO_CACHE field1,field2 FROM TABLE1

关键字SQL_NO_CACHE 就是告诉MYSQL服务器不要从缓冲区中提取数据。

2. MYSQL自动优化索引

当我们的查询结果符合条件的数量占据总数量的30%左右的时候MYSQL就会考虑是否要用到索引了。它会按照自己的算法可能会把索引去掉。

3．基于索引的排序

MYSQL在查询的时候最多只能是使用一个索引。如果WEHRE中已占据了索引了那排序的时候就用不到索引了。

解决办法是：做一个组合索引

4. 使用各种查询选择来提高性能

我们可以使用一些关键字进行干预索引使用可以提高性能。示例

SELECT * FROM TABLE1 USE INDEX（FIELD1，FIELD2.。。。。。）

这样的话就会考虑你所指定的索引值了。

当然如果我们的索引很多，而不被使用的索引又很少的时候我们可以使用反向选择

IGNORE INDEX的方式进行反向提取索引！

 

强迫使用一个索引

使用FORCE INDEX来完成。

示例：select * from e force index(a);

其中的a表示的是索引的名称而不是字段名称！

 

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

当我们查询的结果集里面的数据比较多的时候，我们可以使用SQL_BUFFER_RESULT

这样的话我们就可以将结果集放在一个临时表中去。这样的话就可以马上释放表锁（一旦释放掉了表锁就可以马上进行其他的SQL语句对这些记录的查询了）。

select sql_buffer_result a from e where 1=1;

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

 

结论：在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执 行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。

 

 

六、MSYQL优化WHERE语句

1、切记不要在频繁更新的表上面做复杂的SQL查询，这样很容易发生读、写冲突问题。

2、对于MYISAM的类型的表避免做DELETE操作的！

3、当不知道何时写入数据的时候可以使用INSERT DELAYED。这样可以加快处理，即批处理的方式可以尽可能快地完成写操作！

4、当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。

5、加快插入的一些方法

1. 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。

解释：因为客户端把命令交给了服务器至于什么时候执行那是服务器的事情了。服务器可以考虑等全部的客户端的信息都回来了之后一次性INSERT的。所以我们可以在客户端 的SQL语句上面加上这么一个条件

2. 锁定表能够加速用多个语句执行的INSERT操作

3. 对于事务表可以使用BEGIN和COMMIT代替LOCK TABLES的

 

七、教你编写高性能的MYSQL语法

1. IS NULL与IS NOT NULL

不能用NULL作索引，任何包含了NULL的列都不会被包含在索引中。

2．前缀索引

处理有LIKE的情况的时候会考虑前缀索引。

3. ORDER BY

绝对避免在ORDER BY 子句中使用表达式。

4. NOT

5. IN和EXISTS

第一种格式是使用IN操作符：
... where column in(select * from ... where ...);  
    第二种格式是使用EXIST操作符：
... where exists (select 'X' from ...where ...);  

我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。这个要注意场合了。

 

八、MYSQL获取前一天的时间

date_sub(CURDATE(),interval 1 day)