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 SETname
=‘好书本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 索引什么时候会失效,最左匹配原则是什么?
- 违反最左匹配原则
- 遇到范围查询(>、<、between、like)就会停止匹配,还有使用不等于(!= 、<>),like 中以通配符开头 (’%abc’)
- 在索引列上做计算、函数、(手动或自动)类型转换等操作
- 字符串不加单引号索引失效
- 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,比较高效且易读。