Mysql database index optimization and mysql database index

Mysql database index optimization and mysql database index

With outstanding performance, low cost, and rich resources, MySQL has become the first choice of relational databases for most Internet companies. Although the performance is outstanding, the so-called "good horse with good saddle", how to better use it, has become a required course for development engineers, we often see requirements from job descriptions such as "proficient in MySQL", "SQL statement optimization", and "Understanding Database principles. We know that the general application system has a read/write ratio of around, and insert operations and general update operations rarely encounter performance problems. The most common problems are also the most likely problems, as there are still some complex query operations, the optimization of query statements is obviously the top priority.

Problem: the cpu load is too high, reaching 36.

Symptom: a large amount of information is displayed through mysqladmin-uroot-p processlist:

Sending data select * from `rep_corp_vehicle_online_count` where corp_id = 48 and vehicle_id = 10017543

Perform the following tests based on the above problem that may be caused by the rep_corp_vehicle_online_count table:

View the table structure:

mysql> desc rep_corp_vehicle_online_count;+-------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || corp_id | int(11) | NO | | NULL | || vehicle_id | int(11) | NO | | NULL | || online_day | varchar(20) | NO | | NULL | || loc_total | int(11) | NO | | NULL | || create_time | datetime | NO | | NULL | || update_time | datetime | NO | | NULL | |+-------------+-------------+------+-----+---------+----------------+7 rows in set (0.00 sec) 

View the index, only the primary key index:

mysql> show index from rep_corp_vehicle_online_count;+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rep_corp_vehicle_online_count | 0 | PRIMARY | 1 | id | A | 1247259 | NULL | NULL | | BTREE | | |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec) 

Code execution:

mysql>explain select * from rep_corp_vehicle_online_count where corp_id = 79 and vehicle_id = 10016911 and online_day = '2016-03-29'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: rep_corp_vehicle_online_counttype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1248495Extra: Using where1 row in set (0.00 sec) 

Table data analysis, many duplicate data:

mysql> select count(distinct corp_id) from rep_corp_vehicle_online_count;+-------------------------+| count(distinct corp_id) |+-------------------------+| 18 |+-------------------------+1 row in set (0.63 sec)mysql> select count(corp_id) from rep_corp_vehicle_online_count; +----------------+| count(corp_id) |+----------------+| 1239573 |+----------------+1 row in set (0.00 sec)mysql> select count(distinct vehicle_id) from rep_corp_vehicle_online_count; +----------------------------+| count(distinct vehicle_id) |+----------------------------+| 2580 |+----------------------------+1 row in set (1.03 sec)mysql>explain select count(vehicle_id) from rep_corp_vehicle_online_count; +-------------------+| count(vehicle_id) |+-------------------+| 1239911 |+-------------------+1 row in set (0.00 sec) 

Finally, create an index:

mysql> create index r_c_v on rep_corp_vehicle_online_count(corp_id,vehicle_id); Query OK, 1487993 rows affected (6.09 sec)Records: 1487993 Duplicates: 0 Warnings: 0mysql> show index from rep_corp_vehicle_online_count;+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rep_corp_vehicle_online_count | 0 | PRIMARY | 1 | id | A | 1490176 | NULL | NULL | | BTREE | | || rep_corp_vehicle_online_count | 1 | r_c_v | 1 | corp_id | A | 18 | NULL | NULL | | BTREE | | || rep_corp_vehicle_online_count | 1 | r_c_v | 2 | vehicle_id | A | 2596 | NULL | NULL | | BTREE | | |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

After the index is added, the load is reduced to 1.73:

The above content is the index optimization of the Mysql database introduced by xiaobian. I hope it will help you learn it!

Articles you may be interested in:

• Mysql performance optimization case study-Covering Index and SQL _NO_CACHE
• Index optimization for mysql Performance Optimization
• Primary Key Index of MySQL Indexes
• Introduction to clustered indexes of MySQL Indexes
• How to create an index using Inplace and Online in MySQL
• Simple tutorial on unique indexes in MySQL
• Tutorial on joint Indexing in MySQL
• How to view, create, and delete indexes in MySQL
• Brief Introduction to MYSQL index types
• Mysql database index corruption and restoration experience
• Important questions about mysql Indexes