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); 

 2、插入数据

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');  

 3、查询情景：分析查询 category_id 为 1 且comments 大于 1 的情况下，views 最多的 article_id
  直接查询：

SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

  分析查询语句：

  说明：type 是 ALL，即最坏的情况；Extra 里还出现了 Using filesort，也是最坏的情况，优化是必须的。
  优化：创建索引(有两种方式)，可以看出来索引的创建是根据where或者on中的查询条件创建的

ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
#或
CREATE INDEX idx_article_ccv ON article(category_id,comments,views); 

  再次分析查询语句：

  说明：type 变成了 range，这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引，为啥没用呢？这是因为按照 BTree 索引的工作原理，会先排序 category_id，如果遇到相同的 category_id 则再排序 comments，如果遇到相同的 comments 则再排序 views。但是 comments 字段在联合索引里处于中间位置，因comments > 1 条件是一个范围值(所谓 range)，MySQL 无法利用索引再对后面的 views 部分进行检索，即 range 类型查询字段后面的索引无效。就是说索引在排序的时候是根据创建索引时的顺序来的，先根据前面的排序，排好之后再依次根据后面的进行排序。
  当commets不是范围时：就会用到自定义的索引，效率很高

  删除第一次创建的索引：

DROP INDEX idx_article_ccv ON article;

  创建一个新的索引：

create index idx_article_cv on article(category_id,views); 

  再次Explain：可以看到，type 变为了 ref，Extra 中的 Using filesort 也消失了，结果非常理想

  Tip：在创建索引的时候不要把经常作为查询范围的字段放在索引中

二、两表
 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`));  

 2、插入数据

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
......
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
......
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

 3、sql分析：type为ALL，有优化空间

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

  优化：添加索引

ALTER TABLE `book` ADD INDEX Y ( `card`);

  再次分析：

  说明：可以看到第二行的 type 变为了 ref，rows 也变成了1，优化比较明显。这是由左连接特性决定的，LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
删除旧索引 + 新建索引(在主表建索引) + 第3次explain：发现新建的索引对于左连接不起作用，原因如④所述

DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;  

  然后来看一个右连接查询：

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

  说明：优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引。
  结论：左连接查询时建立右边表的索引(此时建立左侧表的索引没有作用)，右连接建立左边表的索引(此时建立右侧表的索引没有作用)。

三、三表
 1、在原来的基础上再创建表

CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`phoneid`)) ENGINE = INNODB;  

 2、插入数据

INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));  
......

 3、分析和优化

ALTER TABLE `phone` ADD INDEX z ( `card`);
ALTER TABLE `book` ADD INDEX Y ( `card`);
EXPLAIN SELECT * FROM class 
LEFT JOIN book ON class.card=book.card 
LEFT JOIN phone ON book.card = phone.card;

  说明：后 2 行的 type 都是 ref 且总 rows 优化很好，效果不错。因此索引最好设置在需要经常查询的字段和作为连接条件的外键字段上。