MySQL学习--------索引优化和索引失效

1.索引优化

1.1单表索引优化

表结构：

`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'),
(3,3,3,3,'3','3');`

样例：查询category_id =1 且comments大于1的情况下，views最多的文章id

#查询语句
SELECT id,author_id FROM article a where a.category_id = 1 AND a.comments >= 1 ORDER BY a.views DESC LIMIT 1;

不建索引：

EXPLAIN SELECT id,author_id FROM article a where a.category_id = 1 AND a.comments >= 1 ORDER BY a.views DESC LIMIT 1;

#创建索引1
CREATE INDEX idx_article_ccv on article(category_id,comments,views);
EXPLAIN SELECT id,author_id FROM article a where a.category_id = 1 AND a.comments >= 1 ORDER BY a.views DESC LIMIT 1;

#分析：虽然type从All优化为了range，但是Extra仍然存在内排序Using filesort。索引字段存在范围查询，后面字段会索引失效

#创建索引方式2
CREATE INDEX idx_article_cv on article(category_id,views);
EXPLAIN SELECT id,author_id FROM article a where a.category_id = 1 AND a.comments >= 1 ORDER BY a.views DESC LIMIT 1;
#此索引较上面最优

1.2 两表索引优化

表结构

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);

#两表链接 左连接为例
select * from book b LEFT JOIN class c on b. card = c.card;

#不建立索引优化
EXPLAIN select * from book b LEFT JOIN class c on b. card = c.card;

#创建索引方式一   在book表上对card建立索引
CREATE INDEX idx_book_card on book(card);
EXPLAIN select * from book b LEFT JOIN class c on b. card = c.card;
#分析：虽然用到了索引，但是遍历的数目没有变化

#创建索引方式二   在class表上对card建立索引
CREATE INDEX idx_class_card on class(card);
EXPLAIN select * from book b LEFT JOIN class c on b. card = c.card;
#分析：用到了索引，遍历的数目减少。

两表连接时：左连接为右边的表创建索引，右连接为左表创建索引。如果索引已经创建，可以根据调整表的顺序（左连接变右连接或反之）来实现相同的效果。在连接时，要小表驱动大表。

1.3 多表优化

表结构

#前两张表为1.2的两张表
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

三表连接：

SELECT * from book b
LEFT JOIN class c on b.card = c.card
LEFT JOIN phone p on b.card = p.card;

不建立索引：

EXPLAIN SELECT * from book b
LEFT JOIN class c on b.card = c.card
LEFT JOIN phone p on b.card = p.card;

#创建索引方式一 在book和class的card字段创建索引
CREATE index idx_book_card on book(card);
CREATE index idx_class_card on class(card);
EXPLAIN SELECT * from book b
LEFT JOIN class c on b.card = c.card
LEFT JOIN phone p on b.card = p.card;

#创建索引方式二 在book和phone的card字段创建索引
CREATE index idx_book_card on book(card);
CREATE index idx_phone_card on phone(card);
EXPLAIN SELECT * from book b
LEFT JOIN class c on b.card = c.card
LEFT JOIN phone p on b.card = p.card;

#创建索引方式三 在class和phone的card字段创建索引  最优
CREATE index idx_class_card on class(card);
CREATE index idx_phone_card on phone(card);
EXPLAIN SELECT * from book b
LEFT JOIN class c on b.card = c.card
LEFT JOIN phone p on b.card = p.card;

2.索引失效

【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，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'员工记录表';

创建索引

CREATE INDEX idx_nameAgePos on staffs(name,age,pos);

2.1 索引全值匹配，个数和顺序全匹配

#不会失效
EXPLAIN SELECT * from staffs where name='July' and age = 23 and pos = 'dev';

2.2 最佳左前缀原则

#不会失效    用到索引的一个字段
EXPLAIN SELECT * from staffs where name='July';
#不会失效			用到索引的两个字段
EXPLAIN SELECT * from staffs where name='July' and age = 23;
#不会失效			用到索引的一个字段，缺少age字段，pos索引字段失效    如下图一
EXPLAIN SELECT * from staffs where name='July' and pos = 'dev';  
#会失效  			缺少name字段，整个索引失效   如下图二
EXPLAIN SELECT * from staffs where age = 23 and pos = 'dev';

图一

图二
总结：带头大哥(索引的第一个字段)不能死，中间兄弟(中间的字段)不能断。

2.3 不在索引列上做任何操作（计算，函数，类型转换），否则会索引失效

待验证

2.4 索引中如果存在范围查询，索引后面的列失效

# pos索引字段失效
EXPLAIN SELECT * from staffs where name = 'July' and age > 20 and pos = 'dev';

2.5 尽量使用覆盖索引，即select查询字段和索引字段顺序一致

EXPLAIN SELECT name,age,pos FROM staffs where name = 'July' and age = 23 and pos = 'dev';

2.6 索引字段列使用不等时（<>,!=），索引失效。

EXPLAIN SELECT * from staffs where name = 'July' and age != 23 and pos = 'dev';

2.7 索引字段列存在is null，is not null，索引会失效。

#age后面的字段失效
EXPLAIN SELECT * from staffs where name = 'July' and age is not NULL and pos = 'dev';

2.8 like以通配符开头（’%abc…’）mysql索引会失效

#索引字段失效
EXPLAIN SELECT * from staffs where name LIKE '%July%' and age = 23 and pos = 'dev';
#索引字段失效
EXPLAIN SELECT * from staffs where name LIKE '%July' and age = 23 and pos = 'dev';
#索引不失效
EXPLAIN SELECT * from staffs where name LIKE 'July%' and age = 23 and pos = 'dev';

如果需要使用like ‘%字符%’，且不让索引失效可以使用覆盖索引，select查询字段为索引字段。

2.9 字符串不加单引号，导致类型转换，索引失效

2.10 少用or连接，索引会失效