前面我们讲了如何创建索引以及哪些情况下该创建索引。现在我们来说一下sql优化中的索引如何优化。
首先我们需要了解都有哪些纬度可以进行数据库调优：
①索引失效，没有充分利用到索引--索引建立
②关联查询太多join（设计缺陷或不得已的需求）--sql优化
③服务器调优以及各个参数设置（缓冲、线程数等）--调整my.cnf
④数据过多--分库分表
关于数据库调优的知识点非常分散，看了很多的文档介绍也都是零零散散。不同的DBMS，不同的公司不同的职位不同的项目遇到的问题也是不尽相同。
虽然sql查询优化的技术有很多，但是大方向上完全可以分为物理查询优化和逻辑查询优化两块。
①物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
②逻辑查询优化是通过sql等价变换提升查询效率，直白说就是换一种查询写法执行效率可能更高。
这篇文章就来讲一讲如何进行索引优化
1.我这里准备两张表??学员表 插 50万 条， 班级表 插 1万 条。
CREATE TABLE `class`
( `id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
?2.索引失效案例
mysql中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效的访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。
①使用索引可以快速定位表中某条记录，从而提高数据库查询的速度，提高数据库性能。
②如果查询时没有使用索引，查询语句就会扫描表中所有记录。在数据量大的情况下，这样查询的速度会很慢。
大多数情况下都（这里以innodb搜索引擎为例）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。
其实，用不用索引，最终都是优化器说了算。优化器是基于什么优化？基于开销，它不是基于规则，也不是基于语义。怎么样开销小就怎么来。另外，sql语句是否使用索引，跟数据库版本。数据量、数据选择度都有关系。
2.1全值匹配
系统中经常出现的sql语句如下
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 and name='abc';在建立所以前执行，注意关注时间
?建立索引
create index idx_age_classid_name on student(age,classId,name);
建立索引后再执行
?可以看到，创建索引前的查询时间是0.19秒，创建索引后的查询时间显示为0.00，索引帮助我们极大的提高了查询效率。
注意：对于查询条件中使用and来进行连接且条件中为等值连接的。where中的顺序无关。
?2.2最佳左前缀法则
在mysql建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
比如上面我们创建了联合索引create index idx_age_classid_name on student(age,classId,name);索引的顺序为age? classId? name
?上面只用到了age进行索引。
上面没有用到任何索引
上面用到了完整的联合索引的所有字段
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
小结：mysql可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要是用索引必须按照索引建立时的顺序，一次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第一个字段时，多列索引不会被使用。
2.3主键插入顺序
对于一个使用innodb存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点上的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插入，而如果我们插入的主键值忽大忽小的话，就比较麻烦了。假设某个数据页存储的记录已经满了，它存储的主键值在1--100之间
?如果此时插入一条主键值为9的记录，那它插入的位置就是如图
?可这个数据页已经满了，再插入进来怎么办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移到信创建的这个页中。页面分裂和记录一位意味着性能损耗。所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录主键值依次递增，这样就不会发生损耗了。所以我建议：让主键具有auto_increment，让存储引擎自己为表生成主键而不是我们手动插入。这样的主键占用空间小，顺序写入，减少页分裂。（后面我们还会介绍自增长的劣势）
2.4计算、函数导致索引失效
先建立一个关于name的索引
create index name_index on student(name);
我们看看下面两个语句的执行时间
?我们看到虽然功能相同的语句。第一条语句使用了我们创建的索引，第二条语句由于left函数存在而没有使用到我们的索引
再比如我们对student表的字段stuno设置索引(这里使用另一种创建索引的方式) alter table student add index index_sno(stuno);
同样的查询效果但是第一条语句使用了索引，但是第二条语句由于有计算在内所以没有使用。?
?
2.5类型转换（自动或手动）导致索引失效
我们之前在学生表的name上创建了索引，看下面两条语句
?语句一存在隐式的转换因此不会使用索引
2.6范围右边的列索引失效
之前我们创建了基于age? classId? name的索引idx_age_classid_name
?①范围右边的列不能使用索引。比如<? ?>? ?<=? ?>=? between等
②如果这种sql出现比较多应该建立这样的索引
create index age_name_classid on student(age,name,classId);
?这里一定要理解索引的创建以及使用，顺序调整的是创建索引时候的顺序
2.7不能与(!=或者<>)索引失效
2.8is null可以使用索引，is not null无法使用索引
结论：最好在设计数据表的时候将字段设置为not null约束，比如可以将int类型的字段默认值设置为0.将字符串类型的默认值设置为空字符串''
拓展：同理，在查询中使用not like也无法使用索引，导致全表扫描
2.9like以通配符%开头索引失效
拓展：Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊，如果需要情走搜索引擎解决
2.10 OR前后存在非索引列，索引失效
在where字句中，如果在or前的条件列进行了索引而or后的条件没有进行列索引，那么索引会失效。也就是说or前后的两个条件中的列都是索引列时查询中才使用索引。
因为or的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只有条件列没有进行索引，就会进行全表扫描，因此索引的条件列会失效。
2.11数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生乱码。不同的字符集进行比较前需要进行转换会造成索引失效。
练习
?
?
?一般性建议
①对于单列索引，尽量选择针对当前query过滤性更好的索引
②在选择组合索引时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
③在选择组合索引时候，尽量选择能够包含当前query中的where子句中更多字段的索引
④在选择组合索引时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
?