Shulou(Shulou.com)06/01 Report--

The following brings you about the method of using prefix index to optimize MySQL. I believe you must have seen articles similar to the method of using prefix index to optimize MySQL. What's the difference between what we bring to everyone? Let's take a look at the text. I'm sure you'll get something after reading it.

1. View the table structure (root@localhost) [prod_db] > show create table t_file_info\ G * * 1. Row * * Table: t_file_infoCreate Table: CREATE TABLE `troomfileinfo` (`id`varchar (36) NOT NULL DEFAULT'', `ared` varchar (64) DEFAULT NULL, `areaid` int (11) DEFAULT NULL, `fileid` varchar (256) NOT NULL, `filename` varchar (256) DEFAULT NULL `filesize` int (11) DEFAULT NULL, `filemd5` varchar (40) DEFAULT NULL, `extend` varchar (4000) DEFAULT NULL, `status` int (11) NOT NULL DEFAULT '0requests, `createdate` datetime DEFAULT NULL, `fileurl` varchar (256) DEFAULT NULL, `businessid` bigint (20) NOT NULL DEFAULT' 0requests, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.fileid is a condition for our query, and normally an index needs to be created.

Select char_length ('63f2a078018649ca9948f5469550bf2aUniGroup1 and M0000GAVuJcAAAI4FL7ZCA388.jpg') +-+ | char_length ('63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388. Jpg') | +-+ | 84 | + -+ 1 row in set (0.00 sec)-- communicate with developers and understand The first 32 bits are equivalent to uuid that can determine a unique value.

3. How do we create indexes for such fields? is there a rule to follow? Continue to check

-View the selection rate of select count (distinct (fileid)) / count (*) AS Selectivity from tweets filebroken from t_file_info select count (distinct left (fileid,32)) / count (*) from tweets fileholders; (root@localhost) [prod_db] > select count (distinct (fileid)) / count (*) from t_file_info +-+ | count (distinct (fileid)) / count (*) | +-+ | 1.0000 | +- -+ 1 row in set (0.17 sec) (root@localhost) [prod_db] > select count (distinct left (fileid) 32)) / count (*) from t_file_info +-- + | count (distinct left (fileid) 32) / count (*) | +-- + | 0.9999 | +-- + 1 and 0.9999 are almost the same. In fact, because of some special circumstances here, the normal should be 1.

4. View an unindexed execution plan

Explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg'; (root@localhost) [prod_db] > explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg' +-- + | id | select_type | table | type | possible_keys | Key | key_len | ref | rows | Extra | +-- + | 1 | SIMPLE | | t_file_info | ALL | NULL | 35109 | Using where | +- -+ 1 row in set (0.00 sec)

5. Create a prefix index to view the execution plan

Alter table `prod_ db`.`t _ file_ info` add index idx_t_file_info_fileid (fileid (32)); (root@localhost) [prod_db] > explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg' + -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + +-+ | 1 | SIMPLE | t_file_info | ref | idx_t_file_info_fileid | idx_t_file_info_fileid | 98 | const | 1 | Using where | + -- return 1 line is what we want to see.

6. Create an index

(root@localhost) [prod_db] > alter table `prod_ db`.`t _ file_ info` add index idx_t_file_info_fileid (fileid (32)); Query OK, 0 rows affected (5 min 36.03 sec) Records: 0 Duplicates: 0 Warnings: 0 create an index to observe the system resource usage, the memory opportunity does not change, but the CPU single core is almost full (root@localhost) [prod_db] > select count (fileid) from t_file_info +-+ | count (fileid) | +-+ | 12299419 | +-+ 1 row in set (14.94 sec)-tens of millions of lines

Summary:

1. Learn about the practical scenario of prefix indexing.

two。 It is necessary to communicate with developers and understand the business in order to create the most appropriate index.

3. Creating an index will have a great impact on system performance, so choose an appropriate time to create it and evaluate the impact. Don't take anything for granted. It's easy to go wrong when you're inexperienced and take it for granted.

Do you think the above method of using prefix indexes to optimize MySQL is what you want? If you want to know more about it, you can continue to follow our industry information section.

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.