MySQL索引及视图

1. 上节回顾

1.1 information_schema.tables
1.2 B树查找算法
1.3 B树功能分类
聚集索引
辅助索引
1.4 辅助索引分类
单列
联合
唯一
前缀

1. 执行计划分析(desc explain)
   2.1 table
   2.2 type: ALL index range ref eq_ref const NULL
   ALL:
   select from t1;
   select from t1 where xxx where 条件无索引
   select from t1 where != not in like '%xaa%'
   index:
   select id from city;
   range: > < >= <= like , in or
   ref:
   select from city where countrycode='CHN';
   eq_ref : 多表连接
   const :主键和唯一键的等值

2.3 possible_key:可能会用到的索引

2.4 key: 真正选择了哪个索引

2.5 key_len 索引覆盖长度 **
实例：
use school;
create table test (id int,k1 char(2) not null,k2 varchar(2) not null,k3 char(4),k4 varchar(4)) default charset utf8mb4 collate utf8mb4_bin;
insert into test values (1,'aa','中国','aaaa','汉唐雄风');
insert into test values (2,'bb','美国','bbbb','开心自由');
insert into test values (3,'cc','日本','cccc','抱残守缺');
insert into test values (4,'dd','韩国','dddd','宇宙大国');

alter table test add index idx_k1(k1);
alter table test add index idx_k2(k2);
alter table test add index idx_k3(k3);
alter table test add index idx_k4(k4);

desc select from test where k1='aa';
desc select from test where k2='韩国';
desc select from test where k3='cccc';
desc select from test where k4='宇宙大国';

alter table test drop index idx_k1;
alter table test drop index idx_k2;
alter table test drop index idx_k3;
alter table test drop index idx_k4;

alter table test add index idx_k(k1,k2,k3,k4);

desc select from test where k1='aa' and k2='中国' and k3='aaaa' and k4='汉唐雄风';
desc select from test where k4='汉唐雄风' and k2='中国' and k3='aaaa' and k1='aa';

varchar(20) utf8mb4 *

1. 能存20个任意字符
2. 不管存储的是字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节
4. 对于数字和字母,1个实际占用大小是1个字节
5. char 用一个字节表示是否为空
6. varchar 用一个字节表示是否为空，用两个字节表示值长度

===============================
Extra: Using filesort **
出现Using filesort,说明在查询中有关排序的条件列没有应用索引,MySQL有可能就要进行文件排序。
order by
group by
distinct
union

1. 联合索引应用细节

3.1. 只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
唯一值多的列放在最左侧
abcd
acbd
adbc
acbd
等等

原因: 优化器,自动做查询条件的排列

3.2. 不连续部分条件
cda ----> acd ---> a -----> idx(c,d,a) #如果类似查询较多，新建新顺序索引
dba ----> abd ---> ab ----> idx(d,b,a)

3.3. 在where查询中如果出现> < >= <= like ，启用范围range查询
(1)
mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='aaaaaaaa' and k2>'aaa';
(2)
mysql> alter table test add index idx1(k1,k3,k4,k2);

3.4. 多子句查询,应用联合索引
mysql> desc select * from test where k1='aa' order by k2;
mysql> alter table test add index idx3(k1,k2);

1. explain(desc)使用场景（面试题）
   你做过哪些优化?
   你用过什么优化工具?
   你对索引这块怎么优化的?

题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然卡住
应急情况（压力测试）:数据库卡了,资源耗尽
处理过程:
show processlist; 获取到导致数据库夯住的语句
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
建索引,改语句
（2）一段时间慢(持续性的):
记录慢日志slowlog,分析slowlog：
查看慢日志：show variables like '%slow%';
启用慢日志：set global slow_query_log='on';
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
建索引,改语句

1. 索引应用规范
   5.1 建立索引的原则（DBA运维规范） *
   (1) 建表必须要有主键,一般是无关列,自增长
   (2) 经常做为where条件列 order by group by join on, distinct 的条件
   (3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
   (4) 列值长度较长的索引列,我们建议使用前缀索引.
   (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit（pt-duplicate-key-checker检查冗余索引）
   (6) 索引维护要避开业务繁忙期
   (7) 小表不建索引

5.2 不走索引的情况（开发规范） **
(1) 没有查询条件，或者查询条件没有建立索引
select from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上。
(3) 索引本身失效，统计数据不真实（刚更新数据，索引还没有同步）

面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效

(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，，/，! 等)
mysql> desc select from city where id-99=1;
(5) 隐式转换导致索引失效.
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走
(8) 联合索引不查第一个值

=============================================================================
1.视图的定义：

一张虚表，和真实的表一样。视图包含一系列带有名称的行和列数据。
视图是从一个或多个基表中导出来的，可以通过insert，update，delete来操作视图。

2.视图的优点：

简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化操作。经常被使用的查询可以制作成一个视图

安全性：通过视图用户只能查询和修改所能见到的数据，数据库中其他的数据既看不见也取不到。数据库授权命令可以让每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定的行，列上。

逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响

3.创建单表视图
创表：
CREATE TABLE t(quantity INT, price INT);

插入数据：
INSERT INTO t VALUES(3,50);

创建视图：
CREATE VIEW view_t AS SELECT quantity,price,quantity*price FROM t;

查看视图：
SELECT * FROM view_t;

4.创建多表视图
创建表：
create table student( s_id int(3) primary key,s_name varchar(30),s_age int(3),s_sex varchar(8));

create table stu_info(s_id int(3),class varchar(50),addr varchar(100)) default charset utf8mb4 collate utf8mb4_bin;

插入数据：
insert into student values (1,'z3',20,'m'),(2,'L4',30,'m'),(3,'w5',40,'f');

insert into stu_info (s_id,class,addr) values(1,'二班','安徽'),(2,'三班','重庆'),(3,'一班','山东');

创建视图：
CREATE VIEW stu_class(id,name,class)
AS
SELECT student.s_id,student.s_name,stu_info.class from student,stu_info WHERE student.s_id=stu_info.s_id;

查看视图的基本信息：
SHOW TABLE STATUS LIKE 'stu_class'\G

查看视图的详细信息：
show create view stu_class;

5.查看mysql中所有视图
select * from information_schema.views\G

6.修改视图
CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;
或
ALTER VIEW view_t AS SELECT quantity FROM t ;

7.更新视图
UPDATE view_t SET quantity=5;

8.删除视图内容
DELETE FROM view_t WHERE quantity=5;

9.删除视图
DROP VIEW stu_class;

10.视图和表的区别
（1）视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表。而表不是。
（2）视图没有实际的物理记录，而表有。
（3）表是内容，视图窗口
（4）表和视图虽然都占用物理空间，但是视图只是逻辑概念存在，而表可以及时对数据进行修改，但是视图只能用创建语句来修改
（5）视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL 语句的集合。从安全角度来说，视图可以防止用户接触数据表，因而不知道表结构
（6）表属于全局模式中的表，是实表。而视图属于局部模式的表，是虚表
（7）视图的建立和删除只影响视图本身，而不影响对应表的基本表

11.两者的联系
视图是在基本表之上建立的表，它的结构和内容都来自于基本表，它依赖基本表存在而存在。
一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本的抽象和逻辑意义上建立的关系。

12.视图的优缺点
优点：
简化查询，把复杂的单表或多表查询语句，简单化。
提供安全层，可以针对特定用户限制访问某些基表的特殊列。
缺点：
视图嵌套，影响查询效率。
视图与基表有依赖关系，频繁修改基表会比较繁琐。