MySql基础

数据库存储引擎

是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

-- 查看mysql引擎
SHOW ENGINES;

 

• 常用的存储引擎

  1.MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁 ​ 不支持事务 ​ 不支持外键 ​ 不支持崩溃后的安全恢复 ​ 在表有读取查询的同时，支持往表中插入新纪录 ​ 支持BLOB和TEXT的前500个字符索引，支持全文索引 ​ 支持延迟更新索引，极大提升写入性能 ​ 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

2.InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

支持行锁，采用MVCC来支持高并发 ​， 支持事务 ，​ 支持外键 ​ ，支持崩溃后的安全恢复 ，不支持全文索引

mvcc全称是multi version concurrent control（多版本并发控制）。mysql把每个=操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

 

使用何种数据类型

MySQL支持的数据类型非常多， 选择正确的数据类型对于获得高性能至关重要。

更小的通常更好 更小的数据类型通常更快， 因为它们占用更少的磁盘、 内存和CPU缓存， 并且处理时需要的CPU周期也更少。

简单就好 简单数据类型的操作通常需要更少的CPU周期。 例如， 整型比字符操作代价更低， 因为字符集和校对规则（排序规则 ）使字符比较比整型比较更复杂。

尽量避免NULL

如果查询中包含可为NULL 的列， 对MySQL来说更难优化， 因为可为NULL 的列使得索引、 索引统计和值比较都更复杂。

数据类型尽量用数字型，数字型的比较比字符型的快很多

 

 数据库约束

 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无 法插入到表中。约束在创建表的时候添加比较合适。

 约束种类

• NOT NULL 非空约束，规定某个字段不能为空

• UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

• PRIMARY KEY 主键(非空且唯一)约束

• FOREIGN KEY 外键约束

什么是级联操作：

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联操作语法	描述
ON UPDATE CASCADE 级联更新	只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新
ON DELETE CASCADE	级联删除

• CHECK 检查约束

• DEFAULT 默认值约束

 

 杂

 1.如果一个字段设置了非空与唯一约束，该字段与主键的区别?

当一个字段设置了非空（NOT NULL）与唯一性（UNIQUE）约束时，它将确保该字段的值在表中是唯一的，并且不允许为空（NULL）。这听起来非常类似于主键（PRIMARY KEY）的作用，但实际上两者之间还是有区别的：

1. 主键的唯一性和非空性：
   - 主键也要求唯一性和非空性，但主键还额外承担了标识表中每一行的职责。主键是表中记录的唯一标识符，它可以帮助快速定位和检索数据。

2. 复合主键 vs 单独的唯一性约束：
   - 主键可以由一个或多个字段组成，形成复合主键。在这种情况下，只有当所有主键字段的组合值唯一时，才能保证行的唯一性。而单独的唯一性约束仅适用于单个字段或一组字段，但不是作为主键使用。

3. 自动索引：
   - 当你定义一个主键时，数据库通常会自动创建一个索引（通常是B树索引）来加速基于主键的查找。这使得主键上的查找非常快。虽然你也可以在唯一性约束上创建索引，但这不是自动发生的，需要显式创建。

4. 外键引用：
   - 主键还可以被其他表的外键（FOREIGN KEY）所引用，用于建立表之间的关系。虽然唯一性约束也可以被引用，但在实践中，主键通常用于此目的，因为它们明确地标识了行。

5. 更新限制：
   - 更新主键的值通常受到更多的限制，因为这可能会影响依赖于该主键的外键约束。更改唯一性约束字段的值在技术上可能更容易，但这也取决于具体的数据库管理系统和外键约束的规则。

6. 设计哲学：
   - 主键是数据库设计中一个核心的概念，用于确保数据的一致性和完整性。而唯一性约束和非空约束则是更灵活的工具，可以根据具体需求应用于表的任何字段。

2.外键和主键的区别 

外键（Foreign Key）和主键（Primary Key）是关系型数据库中用于维护数据完整性的两种重要概念。它们的主要区别如下：

1. 定义：
   - 主键：主键是一个表中的一个或一组列，用于唯一标识表中的每一条记录。一个表只能有一个主键，而且主键的值不允许为空（NULL）。
   - 外键：外键是一个表中的一个或一组列，其值必须与另一个表（父表）的主键或唯一性约束的值相匹配。外键用于在两个表之间建立联系，实现数据之间的参照完整性。

2. 唯一性与空值：
   - 主键的值必须是唯一的，且不能为NULL。
   - 外键的值可以重复，也可以为NULL，只要NULL值在定义外键时被允许。

3. 数量限制：
   - 一个表只能有一个主键。
   - 一个表可以有多个外键，分别指向其他表的不同主键或唯一性约束。

