mysql的性能优化

82870034

　　原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://superleedo.blog.51cto.com/12164670/1906410
　　mysql的性能优化是运维和DBA们常常面对的问题，也是各大公司招聘人才时看中的要点之一。性能优化听上去很难，似乎只有大神才能做，然而，mysql的性能优化绝不是运维独自一个能完成的，DBA、开发、架构也要参与其中，齐心协力才能打赢性能优化之战。
　　本文将全面解说数据库优化的各个方面，主要也就是硬件、网络、系统、架构、软件这几个方面，其中软件主要包括软件版本、表设计、引擎、SQl语句、配置文件my.cnf等几个方面
　　一 硬件和网络
　　硬件和网络应该怎么优化呢？两个字“砸钱”！购买优质服务器，高性能多核cpu，高内存，高性能磁盘或者ssd盘等。硬件高配置是数据库高性能的基础，一般公司来说一台数据库机器配上72G内存已经足够了，具体配置也可根据公司的业务需求来添加。网络的基础配件性能越高越好，N兆光纤、N兆交换机、N兆网卡。当然，如果公司差钱，那就选择合适当前业务的基础配置即可。总之一句话：硬件与网络要用钱来优化！
　　二 系统
　　操作系统选择，首选当前流行稳定的版本，比如centos6.5 centos6.8，尽量少用5版本，如果公司处于整体环境升级阶段，可以直接换成最新的7版本，其性能优于5、6版本；如果不差钱也可以用redhat的相应版本，本人建议还是用centos吧，不花钱且社区活跃，方便后期管理升级。AIX系统不建议使用，至于什么原因，我只能送给AIX系统两个字“呵呵”。文件系统上尽量用ext4！
　　三 架构体系
　　mysql数据库的架构体系比较单纯，从单机到一主一从，再到一主多从，再到多主多从，再到读写分离，分级存储，分库分表，主要的形式也就这么多，具体怎么选择是根据业务需求而定，业务量较小的公司可以考虑一主多从，一般公司做到主从+读写分离就可以了，这样既有了性能也有了备份，数据量特别巨大的才用分库分表。
　　四 数据库版本控制
　　数据库版本与数据库的性能有着密切的关系，版本越高功能越丰富性能越强悍，但是高版本存在着可能未发现的隐患。目前大部分系统自带的mysql5.1，此版本稳定而悠久，在功能和性能要求不高的数据库环境中可以使用；不过企业环境中还是建议升级到mysql5.6以上版本，如果单机性能要求特高可以使用mysql5.7或者mariadb10.1或者percona5.7；mysql5.6在企业环境的应用较为普遍，性能也稳定，推荐使用这个版本；如果想使用最新版本的高性能数据库（据测5.7版本读写性能是5.6版本的2倍），推荐使用mariadb10.1，因为mysql企业版收费了，也有闭源的趋势。总结下：推荐使用mysql5.6或者mariadb10.1的相应版本。
　　高版本mysql5.7企业版，较之前版本功能性能的确有了较大提升：
　　1 在读模式下，3倍性能提升；在读写模式下，2倍性能提升。
　　2 默认开启ssl安全模式
　　>bin/mysql_ssl_rsa_setup 开启
　　3 Buffer_pool可以动态设置大小，无需重启
　　set global innodb_buffer_pool_size=256*1024*1024   ##设置256M
　　4 缓冲池预热
　　innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup=1
　　5 支持全中文索引
　　6 支持死锁打印到错误日志  innodb_print_all_deadlocks=1
　　7 支持json格式数据存储
　　8 支持kill慢sql        set global max_statement_time=1
　　9 支持日志审计 audit plugin
　　10 支持错误日志打印到系统日志文件中
　　五 表设计优化
　　关系数据库面临着范式的选择，最低满足第一范式：表中的字段都是单一属性，不可再分的；第二范式要求实体的属性完全依赖于主关键字；第三范式是不存在非关键字对任一候选主键的函数依赖；数据库的设计应当最大程度的满足三范式，当然三范式也是存在着问题的，它通常要很多join表，导致查询效率低下；有时候可以适当的做冗余，减少join，但要用审慎的态度对待！
　　表字段类型选择也是优化的重点。选取原则尽量是选小不选大，能用字节少的字段就不用大字段。更小的字段类型占用更少的内存，占用的磁盘和内存也会很小，占用的宽带也会少，所以做字段选择时候必须坚持“用小不用大”。比如主键，强烈推荐使用int整型。
　　数值类型一般常用的int（4个字节）和bigint（8个字节），比如手机号可用bigint，年龄用tinyint；字符类型最常用的有char（256）和varchar（65535），他们跟字符编码也有关系，latin1占用一个字节，gbk占用2个字节，utf8占用3个字节；时间类型常用date（3个字节）和datetime（8个字节）、timestamp（4个字节）。选择各种字段类型时候选择占用最少字节最适合的。
　　在线修改表结构，可以使用percona的工具pt-online-schema-change,不影响业务。
　　六 数据库引擎的选择
　　MyISAM和InnoDB是mysql数据库最常用的两种存储引擎，在5.5版本之前，默认是MyISAM，之后版本默认是InnoDB。两者的主要区别如下：
　　1  MyISAM是非事务安全型，InnoDB是事务安全型
　　2  MyISAM应用表级锁，开销小，InnoDB是行级锁，开销大，支持更好的并发写操作
　　3 MyISAM支持全文索引，Innodb在5.6版本以后才支持
　　4 MyISAM相对简单，管理方便，效率高，小型应用可以考虑使用MyISAM引擎
　　5 MyISAM表保存成文件形式，易于跨平台迁移
　　6 InnoDB比MyISAM更安全，可以随时从非事务切换到事务
　　据测，在同等配置下的压力测试中，InnoDB性能是MyISAM的10倍左右，所以在选着具有大量读写操作的应用中，推荐InnoDB优先使用引擎！
　　七 SQL语句优化
　　SQL语句决定着数据库70%的性能状况，大多数性能不好都是sql语句引起的。
　　普通硬件+普通配置+完美sql语句=一般性能
　　完美硬件+完美配置+垃圾sql语句=较差性能
　　完美硬件+完美配置+一般sql语句=一般性能
　　完美硬件+完美配置+完美sql语句=超优性能
　　1 定位慢SQL语句(记录)
　　开启慢日志功能，在my.cnf中添加配置：
　　slow_query_log=1
　　slow_query_log_file=mysql.slow
　　long_query_time=2        #超过两秒的记录下来
　　当数据库连接数较高时，就可以截取某段时间的满日志
　　sed -n '#time 2017-03-08 14:30:00/,/end/p' mysql.slow > slow.log
　　然后用mysqldumpslow 命令取出耗时间最长的10条慢sql分析
　　mysqldumpslow -s t -t 10 slow.log
　　2 优化not in子查询
　　用left join 代替not in 子查询

