前言：过去的笔记整理而得，未免丢失，发布个人博客。[2012年的资料笔记]
场景：数据库的负载飙升，CPU高达99%。
查看进程。通过猜测推理，定位了一些select语句
查看此表的数据库表结构如下：
---------------------------------------------------------------------------------------------------------------------------------------------------------
| category_doc_info | CREATE TABLE `category_doc_info` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL COMMENT
`doc_title` varchar(255) NOT NULL COMMENT
`category_show` tinyint(1) unsigned NOT NULL COMMENT
`category_Coordinate` tinyint(1) unsigned NOT NULL default '2'
`category_order` tinyint(1) unsigned NOT NULL default '0'
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
发现只有一个组合索引。但是完全没有用到。
现实场景：都是where (doc_title = '???') 的语句
查看具体的一条SQL语句的执行计划，如下:
可以发现都是全表查询，并且是高并发的访问上述语句。
经过分析，修改生产环境的表结构，如下:
| category_doc_info | CREATE TABLE `category_doc_info` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL,
`doc_title` varchar(255) NOT NULL',
`category_show` tinyint(1) unsigned NOT NULL,
`category_Coordinate` tinyint(1) unsigned NOT NULL default '2',
`category_order` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),
KEY `idx_category_title` (`doc_title`) //新添加的表索引
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 |
让上述的程序应用走索引，数据库的负载恢复正常，性能恢复正常。
场景：数据库的负载飙升，CPU高达99%。
查看进程。通过猜测推理，定位了一些select语句
363478427 | apps_read | 192.168.1.113:48945 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '???') |
| 363478430 | apps_read | 192.168.1.113:48948 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '??????') |
| 363478434 | apps_read | 192.168.1.113:48952 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '?????????') |
| 363478437 | apps_read | 192.168.1.113:48955 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '????????') |
| 363478462 | apps_read | 192.168.1.113:48957 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '???????') |
| 363478500 | apps_read | 192.168.1.113:48960 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '????') |
| 363478511 | apps_read | 192.168.1.113:48963 | apps | Query | 0 | Sending data | select * from category_doc_info
where (doc_title = '??????') |
| 363478518 | apps_read | 192.168.1.113:48964 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = 'T2??') |
| 363478535 | apps_read | 192.168.1.113:48965 | apps | Query | 0 | Sending data | select * from category_doc_info
where (doc_title = '???') |
| 363478540 | apps_read | 192.168.1.113:48968 | apps | Query | 1 | Sending data | select * from category_doc_info
where (doc_title = '??') |
| 363478613 | apps_read | 192.168.1.113:48971 | apps | Query | 0 | Sending data | select * from category_doc_info
where (doc_title = '???') |
| 363478630 | apps_read | 192.168.1.113:48975 | apps | Query | 0 | Sending data | select *
查看此表的数据库表结构如下：
---------------------------------------------------------------------------------------------------------------------------------------------------------
| category_doc_info | CREATE TABLE `category_doc_info` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL COMMENT
`doc_title` varchar(255) NOT NULL COMMENT
`category_show` tinyint(1) unsigned NOT NULL COMMENT
`category_Coordinate` tinyint(1) unsigned NOT NULL default '2'
`category_order` tinyint(1) unsigned NOT NULL default '0'
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
发现只有一个组合索引。但是完全没有用到。
现实场景：都是where (doc_title = '???') 的语句
查看具体的一条SQL语句的执行计划，如下:
mysql> explain select * from category_doc_info where (doc_title = '独出新裁');
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | category_doc_info | ALL | NULL | NULL | NULL | NULL |
232717 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
可以发现都是全表查询，并且是高并发的访问上述语句。
经过分析，修改生产环境的表结构，如下:
| category_doc_info | CREATE TABLE `category_doc_info` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL,
`doc_title` varchar(255) NOT NULL',
`category_show` tinyint(1) unsigned NOT NULL,
`category_Coordinate` tinyint(1) unsigned NOT NULL default '2',
`category_order` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),
KEY `idx_category_title` (`doc_title`) //新添加的表索引
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 |
让上述的程序应用走索引，数据库的负载恢复正常，性能恢复正常。
