MySQL视图和索引

索引

索引是对数据库表中一列或多列值进行排序的一种结构，是一个单独存储在磁盘上的数据结构，使用索引可以提高数据的查询速度

按使用方式索引分为

• 普通索引和唯一索引
• 单列索引和组合索引
• 全文索引
• 空间索引

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

创建索引方式
创建索引方式

• 在创建表时创建索引
• 在已存在的表上创建索引
• 用create index创建索引

在创建表时创建索引
可以创建 (唯一索引)UNIQUE|(全文索引)FULLTEXT|(空间索引)SPATIAL

语法格式：
Create table 表名
(
字段1 数据类型,
字段2 数据类型,
……
字段n 数据类型
索引类型 INDEX 索引名(字段名)
)

创建普通索引
在book表中的year_publication字段上建立普通索引

create table book
(
	bookId int not null,
	bookName varchar(50) not null,
	author varchar(50) not null,
	info varchar(255) not null,
	comments varchar(255) not null,
	year_publication year,
	index idx_publication(year_publication)
);

使用explain语句查看索引使用情况
使用explain语句查看索引是否正在使用
possible_keys给出mysql在搜索数据记录时可选用的各个索引，key行是mysql实际选用的索引。

–没有使用索引，因为没有在bookId列上创建索引

explain select * from book where bookId = 1

1.id：这是SELECT的查询***
2.select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

3.table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

创建唯一索引
创建表t1，在表中的id字段上使unique关键字创建唯一索引。

create table t1
(
	id int not null,
	name varchar(50) not null,
	unique index idx_id(id)
)

--使用explain语句查看索引使用情况
insert into t1 values(1,'a')
insert into t1 values(1,'b') --报错的原因：在id字段上创建了唯一索引，默认会在该字段上创建唯一约束

创建单列索引
单列索引是在数据表的某一个字段上创建的索引，一个表中可以创建多个单列索引。
例：创建表t2，在表中name字段上创建单列索引。

create table t2
(
	id int not null,
	name varchar(50),
	index idx_name(name)
);

--使用explain语句查看索引使用情况
explain select * from t2 where name = 'a'

创建组合索引
组合索引是在多个字段上创建一个索引。
组合索引遵循“最左前缀”:利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。 这里由id、name和age构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：{id，name，age}，{id，name}，{id}
例：创建表t3，在表中的id、name和age字段上建立组合索引。

create table t3
(
	id int not null,
	name varchar(50) not null,
	age tinyint not null,
	info varchar(255) not null,
	index idx_id_name_age(id,name,age)
);

--使用explain语句查看索引使用情况
--都满足最左前缀
explain select * from t3 where id = 1 and name = 'a' and age = 20
explain select * from t3 where id = 1 and name = 'a' 
explain select * from t3 where id = 1  
explain select * from t3 where id = 1 and age = 20

--不满足使用索引的情况（不满足最左前缀）
explain select * from t3 where name = 'a'
explain select * from t3 where name = 'a' and age = 20
explain select * from t3 where age = 20

创建全文索引
fulltext全文索引可以用于全文搜索。只有MyIsAM存储引擎支持fulltext索引，并且只能为char，varchar和text列。
全文索引非常适合于大型数据集，对于小的数据集，它的用处可能比较小。
例:创建表t4，在表中的info字段上建立全文索引。

create table t4
(
	id int not null,
	name varchar(50) not null,
	age tinyint not null,
	info varchar(255) not null,
	fulltext index idx_info(info)
)engine=MyIsAm;

跟普通索引稍有不同
使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)

--使用explain语句查看索引使用情况
explain select * from t4 where info = 'abc'

在已存在的表上创建索引

语法格式：
Alter table 表名 add [UNIQUE|FULLTEXT|SPATIAL][index|key] 索引名（字段列表） 

创建普通索引
在book表中的bookName字段上建立名为bookNameIdx的普通索引。

alter table book add index idx_bookName(bookName)

创建唯一索引
在book表上的bookId字段建立名为UniqueIdIdx的唯一索引。

alter table book add unique index idx_bookId(bookId)

创建单列索引
在book表的comment字段上建立单列索引。

alter table book add index idx_comments(comments)

创建组合索引
在book表的authors和info字段上建立组合索引。

alter table book add index idx_author_info(authors,info)

--在已存在的表上创全文索引
alter table book add fulltext index idx_info(info)

Create index语句创建索引
创建普通单列索引

CREATE INDEX 索引名 ON 表名（列名1）

创建普通多列索引(最左前缀策略)

CREATE INDEX 索引名 ON 表名（列名1,列名2，列名3）

创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名（列名1,列名2,…）
添加唯一约束后该列会自动添加唯一索引

创建主键时自动建立主键索引

创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名(列名1,列名2)

查看索引

SHOW INDEX FROM表名

例：

