MySQL吊打面试官总结

最新推荐文章于 2024-08-02 22:16:19 发布

BinBin_Bang

最新推荐文章于 2024-08-02 22:16:19 发布

阅读量263

点赞数 1

分类专栏： sql 文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/biubiubiubibibi/article/details/125846130

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL

内部结构

三明治结构

连接层
- 处理客户端到MySQL的连接
- 多线程并发连接一个连接一个connection
服务器层
- 查询解析, 查询缓存, 函数, 存储过程, 触发器, 视图, 表锁等
存储引擎层
- 索引, 事务, 统计信息

语句

查看信息

show
- show create table tablename
- show columns from tablename
- show index from tablename
- show status like
  
  一般用于查看数据库的运行信息
  - –查看查询时间超过long_query_time秒的查询的个数。
    - show status like ‘slow_queries’;
  - –查看创建时间超过slow_launch_time秒的线程数。
    - show status like ‘slow_launch_threads’;
  - –查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
    - show status like ‘table_locks_waited’;
  - –查看立即获得的表的锁的次数。
    - show status like ‘table_locks_immediate’;
  - –查看激活的(非睡眠状态)线程数。
    - show status like ‘threads_running’;
  - –查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
    - show status like ‘threads_created’;
  - –查看当前打开的连接的数量。
    - show status like ‘threads_connected’;
  - –查看线程缓存内的线程的数量。
    - show status like ‘threads_cached’;
  - –查看试图连接到MySQL(不管是否连接成功)的连接数
    - show status like ‘connections’;
  - –查看delete语句的执行数
    - show [global] status like ‘com_delete’;
  - –查看update语句的执行数
    - show [global] status like ‘com_update’;
  - –查看insert语句的执行数
    - show [global] status like ‘com_insert’;
  - –查看select语句的执行数
    - show [global] status like ‘com_select’;
  - –查看MySQL本次启动后的运行时间(单位：秒)
    - show status like ‘uptime’;
- show variables like
  
  一般用于查看数据库的一些配置信息

rename table tablename1 to tablename2

drop table if exits tableName

alter table tablename

add [FULL | UNIQUE] index indexname ( columns[(length)])
add column colname 属性
modify column colname 属性
change column colname1 colname2 属性
rename column colName1 to colName2
drop COLUMN colName
drop index|key indexName
对表自身的处理
- CHARACTER SET = utf8
- COMMENT = ‘这是一个表注释’
- AUTO_INCREMENT = 15; # 自增度
- add primary key（columnName）
- TYPE = MYISAM #修改引擎
- disable keys
- enable keys

insert

insert into tablename () values (),();
insert into tablename () values ();
insert into tablename select * from …
插入冲突则更新
- insert into tablename () values () on duplicate key update keyname = xxx;
- REPLACE语句(慎用)
  
  https://blog.csdn.net/qq_32331997/article/details/84834623

create

create table tablenameTemp like tablename;
create table tablename();

select

执行顺序
- 1. from
- 1. where
- 1. group by
- 1. having
- 1. order by
- 1. limit 注意 limit永远在最后
select… into
- select v1, v2, v3 into v1,v2,v3 from…
- select … into outfile filepath
select sql_cache * from…where…; #对某一个查询添加查询缓存
select SQL_NO_CACHE * from…where…; #对某一个查询不添加查询缓存
JOIN
- inner join
  - SELECT * from t1 a inner JOIN t2 b on a.id = b.id and a.id = 3; #这种写法比下面的写法要好
  - SELECT * from t1 a inner JOIN t2 b on a.id = b.id where a.id = 3;
- left join
  - 左右查询不应该在on上加一起的条件, 因为无效
  - SELECT * from t1 a LEFT JOIN t2 b on a.id = b.id where b.id = 3;
select * from … for update # 对一条查询添加写锁
SELECT … LOCK IN SHARE MODE
- 应用场景适合于两张表存在关系时的写操作
- 拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就ok了

update

update ci_policy a Inner JOIN ci_policy_temp b on a.id = b.id set a.operator = b.operator
update… from
- update a set a.name = b.name from a, b where a.bid = b.id;

