现在位置: 首页 / 开发软件 / 正文

MySQL面试查缺补漏

https://www.bilibili.com/video/BV1aE41117sk?p=7 非常好的索引课

https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=59 SQL高级教程，很好

基础

满足ACID的一组操作，可以通过commit提交的一个事物，也可以用rollback回滚

  1. Atomicity 原子性：不可分，要么全部成功，要么全部失败
  2. Consistency 一致性：数据库执行事物前后保持一致性，所有事物对同一个数据的读取结果相同
  3. isolation 隔离性：一个事物最终修改前对其他事物不可见
  4. durability 持久性：一旦事物提交，则所作的修改会永远保存道数据库中，即使系统崩溃，事物执行的结果也不能丢

索引（简单介绍，详细在高级篇）

索引：帮助MySQL高效获取数据的排好序的数据结构

分类

  • 单值索引：对一个列建索引，一个表可以有多个
  • 唯一索引：这一列必须每个值都唯一（允许多个空或null）
  • 联合索引：一个索引包含多个列

名词解释：

  • 聚集索引：按主键构建B+树，data放当前行的所有数据，InnoDB是聚集索引
  • 非聚簇索引：按主键构建B+树，data放当其他文件，MyISAM室非聚集索引
  • 辅助索引（二级索引）：按一个字段构建B+树，data放主键的ID
  • 覆盖索引：搜索的字段刚好都是索引字段，这样不用回表查询

主键：主键 = 非空值+唯一索引，主键不实际存在只是个逻辑

  • 主键：主键的B+数结构叶子节点存的是整行的值，非主键索引存的是主键ID image-20210712123649235

数据结构

  • 红黑树（某些场景不适应，所以没用，数据量太大了还是会慢）
  • Hash表（很好）
  • B+Tree（主要）

优势

  • 提交检索效率，降低数据库IO成本
  • 通过索引对数据排序，降低排序成本

劣势

  • 占用空间
  • 降低插入更新删除操作的时间
# 【创建索引】在city表为city_name列创建一个名为idx_cn的索引
CREATE INDEX idx_cn on city(city_name)
​
# 【查看索引】
SHOW INDEX from city
​
# 【删除索引】
DROP INDEX idx_cn FROM on city
​
#【Alter命令】创建主键索引（唯一且不能为null）
alter table tb_name add primary key(column_list)
​
#【Alter命令】创建唯一索引，创建普通索引把union换成index
# index_name索引名称，column_list列名
alter table tb_name add union index_name(column_list)
​
#【Alter命令】全文索引
alter table tb_name add fulltext index_name(column_list)
​

索引的设计原则

  1. 查询频率高，数据量大的表
  2. where中经常出现的
  3. 尽量用唯一索引，区分度越高索引效率越高
  4. 索引多了也不好，影响插入更新删除效率，并且Mysql还要选择一个最好的索引也需要花时间
  5. 索引的长度短（因为创建索引也要用硬盘存）
  6. 最左前缀，N个列创建组合索引，相当于创建了N个索引，如果查询时where子句用了组成该索引的前几个字段，那么这条查询SQL可以用联合索引提高效率

视图

概念：虚拟表，并不在数据库中实际存在。使用时动态生成，视图就是SELECT语句执行后返回的结果集。创建视图主要工具就在SQL上了。

==视图比普通表的优势==

  1. 简单：使用视图不需要关心后面对应表结果、关联条件、筛选条件
  2. 安全：只能访问被允许查询的结构，通过视图可以限制到行和列
  3. 数据独立：表结构增加列，视图不变。 表结构更新就更新视图
# 如果要联合两个表查询，很麻烦
select c.*,t.country_name from city c, contry t where c.country_id = t.country_id;
​
# 【创建视图】用视图替换
create view view_city_country as 上面的语句
​
# 【查询视图】直接当表操作
select * from view_city_country
​
# 【更新视图】实际上更新基表的数据
update view_city_country set city_name =  '西安' where city_id = 1;
​
# 【删除视图】
drop view view_city_country
​
# 【查看有哪些视图】
show tables

存储过程和函数

概念：事先编译好的存储在数据库中的一段SQL语句的集合 好处：减少开发量，降低数据库与应用服务器之间的传输

  • 函数：有返回值
  • 过程：无返回值
# 【创建存储过程】
delimiter $    // 申明分隔符是$，不然默认是;
create procedure test1()
    begin
        select * from city;
    end$
​
# 【调用存储过程】
call test1()$
​
# 【查看存储构成】三种方式
select name from mysql.proc where db='city'; # mysql数据库中所有存储信息
show procedure status;
show create procedure test1; # 看这个存储过程的怎么定义的
​
# 【删除存储过程】
drop procedure test1;

存储过程语法

# 待学习，暂时没时间
https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=16

触发器

概念：可以在insert/update/delete之前或之后执行触发器中定义的SQL语句

用处：帮助确保数据完整性、日志记录、数据校验

触发器代码

# 待学习
https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=28

日志

  1. 错误日志
  2. 二进制日志
  3. 查询日志
  4. 慢日志：默认关闭，开启后设置long_query_log=10，sql时间超10秒就需要记录