MySQL高级应用 - 索引优化

IllTamer
120 阅读4分钟

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

索引优化

  • 索引分析

    • 单表

      SQL

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

      案例

      查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 author_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 -> 尝试建立复合索引：因为条件中 comments>1 (range)，range类型查询字段后面的索引无效，故排除此项。

      CREATE INDEX `idx_article_cv` ON article(`category_id`, `views`);

      SHOW INDEX FROM `article`; # 查看表的索引

      ​ 发现 type=ref、Extra为Using where、ref=const

    • 双表

      SQL

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

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

      案例

      两表联合查询 发现type都是ALL

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

      尝试添加索引 (左连接加在右表)

       CREATE INDEX idx_book_card ON book(card);

      分析：左连接前提下加在右表优化更明显，由左连接特性可知LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，右边是关键点一定要建立索引。右连接同理类推。

    • 三表

      SQL

      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;

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

      案例

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

      CREATE INDEX idx_phone_card ON phone(`card`);
CREATE INDEX idx_book_card ON book(`card`);

      ​ 新增后两表的索引，后两行的type变为ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

      总结

      1. 尽可能减少JOIN语句中的嵌套循环(NestedLoop)的循环总次数，永远用小结果(class)集驱动大结果集(book)。
      2. 优先优化嵌套循环(NestedLoop)的内层循环。
      3. 保证JOIN语句被驱动表上JOIN条件字段已经被索引(如三表优化中的card字段索引)。
      4. 当无法保证驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太再有JoinBuffer的设置。

  • 索引失效

    SQL

    CREATE TABLE staffs (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`NAME` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` INT NOT NULL DEFAULT 0 COMMENT '年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表';

    INSERT INTO staffs(`Name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`Name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`Name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

    CREATE INDEX idx_staffs_nap ON staffs(`name`,`age`,`pos`);

    索引失效常见原因

    1. 最好使用全值匹配 (所有匹配项皆为常数)

    2. 遵守最左前缀法则

      如果索引了多列（单列可以type=index），查询从索引的最左前列开始并不跳过索引中的列。

    3. 不在索引列上做任何操作

      如计算、函数、(自动 / 手动)类型转换，会导致索引失效。

      EXPLAIN SELECT * FROM staffs WHERE left(Name,4) = 'July'; # 计算

    4. 索引中范围条件右边的列全失效

      EXPLAIN SELECT * FROM staffs WHERE Name='July'AND age>3 AND pos='dev'; # pos索引未用

    5. 尽量使用覆盖索引

      覆盖查询 - 只访问索引的查询 (保证索引列和查询列一致)，少用 SELECT *。因为 * 时可能包含其它不需要列，造成 Extra - Using Where 而不是 Using Index，同时 * 也会被解释成 列增加耗时。

    6. MySQL在使用 != / > / < / IS NULL / IS NOT NULL 时无法使用索引造成全表扫描

    7. LIKE ''%abc...' (以通配符开头) 会导致索引失效

    仅以通配符结尾会使用索引，type变为range，但以通配符结尾的查询数据可能出错，如何解决？

    SQL

    CREATE TABLE `tbl_user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(20) DEFAULT NULL,
    `age` INT(11) DEFAULT NULL,
	`email` VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    INSERT INTO tbl_user(`NAME`,`age`,`email`)VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(`NAME`,`age`,`email`)VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(`NAME`,`age`,`email`)VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(`NAME`,`age`,`email`) VALUES('4aa4',21,'d@163.com');

    CREATE INDEX idx_user_na ON tbl_user(`Name`,`age`);

    SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'; 如何解决 LIKE %% 索引不生效的情况

    解决方案

    创建并使用覆盖索引，将 type=NULL->index

    1. 字符串不加单引号导致失效

      根本原因：不加单引号致使MySQL认为输入类型为数字，底层自动转换类型相当于违反第3条。

    2. 少用OR，连接时会导致索引失效

avatar
文章
阅读
粉丝