六、索引的创建和删除
6.1 索引的创建
1.创建表的时候创建2.在已存在的表上创建 6.2 索引的删除 七、索引设计原则
7.1 哪些情况适合索引7.2 哪些情况不适合索引 八、索引失效场景
1.违反最左前缀原则2.在索引列上用运算、函数、类型转换导致失效
2.1 索引列上使用运算2.2 索引列使用函数2.3 索引列类型转换 3.LIKE通配符的问题4.范围条件右边的列索引失效5.<>、NOT、IN、NOT EXISTS失效6. OR 前后存在非索引的列导致索引失效 九、查询优化
9.1 关联查询优化
JOIN 语句的原理Simple Nested-Loop JoinIndex Nested-Loop JoinBlock Nested-Loop Join小结 9.2 子查询优化9.3 排序优化9.4 Group By优化9.5 优先考虑覆盖索引9.6 字符串的前缀索引 十、索引下推ICP
10.1 举例：10.2 使用ICP前后内部过程10.3 ICP的使用条件
往期：
MySQL深入学习 — mysql逻辑架构，SQL的执行流程，数据库缓冲池MySQL深入学习 — MySQL存储引擎，InnoDB、MyISAM索引的数据结构 六、索引的创建和删除
索引的分类：
从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引按照 作用字段个数进行划分，分成单列索引和联合索引
不同的存储引擎支持的索引类型也不一样
InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive ：不支持 B-tree、Hash、Full-text 等索引； 6.1 索引的创建 1.创建表的时候创建
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
UNIQUE 唯一索引、FULLTEXT 全文索引、SPATIAL 空间索引INDEX 与 KEY 是同义词，两者作用相同index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择，可以选多个列组成联合索引length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度ASC或 DESC指定升序或者降序的索引值存储
CREATE TABLE book(
book_id INT,
book_name VARCHAr(100),
year_publication YEAR,
INDEX(year_publication) #普通索引，也是单列索引
INDEX multi_idx(book_id,book_name) #多列索引
UNIQUE INDEX uk_idx_id(book_id) #唯一索引
);
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT,
student_no VARCHAr(200),
student_name VARCHAr(200),
PRIMARY KEY(id) #主键索引
);
CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`) #全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
#不同于like方式查询
SELECt * FROM papers WHERe content LIKE ‘%查询字符串%’;
#全文索引可以用match+against方式查询:
SELECt * FROM papers WHERe MATCH(title,content) AGAINST (‘查询字符串’);
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo) #空间索引
) ENGINE=MyISAM;
2.在已存在的表上创建使用全文索引前，搞清楚版本支持情况；全文索引比 like + % 快 N 倍，但是可能存在精度问题；如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
1.使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
2.使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]6.2 索引的删除
1.使用ALTER TABLE删除
ALTER TABLE table_name DROp INDEX index_name;
2.使用DROP INDEX删除
DROP INDEX index_name ON table_name;七、索引设计原则 7.1 哪些情况适合索引
- 字段的数值有唯一性的限制
唯一性索引的值是唯一的，可以更快通过索引确定某条记录 频繁作为WHERe 查询条件的字段
某个字段在SELECT语句的 WHERe 条件中经常被使用到，那么就需要给这个字段创建索引了 经常 GROUP BY 和 ORDER BY 的列
排序操作会浪费很多时间，如果为其建立索引，可以有效的避免排序操作，相当于事先用索引为其排序 UPDATE、DELETE 的 WHERe 条件列
在UPDATE或DELETE时，首先要用WHERe查询出来，如果添加了索引，找到这个要修改或删除的记录的效率就会提高但是后续的更新操作，更新的字段是非索引字段效率反而会高，因为非索引字段不需要维护索引所以，WHERe条件加索引，尽量不要修改索引字段 DISTINCT字段需要创建索引
如果需要去重，对该字段创建索引也会提升查询效率 使用字符串前缀创建索引
如果索引字段的值很长，最好使用值的前缀来索引 区分度高(散列性高)的列适合作为索引
区分度高，指hash运算后尽量分布范围大，比如性别，只有男女就是区分度小，不适合加索引 在多个字段都要创建索引的情况下，联合索引优于单值索引使用最频繁的列放到联合索引的左侧
按照最左前缀原则，MySQL扫描索引是从左往右的，排序也是从左往右的比如：index(k1,k2)这样一个联合索引，索引树会先给k1排序，在保证k1有序的情况下再给k2排序
- 在where中使用不到的字段，不要设置索引小表最好不用使用索引有大量重复数据的列上不要建立索引
举例：性别，这种区分度不高，不适合建索引 避免对经常更新的表创建过多的索引不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等 删除不再使用或者很少使用的索引不要定义冗余或重复的索引
首先建一个表：
CREATE TABLE `demo`(
`id` INT(10) NOT NULL AUTO_INCREMENT,
`age` INT(2) NOT NULL,
`name` varchar(20) NOT NULL,
`details` varchar(20) NOT NULL,
`comment` varchar(20) NOT NULL,
PRIMARY KEY(`id`),
#INDEX idx_age(age),
#INDEX idx_age_name(age,name),
#INDEX idx_name(name),
INDEX idx_age_name_details(age,name,details)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
1.违反最左前缀原则
MySQL匹配是从左到右匹配的，对于idx_age_name_details(age,name,details)，在where条件列中从左往右不跳过索引中的列，就符合最左前缀原则，就是有效的索引
违背原则的案例：
未从最左列开始，索引失效
跳过索引中间列，只用了部分索引
结论：带头大哥不能死，中间兄弟不能断
2.在索引列上用运算、函数、类型转换导致失效 2.1 索引列上使用运算where age + 1 = 18使用了函数，没走idx_age_name_details(age,name,details)索引，使用了全表扫描
2.2 索引列使用函数where age=18 AND SUBSTRINg(name,3)='zha' ，在索引第二个参数上使用了函数，所以索引只走了age一个参数。
可以看到Extra上写了using index condition，意思是搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
2.3 索引列类型转换首先在name上加个索引
可以明显发现，使用类型转换没有走索引
3.LIKE通配符的问题在SQL语句中LIKE后面的通配符的%或_位置也有讲究
WHERe name LIKE 'zhang%'，通配符写在后面，会走索引WHERe name LIKE '%zhang'，通配符写在前面，不会走索引，会全表扫描
4.范围条件右边的列索引失效建立一个联合索引：idx_name_age_details(name,age,details)
很明显，在中间使用age>20后，索引的key_len就只算了name和age，没算details了
5.<>、NOT、IN、NOT EXISTS失效查询条件为等值查询或范围查询时，索引可以根据查询条件去查找对应的条目。
但是用<>、NOT、IN、NOT EXISTS意味着全表扫描查询不等于，不属于的
注意：IS NULL可以使用索引，而IS NOT NULL不可以
如图，使用的都是全表扫描
6. OR 前后存在非索引的列导致索引失效name上有索引，而 comment上没加索引，此时索引失效
九、查询优化 9.1 关联查询优化 JOIN 语句的原理来看如下语句：
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);
如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表。改用 straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。所以，在这种方式下，t1是驱动表，t2是被驱动表
这条SQL语句对应的流程是：
- 从表t1中读入一行数据 R；从数据行R中，取出a字段到表t2里去查找；取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；重复执行步骤1到3，直到表t1的末尾循环结束。
这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为 “Index Nested-Loop Join” ，简称NLJ。
在这个流程中，对驱动表做了全表扫描
MySQL中Nested-Loop Join 一共有三种方法：
Simple Nested-Loop Join。简单嵌套循环连接Index Nested-Loop Join。索引嵌套循环连接Block Nested-Loop Join。缓存块嵌套循环连接 Simple Nested-Loop Join
Index Nested-Loop Join整个流程是两层循环，遍历左边嵌套遍历右边，如果左边有一万条数据，右边有一万条数据，整个Join就是一万乘一万
Block Nested-Loop Join对于开头提到的这种，遍历左边，当匹配索引时再去右边查询，减少了内层循环
这种方式利用缓冲池，t1一次性可以缓存多条数据到Join Buffer，然后将Join Buffer中的数据批量与t2进行对比，从而减少内循环次数
Join Buffer可以通过修改join_buffer_size参数调整缓存大小
小结保证被驱动表的JOIN字段已经创建了索引需要JOIN 的字段，数据类型保持绝对一致。LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。衍生表建不了索引 9.2 子查询优化
子查询执行效率不高，原因：
- 执行子查询时，会创建一个临时表保存内层查询，查询完毕后再撤销这些表。这样会过多消耗CPU和IO资源，导致慢查询临时表不存在索引，查询性能不好
在MySQL中，可以使用连接（JOIN）查询来替代子查询
9.3 排序优化- SQL 中，可以在 WHERe 子句和 ORDER BY 子句中使用索引
目的是在WHERe 子句中避免全表扫描，在 ORDER BY 子句 避免使用 FileSort排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。 尽量使用 Index 完成 ORDER BY 排序。如果 WHERe 和 ORDER BY 后面是相同的列就使用单列索引； 如果不同就使用联合索引。无法使用 Index 时，需要对 FileSort 方式进行调优。
INDEX a_b_c(a,b,c) order by 能使用索引最左前缀 - ORDER BY a - ORDER BY a,b - ORDER BY a,b,c - ORDER BY a DESC,b DESC,c DESC 如果WHERe使用索引的最左前缀定义为常量，则order by 能使用索引 - WHERe a = const ORDER BY b,c - WHERe a = const AND b = const ORDER BY c - WHERe a = const ORDER BY b,c - WHERe a = const AND b > const ORDER BY b,c 不能使用索引进行排序 - ORDER BY a ASC,b DESC,c DESC - WHERe g = const ORDER BY b,c - WHERe a = const ORDER BY c - WHERe a = const ORDER BY a,d - WHERe a in (...) ORDER BY b,c
举例：
SELECt SQL_NO_CACHE * FROM student WHERe age = 30 AND stuno <101000 ORDER BY NAME ;
对于这样的SQL，我们可以
建立索引去掉filesort，idx_age_name(age,name)
进一步优化，idx_age_stuno_name(age,stuno,name)
对于filesort的优化
尝试提高 sort_buffer_size尝试提高 max_length_for_sort_dataOrder by 时select * 是一个大忌。最好只Query需要的字段 9.4 Group By优化
group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引group by 先排序再分组，遵照索引的最佳左前缀法则当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置where效率高于having，能写在where限定的条件就不要写在having中了减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行 以内，否则SQL会很慢。 9.5 优先考虑覆盖索引
覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
比如说：select * from demo where id = 1，id是主键，主键建立的聚簇索引树中包含了所有表的字段，不需要回表查询，这属于一种覆盖索引
又比如说：建立了一个联合索引idx_name_age_details(name,age,details)
执行sql语句select name,age from demo where age = xxx AND name = xxx AND details = xxx。
在Extra中显示Using index就表示使用了覆盖索引，无需回表查询了
覆盖索引的好处：
- 避免Innodb表进行索引的二次查询(回表)可以把随机IO变成顺序IO加快查询速度
Alibaba的规范中，字符串是要建立前缀索引的
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
选择前缀的长度很重要，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
举例：
alter table teacher add index index1(email); #或 alter table teacher add index index2(email(6));
对于index1：
- 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录，取得ID2的值；到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’ zhangssxyz@xxx.com ’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
对于index2：
- 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；到主键上查到主键值是ID1的行，判断出email的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃；取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然 后判断，这次值对了，将这行记录加入结果集重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。这个过程中，反复扫描了多次，性能不是很好
结论：
前缀索引不是越短越好，需要保证区分度，散列性
十、索引下推ICP注意：前缀索引对覆盖索引的影响
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
10.1 举例：拿之前的表举例，我们建立了一个联合索引idx_name_age_details(name,age,details)
然后此时来了一个需求，需要匹配姓张的年龄为20岁的用户：
SELECt * FROM demo WHERe name LIKE '张%' AND age = 20
在Mysql5.6之前，流程是这样的：
首先找到名字姓张的，然后忽略age，拿到这4个ID，直接回表查询
总共需要回表四次
在Mysql5.6之后，流程是这样的：
首先找到名字姓张的，然后直接在联合索引的下一个字段age进行过滤，跳过了age不为18的两个记录
总共只需要回表两次
10.2 使用ICP前后内部过程不使用ICP索引扫描的过程：
storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。
使用ICP索引扫描的过程：
storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。server层：对返回的数据，使用table filter条件做最后的过滤。
使用前后成本差别：
使用前，存储层多返回了需要被index filter过滤掉的整行记录使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。 10.3 ICP的使用条件
- 只能用于二级索引(secondary index)explain显示的执行计划中type值（join 类型）为range 、 ref、 eq_ref或者 ref_or_null并非全部where条件都可以用ICP筛选 ，如果where条件的字段不在索引列中，还是要读取整表的记录 到server端做where过滤。ICP可以用于MyISAM和InnnoDB存储引擎MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持当SQL使用覆盖索引时，不支持ICP优化方法
