mysql实战优化之一：sql优化

1、选取最适用的字段属性

　　MySQL 可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为char(255),显然给数据库增加了不必要的空间，甚至使用VARchar这种类型也是多余的，因为char(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为enum类型。因为在MySQL中，enum类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

属于表结构优化，详细见《分析诊断工具之五：Procedure Analyse优化表结构》

 

2、优化你的MySQL查询缓存

　　在MySQL服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。

　　但主要的问题是，它是那么容易被隐藏起来以至于我们大多数程序员会忽略它。在有些处理任务中，我们实际上是可以阻止查询缓存工作的。

// query cache does NOT work     
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

3、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL 从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下所示：

delete from customerinfo 
WHERE Customerid NOT in (SELECT Customerid from salesinfo )

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * from customerinfo
WHERE Customerid NOT in (SELECT Customerid from salesinfo )

如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对Customerid建有索引的话，性能将会更好，查询如下：

SELECT * from customerinfo 
LEFT JOIN salesinfoON customerinfo.Customerid=salesinfo. 
Customerid 
WHERE salesinfo.Customerid IS NULL

连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4、使用联合(UNION)来代替手动创建的临时表
MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

SELECT Name, Phone from client UNION SELECT Name, Birthdate from author UNION SELECT Name, Supplier from product

5、用EXPLAIN使你的SELECT查询更加清晰

 

6、利用LIMIT 1取得唯一行（提前终止）

　　有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。

　　在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。

// do I have any users from Alabama?
// what NOT to do:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
// ... 
}
// much better:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}

7、不要使用BY RAND()命令

　　这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND()命令时就开始创建了。

　　如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令(这会消耗处理器的处理能力)，然后给你仅仅返回一行。

// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better:
$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");

8、尽量避免SELECT *命令

　　从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。

　　始终指定你需要的列，这是一个非常良好的习惯。

// not preferred
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// better:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// the differences are more significant with bigger result sets

9、从PROCEDURE ANALYSE()中获得建议

　　PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

10、预处理语句 准备好的语句

　　准备好的语句，可以从性能优化和安全两方面对大家有所帮助。

　　准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

// create a prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// bind parameters
$stmt->bind_param("s", $state);
// execute
$stmt->execute();
// bind result variables
$stmt->bind_result($username);
// fetch value
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}

11、将IP地址存储为无符号整型

　　许多程序员在创建一个VARCHAR(15)时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。

　　你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

介绍两个函数：

inet_aton()是一个改进的方法来将一个字符串IP地址转换为一个32位的网络序列IP地址。

 

 INET_NTOA()将一个IP转换成一个互联网标准点分格式的字符串。

12、优化insert语句
尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。

可以使用insert delayed（马上执行）语句得到更高的效率。

将索引文件和数据文件分别存放不同的磁盘上。

可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。

13、优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order by null来禁止排序：

如下没有使用order by null来禁止排序

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)

如下使用order by null的效果：

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)

14、优化表的类型

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

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

15、拆分大的 DELETE 或 INSERT 语句 

         如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。 
       Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。 
       如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你泊WEB服务Crash，还可能会让你的整台服务器马上掛了。 

所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：

while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
break;
}
// 每次都要休息一会儿
usleep(50000);
}

 

15、Mysql服务器优化

二进制日志

1.log-bin=mysql-bin

查看bin-log日志:

mysql> show binary logs;
查看最后一个bin-log日志:

mysql> show master status;
慢查询日志
开户和设置慢查询时间:

vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:

mysql> show global status like "%quer%"

 

大存储量解决
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会自动进行锁定的.