Mysql index test case

Test Conditions for mysql index test: mysql> show create table users \ G; * *************************** 1. row *************************** Table: usersCreate Table: create table 'users' ('id' int (11) not null, 'name' varchar (45) default null, 'c1 'text, 'c2 'int (6) unsigned zerofill default null, primary key ('id'), unique key 'c2 _ UNIQUE '('c2 '), KEY 'idx _ users' ('c1' (10) ENGINE = InnoDB default charset = utf81 row in set (0. 00 sec) ERROR: No query specified mysql> show create table user_action \ G; * *************************** 1. row *************************** Table: user_actionCreate Table: create table 'user _ action' ('user _ id' int (11) not null, 'Action' varchar (45) default null, 'name' varchar (100) default null, primary key ('user _ id'), unique key 'idx _ userid' ('user _ id'), KEY 'idx _ action' ('action '), KEY 'idx _ userac Tion_action_name '('action', 'name' (10) ENGINE = InnoDB default charset = utf81 row in set (0.00 sec) ERROR: no query specified mysql> select * from users; + ---- + -------- + ------ + -------- + | id | name | c1 | c2 | + ---- + -------- + ------ + -------- + | 1 | libk | NULL | 2 | zyfon | NULL | 3 | daodao | NULL | 4 | 3333 | NULL | 000002 | 5 | 444 | NULL | + ---- + -------- + ------ + --- ----- + 5 rows in set (0.00 sec) mysql> select * from user_action; + --------- + -------- + --------------- + | user_id | action | name | + --------- + -------- + --------------- + | 1 | jump | aaaaaaaaaaa | 2 | run | bbbbbbbbbbbbb | 4 | swim | cccccc | 6 | kick | dd | 15 | jump1 | fff | + --------- + -------- + --------------- + 5 rows in set (0.00 sec) mysql> 1. the usage principle of composite index is that the index's leading column must appear in the condition; otherwise, the index mysql> expla will not be used. In select * from user_action where action = 'run '; + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------------------------------- + ------------ + --------- + ------- + ------ + ------------ -+ | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + 1 row in set (0.00 sec) mysql> explain select * from user_action where name = 'dd'; + ---- + ------------- + ------ + --------------- + ------ +- -------- + ------ + ------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ----------- + ------ + ----------------- + ------ + --------- + ------ + ------------- + | 1 | SIMPLE | user_action | ALL | NULL | 5 | Using where | + ---- + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------ + ---- --------- + 1 row in set (0.00 sec) can be known from type = ALL, full table scan mysql> explain select * from user_action where action = 'run' and name = 'dd '; + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ----------- + ------ + --------- ------------------------------ + ------------ + --------- + ------- + ------ + ------------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + 1 row in set (0.00 sec) mysql selects the execution path using cost similar to oracle, but mysql does not Oracle is so powerful 2. do not use a function in the index column. Otherwise, the index mysql> explain select * from user_action where abs (user_id)> 0 is not used; + ---- + ------------- + ------ + --------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------- + ------ + --------- + ------ + ------------- + | 1 | SIMP LE | user_action | ALL | NULL | 5 | Using where | + ---- + ------------- + ------ + --------- + ------ + ------------- + 1 row in set (0.00 sec) 3. overwrite the index using mysql> explain select * from user_action where action = 'run'; + ---- + ------------- + ----------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ----------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | + ---- + ------------- + ------ + ------------------- -------------------- + ------------ + --------- + ------- + ------ + ------------- + 1 row in set (0.00 sec) from the above type = ref and "Using where", we can see that, mysql first finds the index using the ref method, and then finds the data through the index back table description: ref: when the data is read using the KEY value, the KEY is not UNIQUE or primary key (in other words, if you cannot select a single row based on the keyword, use refUsing where: prompting mysql to use where to filter the result set mysql> explain select action from user_action where action = 'run '; + ---- + ------------- + ------ + -------------------------------------- -+ ------------ + --------- + ------- + ------ + Keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------ + ------------------------------------- + ------------ + --------- + ------- + ------ + ---------------------------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using index | + ---- + ------------- + ------ + upper + -------------- + --------- + ------- + ------ + ---------------------- + 1 row in set (0.00 sec) we can see "Using where; Using index" from the above, which means that the data is obtained through full index scanning and the result set is filtered out, so no data is returned from the table, overwrite index scan mysql> explain select action, user_id from user_action where action = 'run'; + ---- + ------------- + ------ + ---------- Keys + ------------ + --------- + ------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------ + ------------------------------------- + ------------ + --------- + ------- + ------ + ---------------------------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_nam E | idx_actions | 138 | const | 1 | Using where; using index | + ---- + ------------- + ----------- + ------ + upper + ------------ + --------- + ------- + ------ + -------------------------- + 1 row in set (0.00 sec) in the above example, I also used overwriting index scanning, but idx_action does not contain the user_id field. Why? Because idx_action is a secondary index, it can only find data through the primary key, and user_id is a primary key index. Therefore, you do not need to return to the table to obtain the required data. Therefore, to use overwriting indexes, do not use "select *" mysql> explain select action, a1 from user_action where action = 'run '; + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------------------------------- + ----- ------- + --------- + ------- + ------ + ----------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ------------- + 1 row in set (0.00 sec) the preceding field a1 is added, and a1 is placed in the select list. Then, we can view the execution plan and find that there is only "Using where", which means we need to retrieve data from the table. Mysql> explain select action from user_action order by action; + ---- + ------------- + ------- + --------------- + ------------ + --------- + ------ + ------------- + | id | select_type | table | type | keys | key_len | ref | rows | extra | + ---- + ------------- + ------- + --------------- + ------------ + --------- + ------ + ------------- + | 1 | SIMPLE | user_action | ind Ex | NULL | idx_action | 138 | NULL | 5 | Using index | + ---- + ------------- + ------- + --------------- + ------------ + --------- + ------ + ------------- + 1 row in set (0.00 sec) from the above we can see that the type = index and "Using index" indicate that only the index is scanned, And the covered index is used for scanning. If there is no return to the table, the sorted data is retrieved. 4. data Sorting (order by) mysql> explain select * from user_action order by action; + ---- + ------------- + ------ + --------------- + ------ + ------- -- + ------ + -------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ----------- + ------ + ----------------- + ------ + --------- + ------ + -------------- + | 1 | SIMPLE | user_action | ALL | NULL | 5 | Using filesort | + ---- + ----------- + ------------- + ------ + --------------- + ------ + --------- + ------ +- --------------- + 1 row in set (0.00 sec) This type = ALL and "Using filesort" can be seen that the full table scan is used, then sort the result set mysql> explain select * from user_action where user_id> 1 order by action; + ---- + ------------- + ------- + -------------------- + --------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows extra | + ---- + ----------- + ----- -------- + ------- + ------------------ + --------- + ------ + Hour + | 1 | SIMPLE | user_action | range | PRIMARY, idx_userid | PRIMARY | 4 | NULL | 2 | Using where; using filesort | + ---- + ------------- + ------- + -------------------- + --------- + ------ + ----------------------------- + 1 row in set (0.00 sec) because no index is used for sorting in the last SQL statement And the primary key is used. Why do we need to sort the data? The Order is different from the order of the primary key. There are two sorting methods for mysql: index and filesort. The index efficiency is high. It means that MySQL performs sorting by scanning the index itself. The FileSort method is inefficient. Because the index is not used to retrieve data, mysql needs to sort the obtained data in the memory and then return the data to the customer. In mysql, The filesort implementation algorithm actually has two types: 1) There is only one sort algorithm before mysql, first, obtain the corresponding sorting fields and row pointer information that can directly locate row data based on the corresponding conditions, then sort the data in sort buffer, and then return to the table to obtain the required data, here we need to go back to the table twice, and the speed will be slower. 2) starting from mysql4.1, we improved the first Sorting Algorithm to retrieve all the fields that satisfy the condition row at a time, and then sort them in sort buffer. The objective is to reduce the I/O operations that require secondary back-to-table operations in the first algorithm and convert the two operations into one operation, but it also consumes more sort buffer space. Mysql versions later than mysql support two algorithms at the same time. mysql mainly compares the size of the system parameter max_length_for_sort_data and the total size of the Field Types retrieved by the select statement ", to determine which sort algorithm to use. If max_length_for_sort_data is larger, the second optimized algorithm is used. Otherwise, the first algorithm is used. If you want the order by operation to be as efficient as possible, pay attention to the setting of the max_length_for_sort_data parameter. Mysql> explain select * from user_action where action> 'run' order by action, user_id; + ---- + ------------- + ------- + upper + ------------ + --------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + --------------------------------------- + ------------ + --------- + ------ + ------------- + | 1 | SIMPLE | user_action | range | idx_action, idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where | + ---- + ------------- + ------- + response + ------------ + --------- + ------ + ------------- + 1 row in set (0.00 sec) this SQL statement uses index sorting, and then retrieves data from the table to obtain the result set. The query results are not sorted. mysql> explain select * from user_action where Ction> 'run' order by action, a1; + ---- + ------------- + ------- + upper + ------------ + --------- + ------ + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + --------------------------------------- + ------------ + --------- + ------ + ------------- ---------------- + | 1 | SIMPLE | user_action | range | idx_action, idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where; using filesort | + ---- + ------------- + ----------- + ------- + certificate + ------------ + --------- + ------ + --------------------------- + 1 row in set (0.00 sec) this SQL statement also uses the index "idx_action". Why do we need to sort the result set? Because a1 is no longer indexed in idx_action, nor is it a primary key, you need to sort the result set. To use filesort, You can optimize filesort by increasing max_length_for_sort_data. Alternatively, avoid filesort mysql> explain select action, name from user_action order by action asc, name desc; + ---- + ------------- + ------ + --------- + ------ + ---------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------- + ------ + --------- + ------ + ------------------ + | 1 | SIMPLE | user_action | ALL | NULL | 5 | Using filesort | + ---- + ------------- + ------ + --------- + ------ + ---------------- + 1 row in set (0.00 sec) when both ASC and DESC are used for index columns, you need to use filesort mysql> explain select action, name from user_action where user_id = 1 order by action asc, name desc; + ---- + ------------- + ------- + certificate + --------- + ------- + ------ + ------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + ------------------ + --------- + ------- + ------ + ------- + | 1 | SIMPLE | user_action | const | PRIMARY, idx_userid | PRIMARY | 4 | const | 1 | + ---- + --------------- + ----------- + ------- + -------------------- + --------- + ------- + ------ + ------- + 1 row in set (0.00 sec) although both ASC and DESC are used for index columns, index columns in order by are converted to constants through the where Statement (primary key, mysql> explain select action, name from user_action where action = 'run' order by action asc, name desc; + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + upper + ------------ + --------- + ------- + ------ + ----------------------------- + | 1 | SIMPLE | user_action | ref | idx_action, idx_useraction_action_name | idx_actions | 138 | const | 1 | Using where; using filesort | + ---- + ------------- + ------ + accept + ------------ + --------- + ------- + ------ + --------------------------- + 1 row in set (0.00 sec) the where statement and the order by statement use different indexes and also need to be sorted. where uses idx_action, and order by uses idx_useraction_action_name In the case where filesort is used: 1) Too many rows are queried, the optimizer considers it necessary to scan the entire table without overwriting the index. 2) When both ASC and DESC are used for the index column, the where Statement (primary key) is used) convert the index column in order by to a constant. (3) If the where statement and the order by statement use different indexes, they also need to be sorted. (4) The order by clause adds non-index columns, and the non-index column is not in the where clause. 5) in the order by statement, the index column uses the expression ------ end ------