delete from

delete a from ci_policy a Inner JOIN ci_policy_temp b on a.id = b.id

优化

SQL规范

where 条件中不必要的表达式
- a - 1 > =2 , 直接写成 a> = 3 就行
where后面条件的顺序. 过滤数据少的优先(过滤大的, 进行IO比较就会比较多, 所以, 过滤大的一般都是放在最后).
用>= 替换> ; 如>=4, 直接开始定位到4, 而>3, 每次定位到3, 然后在把数据比较

on 、 where 、 having 这三个都可以加条件的子句中， on 是最先执行， where 次之， having 最后. 所以你懂的. (注意lefet. rigth join 在on上副作用)

  - SELECT * from t1 a inner JOIN t2 b on a.id = b.id and a.id = 3; #这种写法比下面的写法要好

  - SELECT * from t1 a inner JOIN t2 b on a.id = b.id where  a.id = 3;

  - # 左右查询不应该在on上加一起的条件, 因为无效

  - SELECT * from t1 a LEFT  JOIN t2 b on a.id = b.id  where  b.id = 3

小表驱动大表原则
- 在多表关联的时候, 因为mysql的多表解析循序是从右到左, 所以呢, 最小的表放在最右面, 最大的表放在最左.
  - select * from ci_policy_prem a INNER JOIN ci_policy_risk b ON a.ci_policy_risk_id = b.id INNER JOIN ci_policy c ON b.ci_policy_id = a.id;
- in 和 Exist 的选择
  - in 和 exist 解析过程
    - in 是先解析 in 里面内部查询的,
      exist是先解析外部查询.
      所以外小内大用Exist,
      外大内小用in

索引

作用
- 选择 (where 和覆盖索引会用上)
- 排序(order by / group by 会用上)
索引选择性
- 列中不同值的数目与表中记录数相比, 越接近于1, 索引的效率越高.
- 所以如果某一个列中包含大量重复数据, 那么索引选择性会很小, 建立索引的意义不大
索引建立
- 连接表, on 连接条件,如何选择哪个表建立索引
  - 两表连接
    - 左连接 Left join
      - 左连接的特性是左表必须有, 所以, 如果索引建立在左表, 还是会扫描全部左表, 所以建立在右表连接字段上.
    - 右连接 Right join
      - 右连接特性右表必须有, 所以同理, 索引建立在左表.
索引失效
- 表达式和函数等
  - a-1 >=2;
- 不符合最左原则
  
  不符合最左原则, 那么在符合之前的索引列还是有效的, 是指从不符合开始的地方往后都无效,
  比如
  index(name, age, sex)
  使用: where name = ‘赵四’ and age >=30 and sex =1; 这条SQL是会用到索引的, 只是只对name和age用到了.
  - 注意:
    - 最左原则导致组合索引失效, 不是说全部失效, 而是只从哪里开始(不包含他本身)失效
      - 不符合最左原则, 那么在符合之前的索引列还是有效的, 是指从不符合开始的地方往后都无效,
      - 比如
      - index(name, age, sex)
      - 使用: where name = ‘赵四’ and age >=30 and sex =1; 这条SQL是会用到索引的, 只是只对name用到了.和 age
  - 组合索引, 使用的时候不是按顺序使用
    - 这里说的按顺序值的是出现循序, 不是说一定要紧挨着
      - create index idx_article_cv on article(category_id, views);

#这个组合索引category_id,views 的使用就是没有紧挨着, 但是他们是顺序出现的, 是满足最左原则的.
EXPLAIN SELECT id.author_id FRON article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;

	- 范围扫描会阻断最左原则传播

		- 比较符 >  < 等, 还有!=也会哟

between and
(like ‘xxx%’ 注意这种不会阻断, 很奇怪)
in()
上面这些的后面组合索引失效
- like ‘%XXX’

			- 可以用覆盖索引解决, 让索引生效, 这个覆盖索引也能让 like

				- 覆盖索引, 就是查询列中的字段都属于索引字段(没有最左原则)

	- order by / group by 使用不同表中的列

