Mysql experiment: using explain to analyze the trend of indexes

Overview

Indexing is a skill that must be mastered in MySQL, and it is also a means to improve MySQL query efficiency. Can you understand it through the following experiment? MySQL index rules can also continuously optimize SQL statements

Purpose

This experiment is to verify the leftmost principle of the combined index

illustrate

This experiment is only to verify the results of actual use of the index, please ignore the rationality of the design

Preparation

1. A user table with fields such as uid, user_name, real_name, eamil, etc. For details, see the table creation statement
2. Add a simple index user_name under the user_name field, and add an index complex_index under the email, mobile, and age fields.
3. The table engine uses MyISAM, increase
4. Prepare 97,000 pieces of data (the specific amount of data can be determined according to the actual situation, here we prepare 97,000+)
5. Experimental tool Navcat

Create table statement

DROP TABLE IF EXISTS `qz_users`;
CREATE TABLE `qz_users` (
 `uid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User's UID',
 `user_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'User name',
 `real_name` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT 'User name',
 `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'EMAIL',
 `mobile` varchar(16) CHARACTER SET utf8 DEFAULT NULL COMMENT 'User's mobile phone',
 `password` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT 'User password',
 `salt` varchar(16) CHARACTER SET utf8 DEFAULT NULL COMMENT 'User-added obfuscation code',
 `avatar_file` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Avatar file',
 `sex` tinyint(1) DEFAULT NULL COMMENT 'Gender',
 `birthday` int(10) DEFAULT NULL COMMENT 'Birthday',
 PRIMARY KEY (`uid`),
 KEY `user_name` (`user_name`(250)),
 KEY `complex_index` (`email`,`mobile`,`sex`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Prepared queries

explain select * from qz_users where user_name = "ryanhe";
explain select * from qz_users where email = "x";
explain select * from qz_users where email = "x" and mobile = "x" and sex=1;
explain select * from qz_users where email = "x" and mobile = "x";
explain select * from qz_users where email = "x" and sex = "x";
explain select * from qz_users where sex = "x" and mobile = "x";
explain select * from qz_users where mobile = "x" and sex = "0";

Results Analysis

Using the user_name condition

explain select * from qz_users where user_name= "x";

result

analyze

Whether to use index	Index Name	Scan records
yes	user_name	1

Use email conditions

explain select * from qz_users where email = "x";

result

analyze

Whether to use index	Index Name	Scan records
yes	complex_index	7

Use email + mobile + sex conditions

explain select * from qz_users where email = "x" and mobile = "x" and sex=1;

result

analyze

Whether to use index	Index Name	Scan records
yes	complex_index	1

Use email + mobile conditions

explain select * from qz_users where email = "x" and mobile = "x";

result

analyze

Whether to use index	Index Name	Scan records
yes	complex_index	7

Use email + sex condition

explain select * from qz_users where email = "x" and sex = "x";

result

analyze

][3] Whether to use index	Index Name	Scan records
yes	complex_index	7

Use sex + mobile conditions

explain select * from qz_users where sex = "x" and mobile = "x";

result

analyze

Whether to use index	Index Name	Scan records
no		97185

Use mobile+ sex conditions

explain select * from qz_users where mobile = "18602199680" and sex = "0";

result

analyze

Whether to use index	Index Name	Scan records
no		97185

in conclusion

From the above results, we can see that after setting the combined index, the reasonable use of the query condition order can avoid slow query of SQL statements.

You may also be interested in:

How to optimize MySQL index function based on Explain keyword
MySQL Index Optimization Explained
Detailed explanation of the use of mysql explain (analysis index)
Mysql index combined with explain analysis example

<<: JavaScript implements long image scrolling effect

>>: Detailed tutorial on installing Docker on CentOS 7.5

Detailed explanation of two ways to dynamically change CSS styles in react

Mysql experiment: using explain to analyze the trend of indexes

Detailed explanation of two ways to dynamically change CSS styles in react

Detailed explanation of MySQL user rights management

Detailed explanation of generic cases in TypeScript

Pure CSS code to achieve drag effect

How to modify the scroll bar style in Vue

Detailed explanation of angular two-way binding

Detailed example of using CSS to beautify HTML form controls (form beautification)

Detailed explanation of Nginx current limiting configuration

Web designers also need to learn web coding

Detailed Example of MySQL curdate() Function

Recommend

W3C Tutorial (3): W3C HTML Activities

Linux virtual memory settings tutorial and practice

Some questions about hyperlinks

JavaScript implements a box that follows the mouse movement

MySQL 8.0.18 deployment and installation tutorial under Windows 7

js to implement file upload style details

Implementation of postcss-pxtorem mobile adaptation

CSS3 sample code to achieve element arc motion

Example of using mycat to implement MySQL database read-write separation

Sample code for the test script for indexes and locks at RR and RC isolation levels

Detailed explanation of the application and difference between filter attribute and backdrop-filter in CSS

MySql fuzzy query json keyword retrieval solution example

Use Grafana+Prometheus to monitor MySQL service performance

Docker uses nextcloud to build a private Baidu cloud disk

This article summarizes the specific use of CSS two-column layout and three-column layout