一、 优化思路
- 选择合适的数据库引擎:详见第二点
- 配置优化:见第三点
- Sql优化:性能瓶颈定位、show status命令、慢查询日志、explain分析查询、profiling分析查询
- 索引优化:见第四点
- 优化排序:见第五点
- 读写分离:见第六点
- 表结构优化:水平拆分、垂直拆分和逆规范化,见第七点
- 硬件升级:是用RAID10磁盘阵列,RAID10兼具RAID1的可靠性和RAID0的优良并发读写性能
- 使用表分区: 跨多个磁盘来分散查询,能获得更大的吞吐量,需要一定的硬件条件
二、常见数据库引擎对比
常见数据库引擎对比-
MyISMA是MySQL的默认存储引擎。MyISMA不支持事务,不支持外键,优势是访问速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。
-
InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全,支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。
-
MEMORY存储引擎-内存数据库,服务重启数据会丢失。适用于那些内容变化不频繁的代码表(常量表),或者作为统计结果的中间结果表。修改的数据不会写入磁盘。
-
MERGE存储引擎是一组MyISMA表的组合,这些MyISMA表的结构必须完全相同,MERGE表本身没有数据,对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。
-- 查看数据库支持的存储引擎
show engines;
三、数据库配置
配置参数 | 配置说明 |
---|---|
innodb_buffer_pool_size | 这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方:这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存) |
log_bin | 如果你想让数据库服务器充当主节点的备份节点,那么开启二进制日志是必须的。如果这么做了之后,还别忘了设置server_id为一个唯一的值。就算只有一个服务器,如果你想做基于时间点的数据恢复,这(开启二进制日志)也是很有用的:从你最近的备份中恢复(全量备份),并应用二进制日志中的修改(增量备份)。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽,你可以用 PURGE BINARY LOGS 来清除旧文件,或者设置 expire_logs_days 来指定过多少天日志将被自动清除。配置log_bin时必须指定server-id,否则无法启动 |
innodb_log_file_size | 这是redo日志的大小。(前提是打开log_bin)redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6,你可以一开始就把它这是成4G |
max_connections | 如果你经常看到‘Too many connections'错误,是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接,你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题 |
skip_name_resolve | 当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。加上这个配置就可以不通过用户直接连接数据库了 |
MySql官方已经决定在以后的版本中取消缓存技术,太鸡肋,可以使用第三方缓存技术(ehcache或者redis等),所以缓存配置就不要看了
四、索引优化
索引设计原则:
- 最适合索引的列是在where子句中的列,或连接子句中的列,而不是出现在select关键字后的列
- 使用唯一索引。考虑某列中值的分布。索引列的基数越大,效果越好(一列中相同的数据越少,索引越好)
- 使用短索引。如果对字符串列进行索引,应该指定一个前缀长度。这样可以节省索引空间和磁盘IO。(alter tableName add key indexName (columnName(7)) --给表tableName的columnName字段的前7位建立前缀做引,索引名字为indexName)
- 利用最左前缀。比如创建了一个多列索引 index_c1_c2_c3 (c1,c2,c3),相当于创建了(c1)单列索引,(c1,c2)的组合做引以及(c1,c2,c3)的组合索引。根据这个原则,在创建多列索引时,要根据业务需求 ,where子句中使用最频繁的一列要放在索引的最左边。
- 不要过度索引。索引过多,会导致磁盘占用较高,insert和update操作耗时增加,查询优化效率会变低。
以下不会使用索引的几种情况:
- 以%开头的like查询不能使用索引
- 数据类型出现隐式转换的不能使用索引。数据INT类型,而用varchar查询
- 复合索引的情况下,假如查询条件不包含索引列最左边部分,不使用索引
- 如果MySQL估计使用索引比全表扫描慢,不使用索引
- 用or分隔开的条件,如果or前的列中有索引,而后边的列中没有索引,不会使用索引。(or的所有条件必须全部使用索引字段才会走索引
五、排序优化
MySql排序算法的执行方式: 将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序,如果内存装载不下,它就会将磁盘上的数据进行分块,再对各个块进行排序,然后将各个块合并成有序的结果集。
优化方案:
- 尽量减少额外的排序,通过索引直接返回有序数据
- 适当加大max_length_for_sort_data系统变量,让更多的SQL可以在内存中完成排序,减少磁盘I/O操作。(因为排序区是每个线程独占的,设置过大会导致服务器SWAP严重)
- 尽量只使用必要的字段,select具体的字段名字,而不是select *,这样可以减少排序区的使用,提高SQL性能
- MySQL会对GROUP BY后的所有字段排序,group by a1,a2,a3相当于后边默认加了order by a1,a2,a3 ,如果要避免排序带来的消耗,可以使用order by null禁止排序
六、读写分离配置
- 修改master配置文件:
log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
binlog-do-db = amoeba_study #用于master-slave的具体数据库
- 添加专门用于replication的用户
mysql> GRANT REPLICATION SLAVE ON *.* TO username@host IDENTIFIED BY 'password';
- 如果库中已有数据,需要记下file和position
mysql> flush tables with read lock;
mysql> show master status; # 得到file和position
mysql> unlock tables;
- 编辑slave的配置文件,添加server-id
server-id=2 #slave的标示,需要唯一
- 配置生效后,配置与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; # 启动从库,开始同步数据
- 安装amoeba,按照官方文档进行配置,这里就不详细介绍了
- 配置服务端直接连接到amoeba即可
七、表结构优化
垂直拆分
把主键和一些常用的字段放到一个表中,把主键和其他的字段放到另一个表中。
优点:垂直拆分可以使一个数据页放更多的数据,可以较少IO次数。
缺点:查询所需的数据可能需要通过JOIN来查询。
适用场景:表过宽,包含text或blob字段,可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。
水平拆分
根据某一列的值把数据放到多个独立的表中,比如历史数据放到另一张表里。
优点:减少大多数查询读取的数据量,降低索引层数,提高查询速度。
缺点:增加查询复杂度,查询多个表需要使用UNION,或者通过MERGE表。
适用场景:表中数据量过大,历史数据查询次数很少,比如订单信息、操作记录等。
逆规范化
增加冗余列:在多个表中具有相同的列,避免联合查询
增加派生列:增加的列来自其他表的计算结果,可避免使用函数
重新组表:将经常联合查询的表组成一个表,减少联合查询
网友评论