MySQL之索引

引入索引：

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引，索引相当于一本书的目录，是为了缩小范围而存在的一种机制

比如：对于一本字典来说，查找某个汉字有两种方式：

第一种方式：一页一页挨着查找，直到找到为止，这种查找方式属于全字典查找，效率比较低

第二种方式：先通过目录(索引)去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找，这种查找方式属于通过索引检索，效率比较高。

举例：

select * from person where name='jack';

如果执行上述这条SQL语句，那么将会在name字段进行扫描，因为查询条件是:name=‘jack’;

如果name字段上没有添加索引[目录]，或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都对比一遍，效率比较低。

MySQL在查询方面主要就是两种方式：

第一种方式：全表扫描

第二种方式：根据索引检索

注意：

在实际开发中，汉语字典前面的目录是排序的，按照a b c d e f…排序，原因是只有排序了才会有区间查找这一说(缩小扫描范围其实就是扫描某个区间罢了)，在MySQL数据库当中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同，TreeSet(TreeMap)底层是一个自平衡的二叉树，在MySQL当中索引是一个B-Tree数据结构

遵循左小右大原则存放，采用中序遍历方式遍历存取数据

索引的实现原理：

假设有一张用户表：t_user

a:在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK，另外在MySQL当中，一个字段上如果有unique约束的话，也会自动创建索引对象

b:在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

c:在MySQL当中，索引是一个单独的对象，不同的存储引擎以不同形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中，在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中，在MEMORY存储引擎当中索引被存储在内存当中，不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在

在MySQL当中，主键上，以及unique字段上都会自动添加索引，那么在什么条件下，我们会考虑给字段添加索引呢？

条件1：数据量非常庞大
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描
条件3：该字段很少的DML(insert delete update)操作，因为DML之后，索引需要重新排序

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能，建议通过主键查询，建议通过unique约束的字段进行查询，效率都是比较高的

创建索引：

语法：

create index 索引名 on 表名(字段名);

举例：

//为person表中的id字段创建索引名为id_student_index
create index id_student_index on  person(id);

删除索引：

语法：

drop index 索引名 on 表名;

举例：

//删除person表上名为id_student_index的索引
drop index id_student_index on person;

使用SQL语句查询当前某个字段是否添加索引：

语法：

explain select ......where=“需要查看是否添加索引的字段名”;

索引失效：

第一种：当SQL语句的限制条件为模糊查询时，比如下述实例：

即使name字段上添加了索引，索引也不会生效，原因是因为模糊匹配当中以“%”开头，索引不知道从哪里开始查找，因此我们应该避免模糊查询的时候以“%”开始

第二种：使用or的时候会失效，比如下述实例：

使用or的时候会失效，如果使用or，那么要求or两边的条件字段都要有索引，那么索引才会生效，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效

第三种：使用复合索引的时候，没有使用左侧的列查找，索引失效。比如下述实例：

复合索引即为：两个或两个以上字段联合起来添加一个索引

第四种：在where当中索引列参与了运算，索引失效。比如下述实例：

第五种：在where当中使用了函数，索引失效。比如下述实例：

索引的分类：

索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引，在数据库索引可以分为很多类：

单一索引：一个字段上添加索引
复合索引：两个字段或者更多的字段上添加索引
主键索引：主键上添加索引
唯一性索引：具有unique约束的字段上添加索引
..............

注：唯一性比较弱的字段上添加索引的用处不大

视图：

view：站在不同的角度去看待同一份数据，只有DQL语句才能以view的形式创建

创建视图：

create view 视图名 as  DQL语句[这里必须是DQL语句];

删除视图：

drop view 视图名;

视图的作用：

面向视图对象进行增删改查，对视图对象的增删改查会导致原表被操作(视图的特点：通过对视图的操作，会影响原表数据)

举例：

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用，每一次使用这个SQL语句的时候都需要重新编写，非常的麻烦，那么我们可以将这条复杂的SQL语句以视图对象的形式新建，在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发，并且有利于后期的维护，因为修改的时候只需要修改视图对象所映射的SQL语句，我们以后面向视图开发的时候，使用视图的时候可以像使用table一样，可以对视图进行增删改查等操作，视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失

注：视图对应的语句只能是DQL语句，但是视图对象创建完成之后，可以对视图进行增删改查操作

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/81385.html