Mysql - 数据库性能从哪几些方面如何优化

 

 

1、硬件和操作系统层面的优化

硬件：cpu、内存、磁盘io、网络带宽
操作系统：Linux、Windows（mysql最初就是基于linux系统开发的，所以mysql在linux系统上的稳定性和性能普遍比在windows系统上高很多。）
应用文件句柄（ulimit -a open files）
网络配置

2、架构设计层面的优化
集群方式（主从集群或者主主集群）：避免单点故障
读写分离：读写分开，将压力分担，避免读写冲突，提高性能
分库分表：分库降低单一服务器的IO压力；分表降低单表数据量，提升sql查询效率
分布式数据库：针对热点数据，使用redis、mongodb等降低直接查询mysql的压力，提升数据检索性能

3、数据库配置优化（存储引擎与表结构）

存储引擎的选择：http://c.biancheng.net/view/7185.html
最大连接数：max_connections 默认151 上限16384 建议值：实际连接数是最大连接数的85%较为合适
binlog日志开启：log_bin
binlog日志格式：binlog_format=row
连接超时时间：wait-timeout=600 默认8小时
并发线程数：innodb_thread_concurrency 默认0无限制 建议值：设置为cpu核心数的两倍
请求堆栈：back_log 默认50 建议值：设置为最大并发连接数的20%～30%
缓存池配置：innodb_buffer_pool_size 建议值：主机内存的70%～80%

关于配置项的修改，需要关注：第一个是配置的作用域，它可以分为会话级别和全局范围。第二个是是否支持热加载，针对这两个点，我们需要注意的是，全局参数的设定，对于已经存在的会话是无法生效的，会话参数的设定，随着会话的销毁而失效。第三个是全局类的统一配置，建议配置在默认配置文件中，否则重启服务会导致配置失效。

4、SQL执行优化
慢sql定位和排查：慢查询日志、慢查询日志分析工具 >> 获取到慢sql
执行计划分析：关键字explain，重点关注type，key，rows，filterd等字段，从而去定位该SQL执行慢的根本原因，再去有的放矢的进行优化。
show profile工具：MySQL提供的可以用来分析当前会话中SQL语句资源消耗情况的工具，可以用于SQL调优的测量，在当前会话中，默认情况下，show profile是关闭状态，打开以后会保存，最近15次的运行结果，针对运行慢的SQL通过profile工具进行详细分析，可以得到SQL执行过程中所有资源的开销情况，比如io开销，cpu开销，内存开销。
示例：

-- 查看当前是否开启此功能
show variables like '%profiling%';
-- 开启
set profiling=1;
-- 查看具体执行时间
show profiles;
-- 查看详细的每个步骤时间
show profile for query 1;
-- 显示所有性能信息
show profile all for query n
--  block io：显示块IO操作次数
show  profile block io for query n
-- context switches：显示上下文切换次数，被动和主动
show profile context switches for query n
-- cpu：显示用户cpu时间、系统cpu时间
show profile cpu for query n
-- IPC：显示发送和接受的消息数量
show profile ipc for query n
-- page faults：显示页错误数量
show profile page faults for query n
-- source：显示源码中的函数名称与位置
show profile source for query n
-- swaps：显示swap的次数
show profile swaps for query n

sql优化规则：

1）、SQL的查询一定要基于索引来进行数据扫描。
2）、避免索引列上使用函数或者运算符。
3）、Where字句中like%号尽量放置在右边。
4）、使用索引扫描，联合索引中的列从左往后，命中越多越好
5）、尽可能使用SQL语句用到的索引完成排序
6）、查询有效的列信息即可，少用*【多使用覆盖索引】
7）、永远要用小的结果集驱动大的结果集

5、业务优化
严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，举几个简单例子：

1）以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；

2）双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；

3）双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。