mysql索引、视图、外键使用详解

1.索引使用

#1.给name添加普通索引(方式一常用)
CREATE index index_name on user(name);
#2.给name添加唯一索引(常用)
CREATE UNIQUE index index_phone on user(phone);
#3.给phone添加普通索引(方式二)
ALTER TABLE user add index index_phone(phone);
#4.添加多列索引(常用)
CREATE index index_name_phone on user(name,phone);
#5.删除索引（常用）
DROP index index_name on user;
#6.查看查询时间
SHOW PROFILES;
注：准备一张表

2.视图使用

视图：即关联查询的结果集，相当于保存查询表

准备：

两张关联表user、prpduct(用powerdesigner工具生成，注意父子表关系，父表一般不写外键，子表写外键即父表的主键)

使用：

#创建视图的方法（视图即关联查询的结果集，相当于保存查询表）
CREATE OR REPLACE view view_name_1 as
SELECT u.,p.name,p.number,p.price
from user u,product p
WHERE u.product_id=p.id
#查表
show TABLES;
#查视图内容
SELECT * FROM view_name_1;
#修改视图的内容，原表中的数据也一并被更新了
UPDATE view_name_1 SET name=‘好书本111’ where id=1;
#创建视图2
CREATE OR REPLACE view view_name_2 as
SELECT u.,p.name,p.number,p.price
from user u,product p
WHERE u.product_id=p.id
#删除视图2
DROP view view_name_2;

3.外键设置

drop table if exists product;
drop table if exists user;
create table product
(
id int not null,
name varchar(25),
price varchar(25),
number varchar(20),
primary key (id)
);
create table user
(
id int not null,
username varchar(25),
password varchar(20),
product_id int not null,
primary key (id)
#外键添加方式一： constraint fk_user_product foreign key(product_id) references product(id));
#外键添加方式二：ALTER TABLE USER add CONSTRAINT fk_user_product FOREIGN KEY(product_id) REFERENCES product(id);

4.索引面试题

4.1 索引为什么要用 b+ 树这种数据结构？

1.B+ 树的磁盘读写代价更低。B+ 树的内部没有指向关键字具体信息的指针，所以其内部节点相对 B 树更小，如果把所有关键字存放在同一块盘中，那么盘中所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相应的，IO 读写次数就降低了;
2.树的查询效率更加稳定。B+ 树所有数据都存在于叶子节点，所有关键字查询的路径长度相同，每次数据的查询效率相当。而 B 树可能在非叶子节点就停止查找了，所以查询效率不够稳定;
3.B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。

4.2 聚集索引和非聚集索引的区别

聚集索引在叶子节点存储的是表中的数据。
非聚集索引在叶子节点存储的是主键和索引列

名词解释：

聚集索引：表记录的排列顺序和索引的排列顺序一致。
非聚集索引：表记录的排列顺序和索引的排列顺序不一致

4.3 索引什么时候会失效，最左匹配原则是什么？

1. 违反最左匹配原则
2. 遇到范围查询（>、<、between、like）就会停止匹配,还有使用不等于（!= 、<>），like 中以通配符开头 (’%abc’)
3. 在索引列上做计算、函数、（手动或自动）类型转换等操作
4. 字符串不加单引号索引失效
5. or 连接索引失效
   最左匹配原则解释：

最左优先，以最左边的为起点任何连续的索引都能匹配上，如不连续，则匹配不上。
如：建立索引为 (a,b) 的联合索引，那么只查 where b = 2 则不生效。换句话说：如果建立的索引是 (a,b,c)，也只有 (a),(a,b),(a,b,c) 三种查询可以生效。

总结

因为sql优化的需要，今天抽时间研究了一下mysql的索引和视图的知识，发现了索引在SQL优化上的作用，通过程序生成的100W条记录，对比传统的条件查询，加索引后的表数据查询，结果几乎妙处；查询大数据时，把加索引的表比作火车，没加索引的表比作自行车一点不为过，建议大家学习一项新的技术时，最好先看官方的开发文档，原理讲的透彻而且权威。通过官方文档，我了解了sql查询的原理，对命中缓存，B+tree,BTree等底层查询有了认识，本文档总结用法，原理详情建议参考官方文档。另外针对视图也学习了一下，视图类似以前关联查询的结果集，更新视图，则其包含的表的列值也会随之改变。项目开发时，根据索引失效的场景进行sql优化。实际开发，建表时，建议使用powerdesigner工具生成sql,比较高效且易读。