【SQL优化/索引失效的几种情况/FIC/OnlineDDL】

SQL优化

• 针对SQL进行调整，在写SQL的时候遵循最左前缀原则，向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，范围列可以用到索引，但是范围列后面的列无法用到索引。

• like以通配符%开头索引失效会变成全表扫描的操作。

• 如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。

• 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。

• 不要使用select *，改用select加字段名称，因为select *走的聚集索引，会进行全表扫描，如果一定要使用select *的话，mysql至少使用5.6版本，这个版本有一个离散读的优化，离散读的优化是将离散度大的列放到联合索引的前面，举个例子，select * from user where staff_id = 2 and customer_id = 584，这个时候索引优化会将customer_id放到前面，因为它的离散度更高，可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。

5.6版本有一个ICP的优化，以往根据索引查找记录，再根据WHERE条件来过滤记录。使用ICP优化后，会在取出索引的同时，直接根据WHERE条件过滤，将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取，从而提高性能。

5.6版本还有一个MRR优化，是批量处理对键值的查询操作ICP优化，减少缓冲池中页被替换的次数，使数据访问变得较为顺序。辅助索引查询得到书签后，先对主键进行排序，再按序进行查找。

• 另外在写sql的时候，尽量使用它的一个执行计划，去看我们的索引是不是失效了。

索引失效的几种情况

• 如果条件中有or，即使其中有部分条件带索引也不会使用。

• 对于复合索引，如果不使用前列，后续列也将无法使用。

• like以%开头。列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

• where中索引列有运算，有函数的，不使用索引。

• 如果mysql觉得全表扫描更快的时候，数据少的情况下，不使用索引。

FIC（Fast IndexCreation）原理

MySQL 5.5版本之前（不包括5.5）存在的一个普遍被人诟病的问题是：MySQL数据库对于索引的添加或者删除的这类DDL操作，MySQL数据库的操作过程为：❑首先创建一张新的临时表，表结构为通过命令ALTERTABLE新定义的结构。❑然后把原表中数据导入到临时表。❑接着删除原表。❑最后把临时表重命名为原来的表名。可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。MySQL数据库的索引维护始终让使用者感觉非常痛苦。InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index Creation（快速索引创建）的索引创建方式——简称FIC。对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用（不影响附注索引的使用，因为可读，后边的同时删除四个字非常传神），同时删除MySQL数据库内部视图上对该表的索引定义即可。由于FIC在索引的创建的过程中对表加上了S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

OnlineDDL

虽然FIC可以让InnoDB存储引擎避免创建临时表，从而提高索引创建的效率。但索引创建时会阻塞表上的DML操作（除读操作）。MySQL 5.6版本开始支持Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE、DELETE这类DML操作，这极大地提高了MySQL数据库在生产环境中的可用性。不仅是辅助索引，以下这几类DDL操作都可以通过“在线”的方式进行操作：
❑辅助索引的创建与删除
❑改变自增长值
❑添加或删除外键约束
❑列的重命名

使用语法

ALTER TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name,...)[index_option]...
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM指定了创建或删除索引的算法
COPY表示按照MySQL 5.1版本之前的工作模式，即创建临时表的方式。
INPLACE表示索引创建或删除操作不需要创建临时表。
DEFAULT表示根据参数old_alter_table来判断是通过INPLACE 还是COPY的算法，该参数的默认值为OFF，表示采用INPLACE的方式。

LOCK部分为索引创建或删除时对表添加锁的情况：
（1）NONE
执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。
（2）SHARE
这和之前的FIC类似，执行索引创建或删除操作时，对目标表加上一个S锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持SHARE模式，会返回一个错误信息。
（3）EXCLUSIVE
在EXCLUSIVE模式下，执行索引创建或删除操作时，对目标表加上一个X锁。读写事务都不能进行，因此会阻塞所有的线程，这和COPY方式运行得到的状态类似，但是不需要像COPY方式那样创建一张临时表。
（4）DEFAULT
DEFAULT模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式

InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认的大小为128MB。需要特别注意的是，由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL优化器不会选择正在创建中的索引。