自己的MySQL数据库优化及逻辑优化技巧总结

之前学习了MySQL数据库优化,刚好项目中用到了,而且交了篇文档,和大家分享下。之前学的大型项目缓存系统memcached没用上,最近学习java安全。

一、MySQL优化

1、索引的使用

数据库索引的建立对于查询是十分快速的,MySQL基于B+数的索引查询,500000条记录只需要4次查询就能定位到改行。所以建立合适的索引是十分必要的,并且可以通过系统的记录查看索引建立的优劣。
可以通过命令show GLOBAL status like ‘Com%’;查看结果(每秒操作次数)

数据库索引优化

 

可以看出查询是很多,修改很少,索引建立的合适

2、优化SQL查询语句

2.1慢查询

由于查询很多,所以要提高查询速度,用户的习惯是2~3秒希望看到结果,所以我们将慢查询时间设为1s,并且将所有慢查询的语句及详细信息记录下来,以供分析和优化。
设置如下,在/etc/mysql/my.cnf文件中设置
log-slow-queries=/var/lib/mysql/slowquery.log
long_query_time =1
查看MySQL记录的日志如下:

mysqld, Version: 5.0.51a-3ubuntu5.8-log ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 120228 11:15:01
# User@Host: root[root] @ [10.11.18.38]
# Query_time: 43 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use tvmarket;
SELECT A.App_ID ,A.LogoUrl ,A.UploadTime,H.ScreenShotUrl FROM T_App A ,T_App_Info B , T_Dev F , T_App_Screen_Shot H , T_App_Type_PK D, T_App_Type C where A.UploadTime > “20120221160603″ order by A.UploadTime desc;
# Time: 120228 11:24:59
# User@Host: root[root] @ [10.11.18.38]
# Query_time: 203 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT A.App_ID ,A.LogoUrl ,A.UploadTime,B.AppName,C.TypeName,H.ScreenShotUrl,F.Score FROM T_App A , T_App_Info B , T_Dev F,T_App_Screen_Shot H , T_App_Type_PK D, T_App_Type C where A.UploadTime > “20120221160603″ order by A.UploadTime desc;

可以看出这两条查询十分缓慢,因此可以锁定该语句进行更改及优化。

2.2部分SQL优化

2.2.1去文件排序

可以通过Explain+SQL语句以及Extend+SQL查看SQL语句执行的表连接顺序、连接方式、是否用到索引、是否需要全表扫描以及是否有中间临时表和文件排序信息。
文件排序是最慢的,文件排序一定要去除,而且尽量去除全表扫描,分析SQL语句
SQL目的:查询根据开发者的ID查询该开发者的所有状态的应用数目
EXPLAIN SELECT COUNT(APP_ID) AS NUM,State FROM T_App WHERE DEV_ID = 1484 GROUP BY State
得到结果:

去文件排序

以上SQL语句用到了文件排序,优化后的SQL语句

SELECT COUNT(APP_ID) AS NUM,State FROM T_App WHERE DEV_ID = 1484 GROUP BY State ORDER BY NULL;

分析结果,去掉了

部分SQL优化:去文件排序

同理,只要是用到了SQL聚合函数而且要进行分组的,MySQL默认要进行文件排序,只要加ORDER BY NULL就可以不排序了,但有些是根据需要进行排序的就不用加了,比如对分组结果进行时间排序。

2.2.2去全表扫描

有些全表扫描是必须的,但是只需要查询一条结果满足条件,通过添加LIMIT 1即可消除全表扫描。

2.2.3尽量去除SELECT *

二、逻辑优化

判断某一个用户注册时的邮箱是否已经使用过了。错误的逻辑:将邮箱和查询所有注册过的邮箱比较。正确的逻辑:根据邮箱去数据库表中查询是否存在返回结果。

插入一条记录判断是否插入成功,错误的逻辑:再去查询这条记录判断返回结果是否为空。正确的逻辑:返回插入的记录的结果是真是假即可。

后记:这只是优化的一部分,比较简单和常见的手法,更多内容参考:MySQL 5.1参考手册-优化

发表评论

邮箱地址不会被公开。 必填项已用*标注