Mysql SQL optimization tracking to see how to optimize and decide which index to use or not to use

background

When using the index field to filter data, the explain query statement found that MySQL did not use the index, which raised doubts, so I decided to investigate why the index was not used, but the full table scan
The result of the cause investigation is that when the amount of data you want to query is continuous and accounts for more than one-fifth of the entire table, then the index will not be used, because sequential reading is faster. This one-fifth is not absolute, but An experience value, in fact, still needs to compare the time-consuming calculation of running this index with the time-consuming of not using this index during SQL optimization, and the fastest way to query data will be used.
The reason is that I probably know it, but I am still not convinced enough. I want to take a closer look at how much time it takes, and finally judge whether to use the index or not, so we use the optimizer to track and take a look.

test

  1. Link to the Mysql database and select the database
  2. Set up record trace
SET optimizer_trace="enabled=on";
  1. Execute the SQL statement to be analyzed
select * from PKSessionHistory where sessionCreateTime >= '2022-11-28 16:00:00'
  1. Query optimization track record
 select * from information_schema.optimizer_trace;

search result

It is recommended to use json tools to view json, such as the json plugin in utools

{
    "steps": [
        {
            "join_preparation": {
                "select#": 1,
                "steps": [
                    {
                        "expanded_query": "/* select#1 */ select `CohabitPKSessionHistory`.`id` AS `id`,`CohabitPKSessionHistory`.`pkSessionId` AS `pkSessionId`,`CohabitPKSessionHistory`.`cohabitSessionId` AS `cohabitSessionId`,`CohabitPKSessionHistory`.`userId` AS `userId`,`CohabitPKSessionHistory`.`objId` AS `objId`,`CohabitPKSessionHistory`.`createTime` AS `createTime`,`CohabitPKSessionHistory`.`ext` AS `ext`,`CohabitPKSessionHistory`.`userScore` AS `userScore`,`CohabitPKSessionHistory`.`objScore` AS `objScore`,`CohabitPKSessionHistory`.`sessionCreateTime` AS `sessionCreateTime`,`CohabitPKSessionHistory`.`sessionEndTime` AS `sessionEndTime`,`CohabitPKSessionHistory`.`expectedSessionEndTime` AS `expectedSessionEndTime`,`CohabitPKSessionHistory`.`expectedPKEndTime` AS `expectedPKEndTime`,`CohabitPKSessionHistory`.`endSource` AS `endSource`,`CohabitPKSessionHistory`.`totalScore` AS `totalScore`,`CohabitPKSessionHistory`.`cohabitType` AS `cohabitType`,`CohabitPKSessionHistory`.`winnerId` AS `winnerId`,`CohabitPKSessionHistory`.`loserId` AS `loserId`,`CohabitPKSessionHistory`.`userRoseScore` AS `userRoseScore`,`CohabitPKSessionHistory`.`objRoseScore` AS `objRoseScore` from `CohabitPKSessionHistory` where (`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                    }
                ]
            }
        },
        {
            "join_optimization": {
                "select#": 1,
                "steps": [
                    {
                        "condition_processing": {
                            "condition": "WHERE",
                            "original_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                            "steps": [
                                {
                                    "transformation": "equality_propagation",
                                    "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns": {}
                    },
                    {
                        "table_dependencies": [
                            {
                                "table": "`CohabitPKSessionHistory`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                        "ref_optimizer_key_uses": []
                    },
                    {
                        "rows_estimation": [
                            {
                                "table": "`CohabitPKSessionHistory`",
                                "range_analysis": {
                                    "table_scan": {
                                        "rows": 2084,
                                        "cost": 433.9
                                    },
                                    "potential_range_indexes": [
                                        {
                                            "index": "PRIMARY",
                                            "usable": false,
                                            "cause": "not_applicable"
                                        },
                                        {
                                            "index": "idx_sessionCreateTime",
                                            "usable": true,
                                            "key_parts": [
                                                "sessionCreateTime",
                                                "id"
                                            ]
                                        },
                                        {
                                            "index": "idx_sessionCreateTime_totalScore",
                                            "usable": true,
                                            "key_parts": [
                                                "sessionCreateTime",
                                                "totalScore",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "setup_range_conditions": [],
                                    "group_index_range": {
                                        "chosen": false,
                                        "cause": "not_group_by_or_distinct"
                                    },
                                    "analyzing_range_alternatives": {
                                        "range_scan_alternatives": [
                                            {
                                                "index": "idx_sessionCreateTime",
                                                "ranges": [
                                                    "0x99ae790000 <= sessionCreateTime"
                                                ],
                                                "index_dives_for_eq_ranges": true,
                                                "rowid_ordered": false,
                                                "using_mrr": false,
                                                "index_only": false,
                                                "rows": 182,
                                                "cost": 219.41,
                                                "chosen": true
                                            },
                                            {
                                                "index": "idx_sessionCreateTime_totalScore",
                                                "ranges": [
                                                    "0x99ae790000 <= sessionCreateTime"
                                                ],
                                                "index_dives_for_eq_ranges": true,
                                                "rowid_ordered": false,
                                                "using_mrr": false,
                                                "index_only": false,
                                                "rows": 182,
                                                "cost": 219.41,
                                                "chosen": false,
                                                "cause": "cost"
                                            }
                                        ],
                                        "analyzing_roworder_intersect": {
                                            "usable": false,
                                            "cause": "too_few_roworder_scans"
                                        }
                                    },
                                    "chosen_range_access_summary": {
                                        "range_access_plan": {
                                            "type": "range_scan",
                                            "index": "idx_sessionCreateTime",
                                            "rows": 182,
                                            "ranges": [
                                                "0x99ae790000 <= sessionCreateTime"
                                            ]
                                        },
                                        "rows_for_plan": 182,
                                        "cost_for_plan": 219.41,
                                        "chosen": true
                                    }
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [],
                                "table": "`CohabitPKSessionHistory`",
                                "best_access_path": {
                                    "considered_access_paths": [
                                        {
                                            "rows_to_scan": 182,
                                            "access_type": "range",
                                            "range_details": {
                                                "used_index": "idx_sessionCreateTime"
                                            },
                                            "resulting_rows": 182,
                                            "cost": 255.81,
                                            "chosen": true
                                        }
                                    ]
                                },
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 182,
                                "cost_for_plan": 255.81,
                                "chosen": true
                            }
                        ]
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                            "attached_conditions_computation": [],
                            "attached_conditions_summary": [
                                {
                                    "table": "`CohabitPKSessionHistory`",
                                    "attached": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                }
                            ]
                        }
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`CohabitPKSessionHistory`",
                                "pushed_index_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                                "table_condition_attached": null
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_execution": {
                "select#": 1,
                "steps": []
            }
        }
    ]
}

From the record of the optimizer, the time-consuming of our two indexes is 219, and the time-consuming of the full table scan is 419, so the first index is selected. We can explain to see if it will be the same result, the first index is selected. In addition, I wrote using index condition, which uses index pushdown. If you don’t know it, you can refer to related blogs to see index pushdown. It mainly pushes down the filtering to be done by the mysql service layer to the storage engine filtering, reducing the gap between the two layers. The amount of data transferred reduces IO.

mysql> explain select * from CohabitPKSessionHistory where sessionCreateTime >= '2022-11-28 16:00:00';
+----+-------------+-------------------------+------------+-------+--------------------------------------------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table                   | partitions | type  | possible_keys                                          | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------------------+------------+-------+--------------------------------------------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | CohabitPKSessionHistory | NULL       | range | idx_sessionCreateTime,idx_sessionCreateTime_totalScore | idx_sessionCreateTime | 5       | NULL |  183 |   100.00 | Using index condition |
+----+-------------+-------------------------+------------+-------+--------------------------------------------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.38 sec)

Since the data volume of the test table is currently only 2,000 rows, we create a copy table, leave four fields, insert a large amount of data first, then build an index, and then do an experiment~

  1. build table

Do not build the index first, wait until the data is inserted before building the index, because the index needs to be changed during the data insertion process, and IO is required

create table PkSessionHistoryCopy(
`id` int(20) unsigned NOT NULL AUTO_INCREMENT primary key COMMENT 'self-increment ID',
 `userId` int(11) NOT NULL COMMENT 'user ID,Active party',
  `objId` int(11) NOT NULL COMMENT 'user ID,passive party',
 `sessionCreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'PK Starting time '
);
  1. insert data

I first copy all the data from the table with 2,000 rows just now, and then copy myself, the data will continue to double

insert into PkSessionHistoryCopy (userId,objId,sessionCreateTime) select userId,objId,sessionCreateTime from CohabitPKSessionHistory 

The amount of data below is doubled every time I execute it, and I have doubled it to 2.5 million rows of data so far

insert into PkSessionHistoryCopy (userId,objId,sessionCreateTime) select userId,objId,sessionCreateTime from PkSessionHistoryCopy
  1. build index
alter table PkSessionHistoryCopy add index idx_time (sessionCreateTime)
  1. Look at the amount of data
select * from PkSessionHistoryCopy order by id desc limit 1
iduserIdobjIdsessionCreateTime
251082352089016520891772022-12-05 16:06:59
  1. To start the experiment, let's first check the execution plan based on the index field
explain select * from PkSessionHistoryCopy where sessionCreateTime >= '2020-01-01 00:00:00';
mysql> explain select * from PkSessionHistoryCopy where sessionCreateTime >= '2020-01-01 00:00:00';
+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | PkSessionHistoryCopy | NULL       | ALL  | idx_time      | NULL | NULL    | NULL | 2264381 |    50.00 | Using where |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.04 sec)

We can see that the results show that the index will not be taken. Why, let's look at the optimizer trace

select * from information_schema.optimizer_trace;
{
    "steps": [
        {
            "join_preparation": {
                "select#": 1,
                "steps": [
                    {
                        "expanded_query": "/* select#1 */ select `PkSessionHistoryCopy`.`id` AS `id`,`PkSessionHistoryCopy`.`userId` AS `userId`,`PkSessionHistoryCopy`.`objId` AS `objId`,`PkSessionHistoryCopy`.`sessionCreateTime` AS `sessionCreateTime` from `PkSessionHistoryCopy` where (`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                    }
                ]
            }
        },
        {
            "join_optimization": {
                "select#": 1,
                "steps": [
                    {
                        "condition_processing": {
                            "condition": "WHERE",
                            "original_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')",
                            "steps": [
                                {
                                    "transformation": "equality_propagation",
                                    "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns": {}
                    },
                    {
                        "table_dependencies": [
                            {
                                "table": "`PkSessionHistoryCopy`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                        "ref_optimizer_key_uses": []
                    },
                    {
                        "rows_estimation": [
                            {
                                "table": "`PkSessionHistoryCopy`",
                                "range_analysis": {
                                    "table_scan": {
                                        "rows": 2264381,
                                        "cost": 458228
                                    },
                                    "potential_range_indexes": [
                                        {
                                            "index": "PRIMARY",
                                            "usable": false,
                                            "cause": "not_applicable"
                                        },
                                        {
                                            "index": "idx_time",
                                            "usable": true,
                                            "key_parts": [
                                                "sessionCreateTime",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "setup_range_conditions": [],
                                    "group_index_range": {
                                        "chosen": false,
                                        "cause": "not_group_by_or_distinct"
                                    },
                                    "analyzing_range_alternatives": {
                                        "range_scan_alternatives": [
                                            {
                                                "index": "idx_time",
                                                "ranges": [
                                                    "0x99a5420000 <= sessionCreateTime"
                                                ],
                                                "index_dives_for_eq_ranges": true,
                                                "rowid_ordered": false,
                                                "using_mrr": false,
                                                "index_only": false,
                                                "rows": 1132190,
                                                "cost": 1.36e6,
                                                "chosen": false,
                                                "cause": "cost"
                                            }
                                        ],
                                        "analyzing_roworder_intersect": {
                                            "usable": false,
                                            "cause": "too_few_roworder_scans"
                                        }
                                    }
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [],
                                "table": "`PkSessionHistoryCopy`",
                                "best_access_path": {
                                    "considered_access_paths": [
                                        {
                                            "rows_to_scan": 2264381,
                                            "access_type": "scan",
                                            "resulting_rows": 1.13e6,
                                            "cost": 458226,
                                            "chosen": true
                                        }
                                    ]
                                },
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 1.13e6,
                                "cost_for_plan": 458226,
                                "chosen": true
                            }
                        ]
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')",
                            "attached_conditions_computation": [],
                            "attached_conditions_summary": [
                                {
                                    "table": "`PkSessionHistoryCopy`",
                                    "attached": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                }
                            ]
                        }
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`PkSessionHistoryCopy`"
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_explain": {
                "select#": 1,
                "steps": []
            }
        }
    ]
}

We can see that the cost of the full table scan is 458228, and 2264381 rows are scanned

Looking at the time-consuming situation of our indexing, the number of rows scanned is 1132190, and the time-consuming is 1.36e6, which is 1360000. Is it much more than 458228, so we can see that it says chosen:false, which means to choose: No, that is Do not select this index. cause:cost The reason is that it consumes more time

When the amount of data is large, it is more efficient to use the index

explain select * from PkSessionHistoryCopy where sessionCreateTime >= '2022-11-29 00:00:00';
+----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
| id | select_type | table                | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | PkSessionHistoryCopy | NULL       | range | idx_time      | idx_time | 5       | NULL | 283980 |   100.00 | Using index condition |
+----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.04 sec)
mysql> select count(*) from PkSessionHistoryCopy where sessionCreateTime >= '2022-11-29 00:00:00';
+----------+
| count(*) |
+----------+
|   145408 |
+----------+
1 row in set (0.06 sec)

This date is the day I tested it out, and then we looked at the proportion and found that it is 6%, so the statement on the Internet that is 20% or more than 1/5 may not be accurate, the actual operation is still You have to explain first to calculate the time-consuming of the two and see if you will choose an index.
There is another way to allow him to use the index efficiently, which is to add a limit to limit the number of data items to be queried, so that he can directly use the index to find the data of those pages, which is more efficient than scanning the entire table. We can also see from this experiment , it’s not that if you build an index and use the index field to filter, you must go to the index. It will still calculate the time-consuming of each method during optimization and choose the best one. In addition, if you use covering index and limit to reduce the amount of data, it can also be optimized. of speed.

Tags: Java Database MySQL SQL

Posted by Xonnie316 on Tue, 06 Dec 2022 11:57:13 +0300