MySQL知识学习汇总_mysql学习-CSDN博客

本文链接：https://blog.csdn.net/RossiLover/article/details/141920303

一、存储引擎

1. InnoDB引擎

InnoDB是一种兼顾高可靠性高和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

特性：

1.支持外键。
2.支持事务，遵循事务的ACID特性。
3.行锁：MyISAM是对行加锁，锁粒度相较MyISAM引擎的表锁较细。操作时只锁定某一行，不影响其他行数据，适合高并发的操作。并且通过MVCC可以有效减少加锁操作，支撑高并发。
4.既缓存索引、又缓存真实数据。（MyISAM只缓存索引，不缓存真实数据）
5.高性能特性：

            插入缓冲区（insert buffer）：提高插入效率
            两次写（double write）：保证InnoDB引擎数据页的可靠性。
            自适应哈希索引（adaptive hash index）：提高查询效率

6.支持索引：

            B树索引、聚簇索引
            全文检索索引（MySQL 5.6 及更高版本提供对 FULLTEXT 索引的支持。）
            地理空间索引（MySQL 5.7 及更高版本提供对地理空间索引的支持。）等

7.日志支持：

undo log （回滚日志）：主要用于事务回滚和MVCC
edo log（重做日志）：主要用于掉电等故障恢复

2. MyISAM引擎

在MySQL5.1及以前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括：全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务、行级锁，并且有一个毫无疑问的缺陷就是奔溃后无法安全恢复。

应用场景：

1、只读、或者读多写少的业务；
2、日志型应用：因为延迟更新索引键的特性，MyISAM表插入速度很快。并且可以减少磁盘空间占用、磁盘IO，从而提升查询性能。

特性：

1.不支持外键。
2.不支持事务。
3.表锁：MyISAM是对整张表加锁，锁的粒度比较粗，及时操作一条记录也会锁住整张表，因此并发写入的性能较低。
4.统计特性：针对数据的统计有额外的常数来存储。所以count（*）的查询效率很高，时间复杂度是O(1)。
5.索引特性：对于MyISAM表，即使是BOLB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引，是一种基于分词创建的索引，可以支持复杂的查询。
6.延迟更新索引键（Delayed Key Write）：创建MyISAM表时，如果指定了DELAY_KEY_WRITE选项，每次执行完只会写到内存中的键缓存区，不会立即将修改的索引数据写入磁盘。只有在清理键缓冲区或者关闭表的时候，才会将对应的索引块写入到磁盘。这种方式可以提高写入性能，但是在数据库或者主机崩溃时，会造成索引损坏，需要执行修复操作。
7.压缩特性：可以使用myisampack对表进行压缩/打包。压缩表可以极大的减少磁盘空间占用、磁盘IO，从而提升查询性能。

3. Memory引擎

Memory引擎的表数据时存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
使用MEMORY引擎主要是因为速度，好处就在MEMORY采用的逻辑存储是系统内存,极大的提高了储存数据表的性能；

特点：内存存放、hash索引

应用场景：

1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
2.如果数据是临时的，而且必须立即使用，那么就可以存放在内存表中。
3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中

二、连接池

数据库连接池是程序启动时建立足够数量的数据库连接，并将这些连接统一管理起来组成一个连接池，程序动态的从池中取连接与归还连接。

优势：

1、资源复用：由于数据库的连接得到的复用，避免频繁的创建和销毁连接的性能开销。在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）。
2、更快的系统响应速度：数据库连接池在初始化后，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了从数据库连接初始化和释放过程的开销，从而缩减了系统整体响应时间。
3、统一的连接管理，避免数据库连接泄露：在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄露。

常用连接池

1、DBCP

是tomcat自带的，相对于C3P0来说速率较快，但是不稳定

2、C3P0

速率比较慢，但是非常稳定

3、Druid(德鲁伊)

是阿里提供，最常用的，它结合了DBCP和C3P0各自的优点

三、集群

1. 主从

释义：

