一、索引单表优化

1.1 建表SQL

  1. CREATE TABLE IF NOT EXISTS `article` (
  2. `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3. `author_id` INT(10) UNSIGNED NOT NULL,
  4. `category_id` INT(10) UNSIGNED NOT NULL,
  5. `views` INT(10) UNSIGNED NOT NULL,
  6. `comments` INT(10) UNSIGNED NOT NULL,
  7. `title` VARBINARY(255) NOT NULL,
  8. `content` TEXT NOT NULL
  9. );
  11. INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
  12. (1, 1, 1, 1, '1', '1'),
  13. (2, 2, 2, 2, '2', '2'),
  14. (1, 1, 3, 3, '3', '3');
  15. SELECT * FROM article;

二、案例

  1. 查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

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

image.png
#结论: 很显然type 是ALL，即最坏的情况，Extra里还出现了 using filesort，也是最坏的情况，优化是必须的

优化1：

建索引：

  1. ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );

或者

  1. create index idx_article_ccv on article(category_id,comments,views);

都可以！
image.png
image.png

结论：是因为范围查询、order by和group by会中断联合索引。

type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。

但是我们已经建立了索引,为啥没用呢?

这是因为按照 BTree 索引的工作原理,

先排序 category_id,

如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。

当 comments 字段在联合索引里处于中间位置时,

因comments > 1 条件是一个范围值(所谓 range),

MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

优化1效果不理想！
优化2：
(先删除之前索引)

  1. DROP INDEX idx_article_ccv ON article;

建索引：

  1. ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;

或者

  1. create index idx_article_cv on article(category_id,views);

image.png
再次explain：

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

image.png
#结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
#优化2 ok！
**

二、两表优化案例

2.1建表SQL

  2. CREATE TABLE IF NOT EXISTS `class` (
  3. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `card` INT(10) UNSIGNED NOT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. CREATE TABLE IF NOT EXISTS `book` (
  8. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  9. `card` INT(10) UNSIGNED NOT NULL,
  10. PRIMARY KEY (`bookid`)
  11. );
  13. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  14. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  15. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  16. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  17. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  18. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  19. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  20. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  21. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  22. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  23. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  24. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  25. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  26. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  27. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  28. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  29. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  30. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  31. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  32. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  34. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  35. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  36. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  37. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  38. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  39. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  40. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  41. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  42. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  43. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  44. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  45. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  46. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  47. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  48. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  49. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  50. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  51. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  52. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  53. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2.2 案例一： LEFT JOIN

分析左连接：

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

image.png
可以发现type中有ALL

优化1：

在主表加索引
再explain
image.png**
发现行数没有变化，优化1没啥太大用

优化2：
在子表建索引

  1. DROP INDEX X ON class; #先删除优化1索引
  3. ALTER TABLE `book` ADD INDEX Y ( `card`); # 新建子表索引

image.png
#可以看到第二行的type变成了ref, rows也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
结论：

1、保证被驱动表的join字段已经被索引 被驱动表 join 后的表为驱动表 (需要被查询) 2、left join 时，选择小表作为驱动表，大表作为被驱动表。 但是 left join 时一定是左边是驱动表，右边是被驱动表

2.3案例二： RIGHT JOIN

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

image.png
可以看到RIGHT JION中索引还是放在左表中有用！
总结：
RIGHT JION索引还是放在左边中有用

2.4 其他

inner join 时，mysql会自己帮你把小结果集的表选为驱动表
mysql 自动选择。小表作为驱动表。因为 驱动表无论如何都会被全表扫描？。所以扫描次数越少越好

三、索引三表优化

  1. CREATE TABLE IF NOT EXISTS `phone`(
  2. `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3. `card` INT(10) UNSIGNED NOT NULL,
  4. PRIMARY KEY(`phoneid`)
  5. )ENGINE=INNODB;
  7. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  8. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  9. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  10. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  11. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  12. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  13. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  14. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  15. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  16. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  17. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  18. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  19. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  20. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  21. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  22. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  23. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  24. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  25. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
  26. INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

3.2案例

image.png
按照双表中的原则建索引：
image.png
可以看到type都是ref且rows优化很好！因此索引最好设置在需要经常查询的字段中。

4. Join语句优化

尽可能减少Join语句中的NestedLoop(嵌套循环)的循环总次数；“永远用小结果集驱动大的结果集”
优先优化NestedLoop(嵌套循环)的内层循环
保证Join语句中被驱动表上Join条件字段已经被索引
当无法保证被驱动表的Join字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置