　　原语句：> select sql_cache count(*) from t1 where>　　优化： > select sql_cache count(*) from t1 left join t2 on t1.id=t2.id where t2.id is null;
　　3 优化like语句
　　在mysql中，like 'xxx%'可以用到索引，但是like '%xxx%'不能用到索引。
　　使用索引可以减少IO，提高性能。
　　原语句：> select * from t1 where name like '%game%';

　　优化： > select>　　4 limit 分页优化

　　原语句： > select game * from t1 order by>
　　优化： > select game * from t1 where>　　5 优化count统计
　　利用辅助索引和distinct优化
　　原语句： > select count（*） from my_user;

　　优化： > select count(*) from my_user where>　　> select count(*) from (select distinct k from my_user)tmp;
　　6 优化or语句
　　使用union all 代替or
　　原语句： > select * from user where name='a' or age=18;
　　优化： > select * from user where name='a' union all select * from user where age =18;
　　7 合理使用索引
　　八 my.cnf配置文件优化
　　配置文件内的可以设置的项很多，下面列出常常需要注意重要项
　　1 max_connections 最大连接数，默认100 ，一般设置500-1000即可。
　　2 innodb_buffer_pool_size，默认128M，可以设置为物理内存的60%-70%。
　　3 query_cache_size=64M query_cache_type=1 query_cache_limit=1M
　　4 wait_timeout=100    等待时长
　　5 connect_timeout=20    interactive_timeout=100
　　6 slow_query_log=1    开启慢日志
　　7 thread_cache_size=64

　　8>　　9 open_files_limit=28512
　　..........
　　具体配置参数需要根据硬件环境和业务需求去定。
　　结语：mysql性能优化是一项系统庞大的工程，需要运维、架构、开发等多方参与共同改进，切勿盲目做调优处理，认真分析性能瓶颈所在，针对瓶颈处调优方能事半功倍！