MySQL优化（3）：索引
MySQL优化中，最重要的优化手段就是索引，也是最常用的优化手段
索引简介：
索引：关键字与数据位置之间的映射关系
关键字：从数据中提取，用于标识，检索数据的特定内容
目的：加快检索
索引检索为什么快：
（1）关键字相对于数据本身，量较小
（2）关键字都是排序好的
MySQL中索引的类型：
普通索引，唯一索引，主键索引，全文索引
不同的类型只是对关键字的限制不同
普通索引：多索引关键字没限制，有长度限制
唯一索引：要求记录提供的关键字不能重复
主键索引：要求关键字不能重复而且不能为NULL
全文索引：不支持中文，后续细讲
索引的语法：
查看索引：
SHOW CREATE TABLE [table-name];
比如查到PRIMARY KEY(‘id’)，就是一个主键索引
创建索引：需要修改表结构和创建表时候完成，基于不同的类型，方式也不同
同时创建四个索引，由于使用到了全文索引，这里使用MYISAM引擎
CREATE TABLE USER( ID INT AUTO_INCREMENT PRIMARY KEY, FIRST_NAME VARCHAR(16), LAST_NAME VARCHAR(16), SN VARCHAR(16), INFORMATION TEXT, KEY(FIRST_NAME,LAST_NAME), UNIQUE KEY(SN), FULLTEXT KEY(INFORMATION) )ENGINE=MYISAM;
索引可以命名，比如KEY NAME (FIRST_NAME,LAST_NAME)
这句话创建了一个基于FIRST_NAME和LAST_NAME的复合普通索引
UNIQUE KEY(SN) 创建了基于SN的唯一索引，默认以字段名命名索引
最后一个全文索引很鸡肋，基本不会用
在修改表结构的时候创建索引：
ALTER TABLE USER( ADD KEY(FIRST_NAME,LAST_NAME), ADD UNIQUE KEY(SN), ADD FULLTEXT KEY(INFORMATION) )
删除索引：
ALTER TABLE [table-name] DROP PRIMARY KEY; ALTER TABLE [table-name] DROP KEY [key-name];
一般不简易删除主键索引，记录是按照主键来排序的，设计主键要注意一定与业务逻辑无关
执行计划：
执行计划：当MySQL执行SQL语句时，会分析、优化、形成执行计划后按照执行计划来执行
在执行计划中可以清楚的看到当前的查询是否需要用到索引：
EXPLAIN SELECT * FROM [table-name] WHERE ID<20;
结果中有一样：KEY:PRIMARY，代表该查询语句会用到主键索引
索引使用的场景：
如果两张表，学生和班级表，多对一的关系，导入较多的记录来测试
（1）WHERE查询
EXPLAIN SELECT * FROM STUDENT WHERE ID=123456
和上面的例子一样，可以看到使用到了主键索引
EXPLAIN SELECT * FROM STUDENT WHERE USERNAME="XXX"
这句话执行后会发现：KEY:NULL，没有索引
我们给它加上索引：（这里的INDEX和上文的KEY都可以）
ALTER TABLE STUDENT ADD INDEX (USERNAME);
查看执行计划后可以发现：KEY:USERNAME，说明使用到了新建的索引
（2）ORDER BY排序
我们有可能会遇到以下的情况：
SELECT * FROM STUDENT ORDER BY USERNAME;
查看执行计划后可以发现没有使用到任何索引，并且看到了Extra:Using Filesort，使用到了外部文件排序，性能更低，需要先将数据读取到内存，分段读取合并排序
提高效率的方式是增加索引：
ALTER TABLE STUDENT ADD INDEX (USERNAME);
现在查看执行计划后，可以发现使用到了USERNAME索引，并且没有使用外部文件排序，性能会有明显提升
（3）JOIN 连接
使用到这条语句：
SELECT C.* COUNT(S.ID) FROM CLASS C JOIN STUDENT S ON C.ID=S.CLASS_ID GROUP BY C.ID;
当数据量巨大的时候，这句话要执行5S以上
查看执行计划后，发现其中一张表没有索引，且使用到了外部文件排序
解决：
ALTER TABLE STUDENT ADD INDEX (CLASS_ID);
执行后发现速度明显提升，并且两张表都使用到了索引，没有外部文件排序
（4）索引覆盖
前三条很重要，是必须做的优化，这条只是一个现象
比如我们使用这条语句：建立一个复合索引
ALTER TABLE STUDENT ADD INDEX (FIRSTNAME,LASTNAME);
然后再执行：这句话没有使用到以上三种情况
SELECT FIRSTNAME,LASTNAME FROM STUDENT;
但是查看计划后，发现还是使用到了索引，并且Extra:Using index，说明这句话只使用了索引来完成
如果执行这句话
SELECT FIRSTNAME,LASTNAME,USER FROM STUDENT;
再查看计划后发现没有使用到了索引，并且进行了全表扫描
两次的差异只是多了一个USER字段，而复合索引没有包含该字段
总结：MySQL的查询优先使用了索引，由于索引覆盖，建议SELECT后面只写有必要的字段，被覆盖的可能性就会提升，尽可能地优化
语法注意细节：
（1）字段需要独立出现
SELECT * FROM STUDENT WHERE ID+1=20;
这句话是能执行成功的，ID是主键，查看计划后却没有使用到主键索引
字段没有独立出现，不能触发该字段上的索引，避免这种情况
（2）LIKE查询不能以通配符开头
SELECT * FROM STUDENT WHERE USERNAME LIKE '%A%';
这句话无法使用到索引，如果是以下的情况，那么会用到索引
SELECT * FROM STUDENT WHERE USERNAME LIKE 'A%';
字符串比较中，不能使用包含的逻辑，比如查询包含Java的字符串，不能写'%Java%'，效率过低
解决办法：全文索引，但是MySQL全文索引很鸡肋，应该使用第三方的比如ES，Solr
（3）复合索引的右侧字段不能独立使用索引
已有INDEX NAME(FIRSTNAME,LASTNAME);
使用语句，这句话使用到了索引
SELECT * FROM STUDENT WHERE FIRSTNAME='XXX';
而下面这句话没有使用到索引
SELECT * FROM STUDENT WHERE LASTNAME='XXX';
原因：复合索引是按照左侧字段排序的，如果左侧字段相同再用右侧字段排序，总体上来看，右侧字段是未排序的
既然这样为什么还要建复合索引呢？以下这种情况
SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' AND LASTNAME LIKE 'XX%';
这句话如果建立两个索引，那么计算两个索引的交集会更慢，所以需要复合索引
如果遇到上面的情况，再给LASTNAME建立一个索引即可
（4）早期版本NULL值无法使用索引
SELECT * FROM STUDENT WHERE FIRSTNAME=NULL;
新版本无需关心这一条
（5）OR语法保证两边的条件都有索引可用
SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' OR USER LIKE 'XX%';
如果USER没有索引，那么还是会全表查询
（6）状态值不容易使用到索引
GENDER 0,1,2表示男，女，未知
即使在字段上增加了索引，通常也不会起作用
SELECT * FROM STUDENT WHERE GENDER IN (0,1);
原因：状态值往往导致一个状态值匹配大量记录，查询大量记录的时候，MYSQL认为使用索引开销比全表扫描都要大
如何创建索引：
（1）WHERE,ORDER BY,JOIN字段上建立索引
（2）组合索引的建立：基于业务逻辑
（3）如果条件经常出现在一起，多字段索引可以升级为复合索引
（4）如果通过增加个别字段，就可以出现索引覆盖，那么增加个别字段
（5）不会用到的索引应该删掉
（6）常规情况下我们建立的数据库系统本身性能就不差了
（7）有些字段是否只使用前缀就能完成，使用前缀索引
前缀索引：INDEX(FIELD(10))
使用字段field的前10个字符建立索引，默认是使用字段全部内容建立索引
使用：GIT的COMMIT_ID；密码字段
索引的存储结构：
BTREE索引，HASH索引，聚簇索引
以上概念指的是索引的存储结构，数据结构上的概念，实际使用无需关心，了解即可
BTREE索引：
索引存储在磁盘上所用的基础的通用的存储结构
特征：磁盘上的数据结构，不是二叉树，一定要一个中文，那就是多路平衡查找树
特点：一个BTREE节点，存储多个索引关键字，多少由节点大小和关键字来确定的，
节点大小是固定的，由计算机文件系统来确定，一次性磁盘读取内存量，就是一个节点大小
由于一个节点的大小是固定的，一个节点无法容纳大量关键字，所以分散在多个节点来存储关键字
这时候如何进行排序呢？通过上层节点的子节点指针指向下层节点，用来关联所有的节点，子节点指针位于关键字之间
例如每个节点存储1000个关键字，深度为2的两层BTREE大概可以存储1000000（1000*1000）个关键字
查找一个关键字，需要读取几个节点的内容呢？从根开始，确定下级节点，仅仅两次的磁盘读取就可以做到
Btree的意义在于可以遍历大量关键字，减少磁盘读取量的开销
聚簇索引：
关键字和记录在一起进行存储
是升级后的Btree，数据结构上的B+Tree
MySQL中只有Innodb的主键索引是聚簇结构
HASH索引：
当索引被载入到内存后采用的存储结构，采用哈希结构存储了，类似Java的Map，Key-Value
