Shulou(Shulou.com)05/31 Report--

How to use explain in Mysql to analyze the direction of the index? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Preparatory work

1. A user table with fields such as uid, user_name,real_name, eamil, etc. For more information, please see the statement of the Construction Table.

2. Add a simple index user_name under the user_name field and index complex_index under the three fields of email,mobile,age

3. MyISAM is used in the table engine to increase the number of

4. Prepare 97000 pieces of data (specifically, you can determine the amount of data according to the actual situation. Here you prepare 97000 +)

5. Experimental tool Navcat

Construction table sentence

DROP TABLE IF EXISTS `qz_ users` CREATE TABLE `user_ users` (`uid` int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user's UID', `user_ name` varchar' user name', `real_ name` varchar '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 phone', `password` varchar (32) CHARACTER SET utf8 DEFAULT NULL COMMENT 'user password' `salt` varchar (16) CHARACTER SET utf8 DEFAULT NULL COMMENT 'user additional confusion code', `avatar_ file` varchar 'CHARACTER SET utf8 DEFAULT NULL COMMENT' profile', `sex`tinyint (1) DEFAULT NULL COMMENT 'gender', `day`int (10) DEFAULT NULL COMMENT 'birthday', PRIMARY KEY (`uid`), KEY `complex_ name` (`user_ name`), 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.

Whether to use the index name scan record number is user_name1

Use email condition

Explain select * from qz_users where email = "x"

Result

Analysis.

Whether to use the index name scan record number is complex_index7

Use the email + mobile + sex condition

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

Result

Analysis.

Whether to use the index name scan record number is complex_index1

Use email + mobile condition

Explain select * from qz_users where email = "x" and mobile = "x"

Result

Analysis.

Whether to use the index name scan record number is complex_index7

Use email + sex condition

Explain select * from qz_users where email = "x" and sex = "x"

Result

Analysis.

] [3] whether to use the index name scan record number is complex_index7

Use sex + mobile condition

Explain select * from qz_users where sex = "x" and mobile = "x"

Result

Analysis.

Whether to use the index name to scan the number of records

97185

Use mobile+ sex condition

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

Result

Analysis.

Whether to use the index name to scan the number of records

97185 after reading the above, have you mastered how to use explain to analyze the direction of the index in Mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.