Analysis of order Derby’s use of indexes

Mysql > alter TABLE Order by;

In some cases, MySQL can use indexes to satisfy clauses and avoid the extra sorting involved in performing operations. An index can be used even if it does not exactly match the index, as long as all unused portions of the index and all additional columns are constants in the clause. If the index does not contain all the columns accessed by the query, it is used only if index access is less expensive than other access methods. That is, if the query results contain fields other than those contained in the index, the index is not used.

Create table

DROP TABLE IF EXISTS `t_orderby`;
CREATE TABLE `t_orderby`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL.`age` int(11) NULL DEFAULT NULL.`address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age`(`name`.`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_orderby
-- ----------------------------
INSERT INTO `t_orderby` VALUES (1.'zhangsan'.18.'Beijing');
INSERT INTO `t_orderby` VALUES (2.'zhangsan2'.20.'Beijing');
INSERT INTO `t_orderby` VALUES (3.'zhangsan3'.23.'Beijing');
INSERT INTO `t_orderby` VALUES (4.'zhangsan4'.13.'Beijing');
INSERT INTO `t_orderby` VALUES (5.'zhangsan5'.19.'Beijing');
INSERT INTO `t_orderby` VALUES (6.'zhangsan6'.30.'Beijing');
INSERT INTO `t_orderby` VALUES (7.'lisi1'.30.'Beijing');
INSERT INTO `t_orderby` VALUES (8.'lisi2'.32.'Beijing');
INSERT INTO `t_orderby` VALUES (9.'lisi3'.12.'Beijing');
INSERT INTO `t_orderby` VALUES (10.'lisi4'.19.'Beijing');
INSERT INTO `t_orderby` VALUES (11.'wangwu'.14.'Beijing');
INSERT INTO `t_orderby` VALUES (12.'wangwu2'.18.'Beijing');
INSERT INTO `t_orderby` VALUES (13.'wangwu3'.17.'Beijing');
INSERT INTO `t_orderby` VALUES (14.'wangwu4'.47.'Beijing');
INSERT INTO `t_orderby` VALUES (15.'wangwu5'.37.'Beijing');
INSERT INTO `t_orderby` VALUES (16.'wangwu6'.38.'Beijing');
Copy the code

Third, do not use index situation

1. SQL statements

mysql> explain select * from t_orderby order by name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orderby
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2090301
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

Copy the code

2, analysis,

Select * from idx_name_age; select * from idx_name_age; select * from idx_name_age;

Using indexes Scenario 1

1. SQL statements

mysql> explain select id,name,age from t_orderby order by name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orderby
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name_age
      key_len: 38
          ref: NULL
         rows: 2090301
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
Copy the code

2, analysis,

You can use the index at this point because the fields in the query are contained in the idx_NAMe_age index.

Using indexes Scenario 2

1. SQL statements

mysql> explain select id,name,age from t_orderby order by age \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orderby
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name_age
      key_len: 38
          ref: NULL
         rows: 2090301
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

Copy the code

2, analysis,

In this case, you can also use the index to sort the query. Although the sorted field is not the first field in the index, the cost of sorting the age by index is lower than that of sorting the file. Therefore, you can use the index. But you can’t rely entirely on indexes and you still need file sorting to help.

Using indexes Scenario 3

1. SQL statements

mysql> explain select * from t_orderby where name='zhangsan' order by age \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orderby
   partitions: NULL
         type: ref
possible_keys: idx_name_age
          key: idx_name_age
      key_len: 33
          ref: const
         rows: 277562
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
Copy the code

2, analysis,

Select * from where (name) and order by (age); select * from where (age) and order by (age); But this process requires back table operations.

Index break cannot use index:

mysql> explain select * from t_orderby where name>'zhangsan' order by age \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_orderby
   partitions: NULL
         type: ALL
possible_keys: idx_name_age
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2090301
     filtered: 50.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
Copy the code

Seven,

There are two questions to consider about whether an index can be used in the order by clause. The first is whether the list of fields following the SELECT can be retrieved directly from the index, and the second is whether the WHERE condition can use the index to limit a range that is acceptable even if the query returns to the table again.