order by / group by 使用不满足最左原则

		- create index idx_article_ccv on article(category_id. coments, views);

这条就不满足了最左原则, 因为中间有个范围, 如果> 1 改成 = 1 就满足了最左原则

EXPLAIN SELECT id.author_id FRON article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;

- or 两边有一个不为索引列

案例
- 最左案例.txt

临时表

做表更新和删除之前, 一般都需要备份, create table ci_policy_temp like ci_policy; insert into ci_policy_temp select * from ci_policy where …;
update 大表之前, 先把要查出来的数据放入临时表中, 然后用主键关联去更新
- update ci_policy_temp a inner join ci_policy b on a.id = b.id set b.xxx = yyy;
同理删除表中数据也是一样,;
- delete b from ci_policy_temp a inner join ci_policy b on a.id = b.id;

缓存

查询缓存
join 缓存

表优化

去碎片
- optimize table tablename
适量的反范式
不常用字段, 且占用空间大的字段, 单独拎出来
数据结构选择
- 小的优先
  - 状态标识字段用Smallint
  - varchart 的长度别滥用
  - 时间可以用TIMESTAMP
- 主键尽量用auto_increament

其他

插入大量数据的时候先关闭索引, 等插入完成在开启索引
- alter table disable keys
- alter table enable keys
在进行大量数据插入的时候可以用程序先根据主键进行排序, 这样也能插入的更快
更新存储引擎的统计信息.
- 查询解析是根据统计信息进行的, 所以跟新统计信息可以让解析的更到位.

存储引擎

InnoDB

索引结构
- B+树
  - 非叶子节点不存储数据, 叶子节点才存储数据
    - 好处
      - 每一个非叶子节点的数据块很小, 就能减少磁盘IO.
  - 主键的索引
    - 数据的存储是按照主键索引进行存储的.
  - 非主键索引
    - 非主键索引, 执行主键索引

MyISAM

也是B+树结构
- 不同的是, 数据存储都不是按照主键和非主键索引顺序, 索引最终指向实际数据存储位置

哈希索引

对比

MyIsam 不支持事务, MyIsam读效率高, Myisam锁粒度是表, MyIsam读写表锁, 写锁优先->所以不适合写操作频繁, 会造成读写阻塞.

高可用

主从复制

作用
- 备份
- 读写分离
原理
实战

分库分表

索引

Innodb索引

存储结构
算法

myscam索引(写错了估计)

哈希索引

使用哈希算法, 将数据值计算成hash值, 存储
只能做等值查询, 不能做范围查询(所以也不能排序)
不支持组合索引
要求重复的值比较低才有意义, 不然hash冲突太严重
比如在supplierCode上建立的就是等值索引, 因为我们找一家保险机构, 是确定要找一家, 而不是范围查找

实用工具

分析工具

mysqldumpslow(自带)
- 工作常用参考
  - 得到返回记录集最多的 10 个SQL
    - mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log
  - 得到访问次数最多的 10 个SQL
    - mysqldumpslow -s c -t 10 /var/bin/mysql/xx-slow.log
  - 得到按照时间排序的前10条里面含有左连接的查询语句
    - mysqldumpslow -s c -t 10 -g “left join” /var/bin/mysql/xx-slow.log
  - 另外建议在使用这些命令结合｜和 more 使用，否则有可能出现爆屏现象
    - mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log | more
