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 configure tomcat server for eclipse and IDEA

tomcat server configuration When everyone is lear...

Solution to the problem that elements with negative z-index cannot be clicked

I was working on a pop-up ad recently. Since the d...

Solve the problem of MySQL reporting Invalid default value for ''operate_time'' error

Execute the create table statement in the databas...

React High-Order Component HOC Usage Summary

One sentence to introduce HOC What is a higher-or...

10 performance configuration items that need to be adjusted after installing MySQL

In this blog, we will discuss ten performance set...

How to implement CSS to display ellipsis when single-line or multi-line text overflows

1. Single row overflow 1. If a single line overfl...

Implementation of Nginx configuration of multi-port and multi-domain name access

To deploy multiple sites on a server, you need to...

Execute initialization sql when docker mysql starts

1. Pull the Mysql image docker pull mysql:5.7 2. ...

Add ?v= version number after js or css to prevent browser caching

Copy code The code is as follows: <span style=...

Semantics: Is Html/Xhtml really standards-compliant?

<br />Original text: http://jorux.com/archiv...

How to restore docker container data

The project test environment database data is los...

MySQL recursion problem

MySQL itself does not support recursive syntax, b...

How does MySQL achieve multi-version concurrency?

Table of contents MySQL multi-version concurrency...

Solution to Workbench not connecting to MySQL on Alibaba Cloud Server Ubuntu (tested)

In the past two days, I have been very troubled t...

CSS shadow animation optimization tips

This technique comes from this article - How to a...