mysql sql优化之straight

  • 时间:
  • 浏览:0
  • 来源:彩神官网

mysql>alter table user drop index ix_user_username,add index ix_user_username(username,isfree);

Query OK, 42722 rows affected (0.73 sec)

Records: 42722  Duplicates: 0  Warnings: 0

5.查看执行计划:

mysql> explain  SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  test_log a,USER b

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: index

possible_keys: ix_test_log_userid

key: ix_test_log_userid

key_len: 105

ref: NULL

rows: 388451 Extra: Using index; Using filesort

3.查看索引:

mysql> show index from test_log;

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

| Table        | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

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

| test_log |          0 | ix_test_log_unique_ |            1 | unitid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |

| test_log |          0 | ix_test_log_unique_ |            2 | paperid     | A         |          20 |     NULL | NULL   |      | BTREE      |         |

| test_log |          0 | ix_test_log_unique_ |            3 | qtid        | A         |          20 |     NULL | NULL   |      | BTREE      |         |

| test_log |          0 | ix_test_log_unique_ |            4 | userid      | A         |      500670 |     NULL | NULL   |      | BTREE      |         |

| test_log |          0 | ix_test_log_unique_ |            5 | serial      | A         |      500670 |     NULL | NULL   |      | BTREE      |         |

| test_log |          1 | ix_test_log_unit    |            1 | unitid      | A         |         519 |     NULL | NULL   |      | BTREE      |         |

| test_log |          1 | ix_test_log_unit    |            2 | paperid     | A         |        2023 |     NULL | NULL   |      | BTREE      |         |

| test_log |          1 | ix_test_log_unit    |            3 | qtid        | A         |       16694 |     NULL | NULL   |      | BTREE      |         |

| test_log |          1 | ix_test_log_serial  |            1 | serial      | A         |      133556 |     NULL | NULL   |      | BTREE      |         |

| test_log |          1 | ix_test_log_userid  |            1 | userid      | A         |        5892 |     NULL | NULL   |      | BTREE      |         |

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

6.执行时间仍然须要很长,时间的消耗主要耗费在Using filesort中,参与排序的数据量有38W之多,就说 须要转换驱动表;尝试采用user表做驱动表:使用straight_join强制连接顺序:

mysql> explain  SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  USER b straight_join test_log a

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime)\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: b

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 425006 Extra: Using where; Using temporary; Using filesort

1.用户实例:spxxxxxx的四根sql执行非常的缓慢,sql如下:

73871 | root            | 127.0.0.1:49665     | user_app_test   | Query       |     5000 | Sorting result           |

SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  test_log a,USER b

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime)

2.查看执行计划:

mysql> explain  SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  test_log a,USER b

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime);

mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

-> FROM test_log a,USER b

-> WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

-> GROUP BY DATE(practicetime)\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys: ix_test_log_userid

key: NULL

key_len: NULL

ref: NULL

rows: 416782

Extra: Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: b

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 96

ref: user_app_testnew.a.userid

rows: 1

Extra: Using where

2 rows in set (0.00 sec)

7.在分析执行计划的第一步:Using where; Using temporary; Using filesort,user表真是也可不须要采用覆盖索引来处理using where的总出 ,就说 继续调整索引:

mysql> show index from user;

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

| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

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

| user  |          0 | PRIMARY          |            1 | userid      | A         |       43412 |     NULL | NULL   |      | BTREE      |         |

| user  |          0 | ix_user_email    |            1 | email       | A         |       43412 |     NULL | NULL   |      | BTREE      |         |

| user  |          1 | ix_user_username |            1 | username    | A         |         202 |     NULL | NULL   |      | BTREE      |         |

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

3 rows in set (0.01 sec)

8.执行时间降低到了1.43秒:

mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  USER b straight_join test_log a

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime);

。。。。。。。

143 rows in set (1.43 sec)

在oracle中可不须要指定的表连接的hint有就说 :ordered hint 指示oracle按照from关键字后的表顺序来进行连接;leading hint 指示查询优化器使用指定的表作为连接的首表,即驱动表;use_nl hint指示查询优化器使用nested loops土方法连接指定表和什儿 行源,有已经 将强制指定表作为inner表。

在mysql中全部都是之对应的straight_join,可能性mysql只支持nested loops的连接土方法,就说 这里的straight_join同类 oracle中的use_nl hint。mysql优化器在处理多表的关联的已经 ,很有可能性会选折 错误的驱动表进行关联,原困了关联次数的增加,从而使得sql一句话执行变得非常的缓慢,你什儿 已经 须要有经验的DBA进行判断,选折 正确的驱动表,你什儿 已经 straight_join就起了作用了,下面我门歌词 歌词 来看一看使用straight_join进行优化的案例:

mysql>explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  USER b straight_join test_log a

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime);

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: b

type: index

possible_keys: PRIMARY

key: ix_user_username

key_len: 125

ref: NULL

rows: 42466

Extra: Using where; Using index; Using temporary; Using filesort *************************** 2. row ***************************

4.调整索引,A表优化采用覆盖索引:

mysql>alter table test_log drop index ix_test_log_userid,add index ix_test_log_userid(userid,practicetime)

调整后执行稍有效果,有已经 还不明显,还没有找到要害:

SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows

FROM  test_log a,USER b

WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4

GROUP BY DATE(practicetime);

……………….

143 rows in set (1 min 12.62 sec)

猜你喜欢

怎样加入基督教,做一个信徒?

为你推荐:加入基督教都有目的,要信仰耶稣基督,做1个多真正的信徒不不 得救。圣经上叫做【因信得救】。新约《圣经》罗马书10章9节说:你若口里认耶稣为主,心里信神叫他从死里复活

2020-02-27

西方艺术发展至今共分为哪几个阶段

展开删剪可选中一个多或多个下面的关键词,搜索相关资料。也可直接点“搜索资料”搜索整个大问題。下载百度知道APP,抢鲜体验 我来答换一换使用百度知道APP,立即抢鲜体验。你的

2020-02-27

国外名人美国总统三个字

展开全版展开全版收起1条折叠回答可选中有有另三个 或多个下面的关键词,搜索相关资料。也可直接点“搜索资料”搜索整个什么的问题。使用百度知道APP,立即抢鲜体验。你的手机镜头里

2020-02-27

盘它!10篇+阿里云技术经典案例,看完必收藏(下)

讲透Netty底层通讯原理就看哪些干货帖,大数据产品从入门到精通|MVP讲堂盘它!40篇+阿里技术经典案例,就看必收藏下拉加载更多请收下这份关于人工智能的根目录——博客埋点系列

2020-02-26

游客cmvdxudqxtao4的主页

阿里云ET专家,重点在智能语音、人脸识别、图像识别、OCR等...阿里云计算美码师,老码农一枚,唯美食与技术不能辜负,欢迎交流及打扰!文章:12丨粉丝:641500丨话题:1云

2020-02-26