标签归档:MySQL

GROUP BY另类优化技巧

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

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

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

提示:PPT的内容有限,建议直接观看视频,效果更佳!

1、资料发布

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

2、优惠资讯

  • SQL开发与优化课程:郑老师主讲《SQL开发与优化》课程,助力DBA、开发工程师等加薪升职!首期课程预计5月初开班,目前筹备期特享原价直减1200元超低优惠折特惠,仅需3000元,机会不容错过哦!
  • MySQL DBA实战班、优化提升班:持续招生中,第十一期课程于4月24日开班,双班报名更优惠;
  • Python运维开发班:从零基础入学,结业可达中级Python工程师水平,随到随学,持续招生中扫码加入QQ群 579036588 撩各位助教妹子获得最新课程信息。

3、你问我答

  • group by的效率和distinct与order by的效率比较哪个更好 ?

答:没有区别,因为没有 limit 关键字,所以两个都是进行全局扫描 。

  • 老师请问如何尽快学好SQL 优化方法论呢 ?

答:报班学习,因为有系统课程+学习氛围+讨论,能有效提升学习的效率。

  •  推荐理解MySQL底层运行机制的资料

答:叶老师是国内MySQL圈里权威人物,他的课程值得信赖。

  • select a ,b from tab where c=2 group by a 怎么优化

答:首先 这个SQL是有点 问题的 ,原因是这里的b没有聚合函数,不能保证结果的严谨;其次,因为数据分布不明确,无法给予,因为如果c=2选择率特别好,没啥数据,那么只要在c中有索引就行,这样的假设将会不断,所以光凭这个就无法提供优化建议。

  •  order by a is null,a asc这个例子如下,这样比较容易理解:

答:zst01@3306>[employees]>select emp_no , emp_no is null  from t_order order by  emp_no is  null ,emp_no desc ;

+——–+—————-+

| emp_no | emp_no is null |

+——–+—————-+

|  50449 |              0 |

|  49667 |              0 |

|  48317 |              0 |

|  40983 |              0 |

|  31112 |              0 |

|  30970 |              0 |

|  24007 |              0 |

|  22744 |              0 |

|  10004 |              0 |

|   NULL |              1 |

+——–+—————-+

  • SQL开发能力较弱,有没有什么方法可以提高?

答:要综合提升,还是需要系统的学习,并辅以实战操练,报班就可以提供给你这个氛围,能帮助尽快提升SQL开发能力。

优化案例 | CASE WHEN进行SQL改写优化

导读

今天给大家分享一个通过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优化之路

SQL优化思路

不厌其烦地再说一遍SQL优化思路。

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,

同时要关注预计扫描的行数,

以及是否产生了临时表(Using temporary) 或者 

是否需要进行排序(Using filesort),

想办法消除这些情况。

SQL性能瓶颈定位

