MYSQL 表优化

一、单表优化

1、准备

create table if not EXISTS `article`(
	`id` int(10) UNSIGNED not NULL PRIMARY key AUTO_INCREMENT,
	`author_id` int(10) UNSIGNED not NULL,
	`category_id` int(10) UNSIGNED not NULL,
	`views` int(10) UNSIGNED not NULL,
	`comments` int(10) UNSIGNED not null,
	`title` VARBINARY(255) not NULL,
	`content` text not NULL
);
insert into `article`(author_id,category_id,views,comments,title,content) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

2、案例

2.1 查询category_id为1且comments>1的情况下，views最多的article_id

mysql> select * from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
1 row in set
mysql> explain 
select * from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+

功能满足，但是通过explain可以看出没有使用任何优化而且还有filesort文件内排序。
开始优化：

• 构建符合索引

mysql> create index inx_article_ccv on article
(category_id,comments,views);
mysql> explain select * from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | inx_article_ccv | inx_article_ccv | 8       | NULL |    1 |      100 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set

从上面可以看到，type为range，表示使用到了范围索引而且使用到的索引为idx_article_ccv，但是还是有filesort，因为范围查找（>，<，<>，between）将会使索引失效，比如

mysql> explain select * from article where category_id=1 and comments=
1 order by views desc limit 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra               |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------+
|  1 | SIMPLE      | article | NULL       | ref  | inx_article_ccv | inx_article_ccv | 8       | const,const |    1 |      100 | Backward index scan |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------+
1 row in set

• 表示上一个索引不符合，接下来重新构建索引

mysql> alter table article add index idx_cv(category_id,views);
mysql> explain select * from article where category_id=1 and comments>1 order by views desc limit 1;

+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_cv        | idx_cv | 4       | const |    2 |    33.33 | Using where; Backward index scan |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
1 row in set

可以看到type为ref表示使用到了范围索引，而且为const毕竟=1嘛，没有文件内排序，使用到了索引idx_cv，OK，基本满足需求。

二、两表优化

1、准备

create table if not exists `class`(
	id int(10) UNSIGNED not null auto_increment,
	card int(10) UNSIGNED not null,
	PRIMARY key (id)
);
create table if not exists book (
	bookid int(10) UNSIGNED not null auto_increment,
	card int(10) UNSIGNED not null,
	primary key (bookid)
);
delimiter $$
SET GLOBAL log_bin_trust_function_creators=TRUE $$
create FUNCTION insertN(n int UNSIGNED) returns int
begin
	DECLARE i int default 0;
	WHILE i < n DO
		INSERT into class (card) values (FLOOR(1+(RAND()*20)));
		INSERT into book (card) values (FLOOR(1+(RAND()*20)));
		set i=i+1;
	end WHILE;
	return i;
end $$
delimiter ;
select insertN(30);

2、案例

mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   30 |      100 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   30 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set

全表扫描ALL，30*30，需要优化，可是加载哪个表呢？无法判断，只能一个个试

• 右边添加索引

mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL     | NULL    | NULL               |   30 |      100 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_card      | idx_card | 4       | advance.class.card |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
2 rows in set

可以看到右表使用到了索引，使用到的索引字段为advance库的class表的card字段

mysql> alter table book drop index idx_card
;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table class add index idx_card (card)
;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | index | NULL          | idx_card | 4       | NULL |   30 |      100 | Using index                                        |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |   30 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
2 rows in set

可以看到添加在class左表时，左表使用到了索引，但是没有过滤作用，还是30*30，索引类型index，表示使用到了索引扫描，但是效率比ref低下。

总结

连接时，总有一端是需要全部返回的，因此搜索的是另一端所以一般在另一端添加索引比较合适。
多表连接时，也是类似，索引一般建在另一端。
当然，如果建在左端，那右连接就可以搞定，相反其他类推。
在join时，一般使用小表驱动大表，毕竟小表是要全表扫描的，所以永远小表驱动大表
优先优化最内层的循环。
保证join语句中被驱动表上join条件字段已经创建索引。
当无法保证被驱动表的join条件字段被索引且内存资源充足时，可是设置joinBuffer大点。