MySQL之索引快速入门

索引是什么

在数据之外，MySQL还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用着数据，这样就可以在这些数据结构上实现一些高级查找算法。这种数据结构就是索引。
简而言之：索引是一种用于排序和快速查找的数据结构。
索引本身也很大，所以以索引文件的形式存储在磁盘中

索引优势

1. 大大加快查找的速度，提高数据的检索效率，减少了数据的IO成本
2. 加快排序数据的速度，较少了CPU的消耗

索引劣势

1. 索引会占用了空间
2. 索引虽然会增加排序和查找的速度，但是会降低更新、删除、新增数据时的速度，因为MySQL此时不仅仅的要更改表，同时也要对调整表变化后索引的信息

索引分类

1. 唯一索引：索引列的值必须唯一，但允许有空值

   # table为表名，indexName索引名，colum列名
   CREATE UNIQUE INDEX indexName ON table(column);
   # 或者
   ALTER table ADD UNIQUE INDEX indexName ON (column);

2. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

   # 
   CREATE INDEX indexName ON table(column);
   # 或者
   ALTER table ADD INDEX indexName ON (column);

3. 复合索引：一个索引包含多个列

   CREATE INDEX indexName ON table(column1,column2,...);
   # 或
   ALTER table ADD INDEX indexName ON (column1,column2,...);

4. 全文索引：

   CREATE FULLTEXT INDEX indexName ON table(column);
   # 或者
   ALTER TABLE table ADD FULLTEXT indexName ON (column);

什么情况下需要用索引

1. 主键会自动创建唯一索引
2. 频繁作为查询条件的字段需要创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
5. 查询中统计或分组字段

   什么情况下不需要用索引

6. 表的数据太少
7. 经常增删改的字段不需要创建索引
   索引提高了查询速度，同时会降低更新表的速度，如对表进行 insert , update , delete 因为表更新时，
   MySQL 不仅仅要保存数据，还要保存索引文件。
8. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
   如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。

索引失效原因总结

1. 最佳左前缀法则：

如果索引了多列，需要准守最左前缀法则，指的是查询从索引的最左前列开始并且 不跳过索引中的列。

2. 不在索引列上左任何操作 （计算、函数、（自动 or 手动）类型转换）， 会导致索引失效而转向全表扫描
3. 存储引擎不能使用索引中范围条件右边的列
4. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *
5. mysql 在适应不等于 （!= 或者 <>）的时候无法使用索引会导致全表扫描
6. is null, is not null 也无法使用索引
7. ike 以通配符开头 （’%abc …’）mysql 索引失效会变成全表扫描的操作
8. 字符串不加单引号索引失效
9. 少用 or, 用它来连接时会索引失效