毫无疑问,想要优化,先看表DDL以及执行计划:

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `hour` char(2) NOT NULL DEFAULT '00',
  `kid` int(4) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `aType` tinyint(2) NOT NULL DEFAULT '0',
  `src` tinyint(2) NOT NULL DEFAULT '1',
  `aid` int(11) NOT NULL DEFAULT '1',
  `acount` int(11) NOT NULL DEFAULT '1',
  `power` decimal(20,2) DEFAULT '0.00',
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `did` (`date`,`hour`,`kid`,`uid`,`aType`,`src`,`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=50486620 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(`date`)
(PARTITION p20170316 VALUES LESS THAN ('2017-03-17') ENGINE = InnoDB,
 PARTITION p20170317 VALUES LESS THAN ('2017-03-18') ENGINE = InnoDB
...

yejr@imysql.com[myDB]> EXPLAIN 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\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170324,p20170325,....all partition
         type: ALL
possible_keys: did
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25005577
     filtered: 15.00
        Extra: Using where; Using temporary; Using filesort

明显的,这个SQL效率非常低,全表扫描没有索引有临时表需要额外排序,什么倒霉催的全赶上了。

优化思考

这个SQL是想统计符合条件的power列总和,虽然 date 列已有索引,但WHERE子句中却对 date 列加了函数,而且还是 date 和 hour 两列的组合条件,那就无法用到这个索引了。

还好,有个聪明伶俐的妹子,突发起想(事实上这位妹子本来就擅长做SQL优化的~),可以用 CASE WHEN 方法来改造下SQL,改成像下面这样的:

select uid,sum(powerup+powerup1) from
(
   select uid,
          case when concat(date,' ',hour) >='2017-03-24 13:00' then power else '0' end as powerup,
          case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1
   from t1
   where date>='2017-03-24' 
   and   date <'2017-03-25'
   and  aType in (1,6,9)
) a  group by uid;

是不是很有才,直接把这个没办法用到索引的条件给用CASE WHEN来改造了。看看新的SQL执行计划:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170324
         type: range
possible_keys: did
          key: idx2_date_addRedType
      key_len: 4
          ref: NULL
         rows: 876375
     filtered: 30.00
        Extra: Using index condition; Using temporary; Using filesort

看看这个SQL的执行代价:

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_read_first         | 1       |
| Handler_read_key           | 1834590 |
| Handler_read_last          | 0       |
| Handler_read_next          | 1834589 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 232276  |
| Handler_read_rnd_next      | 232277  |
+----------------------------+---------+

及其SLOW QUERY LOG记录的信息:

# Query_time: 6.381254  Lock_time: 0.000166  Rows_sent: 232276  Rows_examined: 2299141  Rows_affected: 0
# Bytes_sent: 4237347  Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 4187168
# InnoDB_trx_id: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 9311

看起来还不是太理想啊,虽然不再扫描全表了,但毕竟还是 有临时表 和 额外排序,想办法消除后再对比看下。

有个变化不知道大家注意到没,新的SLOW QUERY LOG记录多了不少信息,这是因为用了Percona分支版本的插件才支持,这个功能确实不错,甚至还能记录Profiling的详细信息,强烈推荐。

我们新建个 uid 列上的索引,看看能除临时表及排序后的代价如何,看看这个的开销会不会更低。

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX idx_uid(uid);
yejr@imysql.com[myDB]> EXPLAIN select uid,sum(powerup+powerup1) from
(
   select uid,
          case when concat(date,' ',hour) >='2017-03-24 13:00' then power else '0' end as powerup,
          case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1
   from t1
   where date>='2017-03-24' 
   and   date <'2017-03-25'
   and  aType in (1,6,9)
) a  group by uid\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: if_date_hour_army_count
   partitions: p20170331,p20170401...
         type: index
possible_keys: did,idx_uid
          key: idx_uid
      key_len: 4
          ref: NULL
         rows: 12701520
     filtered: 15.00
        Extra: Using where

看看添加索引后SQL的执行代价:

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 1834589 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
+----------------------------+---------+

及其SLOW QUERY LOG记录的信息:

# Query_time: 5.772286  Lock_time: 0.000330  Rows_sent: 232276  Rows_examined: 1834589  Rows_affected: 0
# Bytes_sent: 4215071  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 11470

我们注意到,虽然加了 uid 列索引后的SQL扫描的data page更多了,但执行效率其实是更高的因为消除了 临时表 和 额外排序,这从 Handlerread% 的结果中也能看出来,很显然它的顺序I/O更多,随机I/O更少所以虽然需要扫描的 data page 更多,实际上效率却是更快的

后记

再想想这个SQL还有优化空间吗,显然是有的,那就是把数据表重新设计,将 date 和 hour 列整合到一起,这样就不用费劲的拼凑条件并且也能用到索引了。


最后安利下,知数堂培训马上推出 SQL开发优化 课程,由业界资深SQL优化专家郑老师授课。

该课程关键字:MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。

学完本课程,无论您是DBA工程师、运维工程师,还是开发工程师,抑或系统架构师、技术主管,都将大幅增强您的职场实力,加薪50%轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。

本周四晚上郑老师还会再进行一次公开课分享,讲讲GROUP BY的用法及堵门优化技巧。

有兴趣的同学可以扫码加入知数堂QQ群 579036588 关注课程进展。

优化案例 | 分区表场景下的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
/*!50500 PARTITION BY RANGE  COLUMNS(`date`)
(PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB,
 PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB,
 PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,
...

该表上经常发生下面的慢查询:

SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。

更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。

SQL性能瓶颈定位

现在,我们来看下这个SQL的执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: range
possible_keys: date,date_2
          key: date
      key_len: 3
          ref: const
         rows: 9384602
        Extra: Using where

这个执行计划看起来还好,有索引可用,也没临时表,也没filesort。不过,我们也注意到,预计要扫描的行数还是挺多的 rows: 9384602,而且要扫描zheng整个分区的所有数据,难怪效率不高,总是SLOW QUERY。

优化思考

我们注意到这个SQL总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽略 WHERE 子句中的 时间条件呢?

还有,既然去掉了 date 条件,反观表DDL,剩下的条件貌似就没有合适的索引了吧?

所以,我们尝试新建一个索引:

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

然后,把SQL改造成下面这样,再看下执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE 
  `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: const
         rows: 7800
        Extra: Using where

这优化效果,杠杠滴。

事实上,如果不强制指定分区的话,也是可以达到优化效果的:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: NULL
         rows: 7800
        Extra: Using where

后记

绝大多数的SQL通过添加索引、适当调整SQL代码(例如调整驱动表顺序)等简单手法来完成。

多说几句,遇到SQL优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息:

  • 表DDL
  • 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1’ 查看
  • 表索引分布信息,可执行 SHOW INDEX FROM t1 查看
  • 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

最后安利下,知数堂培训马上推出 SQL开发优化 课程,由业界资深SQL优化专家郑老师授课。

该课程关键字:MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。

学完本课程,无论您是DBA工程师、运维工程师,还是开发工程师,抑或系统架构师、技术主管,都将大幅增强您的职场实力,加薪50%轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。

有兴趣的同学可以扫码加入知数堂QQ群 579036588 关注课程进展。

基于MySQL 5.7多源复制及Keepalived搭建三节点高可用架构

导读

本内容摘自知数堂第35期公开课《MySQL 5.7 高可用新玩法》

本次公开课视频请访问  http://pan.baidu.com/s/1mia6MZu

知数堂公开课相关视频请访问  https://ke.qq.com/course/172600

课中涉及到的脚本及配置文件请点击文末 “阅读原文

基本环境准备

使用Centos 6.X 64位系统 MySQL 使用 MySQL-5.7.17-x86_64 版本,去官方下载mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 版本

机器名 操作系统 Ip
node1 centos-6.8 192.168.11.100
node2 centos-6.8 192.168.11.101
node3 centos-6.8 192.168.11.102

三节点集群设置VIP为 192.168.11.110

一般我们建议关闭iptables

[wubx@zhishuedu.com ~]# chkconfig —del iptables
[wubx@zhishuedu.com ~]# /etc/init.d/iptables stop

并且关闭 selinux

[wubx@zhishuedu.com ~]# setenforce 0

并且将配置文件 /etc/sysconfig/selinux 中的下面这行
SELINUX=permissive

更改为

SELINUX=disabled

下载MySQL

[wubx@zhishuedu.com ~]# mkdir /data/Soft
[wubx@zhishuedu.com ~]# cd /data/Soft
[wubx@zhishuedu.com ~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86\_64.tar.gz

MySQL部署约定

二进制文件放置到 /opt/mysql/ 下面对应的目录。
数据文件全部放置到 /data/mysql/ 下面对应的目录。
原始二进制文件下载到 /data/Soft/ 目录下。

MySQL基本安装

以下安装步骤需要在node1, node2, node3上分别执行。

[wubx@zhishuedu.com ~]# mkdir /opt/mysql
[wubx@zhishuedu.com ~]# cd /opt/mysql
[wubx@zhishuedu.com ~]# tar zxvf /data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
[wubx@zhishuedu.com ~]# ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
[wubx@zhishuedu.com ~]# mkdir /data/mysql/mysql3309/{data,logs,tmp} -p
[wubx@zhishuedu.com ~]# groupadd mysql
[wubx@zhishuedu.com ~]# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -M mysql
[wubx@zhishuedu.com ~]# chown -R mysql:mysql /data/mysql/
[wubx@zhishuedu.com ~]# chown -R mysql:mysql /usr/local/mysql
[wubx@zhishuedu.com ~]# cd /usr/local/mysql/
[wubx@zhishuedu.com ~]# ./bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf —initialize
[wubx@zhishuedu.com ~]# cat /data/mysql/mysql3309/data/error.log |grep password
[wubx@zhishuedu.com ~]# /usr/local/mysql/bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf &
[wubx@zhishuedu.com ~]# echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
[wubx@zhishuedu.com ~]# source /etc/profile

[wubx@zhishuedu.com ~]# mysql -S /tmp/mysql3309.sock -uroot -pXX

mysql> grant replication slave,replication client on . to ‘repl’@’%’ identified by ‘repl4slave’;
mysql> grant all privilegs on test.* to ‘wubx’@’%’ identified by ‘wubx’;
mysql> reset master;

每个节点按上面进行,遇到初始化和启动故障请认真阅读 error log 日志文件。

搭建主从结构

node1上设置master

mysql> change master to master_host=’192.168.11.101’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_101_3309’;

mysql> change master to master_host=’192.168.11.102’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_102_3309’;

#确认同步OK
mysql> start slave; 
mysql> show slave status\G

node2上设置master

mysql> change master to master_host=’192.168.11.100’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_100_3309’;

mysql> change master to master_host=’192.168.11.102’,
master_port=3309,master_user=’repl’, 
master_password=’repl4slave’,master_auto_position=1 
for channel ‘192_168_11_102_3309’;

#确认同步OK
mysql> start slave; 
mysql> show slave status\G

node3上设置master

mysql> change master to master_host=’192.168.11.100’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_100_3309’;

mysql> change master to master_host=’192.168.11.101’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’,master_auto_position=1 
for channel ‘192_168_11_101_3309’;

#确认同步OK
mysql> start slave;
mysql> show slave status\G

安装keepalived

node1, node2, node3 上分别安装keepalived。

yum install keepalivled

安装python依赖模块。

yum install MySQL-python.x86_64 yum install python2-filelock.noarch

keepalived配置 配置文件放置在 /etc/keepalived/keepalived.conf,内容如下

vrrp_script vs_mysql_82 {

    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"

    interval 15

}

vrrp_instance VI_82 {

    state backup

    nopreempt

    interface eth1

    virtual_router_id 82

    priority 100

    advert_int 5

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        vs_mysql_82

    }

    notify /etc/keepalived/notify.py

    virtual_ipaddress {

        192.168.11.110

    }

}

在node1, node2, node3分别执行下面命令,启动keepalived。

/etc/init.d/keepalived start

观察每个系统上的 /var/log/messages 中是否有报错等内容。

在client端机器上测试验证当前连接到哪个实例上。

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubx -e “select @@hostname”

可以尝试关闭实例,自行触发keepalived高可用切换,完成一次高可用自动切换。

扫码关注 知数堂 公众号,第一时间关注干货文章

点击“阅读原文”,获取三节点高可用切换脚本。

从MySQL开发规范处看创业

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

导读

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

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

创业观点解读:创业必须紧跟时代潮流,违背或者对抗时代潮流,是很难进行下去的

第二条,每一个Innodb表都必须要有主键

创业观点解读:每一个创业组织,都必须要有自己的核心竞争力,跟风是活不长久的,因为你没办法在客户的心智中占据一个位置

第三条,为了通用,表的字符集都选择utf-8

创业观点解读:在创业的过程中,不要试图自己去创造一些效率工具,就用别人用过的现成工具,因为你的重心在于实用性,而不是创造性

第四条,根据业务设计索引,单表的索引个数最好不要超过5个

创业观点解读:别人在做的事情,未必就是你以后要做的事情。创业应该是基于一个痛点,一个问题,你只有提供产品为用户解决了一个问题,才会有目标消费群体

第五条,尽量使用复合索引,而不是添加新的索引

创业观点解读:创业过程中尽量不要使用”空降兵“,从创业团队中内部挖掘,提升他们的才智和领导力。

第六条,不要在索引列上使用数学运算和函数运算

创业观点解读:创业的目的应该是很简单的,或者为了一个梦想,或者为了解决一个问题,别把实现财务自由、走上人生巅峰、迎娶白富美这些YY的观点加进去,这样的话,你都没法一心一意去创业

第七条,禁止使用select *,要查询数据后面必须紧跟字段值

创业观点解读:创业头脑风暴的时候,不要想着去满足所有人的需求,去解决所有问题,聚集于一个垂直领域并做到行业领先就够了

第八条,注意组合索引的顺序,以便利用索引的最左原则

创业观点解读:创业时,要紧跟着政策走,在中国这个特色社会,低着头创业,会死得很难看

第九条,一张表的字段个数最好不要超过50个

创业观点解读:在创业初具规模时,先不要想着多元化发展,要先扎实做好自己的老本行,多元化发展那是成为行业领导者之后才能想的事情

第十条,禁止在主库上执行sum,count等复杂的统计分析语句(既然做了从库,为什么还要到主库去查呢)

创业观点解读:能自己活下去的,就不要随便去融资,融资不只是股权上的再分配,也有可能是公司控制权的倾斜

后记

唐勇同学的创业心得和MySQL开发规范结合的非常好,我想这些创业观点对大多数的创业者也是很有帮助的。

备注

1、唐勇同学是知数堂的早期学员;

在MySQL 5.7下排查内存泄露和OOM问题全过程

0、导读

我的个人网站后台使用的是MySQL 5.7版本,前段时间经常被oom-kill,借助5.7的新特性,经过一番排查,终于抓到这只鬼。

1、问题现象

我的网站前段时间经常时不时就抽风一下,提示数据库无法连接,提示:

建立数据库连接时出错

本想反正是个人网站,挂就挂了,无所谓啦;也可能是VPS配置太低,访问量一大就容易出问题,忍忍算啦。

后来启荣大师说了一句话:看那木匠做的烂门 😓(⊙﹏⊙)b

于是下决心解决问题,不能再被鄙视啦,作为一个DBA,不能容忍数据库无缘无故挂掉,哪怕是个人VPS也不行 O(∩_∩)O~

2、问题排查

首先,加了个cron任务,每分钟自动检测mysqld进程是否存活,如果没有,就启动之。这也是我们在企业里解决问题的指导思想:尽快找到问题,但在还没确认问题之前,优先保证服务可用性,不管你用啥方法。

接下来,再看MySQL自身的日志,看看能不能找到什么线索。然并卵,啥也没找到,应该是被异常kill的,所以只有mysqld启动过程记录,并没有异常关闭的过程记录。

再排查系统日志,终于看到mysqld进程因为OOM给kill了:图1

可以看到,mysqld进程消耗了最多内存,被oom-killer选中,给干掉了。

既然是OOM,那我们再看下当时系统整体内存消耗情况:图2

是不是有明显的内存泄露迹象?不清楚的同学可以先看下面这篇文章普及下:

好了,现在我们已经基本明确mysqld进程是因为内存泄露,导致消耗大量内存,最终被oom-kill了。

那么,我们如何知道mysqld进程究竟什么原因消耗掉内存的,都用哪里去了呢?还好,MySQL 5.7的P_S(performance_schema的简称)集成了这样的功能,能帮助我们很方便的了解这些信息。因此我们执行下面的SQL,就能找到MySQL里到底谁消耗了更多内存:

yejr@imysql> select event_name,SUM_NUMBER_OF_BYTES_ALLOC  from

memory_summary_global_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

图3

我们注意到 “memory/innodb/mem0mem” 消耗的内存最大。

再执行下面的SQL,查看都有哪些内部线程消耗了更多内存:

yejr@imysql>select event_name, SUM_NUMBER_OF_BYTES_ALLOC from

memory_summary_by_thread_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;

图4

我们注意到,上面的结果中,有很多非MySQL内部后台线程(thread_id 值比较大)用到了 “memory/innodb/mem0mem”,并且内存消耗也比较大。看到这种现象,我的第六感告诉我,应该是并发线程太高或者线程分配不合理所致。因为前端是PHP短连接,不是长连接,不应该有这么多thread,推测是多线程连接或线程池引起的。

于是排查了一圈和线程、连接数相关的参数选项及状态,基本确认应该是开了线程池(thread pool),导致了内存泄露,持续消耗内存,最终mysqld进程消耗过多内存,被系统给oom-kill了。

下面是我的thread pool相关设置:

图5

当我们把线程池功能关闭后,内存泄露的问题也随之消失,mysqld进程再也没有被oom-kill了,看起来确实是解决的问题。

经过几次反复测试,最终观察到以下结论:

  • 同时开着P_S和thread pool会导致发生内存泄露
  • 同时开着P_S和thread pool,不过采用”one-thread-per-connection”模式而非”pool-of-threads”模式(只用到extra port功能),不会发生内存泄露;
  • 只开着P_S,不开thread pool,不会发生内存泄露;
  • 不开P_S,只开thread pool,也不会发生内存泄露;
  • 同时关闭P_S和thread pool,也不会发生内存泄露;

交代下,我的MySQL版本是:

5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

更早之前用官方的5.7.13版本也是有问题的。

3、结论及建议

在前端应用经常有突发短连接或相似场景中,开启线程池对缓解用户连接请求排队有很大帮助,可以避免MySQL连接瞬间被打满、打爆的问题。但线程池也并非适用于全部场景,像本案例遇到的问题就不适合了(我当初开thread pool更多是为了extra port功能)。

上面我们提到,从MySQL 5.7版本开始,新增了很多有用的视图,可以帮助我们进一步了解MySQL内部发生的一些事情。在P_S中新增了下面这几个内存相关的视图:

  • memory_summary_by_account_by_event_name
  • memory_summary_by_host_by_event_name
  • memory_summary_by_thread_by_event_name
  • memory_summary_by_user_by_event_name
  • memory_summary_global_by_event_name

看视图的名字,就知道这可以帮助我们分别从账号(包含授权主机信息)、主机线程用户(不包含授权主机信息)、整体全局等多个角度查看内存消耗统计。

除了内存统计,还有和事务、复制相关的一些视图,并且原来有些视图也进一步增强。

在MySQL 5.7中,还集成了sys schema,关于sys schema大家可以看本文下方的推荐链接。sys schema主要是对Information_schema以及Performance_Schema的视图进一步增强处理,提高结果的可读性。比如,我们可以查看当前实例总消耗的内存,以及内存主要由哪些部分给占用了,也可以透过sys schema来查看:图6

建议大家应该花些时间好好再深入理解下I_S、P_S、sys schema,对我们这些非底层代码的MySQL DBA而言,这些都是很好的辅助手段。

延伸阅读

重装上阵 | 最方便可靠的MySQL my.cnf生成工具

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

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

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

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

改版之后的界面会好看一些了(不过还是比较素 😓)

填写必要的选项值后,直接提交即可直接生成my.cnf配置文件,并且输出下载。下载文件我设定的名字是 my.cnf-5.7.txt,用 .txt 后缀是为方便大家下载到本地后先用文本编辑器打开,确认符合您的预期后再正式使用。

这份配置文件,有以下几个地方建议您关注下:

  • 已默认关闭了query cache,如果您觉得确实有需要,可自行打开;
  • 客户端连接超时我设置600秒,如果您觉得太长或太短,也请自行调整;
  • 默认打开innodb status输出到log中,以及innodb lock输出到log中,如果觉得烦人,可自行关闭;
  • 把binlog-format默认改为row格式,如果不符合您的预期,请自行调整;
  • 把table lock的默认timeout时长(lock_wait_timeout)设置为3600秒,这个值之前的默认值是1天,我觉得太长了;
  • 几乎启用了全部Performance Schema功能,方便后续定位性能问题;
  • 启用了InnoDB Metric的大多数检测模块,方便对InnoDB进行监控;

好了,先介绍到这里,祝大家用的愉快,点击文末“阅读原文”即可直接开始使用,用得不爽的地方尽管在后面给我留言。

MySQL工具推荐 | 基于MySQL binlog的flashback工具

1、前言

相信您应该遇到过因为误操作破坏数据库的问题,比如忘了带WHERE条件的UPDATE、DELETE操作,然后就需要进行传统方式的全量 & 增量恢复。现在,给您介绍一下MySQL中的flashback玩法,也可以做到像Oracle的flashback那样。

目前MySQL的 flashback(又称 闪回)一般是利用binlog完成的,能快速完成恢复且无需停机维护。

第一个实现该功能的是阿里云的 彭立勋,他在MySQL 5.5版本上就已实现,并将其开源及提交给MariaDB,为社区提供了非常优秀的参考模型。

2、闪回原理

本节我们先来介绍一下MySQL binlog flashback的基本工作原理。

MySQL的binlog以event的形式,记录了MySQL中所有的变更情况,利用binlog我们就能够重现所记录的所有操作。

MySQL引入binlog主要有两个用途/目的:一是为了主从复制;二是用于备份恢复后需要重新应用部分binlog,从而达到全备+增备的效果。

MySQL的binlog共有三种可选格式(binlog_format),其各有优缺点:

  • statement,基于SQL语句的模式,一般来说生成的binlog尺寸较小,但是某些不确定性SQL语句或函数在复制过程可能导致数据不一致甚至出错;
  • row,基于数据行的模式,记录的是数据行的完整变化。相对更安全,推荐使用(但通常生成的binlog会比其他两种模式大很多);
  • mixed,混合模式,可以根据情况自动选用statement抑或row模式;这个模式下也可能造成主从数据不一直。它属于MySQL 5.1版本时期的过渡方案。因此,如果你现在还使用mixed的话,那你的过渡时间也太久了……

备注:想要使用binlog flashback工具,需要将binlog_format设置为row才行。

3、工具推荐

项目一:mysqlbinlog_flashback

项目作者:赖亿@58到家

github项目地址:https://github.com/58daojia-dba/mysqlbinlog_flashback

也可在github.com上搜索“mysqlbinlog_flashback

项目介绍:产生在线mysqlbinlog的回滚的sql,现在已经在阿里的rds上,db为utf8字符集的生产环境下使用。其他环境没有在生产环境下使用,请小心。

项目使用反馈:laiyi@daojia.com

项目二:binlog2sql 

项目作者:曹单锋

github项目地址:https://github.com/danfengcao/binlog2sql

也可在github.com上搜索“binlog2sql

项目介绍:从MySQL binlog解析出你要的SQL。根据不同选项,你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

项目反馈:danfengcao.info@gmail.com

应用场景

  • flashback,数据快速回滚;
  • 主从切换后数据不一致的修复;
  • 从binlog生成标准SQL,再自行二次开发;

5、使用方法

两个软件的使用上都比简单,都是在 https://github.com/noplay/python-mysql-replication 项目基础上进行的二次开发。

两个项目中都有详细的使用说明,感谢两位作者细心的整理,我们这里不再进行赘述,请自行到作者的项目上查看,如果对你帮助,请记得给个 star 哟!

6、flashback总结

社区里做这块的工具比较多,不开源的这里不在讨论,开源产品随着时间的前进,作者有可能忙于其它事情,没来的及更新也会失效过期。所以使用中需要有一定自我修订能力,也希望各位使用者也能加入到开源的大家庭中,共同维护这些项目。

这两款工具开发时侧重点不同,所以使用中也需要注意一下:

  • mysqlbinlog_flashback 更便重于阿里云 RDS环境的使用。
  • binlog2sql  便重于通常MySQL的处理。从代码上来看,该项目更简洁一点。

在具体使用中及项目定制中那个更合适,只有使用后,找到适合自已就可以。 这也是开源的魅力。 如果对这两个软件有兴趣深度交流的,也可以加到QQ群: 529671799  (两位作者已在)来交流吧。

优化系列 | DELETE子查询改写优化

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

delete1

几个表的DDL是这样的:

delete2

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

Query OK, 5 rows affected (31.74 sec)

如果我们把DELETE改写成SELECT的话,执行耗时仅是:0秒,来对比看下执行计划:

[yejr@imysql.com]mydb >EXPLAIN select id 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

delete3

可以看到,trade_info 表从的全表扫描(type=ALL)变成了基于主键的等值查询(type=eq_ref),计划扫描数据量也从571万变成了1条,而且还可以避免回表,这2个SQL对比代价相差巨大。

2、优化思路

既然这个SQL把DELETE改成SELECT后执行效率就可以获得很大提升,除此外没特别区别,可能是查询优化器方面有些不足,导致无法直接优化,就得另想办法了。

我们的思路是把基于子查询的DELETE简化改写成多表JOIN后DELETE(一般来说,子查询效率比较低的话,可以考虑改写成JOIN),多表DELETE的语法课参考:https://dev.mysql.com/doc/refman/5.7/en/delete.html#idm140469624466800,例如这样的:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

参照上面的形式,改写之后的SQL变成了下面这样:

DELETE trade_info

FROM

trade_info,

(

SELECT

a.id

FROM

trade_info a

JOIN order_info b ON a.order_id = b.id

JOIN user c ON b.buyer = c.id

WHERE

c.itv_account = ‘90000248’

) t2 where trade_info.id = t2.id;

delete4

可以看到新的SQL执行效率相对就高很多了,不需要再扫描571万条记录,执行耗时只需:0.01秒。

Query OK, 5 rows affected (0.01 sec)

3、其他建议

虽然MySQL 5.6及以上的版本对子查询做了优化,但从本案例的结果来看,在一些情况下还是不如意。

因此,如果发现有些子查询SQL效率比较差的话,可以尝试改写成JOIN形式,看看是否有所提升。此外,也要勇于怀疑查询优化器个别情况下存在不足,想办法绕过这些坑。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达

MySQL安全策略

0、导读

MySQL被运用于越来越多的业务中,在关键业务中对数据安全性的要求也更高,如何保证MySQL的数据安全?

MySQL被运用于越来越多的业务中,在关键业务中对数据安全性的要求也更高,如何保证MySQL的数据安全。

数据安全如果只靠MySQL应用层面显然是不够的,是需要在多个层面来保护的,包括网络、系统、逻辑应用层、数据库层等。

下面是我们可借鉴的一些安全策略。

1、网络、系统层面

在这个层面可以做很多的事情,我们可以把这些安全要求作为新系统安装时的标准要求,放到自动化装机方案中。

  • 把运行MySQL的服务器放在内网中,不要启用公网;
  • 迫不得已启用公网的话,修改sshd端口到10000以上;
  • 设置防火墙策略,只允许信任的服务器连接sshd和MySQL端口;
  • 修改idrac/imm密码,设置GRUB密码;
  • 设置密码安全策略,比如要求 PASS_MIN_LEN 不低于8位,其实最好是直接用一个复杂密码做MD5之后再作为正式密码,32位长度的安全程度够高吧;
  • 将操作日志记入syslog并且发送到远程log server上,坚决不能只存储在本地;
  • 除了必须的账号,其他的都设为无登入权限;
  • 尽量把运行MySQL的服务器独立出来,不要和web server、app server放一起。必须放一起的话,也要设置好权限分离,不允许web server、app server进程的属主有直接访问MySQL datadir的权限;
  • 禁用web server层的autoindex配置;
  • 可能的话,采用https代替http;
  • 关键应用保持更新,避免老版本的漏洞风险;
  • 设置nginx、php等应用服务的安全策略,禁用危险函数等;
  • 可以考虑购买运营商提供的一些安全防护、扫描器等产品;
  • 坚决杜绝二逼行为,把关键配置文件上传到公共网络上(如把公司项目代码放在github上作为个人项目,内含内网账号密码信息)。

2、逻辑应用层

在这个层面,等多的是依赖运营及开发人员的安全意识,很多本可以避免的低级安全漏洞完全可以在这个层面处理掉,比如下面提到的XSS、CSRF、SQL注入等漏洞。

  • 尽量不要在公网上使用开源的cms、blog、论坛等系统,除非做过代码安全审计,或者事先做好安全策略。这类系统一般都是黑客重点研究对象,很容易被搞;
  • 在web server层,可以用一些安全模块,比如nginx的WAF模块;
  • 在app server层,可以做好代码安全审计、安全扫描,防止XSS攻击、CSRF攻击、SQL注入、文件上传攻击、绕过cookie检测等安全漏洞;
  • 应用程序中涉及账号密码的地方例如JDBC连接串配置,尽量把明文密码采用加密方式存储,再利用内部私有的解密工具进行反解密后再使用。或者可以让应用程序先用中间账号连接proxy层,再由proxy连接MySQL,避免应用层直连MySQL;
  • 应用层启用关键日志记录,例如交易日志,方便后续对账什么的。

3、MySQL数据库层

前面几层如果都做的不够安全的话,在这层也几乎是岌岌可危了。但我们依然可以做些事情的。

  • 启用 safe-update 选项,避免没有 WHERE 条件的全表数据被修改;
  • 将 binlog 的保存周期加长,便于后续的审计、审查;
  • 应用账号只赋予SELECT、UPDATE、INSERT权限,取消DELETE权限。把需要DELETE权限的逻辑改成用UPDATE实现,避免被物理删除;
  • 需要真正删除时,交由DBA先备份后再物理删除;
  • 可以采用Percona的SQL审计插件,据说还有macfee的插件;
  • 还可以采用触发器来做一些辅助功能,比如防止黑客恶意篡改数据。

4、后记

数据安全可以做的事情很多,本文也只是罗列了一些比较简单可快速实施的方案。每个企业应有自己的安全策略规范,每一位参与者都应该心怀敬畏,努力遵守这些必要的规范,不使信息安全成为空谈。

真正的数据安全,是靠所有人的意识安全作为支撑的,没有这个意识靠机制、制度、工具都是不靠谱。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