0. 什么是索引？
在数据之外，数据库系统维护着一种帮助快速获取数据的有序的数据结构，这种数据结构实现了高级查找算法，以某种方式指向数据。索引会影响查找和排序的效率。
一般来说索引本身也很大，在不能全部存在内存中的情况下，会以索引键的形式存储在磁盘上。
一、优劣
1. 优势
- 对数据进行索引
提高查找效率，降低数据库 IO 成本。 - 对数据进行排序
降低排序成本和 CPU 消耗。
2. 劣势
- 索引实际上也是存于表中，记录索引的字段并指向实体的记录，同样占用空间；
- 对数据进行更新（增删改），每次更新索引字段等信息也需要更新，造成额外负担；
- 索引不是一劳永逸，而是要不断的调整。
3. 适合使用索引的场景
- 主键和唯一约束都会默认创建唯一索引；
- 频繁查询的字段适合使用索引；
- 连表查询的关联字段，外键关心建立索引；
- 排序字段可用索引降低消耗；
- 统计或分组字段适合索引。
4. 不适合索引的场景
- 频繁更新数据的字段不适合使用索引；
- 没用用于查找和排序的字段不适合索引；
- 记录太少，没必要建索引
- 重复值过多的字段不适合索引，如性别，即使建了索引也没有实际效果
二、索引分类
- 单值索引
即一个索引对应一个列 - 复合索引
一个索引对应多个列 - 唯一索引
索引列须是唯一
三、语法
- 创建索引
create index [unique] indexName on tableName(columnName(length))
#或
alter table tableName add [unique] index 【indexName】(columnName)
#indexName 索引名
#tableName 表名
#columnName 列名
#length 列的长度
- 删除索引
drop index [indexName] on table
- 查看索引
show index from tableName
四、主要索引结构
- B-Tree 索引
- Hash 索引
- Full-text 索引
- R-Tree 索引
五、索引分析
索引失效不仅会使查询、排序变慢，还会使行锁变表锁，所以一定要避免索引失效
1. where后使用索引的原则
-
最好能到到全值匹配
意思就是查询能够完全匹配索引，包括列和顺序，比如：
创建了 idx_a_b_c，那么最好查询的时候也能按照 a，b，c 的进行查找。 -
最佳左前缀法则
查询要从索引的最左侧开始，并且中间不能断，比如：
创建了 idx_a_b_c，那么按照 a 查询，或者按照 a、b 查询，都是满足最佳最前缀法则的；
而按照 b、c 或者 a、c 或者 c 查询都违反了此原则；
a、c 还好，至少还有 a 可以使用索引，b、c 和 c 则索引完全用不上。 -
不要在索引列上做以下操作：计算，使用函数，类型转换（自动或手动）
这类操作会导致索引失效，转向全表扫描 -
字符串不加单引号会导致索引失效
例如，varchar 类型的字段 a，where a=1 和 where a='1'查询结果一样，但是 a=1 会导致 mysql 隐式的类型转换，导致索引失效
用单引号，不要在用双引号了 -
范围条件搜索右侧都会失效
例如，创建 idx_a_b_c，
select * from t1 where a=1 and b>1 and c=1，这条语句中，a=1 是 ref 类型的，b>1是 range 类型的，这两个都用到了索引，但是 c=1 就无法使用索引了，因为 b>1 被打断了。此时，b 用到了索引，但是是用来排序，所以是 range 级别
in 也是范围查找。 -
不等于（!= 或 <>）会导致索引失效，从而全表扫描
-
is not null 会导致索引失效
尽量索引字段有 null，可以增加空的默认值，例如''。 -
like '%...' 或导致索引失效
'%....' 会导致索引失效（索引类型变成 all，全表扫描），但 '....%' 仍是 range 类型索引(虽然是 range，但是这种比较特殊，和 >,< 不同，它不会打断索引，也就是说，他后边的索引还可以用)。 -
尽量使用覆盖索引，减少使用 select *
例如 idx_a_b_c，select a,b,c 就可以形成覆盖索引（最好按顺序，可以少，但不可多于a,b,c），select * 则不行。
解决方法：利用覆盖索引，例如：select id from t1 where a like '%123%'（id 是主键，有唯一索引），这个查询类型是 index，优于 all。
-
or 会导致索引失效，应少用
解决办法：拆成多个语句，将查询结果合并即可。 -
创建复合索引的时候，尽量吧过滤性好的字段放在前边，例如：手机号姓名部门，这样每一个字段的筛选会过滤掉更多，使得后面的查询更轻松
具体是全部失效还是失效一般需验证----------------------------------------
2. order by
order by 使用索引的原则与 where 之后大部分相同，排序分为 using filesorts（文件内排序）和 using index（索引排序），对它的优化主要目标就是消除 using filesorts，使用 using index。
- 最佳左前缀原则
- 没有过滤条件的 order by，是用不到索引的，会产生 using filesort。也就是要有 where 过滤条件，或者加 limit。
- 多个字段排序顺序不同（同时存在asc，desc），也会产生 filesort
其中，最左前缀原则举例：
创建了索引 idx_a_b_c，
order b,c #不满足最前缀原则
where a='1' order by b,c #a是常量，不需排序，所以这个满足左前缀原则
where a like 'a%' order by b,c #同上
order c,a,b #不满足最前缀原则
order a asc，b desc #不能使用索引，必须同升同降
order by 与索引
但有时 using filesort 是不可避免的，而 filesort 又分为双路排序（mysql 4.1之前）和单路排序：
双路排序：要扫描两次磁盘得到最终数据，先读取（第一次）行指针和 order by 列到 buffer，并进行排序，按照排序后的虚拟列表重新从实体表中获取（第二次）数据，需要两次 IO，所以诞生了单路排序
单路排序：扫描一次得到数据，直接把所有查找列都读取出来，并在 buffer 中排序，然后将 buffer 中排序好的结果输出，只需要一次 IO
从上面可以看出，通常情况下，单路排序是要由于双路排序的，但仍存在特殊情况：取出数据太大，buffer 中存不下，单路排序只能每次取出 buffer 的大小的数据（创建 tmp 文件，多路合并），如此多次操作，完成全部数据的查找，导致多次 IO，效果可能比双路排序更糟。。。
解决办法就是调整 my.cnf 配置文件中一下两个参数：
sort_buffer_size,
max_length_for_sort_data
另外，需要什么字段就取，不要用 select *，避免查询结果因多余字段而过大，超过buffer 大小就不好了。
3. group by
group by 适用于分组，实际上是先排序，然后才分组。所以上面的 order by 的原则同样适用于 group by。如果 group by 使用不当，不仅会产生 using filesort，还会有 using temporary。
除了上面的几点，能用 where 就不要用 having。
select * 的危害
1. 影响覆盖索引
例如：有一个索引 idx_a_b_c, select a,b,c 或者 select a,b 都可以形成覆盖索引，因为查询字段小于等于索引的字段才能形成，一旦多于索引字段，就会无效。select * 却很有可能是查询字段多于索引字段
2. 排序生成临时表
由于 mysql 4.1 之后使用的都是单路排序（一次查询所有查询列到 buffer 中排序），由于 buffer 有限，一旦数据超出 buffer，就需要将数据分批存储到新创建的多个临时表中，全部查询、排序完成要进行整合，最后删除临时表。这一过程极其耗时，select * 增加了 buffer 爆满的风险。
msyql 优化器
mysql优化器，可以在查询时对sql进行优化，达到更好的查询效果，例如 idx_a_b_c, where 中的顺序是 a, c, b，这样 sql 优化器会对其进行优化成 a，b，c使索引得到应用。
网友评论