4. 数据完整性：
   - 主键主要用于保持表内数据的实体完整性，确保不会有重复的记录。
   - 外键用于维护引用完整性，确保在子表中引用的值在父表中确实存在。

5. 索引：
   - 主键自动创建一个唯一索引，以优化数据检索速度。
   - 外键通常也需要索引，但这个索引不一定自动创建，可能需要显式创建。

6. 关系建立：
   - 主键不涉及与其他表的关系，除非它同时被用作其他表的外键。
   - 外键明确地指定了与另一表的关系，通常与主键相结合。

7. 更改限制：
   - 主键的更改通常更加受限，因为这可能会影响到所有引用它的外键。
   - 外键的值可以更新或删除，只要更新后的值在父表的主键中存在。

8. 删除行为：
   - 删除含有主键的记录通常不需要考虑其他表，除非有外键约束。
   - 删除含有外键的记录时，可能需要考虑级联删除、级联设置为NULL或其他参照完整性规则。

 表和表之间的关系

有三种关系.一对一、一对多、多对多。

一对一 

 不常用，一对一可以创建成一张表。

一对一的建表原则	说明
外键唯一	主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键	主表的主键和从表的主键，形成主外键关系

一对多

 一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

 多对多

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。 

 

索引 

 在数据库中还有一种以某种方式指向数据的数据结构，可以在这些数据结构上实现高效查找。这些数据结构就是索引。

分类

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引：一个索引包括多个列

优点 

提高数据检索效率，降低磁盘IO成本

通过对数据的排序，降低排序成本  

缺点

 索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

什么情况下适合建立索引

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

什么情况下不适合建立索引

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

什么时候会出现索引失效

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：

1. 查询必须从索引的最左列开始： 当数据库执行一个查询时，如果要利用复合索引，那么WHERE子句中的条件必须从索引的最左侧列开始，并且不能跳过中间的任何列。例如，如果有一个复合索引 (col1, col2, col3)，那么查询条件可以是 col1 = ? 或者 col1 = ? AND col2 = ?，但不能是 col2 = ? 或 col3 = ?。

2. 范围查询的影响： 如果查询中包含了索引列的范围查询（比如 col1 > ? 或 col1 < ?），那么范围查询右侧的索引列将无法被利用。这是因为B+树的性质决定了数据库无法跳跃到索引的任意位置，它需要从最左端开始查找直到找到符合条件的范围。

3. 索引列的顺序： 复合索引中列的顺序非常重要，应该根据查询中最常出现的列和WHERE子句的使用频率来设计索引。最常用或最具选择性的列应该放在最左侧。

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

为什么建议InnoDB必须建主键

对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集

索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。为什么建议InnoDB

必须建主键呢？因为本身数据库的资源就非常宝贵，我们尽量能手动做的就不要麻烦MySQL

去帮我们维护，说白了就是降低数据库开销。

为什么推荐使用整型主键

我们就拿UUID举个例子，一大串十分长但无具体意义的字符串，

回顾上面InnoDB的索引图，是比较两个int型数据快捷呢还是比较

两个字符串快捷呢？想都不用想肯定是比较两个int型更具有优势，

字符串需要逐位的去比较，如果碰巧两个字符串只有最后一位不一

致那不是亏得要死。

为什么推荐使用自增主键

上文B+Tree第三条特性：叶子节点用指针连接，提高区间访问性能。

这样带来了一个好处那就是范围查找，比如一行SQL：select * from table

name where id between 1 and 20，MySQL只需要查到索引等于1的位置，

然后通过链表往后依次找到20的位置，首尾位置之间就是我们需要查找的结果集。

但这样也带来了一个问题，加入我们主键已经插入了1、2、3、4、6、7，这时候我

们插入了5，MySQL在维护索引的时候就会打破原有链表顺序，导致链表节点分裂重排，从而消耗性能。

为什么InnoDB非主键索引存储的是主键值

保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，

因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，

因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还

继续再保存一份数据，就会导致有多少索引就要存多少份数据。

 

存储过程

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

就是数据库 SQL 语言层面的代码封装与重用。

 

触发器

• 概念：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

• 作用：

触发器经常用于加强数据的完整性约束和业务规则等;

可在写入数据前，强制检验或者转换数据(保证安全性);

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

 

 事务

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。

数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。

事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。

功能	SQL 语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

 

 

事务特性	含义
原子性（Atomicity）	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。
一致性（Consistency）	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是 2000，转账后 2 个人总金额也是 2000
隔离性（Isolation）	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这 是事务 update 时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是 insert 或 delete 时引发的问题

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别越高，性能越差，安全性越高