explain
- 元素
  - id
    - id越大, 越先执行, id相同从上往下执行
    - 子查询的id比外查询大, 连接表右边的表比左边的表大
  - type
    - sql语句执行级别
      - ALL
        
        全表扫描, 此时对应的rows列应该会是表的行数
      - index
        
        全索引树扫描, 和全表扫描的不同在于它不会扫描表的非索引文件数据数据. 只会扫描索引树.如在不带where条件查询且select列都是索引列的select 中会出现
      - rang
        
        指定的范围扫描, 常出现在索引查询中有>=/>/
      - index_merge
        
        组合使用多个索引
      - fulltext
        
        全文索引
      - ref
        
        索引得到的结果集可能大于一列. (即非唯一索引或者主键索引的使用), 注意区分explain中的ref列
      - equ_ref
        
        连表查询中索引得到的结果集唯一(即关联条件都是唯一或主键索引)
      - const
        
        单表使用的索引得到的结果集唯一
      - system
        
        const中的一种特例, 就是待查询的表或临时表只有一行数据
  - key
    - 用到的索引
  - ref
    - 连接查询使用的索引或这单表查询使用到的常量(constant)
  - key_len
    - 这个在组合索引中可以很方便的看到底使用到了几个索引
      - key_len长度计算方法
  - extra
    - 执行策略
      - DISTINCT
        
        得到了一种结果行后不会得到另一个一模一样的结果行(查询列中使用到了distinct)
      - Using filesort
        
        结果集需要在服务器层进行二次排序. 比如用到了group by / order by且不是用索引完成的排序
      - Using temporary
        
        服务器层使用到了临时表, 比如连表查询(还有子查询,待验证),比如group by/ order by
      - Using join buffer
        
        常见于连表查询中没有使用到索引去关联, 需要用缓冲区去存储中间结果
      - Using index
        
        结果集都是索引列
        
        如果同时出现using where，表明索引被用来执行索引键值的查找
        如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
      - Using index condition
        
        索引条件推送(不太明白), 貌似是说所用的索引是二级索引(非主键索引)
      - Using where
        
        仅仅使用了索引就确定了结果集, 没有读取实际的行
  - partition
    - 如果有分区, 使用的分区为
  - rows
    - 可能查询的行数
show profile
- 作用
  - 分析SQL每一步资源消耗情况
- 默认关闭, 使用前先开启, 开启后默认记录15条
  - set profiling=on;
- show profiles;
  - 列出最近的15条SQL
- 诊断SQL ： show profile cpu, block io for query 上一步前面的问题 SQL 数字号码；
  - 参数备注
    - ALL
      - 显示全部开销信息
    - BLOCK IO
      - 显示块 IO 相关开销
    - CONTEXXTSWITCHAES
      - 上下文切换相关开销
    - CPU
      - 显示 CPU 相关的开销信息
    - IPC
      - 显示发生和接收相关的信息
    - MEMORY
      - 显示内存相关的信息
    - PAGE FAULTS
      - 显示页面错误相关的开销信息
    - SOURCE
      - 显示和 Source_function, Source_file, Source_line 相关的开销信息
    - SWAPS
      - 显示交换次数相关的开销的信息。
- 日常开发需要注意的事项
  - converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了
  - Create tmp table 创建临时表
    - 拷贝数据到临时表
    - 用完再删除
  - Copying to tmp table on disk 把内存中的临时表复制到磁盘，危险！！！！
  - bocked

日志

原则

日志先行

数据安全

备份技术

Mysqldum
Mysqlhotcopy
- 原理就是文件备份, 所以只能用在MyIsam
导入导出
- select … into outfile filepath
- load data infile filepath
工具

并发控制

事务
- ACID
  - 一致性
  - 原子性
  - 可见性/隔离性
  - 持久性
- 隔离级别
  - 4种
    - 读未提交
      - 可能会导致脏读
    - 读已提交(不可重复读)
      - 可能出现不可重复读
    - 可重复读(默认)
      - 也不能防止幻读，不过在此基础上的MVCC可以防止幻读
    - 序列化
      - 串行
- 多事务并发问题
  - 死锁
    - 失败重试解决
  - 脏读
    - 读未提交: 读取到别个事务未提交的事务, 然后别个事务又回滚了事务, 所以读出来的数据是一个脏数据
    - 隔离级别设置成可重复读
  - 不可重复读
    - 同一事务, 先后两次读取到的内容不一致
    - 设置隔离级别成可重复度
  - 幻读
    - 同一事务, 先后两次读取到的行数不一致
    - MVCC间隙锁解决.
