当前位置:

台州PHP培训学校推荐

台州PHP培训课程推荐

台州培训新闻内容

[ 2017-07-29 12:01:19 ] 点击:600 台州PHP培训

技术分享:Mysql性能优化技巧方法

Mysql性能优化技巧方法


[原创]
 
对于mysql基础不做过多的介绍。本篇重点介绍mysql性能优化的入门方法和技巧。
 
mysql优化分为mysql部分、程序部分、业务逻辑部分。
 
1、业务逻辑部分优化,通常是产品经理瞎BB,各种对数据库应用的扭曲调用(多表关联,分库查询,查询结果2次排序等古怪需求)。说服一个瞎BB的产品经理是很困难的事情,祈祷有一个好产品经理很重要,遇到瞎BB的产品经理,别做mysql优化了,赶紧跳槽,否则即便你优化到很牛逼,产品经理也会给你安上一堆毛病,最后老板把你给开除了。
 
 
2、程序、设计优化
这部分设计要尽可能把系统的调用往k-v的取值上靠拢,完美的状况是所有的sql都是
select xxx from tbl1 where id = ? 
这种可以直接k-v的语句,是效率最高的。但是实际项目的应用是无法做到这一点,常见的情况通常是
select col1,col2... from tbl1 where name like '%xxx%' order by ... desc limit 0,20;
为了解决翻页,还得进化成
select SQL_CALC_FOUND_ROWS col1,col2... from tbl1 where name like '%xxx%' order by ... desc limit 0,20;
以上简单的一个根据名称查询,根据价格或者xx排序,有很多数据需要翻页的基本需求,已经会出问题了,这种需求的优化,要放在程序设计时处理掉,不应该推送到mysql层面的优化。
以上的SQL可以如下的程序设计优化:
步骤a:  select count(1) as FOUND_ROWS from tbl1 where name like '%xxx%';
步骤b:  select col1,col2... from tbl1 where name like '%xxx%' order by ... desc;
拆成2步可以明显提高sql效率,步骤a没有排序,使得sql避免了一次大量数据的 create_tmp_table和file_sort动作。步骤b免除SQL_CALC_FOUND_ROWS,执行效率也可以大幅度提高。
 
以上提高效率之后,可以做进一步的优化,考虑count(1) as FOUND_ROWS 在每一页的结果都是一样,直接把这个FOUND_ROWS值缓存(memcache,redis随意了啊)起来,避免重复向mysql发送请求。
步骤b可以做进一步拆分,先select id ...,再2次利用id做k-v数据读取。这个好处在于 select .. from .. where ... like .. order by ....,传递到mysql层时,mysql的优化将会便捷很多。否则mysql无法对此建立有效的索引。
 
总结:合计的程序设计很重要,尽量把sql组合到最简单的 k-v,或者单表,少字段的查询SQL上,向mysql请求。
 
3、mysql优化
mysql层面的优化,一般的应用,只需做一些普通的参数调整,索引创建,监控等,能提高数倍的性能。
服务器内存不能太低,8-16G之上最好。
表引擎:很少更新,查询密集的,用myisam(表锁)。 更新情况多,用innodb(行锁)。临时数据,并且查询频繁的,用内存表。如果表之间有join的,两表用同一种引擎。图省事的处理方法是全部用innodb。
index索引:是所有优化的根源,可以用 show full processlist,或者在my.cnf中录下slow query,看到当前的慢sql语句,拿出来用explain xxx 分析一遍,(建议用mysql workbench,有visual模式,可以到具体的解析过程),针对性做 index优化,尽量做到无tmp table,filesort,range all ,等耗时耗力的执行。
一些常见影响性能的参数配置 /etc/my.cnf,这些一定要改,20%的参数提升80%性能!
skip-name-resolve   去除反向域名解析
thread_concurrency  根据cpu的内核数量,调整
max_connections   没有连接池的情况下,这个参数别太小
innodb_buffer_pool_size  结果缓存空间,能大则大
wait_timeout          小一点吧,在程序员忘记释放连接,又有高并发时,就知道用处了
table_open_cache    1024? 2048?反正大一点吧,根据系统状态调整
slow_query_log      不要忘记对慢查询日志记录,解决问题
 
还有一些其它参数的优化,可以使用命令 show status ...查看优化,推荐使用工具 phpmyadmin观察系统状态变量,其中状态不正常的地方,它会用红色标记出来,根据不同的情况,分别做参数调整优化。
 
只要做到以上几点优化,可以轻松应对百万级别的数据。
 
一些复杂的优化方法,比如一主多从,双主从,MHA,FABRIC,CLUSTER,中间慢查询转移到MONGO,redis,lucence减负等方法,只有在以上手段都用过,还是没有效果的情况下,才考虑用更复杂的优化方法。
 

匿名评论

学途最新培训新闻