关于sql语句的优化

最近在做mysql的数据库优化以及对sql语句优化的指导，写了一点文档，这个大家共勉一下！

数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定，通过优化应用程序来对数据库系统进行优化能获得更大的收益。

通常可分为两个方面: SQL语句的优化和数据库性能调优。应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。而数据库性能调优是结合硬件，软件，数据量等的一个综合解决方案，这个需要测试人员进行性能测试，和开发人员配合进行性能调优。

SQL语句优化

3.1关键词优化

所有关键词都大写。如：SELECT,FORM,WHERE,AND,CREATE,TABLE等等，例如：使用mysql管理工具导出sql文件，我们可以看到大部分关键词都是大写。如下图：

解释：这是因为，sql的处理底层，默认就将所有的sql语句，进行大写转换。

3.2 sql语句中不能存在*

在所有的查询sql语句中，不能存在*符号。即，SELECT *FORM 。举例我们的部门表的查询。错误写法：SELECT * FROM tdepartment 正确写法：SELECT idepartmentid,scompanycode,sdepartmentname,iparentdepartmentid,sdeptposttype,sifdeleted,sleafnode,sdesc FROM tdepartment。原因：*号会检索全部字段，

用*号效率低，就相当于for循环和foreach一样。用*号，sql语句查询底层会默认去字      

典库里查询公有多少个字段，然后在一个一个的取。如果不使用*，就不是去先查字典库。

3.3 COUNT(*)使用

项目中不能使用COUNT(*)的sql语句。COUNT(*)全部替换成COUNT(1)。这在数据量比较小的情况下，不明显，但是在表中数据较多的情况下，效果非常明显。

3.4多用匹配查询，少用like查询

      原因，like查询会直接放弃索引。

3.5主键索引使用

      所有表的主键全是索引。应尽量使用主键查询。如：SELECT * FROM tusers ORDER BY dregistertime DESC效率低于SELECT * FROM tusers ORDER BY iuserid DESC。这是因为所有的主键都默认是索引。而注册时间不是索引字段。 

3.6第1第2索引排列使用

假设我们的用户表中的scompanycode,dregistertime两个字段都创建了索引。而scompanycode是第一索引。dregistertime是第二索引。那么查询时：SELECT * FROM tusers ORDER BY scompanycode,dregistertime DESC的效率高于SELECT * FROM tusers ORDER BY dregistertime,scompanycode DESC。这是因为第一索引将首先被检索。

3.7建表不要给字段设置默认值

如：`sifaudited` varchar(2) default ‘0‘ COMMENT ‘0:未审核；1:已审核‘。默认值会在插入数据时，增加数据库底层判断是否有值情况，进行赋默认值。

3.8字段不要留null值

这是因为null值占用的数据大小比较大。Null和空一般占4到8个字节。如：`scompanycode` varchar(16) default NULL COMMENT ‘公司编号(唯一识别）‘，对于这样的，我们一般把空值改为0，你们应该懂的。

3.9多用子查询

      子查询性能高于连接查询。子查询性能高于左联接、右连接、全连接查询。

3.10连接查询性能高于循环查询

对于部门查询，我们一般是查询根目录，然后循环查询子部门，一直循环到查询结束。性能较低。我们应该采用，连接查询。或者写函数，存储过程进行查询。

4.设计优化

4.1 日志模块，新增队列，当日志达到100条或者200、500条的时候，我们采用批量插入n条，减少磁盘的io次数。这样可以延长磁盘的寿命，同时对数据的插入也有了明显的提高。

5.数据库引擎使用

5.1   ENGINE = innodb

    Innodb数据库引擎是对外键，事务进行过优化。我们对创建所有的表都使用innodb引擎。这是错误的，应该对每一个表的用途对应一个不同的数据库引擎。

5.2   ENGINE = MyISAM

MyISAM类型不支持事务处理等高级处理。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持。MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。这个类型东海们项目使用的多。最常用的引擎之一。

5.3   ENGINE = BDB

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

5.4   ENGINE = Memory

Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

5.5   ENGINE = Merge

Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

5.6    ENGINE = Archive

Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

5.7    ENGINE = Federated

 Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式 环境或数据集市环境。

5.8    ENGINE =Cluster/NDB

Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

5.9    Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

6.表字段设计

  6.1对于类型限制。是否删除字段，如：`sifdeleted` varchar(2) default ‘0‘ COMMENT ‘0:正常；1:已删除‘,使用int(1)类型标识，不要使用varchar(2)多占用空间。

     6.2 对于字段长度限制，如手机号11位，我们就没有必要设计更多位数。公司编号可以只设定8位。用户名限制32位等等。

     6.3 少用外键限制

         我们可以使用代码限制。如：级联删除，级联新增，修改等等操作。最好不要设计外键，外键对新增数据不利。

     6.4  少用约束，如：唯一约束。

 6.5  少用自动增长

      在圆通主键没有自动增长，而是使用uuid，java自动生成。考虑到我们数据表数据较少，少用。

 6.6  对于内容较少的表，没有必要创建索引。因为索引浪费空间。

 6.7  表分区使用

      对于日志表，我们可以使用表分区。表分区之后，对于查询效率有很高的提升。默认有时间分区，大小分区，类型分区等等。

 6.8  对表的内容进行限制，如：日志表可以限制条数。再创建表时。我们使用MAX_ROWS进行限制。

7.其他请遵守建表规则

   如：三范式等。

如有疑问，请加qq群：135430763 共同学习！