作者归档:叶金荣

关于叶金荣

ORACLE ACE(MySQL),国内最早的MySQL推广者,资深MySQL专家,10余年MySQL经验,擅长MySQL性能优化、架构设计、故障排查

10分钟让你明白MySQL是如何利用索引的

作者:王航威 – fordba.com

来源:http://fordba.com/spend-10-min-to-understand-how-mysql-use-index.html

备注:王航威是知数堂第8期同学,现任职有赞DBA

一、前言

在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。

譬如:

  1. MySQL 在遇到范围查询条件的时候就停止匹配了,那么到底是哪些范围条件?
  2. MySQL 在LIKE进行模糊匹配的时候又是如何利用索引的呢?
  3. MySQL 到底在怎么样的情况下能够利用索引进行排序?

今天,我将会用一个模型,把这些问题都一一解答,让你对MySQL索引的使用不再畏惧


二、知识补充

key_len

EXPLAIN执行计划中有一列 key_len 用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。

在这里 key_len 大小的计算规则是:

  • 一般地,key_len 等于索引列类型字节长度,例如int类型为4 bytes,bigint为8 bytes;
  • 如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90 bytes;
  • 若该列类型定义时允许NULL,其key_len还需要再加 1 bytes;
  • 若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引也被视为动态列类型),其key_len还需要再加 2 bytes;

三、哪些条件能用到索引

首先非常感谢登博,给了我一个很好的启发,我通过他的文章,然后结合自己的理解,制作出了这幅图

乍一看,是不是很晕,不急,我们慢慢来看

图中一共分了三个部分:

  1. Index Key :MySQL是用来确定扫描的数据范围,实际就是可以利用到的MySQL索引部分,体现在Key Length。
  2. Index Filter:MySQL用来确定哪些数据是可以用索引去过滤,在启用ICP后,可以用上索引的部分。
  3. Table Filter:MySQL无法用索引过滤,回表取回行数据后,到server层进行数据过滤。

我们细细展开。

Index Key

Index Key是用来确定MySQL的一个扫描范围,分为上边界和下边界。

MySQL利用=、>=、> 来确定下边界(first key),利用最左原则,首先判断第一个索引键值在where条件中是否存在,如果存在,则判断比较符号,如果为(=,>=)中的一种,加入下边界的界定,然后继续判断下一个索引键,如果存在且是(>),则将该键值加入到下边界的界定,停止匹配下一个索引键;如果不存在,直接停止下边界匹配。

exp:
idx_c1_c2_c3(c1,c2,c3)
where c1>=1 and c2>2 and c3=1
–> first key (c1,c2)
–> c1为 ‘>=’ ,加入下边界界定,继续匹配下一个
–> c2 为 ‘>’,加入下边界界定,停止匹配

上边界(last key)和下边界(first key)类似,首先判断是否是否是(=,<=)中的一种,如果是,加入界定,继续下一个索引键值匹配,如果是(<),加入界定,停止匹配。

exp:
idx_c1_c2_c3(c1,c2,c3)
where c1<=1 and c2=2 and c3<3
–> first key (c1,c2,c3)
–> c1为 ‘<=’,加入上边界界定,继续匹配下一个
–> c2为 ‘=’加入上边界界定,继续匹配下一个
–> c3 为 ‘<‘,加入上边界界定,停止匹配

注:这里简单的记忆是,如果比较符号中包含’=’号,’>=’也是包含’=’,那么该索引键是可以被利用的,可以继续匹配后面的索引键值;如果不存在’=’,也就是’>’,'<‘,这两个,后面的索引键值就无法匹配了。同时,上下边界是不可以混用的,哪个边界能利用索引的的键值多,就是最终能够利用索引键值的个数。

Index Filter

字面理解就是可以用索引去过滤。也就是字段在索引键值中,但是无法用去确定Index Key的部分。

exp:
idex_c1_c2_c3
where c1>=1 and c2<=2 and c3 =1
index key –> c1
index filter–> c2 c3

这里为什么index filter 只是c1呢?因为c2 是用来确定上边界的,但是上边界的c1没有出现(<=,=),而下边界中,c1是>=,c2没有出现,因此index key 只有c1字段。c2,c3 都出现在索引中,被当做index filter。

Table Filter

无法利用索引完成过滤,就只能用table filter。此时引擎层会将行数据返回到server层,然后server层进行table filter。


四、Between 和Like 的处理

那么如果查询中存在between 和like,MySQL是如何进行处理的呢?

Between

where c1 between  'a' and 'b' 等价于 where c1>='a' and c1 <='b',所以进行相应的替换,然后带入上层模型,确定上下边界即可。

Like

首先需要确认的是%不能是最在最左侧,where c1 like '%a' 这样的查询是无法利用索引的,因为索引的匹配需要符合最左前缀原则。

where c1 like 'a%'  其实等价于 where c1>='a' and c1<'b' 大家可以仔细思考下。


五、索引的排序

