MySQL基础理论

先整理大纲
补充初略细节
补充完整细节
标注重点内容
补充图片、趣味内容
AI整理

历史

MySQL的发展史

名词

一些名词的解释

范式

三大范式

基础表

部署好MySQL后系统自带的几张表

索引(hash/B+树)

作用/优缺点/索引类型/索引结构/索引查询算法/如何高效创建索引/联合索引/前缀索引/聚簇索引/回表

Key，索引是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大是，索引对性能的影响愈发重要， 索引优化是对查询性能优化的最佳手段。 在数据量愈来愈大时，不合理的索引会使性能急剧下降。

在MySQL中，索引实在存储引擎层而不是服务器层实现的，所以不同的存储引擎的索引工作方式不同。

作用

提高数据检索性能
提高数据排序性能

主键索引

概念

Primary Key，是一张表中每行数据的唯一标识，用于确保数据的唯一性和完整性。 如果没有建立主键索引，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

每张表只有一个主键索引。
主键索引不允许为Null。
主键索引不允许重复。

命令

创建user表，定义id为主键索引

CREATE TABLE user (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    Primary Key (id)
)

普通索引

概念

基本的索引，可以重复，也可以为Null

唯一索引

概念

数据列不可以重复。
允许为Null。
一张表可以给多个字段创建唯一索引。

命令

创建唯一索引

1	ALTER TABLE table_name ADD UNIQUE (column);

创建唯一组合索引

1	ALTER TABLE table_name ADD UNIQUE (column1, column2);

全文索引

概念

全文索引（Full-Text Index）是一种支持全文检索的索引类型，允许你在大块的文本中快速寻找关键词，从而提高查询效率。

百度百科中：原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置

联合索引（组合索引）

概念

对多个字段组合建立索引称为联合索引

联合索引在B+树上排序是按照创建时字段顺讯排列的。

当给 a,b,c建立联合索引时

WHERE a=1 AND b=2 AND c=3 索引全部匹配上

WHERE a=1 AND b<2 AND c=3 a,b使用了联合索引，c可能使用了联合索引，如果通过a,b扫描后得到的值非常多，则c不会使用索引。

简单理解就是对某一个联合索引中某一个字段的扫描是基于它有序的情况下，a为第一个字段，在创建是就是有序，b在a相同的情况下进行排序，所以b可以使用扫描，但是在多个b下面的c则是无序的，则无法进行扫描。

空间索引

概念

空间索引（Spatial Index）是一种用于提高查询效率的索引类型，专门针对空间数据类型（如点、线、多边形等）的查询优化。空间索引的主要作用是加速包含空间函数或运算符的查询，例如计算距离、判断位置关系（如包含、交叉、相交等）等。

聚簇索引

概念

聚簇索引并不是一种单独的索引类型，而是一种 数据存储方式

通过非主键的索引查询到主键，再需要再通过主键查询到聚簇索引的数据称为回表

索引覆盖

当查询的条件都是使用索引，需要查询的字段也都建立了索引，这时存储引擎会直接从索引中获取需要的数据，不会去访问原始数据，这就叫索引覆盖。

所以在写SQL查询时，应尽量写指定要查的字段，而不是星号。

Binlog

使用场景/开启方式/记录方式

数据类型

各种类型介绍

存储引擎

MyISAM/Innnodb

事物

处理过程

视图

介绍/使用场景

概念

以数据库表为基础创建的虚拟表

如果创建的视图使用了group by , distinc等方法则，视图数据不可修改，否则可以修改，修改会影响基础表数据。

存储过程

介绍/使用场景/处理过程

概念

一组预设在数据库中的SQL，程序直接调用存储过程名称即可调用。

触发器

介绍/使用场景/处理过程

概念

数据库数据发生特定动作是所触发的动作。

有六种，分别是update/insert/delete前后。

SQL

优化/分析Explain/执行过程

关联查询

内连接（inner join）：满足条件并且两张表相互匹配的数据

左连接（left join）：满足条件的左边数据为主，没有对应右表数据时为空

右连接（right join）：满足条件的右表数据为主，没有对应左表数据时为空

全连接（full join）：满足数据的左右表相互关联，没有这为空

笛卡尔（）: 左表的每条数据与右表的每条数据关联

联合查询（union）：将多条SQL的结果合成一张表，多条SQL的字段对应的类型要相同，会自动去重，union all则不会去重。

优化

问题/解决方案

Explain

type:

all:全表扫描

const: 根据主键或者唯一索引查询，索引值是常量值时。explain select * from emp where empno=7369;

eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。

ref：使用了非唯一的索引进行查询。

range：使用了索引，扫描了索引树的一部分。

index：表示用了索引，但是也需要遍历整个索引树。

possible_keys：该查询可能会用到的索引

key：实际用到的索引

key_len：查询过程中所用到的索引列占的总字节数

rows：查询扫描的预估计行数

Extra：与查询相关的额外信息和说明

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。

Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。

Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。

Using index condition：表示查询优化器选择使用了索引条件下推这个特性。

Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

运维

部署/高可用/分布式