Mysql experiment: using explain to analyze the trend of indexes

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

Recommend

MySQL table auto-increment id overflow fault review solution

Problem: The overflow of the auto-increment ID in...

Some ways to solve the problem of Jenkins integrated docker plugin

Table of contents background Question 1 Error 2 E...

How to display percentage and the first few percent in MySQL

Table of contents Require Implementation Code dat...

MySQL dual-machine hot standby implementation solution [testable]

Table of contents 1. Concept 2. Environmental Des...

Detailed tutorial on installing nvidia driver + CUDA + cuDNN in Ubuntu 16.04

Preparation 1. Check whether the GPU supports CUD...

CSS Summary Notes: Examples of Transformations, Transitions, and Animations

1. Transition Transition property usage: transiti...

Detailed analysis of MySQL instance crash cases

[Problem description] Our production environment ...

Analysis and solution of the reason why the frameset tag in HTML cannot be displayed normally

<frameset></frameset> is familiar to e...

Detailed description of component-based front-end development process

Background <br />Students who work on the fr...

Detailed explanation of formatting numbers in MySQL

Recently, due to work needs, I need to format num...

How to store false or true in MySQL

MySQL Boolean value, stores false or true In shor...

The problem of form elements and prompt text not being aligned

Recent projects involve the creation of a lot of ...

Example of using JSX to build component Parser development

Table of contents JSX environment construction Se...

Detailed tutorial on installing mysql 8.0.13 (rpm) on Centos7

yum or rpm? The yum installation method is very c...

Steps to install MySQL on Windows using a compressed archive file

Recently, I need to do a small verification exper...