Concept one, the optional cardinality of data, which is often referred to as cardinal number.
Before generating various execution plans, the query optimizer must obtain relevant data from statistical information to estimate the number of records involved in each operation. This relevant data is the cardinality. Simply put, it is the unique value distribution state of each value in each field.
For example, table t 1 has 100 rows, one of which is f 1. The number of unique values in f 1 can be 100, 1, and of course it can be any number between 1 and 100. The only value here is the optional cardinality of the column.
Then seeing this, we can understand why it is necessary to build indexes on fields with high cardinality, while those with low cardinality are not as fast as full table scanning. Of course, this is only one aspect, and further discussion is beyond my scope.
Concept two, about the use of suggestion.
Here I will talk about what is a hint and when to use it.
HINT is simply to manually assist MySQL optimizer in some specific scenarios, so that she can generate the optimal execution plan. Generally speaking, the execution plan of the optimizer is optimal, but in some specific scenarios, the execution plan may not be optimal.
For example, table t 1 has undergone many frequent updating operations (updating, deleting and inserting), and the cardinality is already very inaccurate. At this point, an SQL has just been executed, so the execution plan of this SQL may not be optimal. Why is it possible?
Let's take a look at the concrete demonstration.
For example, the following two SQL,
A:
Select * from t 1 where f1= 20;
B:
Select * from t 1 where f1= 30;
If the value of f 1 is only frequently updated to 30, it does not reach the critical value of MySQL automatically updating the base value, or the user has set a manual update, or the user has reduced the number of sample pages. Then both statements may not be accurate.
By the way, MySQL provides methods to update the base values of tables automatically and manually. Due to the limited space, you can refer to the manual if necessary.
Back to the topic, MySQL 8.0 brings several tips, so I will give an example of index_merge today.
Sample table structure:
mysql & gtdesc t 1； +-+-+-+-+| field? | Type | Null | Key | Default | Extra？ |+-+--+-+-+| id | int( 1 1)？ | No? | PRI | NULL | auto _ increment | | rank 1？ | int( 1 1)？ | Really? | MUL | NULL | || rank2？ | int( 1 1)？ | Really? | MUL | NULL | || log_time？ | Date and Time | Yes? | mul | null |||| prefix _ uid | varchar (100) | Yes? | | NULL | || desc 1？ | Wen | Yes? | | NULL | || rank3？ | int( 1 1)？ | Really? | MUL | NULL | |+-+-+-+-+7 lines in the set (0.00 seconds)
Number of table records:
Mysql & gt select count (*) from t 1; +-+| Count (*) |+-| 32768 |+-+65438+0 row in the set (0.0 1 sec)
Here we have two classic SQL:
SQL C:
Select * from t 1 where rank 1 = 1 or rank2 = 2 or rank3 = 2;
SQL D:
Select * from t 1 where rank 1 = 100? And rank2 = 100? Rank 3 =100;
Table t 1 actually has three auxiliary indexes: Rank 1, Rank2 and Rank3.
Then let's look at the query plan of SQL C.
Obviously, no index is used, the number of rows scanned is 32034, and the overhead is 3243.65.
Mysql & gt explain? format=json select * from t 1？ Where rank 1 = 1 or rank2 = 2 or rank3 = 2 \ g * * * * * * * * * * * * * * * * * * * *1. Line * * * * * * * * * * * * * * * * * * * * * * * Explanation: {? " query _ block ":{ " select _ id ": 1，" cost_info": {？ " query_cost": "3243.65" }，" table": {？ " table_name": "t 1 "，" access_type": "ALL "，？ " possible _ keys ":[" idx _ rank 1 "，" idx_rank2 "，" idx_rank3 "？ ], ? " rows_examined_per_scan": 32034，？ " rows _ produced _ per _ join ": 1 15，？ "Filter": "0.36",? " cost _ info ":{ " read _ cost ":" 3232.07 "，" eval_cost": " 1 1.58 "，" prefix_cost": "3243.65 "，" data_read_per_join": "49K "？ }, ? " used_columns": [ "id "，" rank 1 "，" rank2 "，" log_time "，" prefix_uid "，" desc 1 "，" rank3 "？ ], ? " attached _ condition ":"(` ytt `.` t 1 `.` rank 1 `= 1 ` .)or(` ytt `.` t 1 `.` rank 3 ` = 2))" }？ 1 line in}} set, 1 warning (0.00 seconds)
Let's add hints to the same query and look at the query plan again.
At this time, use index_merge and union union, and use three columns. The number of lines scanned is 1 103, and the cost is 44 1.09, which is obviously several times faster than before.
Mysql & gt explain? format = JSON select/*+index _ merge(t 1)*/* from t 1？ Where rank 1 = 1 or rank2 = 2 or rank3 = 2 \ g * * * * * * * * * * * * * * * * * * * *1. Line * * * * * * * * * * * * * * * * * * * * * * * Explanation: {? " query _ block ":{ " select _ id ": 1，" cost_info": {？ " query_cost": "44 1.09" }，" table": {？ " table_name": "t 1 "，" access_type": "index_merge "，" possible _ keys ":[" idx _ rank 1 "，" idx_rank2 "，" idx_rank3 "？ ], ? " key": "union(idx_rank 1，idx_rank2，idx_rank3)"，？ " key_length": "5，5，5 "，" rows _ examined _ per _ scan ": 1 103，？ " rows _ produced _ per _ join ": 1 103，？ "Filtered": "100.00"? " cost _ info ":{ " read _ cost ":" 330.79 "，" eval_cost": " 1 10.30 "，" prefix_cost": "44 1.09 "，" data_read_per_join": "473K "？ }, ? " used_columns": [ "id "，" rank 1 "，" rank2 "，" log_time "，" prefix_uid "，" desc 1 "，" rank3 "？ ], ? " attached _ condition ":"(` ytt `.` t 1 `.` rank 1 `= 1 ` .)or(` ytt `.` t 1 `.` rank 3 ` = 2))" }？ 1 line in}} set, 1 warning (0.00 seconds)
Let's look at the plan of SQL D again:
No hint,
MySQL & gt explain format = JSON select * from t1where rank 1 = 100, rank2 = 100, rank3 =100 \ g * * * * * * *. Line * * * * * * * * * * * * * * * * * * * * * * * Explanation: {? " query _ block ":{ " select _ id ": 1，" cost_info": {？ " query_cost": "534.34" }，" table": {？ " table_name": "t 1 "，" access_type": "ref "，？ " possible _ keys ":[" idx _ rank 1 "，" idx_rank2 "，" idx_rank3 "？ ], ? " key": "idx_rank 1 "，？ " used_key_parts": [ "rank 1 "？ ], ? " key_length": "5 "，" ref": [ "const "？ ], ? " rows_examined_per_scan": 555，？ " rows_produced_per_join": 0，？ "Filtered": "0.07", " cost _ info ":{ " read _ cost ":" 478.84 "，" eval_cost": "0.04 "，" prefix_cost": "534.34 "，" data_read_per_join": " 176 "？ }, ? " used_columns": [ "id "，" rank 1 "，" rank2 "，" log_time "，" prefix_uid "，" desc 1 "，" rank3 "？ ], ? "attached _ condition": "(`ytt` .`t/kloc-0 /` .`rank3` =100) and (`ytt` .`t1` .`rank2` =100). 1 line in}} set, 1 warning (0.00 seconds)
With a hint,
MySQL & gt explain format = JSON select/*+index _ merge (t1) */* from t1where rank 1 = 100, rank2 = 100, rank 3 = 100 \ G * * * * * * * * * * * * * * * * * * * * * * * * * 1。 Line * * * * * * * * * * * * * * * * * * * * * * * Explanation: {? " query _ block ":{ " select _ id ": 1，" cost_info": {？ " query_cost": "5.23" }，" table": {？ " table_name": "t 1 "，" access_type": "index_merge "，" possible _ keys ":[" idx _ rank 1 "，" idx_rank2 "，" idx_rank3 "？ ], ? " key ":" intersect(idx _ rank 1，idx_rank2，idx_rank3)"，？ " key_length": "5，5，5 "，" rows _ examined _ per _ scan ": 1，？ " rows _ produced _ per _ join ": 1，？ "Filtered": "100.00"? " cost _ info ":{ " read _ cost ":" 5. 13 "，" eval_cost": "0. 10 "，" prefix_cost": "5.23 "，" data_read_per_join": "440 "？ }, ? " used_columns": [ "id "，" rank 1 "，" rank2 "，" log_time "，" prefix_uid "，" desc 1 "，" rank3 "？ ], ? " attached _ condition ":"(` ytt `.` t 1 `.` rank 3 ` = 100)and(` ytt `.` t 1 `.` rank 1 `= 100))" }？ 1 line in}} set, 1 warning (0.00 seconds)
Comparing the above two, the cost of prompting is 100 times less than that of not prompting.
In a word, the base value of the table affects this query plan. If the value is not updated normally, you need to add a prompt manually. I believe that the future MySQL version will bring more tips.