Mysql experiment uses explain to analyze the index trend, mysqlexplain

Mysql experiment uses explain to analyze the index trend, mysqlexplain

Overview

Index is a required skill for mysql and a means to provide mysql query efficiency. Which of the following experiments can be understood? Mysql index rules can also be constantly optimized for SQL statements

Lab Purpose

This experiment aims to verify the leftmost principle of the composite index.

Description

This experiment is only used to verify the results of the actual use of indexes. Ignore the rationality of the design.

Preparations

1. A user table contains fields such as uid, user_name, real_name, and eamil. For details, see the table creation statement.
2. Add a simple index user_name under the user_name field, and add the index complex_index under the email, mobile, and age fields.
3. Use MyISAM for the table engine and add
4. Prepare 97000 pieces of data (the data volume can be determined based on the actual situation. Here we prepare more than 97000 pieces of data)
5. Experiment tool Navcat

Table creation statement

Drop table if exists 'qz _ users'; create table 'qz _ users' ('uid' int (11) unsigned not null AUTO_INCREMENT COMMENT 'user uid ', 'User _ name' varchar (255) COLLATE utf8mb4_unicode_ci default null comment 'username', 'real _ name' varchar (128) character set utf8 default null comment 'user name ', 'email 'varchar (255) character set utf8 default null comment 'mail', 'mobile' varchar (16) character set utf8 default null comment 'user phone ', 'Password' varchar (32) character set utf8 default null comment 'user password', 'salt' varchar (16) character set utf8 default null comment' user append obfuscation Code ', 'Avatar _ file' varchar (128) character set utf8 default null comment 'profile picture file', 'sex' tinyint (1) default null comment 'gender ', 'birthday' int (10) default null comment' birthday', primary key ('uid'), KEY 'user _ name' (250 )), KEY 'complex _ Index' ('email ', 'mobile', 'sex') ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Prepared query statement

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";

Result Analysis

Use user_name Condition

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

Result

Analysis

Indexed?	Index name	Number of scan records
Yes	User_name	1

Email Condition

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

Result

Analysis

Indexed?	Index name	Number of scan records
Yes	Complex_index	7

Email + mobile + sex Conditions

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

Result

Analysis

Indexed?	Index name	Number of scan records
Yes	Complex_index	1

Email + mobile conditions

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

Result

Analysis

Indexed?	Index name	Number of scan records
Yes	Complex_index	7

Email + sex Conditions

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

Result

Analysis

] [3] indexing?	Index name	Number of scan records
Yes	Complex_index	7

Sex + mobile conditions

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

Result

Analysis

Indexed?	Index name	Number of scan records
No		97185

Mobile + sex Conditions

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

Result

Analysis

Indexed?	Index name	Number of scan records
No		97185

Conclusion

The preceding results show that after the combined index is set, the query condition order can be reasonably used to avoid slow query of SQL statements.