欢迎来到 黑吧安全网 聚焦网络安全前沿资讯,精华内容,交流技术心得!

怎么样优化Mysql数据库索引

来源:本站整理 作者:佚名 时间:2016-04-19 TAG: 我要投稿

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
问题:cpu负载过高,达到36。

现象:通过mysqladmin -uroot -p processlist 查看到大量如下信息:
Sending data select * from `rep_corp_vehicle_online_count` where corp_id = 48 and vehicle_id = 10017543
根据以上的可能是表rep_corp_vehicle_online_count的问题 做出如下测试:
查看表结构:
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> 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>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> 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> 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 | | |

[1] [2]  下一页

【声明】:黑吧安全网(http://www.myhack58.com)登载此文出于传递更多信息之目的,并不代表本站赞同其观点和对其真实性负责,仅适于网络安全技术爱好者学习研究使用,学习中请遵循国家相关法律法规。如有问题请联系我们,联系邮箱admin@myhack58.com,我们会在最短的时间内进行处理。
  • 最新更新
    • 相关阅读
      • 本类热门
        • 最近下载