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的优化
我们可以且应该优化什么?
-
硬件
-
操作系统
Mysql 数据库优化的方案与实践
一、硬件优化:
-
开启BBWC(Battery Backed Write Cache)。RAID卡都有写cache(Battery Backed Write Cache),写cache对IO性能的提升非常明显
-
RAID卡配置。
(1)关闭读cache:RAID卡上的cache容量有限,我们选择direct方式读取数据,从而忽略读cache。
(2)关闭预读:RAID卡的预读功能对于随机IO几乎没有任何提升,所以将预读功能关闭。
(3)关闭磁盘cache:一般情况下,如果使用RAID,系统会默认关闭磁盘的cache,也可以用命令强制关闭。
-
开启Fastpath功能。Fastpath是LSI的新特性,在RAID控制器为SSD做了优化,使用fastpath特性可以最大程度发挥出SSD的能力。如果使用SSD做RAID的方式,可以开启fastpath功能。 LSI MegaRAID FastPath 软件是高性能输入输出加速器,用于连接到MegaRAID 控制器卡的固态驱动器(SSD)阵列。此高级软件是LSI MegaRAID 技术的最优化版本,可以在与连接到 SSD 的 6Gb/s,MegaRAID SATA+SAS 控制器一起部署时 — 显著地提升存储子系统和整体应用性能 — 尤其是那些表现出高随机读/写操作的工作负载。
-
为系统、程序和临时文件配备一个专用磁盘,如果确是进行很多修改工作,将更新日志和事务日志放在专用磁盘。 降低寻道时间。
-
将数据库和表分在不同的磁盘上。
-
使用电池供电的缓存 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算法,表现更稳定。
-
不要交换区。如果内存不足,增加更多的内存或配置你的系统使用较少内存。
-
不要使用NFS磁盘(会有NFS锁定的问题)。
-
增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。
-
增加系统的进程和线程数量。
-
NUMA设置。
-
如果你有相对较少的大表,告诉文件系统不要将文件打碎在不同的磁道上
-
选择使用哪种文件系统。
-
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)为搜索字段建索引
…….