索引再复习

索引的管理

• 索引的增删改:
  增加:

CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length));
ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length));
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
);

删除:

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

• 查看索引: show index from table
  关键看Cardinality列，它可以用于估算索引的选择性，用它除以总行数越接近1，选择性越大。
  这个关键字不是及时更新的，需要更新的话，需要使用ANALYZE TABLE tablename

• Fast Index Creation
  在MySQL 5.5之前，对于索引的添加或者删除，每次都需要创建一张临时表，然后导入数据到临时表，接着删除原表，如果一张大表进行这样的操作，会非常的耗时，这是一个很大的缺陷。
  InnoDB存储引擎从1.0.x版本开始加入了一种Fast Index Creation（快速索引创建）的索引创建方式。
  这种方式的策略为：每次为创建索引的表加上一个S锁（共享锁），在创建的时候，不需要重新建表，删除辅助索引只需要更新内部视图，并将辅助索引空间标记为可用，所以，这种效率就大大提高了。

• 在线数据定义
  MySQL5.6开始支持的在线数据定义操作就是：允许辅助索引创建的同时，还允许其他insert、update、delete这类DM操作，这就极大提高了数据库的可用性。
  所以，我们可以使用新的语法进行创建索引：

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
[ALGORITHM = {DEFAULT|INPLACE|COPY}]
[LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]
ALGORITHM指定创建或者删除索引的算法
COPY：创建临时表的方式
INPLACE：不需要创建临时表
DEFAULT：根据参数old_alter_table参数判断，如果是OFF,采用INPLACE的方式
LOCK表示对表添加锁的情况
NONE：不加任何锁
SHARE：加一个S锁，并发读可以进行，写操作需要等待
EXCLUSIVE：加一个X锁，读写都不能并发进行
DEFAULT：先判断是否可以使用NONE，如不能，判断是否可以使用SHARE，如不能，再判断是否可以使用EXCLUSIVE模式。

索引的使用

• 最左前缀列匹配，用explain关键字分析语句，得到key_len可分析使用了索引的哪些字段，一个utf-8字符是3个字节，如果key_len为6，即六个字节，那么就是两个utf-8字符。即最左的前两个utf-8字符。
• 联合索引的使用有一个好处，就是索引的下一个字段是会自动排序的。如果用不了索引排序，会显示filesort。

Q1：为什么不对表中的每一个列创建一个索引呢

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

Q2：为什么需要使用联合索引

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

索引优化

Multi-Range Read 优化

MySQL5.6开始支持，这种优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这种优化适用于range、ref、eq_ref类型的查询。

Multi-Range Read 优化的好处：

• 让数据访问变得较为顺序。
• 减少缓冲区中页被替换的次数。
• 批量处理对键值的查询操作。

我们可以使用参数optimizer_switch中的标记来控制是否开启Multi-Range Read 优化。下面的方式将设置为总是开启状态：

SET @@optimizer_switch='mrr=on,mrr_cost_based=off';

Index Condition Pushdown（ICP） 优化

这种优化方式也是从MySQL5.6开始支持的，不支持这种方式之前，当进行索引查询时，首先我们先根据索引查找记录，然后再根据where条件来过滤记录。然而，当支持ICP优化后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件过滤，也就是将where过滤部分放在了存储引擎层，大大减少了上层SQL对记录的索取。

ICP支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。

我们可以使用下面语句开启ICP：

set @@optimizer_switch = "index_condition_pushdown=on"

或者关闭：

set @@optimizer_switch = "index_condition_pushdown=off"

当开启了ICP之后，在执行计划Extra可以看到Using index condition提示。

索引的特点、优点、缺点及适用场景

索引的特点

• 可以加快数据库的检索速度
• 降低数据库插入、修改、删除等维护的速度
• 只能创建在表上，不能创建在视图上
• 既可以直接创建也可以间接创建

索引的优点

• 创建唯一性索引，保证数据库表中的每一行数据的唯一性
• 大大加快数据的检索速度
• 加快数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
• 在使用分组和排序字句进行数据检索时，同样可以显著减少查询的时间
• 通过使用索引，可以在查询中使用优化隐藏器，提高系统性能

索引的缺点

• 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引的适用场景

• 匹配全值

对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。

• 匹配值的范围查询

对索引的值能够进行范围查找。

• 匹配最左前缀

仅仅使用索引中的最左边列进行查询，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、(col1 + col2)、（col1 + col2 + col3）的等值查询利用到，可是不能够被 col2、（col2、col3）的等值查询利用到。
最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。

• 仅仅对索引进行查询

当查询的列都在索引的字段中时，查询的效率更高，所以应该尽量避免使用 select *，需要哪些字段，就只查哪些字段。

• 匹配列前缀

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

• 能够实现索引匹配部分精确而其他部分进行范围匹配
• 如果列名是索引，那么使用 column_name is null 就会使用索引，例如下面的就会使用索引：

explain select * from t_index where a is null \G

• 经常出现在关键字order by、group by、distinct后面的字段
• 在union等集合操作的结果集字段
• 经常用作表连接的字段
• 考虑使用索引覆盖，对数据很少被更新，如果用户经常值查询其中你的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描变为索引的扫描

索引失效情况

• 以%开头的 like 查询不能利用 B-Tree 索引，执行计划中 key 的值为 null 表示没有使用索引
• 数据类型出现隐式转换的时候也不会使用索引，例如，where 'age'+10=30
• 对索引列进行函数运算，原因同上
• 正则表达式不会使用索引
• 字符串和数据比较不会使用索引
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的
• 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引
• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
• 使用负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ） 不会使用索引