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

How to fix abnormal startup of mysql5.7.21

A colleague reported that a MySQL instance could ...

HTML Tutorial: Ordered Lists

<br />Original text: http://andymao.com/andy...

Summary of JavaScript's setTimeout() usage

Table of contents 1. Introduction 2. The differen...

Detailed explanation of grep and egrep commands in Linux

rep / egrep Syntax: grep [-cinvABC] 'word'...

js to realize automatic lock screen function

1. Usage scenarios There is such a requirement, s...

MySQL horizontal and vertical table conversion operation implementation method

This article uses examples to illustrate how to i...

How to solve the DOS window garbled problem in MySQL

The garbled code problem is as follows: The reaso...

Windows keeps remote desktop from being automatically disconnected for a long time

Anyone who has used Windows Remote Desktop to con...

Application of Hadoop counters and data cleaning

Data cleaning (ETL) Before running the core busin...

Summary of basic SQL statements in MySQL database

This article uses examples to describe the basic ...

A brief analysis of Docker private image library and Alibaba Cloud object storage OSS

Docker private image library Docker private image...

Detailed explanation of replication configuration example between mysql containers

background Last week the company trained on MySQL...

How to package the docker image, push it to the remote server and deploy it to k8s

Table of contents 1. Dockerfile 2. pom configurat...

The difference between two MySQL delete user statements (delete user and drop user)

Tip: In MySQL, we often need to create and delete...

js realizes 3D sound effects through audioContext

This article shares the specific code of js to ac...