数据库进阶の索引优化（一）索引优化怎么优化

MySQL 的设置以及常用 SQL 语句对于数据库管理至关重要。以下是一些常见操作示例。

首先是创建表，例如创建一个名为 person 的表：

CREATE TABLE IF NOT EXISTS person(

id INT(10) AUTO_INCREMENT,

age VARCHAR(20),

name VARCHAR(50),

PRIMARY KEY(id)

);

若要查看表的结构详情，可使用：

DESCRIBE person

在数据库操作方面，可进行数据库的创建与删除等操作：

CREATE DATABASE test01

CREATE DATABASE test02

DROP DATABASE test02;

还能查看所有数据库：

SHOW DATABASES;

使用某个数据库：

USE test01

查看该数据库中的表：

SHOW TABLES;

创建一个名为 student 的表：

CREATE TABLE student(

id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',

name VARCHAR(20) NOT NULL COMMENT '名字',

class INT NOT NULL COMMENT '班级号'

) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '学生列表';

删除表：

DROP TABLE student;

向 student 表中插入数据：

INSERT INTO student

(name, class)

VALUES

("小王", 1),

("小李", 2),

("小孙", 3);

查询 student 表中的所有数据：

SELECT * FROM student;

查询特定列：

SELECT class, name FROM student;

查询去重后的特定列：

SELECT DISTINCT class, name FROM student;

查询满足条件的数据：

SELECT * FROM student WHERE class = 1

一.单表索引优化

1.1 建表：

创建 article 表：

CREATE TABLE article(

id INT(10) NOT NULL 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,

PRIMARY KEY(id) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

1.2 往表内插值：

INSERT INTO article(author_id, category_id, views, comments, title, content)

VALUES

(1, 1, 1, 1, "1", "2"),

(2, 2, 2, 2, "2", "2"),

(1, 1, 3, 3, "3", "3");

1.2 查询

案例一：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id。

EXPLAIN SELECT

id,

author_id

FROM

article

WHERE

category_id = 1

AND comments > 1

ORDER BY

views DESC

LIMIT 1;

更新数据：

UPDATE person SET name = "小王", class = 5 WHERE id = 1;

之前查询中，type 是 ALL，而且 extra 里还出现了 using filesort，这是不理想的情况，需要优化。

建立索引：

CREATE INDEX idx_article_ccv ON article(category_id, comments, views);

索引是依据 where 后面的条件和排序的关键字来创建。

查看索引：

SHOW INDEX FROM article;

索引会按照 1, 2, 3 的顺序进行排序。

再次执行查看命令：

EXPLAIN

SELECT

FROM article

WHERE

category_id = 1

AND

comments > 1

ORDER BY

views DESC

LIMIT 1;

虽有优化，但 using filesort 仍存在。

这是因为索引无法对范围进行索引，在 comments > 1 这个范围处存在问题，所以要新建索引：

CREATE INDEX idx_article_cv ON article(category_id, views);

EXPLAIN

SELECT

FROM

article

WHERE

category_id = 1

AND comments > 1

ORDER BY

views DESC

LIMIT 1;

二.索引两表优化

2.1 建表

建立 class 和 book 两个表：每个表里随机的产生 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)));

SELECT * FROM book INNER JOIN class ON book.card = class.card;

筛选出来的结果：15 条记录

内连接就是做笛卡尔积，然后把所有符合 on 条件的都提取出来

左连接：

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

左连接查询共 22 条记录。

2.2 建立索引

ALTER TABLE book ADD INDEX Y(card);

再次执行查找命令：

EXPLAIN SELECT

FROM

class

LEFT JOIN book ON class.card = book.card;

2.3 换个地方建立索引

删除索引：

DROP INDEX Y ON book;

在 class 上建立索引：

ALTER TABLE class ADD INDEX Y(card);

这种情况是由左连接的特性决定的，left join 条件用于确定如何从右表搜索行，左边一定能够是有的，所以左边才是关键点，一定要建立索引。

三.索引三表优化系列

在原来两个表的基础上再建一个表，也是 20 行，内容如下：

SELECT * FROM

class LEFT JOIN book

ON class.card = book.card

LEFT JOIN phone

ON book.card = phone.card;

结果有 25 条记录：

explan 效果图：

建立索引：

查询语句：

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

索引的建立索引从 join 的内部开始往外建立，先见 phone 的索引，再建立 book 的索引，最后的 class 是不需要建立索引的。

ALTER TABLE phone ADD INDEX Z(card);

ALTER TABLE book ADD INDEX Y(card);

再执行该查询语句：

EXPLAIN

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

从 rows 上来看，有明显的提升

join 语句的优化：

尽可能的减少 join 语句中的 nested loop 的内循环次数，永远用小结果驱动大的结果集

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

当无法保证驱动表中的 join 条件字段被索引且内存资源内存资源充足的情况下，不要太吝惜 join buffer 的设置

四.索引失效

全值匹配我最爱。

最佳左前缀法则

不在索引列上做任何操作类型转换）,会导致索引失效二

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

尽量使用覆盖索引，减少 select 之类的操作

mysq 中使用不等于的时候无法使用索引会导致全表扫描

isnull，isnotnull 也无法使用索引

like 以通配符开头的mysql 索引失效会变成全表扫描的操作，最好改用右边使用通配符

字符串不加单引号，索引失效，就是相当于把字符串"2000"转成数字 2000

少用 or，用他来链接，索引会失效

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

如果使用 like 必须要有百分号的话，尽量将百分号放在右边，如果两边必须有百分号的话，那么就是用覆盖索引。

索引优化的口诀：

大头大哥不能死

中间兄弟不能断

最佳左前缀原则

索引列上无计算

like 百分加右边

范围之后全失效

字符串里有引号

下边的小例子，重点记忆一下

参考文献