MySql性能优化

一、 优化思路

1. 选择合适的数据库引擎：详见第二点
2. 配置优化：见第三点
3. Sql优化：性能瓶颈定位、show status命令、慢查询日志、explain分析查询、profiling分析查询
4. 索引优化：见第四点
5. 优化排序：见第五点
6. 读写分离：见第六点
7. 表结构优化：水平拆分、垂直拆分和逆规范化，见第七点
8. 硬件升级：是用RAID10磁盘阵列，RAID10兼具RAID1的可靠性和RAID0的优良并发读写性能
9. 使用表分区： 跨多个磁盘来分散查询，能获得更大的吞吐量，需要一定的硬件条件

二、常见数据库引擎对比

常见数据库引擎对比

• MyISMA是MySQL的默认存储引擎。MyISMA不支持事务，不支持外键，优势是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。
• InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全，支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。
• MEMORY存储引擎-内存数据库，服务重启数据会丢失。适用于那些内容变化不频繁的代码表（常量表），或者作为统计结果的中间结果表。修改的数据不会写入磁盘。
• MERGE存储引擎是一组MyISMA表的组合，这些MyISMA表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。

-- 查看数据库支持的存储引擎
show engines;

三、数据库配置

MySql官方已经决定在以后的版本中取消缓存技术，太鸡肋，可以使用第三方缓存技术（ehcache或者redis等），所以缓存配置就不要看了

四、索引优化

索引设计原则：

1. 最适合索引的列是在where子句中的列，或连接子句中的列，而不是出现在select关键字后的列
2. 使用唯一索引。考虑某列中值的分布。索引列的基数越大，效果越好（一列中相同的数据越少，索引越好）
3. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。这样可以节省索引空间和磁盘IO。（alter tableName add key indexName (columnName(7)) --给表tableName的columnName字段的前7位建立前缀做引，索引名字为indexName）
4. 利用最左前缀。比如创建了一个多列索引 index_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)单列索引，(c1,c2)的组合做引以及(c1,c2,c3)的组合索引。根据这个原则，在创建多列索引时，要根据业务需求 ，where子句中使用最频繁的一列要放在索引的最左边。
5. 不要过度索引。索引过多，会导致磁盘占用较高，insert和update操作耗时增加，查询优化效率会变低。

以下不会使用索引的几种情况：

1. 以%开头的like查询不能使用索引
2. 数据类型出现隐式转换的不能使用索引。数据INT类型，而用varchar查询
3. 复合索引的情况下，假如查询条件不包含索引列最左边部分，不使用索引
4. 如果MySQL估计使用索引比全表扫描慢，不使用索引
5. 用or分隔开的条件，如果or前的列中有索引，而后边的列中没有索引，不会使用索引。（or的所有条件必须全部使用索引字段才会走索引

五、排序优化

MySql排序算法的执行方式： 将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个块进行排序，然后将各个块合并成有序的结果集。

优化方案：

1. 尽量减少额外的排序，通过索引直接返回有序数据
2. 适当加大max_length_for_sort_data系统变量，让更多的SQL可以在内存中完成排序，减少磁盘I/O操作。（因为排序区是每个线程独占的，设置过大会导致服务器SWAP严重）
3. 尽量只使用必要的字段，select具体的字段名字，而不是select *，这样可以减少排序区的使用，提高SQL性能
4. MySQL会对GROUP BY后的所有字段排序，group by a1,a2,a3相当于后边默认加了order by a1,a2,a3 ，如果要避免排序带来的消耗，可以使用order by null禁止排序

六、读写分离配置

1. 修改master配置文件：

log-bin=mysql-bin        #slave会基于此log-bin来做replication
server-id=1    #master的标示
binlog-do-db = amoeba_study        #用于master-slave的具体数据库

1. 添加专门用于replication的用户

mysql> GRANT REPLICATION SLAVE ON *.* TO username@host IDENTIFIED BY 'password';

1. 如果库中已有数据，需要记下file和position

mysql> flush tables with read lock;
mysql> show master status;   # 得到file和position
mysql> unlock tables;

1. 编辑slave的配置文件，添加server-id

server-id=2    #slave的标示,需要唯一

1. 配置生效后，配置与master的连接：

mysql> CHANGE MASTER TO
-> MASTER_HOST='masterhost',
-> MASTER_USER='2中的username',
-> MASTER_PASSWORD='2中的password',
-> MASTER_LOG_FILE='3中的file',
-> MASTER_LOG_POS='3中的posiition';

mysql> start slave;  # 启动从库，开始同步数据

1. 安装amoeba，按照官方文档进行配置，这里就不详细介绍了
2. 配置服务端直接连接到amoeba即可

七、表结构优化

垂直拆分 把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。 优点：垂直拆分可以使一个数据页放更多的数据，可以较少IO次数。 缺点：查询所需的数据可能需要通过JOIN来查询。 适用场景：表过宽，包含text或blob字段，可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。

水平拆分 根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。 优点：减少大多数查询读取的数据量，降低索引层数，提高查询速度。 缺点：增加查询复杂度，查询多个表需要使用UNION，或者通过MERGE表。 适用场景：表中数据量过大，历史数据查询次数很少，比如订单信息、操作记录等。

逆规范化 增加冗余列：在多个表中具有相同的列，避免联合查询 增加派生列：增加的列来自其他表的计算结果，可避免使用函数 重新组表：将经常联合查询的表组成一个表，减少联合查询

作者：一一道长一一 链接：https://www.jianshu.com/p/477b81ef4240 来源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。