为了账号安全,请及时绑定邮箱和手机立即绑定

【原创】MySQL新旧版本ORDER BY 处理方法

标签:
MySQL


MySQL 的order by 涉及到三个参数:

A. sort_buffer_size 排序缓存。

B. read_rnd_buffer_size 第二次排序缓存。

C. max_length_for_sort_data 带普通列的最大排序约束。

我来简单说下MySQL的排序规则。

假设查询语句select * from tb1 where 1 order by  a ; 字段a没有建立索引;以上三个参数都足够大。

MySQL内部有两种排序规则:

第一种,是普通的排序。这种排序的特点是节省内存,但是最终会对磁盘有一次随机扫描。 大概主要过程如下:

1. 由于没有WHERE条件,所以直接对磁盘进行全表扫描,把字段a以及每行的物理ID(假设为TID)拿出来。然后把所有拿到的记录全部放到sort_buffer_size中进行排序。

2. 根据排好序的TID,从磁盘随机扫描所需要的所有记录,排好序后再次把所有必须的记录放到read_rnd_buffer_size中。

第二种,是冗余排序。这种排序的特点是不需要二次对磁盘进行随机扫描,但是缺点很明显,太浪费内存空间。

跟第一种不同的是,在第一步里拿到的不仅仅是字段a以及TID,而是把所有请求的记录全部拿到后,放到sort_buffer_size中进行排序。这样可以直接从缓存中返回记录给客户端,不用再次从磁盘上获取一次。

从MySQL 5.7 后,对第二种排序进行了打包压缩处理,避免太浪费内存。比如对于varchar(255)来说,实际存储为varchar(3)。那么相比之前的方式节约了好多内存,避免缓存区域不够时,建立磁盘临时表。

以下为简单的演示

mysql> use t_girl;

Database changed

三个参数的具体值:

mysql> select truncate(@@sort_buffer_size/1024/1024,2)||'MB' as 'sort_buffer_size',truncate(@@read_rnd_buffer_size/1024/1024,2)||'MB' as read_rnd_buffer_zie,@@max_length_for_sort_data as max_length_for_sort_data;

+------------------+---------------------+--------------------------+

| sort_buffer_size | read_rnd_buffer_zie | max_length_for_sort_data |

+------------------+---------------------+--------------------------+

| 2.00MB           | 2.00MB              |                     1024 |

+------------------+---------------------+--------------------------+

1 row in set (0.00 sec)

演示表的相关数据:

mysql> select table_name,table_rows,concat(truncate(data_length/1024/1024,2),'MB') as 'table_size' from information_schema.tables where table_name = 't1' and table_schema = 't_girl';

+------------+------------+------------+

| table_name | table_rows | table_size |

+------------+------------+------------+

| t1         |    2092640 | 74.60MB    |

+------------+------------+------------+

1 row in set (0.00 sec)

开启优化器跟踪:

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

Query OK, 0 rows affected (0.00 sec)

从数据字典里面拿到跟踪结果:

mysql> select * from information_schema.optimizer_trace\G

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

                            QUERY: select * from t1 where id < 10 order by id

                            TRACE: {

  "steps": [

    {

      "join_preparation": {

        "select#": 1,

        "steps": [

          {

            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`log_time` AS `log_time` from `t1` where (`t1`.`id` < 10) order by `t1`.`id`"

          }

        ] /* steps */

      } /* join_preparation */

    },

    {

      "join_optimization": {

        "select#": 1,

        "steps": [

          {

            "condition_processing": {

              "condition": "WHERE",

              "original_condition": "(`t1`.`id` < 10)",

              "steps": [

                {

                  "transformation": "equality_propagation",

                  "resulting_condition": "(`t1`.`id` < 10)"

                },

                {

                  "transformation": "constant_propagation",

                  "resulting_condition": "(`t1`.`id` < 10)"

                },

                {

                  "transformation": "trivial_condition_removal",

                  "resulting_condition": "(`t1`.`id` < 10)"

                }

              ] /* steps */

            } /* condition_processing */

          },

          {

            "table_dependencies": [

              {

                "table": "`t1`",

                "row_may_be_null": false,

                "map_bit": 0,

                "depends_on_map_bits": [

                ] /* depends_on_map_bits */

              }

            ] /* table_dependencies */

          },

          {

            "ref_optimizer_key_uses": [

            ] /* ref_optimizer_key_uses */

          },

          {

            "rows_estimation": [

              {

                "table": "`t1`",

                "table_scan": {

                  "rows": 2092640,

                  "cost": 4775

                } /* table_scan */

              }

            ] /* rows_estimation */

          },

          {

            "considered_execution_plans": [

              {

                "plan_prefix": [

                ] /* plan_prefix */,

                "table": "`t1`",

                "best_access_path": {

                  "considered_access_paths": [

                    {

                      "access_type": "scan",

                      "rows": 2.09e6,

                      "cost": 423303,

                      "chosen": true,

                      "use_tmp_table": true

                    }

                  ] /* considered_access_paths */

                } /* best_access_path */,

                "cost_for_plan": 423303,

                "rows_for_plan": 2.09e6,

                "sort_cost": 2.09e6,

                "new_cost_for_plan": 2.52e6,

                "chosen": true

              }

            ] /* considered_execution_plans */

          },

          {

            "attaching_conditions_to_tables": {

              "original_condition": "(`t1`.`id` < 10)",

              "attached_conditions_computation": [

              ] /* attached_conditions_computation */,

              "attached_conditions_summary": [

                {

                  "table": "`t1`",

                  "attached": "(`t1`.`id` < 10)"

                }

              ] /* attached_conditions_summary */

            } /* attaching_conditions_to_tables */

          },

          {

            "clause_processing": {

              "clause": "ORDER BY",

              "original_clause": "`t1`.`id`",

              "items": [

                {

                  "item": "`t1`.`id`"

                }

              ] /* items */,

              "resulting_clause_is_simple": true,

              "resulting_clause": "`t1`.`id`"

            } /* clause_processing */

          },

          {

            "refine_plan": [

              {

                "table": "`t1`",

                "access_type": "table_scan"

              }

            ] /* refine_plan */

          }

        ] /* steps */

      } /* join_optimization */

    },

    {

      "join_execution": {

        "select#": 1,

        "steps": [

          {

            "filesort_information": [

              {

                "direction": "asc",

                "table": "`t1`",

                "field": "id"

              }

            ] /* filesort_information */,

            "filesort_priority_queue_optimization": {

              "usable": false,

              "cause": "not applicable (no LIMIT)"

            } /* filesort_priority_queue_optimization */,

            "filesort_execution": [

            ] /* filesort_execution */,

            "filesort_summary": {

              "rows": 62390,

              "examined_rows": 2097152,

              "number_of_tmp_files": 0,

              "sort_buffer_size": 2097152,

              "sort_mode": "<sort_key, additional_fields>"

            } /* filesort_summary */

          }

        ] /* steps */

      } /* join_execution */

    }

  ] /* steps */

}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

          INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec)

mysql>

其中以上红色部分<sort_key, additional_fields> 表示用了第二种排序规则。

其他的两种<sort_key, rowid> 以及<sort_key, packed_additional_fields>分别代表第一种和后续版本MySQL的提升, 自己体验去吧。

©著作权归作者所有:来自51CTO博客作者david_yeung的原创作品,如需转载,请注明出处,否则将追究法律责任

MySQL排序orderMySQL性能优化


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消