Query performance of a mysql database

A mysql database query performance problem encountered this week. Similar to the following simple query in a MYSQL table, it takes nearly one second. The table www.2cto.com 1 select sum (col5), sum (col6) from table_name2where col_key_2 = 'value1 'and col_key_3 = 'value2' is defined as follows: 01 create table 'table _ name' (02 'col _ key_1 'date not null default '2017-00-00', 03 'col _ key_3 'varchar (32) not null default '', 04 'col _ key_2 'varchar (32) not null default'', 05 'col5' bigint (20) unsigned default NULL, 06 'col6' bigint (20) unsigned default NULL, 07 'col7' bigint (20) unsig Ned default NULL, 08 'col8' bigint (20) unsigned default NULL, 09 'col _ key_4 'varchar (32) not null default '', 10 primary key ('col _ key_1 ', 'col _ key_2', 'col _ key_3 ', 'col _ key_4') 11) ENGINE = MyISAM default charset = latin1 | the entire table contains about more than 2 million data records. However, the query speed can be slowed down to one second before it can be queried. Then I added another index: KEY 'class' ('col _ key_2 ', 'col _ key_3') www.2cto.com. The query speed was immediately increased to 0.00 seconds. So I carefully checked section 8.3 of the mysql manual. The types and roles of MySQL indexes: primary key, unique, index, fulltext index. Primary key is the primary key, unique is the unique index, and index is a common index. Fulltext index is a full-text index. The function of indexing is as direct to a table as a pointer in the C language. You can index the first N Bytes of the string using col_name (N. Text and blob types must be indexed on the first N Bytes. MYISAM supports indexes of up to 1000 bytes, while INNODB supports indexes of up to 767 bytes. The index has the following functions: 1. It helps the where statement to quickly query. 2. perform multi-table join 3. Find the maximum and minimum values (only the B-tree index has this function, and the hash index does not have this function) 4 sort (only the B-tree index has this function, hash index does not have this function) and group Multi-column index multi-column index are particularly useful when querying multiple columns at the same time. Multi-column indexes support up to 16 columns. We can understand the multi-column index in this way: Put the concat of multiple columns together, and then make an index on the value of this concat. What's amazing is that, for example, if you have an index targeting the three columns col1 col2 col3, you can only query col1 and only query col1 col2. For example, the following TABLE is available: 1 create table test (2 id int not null, 3 last_name CHAR (30) not null, 4 first_name CHAR (30) not null, 5 primary key (id), 6 INDEX name (last_name, first_name) 7); the following queries can use multiple column indexes: 01 SELECT * FROM test WHERE last_name = 'widenius '; 02 03 SELECT * FROM test04 WHERE last_name = 'widenius 'AND first_name = 'Michael '; 05 06 SELECT * FROM test07 WHERE last_name = 'widenius '08 AND (first_name = 'Michael 'OR first_name = 'Monty '); 09 10 SELECT * FROM test11 WHERE last_name = 'widenius '12 AND first_name> = 'M' AND first_name <'n '; the following queries cannot use multiple column indexes: 1 SELECT * FROM test WHERE first_name = 'Michael '; 2 3 SELECT * FROM test4 WHERE last_name = 'widenius' OR first_name = 'Michael '; you can use the explain statement before the SQL statement to determine whether an index is used. For example, the following query can use the class index 01 mysql> explain select sum (col5), sum (col6) from table_name02where col_key_2 = 'value1' and col_key_3 = 'value2' \ G03 ************************* ** 1. row ************************** 04 id: 105 select_type: SIMPLE06 table: table_name07 type: ref08possible_keys: class09 key: class10 key_len: 6811 ref: const, const12 rows: 113 Extra: Using where141 row in set (0.00 sec) but the following query cannot be used To index: 01 mysql> explain select sum (col5), sum (col6) from table_name02 where col5 = 'value1' and col_key_3 = 'value2' \ G03 ************************ * ** 1. row ************************** 04 id: 105 select_type: SIMPLE06 table: table_name07 type: ALL08possible_keys: NULL09 key: NULL10 key_len: NULL11 ref: NULL12 rows: 235745513 Extra: Using where141 row in set (0.00 sec) Index Quality MySQL uses an indicator value group size to balance The quality of the index. What is value group? Is the number of rows with the same index key value. The smaller the indicator, the better. The ideal condition is that each key value corresponds to only one row. In this case, only one row is returned for each key value search, which is obviously very fast. You can use the tools provided by mysql to check the index quality of a table. You can use the analyze table statement to update statistics, and then use show index to view statistics: 1 mysql> analyze table table_name; 2 + ----------------- + --------- + ---------- + 3 | Table | Op | Msg_type | Msg_text | 4 + ----------------- + --------- + ---------- + 5 | stat. table_name | analyze | status | OK | 6 + ----------------- + --------- + ---------- + 71 row in set (3.13 sec) 8 9 mysql> show index in table_name;
Table_name this table has two indexes PRIMARY and class. The PRIMARY index is a multi-column Index containing four columns. Cardinality indicates the number of rows of the index value. For example, the value of col_key_1 has 18 rows. The value of col_key_1 + col_key_2 contains 392909 rows. The value of col_key_1 + col_key_2 + col_key_3 contains 235745 rows. There are 235745 rows of col_key_1 + col_key_2 + col_key_3 + col_key_4 values. Through the number of rows of the index value, we can see whether the index is good or not. The more rows with different index values, the better the index. When the number of rows with different index values = the total number of rows in the table reaches the optimal value group size = 1. By default, MySQL uses B-tree indexes to compare B-tree indexes and Hash indexes. Let's talk about the defects of Hash indexes: 1 can only handle the where clause like '=', but it is powerless for <>. This is related to the order of B-tree indexes and Hash disorder. 2. order by cannot be processed. The reason is the same as above. 3. The distance between the two rows is unknown. The reason is the same as above. 4. You can only search for complete fields, not just a part of the fields. For B-tree indexes, you can search for the leftmost part of a string. For example, "police % ".