在数据库中,如果无法利用索引完成排序,随着过滤数据的数据量的上升,排序的成本会越来越大,即使是采用了limit,但是数据库是会选择将结果集进行全部排序,再取排序后的limit 记录,而且MySQL 针对可以用索引完成排序的limit 有优化,更能减少成本。

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index – in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  `c3` int(11) NOT NULL DEFAULT '0',
  `c4` int(11) NOT NULL DEFAULT '0',
  `c5` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_c1_c2_c3` (`c1`,`c2`,`c3`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 select * from t1;
+----+----+----+----+----+----+
| id | c1 | c2 | c3 | c4 | c5 |
+----+----+----+----+----+----+
|  1 |  3 |  3 |  2 |  0 |  0 |
|  2 |  2 |  4 |  5 |  0 |  0 |
|  3 |  3 |  2 |  4 |  0 |  0 |
|  4 |  1 |  3 |  2 |  0 |  0 |
|  5 |  1 |  3 |  3 |  0 |  0 |
|  6 |  2 |  3 |  5 |  0 |  0 |
|  7 |  3 |  2 |  6 |  0 |  0 |
+----+----+----+----+----+----+
7 rows in set (0.00 sec)

 select c1,c2,c3 from t1;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
|  1 |  3 |  2 |
|  1 |  3 |  3 |
|  2 |  3 |  5 |
|  2 |  4 |  5 |
|  3 |  2 |  4 |
|  3 |  2 |  6 |
|  3 |  3 |  2 |
+----+----+----+
7 rows in set (0.00 sec)

存在一张表,c1,c2,c3上面有索引,select c1,c2,c3 from t1; 查询走的是索引全扫描,因此呈现的数据相当于在没有索引的情况下select c1,c2,c3 from t1 order by c1,c2,c3; 的结果。
因此,索引的有序性规则是怎么样的呢?

c1=3 —> c2 有序,c3 无序
c1=3,c2=2 — > c3 有序
c1 in(1,2) —> c2 无序 ,c3 无序

有个小规律,idx_c1_c2_c3,那么如何确定某个字段是有序的呢?c1 在索引的最前面,肯定是有序的,c2在第二个位置,只有在c1 唯一确定一个值的时候,c2才是有序的,如果c1有多个值,那么c2 将不一定有序,同理,c3也是类似


六、小结

针对MySQL索引,我这边只是提到了在单表查询情况下的模型,通过这篇文章,想必大家应该了解到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高可用切换,完成一次高可用自动切换。

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

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

2017.4.6发车 |《MySQL 5.7高可用新玩法》

1、主题

《MySQL 5.7高可用新玩法》

2、嘉宾介绍

吴炳锡 知数堂培训联合创始人,前新媒传信首席DBA,吴炳锡老师具有多年MySQL及系统架构设计及培训教学经验,擅长MySQL大规模运维管理优化、高可用方案、多IDC架构设计,以及企业应用数据库设计等经验。

3、课程简介

MySQL 5.7版本中新增了真正意义上的多线程复制、多源复制、增强半同步复制,以及Group Replication,众多新特性让人眼前一亮。随着这些新特性的日益成熟,在MySQL 5.7上的高可用架构也可以有了新玩法。

在本次分享中,先介绍Group Replicaiton的实现原理,重点介绍MySQL 5.7下利用多源复制实现的高可用架构方案。

内容大纲:

1、MySQL 5.7在复制方面的几个新特性;

2、MySQL 5.7 Group Replication实现原理;

3、利用多源复制,实现新的高可用架构;

4、基于多源复制及Group Replication限制。

分享方

分享时间:2017.4.6(周四) 晚上20:30 – 21:30

分享方式:通过YY语音同步直播,以及在QQ&微信群发送PPT等图文内容

YY频道:53695719(需提前安装YY客户端,支持windows/ios/andriod多平台)

请提前加入知数堂技术交流QQ群:579036588,或扫描识别下面二维码加入。(已经在知数堂一群、二群的无需再加入三群,分享时三群联动)

关于知数堂

“知数堂培训”是由资深MySQL专家叶金荣、吴炳锡联合推出专业优质在线培训课程,主要有MySQL DBA实战优化、Python运维开发和 SQL开发优化三个课程,是业内最有良心、最有品质的培训品牌。

目前MySQL DBA实战优化班以及Python运维开发班均在招生中。学员已超600多人,众多优秀学员在腾讯、淘宝、支付宝、百度、网易、京东、乐视、去哪儿、滴滴、猎豹、58、微博、金山云、聚美、顺丰、德邦、韵达、苏宁、恩墨、沃趣、爱可生、37玩、宝存、人人贷、美的、新东方、平安金融等众多知名公司担任DBA等职位,在获得更好的职业发展机遇同时薪资也得到了大幅提升。

有兴趣的同学请关注 知数堂 官方公众号”izhishuedu” 后发送 “开班”、“招生”、“大纲” 等关键字即可获得课程详细信息。

从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进行监控;

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