- 事务的实现原理
  - Innodb 的MVCC(隐式加锁)
    - 通过增加两个隐藏字段实现，一个是事务开始序号，一个是事务过期序号（序号只是一个全局唯一ID）
      - select
        
        当前事务 >= 所在行的事务开始序号
        当前事务 <= 所在行的事务结束序号（或者所在行的事务结束序号为空）
      - delete
        
        事务结束序号 = 当前事务序号
      - update
        
        undo log拷贝当前选中行，且选中行的事务结束序号 = 当前序号
        新的行，事务开始序号 = 当前序号
      - insert
        
        事务开始序号 = 当前事务序号
        事务结束序号 = null
    - undo log
      - undo log

undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志
当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃
当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

	- 锁

		- 锁粒度

			- 行级锁

				- 显式加锁

					- select ... for update

			- 表级锁

				- 显式加锁

					- select... lock  in shar mode
					- 服务器层加表锁

						- Lock Table tablename read/write
						- Unlock Tables

		- 锁策略

			- 分类

				- 共享锁

					- 读锁

				- 排他锁

					- 写锁

			- 实操

				- 如果当前线程对某一个表加了读锁, 则当前线程只能读, 不能写; 其他线程可以读该表, 也可以写该表, 不过写该表的时候是需要那个线程释放读锁之后, 不然会一直阻塞.
				- 如果当前线程对一个表加了写锁, 则,当前线程可以读, 也可以写,  别的线程就不能读该表, 也不能写该表. 
				- MyIsam 默认当执行select的时候会对表添加读锁, 执行增删改的时候会对表添加写锁.
				- 子主题 4

		- 锁查看

			- show open tables; # 查看所有的表是否加锁情况

	- DDL 语句会使所有的事务强制提交比如Alter Table, Local Table

- 事务日志

	- 日志先行

		- mysql 会把日志先存储在缓存中, 也就是内存. 然后会把内存中的内容写入到日志文件中(redo log和 undo long), 然后在慢慢同步的磁盘中持久化
		-

主从复制

日志方式配置
Mycat工具
- 作用
  - 主从复制
  - 读写分离
  - 分库分表
  - 多数据源整合
- 原理
  - 拦截-解析-路由
  - java项目的数据源直接连接mycat的数据库，即java项目把mycat当做一个数据库，不需要关心mycat具体的实现方式。
- 重要文件
  - ①schema.xml：定义逻辑库，表、分片节点等内容
  - ②rule.xml：定义分片规则
  - ③server.xml：定义用户以及系统相关变量，如端口等
- 数据库分布式痛点
  - 事务一致性
  - 跨节点join
  - 跨节点排序
  - 多数据源连接管理

存储过程

存储过程和函数的区别

存错过程没有返回值
函数有返回值

常用内置函数

加密函数
字符串
- concate(str1,str2,…)
- CONCAT_WS(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔
- replace(str, str1, str2);# 将str中包含的str1, 替换成str2
- insert(str, index, length, str1);
- GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
- reverse(str); # 将str反转
日期
- DATE_ADD(date, interval num MONTH|YEAR|DAY);
- DATE_FORMAT(date, ‘%Y-%m-%d’);
- YEAR(date);
- METHON(date);
- DAY(date);
- now();
控制流
- IF(test, var1, vat2); #如果test为true, 返回var1, 否则var2
- IFNULL(var1, var2); #如果var1为真, 返回var1, 否则var2
- Case when exp then var1 when exp then var2 else var3 end
- case( var1 as 数据类型); # 把var1 转成指定数据类型
系统信息函数
- datebase(); #当前数据库名
- use(); # 当前登录的用户
数学
- GREATEST(x1,x2,…,xn)返回集合中最大的值
- LEAST(x1,x2,…,xn) 返回集合中最小的值

实战

批量
- delimiter $create PROCEDURE update_ci_policy (in step int, in count int) BEGIN DECLARE startCount int DEFAULT 0; REPEAT set autocommit = 0; update ci_policy a INNER JOIN (SELECT * from ci_policy_temp b ORDER BY b.id limit startCount OFFSET step) d on a.id = d.id set a.operator = d.operator; COMMIT; set startCount = startCount + step; UNTIL startCount > count END REPEAT; COMMIT; end$
  delimiter ;