乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql入門Mysql數(shù)據(jù)庫之索引優(yōu)化

作者：VEPHP   時間 2017-10-02

《Mysql入門Mysql數(shù)據(jù)庫之索引優(yōu)化》要點(diǎn)：
本文介紹了Mysql入門Mysql數(shù)據(jù)庫之索引優(yōu)化，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL數(shù)據(jù)庫MySQL憑借著出色的性能、低廉的成本、豐富的資源,已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫.雖然性能出色,但所謂“好馬配好鞍”,如何能夠更好的使用它,已經(jīng)成為開發(fā)工程師的必修課,我們經(jīng)常會從職位描述上看到諸如“精通MySQL”、“SQL語句優(yōu)化”、“了解數(shù)據(jù)庫原理”等要求.我們知道一般的應(yīng)用系統(tǒng),讀寫比例在10:1左右,而且插入操作和一般的更新操作很少出現(xiàn)性能問題,遇到最多的,也是最容易出問題的,還是一些復(fù)雜的查詢操作,所以查詢語句的優(yōu)化顯然是重中之重.

MYSQL數(shù)據(jù)庫問題：cpu負(fù)載過高,達(dá)到36.

MYSQL數(shù)據(jù)庫Mysql入門Mysql數(shù)據(jù)庫之索引優(yōu)化

MYSQL數(shù)據(jù)庫現(xiàn)象：通過mysqladmin -uroot -p processlist 查看到大量如下信息：

MYSQL數(shù)據(jù)庫
Sending data select * from `rep_corp_vehicle_online_count` where corp_id = 48 and vehicle_id = 10017543

MYSQL數(shù)據(jù)庫根據(jù)以上的可能是表rep_corp_vehicle_online_count的問題 做出如下測試：

MYSQL數(shù)據(jù)庫查看表結(jié)構(gòu)：

MYSQL數(shù)據(jù)庫
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)

MYSQL數(shù)據(jù)庫查看索引,只有主鍵索引：

MYSQL數(shù)據(jù)庫
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)

MYSQL數(shù)據(jù)庫代碼執(zhí)行情況：

MYSQL數(shù)據(jù)庫
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: 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
1 row in set (0.00 sec)

MYSQL數(shù)據(jù)庫表數(shù)據(jù)分析情況,重復(fù)數(shù)據(jù)很多：

MYSQL數(shù)據(jù)庫
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)

MYSQL數(shù)據(jù)庫最后處理,創(chuàng)建索引：

MYSQL數(shù)據(jù)庫
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_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)

MYSQL數(shù)據(jù)庫添加索引過后負(fù)載降低到了1.73：

MYSQL數(shù)據(jù)庫Mysql入門Mysql數(shù)據(jù)庫之索引優(yōu)化

MYSQL數(shù)據(jù)庫以上內(nèi)容是小編給大家介紹的Mysql數(shù)據(jù)庫之索引優(yōu)化 ,希望對大家學(xué)習(xí)有所幫助!

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/6215.html


    標(biāo)簽：