数据库基础知识---------------------------（3）
MYSQL的索引
用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行。按实现方式分为Hash索引和B+Tree索引
- 单列索引
- 普通索引 允许在定义索引的列中插入重复值和空值
- 唯一索引 索引列的值必须唯一，但允许有空值
- 主键索引 在创建表时，MySQL会自动在主键列上建立一个索引.唯一非空
- 组合索引 列值的组合必须唯一
create index indexname on table_name(column1(length),column2(length));-- 创建索引的基本语法-- 普通索引 create index index_phone_name on student(phone_num,name); -- 操作-删除索引 drop index index_phone_name on student; -- 创建索引的基本语法-- 唯一索引 create unique index index_phone_name on student(phone_num,name);select * from student where name = '张三';
select * from student where phone_num = '15100046637';
select * from student where phone_num = '15100046637' and name = '张三';
select * from student where name = '张三' and phone_num = '15100046637';
【靠左原则】
三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段 才能够进行匹配。
而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。
- 创建索引
- 创建表时直接指定
create table student( sid int primary key, card_id int, name varchar(20), gender varchar(20), age int index index_name(name) -- 给name列创建索引 unique index_card_id(card_id) -- 给card_id列创建索引 );
- 直接创建
create index index_gender on student(gender);
create unique index index_card_id on student(card_id);
- 修改表结构时添加
alter table student add index index_age(age);
alter table student add unique index_card_id(card_id)
- 查看索引
库中：
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';表中：
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';表中：
show index from student;
- 删除索引
drop index 索引名 on 表名
-- 或
alter table 表名 drop index 索引名
- 索引的优缺点
- 优点
- ①大大加快数据的查询速度
②使用分组和排序进行数据查询时，可以显著减
少查询时分组和排序的时间
③创建唯一索引，能够保证数据库表中每一行数 据的唯一性
④在实现数据的参考完整性方面，可以加速表和表之间的连接- 缺点
- ①创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
②索引需要占据磁盘空间
③对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度- 创建索引的原则
更新频繁的列不应设置索引
数据量小的表不要使用索引
重复数据多的字段不应设为索引
首先应该考虑对where 和 order by 涉及的列上建立索引
MYSQL的事务
在MySQL中的事务（Transaction）是由存储引擎实现
- 事务处理可以用来维护数据库的完整性
- 事务用来管理 DDL、DML、DCL 操作
- 事务的主要操作
开启事务：BEGIN 或 Start Transaction
提交事务：Commit
回滚事务：Rollback- set autocommit=0 禁止自动提交
- 事务的特性
- 原子性
事务是一个不可分割的主体，开启后的操作要么全做要么全不做- 一致性
系统从一个正确状态迁移到另一个正确的状态- 隔离性
每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不可见- 持久性
事务一旦提交，其结果是永久性的- 事务的隔离级别
- set session transaction isolation level read committed;
- show variables like '%isolation%';
-- 查看隔离级别- 读未提交(Read uncommitted)
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读- 读已提交(Read committed)
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读- 可重复读(Repeatable read)（默认）
就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。- 串行(Serializable)
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
MYSQL的视图
视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
- 创建视图
create or replace view view_name
as
select ename,job from emp;
update view1_emp set ename = '周瑜' where ename = '鲁肃'; -- 可以修改
insert into view1_emp values('孙权','文员'); -- 不可以插入
- 修改视图
alter view 视图名 as select语句
- 查看表和视图
show full tables;
- 重命名视图
-- rename table 视图名 to 新视图名;
- 删除视图
-- drop view 视图名[,视图名…];
- 视图不可更新
MYSQL的优化
- explain分析执行计划
- 通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序
- explain select * from user where uid = 1;
- show profile分析sql
- show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
- select @@have_profiling;
set profiling=1; -- 开启profiling 开关；
show profiles;
show profile for query 8;
--查看到该SQL执行过程中线程8的状态和消耗的时间- 使用索引优化
- -- 创建组合索引
- create index idx_seller_name_sta_addr on tb_seller(name,status,address);
- 给表创建一个主键
- 优化insert语句
- 合并插入
- 优化子查询
- 被更高效的连接（JOIN）替代
- 连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
- type类型 ：system>const>eq_ref>ref>range>index>ALL
MYSQL常见的窗口函数
MySQL 8.0 新增窗口函数，窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。
window_function ( expr ) OVER (
[PARTITION BY 分组的列...
ORDER BY 排序列...
rows between 起始行 and 结束行
unbound preceding 表示第1行
n preceding
n表示数字, 表示向上n行, 例如: 3 preceding表示向上3行.
current row 表示 当前行
n following n表示数字, 表示向下n行, 例如: 3 following 表示 向下 3行.
unbound following 表示最后1行
]
)
- 序号函数
- row_number()
- select dname,salary,row_number() over(partition by dname order by salary) 等级 from employee;-- 【1，2，3，4】
- rank()
- select dname,salary,rank() over(partition by dname order by salary) 等级 from employee; -- 【1，2，2，4】
- dense_rank()
- select dname,salary,dense_rank() over(partition by dname order by salary) 等级 from employee; -- 【1，2，2，3】
--求出每个部门薪资排在前三名的员工- 分组求TOPN select * from ( select dname, ename, salary, dense_rank() over(partition by dname order by salary desc) as rn from employee )t where t.rn <= 3
- 分布函数
- cume_dist()
- 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
- 应用场景：查询小于等于当前薪资（salary）的比例
- percent_rank()
- 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
- 应用场景：不常用
select dname, ename, salary, cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组 cume_dist() over(partition by dname order by salary) as rn2 from employee;
- 前后函数
- lag(列名,n,默认值)
- lead(列名,n,默认值)
- 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
- 应用场景：查询前1名同学的成绩和当前同学成绩的差值
select dname, ename, hiredate, salary, lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time, lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time from employee;
- 头尾函数
- first_value(列名)/last_value(列名)
- -- 注意, 如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
- 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
- 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
select dname, ename, hiredate, salary, first_value(salary) over(partition by dname order by hiredate) as first, last_value(salary) over(partition by dname order by hiredate) as last from employee;
- 其他函数
- nth_value(列名,n)
- 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
- 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
-- 查询每个部门截止目前薪资排在第二和第三的员工信息 select dname, ename, hiredate, salary, nth_value(salary,2) over(partition by dname order by hiredate) as second_score, nth_value(salary,3) over(partition by dname order by hiredate) as third_score from employee
- ntile(n)
- 用途：将分区中的有序数据分为n个等级，记录等级数
- 应用场景：将每个部门员工分成3组并按照入职日期排序
-- 根据入职日期将每个部门的员工分成3组 select dname, ename, hiredate, salary, ntile(3) over(partition by dname order by hiredate ) as rn from employee;
- 开窗聚合函数
- SUM,AVG,MIN,MAX,COUNT
- sum()纵向求和
- count()横向求个数
原文地址：https://blog.csdn.net/weixin_54353942/article/details/142372082
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！