MYSQL必知必会_mysql 必知必会

作者：知新_RL | 2024-03-24 00:50:39

踩

mysql 必知必会

基础

0.1 关联 inner/left/right/full join
- inner join 典型的连接运算，使用像=或<> 之类的比较运算符
- left outer join 左外连接，以左表为主表，右表为辅表（没有的显示null）
- right outer join 右外连接，以右表为主表（显示全部），左表为辅表（没有的显示null）
- full join 全外连接，将所有数据都展示出来
- union/union all union 不允许有重复值，union all 可以有
0.2 数据库的三大范式
- 第一范式，确保数据表字段的原子性
- 第二范式，表必须有一个主键；非主键列，必须完全依赖于主键，而不能部分依赖主键
- 第三范式，非主键列，必须直接依赖于主键，不能传递依赖

索引

1.1 什么是索引
- 索引是存储引擎用于提高查询数据库表访问速度的一种数据结构
1.2 索引的优缺点？
- 优点：提高查询数据库表的速度；加快表与表的连接
- 缺点：占用物理空间，不适用频繁增删改的场景，会降低表的增删改的效率，因为需要进行动态维护索引，导致时间变长
1.3 索引的作用？
- 提高查询速度
- 数据存储在磁盘上，查询数据是，如果没有索引，会加载所有数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树一般在2～4层，最多只用读取2～4层的磁盘，查询速度会大大提什
1.4 索引的数据结构
- B+ 树，默认的索引类型为B+树索引
  - B+树是基于B树和叶子结点顺序访问指针进行实现，它具有B树的平衡性，也能通过顺序访问指针提高区间查询性能
  - 在B+树中，结点中的key从左到右递增排列，如果某个指针的左右相邻key分别是keyi和keyi+1，则该指针指向结点的所有key大于等于keyi且小于等于keyi+1
  - Mysql 数据库使用最多为B树索引，底层用的是B+树数据结构来实现
- 哈希表
  - 哈希索引是基于哈希表实现的
  - 对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码
  - 对于不同的索引列，会计算出不同哈希码的值作为哈希表的key值
  - 将指向数据行的指针作为哈希表的value值，这样查找一个数据的时间复杂度就是O（1），一般多用于精确查找
- 两者区别
  - 哈希索引不支持排序，因为哈希表是无序的
  - 哈希表不支持范围查找
  - 哈希索引不支持模糊查找及多列索引的最左前缀匹配
  - 因为哈希表会存在哈希冲突，所以哈希索引的性能不稳定，而B+树是性能相对稳定的，每次都是从根节点查询到叶子结点
- 为什么B+树比B树更适合数据库索引
  - B+树的数据存储在叶子结点中，叶子结点均为索引，方便扫库，只需扫一遍叶子结点
  - 但B树因为其分支结点同样存储数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫
  - 所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁，所以通常B+树适合做数据库索引
  - B+树的结点只存储索引key值，具体信息的地址存在叶子结点的地址中
  - 这就使以页为单位的索引中可以存放更多的结点，减少更多的I/O支出
  - B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
1.5 索引的分类
- 主键索引主键名为primary的唯一非空索引，不能为空值
- 唯一索引唯一索引的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入
- 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引
- 组合索引：在表中多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则
1.6 索引的设计原则
- 尽量使用短索引，目的是小的索引设计到的磁盘I/O较少，查询速度更快
- 索引不是越多越好，因为占用额外的物理空间，维护也需要花费时间
- 利用最左前缀原则
1.7 索引的失效原则
- 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
- 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
- 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效，例：where col=a
- 判断索引列是否不等于某个值时，例：where col!=123
- 对索引列进行运算，查询条件使用or连接，也会导致索引失效，例：where col_a=123 or col_b=456
1.8 哪些场景能建立索引
- 经常用于查询的字段
- 经常用于连接的字段建立索引，可以加快连接的速度
- 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度
1.9 哪些场景不能建立索引
- where 条件中用不到的字段不适合建立索引
- 表记录较少
- 需要经常增删改
- 参与列计算的列不适合建索引
- 区分度不高的字段不适合建立索引，如性别
1.10 什么是最左匹配原则？
- 如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。

当遇到范围查询(>、
对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。
对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

1.11 什么是聚集索引？
- innoDB 使用表的主键构造主键索引树，同时叶子结点中存放的即为整张表的记录数据。聚集索引叶子结点的存储是逻辑上连续的，使用双向链表连接，叶子结点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快
- 聚集索引的叶子结点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多
- 对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有指定主键或索引那么InnoDB内部会生成一个隐藏的逐渐作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增
1.12 什么是覆盖索引？
- select 的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询
- 不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以Mysql使用B+树索引做覆盖索引
- 对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index

1.13 什么是前缀索引？
- 有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题
- 前缀索引是指对文本或者字符串的前几个字符建立索引，这样的索引长度更短，查询速度更快
- 创建前缀索引的关键在于选择足够长的前缀，以保证较高的索引选择性。索引选择性越高查询效率也就越高，因为选择性高的索引可以让Mysql在查找过滤时过滤掉更多的数据行
- 建立前缀索引的语句：ALTER TABLE table_name ADD KEY(column_name(prefix_length));
1.14 什么是分库分表？
- 数据库的存储资源是有限的，负载能力也是有限的，数据的大量积累肯定会导致其处理数据的能力下降
- 数据量越多，那么多数据的增删改查的开销也会越来越大，故为了解决这两种情况，引出了分库分表
- 分库即单个数据库拆分成多个数据库，分表即单表拆成多表
- 好处/目的：减小数据库的负担缩短查询时间；常见的数据切分的方式为垂直切分和水平切分
1.15 什么是分区表？
- 分区表是一个独立的逻辑表，底层有多个物理字表组成
- 当查询条件的数据分布在某一个分区的时候，查询引擎查询引擎只会去某一个分区查询，而不遍历整个表。在管理上，如果需要删除一个分区的数据，只需要删除对应的分区即可。
- 分区表类型：范围分区/list分区/hash 分区
- 分区可能带来的问题：
  - 打开和锁住所有底层表的成本很高
  - 维护分区的成本可能很高
  - 所有分区必须使用相同的存储引擎

2.1 共享锁和排他锁是什么
- 共享锁就是只读不写
- 当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据，eg：SELECT ... LOCK IN SHARE MODE;
- 排他锁就是写锁
- 在查询语句后面增加for update，mysql会对查询结果中的每行都加排他锁 eg：SELECT ... FOR UPDATE;
2.2 乐观锁和悲观锁是什么
- 乐观锁，就是比较乐观的锁，总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下，在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。适用于多读的场景
- 悲观锁，总是假设最坏的情况，每次去拿数据时，都认为别人会修改，所以拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞知道他拿到锁。

事务

3.1 事务四大特性
- ACID 原子性，一致性，隔离性，持久性
3.2 事务隔离级别有哪些
- 问题
  - 脏读
  - 幻读
  - 不可重读
- 区别
  - 不可重读和脏读的区别：脏读是某一事物读取了另一个事物未提交的脏数据，而不可重复度则是读取了前一个事物提交的数据
  - 不可重读和幻读的区别：都是读取了另一条已经提交的食物，不同的是不可重读的重点是修改，幻读的重点是新增或删除
- 事务隔离就是为了解决声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：【wpsshop博客】