mySQL优化 my.ini 配置说明

【服务器】  【mysql】  【innodb】  【性能】  【线程】  【配置】  【mysql优化】  【操作系统】  【索引】   …

分享嘉宾：知数堂〖SQL开发优化班〗讲师郑松华，韩国Infobridge的SQL优化专家&7年SQL开发和调优经验&资深数据库工程师。

本次主题《GROUP BY另类优化技巧》，主要内容是从 GROUP BY、ORDER BY的基础语法到内部算法，最后到实际应用，对需要开发含有复杂的排序功能的同学们更是提供了思路。

内容干货满满，实用性强，小伙伴们快来围观吧！

提示：PPT的内容有限，建议直接观看视频，效果更佳！

1、资料发布

本次公开课的PPT、视频以及课中提到的相关学习资料均已上传到百度云盘，链接: https://pan.baidu.com/s/1bOau9w，欢迎转存及转发。

2、优惠资讯

• …

导读

今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例

待优化场景

发现SLOW QUERY LOG中有下面这样一条记录：

...
# Query_time: 59.503827  Lock_time: 0.000198  Rows_sent: 641227  Rows_examined: 13442472  Rows_affected: 0
...
select uid,sum(power) powerup from t1 where 
date>='2017-03-31' and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))<1492174801  and 
aType in (1,6,9) group by uid;

实话说，看到这个SQL我也忍不住想骂人啊，究竟是哪个脑残的XX狗设计的？

竟然把日期时间中的 date 和 hour 给独立出来成两列，查询时再合并成一个新的条件，简直无力吐槽。

吐槽归吐槽，该干活还得干活，谁让咱是DBA呢，SQL优化是咱的拿手好戏不是嘛~

SQL优化之路 …

导读

有个表做了分区，每天一个分区。

该表上有个查询，经常只查询表中某一天数据，但每次都几乎要扫描整个分区的所有数据，有什么办法进行优化吗？

待优化场景

有一个大表，每天产生的数据量约100万，所以就采用表分区方案，每天一个分区。

下面是该表的DDL：

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `kid` int(11) DEFAULT '0',
  `uid` int(11) NOT NULL,
  `iid` int(11) DEFAULT '0',
  `icnt` int(8) DEFAULT '0',
  `tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `countp` smallint(11) DEFAULT '1',
  `isr` int(2) NOT NULL DEFAULT '0',
  `clv` int(5) NOT NULL DEFAULT '1'，
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `date` (`date`,`uid`,`iid`),
  KEY `date_2` (`date`,`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4 …

作者：唐勇，深圳市环球易购，MySQL DBA。个人爱好：看书、跑步、看电影、旅行、倒腾文字、NBA

导读

MySQL是时下热度仅次于Oracle的关系型数据库，因为便捷高效的特点风靡整个DB行业。而创业呢，政策层面的“双创”让多少热血青年跳进了创业这个”火坑“，从去年的内容创业到共享经济，从罗辑思维、吴晓波频道到摩拜单车、ofo。只要提及创业，必讲互联网，必讲商业模式、大数据、DAU、变现、人工智能、认知、消费升级、中产崛起。但是这些概念和认知与MySQL的开发规范又有什么关系呢，还真真有关系，并且能联系起来，你不信啊，你自己往下看咯。

首先第一条，表的存储引擎必须选择InnoDB(MyISAM存储引擎已经被时代淘汰了，既然InnoDB能满足99%以上的业务场景，你还有什么理由去返古)

作者：吴炳锡　来源：http://wubx.net/ 联系方式： wubingxi#163.com 转载请注明 […]

关注我网站（http://imysql.com）的亲们应该都知道，有个my.cnf配置文件生成器功能，2008年8月20日开始上线提供服务至今，历经了5.1到5.7多个版本。

当初上线时，本着简单了事、能用就行的原则，只提供了基本的功能，但界面那个丑啊，我有无数次想改版，但也一直懒得动工。老的界面是像下面这样的 … (ノへ￣、)捂脸

今天上班时，我终于再也忍不住了，于是找到了一个简单大方的表单模板直接照搬过来用，感谢下面这位同学的无私分享。

同时顺便把一些功能逻辑也重新整理了下，简单重操了许久没动手的PHP，还好基本上还能玩得转，嘿。

…

0、导读

有个采用子查询的DELETE执行得非常慢，改写成SELECT后执行却很快，最后把这个子查询DELETE改写成JOIN优化过程

1、问题描述

朋友遇到一个怪事，一个用子查询的DELETE，执行效率非常低。把DELETE改成SELECT后执行起来却很快，百思不得其解。

下面就是这个用了子查询的DELETE了：

[yejr@imysql.com]mydb > EXPLAIN delete from trade_info where id in (

select id from (

select a.id from trade_info a, order_info b, user c where

b.buyer = c.id and c.itv_account=’90000248′ and a.order_id = b.id) temp)\G

几个表的DDL是这样的：

上面这个SQL的执行耗时是：31.74秒

Query OK, 5 rows affected (31.74 sec)

…

大多数MySQL规范在网上也都能找得到相关的分享，在这里要分享的是老叶个人认为比较重要的，或者容易被忽视的，以及容易被混淆的一些地方。

1、默认使用InnoDB引擎
【老叶观点】已多次呼吁过了，InnoDB适用于几乎99%的MySQL应用场景，而且在MySQL 5.7的系统表都改成InnoDB了，还有什么理由再死守MyISAM呢。

此外，频繁读写的InnoDB表，一定要使用具有自增/顺序特征的整型作为显式主键。

【参考】：[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键。

2、字符集选择utf-8
…

本文整理了一些MySQL的通用优化方法，做个简单的总结分享，旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作，至于具体的SQL优化，大部分通过加适当的索引即可达到效果，更复杂的就需要具体分析了，可以参考本站的一些优化案例或者联系我，下方有我的联系方式。这是下篇。

3、MySQL层相关优化 3.1、关于版本选择

官方版本我们称为ORACLE MySQL，这个没什么好说的，相信绝大多数人会选择它。

我个人强烈建议选择Percona分支版本，它是一个相对比较成熟的、优秀的MySQL分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方ORACLE MySQL版本基本完全兼容，并且性能大约有20%以上的提升，因此我优先推荐它，我自己也从2008年一直以它为主。

…