主负责写操作，从负责读操作，从库的数据从主库同步复制，这样的集群模式就主从同步。数据库的性能瓶颈往往都在80%的读操作上，我们可以搞多个从需要减轻读的压力。
实现：主从同步（mysql自己实现），读写分离（依赖三方组件,mysqlproxy,mycat(单独服务)，shardingjdbc(jar)）

主从复制步骤：

1.将Master的binary-log日志文件打开，mysql会把所有的DDL,DML,TCL写入BinaryLog日志文件中
2.Master会生成一个 log dump 线程，用来给从库的 i/o线程传binlog
3.从库的i/o线程去请求主库的binlog，并将得到的binlog日志写到中继日志（relaylog）中
4.从库的sql线程，会读取relaylog文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致

2. 分库分表

1.垂直分库分表-设计上的

1.1.垂直分表-宽表拆分
1.2.垂直分库-按业务分库

2.水平分库分表-海量表拆分小表

2.1.水平分表

按区间分
按时间分
Hash分表
雪花算法

雪花算法是一种全局ID生成算法，其核心思想是将64位的long型ID分为四个部分，分别为：时间戳、工作机器ID、数据中心ID和序列号。通过将数据映射到具有特定结构的分布式系统中，实现数据的存储和查询。该算法由一系列节点组成，每个节点负责存储数据的一部分。这些节点通过哈希函数将数据映射到特定的位置，形成类似于雪花结构的分布式系统。通过这种方式，雪花算法能够在分布式系统中保证ID的唯一性和有序性。

2.2.水平分库

3. 问题：

1.一个操作设计到多个库的话需要有分布式事务的支持
2.查询操作：一个查询涉及多个库、排序操作、分页操作

4. 数据库优化顺序：

1)垂直分库，垂直分表，冗余设计(反第三范式),存储引擎,索引，SQL优化，缓存(热点查询)，全文检索(搜索场景)，页面静态化
分布式项目可以直接考虑垂直分库
2)主从(读压力大)-集群-高并发
================
3)水平分表
4)水平分库

四、索引

1. 概念

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

2. 类型

1.数据结构维度

B+树索引（所有数据存储在叶子结点，适合范围查询）
哈希索引（适合等值查询，检索效率高）
全文索引（三种模式）

1. 自然语言模式：根据每个单词在整个集合中出现的频率以及它们在给定文档中出现的频率来计算相关性。不允许使用运算符。

2. 布尔模式：可以使用布尔运算符，如 +（必须存在）、 -（不能存在）、 < > ~ * " "（引号内的词汇必须作为一个词汇匹配）等，以控制搜索的行为

3. 查询扩展模式：首先执行自然语言模式搜索，然后再搜索与初步搜索结果最相关的行，并基于这些行生成查询，然后执行第二次搜索并返回结果。

R-树索引（处理多维数据）

2.物理储存维度

聚簇索引：为每张表的主键构造一棵B+树，该树的叶子节点存放的是整张表的行记录数据，就是好像把数据和索引聚集在了一棵B+树上，所以这种数据组织形式的索引叫聚簇索引。聚簇索引：主键值+完整记录
非聚簇索引：也被称为二级索引或辅助索引。索引的逻辑顺序与磁盘上行的物理存储顺序不同。在非聚簇索引中，每一个索引条目都包含了键值和一个指向该键值对应的数据行的指针。这个指针通常是数据行的物理地址或者是一个指向数据行的其他种类的标识符。一个表可以有多个非聚簇索引。
回表：指当使用非聚集索引（Secondary Index）查询数据时，MySQL 首先使用索引查找到满足条件的主键值，然后通过主键值到聚集索引（Primary Index）中查找对应的行记录的过程。因为非聚集索引中只包含了部分的列数据，而聚集索引中包含了所有的列数据，所以需要通过回表来获取未包含在非聚集索引中的列数据。

聚簇索引和非聚簇索引对比

3.逻辑维度

