nosqlmysql高级篇

mysql索引优化案例分析

本文字数: 693 阅读时长: 3 分钟
阅读次数次

单表

  • 1.创建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create tables if not exists article(
    	id int unsigned primary key not null auto_increment,
    	author_id int unsigned not null,
    	category_id int unsigned not null,
    	comments int unsigned not null,
    	views int unsigned not null,
    	tiltle varbinary(255) not null,
    	content TEXT(255) not null
    );

  • 2.插入数据

    1
    insert into article(author_id, category_id, comments, views, title, content) vlaues (1,1,1,1,’1’,’1’), (2,2,2,2,’2’,’2’),(1,1,3,3,’3’,’3’);

  • 3.explain

    1
    explain select author_id, id from article where category_id=1 and comments>1 oder by views limit 1;

    这个时候是全表扫描，type为all效率极低。

  • 4.建立联合索引
    我们按where筛选顺序建立联合索引

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

  • 5.再次explain

    1
    explain select author_id, id from article where category_id=1 and comments>1 oder by views limit 1;

    发现type为range extras有file_sort
    分析：由于comments是大于1的一个范围值，不具备索引查找条件，
    所以联合索引到comments就失效了，所以我们需要重新建立不包含comments的联合索引

  • 6.重新建立联合索引
    create index idx_article_cv on article(category_id, views);
    再次explain select author_id, id from article where category_id=1 and comments>1 oder by views limit 1;
    发现type变为ref，file_sort消失。
    优化结束。

两表

当通过连接进行的sql会如何进行优化sql？

  • 1.创建表

    1
    2
    3
    4
    5
    6
    7
    8
    create table if not exists class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
    );
    create table if not exists book(
    bookid int unsigned not null primary key auto_increment,
    card int unsigned not null
    );

  • 2.插入数据
    随便给两张表插入点数据

    1
    insert into class(card) values(floor(1+rand(20)));

  • 3.explain

    1
    explain select * from class left join book on class.card=book.card

    这个时候没有建立索引我们发现type都为all

  • 那我们到底是给left join的左表建立索引还是右表？
    分析：左边建立索引就是full index scan，type为index，而右边建立索引就是非唯一索引扫描，通
    过左边筛选出的card去找寻对应的card，所以右边type为ref，果断选择建立右表索引。

1
2
alter table book add index idx_book_card (card);
create index idx_book_card on book(card);
  • 4.再次explain，发现分析正确。建索引应该建立在从表上。
    （如果两表都建索引呢）？？？？？？

三表

  • 1.建表
    再新建一个表phone
    1
    2
    3
    4
    create table if not exists phone(
    phoneid int unsigned not null primary key auto_increment,
    card int unsigned not null
    );
    分析：其实思路和双表一样，两个外表都建立索引

join语句优化总结：

1.尽可能减少join语句中的nestedLoop的循环总次数：永远用小的结果集驱动大的结果集，小表驱动大表。
2.保证join语句被驱动表上join条件字段已经被索引：
当无法保证被驱动表的join字段被索引且内存资源充足的前提下，不要太吝惜joinbuffer的设置
3.以上都是mysql5.7的检测结果，msyql8后连表查询有所改变

分享到