mysql数据库优化

    mysql是一款开源数据库，因其开源免费、可移植、便于使用、高性能等特点被广泛使用。而身为后端程序员，不止需要sql的编写使用技能，更是需要对其实现架构有所了解，以便在具体的业务场景中对其进行有针对性的优化。而本文将记录在日常使用中，对mysql多个维度对一些优化技巧及使用注意项，叙述以innodb引擎为主

系统环境层面优化

    mysql被设计成一个可移植的数据库，几乎可以在当前所有系统上运行，在实际部署中，机器及系统环境的选择我们需要注意：

• 选择合适的cpu：数据库应用一般可分为OLTP及OLAP两大类，OLTP（online transaction processing）应用特点是用户操作并发量大、处理时间短，查询语句较为简单，一般走索引，因此这种应用是IO密集型，设备采购应主要注意磁盘IOPS；OLAP（online analysis processing）型应用一般涉及复杂查询例如归并、排序、连接呢，为cpu密集型应用，应选择多核、高频的CPU；在innodb1.2后版本，mysql除了原有对master thread，又新增了多个purge线程将数据页刷新到磁盘的操作从master线程中分离处理，因此若是多核cpu，通过设置innodb_read_io_thread和innodb_write_io_thread大小可以获得更高读写性能
• 选择合适的内存大小：innodb基于磁盘存储，并将其中数据分页存储，但对数据库的读取或写入不是直接操作磁盘，而是通过一个缓冲池获取更高性能，读取时会先看缓冲池是否已存在需要查询的数据，若有直接返回内存中数据而不进行磁盘io，写时也是先写入缓冲池数据页，再由专门的purge线程异步写入磁盘，更大的内存大小意味着可以设置更大的mysql缓冲池大小，更大的数据内存驻留可减少磁盘IO，减少时延
• 选择合适的硬盘：机械硬盘由磁头在物理磁盘中寻道获取数据，因此其寻道时间、转速是其性能关键，而固态硬盘是闪存存储，能提供一致的随机访问时间，读取速度远快于写入速度，iops更高，因此能选择固态硬盘就选择固态硬盘。下图为机械硬盘与各规格固态硬盘随机访问耗时统计

• 合理设置RAID（Redundant Arrays of Independent Drive）：raid基本思想就是多个相对便宜、容量小的硬盘组合成为一个磁盘组，使性能达到甚至超过一个昂贵、容量巨大的硬盘。他可以增强数据集成度、增强容错能力、增加处理量和容量等。对于数据库应用来说，RAID10是个较好的选择，提供了较高的数据保护性及读取速度
• 操作系统选择：考虑到线上的稳定性，才用linux x64系统将是首选，可通过配置linux系统关键参数提升一些性能

• 文件系统选择：不同系统支持的文件格式各不相同，例如linux可支持EXT3、EXT4、XFS等，但是各文件系统对数据库性能的影响并没有那么大，我们还是应该更多的聚焦到数据库上
• 合理的mysql设置：

 

SQL语句优化

    sql语句的合理编写对于编程人员来说尤为重要，个人认为写sql最重要的一条就是合理利用索引。索引就相当于我们看书的目录，帮助我们快速定位到需要的数据，如何合理利用索引，可总结为以下几个方面：

1、索引有哪些种类（innodb）

• 聚簇索引：即主键，以插入顺序排列，B+树结构，叶子结点存放具体数据结构，一个表仅有一个聚簇索引，由于需排序，建议以自增序列数字方式存储，不然会影响插入、读取性能
• 非聚簇索引：可细分为普通索引、联合索引、覆盖索引等，满足不同场景需求，也为B+ 树结构，叶子结点存放数据的主键值，用于从聚簇索引中定位到具体数据。

2、索引的失效条件，写sql时应避免这些情况

• 对索引字段进行了隐式转换，如int型用‘’包围变成了string型时
• 对索引字段进行了加工或计算，如concat、to_char等
• 使用like时使用了左%
• 使用了not in，not exist，!=等
• B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
• 单独引用联合索引里非第一位置的索引列
• 其他查询优化器认为不走索引更合适的场景

3、哪些字段适合作为索引

• 查询语句中where后作为筛选条件的字段
• group by、order by等后接的字段
• 区分度不高的字段不适合作为索引，例如性别、状态常量等
• 有大量null值的字段，索引对null值需特殊处理，影响效率
• 经常需要修改的字段不适宜作为索引，因为这涉及到频繁修改索引B+树

4、索引是否越多越好

适当的索引数量可以加快查询速度，但过多但索引会影响插入速度，因为需要修改相应的索引B+树，且存储索引需要耗费磁盘空间

5、如何判断sql语句是否走了索引

执行 explain +sql 可以查看sql执行计划，便可看出是否走了索引；也可以通过sql语句中添加hint信息强制选择走哪条索引

6、日常sql调优思路

• 日常调优：打开slowlog记录查询超过一定耗时的sql-->按照业务优先级排序，对top sql进行explain，分析调优可能-->调整索引或语句本身
• 线上应急调优：showprocesslist-->explain sql-->判断执行计划是否合适-->show status like '%lock%'查询锁状态-->kill sessionId杀掉有问题对session

7、其他一些sql注意项

• 表join时应以小表驱动大表，即小表在前，加快筛选效率
• join表数量不宜超过三个，若超过则考虑在应用层进行拆分

数据的备份与高可用

mysql数据备份有多种方式，可考虑的方向有：

1、创建crontab定时执行备份的sh脚本，shell中利用mysqldump进行数据备份，mysqldump命令可以支持指定库、指定表备份

2、select * into outfile '/user/test.csv' from table ...导出数据到目录

3、mysql自带主从复制功能，从服务器订阅master的binlog实现数据备份，原理如下：

4、外部应用订阅binlog防止数据丢失