数据库问题

Naby-

已于 2023-04-20 11:20:38 修改

阅读量302

点赞数

文章标签：数据库 mysql sql

于 2022-11-08 11:46:55 首次发布

本文链接：https://blog.csdn.net/weixin_45758442/article/details/127745919

版权

10.大表查询优化，即一张数据量很大表，怎么提升查询效率

11.慢查询问题，一条sql执行过长的时间，如何优化，从哪些方面？

12.SQL事务ACID特性

13.三大范式

14.join的区别

15.union和union all的区别

16.有哪些约束

17.SQL去重

18.truncate，drop，delete的区别，哪个可以回滚

19.mySQL求并集、交集、差集

20.count(*)，count(1)，count(列)的区别

1.数据定义DDL

建表

Create table (id int primary key auto_increment, name varchar)

增加一个字段

Alter table <表名> add column 字段名字段类型；

修改字段类型

Alter table <表名> modify 字段名字段类型；

字段重命名

Alter table <表名> change 原字段名新字段名字段类型；

删除字段

Alter table <表名> drop column 字段名；

删除表

Drop table <表名>；

表重命名

Rename table 原表名 to 新表名；

删除表中的所有记录

Truncate <表名>

2.数据操作DML

插入数据

Insert into table <表名>(字段1,字段2,……) values(数据1,数据2,……)

删除记录

Delete from <表名> where 查询条件；

更新记录

Update 表名 set 更新内容 where 查询条件；

查询记录

Select * from 表名；

3.存储过程

(千万不要忘记into)

调用采用call 输出结果用select

delimiter $

create procedure test(in id int,out name varchar(10))

begin

select emp_name into name from emp where emp_id = id;

end$

delimiter ;

set @id=1

call test(@id,@name);

select @name;

4.存储函数

调用直接用select

delimiter $

create function test(name varchar(10))

return int

begin

return(select id from emp where emp_name = name);

end$

delimiter ;

select test('Abel')

5.索引

定义

索引是对数据库表中一列或多列的值进行排序的数据结构

类别

普通索引 index

唯一性索引 unique index

主键索引 primary key

全文索引

单列索引

联合索引 mutil_idx(column1, column2, column3……)

Innodb根据存储形式可以分为聚簇索引和非聚簇索引(二级索引)

创建索引

create table test1 (id int primary key auto_increment,name varchar(10),index (name));

alter table test1 add index idx_gender(gender);

create index idx_gender on test1(gender);

删除索引

alter table test1 drop index idx_gender;

查看索引

show index from test1;

索引的优点

1.提升查询效率

2.唯一索引确保了数据的唯一性

3.确保了参考完成性，加速表之间的连接

4.在分组排序中减少时间，降低cpu消耗

索引的缺点

1.占用一定的存储空间

2.创建与维护成本高

3.对记录或表的更新不友好，降低更新表的速度

什么时候创建索引，即如何提高mysql的查询效率(单张表的索引数不超过6个)

1.字段有唯一性限制

2.频繁作为where查询条件的字段

3.group by，order by涉及字段也可创建索引以提升效率

4.update，delete的where条件列

5.distinct字段需要创建列

6.多表join连接操作的where条件

7.优先使用类型小的列创建索引

8.使用字符串前缀创建索引

9.区分度高(散列性高)的列适合作为索引

10.使用最频繁的列放到联合索引的左侧

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

什么时候不建议创建索引

1.where中使用不到的字段

2.数据量小的表不建议创建索引

3.有大量重复数据的列上不创建索引，区分度低

4.避免对频繁更新的表创建过多索引

5.不建议使用无序的值作为索引，如身份证等

6.删除不再使用或者很少使用的索引

7.不要重复定义冗余或重复的索引

索引失效的情况

1.全值匹配我最爱(索引最佳)，可能存在使用索引查询所有数据后还需要回表操作，性能不如全表扫描，这时候索引就不管用了

2.不遵守最左前缀匹配原则，如联合索引abc，针对c进行查询，则用不上了

3.不按照递增顺序插入主键

4.计算、函数、类型转换（自动或手动）导致索引失效

5.范围条件右边的列索引失效

6.不等于（!= 或者 <>）索引失效

7.is null可以使用索引，is not null无法使用索引(相当于!= null)

8.like以通配符 % 开头索引失效，还是需要全表扫描

9.OR前后存在非索引的列，一个带索引一个不带索引，还是要对不带索引的进行全表扫描

10.数据库和表的字符集不匹配

6.视图

定义

与基本表不同，是个虚表，数据库只存放视图的定义，不存放视图对应的数据，数据仍存放在原来的基本表中。视图就像是个窗口，透过视图可以看到数据库中自己感兴趣的数据及其变化。

创建视图

create view '视图名' as select ……

删除视图

drop view '视图名'

查看视图

show create view '视图名'

show tables;

desc '视图名';

更新视图

create or replace view '视图名' as select ……;

alter view '视图名' as select ……;

视图的优点

1.把经常使用的数据定义为视图，简化了查询操作；

2.控制数据的访问，保证数据的安全性；

3.减少数据冗余

视图的缺点

维护成本较高(如果基表变了，视图表也需要改变)

7.游标

1.声明游标 declare cursor for

2.打开游标 open

3.使用游标 fetch 游标名 into

4.关闭游标 close

例子：

delimiter $

create procedure get_count_by_limit_total_salary(in limit_total_salary double ,out total_count int)

