Index optimization _mysql of Mysql database

With its excellent performance, low cost and rich resources, MySQL has become the preferred relational database for most Internet companies. Although excellent performance, but the so-called "good horse with a good saddle", how to better use it, has become a compulsory course for development engineers, we often see from the job description such as "Proficient in MySQL", "SQL statement optimization", "understand the principle of the database" and other requirements. We know that the general application system, reading and writing ratio of about 10:1, and insert operation and general update operations rarely appear performance problems, the most encountered, but also the most problematic, or some complex query operations, so the query statement optimization is obviously the most important.

Problem: CPU load is too high, up to 36.

Phenomenon: Through Mysqladmin-uroot-p processlist see a lot of the following information:

Sending data select * from ' rep_corp_vehicle_online_count ' where corp_id = + and vehicle_id = 10017543

The following tests are based on the above questions that may be table Rep_corp_vehicle_online_count:

View 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 | |
+-------------+-------------+------+-----+---------+----------------+

View index, only 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 | | | +-------------------------------+------------+----------+--------------+-------------+-----------+------------- +----------+--------+------+------------+---------+---------------+

Code Execution:

Mysql>explain SELECT * from rep_corp_vehicle_online_count where corp_id = and vehicle_id = 10016911 and Online_day = ' 2016-03-29 ' \g
*************************** 1. Row ***************************
id:1
select_type:simple
table:rep_corp_vehicle_online_count
type:all
possible_keys:null
key:null
key_len:null
ref:null
rows:1248495
extra:using where

Table data analysis, many duplicate data:

Mysql> Select COUNT (Distinct corp_id) from Rep_corp_vehicle_online_count;
+-------------------------+
| count (distinct corp_id) |
+-------------------------+
|
+-------------------------+
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 |
+-------------------+

Final processing, creating 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:0 mysql> Show index from Rep_corp_v
Ehicle_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 adding an index, the load is reduced to 1.73:

The above content is small series to introduce the MySQL database index optimization, I hope to help you learn!