【MySQL】索引(索引分类、索引失效)与视图详解

一、索引

1.索引概述

索引就是为了提高查询效率的一种机制。

注意
① 在任何数据库中，主键所在的字段会自动添加默认的索引；若某一字段被unique约束，则该字段也会添加索引。
② 在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有—个硬盘的物理存储编号。
③ 在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
MyISAM：索引存储在.MYI文件；InnoDB：索引存储在tablespace；MEMORY：存储在内存。
查询时的两种方式：全表扫描、根据索引检索

2.索引实现原理

理解MySQL索引原理和实现
B+树索引是数据库中使用最为频繁的一种索引，B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。

3.何时需要索引

① 数据量庞大
② 该字段经常出现在where子句的后面作为筛选条件出现
③ 该字段很少进行DML操作（insert、delete、update）（DML操作后，索引需要重新排序。）
（建议使用主键作为索引，索引需要占用内存，也需要维护）

4.创建索引

（1）单列索引
语法格式：create index 索引名 on 表名(字段名...);

create index ename_iindex on emp(ename);

（2）复合索引
语法格式：create index 索引名 on 表名(字段名1，字段名2...);

5.删除索引

语法格式：drop index 索引名 on 表名;

drop index ename_iindex on emp;

6.查看查询语句是否使用索引检索

语法格式：explain 查询语句

explain select * from emp where ename = "KING";

在对ename字段设置了索引之后，查询效率从全表扫描变为索引扫描，查询速度从14行扫描提升到1行检索。

7.索引失效

索引失效共有7种情况，如下：
（1）使用模糊查询时，以”%“开头索引会失效。
策略：模糊查询时避免使用”%“开头
如：select * from emp where ename like "%H";
如下，尽管对ename字段创建了索引，但是使用了以“%”开头的模糊查询，索引失效，仍然是全表扫描14次。

（2）where筛选条件中，or语句中前后字段没有同时都为索引
策略：当or两边的连接条件只有一个有索引时，使用union操作替换or
（or连接的两个条件，一边有索引，另一边没有索引，导致索引失效；两边都有索引，索引不会失效）

（3）使用复合索引时，未使用左侧的列查找
策略：尽量使用左侧的或者两个索引同时使用

（4）where筛选条件中索引参与数学运算
策略：参与数学运算的列不要设置为索引字段

（5）where筛选条件中索引使用了函数
策略：需要经常使用函数处理的字段不设置索引

（6）索引字段中使用is null 、is not null 、！=、<>

8.索引分类

索引分类
（1）单一索引：在一个字段上添加索引
（2）复合索引：两个及以上字段上添加索引
（3）主键索引：主键上添加索引
（4）唯一性索引：具有unique约束的字段上添加索引（索引列的值必须唯一，但允许有空值）
（5）全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

二、视图

1.视图： 视图就是在不同的角度看待同一张表。
注意：视图本身不存在，对视图数据的增删改查操作，会导致原表被操作。
2.创建视图
语法格式：create view 视图名 as select 字段名1,字段名2... from 表名;
(as后面只能是查询语句，结果集可以是多列或者一列）

对t_students表创建视图

create view stu_view as select * from t_students;

视图stu_view中的数据来自t_students表中。

删除视图stu_view中的数据，原表t_students中的数据被删除

先视图stu_view中插入数据，原表t_students中插入数据

删除视图，原表中的数据不会受影响。

3.删除视图
语法格式：drop view 视图名;

drop view stu_view;

可以对多张表创建一个视图，此时对视图的增删改查操作会直接作用在着多张表上，简化了SQL操作，利于维护。

三、DBA命令

1.数据导出：
此操作只能是在windows的命令行窗口中执行。
（1）导出数据库
语法格式：mysqldump 数据库名 > 文件名 -uroot -p密码;

mysqlduump stu>D:\stu.sql -uroot -p12345678;

（2）导出数据库中的表
语法格式：mysqldump 数据库名 表名 > 文件名 -uroot -p密码;

mysqlduump stu course>D:\course.sql -uroot -p12345678;

可以使用记事本点击查看内容.

2.数据导入
只能导入后缀名是.sql的SQL文件。
语法格式：source 文件名;

source D:\stu.sql;

注意执行顺序：
首先要登录到MySQL数据库服务器上：mysql -uroot -p或mysql -uroot -p密码
然后创建数据库：create database 数据库名;
再使用数据库：use 数据库名;
最后导入：source 文件名;

DBA常用命令