create table book1
(
	bookId int not null,
	bookName varchar(50) not null,
	author varchar(50) not null,
	info varchar(255) not null,
	comments varchar(255) not null,
	year_publication year,
	index idx_publication(year_publication)
)engine=MyISAM;
--创建普通单列索引
--语法：CREATE INDEX 索引名 ON 表名（列名1）
create index idx_bookName on book1(bookName)

--创建普通多列索引(最左前缀策略)
--语法：CREATE INDEX 索引名 ON 表名（列名1,列名2，列名3）
create index idx_bookName_author on book1(bookName,author)

--创建唯一索引
--语法：CREATE UNIQUE INDEX 索引名 ON 表名（列名1,列名2,…）
---添加唯一约束后该列会自动添加唯一索引
create unique index idx_bookId on book1(bookId) 

--创建全文索引
--语法：CREATE FULLTEXT INDEX 索引名 ON 表名(列名1,列名2)
create fulltext index idx_info1 on book1(info)

--查看索引
--SHOW INDEX FROM表名
show index from book
show index from book1

删除索引
使用alter table删除索引

Alter table 表名 drop index 索引名称

使用drop index语句删除索引

Drop index 索引名称 on 表名

创建索引的时机
1.若表中的某字段出现在select、过滤、排序条件中，为该字段建立索引
2.对于like '%xxx’的模糊查询，普通的索引是无法满足的，需要建立全文索引
3.对于有多个条件的需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。
4.建立索引是有代价的，当update、delete语句执行时，会使得索引更新，将耗掉更多的时间
建索引的几大原则
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

视图

视图(view)是虚拟表，以真实的表为基础，从一个或多个表中查询出来的形成新的行和列数据，视图可以简化SQL查询。
视图是一张虚拟的表，它本身并不包含数据，它的数据来自于真实存在的表，主要是为了简化sql的查询

create table dept
(
	deptId int primary key auto_increment,
	deptName varchar(20) unique not null
);
create table emp
(
	empId int primary key auto_increment,
	empName varchar(10) not null,
	empSex char(2) default '男',
	salary float,
	deptId int,
	constraint fk_dept_emp foreign key(deptId) references dept(deptId)
);

insert into dept values(null,'学术部'),(null,'教质部'),(null,'市场部'),(null,'财务部');

insert into emp values 
(null,'邱碧波',default,10000,1),
(null,'徐炼',default,6000,1),
(null,'易云龙',default,6500,1),
(null,'王大豹',default,5500,1),
(null,'黄振',default,5000,1),
(null,'彭振勇',default,5000,1),
(null,'张三','女',4500,2),
(null,'李四','女',3500,2),
(null,'王五',null,2500,3),
(null,'赵六','女',3500,3),
(null,'钱七',default,5500,3);

创建视图
create [or replace] view 视图名称[(字段列表)]表示创建或者重新定义一个视图，重新定义or replace只在修改已有视图才需要

语法格式：

create [or replace] view 视图名称[(字段列表)]
as
select 语句；

--使用单表创建视图
--例子：在emp表上创建视图，视图名为view_emp
create view view_emp
as
select * from emp;

--使用视图，在定义视图时没有指定字段列表，则视图返回的字段与查询语句一致
select * from view_emp;

--在创建视图时指定返回的字段
create or replace  view view_emp(empName,salary)
as 
	select empName,salary from emp;
	
	
--使用视图
select * from view_emp

--使用多表创建视图
create view view_dept_emp(empId,empName,deptId,deptName)
as
	select empId,empName,d.deptId,deptName from dept d,emp e where d.deptId = e.deptId;
	
--使用视图
select * from view_dept_emp
select * from view_dept_emp where deptId = 1

查看视图
describe查看视图基本信息

describe  视图名称

show table status 语句查看基本信息

show table status like  视图名称

show create view查看详细信息

show create view  视图名称

在views表中查看试图详细信息

select *　from　information_schema.vieews

--在views表中查看试图详细信息
select * from information_schema.views

--describe查看视图基本信息
desc view_dept_emp

--show table status 语句查看基本信息
--语法：show table status like  视图名称或表名
show table status like 'emp'
show table status like 'view_dept_emp'

--show create view查看详细信息
--语法：show create view  视图名称
show create view view_dept_emp

更新视图
更新视图是指通过视图来插入、更新、删除表中的数据，对视图增加、修改或删除一样可以更新基表数据
限制：当视图包含如下内容，更新不能被执行

• 视图中不包含基表中被定义为非空的列
• 在定义视图的select语句后的字段中使用了数学表达式
• 在定义视图的select语句后的字段列表中使用聚合函数
• 在定义视图的select语句中使用了distinct,union,top,group by,having子句

--更新视图(insert、update、delete),对视图更新操作，最终会修改原始表
update view_dept_emp set empName = '老徐' where empId = 2
select * from emp;

删除视图
删除视图语法

Drop view [if exists] 视图名称

--语法：Drop view [if exists] 视图名称
drop view if exists  view_dept_emp