begin

    declare sum_salary double default 0.0;

    declare emp_count int default 0;

    declare emp_salary double ;

    declare emp_cursor cursor for select salary from emps order by salary desc;

    open emp_cursor,

    repeat

        fetch emp_cursor into emp_salary;

        set sum_salary = sum_salary+emp_salary;

        set emp_count = emp_count+1;

        until sum_salary>=limit_total_salary

    end repeat

    set total_count = emp_count

    close emp_cursor

end$

delimiter ;

8.数据库结构优化

1.范式优化：消除冗余

2.反范式优化：适当增加冗余以减少join

3.拆分表：冷热数据分离

4.增加中间表

5.优化数据类型，选择符合存储需要的最小数据类型

6.优化数据插入速度，先禁索引，等插入完成后再开启索引，alter table <表名> disable keys

7.使用非空约束

9.Sql语句的优化

多表查询的优化 select a.name from a left join b on a.id=b.id

1.外连接时，给被驱动表添加索引

2.内连接时，优化器可以决定驱动表，如果只有一个表有索引，则该表会被作为被驱动表；如果两个表都有索引，则会选择用小表驱动大表。

子查询优化(因为内层查询结果是一个临时表，查询再删除，会消耗过多cpu；临时表不具有索引，查询效率低)

1.使用join代替子查询

2.尽量不要使用not in或not exists，用left join XXX on XXX where XX is null代替

排序优化

在where子句和order by子句中使用索引，若两者字段一致，则单列索引，否则联合索引

Group by优化

1.对group by字段创建索引

2.where效率高于having，能用where解决就不要用having

3.能不order by就不要order by

分页查询优化limit

1.在主键上完成排序操作，在根据主键的返回值查询其他值

2.在主键自增的表当中，把limit查询转换成某个位置的查询

limit 20000,10 →where id>20000 limit 10

10.大表查询优化，即一张数据量很大表，怎么提升查询效率

1.合理建立索引

2.水平分区，如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能

3.垂直分区

4.读写分离，主库负责写，从库负责读

5.限制查询范围

11.慢查询问题，一条sql执行过长的时间，如何优化，从哪些方面？

1.是否涉及多表的联表或者子查询，如果有，看是否能将相关字段冗余或者合并成临时表

2.涉及链表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合

3.如果以上两种都不能操作，非要链表查询，那么考虑对相对应的查询条件做索引。

4.针对数量大的表进行历史表分离（如交易流水表）

5.数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql有自带的binlog实现主从同步

6.explain分析sql语句，查看执行计划，分析索引是否用上，分析扫描行数等等

7.查看mysql执行日志，看看是否有其他方面的问题

12.SQL事务ACID特性

事务：一组逻辑操作(DML)单元，使数据从一种状态变换到另一种状态

Atomicity原子性：事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

Consistency一致性：事务执行前后，数据从一个合法性状态变换到另一个合法性状态，这种状态是语义上的，跟具体业务有关。比如账户中有200元，转账300元，结果账户余额为-100元，不符合实际情况。

Isolation隔离性：指一个事务的执行不能被其他事务干扰，并发执行的各个事务之间不能相互干扰。

Durability持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的。

原子性是基础，隔离性是手段，一致性是约束条件，持久性是目的

隔离级别

脏读：一个事务读取到另一个事务尚未提交的数据。A事务执行过程中，B事务读取了A事务的修改。但是由于某些原因，A事务可能没有完成提交（读未提交），发生RollBack了操作，则B事务所读取的数据就会是不正确的。用读提交来解决

不可重复读：一个事务中两次读取的数据内容不一样(侧重于修改)。B事务读取了两次数据，在这两次的读取过程中A事务修改了数据（读已提交），B事务的这两次读取出来的数据不一样。B事务这种读取的结果，即为不可重复读。用重复读来解决，在事务开启时，不允许有修改操作

幻读：一个事务中两次读取的数据量不一样(侧重于增加)。事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的数据不一样。用序列化解决

13.三大范式

第一范式(原子性)

确保数据表中每个字段的值必须具有原子性，也就是说数据中每个字段的值不可再次拆分的最小数据单元。

第二范式(一张表就是一个独立的对象，一张表只表达一个意思)

在满足第一范式的基础上，满足数据表里的每一条数据记录都是可以唯一标识的，而且所有的非主键字段，都必须完全依赖于主键，不能依赖主键的一部分，如联合主键，非主键字段需要依赖一整个联合主键，不能仅仅依赖其中的一个，否则会产生数据冗余、插入异常、删除异常、更新异常问题。

第三范式(每个非键属性依赖于键，依赖于整个键，并且除了键别无他物)

确保数据表中的每一个非主键字段都和主键字段直接相关，要求数据表中的所有非主键字段不能依赖于其他非主键字段，消除传递依赖。

优点：消除数据库中的数据冗余。

缺点：降低查询效率，可能要关联多张表。

14.join的区别

left join 以左表作为主表，匹配右表，若右表匹配成功则输出右表对应的值，否则为null，即保留左表中的所有值以及右表中联结字段相等的记录

join其实就是取交集，只返回两个表中联结字段相等的记录

15.union和union all的区别

union有一个自动去重处理

union all则是将所有的数据进行合并，不进行去重处理，效率比较高

16.有哪些约束

1.非空约束 not null

2.主键约束 primary key

3.唯一性约束 unique

4.外键约束 foreign key

5.查询约束 check

17.SQL去重

1.distinct

2.group by

3.窗口函数去重 row_number() over(partition by 字段) 然后选择row_number为1的字段

18.truncate，drop，delete的区别，哪个可以回滚

delete是每次从表中删除一条记录，并且同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作。

truncate是一次性从表中删除所有记录

drop是直接删除表

执行速度：drop>truncate>>delete