摘要
最近在写一个微信的项目，把数据库优化重新学习了一遍，特别是索引，受益良多
本文为数据库优化系列文章的第一篇文章 :《数据库优化》
MySQL数据库优化常用技巧
以下内容均来自《深入浅出MySQL数据库开发、优化与管理维护》，这本书很值得读，对于数据库的基础知识和优化有很高的实战借鉴价值
1. 优化SQL语句的一般步骤
1. 通过show status命令了解SQL的执行频率
mysql> show status like 'Com_%'; Com_xxx表示的是每个xxx语句执行的次数，我们通常比较关心如下几个统计参数：
- Com_select: 执行select操作的次数，每次执行累加1
- 同理有Com_insert, Com_update, Com_delete
对于事务型的应用，通过Com_commit, Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，一下几个参数便于用户了解数据库的基本情况：
- Connections: 试图连接MySQL服务器的次数
- Uptime: 服务器工作时间
- Slow_queries: 慢查询的次数
2. 定位执行效率较低的SQL语句
- 通过慢查询定位效率低下的SQL语句，用
--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。 - 使用
show processlist;查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，对一些锁表操作进行优化。
3. 通过EXPLAIN分析低效SQL的执行计划，采用索引进行优化
2. 索引问题
1. 索引的存储分类
- MyISAM和InnoDB只支持BTREE索引，MEMORY/HEAP支持HASH和BTREE
- MySQL不支持函数所有，但是可以对头部分进行所以，比如对name字段的前四个字符进行索引，这么做可以大大减小索引文件的大小
mysql> create index ind_company2_name on company2(name(4));2. MySQL如何使用索引
- 对于复合索引，只要用了最左边的列，那么就会用到索引，如果用了右边的列，则复合索引失效。
- 使用like的查询，只有%不在第一个字符，索引才可能被用到
- 对于大的文本进行搜索，使用全文索引而不要用
like '%...%' - 如果列类型是字符串，那么一定要把字符常量值用引号括起来，否则即使有索引，MySQL也不会用到！
//错误
select * from company2 where name = 294;
//正确
select * from company2 where name = '294';3. 查看索引使用情况
mysql> show status like 'Handler_read%';- 如果索引正在工作，Handler_read_key的值将很高，当很低时表示索引对性能的改善不高。
- Handler_read_rnd_next的值高说明查询运行低效，并且应该建立索引补救。
3. 两个简单实用的优化方法
1. 定期分析表和检查表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。
检查表的语法如下
CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}检查表的作用是检查一个或多个表是否有错误
2. 定期优化表
优化表的语法如下：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...如果已经删除了表的一大部分，或者对含有可变长度行的表（含有VARCHAR, BLOB 或TEXT列的表）进行了很多修改，则需要使用OPTIMIZE来对表中的空间碎片进行合并。
！注意：OPTIMIZE TABLE 命令支队MyISAM，BDB，InnoDB有效。
！注意：ANALYZE, CHECK, OPTIMIZE执行期间将对表进行锁定，因此应当在空闲时段进行。
4. 常用SQL优化
参见我的另一篇文章《数据库优化——常用SQL优化 》:
5. 优化数据库对象
1. 优化表的数据类型
通常在数据库设计时，需要为字段提供一定的冗余，但是在实施阶段，对于特定的系统来说，设计的冗余可能是过大的，浪费了很多空间，此时可以通过PROCEDURE ANALYSE() 操作，获取优化建议，然后根据实际情况酌情考虑是否实施优化
SELECT * FROM tbl_name PROCEDURE ANALYSE();比如，我对我的数据库user表进行分析
其中给出了每个字段的最小长度，平均长度等信息，此时，我就可以将该字段的VARCHAR长度改为合适的长度，减小冗余值，以实现空间缩减。
ALTER TABLE user MODIFY headImgUrl VARCHAR(130);注意！ 使用ALTER改变表结构只能对后来的新数据产生影响，不会改变旧数据，如果想要改变旧数据的存储格式，必须dump出去后再导入进来。
注意！ 小心字符集之间的兼容问题（比如GBK=>UTF8就会导致乱码）
2. 通过拆分提高表的访问效率
- 垂直拆分
这种拆分方式是最常用的拆分方式，在第三范式中就经常使用到。
优点在于查询时会减少IO次数。
缺点在于需要经常使用JOIN来进行查询
- 水平拆分
对于表中存在着基于时间、类别等分类条件时，可以对表进行水平拆分。
比如可以拆分成一个一月份报表和二月份报表，这样可以缩减单个表中的数据容量，减小索引的层数，加快数据读取效率和索引的修改效率。
3. 逆规范化
规范化程度越高 => 产生的关系越多 => JOIN操作增加 => 效率降低
因此，我们考虑舍去某些规范，减少JOIN操作数目、降低FK和索引的数目。
在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算（比如合计）、数据的物理存储位置等。
常用的反规范技术有：
增加冗余列 ：在多个表中具有相同的列，它常用来在查询时避免连接操作
增加派生列 ： 指增加的列来自其他表中的数据，由其他表经过计算生成
重新组表 ： 如果许多用户需要查看两个表连接出来的数据，则把这两个表重新组成一个新表来减少JOIN
分割表 ： 参见上一节
维护数据完整性：
批处理维护 ： 对复制列或者派生列的修改累计到一定的时间后，运行一批处理作业或者存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。
应用逻辑 ： 数据的完整性可有应用逻辑来实现，这就要求必须在同一事务中对所有关联表都进行相应的增删改查操作
触发器 ： 对数据的任何修改都会出发对关联列的相应修改。容易维护， 但是触发器本身的缺陷需要避免。
4. 使用中间表提高统计查询速度
就是用一个临时表，然后在需要统计的列上添加索引、辅助字段啥的，然后也不会对原表产生影响，大大提高效率
6. 锁问题
参见我的另一篇文章《数据库优化——MySQL锁问题 》:
7. 优化MySQL Server
参见我的另一篇文章《数据库优化——优化MySQL Server 》:
8. 磁盘IO问题
磁盘作为所有现代系统中的性能瓶颈，IO的问题是无法避免的，上文所说的所有优化只是尽量的减少和延缓磁盘的读写来减轻磁盘IO的压力，对于磁盘本身的吞吐量的优化，将在本章中介绍
1. 使用磁盘阵列
RAID（廉价磁盘冗余阵列），RAID按照一定的策略将数据分布到弱冠的物理磁盘上，不仅更安全，而且还通过分布式实现了高速”并行”读写
| RAID级别 | 特性 | 优点 | 缺点 |
|---|---|---|---|
| RAID0 | 按一定的chunk size将数据依次分布到每个磁盘，没有数据冗余 | 数据并发读写速度快 | 数据无冗余，可靠性差 |
| RAID1 | 两个磁盘一组，所有数据都同时写入两个磁盘，读取时从任一磁盘都可以 | 数据有完全冗余保护，有较高并发读性能 | 容量一定的话，需要2倍的磁盘，费用高 |
| RAID10 | 是RAID1+0。先对磁盘做镜像，再条带化，使其兼具两者优点 | 可靠性高，并发读写性能高 | 容量一定的话，需要2倍磁盘 |
| RAID4 | 与RAID0一样，使用条带化，但是需要额外增加一个磁盘用于校验纠正错误 | 具有一定的容错保护能力，读数据快 | 由于数据需要进行校验分析，写数据性能差 |
| RAID5 | 改进RAID4，将校验信息也分布到每个盘 | 基本同RAID4，但是写性能和数据保护能力稍强 | 写不如RAIND0,1,10；容错不如RAID1;出现坏盘时读性能下降 |
如何选择RAID级别
读写频繁，可靠性要求高 => 选择RAID10
读频繁，写相对少，可靠性要求一般 => RAID5
读写频繁，可靠性要求不高 => RAID0在Linux下可以使用虚拟文件卷来实现软RAID，例如Linux逻辑卷系统lvm2，支持条带化；Linux下multiple device驱动，支持RAID0,1,4,5,6等
2. 使用Symbolic Links分布IO
一般情况下，数据库的数据块都放在一个磁盘下，我们可以利用操作系统的符号链接功能，将表数据和表结构指向不同的物理磁盘，从而达到分布磁盘IO的目的
//(1) 将一个数据库指向其他物理磁盘
shell> mkdir /otherdisk/databases/test
shell> ln -s /otherdisk/databases/test /path/to/datadir
//(2) 将MyISAM（其他存储引擎不支持）表的数据文件或索引文件指向其他物理磁盘
//对于新建的表可以通过添加DATA DIRECTORY 和INDEX DIRECTORY来完成
CREATE table test(id int primary key, Name varchar(30))
Type = myisam
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk3/index'
//对于已有的表，可以先将数据文件转移到目标磁盘，然后再建立符号链接即可。
//需要说明的是，表定义文件（fpm）必须位于MySQL数据文件目录下，不能用符号链接
//(3)Windows下使用符号链接
//加入MySQL的数据文件目录是C:\mysql\data，要把数据库foo放到D:\data\foo，可以这么做
-> 创建目录D:\data\foo
-> 创建文件C:\mysql\data\foo.sym， 在其中输入D:\data\foo
3. 禁止操作系统更新文件的atime属性
每当读取数据时，Linux都会将读操作的发生时间回写到磁盘上，造成磁盘负担；
可以通过设置文件系统的mount属性，阻止系统写atime信息
//修改文件系统配置文件/etc/fstab，指定noatime选项
LABEL=\home /home /ext3 noatime 1 2
//然后重启mount文件系统
#mount -oremount /home
//完成上述操作，以后读/home下文件就不会再写磁盘了4. 用裸设备（RawDevice）存放InnoDB的共享表空间
MyISAM有自己的索引缓存机制，但数据文件的读写完全依赖于操作系统，操作系统磁盘IO缓存对MyISAM的存取很重要。
但InnoDB存储引擎与MyISAM不同，它采用类型Oracle的数据缓存机制来Cache索引和数据，操作系统的磁盘IO缓存对其性能不仅没有帮助，甚至还有反作用。
因此在InnoDB缓存充足的情况下，可以考虑使用RawDevice来存放InnoDB共享表空间，具体操作如下：
//(1) 修改MySQL配置文件，在innodb_data_file_path参数中增加裸设备文件名并制定newraw属性：
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
//(2) 启动MySQL，使其完成分区初始化工作，然后关闭MySQL。此时还不能创建或修改InnoDB表
//(3) 将innodb_data_file_path中的newraw改成raw
····
class=programlisting[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
····
//(4) 重新启动即可开始使用9. 应用优化
1. 使用连接池
连接池水很深，在这里就不深入说了，这锅应该让SA去背，23333
这里给一个用swoole实现的php环境下的数据库连接池
http://rango.swoole.com/archives/265
2. 减少对MySQL的访问
1. 避免对同一数据做重复检索
必须对SQL逻辑进行优化，能用一次连接提取所需结果，就绝对不用两次连接
2. 使用查询缓存
MySQL的查询缓存是在4.1版本后新增的功能，作用是存储SELECT查询的文本以及相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询。
查询缓存的使用对象是更新不频繁的表，当表更改后（包括表数据和表结构），查询缓存被清空
//查询缓存相关的参数主要有一下几个
mysql> show variables like'%query_cache%';
有一下几个参数：
have_query_cache:表明服务器在安装是否已经配置了高级缓存
query_cache_size:表明缓存区大小，单位为MB
query_cache_type的变量值从0到2，含义分别为：
0或者off（缓存关闭）、
1或者on（缓存打开，使用SQL_NO_CACHE提示的SELECT除外）、
2或者demand（只有带SQL_CACHE的SELECT语句提供高速缓存）
通过SHOW STATUS命令，可以监视查询缓存的使用状况
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目3. 增加CACHE层
相当于增加一个高速缓存区，用来缓冲对数据库本身的访问压力，但是需要注意几点：
(1) 缓存区的数据刷新规则（可以参考系统结构课程中的LRU等策略）
(2) 缓存区数据的读与写是否分离，若不分离，写操作的刷入频率规则
能够实现CACHE的方式有很多，问问度娘吧
3. 负载均衡
1. 利用MySQL复制分流查询操作
利用MySQL的主从复制可以有效的分流更新和查询操作，具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作
2. 采用分布式数据库架构
3. 其他优化
(1) 对于MyISAM表的删除操作，应当在空闲时间进行批量操作，并执行OPTIMIZE来消除空洞
(2) 充分利用默认值，可以减少MySQL需要做的语法分析量
(3) 表的字段尽量不要使用自增，高并发下会拖慢效率，最好将自增逻辑放在应用端实现10. MySQL工具
MySQL提供了大量丰富的工具帮助我们实现复杂的功能
1 mysql
2 myisampack(MyISAM表压缩工具)
3 mysqladmin（MySQL管理工具）
4 mysqlbinlog （日志管理工具）
5 mysqlcheck（MyISAM表维护工具）
6 mysqldump （数据导出工具）
7 mysqlhotcopy （MyISAM表热备份工具）
8 mysqlimport （数据导入工具）
9 perror （错误代码查看工具）11. MySQL日志
参见我的另一篇文章《数据库优化——MySQL日志》 :
12. 备份与恢复
参见我的另一篇文章《数据库优化——备份与恢复 》:
13. 权限与安全
参见我的另一篇文章《数据库优化——权限与安全 》:
传送门
- 聚集索引和非聚集索引
- SQL语句相关优化
更多数据库的文章，请戳下面的链接
数据库优化——常用SQL优化:
http://blog.csdn.net/leyounger/article/details/71583158
数据库优化——MySQL锁问题 :
http://blog.csdn.net/leyounger/article/details/71583665
数据库优化——优化MySQL Server :
http://blog.csdn.net/LeYOUNGER/article/details/72623944
数据库优化——MySQL日志 :
http://blog.csdn.net/leyounger/article/details/72626980
数据库优化——备份与恢复 :
http://blog.csdn.net/leyounger/article/details/72629578
数据库优化——权限与安全 :
扫一扫
726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
