4.MySQL索引(一)

1. 索引
   在关系数据库中，索引是一种单独的。物理的对数据库中一列或多列的值进行排序的一种存储结构，索引的作用相当于图书的目录，可以根据目录中页码快速找到所需的内容。
   数据库使用索引可以使 SQL 语句执行得更快，可快速访问数据库中的特定数据。

2. 索引作用
   优点：
   1）可以大大加快数据的检索速度，这也是创建索引的最主要的原因；
   2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
   3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
   缺点：
   1）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；
   2）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
   3）当对表中的数据进行增加、删除和修改的时候，所以也要动态的维护，这样就降低了数据的维护速度；

3. 索引提高查询效率原因
   1）索引数据的存储是有序的；
   2）因为有序，通过索引查询数据是无需遍历所有记录的；
   3）极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N);

4. 索引的设计原则
   1）对于经常查询的字段，建议创建索引；
   2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响insert，delete，update等语句的性能；
   3）避免对经常更新的表进行行过多的索引，因为当表数据更改的同时，索引也会进行调整和更新，十分消耗系统资源；
   4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源；
   5）不要再区分度低的字段建立索引。比如性别字段，只要男和女，建索引完全起不到优化效果；
   6）在频繁进行排列分组（即进行 group by 或 order by 操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引；
   7）当唯一性是某一字段本身的特征时，指定唯一索引能提高查询速度；

5. 什么情况下应不建或少建索引
   以下几种情况不适合建立索引：
   1）表中数据太少；
   2）经常插入、删除、修改的表
   3）表数据重复且分布平均的表字段；

6. MySQL 索引的种类
   MySQL 的索引有两种分类方式：逻辑分类和物理分类。
   1）按照逻辑分类，索引可分为：

   1. 普通索引：仅加速查询（MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询你数据更快一点。）
   2. 唯一索引：加速查询 + 列值唯一（可以有null）
   3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
   4. 复合索引：多个列组合一个索引，用于多个字段组合索引。
   5. 全文索引：目前只能在 MyISAM、InnoDB 引擎上才能使用，只能在char，varchar，text类型字段上使用全文索引，可以通过某个关键字找到该字段索引的记录行。

   2）按照物理分类，索引可分为：
   聚集索引：也叫聚簇索引，是指数据行的物理顺序和列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。MySQL 中一般默认主键为聚集索引。
   非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

7. MySQL 索引命名规范
   一般命名规则如下：
   1）主键索引（primary）：pk_字段名
   2）唯一索引（unique）：uk_字段名
   3）普通索引（index）：idx_字段名

8. MySQL 二级索引
   二级索引即非聚集索引，聚集索引之外的其他索引都属于二级索引，也叫辅助索引。
   不如：唯一索引、普通索引、前缀索引等都是二级索引。
   MySQL 有了聚簇索引为啥还要二级索引？
   因为聚簇索引的叶子节点存储的是一行完整的数据，而二级索引存储了主键值，所以相比于聚簇索引，二级索引占用的空间要少很多。
   如果全部都是聚簇索引，那将占用大量的空间，所以在 MySQL InnoDB 引擎中，只有主键建立的索引才是聚簇索引，其他的都是非聚簇索引。

9. MySQL 索引最左匹配原则
   索引最左匹配原则就是指在 MySQL 复合索引中从最左边开始匹配的原则，如果你的 SQL 语句中用到了复合索引中最左边的索引，那么这条SQL语句就可以利用这个联合索引去进行匹配。
   如：建立 abc 三个字段的复合索引，就相当于建立了索引：（a）、（a,b）、（a,b,c）
   索引字段可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

   # 用上了索引：a b c
   select * fromm table where a=1 and b=1 and c=1;
   # 用上了索引：a b
   select * fromm table where a=1 and b=1;
   # 用上了索引：a b
   select * fromm table where b=1 and a=1;
   # 用上了索引：a 
   select * fromm table where a=1;
   # 没有用上了索引
   select * fromm table where c=1;
   # 用上了索引：a 
   select * fromm table where a=1 and c=1;
   

   值得注意的是，当遇到范围查询（>,<,between,like）就会停止匹配，如：

   # 用上了索引：a b 
   select * fromm table where a=1 and b<1 and c=1;