Mysql优化分析

Mysql层次结构：

1、Connectors指的是不同语言中与SQL的交互

2、Management Serveices & Utilities：系统管理和控制工具

3、Connection Pool: 连接池管理缓冲用户连接，线程处理等需要缓存的需求。

4、SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

5、Parser: 解析器。

6、Optimizer: 查询优化器

7、Cache和Buffer：查询缓存

8、Engine ：存储引擎

Mysql主要存储引擎： （感觉存储引擎是一个优化点，可以自己写一个或者在某一个基础上改。。。。。）

• MyISAM： 拥有较高的插入，查询速度，但不支持事务
• InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定
• BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性

 

MySQL的优化

我们可以且应该优化什么？

1. 硬件
2. 操作系统

 

 

 

Mysql 数据库优化的方案与实践

一、硬件优化：

1. 开启BBWC(Battery Backed Write Cache)。RAID卡都有写cache(Battery Backed Write Cache)，写cache对IO性能的提升非常明显
2. RAID卡配置。

   （1）关闭读cache：RAID卡上的cache容量有限，我们选择direct方式读取数据，从而忽略读cache。

   （2）关闭预读：RAID卡的预读功能对于随机IO几乎没有任何提升，所以将预读功能关闭。

   （3）关闭磁盘cache：一般情况下，如果使用RAID，系统会默认关闭磁盘的cache，也可以用命令强制关闭。

3. 开启Fastpath功能。Fastpath是LSI的新特性，在RAID控制器为SSD做了优化，使用fastpath特性可以最大程度发挥出SSD的能力。如果使用SSD做RAID的方式，可以开启fastpath功能。 LSI MegaRAID FastPath 软件是高性能输入输出加速器，用于连接到MegaRAID 控制器卡的固态驱动器（SSD）阵列。此高级软件是LSI MegaRAID 技术的最优化版本，可以在与连接到 SSD 的 6Gb/s，MegaRAID SATA+SAS 控制器一起部署时 — 显著地提升存储子系统和整体应用性能 — 尤其是那些表现出高随机读/写操作的工作负载。
4. 为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘。 降低寻道时间。
5. 将数据库和表分在不同的磁盘上。
6. 使用电池供电的缓存 RAID（Battery-Backed Cache RAID）控制器。

7. 避免使用软磁盘阵列。

 

二、操作系统优化

0.IO调度算法

• CFQ(Complete Fair Queuing)完全公平的排队：通过时间窗口在不同进程间的移动，保证了对于所有进程而言都有公平的发出IO请求的机会。对于只有少数进程存在大量密集的I/O请求的情况，会出现明显的I/O性能下降
• NOOP调度算法：按先来先处理的思路将请求插入到等待队列的尾部
• DEADLINE调度算法：一是按照扇区排序的读写请求队列；二是按照过期时间排序的读写请求队列。

完全随机的访问环境下，CFQ与Deadline，NOOP性能差异很小，但是一旦有大的连续IO，CFQ可能会造成小IO的响应延时增加，所以数 据库环境建议修改为deadline算法，表现更稳定。

1. 不要交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存。
2. 不要使用NFS磁盘(会有NFS锁定的问题)。
3. 增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。
4. 增加系统的进程和线程数量。
5. NUMA设置。
6. 如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道上
7. 选择使用哪种文件系统。

• XFS是一种高性能的日志文件系统，是一个64位文件系统，最大支持 8EB 减1字节的单个文件系统，实际部署时取决于宿主操作系统的最大块限制。对于一个32位Linux系统，文件和文件系统的大小会被限制在 16tebibytes。主要特性包括以下几点：数据完全性、传输特性、可扩展性、传输带宽。
• ReiserFS是一种新型的文件系统，它通过一种与众不同的方式--完全平衡树结构来容纳数据，包括文件数据，文件名以及日志支持。ReiserFS还以支持海量磁盘和磁盘阵列，并能在上面继续保持很快的搜索速度和很高的效率。
• Ext4/3/2文件系统。

         总结：ext3 为 ext2 的日志版，提供了 metadata 日志系统 并且可以快速地使用日志系统复原。它有额外的 hashed b-tree 索引功能，开启他后 几乎任何情况内都是高效能。

ReiserFS 它处理小型档案(少于4kb)时效能会比 ext2 和 ext3 来的好。他使用的是 B*-tree 为基础的文件系统. 另外也能很有效率地处理大型 文件。Linux 核心版本 2.4.18+ 后，ReiserFS 是一个相当有值得推荐的系统, 从很少，到很多档桉。ReiserFS 都可以处理的非常好。开机 分割区并不建议使用此文件系统。

XFS 是一个 metadata 日志系统，并且拥有完整的功能及针对延展性最佳化。如果您使用高速 SCSI或是纤维的储存装置，并且有持续不断 的电源供应，我们才推荐您使用 XFS。 如果没有，请使用其他文件系统。因为 XFS 大量地将要转送的资料快取在记忆体中，设计不好的程 式(的确有一些程式在写入磁碟时不做一般的预防措施)可能当系统意外断电时损失大量的资料。

           8. 使用 64 位操作系统 — 有更多的内存能用于寻址和 MySQL 使用。

 

 

（其他方面的优化忽略）

三、数据库：

    1.Flashcache参数

2.Percona参数

　　innodb_page_size：如果使用fusionio，4K的性能最好;使用SAS磁盘，设置为8K。如果全表扫描很多，可以设置为16K。比较小的page size，可以提升cache的命中率。

　　innodb_adaptive_checkpoint：如果使用fusionio，设置为3，提高刷新频率到0.1秒;使用SAS磁盘，设置为2，采用estimate方式刷新脏页。

　　innodb_io_capacity：根据IOPS能力设置，使用fuionio可以设置10000以上。

　　innodb_flush_neighbor_pages = 0：针对fusionio或者SSD，因为随机IO足够好，所以关闭此功能。

　　innodb_flush_method=ALL_O_DIRECT：公版的MySQL只能将数据库文件读写设置为DirectIO，对于Percona可以将log和数据文件设置为direct方式读写。但是我不确定这个参数对于innodb_flush_log_at_trx_commit的影响。

　　innodb_read_io_threads = 1：设置预读线程设置为1，因为线性预读的效果并不明显，所以无需设置更大。

　　innodb_write_io_threads = 16：设置写线程数量为16，提升写的能力。

innodb_fast_checksum = 1：开启Fast checksum特性。

四、SQL优化：

    （1）为查询缓存优化查询。某些查询语句会让MYSQL不使用缓存，所以尽量避免这个语句，比如[ $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); ] ，其中CURDATE()就不会开启缓存

 

    （2）EXPLAIN查询。使用 EXPLAIN 关键字可以知道MySQL是如何处理SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

    （3）当只要一行数据时使用 LIMIT 1

（4）为搜索字段建索引

…….