MySQL查询——索引优化

最新推荐文章于 2023-09-01 20:58:18 发布

武汉小喽啰

最新推荐文章于 2023-09-01 20:58:18 发布

阅读量141

点赞数

分类专栏：数据库相关文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43318134/article/details/115472432

版权

数据库相关专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 常规优化

1.1 单表优化

建表

CREATE TABLE 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` VARCHAR(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');

1）查询category_id为1且comments大于1的情况下，views最多的article_id

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

结果如图：

很显然，type是ALL，即最坏的情况，extra里还出现了Using filesort，也是最坏的情况，需要优化

优化：新建索引

哪些字段需要建索引？where条件后面需要用到的字段，order排序字段，因此这里可以建立三个字段的复合索引

-- 删除索引
DROP INDEX idx_views ON article;
-- 查看索引
SHOW INDEX FROM article;

-- 新建索引
CREATE INDEX idx_article_ccv ON article (category_id,comments,views);
-- 新建索引方式二
ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views)

再次执行，如图：

从图上可以看到，该查询用到了索引，且type=range了，有了一定的优化，但是，extra的using filesort还没解决，这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则在排序comments，如果遇到相同的comments则再排序views，当comments字段在联合索引里处于中间位置时，因comments>1是一个范围值（所谓range），mysql无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效

那么，我们可不可以不要comment作为索引字段了？

-- 删除索引
DROP INDEX idx_article_ccv ON article;
-- 查看索引
SHOW INDEX FROM article;

-- 新建索引
CREATE INDEX idx_article_ccv ON article (category_id,views);

结果如图：

优化完成。

1.2 两表优化

CREATE TABLE class(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL
);

CREATE TABLE book (
	bookid INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL
);

INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
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)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));

查询

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

如图：

type为all，需要优化

优化：两表查询，给关联字段添加索引

left join条件用于确定如何从右表搜索行，左边一定都有，所以，右边是关键点，一定要建立索引

新建索引：

CREATE INDEX idx_book_card ON book(card);

再次查询：

相反的，right join右边一定都有，所以，左边是关键点，一定要建索引

1.3 三表优化

在2的基础上新加表

CREATE TABLE phone (
	phoneid INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL
);

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

查询

发现type都是all，需要优化。

因为三张表，索引该怎么建立了？从2张表优化得出的left join索引建立在右表，因此可以在表book和phone表给card新建索引

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

再次分析查询，结果如图：

注意：其实这里还可以再给class建立索引

总结：索引最好建立在经常要查询的字段上。

1.4 join语句总结

1）尽可能减少join语句中的Nestedloop的循环总次数，“永远用小结果集驱动大的结果集”

2）优先优化NestedLoop的内层循环

3）保证join语句中被驱动的表上join条件字段已经被索引

4）当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

2. 索引失效

CREATE TABLE staff(
	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 staff(NAME,age,pos,add_time) VALUES
('z3',22,'manager',NOW()),
('July',23,'dev',NOW()),
('2000',23,'dev',NOW())

-- 新建索引
ALTER TABLE staff ADD INDEX idx_staff_nameAgePos(NAME,age,pos)

2.1 全值匹配

就是查询与索引字段的顺序、个数完全一致。

例如：SELECT * FROM staff WHERE NAME='July' AND age=25 AND pos='dev'

2.1.1 使用一个索引列

EXPLAIN SELECT * FROM staff WHERE NAME ='July';

可以看出，使用了索引

2.1.2 使用2个索引列

EXPLAIN SELECT * FROM staff WHERE NAME='July' AND age=25

可以看出，也使用了索引，同时key_len增加了，是因为相同结果下，精度越少长度越短越好，精度越多付出的代价越大所以长度越长。

2.1.3 全覆盖索引

EXPLAIN SELECT * FROM staff WHERE NAME='July' AND age=25 AND pos='dev'

从这三个运行结果可以看出，精度越高，key_len越大，查询结果越精确。

2.1.4 索引失效

EXPLAIN SELECT * FROM staff WHERE age=23 AND pos='dev';

可以看出，查询并未用到索引。如果where后面只跟pos，一样不会用到索引，如图：

但是，前面使用name做查询条件的时候，可以使用到索引，这是因为索引顺序是name-age-pos，而查询中没有用到name，这违背了最佳左前缀法则。即带头大哥不能死。

2.2 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

例如：

EXPLAIN SELECT * FROM staff WHERE NAME='July' AND pos='dev'

执行结果如图：

虽然用到了索引，但是key_len=74，很明显，只有name使用了索引，pos并没有使用到索引，也就是最左前缀法则的不跳过索引中的列，即中间兄弟不能断。

2.3 索引列上少计算

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

例如：

EXPLAIN SELECT * FROM staff WHERE LEFT(NAME,4) = 'July';

查询结果如图：

因为在索引列name上使用了函数left，索引失效了。

如果函数使用在兄弟列（非索引头部列），则头部列会使用索引，如图：

name使用了索引，但是pos没有使用索引。

2.4 存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM staff WHERE NAME='July' AND age > 22 AND pos='dev'

如图：

索引只用到了name，位于索引列的第二位age是范围查询，因此导致pos没有使用索引。

2.5 尽量使用覆盖索引

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

2.6 常见的索引失效导致的全表扫描

1）mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

2）is null，is not null 也无法使用索引

3）字符串不加单引号索引失效

4）少用or，用它连接的时候，会索引失效

2.7 like使用

like以通配符开头('%abc...')，mysql索引失效会变成全表扫描的操作

注意：如果只在右侧加%，那么还是会走索引，。即like百分在右边

在实际生产中，不可避免的可能会使用到双%，这时候怎么解决like '%字符串%' 时索引不被使用的方法了？

对于like使用双%，比较推荐的方法是使用覆盖索引

例如：

建表：

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

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

建索引之前，分析结果如图：

建立复合索引：

CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age)

再次执行分析，如图：

一般性建议：
1）对于单键索引，尽量选择针对当前query过滤性更好的索引
2）在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
3）在选择索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
4）尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化口诀：

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！