This is the sixth day of my participation in Gwen Challenge

preface

There’s nothing like a high-performance index to a database to reduce query time, and a dictionary without a front table of contents can be a real drag to find a word.

The column index more

Create a multi-column index with all related columns, instead of multiple independent single-column indexes

CREATE TABLE `chinese_score` (
  `exam_id` int(11) NOT NULL COMMENT 'Test Id, foreign key',
  `exam_code` int(11) NOT NULL COMMENT 'candidate number',
  `user_name` char(5) DEFAULT NULL,
  `class_name` int(11) DEFAULT NULL COMMENT 'Class Name',
  `total_score` int(11) DEFAULT NULL,
  `area_ranking` int(11) DEFAULT NULL,
  `school_ranking` int(11) DEFAULT NULL,
  `class_ranking` int(11) DEFAULT NULL.PRIMARY KEY (`exam_code`) USING BTREE,
  UNIQUE KEY `idx_exam_id_name` (`exam_id`,`class_name`,`user_name`) USING BTREE,
  KEY `idx_total_score` (`total_score`) USING BTREE,
  KEY `idx_exam_id` (`exam_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Select * from exam_id where exam_id=1 and exam_id=1

select * from `chinese_score` where exam_id=1 and class_name=15
Copy the code

You can see that the multi-column index we created idx_exam_ID_name is used whereas if we search for students with exam_id=1 and a score of 90

We can see that MySQL has found multiple indexes that contain our query criteria, so which one does it choose? It selects idx_total_score instead of multiple single-column indexes. The score is selected as the index to look up, which is highly differentiated and returns the least amount of data, while idx_exam_id is excluded because of low differentiation and returns more data.

Select the appropriate index column order

In a multi-column B-tree index, the order of the index columns means that the index is sorted by the leftmost column first, the second column second, and so on. Therefore, indexes can be scanned in ascending or descending ORDER to satisfy the query requirements for clauses such as ORDER BY, GROUP BY, and DISTINCT that precisely match column ORDER. The most selective columns are usually best placed first, and you need to take advantage of the orderliness of indexes if you are thinking about sorting and grouping. The fields at the end of the order by are part of the composite index and are placed at the end of the index composite order to avoid file_sort, which can affect query performance. Where a = where? and b =? order by c; Index: A _ b _ C for example

select * from `chinese_score` where exam_id=1 and class_name=15 ORDER BY user_name
Copy the code

The corresponding explanation is as follows

Order BY = idx_exam_id_name = idx_exam_id_name = idx_exam_id_name

select * from `chinese_score` where exam_id=1 and class_name=15 ORDER BY class_ranking
Copy the code

If Order By is sorted using columns that are not in the index, then

In the extra column there is a Using filesort in addition to the Using index condition. There is a class_ranking not in the index, so we have to add a filesort.

WHERE a >10 ORDER BY b; WHERE a >10 ORDER BY b; Index A _ b cannot be sorted

For example, we want to query the number of students in both classes 1 and 2 who took the exam with exam_id=1

select * from `chinese_score` where exam_id=1 and class_name< =2 ORDER BY user_name
Copy the code

The explanation is as follows:

You can see that having a range query causes file sorting with file_sort to affect performance.

conclusion

Today we have covered how MySQL can create high performance indexes. Tomorrow we will continue to cover other methods of creating high performance indexes. See you in the next article!