MySql索引优化案例

单表优化

-- 创建新表
create table article(
    id int unsigned not null primary key auto_increment,
    author_id int unsigned not null,
    category_id int unsigned not null,
    views int unsigned not null,
    comments int unsigned not null,
    title varchar(255) not null,
    content text not null
)character set utf8 collate utf8_general_ci;

--插入数据
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');

--查询category_id为1且comments大于1的情况下,views最多的article_id
explain select * from article where category_id=1 and comments>1 order by views desc limit 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vq5NeA3U-1577071571703)(1D74CFA08A514D56904670B06B6D5421)]
type 为 all，且出现文件内排序(using filesort),也没有索引(没建)

-- 建立索引
create index idx_article_views on article(`views`);

-- 再分析
explain select * from article where category_id=1 and comments>1 order by views desc limit 1;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_article_views
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 33.33
        Extra: Using where; Backward index scan
1 row in set, 1 warning (0.00 sec)

-- 删除索引
drop index idx_article_views on article;

Backward index scan 是反向扫描，反向扫描的性能也是可以的

详细参考大佬文章：https://www.jb51.net/article/143464.htm

双表优化

-- 建表
create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
)character set utf8 collate utf8_general_ci;

create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
)character set utf8 collate utf8_general_ci;

-- 向两个表中插入随机数据，各插入20行数据
INSERT INTO class(card) VALUES(ROUND(RAND()*100));
INSERT INTO book(`card`) VALUES(ROUND(RAND()*100));

explain select * from class inner join book on class.card=book.card;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: class
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where; Using join buffer (Block Nested Loop)

rand()函数产生的是0到1之间的随机数

round()表示向下取证

-- 建立索引
create index idx_cc on class(card);
explain select * from book left join class on class.card=book.card\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: class
   partitions: NULL
         type: ref
possible_keys: idx_cc
          key: idx_cc
      key_len: 4
          ref: demo.book.card
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

这里建立1个索引就可以了，为什么呢？

驱动表的概念，mysql中inner join指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。

left join 写在左边的是驱动表，join后的是被驱动表

详情参考大佬文章：https://blog.youkuaiyun.com/Crystalqy/article/details/102730959

三个表的关联与双表是类似的，以小表驱动大表