主键索引（不允许有空值）
普通索引
联合索引 ：两个或更多个列上的索引：最左原则：例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找
唯一索引
空间索引：处理空间数据：涉及空间关系（如包含、相交、相邻等）的查询

3. 创建原则

原则

一、搜索的索引列
二、使用唯一索引
三、使用短索引
四、最左前缀原则
五、不要过度使用
六、尽量使用主键索引

具体做法：

●表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
●记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。
●经常与其他表进行连接的表，在连接字段上应该建立索引。
●唯一性太差的字段不适合建立索引。
●更新太频繁地字段不适合创建索引。
●经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
●在经常进行 GROUP BY、ORDER BY 的字段上建立索引；
●索引应该建在选择性高的字段上。
●索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

4. 优化

1. 分析查询语句：EXPLAIN

1. 复杂查询
对于包含多个表连接、子查询或联合操作的复杂查询，EXPLAIN 可以帮助你理解查询优化器是如何处理这些组件的。你可以查看每个表的连接类型、索引使用情况等，从而确定是否有可能进一步优化查询。

2. 性能瓶颈
如果你发现某个查询特别慢，使用 EXPLAIN 可以帮助识别问题所在。例如，如果 EXPLAIN 显示查询正在执行全表扫描（type: ALL），那么添加适当的索引可能会显著提高查询速度。

3. 索引评估
当你考虑添加新索引或调整现有索引时，EXPLAIN 是一个很好的工具来评估这些变化的效果。通过比较添加索引前后的 EXPLAIN 输出，你可以看到索引是否被利用以及查询性能的改进程度。

2. 避免索引失效情况

1、要满足最佳左前缀法则
2、计算、函数导致索引失效
3、类型转换导致索引失效
4、范围条件右边的列索引失效
5、没覆盖索引时，“不等于”导致索引失效
6、没覆盖索引时，is not null、not like导致索引失效
7、没覆盖索引时，左模糊查询导致索引失效
8、“OR”前后存在非索引列，导致索引失效
9、不同字符集导致索引失败，建议utf8mb4

五、SQL

数据查询语言-DQL：Data Query Language-如 SELECT
数据操作语言-DML：Data Manipulation Language-INSERT，DELETE，UPDATE
事务控制语言-TCP：Transaction Control Language-COMMIT（提交）命令，SAVEPOINT（保存点）命令，ROLLBACK（回滚）命令）
数据控制语言-DCL：Data Control Language-GRANT，REVOKE
数据定义语言-DDL：Data Definition Language-CREATE，ALTER，DROP
指针控制语言-CCL：Pointer Control Language-DECLARE CURSOR，FETCH INTO，UPDATE CURRENT

select

1. 执行顺序：

1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit

2. 条件运算符：

比较：=, >, <, >=, <=, !=, <>, !>, !<
确定范围：between and, not between and
确定集合：in, not in
字符匹配：like, not like
空值：is null, is not null
多重条件（逻辑运算）：and, or, not

3. 单行处理函数：

lower(字段)-转换小写
upper(字段)-转换大写
substr(字段,起始下标,截取长度)-取子串
concat(字段1,字段2)-字段拼接
length(字段)-字段长度
trim(字符串/字段)-去除字符串/字段前后空格
round(字段/字面值,保留位数)-四舍五入
rand()-生成0到1之间的随机数
ifnull(字段数据,被指定的值)-将字段中的NULL数据转为被指定的值

4. 分组：

GROUP BY/HAVING
HAVING可用于分组后的进一步筛选，但必须跟在 group by 后！
DISTINCT去除重复记录使用

5. 高级查询

联表查询:
- 取多张表的笛卡尔积：select *from 表名1，表名2;
- inner join 返回两个表中都有的数据
- left join 会从左表中返回所有的值，即使右表中没有匹配
- right join 会从右表中返回所有的值，即使左表中没有匹配
子查询
- 1.比较子查询
- 2.IN子查询
- 3.批量子查询ANY、ALL
- 4.EXISTS子查询