一、单表索引
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`;
SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
EXPLAIN SELECT `id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
CREATE INDEX idx_article_ccv ON `article`(`category_id`,`comments`,`views`);
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`);
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`;
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
ALTER TABLE `book` ADD INDEX Y(`card`);
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
DROP INDEX Y ON `book`;
ALTER TABLE `class` ADD INDEX Y(`card`);
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`;
ALTER TABLE `phone` ADD INDEX z(`card`);
ALTER TABLE `book` ADD INDEX Y(`card`);
EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `book`.`card` = `class`.`card` LEFT JOIN `phone` ON `book`.`card` = `phone`.`card`;
3. 结论
- 单表索引的添加相应根据查询的字段和where后的限定条件进行相应的调整,同时还要注意索引失效的情况发生。
- 两表连接的索引创建,一般在left后的表添加索引。
- 三表和两表类似。