MySQL知识学习汇总

标签：log 知识 汇总 查询 索引 MyISAM MySQL 日志 数据

一、存储引擎

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子查询

常见函数

• 字符串函数：CONCAT，SUBSTRING，LENGTH，UPPER，LOWER，TRIM，REPLACE …
• 数值函数：ABS，ROUNO，CEIL，FLOOR，MOD，RAND …
• 日期和时间函数：NOW，CURDATE，CURTIME，TIME，YEAR，MONTH，DAY …
• 条件函数：IF，CASE（一般存储过程等写，不建议 MySQL 中使用存储过程，业务逻辑尽量再业务代码中实现） …
• 聚合函数：COUNT，SUM，AVG，MIN，MAX（一般查询中用） …
• 分组函数：GROUP，CONCAT，GROUP BY …
• 转换函数：CAST，CONVENRT …
• 数据类型函数：CAST，CONVERT，DATE_FORMAT …
• 数据库函数：DATABASE，USER，VERSION …
• 系统函数：SLEEP

六、日志

MySQL InnoDB 引擎重要的三个日志

1. binlog

        释义：

        binlog记录DDL 和 DML语句，但不包括SELECT、SHOW 等语句，简单说只要发上了表结构变化或表数据更新，都会产生binlog日志。
写入过程：事务执行过程中，先把日志写到binlog cache。事务提交的时候，再把binlog cache写到binlog文件中。

        作用：

• 1.灾难时的数据恢复；
• 2.MySQL 的主从复制。

2. redo log

        释义：

redo log是"物理日志", 记录的是具体数据页上做了什么修改，记录修改后的值，用来实现事务的持久性，适用于崩溃恢复。它不是随着事务的提交才写入的，而是在事务的执行过程 中，便开始写入 redo 中。两阶段提交。

        redo log由两部分组成，重做日志缓冲redo log buffer（易失的），和重做日记缓冲文件（redo log file），是持久性的。

        reldolog日志的刷盘

                    
3. undo log

        释义：

        undo log，回滚日志，事务执行时，用于记录数据被修改前的信息。作用：在异常发生时，会对已经执行的操作进行回滚。
        undo log是逻辑日志，可以认为：

• 1、当delete一条数据时，它会插入一条对应的insert记录；
• 2、当update一条记录时，它会插入一条对象相反的记录。
• 3、新增一条记录，记录下主键，回滚时直接DELETE这个主键的内容;

4. redo log 和 bin log区别

• 1.redo log是innodb存储引擎独有的，binlog是不区分存储引擎
• 2.记录内容不同, redo log是物理日志,记录事务具体操作的内容; bin log是逻辑日志,记录事务操作过程
• 3.写入时间不同，先写入redo log,再写入binlog
• 4.redo log是循环使用文件，bin log每次新增一个文件

查询日志

            记录了客户端所有的操作语句，默认关闭

慢SQL

            该日志记录了所有执行时间超过参数long_query_time，且所记录数不小于min_examined_row_limit的所有 SQL 语句。默认关闭，开启需配置。

错误日志

            记录了当mysqld.log启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息，当数据库出现故障无法使用时，建议先看此日志。默认打开。
作用：故障诊断、系统检控、恢复参考

七、同步缓存

        SpringBoot 集成 Canal 使用MySQL BinLog同步数据

八、三范式

•     第一范式：数据属性不可再分。一个列不能再拆分为两个列。
•     第二范式：数据表不可再分。满足第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
•     第三范式：满足第二范式；且不存在传递依赖。非主属性必须直接依赖于主属性，而不是间接依赖于主属性。

标签：log,知识,汇总,查询,索引,MyISAM,MySQL,日志,数据
From： https://blog.csdn.net/RossiLover/article/details/141920303