人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充分的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显的提高!下面我将从这三个方面分别进行总结。

　　为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过1秒的均表示为(1秒)。测试环境

　　主机:HP LH II

　　主频:330MHz

　　内存:128MB

　　操作系统:Oper server 5.0.4

　　数据库:Sybase 11.0.3

　　一、不合理的索引设计

　　例:表record有620000行，试看在不同的索引下，下面几个SQL的运行情况:

　　1.在date上建有一个非群集索引

　　select count(*) from record where date'19991201' and date'19991214'

　　and amount2000 --(25秒)

　　select date, sum(amount) from record group by date --(55秒)

　　select count(*) from record where date'19990901' and place in

　　('BJ','SH') --(27秒)

　　分析:date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。

　　2.在date上的一个群集索引

　　select count(*) from record where date'19991201' and date'19991214'

　　and amount2000 --(14秒)

　　select date,sum(amount) from record group by date --(28秒)

　　select count(*) from record where date'19990901' and place in

　　('BJ','SH') --(14秒)

　　分析:在群集索引下，数据在物理上按顺序排在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。

　　3.在place、date、amount上的组合索引

　　select count(*) from record where date'19991201' and date'19991214'

　　and amount2000 --(26秒)

　　select date,sum(amount) from record group by date --(27秒)

　　select count(*) from record where date'19990901' and place in

　　('BJ’,'SH') --(1秒)

　　分析:这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引;第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。

　　4.在date、place、amount上的组合索引

　　select count(*) from record where date'19991201' and date'19991214'

　　and amount2000 --(1秒)

　　select date, sum(amount) from record group by date --(11秒)

　　select count(*) from record where date'19990901' and place in

　　('BJ','SH') --(1秒)

　　分析:这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。

　　5.总结

　　缺省情况下建立的索引是非群集索引，但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。一般来说:

　　有大量重复值且经常有范围查询(between, , ，=, =)和orderby、groupby发生的列，可考虑建立群集索引。经常同时存取多列，且每列都含有重复值可考虑建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
服务启动后/data/app_1/下相应的文件和目录分布如下:

　　/data/app_1/

　　start_mysql.sh 服务启动脚本

　　stop_mysql.sh 服务停止脚本

　　mysql.pid 服务的进程ID

　　mysql.sock 服务的SOCK

　　var/ 数据区

　　mysql/ 用户库

　　app_1_db_1/ 应用库

　　app_2_db_2/

　　...

　　/data/app_2/

　　...

　　查看所有的应用进程ID:

　　cat /data/*/mysql.pid

　　查看所有数据库的错误日志:

　　cat /data/*/var/*.err

　　个人建议:MYSQL的主要瓶颈在PORT的连接数上，因此，将表结构优化好以后，相应单个MYSQL服务的CPU占用仍然在10%以上，就要考虑将服务拆分到多个PORT上运行了。

　　服务的备份

　　==========

　　尽量使用MYSQL DUMP而不是直接备份数据文件，以下是一个按weekday将数据轮循备份的脚本:备份的间隔和周期可以根据备份的需求确定

　　/home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql db_name | gzip -f>/path/to/backup/db_name.`data +%w`.dump.gz

　　因此写在CRONTAB中一般是:

　　* 6 * * * /home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql db_name | gzip -f>/path/to/backup/db_name.`data +\%w`.dump.gz

　　注意:

　　1 在crontab中'%'需要转义成'\%'

　　2 根据日志统计，应用负载最低的时候一般是在早上6点

　　先备份在本地然后传到远程的备份服务器上，或者直接建立一个数据库备份帐号，直接在远程的服务器上备份，远程备份只需要将以上脚本中的-S /path/to/msyql.sock改成-h IP.ADDRESS即可。

　　数据的恢复和系统的升级

　　======================

　　日常维护和数据迁移:在数据盘没有被破坏的情况下

　　硬盘一般是系统中寿命最低的硬件。而系统(包括操作系统和MYSQL应用)的升级和硬件升级，都会遇到数据迁移的问题。

[1] [2] 下页