10. mysql单表索引和多表索引的建立原则

一、单表索引

1. 建表和添加数据

#建表 单表
CREATE TABLE article(
	id INT UNSIGNED 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(100) 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');

2. 建立索引及优化分析

#查询
SELECT * FROM `article`;
SHOW INDEX FROM `article`;
#案例1 查询`category_id`为1且`comments`大于1的情况下，`views`最多的`id`
SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
#分析sql语句
EXPLAIN SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
#分析结果：type:ALL 全表扫描，Extra中出现了Using filesort也是最坏的情况。所以必须建立索引优化查询

#添加索引
CREATE INDEX idx_article_ccv ON `article`(`category_id`,`comments`,`views`);
#继续分析：type为rang,Extra中还有Using filesort，需要继续优化
EXPLAIN SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;

#删除索引
DROP INDEX idx_article_ccv ON `article`;
#添加索引
CREATE INDEX idx_article_cv ON `article`(`category_id`,`views`);
#继续分析：type为ref,Extra中没有Using filesort 优化成功
EXPLAIN SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;

二、两表

1. 建表和插入数据

#两表
CREATE TABLE class(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	card INT UNSIGNED NOT NULL
);
CREATE TABLE book(
	bookid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	card INT 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 `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)));
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)));

2. 添加索引优化查询

#查询
SELECT * FROM `class`;
SELECT * FROM `book`;
#无索引性能分析 	type:都是ALL
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;

#添加索引优化
ALTER TABLE `book` ADD INDEX Y(`card`);
#添加book索引性能分析 	type: class为ALL
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;

#删除索引
DROP INDEX Y ON `book`;
#添加索引优化
ALTER TABLE `class` ADD INDEX Y(`card`);
#添加book索引性能分析 	type: book为ALL rows较大索引没有上一个好
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
#结论 左连接给右表添加索引
#删除索引
DROP INDEX Y ON `class`;
#添加索引优化
ALTER TABLE `book` ADD INDEX Y(`card`);
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;

#测试右连接
EXPLAIN SELECT * FROM `class` RIGHT JOIN `book` ON `class`.`card` = `book`.`card`;
#对换两表位置
EXPLAIN SELECT * FROM `book` RIGHT JOIN `class` ON `class`.`card` = `book`.`card`;

三、三表

1. 建表和插入数据

#建立第三张表
CREATE TABLE phone(
	phoneid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	card INT 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)));

2. 建立索引优化查询

#查看索引
SHOW INDEX FROM `class`;
SHOW INDEX FROM `book`;
#删除之前存在的索引
DROP INDEX Y ON `book`;
#创建查询语句
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `book`.`card` = `class`.`card` LEFT JOIN `phone` ON `book`.`card` = `phone`.`card`;
#无索引性能分析 	type:都是ALL 全部全表扫描

#添加索引优化
ALTER TABLE `phone` ADD INDEX z(`card`);
ALTER TABLE `book` ADD INDEX Y(`card`);
#添加book,phone索引性能分析 	type: class为ALL
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `book`.`card` = `class`.`card` LEFT JOIN `phone` ON `book`.`card` = `phone`.`card`;
#效果不错，给left后的表添加索引

3. 结论

1. 单表索引的添加相应根据查询的字段和where后的限定条件进行相应的调整，同时还要注意索引失效的情况发生。
2. 两表连接的索引创建，一般在left后的表添加索引。
3. 三表和两表类似。