随手集☞MySQL部分知识盘点（loading。。。）

在这里插入图片描述

字段类型

  • 数值类型
    1. INT: 整数类型，可以是正数或负数。根据显示宽度和是否有符号，其范围会有所不同。
    2. TINYINT: 非常小的整数。
    3. SMALLINT: 小的整数。
    4. MEDIUMINT: 中等大小的整数。
    5. BIGINT: 大整数。
    6. FLOAT: 单精度浮点数。
    7. DOUBLE: 双精度浮点数。
    8. DECIMAL(M,N) 或 NUMERIC(M,N): 精确的小数值，其中 M 是数字的最大数目（精度），N 是小数点后的位数（标度）。
  • 日期和时间类型
    1. DATE: 日期，格式为 ‘YYYY-MM-DD’。
    2. TIME: 时间，格式为 ‘HH:MM:SS’。
    3. DATETIME: 日期和时间组合，格式为 ‘YYYY-MM-DD HH:MM:SS’。
    4. TIMESTAMP: 时间戳，格式为 ‘YYYY-MM-DD HH:MM:SS’，且自动包含时区信息。
    5. YEAR: 年份，可以是两位或四位数字。
  • 字符串类型
    1. CHAR(M): 定长字符串，长度为 M。
    2. VARCHAR(M): 变长字符串，最大长度为 M。
    3. TINYTEXT: 非常小的文本字符串。
    4. TEXT: 文本字符串。
    5. MEDIUMTEXT: 中等大小的文本字符串。
    6. LONGTEXT: 长的文本字符串。
    7. BINARY(M): 定长二进制字符串。
    8. VARBINARY(M): 变长二进制字符串。
    9. TINYBLOB: 非常小的 BLOB（二进制大对象）。
    10. BLOB: BLOB（二进制大对象）。
    11. MEDIUMBLOB: 中等大小的 BLOB。
    12. LONGBLOB: 大的 BLOB。
  • 空间类型
    1. GEOMETRY: 用于存储地理数据的列类型。
    2. POINT: 点。
    3. LINESTRING: 线段。
    4. POLYGON: 多边形。
  • 枚举和集合类型
    1. ENUM(‘value1’,‘value2’,…): 枚举类型，只能包含预定义的值列表中的一个值。
    2. SET(‘value1’,‘value2’,…): 集合类型，可以包含预定义的值列表中的一个或多个值。
  • 位字段类型
    1. BIT(M): 位字段类型，用于存储位值。
    2. JSON 类型
    3. JSON: 用于存储 JSON 格式的数据。

存储引擎

  • 每种存储引擎都使用不同的存储机制、索引技巧、锁定水平，并最终提供不同的功能和能力。以下是一些常见的MySQL存储引擎及其特点：

    • InnoDB：

      • 支持事务处理，具有提交、回滚和崩溃恢复能力，提供ACID兼容的事务安全存储引擎。
      • 行级锁定，并在SELECT语句中提供非锁定读。
      • 适用于处理大量数据的场景，具有较高的CPU效率。
      • 与MySQL服务器完全整合，维持自己的缓冲池用于缓存数据和索引。

    • MyISAM：

      • 不支持事务处理。
      • 具有较快的查询速度，适合用于读密集型的应用。
      • 每个表被存放在分离的文件中。

    • MEMORY：

      • 将表存储在内存中，查询速度很快。
      • 但数据不是持久化的，适合用于临时性的数据存储。

    • NDB Cluster：

      • 适用于高可用性和高性能的集群环境。
      • 支持分布式存储。

    • BLACKHOLE：

      • 接收写入但不存储数据，适合用于数据的复制和同步。

    • CSV：

      • 将表中的数据保存在CSV文件中。
      • 适合用于数据的交换和导入导出。

    • ARCHIVE：

      • 适用于只需要偶尔访问数据的应用场景，如归档数据和历史数据。
      • 具有高效的储存空间利用率和快速的数据压缩能力。

    • FEDERATED：

      • 允许在多个MySQL服务器之间共享数据，适用于分布式应用场景。

每种存储引擎都有其特定的应用场景和优势。在选择存储引擎时，需要考虑数据的性质、访问模式、可靠性需求以及系统的整体性能。例如，如果需要支持事务处理和崩溃恢复，那么InnoDB可能是一个好的选择；而如果只是需要快速读取数据，并且不需要持久化存储，那么MEMORY存储引擎可能更适合。

存储引擎特性

特点InnoDBMyISAMMEMORYMERGENDB
存储限制64TB有有没有有
事务安全支持
锁机制行锁(适合高并发)表锁表锁表锁行锁
B树索引支持支持支持支持支持
哈希索引支持
全文索引支持(5.6版本之后)支持
集群索引支持
数据索引支持支持支持
索引缓存支持支持支持支持支持
数据可压缩支持
空间使用高低N/A低低
内存使用高低中等低高
批量插入速度低高高高高
支持外键支持

事务的特性

  • 原子性（Atomicity）：原子性是指事务是一个不可分割的操作单元，要么全部执行成功，要么全部失败回滚。如果事务中的任何一个操作失败，整个事务都会被回滚到事务开始前的状态，保持数据的一致性。
  • 一致性（Consistency）：一致性是指事务执行前后，数据库的状态必须保持一致。如果事务执行过程中发生了错误，数据库会回滚到事务开始前的状态，确保数据的一致性。
  • 隔离性（Isolation）：隔离性是指多个事务并发执行时，每个事务的操作都不会被其他事务所干扰。每个事务都应该感觉不到其他事务的存在，即使多个事务同时对同一数据进行操作，也不能相互影响。
  • 持久性（Durability）：持久性是指一旦事务提交成功，对数据库的修改就是永久性的，即使系统发生故障或重启，修改的数据也不会丢失。持久性通过将事务的操作记录在数据库的日志中，并定期将日志写入磁盘来实现。

MySQL的事务隔离机制

  • READ UNCOMMITTED（读未提交）

    • 允许读取尚未提交的数据变更。这可能导致脏读、不可重复读和幻读。
    • 性能最好，但数据一致性最差。

  • READ COMMITTED（读已提交）

    • 只允许读取已经提交的数据。这可以防止脏读，但仍然可能出现不可重复读和幻读。
    • 这是大多数数据库系统的默认隔离级别（但不是MySQL的默认设置）。

  • REPEATABLE READ（可重复读）

    • 这是MySQL的默认隔离级别。它确保了在同一事务中多次读取同一数据的结果是一致的，但可能出现幻读。
    • InnoDB存储引擎通过多版本并发控制（MVCC）来实现这一隔离级别。

  • SERIALIZABLE（串行化）

    • 最严格的隔离级别，它通过强制事务串行执行，从而避免了脏读、不可重复读和幻读。
    • 但这也会大大降低并发性能。
  • 脏读：读取到未提交的数据变更。
  • 不可重复读：在同一事务中，多次读取同一数据返回的结果有所不同。
  • 幻读：当事务不是独立执行时发生的一种现象，例如一个事务读取了几行数据，另一个并发事务插入了一些新行，然后第一个事务再次读取同样的范围时，看到了这些新的“幻影”行。

Mysql复制

复制概述

  • 复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行，进行数据同步。

  • MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

复制原理

MySQL 的主从复制原理如下。
在这里插入图片描述

  • Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。

  • 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。

  • slave重做中继日志中的事件，将改变反映它自己的数据。

    • 优势

      • 主库出现问题，可以快速切换到从库提供服务。

      • 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。

      • 可以在从库中执行备份，以避免备份期间影响主库的服务。

索引

  • 定义

    • 帮助SQlL快速获取数据的数据结构
    • 数据库系统还维护着满足特定查找算法的数据结构，该结构以某种方式指向数据，实现高级的查找算法，这种数据结构就是索引

  • 优势

    • 类似于数据的目录检索工具，提高数据的检索效率减低数据的IO成本
    • 通过索引对数据进行排序，减低数据排序的成本，降低CPU的消耗

  • 劣势

    • 索引也是一张表，需要进行数据的存储，存储了主键和索引字段，也是需要占用空间的

    • 每次更新数据都需要重新更新索引的记录，所以更新的速度也受到影响，

  • 常见的类型

    • B-Tree索引：
      • 这是MySQL中最常用的索引类型，也是默认的索引类型。
      • 它使用B树数据结构来存储索引数据，每个节点包含多个键值对和指向子节点的指针。
      • B-Tree索引适用于范围查询，可以高效地支持等值查询、范围查询和排序。
      • 适用于具有多个列的主键或唯一索引。
      • 索引的键值按顺序排列，通过比较键值来找到数据行。
      • 支持多列索引，可以同时对多个列进行排序。
      • 适用于大多数类型的查询，特别是范围查询和精确匹配查询。
    • B+Tree索引：
      • B+树索引是一种基于B树的变体。
      • 它的非叶子节点不存储数据，只存储索引，而所有数据都存储在叶子节点中。
      • 这种结构使得B+树索引的查询速度更快，因为在进行范围查询时，只需要遍历叶子节点即可。
      • B+树索引的深度较浅，因此查询速度快，同时也减少了磁盘I/O操作的次数，提高了性能。
    • 哈希索引：
      • 哈希索引也称为散列索引或HASH索引。
      • 它通过哈希函数将键值映射到一个哈希表中，每个哈希表中包含键值对。
      • 哈希索引主要用于等值查询，特别是单列的精确匹配查询。
      • 适用于列的数据类型是字符串、整数或布尔值。
      • 查询速度非常快，因为可以通过哈希函数直接定位到数据。
    • 全文索引：
      • 全文索引用于在包含文本数据的列中进行搜索，如文章内容、博客正文等。
      • 它允许在文本中查找关键字，而不是完全匹配。
      • MySQL提供了Full-Text功能，可以高效地进行全文索引。
      • 适用于需要在文本数据中进行模糊匹配的查询。
      • 支持布尔运算表达式，可以组合多个关键字进行查询。
      • 可以根据文本的相关性进行排序。
      • 不支持复合索引，只能对整列进行全文索引。
    • 空间索引：
      • 空间索引用于存储和查询包含空间数据的列，如地理位置、二维坐标等。
      • 适用于需要对空间数据进行高效查询的应用。
        普通索引：
      • 允许在定义索引的列中插入重复值和空值。
    • 唯一索引：
      • 避免数据出现重复。
    • 主键索引：
      • 为主键字段创建的索引。
    • 单列索引：
      • 索引只包含原表的一个列。

索引分类

  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引： 索引列的值必须唯一，但允许有空值
  • 复合索引 ：多个索引包含多个列

索引操作

  • 创建索引
create union fulltext spatial index  index_name  on  table_name
  • 查看索引
show index from table_name
  • 删除索引
drop index index_name on table_name

索引的设计原则

  1. 查询频次较高的，数据量大的表建立索引

  2. 索引字段的选择，最佳候选列从where 子句中获取，提取where子句中出现频率最多的，最常用的组合

  3. 最左前缀 n个列组合而成的组合索引，那么相当于是创建了n个索引，如果查询时where 子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率

    • name,
      name id
      name id, age

优化SQL的步骤

  1. 查看SQL执行的频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

  • 下面的命令显示了当前 session 中所有统计参数的值：

    show status like 'Com_______';

    在这里插入图片描述

show status like 'Innodb_rows_%';

在这里插入图片描述
Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数含义
Com_select执行 select 操作的次数，一次查询只累加 1。
Com_insert执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update执行 UPDATE 操作的次数。
Com_delete执行 DELETE 操作的次数。
Innodb_rows_readselect 查询返回的行数。
Innodb_rows_inserted执行 INSERT 操作插入的行数。
Innodb_rows_updated执行 UPDATE 操作更新的行数。
Innodb_rows_deleted执行 DELETE 操作删除的行数。
Connections试图连接 MySQL 服务器的次数。
Uptime服务器工作时间。
Slow_queries慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

  1. 定位低效率执行的SQL

    1. 慢查询日志：慢查询日志定位执行效率低的SQL语句，

      通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld
      写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

    2. show processslist 慢查询日志在查询结束以后才记录，
      可以通过该命令查看当前MySQL正在执行的线程，包括线程的状态，是否锁表等

    在这里插入图片描述

操作备注
id列用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
user列显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host列显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db列显示这个进程目前连接的是哪个数据库
command列显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time列显示这个状态持续的时间，单位是秒
state列显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info列显示这个sql语句，是判断问题语句的一个重要依据

explain 分析

可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
在这里插入图片描述

字段含义
idselect查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table输出结果集的表
type表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
rows扫描行的数量
extra执行情况的说明和描述

☞ id

  1. Id 表示加载的顺序是从上到下的

  2. id不同id值越大，优先级越高，优先被执行

  3. id有相同的，也有不同的。同时存在，id相同的可以被认为是一组，从上到下执行，所有的组中，id值越大，优先级越高，越先执行

☞select_type

select_type含义
SIMPLE简单的select查询，查询中不包含子查询或者UNION
PRIMARY查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY在SELECT 或 WHERE 列表中包含了子查询
DERIVED在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT从UNION表获取结果的SELECT

☞table

展示这一行的数据是关于哪一张表的

☞ type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type含义
NULLMySQL不访问任何表，索引，直接返回结果
system表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
indexindex 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

查询至少达到 range 级别， 最好达到ref

☞ key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

☞rows

扫描行的数量。

☞extra

其他的额外的执行计划信息，在该列展示 。

extra含义
using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

避免索引失效的情况

  1. 全值匹配，对索引所有列都指定具体值

     explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

  2. 最左前缀法则

    • 如果索引了多列，要遵守该法则，查询从索引的最左前列开始，并且不能跳过索引中的列

    • 最左侧查询的字段一定为 索引列的字段

    • 如果符合最左法则，但是跳跃了某一列，就只有最左列索引有效

  3. 范围查询之后右边的列是不会走索引的

    • explain select * from name = ‘asd’ and ids > 0 and address =‘ads’
    • 根据前面的两个字段name status 是走索引的，但是最后一个条件没有走

  4. 不要在索引列上进行运算操作，索引将失效

  5. 字段串不加单引号，造成索引失效

    失效 explan select * from id = 0;
explan select * from id = '0';
    • 原因： 查询时，没有将字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效

  6. 尽量使用覆盖索引，避免实现select *

    • 只访问索引的查询（索引列完全的包含查询列）

      • 查询列如果超出索引列也会降低性能

        using备注
        using index使用覆盖索引的时候就会出现
        using where在查找使用索引情况下，需要回表查询数据
        using index condition查找使用了索引，但是需要回表查询数据
        using index using where查询使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

  7. 用or 分开的条件，如果or之前条件的列中有索引，而后面的列中没有索引，那么涉及的索引就不会被用到

    • 示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
      explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

  8. 以 %开头的like模糊查询，索引失效

    • 如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效

      select *from table where address like "asdas%"

      • 失效

        select *from table where address like "%asdas%"

      • 解决方案:通过覆盖索引来解决

        select id.name,status from table where address like "%asdas%"

  9. 如果MySQL的评估 索引比全表慢，那么不会使用索引

  10. is nul 、is not null 有时索引会失效

  11. in 走索引，not in 索引失效

  12. 单列索引和复合索引

    • 原因：单列索引的情况数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。
    • 建议：
      • 尽量使用复合索引，少使用单列索引
      • 如果使用复合索引，那么覆盖就比较广泛

分析性能问题

系统中用户访问日志的数据量，随着时间的推移，这张表的数据量会越来越大，因此我们需要根据业务需求，来对日志查询模块的性能进行优化。

  1. 分页查询优化

    • 分页查询的查询分为两步：

      1. 查询符合条件的总记录数（count()）

      2. 查询符合条件的列表数据（limit ()）

    • 优化解决：

      • 增加汇总表（中间表记录count数量）
      • redis缓存来专门记录该表对应的记录数（注意：并不能保证百分之百的准确）

    对于数据库的操作，“快速、精确、实现简单”，三者永远只能满足其二，必须舍掉其中一个。

  2. 条件查询优化

    对查询条件及排序字段建立索引。

  3. 读写分离

    主从复制集群，完成读写分离。

  4. MySQL服务器优化

  5. 应用优化

分页优化

优化count
  • 创建一张表用来记录日志表的总数据量：
create table log_counter(
	logcount bigint not null
)engine = innodb default CHARSET = utf8;
  • 在每次插入数据之后，更新该表 ：
<update id="updateLogCounter" >
    update log_counter set logcount = logcount + 1
</update>
  • 在进行分页查询时, 获取总记录数，从该表中查询既可。
<select id="countLogFromCounter" resultType="long">
    select logcount from log_counter limit 1
</select>
优化 limit

  • 问题提出：在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。

    • 场景：SQL查询 “limit 1000000,10” ，执行语句需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记录，那么前面的1000000 条记录会被抛弃，查询代价非常大。
    • SQL语句：
      select * from operation_log limit 1000000 , 10;

  • 优化SQL：

    select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;

排序优化

在查询数据时，如果业务需求中需要我们对结果内容进行了排序处理 , 这个时候,我们还需要对排序的字段建立适当的索引, 来提高排序的效率。

读写分离

  • 概述

    • 在Mysql主从复制的基础上，可以使用读写分离来降低单台Mysql节点的压力，从而来提高访问效率，读写分离的架构如下：

    • 对于读写分离的实现，可以通过Spring AOP 来进行动态的切换数据源，进行操作 ：

jdbc.write.driver=com.mysql.jdbc.Driver
jdbc.write.url=jdbc:mysql://192.168.142.128:3306/mysql_demo
jdbc.write.username=root
jdbc.write.password=itcast

jdbc.read.driver=com.mysql.jdbc.Driver
jdbc.read.url=jdbc:mysql://192.168.142.129:3306/mysql_demo
jdbc.read.username=root
jdbc.read.password=itcast

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">


    <!-- 配置数据源 - Read -->
    <bean id="readDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.read.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.read.url}"></property>
        <property name="user" value="${jdbc.read.username}"></property>
        <property name="password" value="${jdbc.read.password}"></property>
    </bean>


    <!-- 配置数据源 - Write -->
    <bean id="writeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.write.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.write.url}"></property>
        <property name="user" value="${jdbc.write.username}"></property>
        <property name="password" value="${jdbc.write.password}"></property>
    </bean>


    <!-- 配置动态分配的读写 数据源 -->
    <bean id="dataSource" class="cn.itcast.aop.datasource.ChooseDataSource" lazy-init="true">
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <entry key="write" value-ref="writeDataSource"/>
                <entry key="read" value-ref="readDataSource"/>
            </map>
        </property>

        <property name="defaultTargetDataSource" ref="writeDataSource"/>

        <property name="methodType">
            <map key-type="java.lang.String">
                <entry key="read" value=",get,select,count,list,query,find"/>
                <entry key="write" value=",add,create,update,delete,remove,insert"/>
            </map>
        </property>
    </bean>

</beans>

ChooseDataSource

public class ChooseDataSource extends AbstractRoutingDataSource {

    public static Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();

    /**
     * 实现父类中的抽象方法，获取数据源名称
     * @return
     */
    protected Object determineCurrentLookupKey() {
        return DataSourceHandler.getDataSource();
    }

    // 设置方法名前缀对应的数据源
    public void setMethodType(Map<String, String> map) {
        for (String key : map.keySet()) {
            List<String> v = new ArrayList<String>();
            String[] types = map.get(key).split(",");
            for (String type : types) {
                if (!StringUtils.isEmpty(type)) {
                    v.add(type);
                }
            }
            METHOD_TYPE_MAP.put(key, v);
        }
        System.out.println("METHOD_TYPE_MAP : "+METHOD_TYPE_MAP);
    }
}

DataSourceHandler

public class DataSourceHandler {

    // 数据源名称
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();

    /**
     * 在项目启动的时候将配置的读、写数据源加到holder中
     */
    public static void putDataSource(String datasource) {
        holder.set(datasource);
    }

    /**
     * 从holer中获取数据源字符串
     */
    public static String getDataSource() {
        return holder.get();
    }
}

DataSourceAspect

@Aspect
@Component
@Order(-9999)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 配置前置通知,使用在方法aspect()上注册的切入点
     */
    @Before("execution(* cn.itcast.service.*.*(..))")
    @Order(-9999)
    public void before(JoinPoint point) {
        
        String className = point.getTarget().getClass().getName();
        String method = point.getSignature().getName();
        logger.info(className + "." + method + "(" + Arrays.asList(point.getArgs())+ ")");

        try {
            for (String key : ChooseDataSource.METHOD_TYPE_MAP.keySet()) {
                for (String type : ChooseDataSource.METHOD_TYPE_MAP.get(key)) {
                    if (method.startsWith(type)) {
                        System.out.println("key : " + key);
                        DataSourceHandler.putDataSource(key);
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
  • 通过 @Order(-9999) 注解来控制事务管理器, 与该通知类的加载顺序 , 需要让通知类 , 先加载 , 来判定使用哪个数据源 .

应用优化读写分离

缓存
  • 可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率。
全文检索
  • 如果业务系统中的数据量比较大（达到千万级别），这个时候，如果再对数据库进行查询，特别是进行分页查询，速度将变得很慢（因为在分页时首先需要count求合计数），为了提高访问效率，这个时候，可以考虑加入Solr 或者 ElasticSearch全文检索服务，来提高访问效率。
非关系数据库
  • 也可以考虑将非核心（重要）数据，存在 MongoDB 中，这样可以提高插入以及查询的效率。

视图

  • 定义

    • 是一种虚拟存在的表，视图并不在数据库中实际存在，行和列数据来定义视图查询中使用的表，并且在使用视图时动态生成的，通俗的将，视图就是一条select 语句执行后返回的结果集，

  • 优势：

    • 简单：使用视图查询，完全不用关系表中的数据结构和数据字段，关联条件和筛选条件，对用户来说只需要看已经查询完毕的数据集即可

    • 安全，只能访问他们被允许查询的结果集，且数据独立，可以屏蔽某些数据表结构，但对用户来说没有任何影响

  • 创建

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]
  • 修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 : 
	WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
	
	LOCAL ： 只要满足本视图的条件就可以更新。
	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.
  • 示例：
create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
  • 查询视图
select * from viewname_viewxxx

  • 查看视图

    • SHOW TABLES / SHOW VIEWS
    • SHOW TABLE STATUS:显示表的信息，同时也可以显示视图的信息
    • SHOW CREATE VIEW:查询某个视图的定义

  • 删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

存储过程

。。。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/520026.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Oracle的物理结构解析

Oracle的物理结构解析

这些图是我自己画的&#xff0c;我也会在我的公众号【会用数据库】解析。理解起来非常简单&#xff0c;而且非常好记。不用死记硬背&#xff0c;有兴趣可以来公众号看呀。
阅读更多...
GitOps - 为 OpenShift GitOps 配置邮件通知

GitOps - 为 OpenShift GitOps 配置邮件通知

《OpenShift 4.x HOL教程汇总》 说明&#xff1a;本文已经 在OpenShift 4.15 OpenShift GitOps 1.11.2 环境中验证 文章目录 ArgoCD 的 Notification 功能简介启动 OpenShift GitOps 的 Notification 功能配置邮件通知验证参考 说明&#xff1a;先根据《OpenShift 4 之 GitOp…
阅读更多...
Peter算法小课堂—树状数组

Peter算法小课堂—树状数组

大家好&#xff0c;我是人见人爱&#xff0c;花见花开&#xff0c;车见车爆胎的树状数组Peter Pan&#xff0c;hhh 讲正文前&#xff0c;先来一个长文警告⚠很重要的知识点&#xff1a;L SB&#xff08;SB&#xff1f;&#xff09; LSB 怎么算呢&#xff1f; 哦……懂了&…
阅读更多...
环形链表2--绝妙的运算

环形链表2--绝妙的运算

一、要求 给定一个链表的头节点 head &#xff0c;返回链表开始入环的第一个节点。 如果链表无环&#xff0c;则返回 null。 如果链表中有某个节点&#xff0c;可以通过连续跟踪 next 指针再次到达&#xff0c;则链表中存在环。 为了表示给定链表中的环&#xff0c;评测系统…
阅读更多...
静态路由协议实验综合实验

静态路由协议实验综合实验

需求&#xff1a; 1、除R5的换回地址已固定外&#xff0c;整个其他所有的网段基于192.168.1.0/24进行合理的IP地址划分。 2、R1-R4每台路由器存在两个环回接口&#xff0c;用于模拟连接PC的网段&#xff1b;地址也在192.168.1.0/24这个网络范围内。 3、R1-R4上不能直接编写到…
阅读更多...
机器学习KNN最邻近分类算法

机器学习KNN最邻近分类算法

文章目录 1、KNN算法简介2、KNN算法实现2.1、调用scikit-learn库中KNN算法 3、使用scikit-learn库生成数据集3.1、自定义函数划分数据集3.2、使用scikit-learn库划分数据集 4、使用scikit-learn库对鸢尾花数据集进行分类5、什么是超参数5.1、实现寻找超参数5.2、使用scikit-lea…
阅读更多...
【vite】

【vite】

目录 vite介绍vite的基础应用vite创建项目vite创建vue3项目vite创建vue2项目vite创建react项目 vite中使用css的各种功能vite中使用ts vite介绍 一、特点&#xff1a; 开发时效率极高开箱即用&#xff0c;功能完备摄取丰富&#xff0c;兼容rollup超高速热重载预设应用和类库打…
阅读更多...
vulhub中 Struts2-015 远程代码执行漏洞复现

vulhub中 Struts2-015 远程代码执行漏洞复现

影响版本: 2.0.0 - 2.3.14.2 ## 原理与测试 漏洞产生于配置了 Action 通配符 *&#xff0c;并将其作为动态值时&#xff0c;解析时会将其内容执行 OGNL 表达式&#xff0c;例如&#xff1a; xml <package name"S2-015" extends"struts-default"> …
阅读更多...
上位机图像处理和嵌入式模块部署（qmacvisual之n点标定）

上位机图像处理和嵌入式模块部署（qmacvisual之n点标定）

【 声明&#xff1a;版权所有&#xff0c;欢迎转载&#xff0c;请勿用于商业用途。 联系信箱&#xff1a;feixiaoxing 163.com】 工业场景中&#xff0c;很多时候图像是用来做测量的。虽然我们很希望载台是平的&#xff0c;摄像头是正对着拍摄物体的&#xff0c;但是运行时间长…
阅读更多...
可视化图表：象形柱图，比柱图漂亮、有趣100倍。

可视化图表：象形柱图，比柱图漂亮、有趣100倍。

一、什么是象形柱图 象形柱图&#xff08;Pictorial Bar Chart&#xff09;是一种可视化图表&#xff0c;它使用图形或图片代替传统的矩形柱子来表示数据。每个图形或图片的大小和形状都与对应的数据值相关联&#xff0c;从而形成一种视觉上的象征性表示。 象形柱图通常用于展…
阅读更多...
【每日刷题】Day3

【每日刷题】Day3

【每日刷题】Day3 &#x1f955;个人主页&#xff1a;开敲&#x1f349; &#x1f525;所属专栏&#xff1a;每日刷题&#x1f34d; 目录 1. 69. x 的平方根 - 力扣&#xff08;LeetCode&#xff09; 2. 70. 爬楼梯 - 力扣&#xff08;LeetCode&#xff09; 3. 118. 杨辉三…
阅读更多...
STL容器（3）

STL容器（3）

1,stack容器 1.1 基本概念 概念&#xff1a;stack是一种先进后出的数据结构&#xff0c;它只有一个出口 因此&#xff1a; 栈中只有顶端的元素才可以被使用&#xff0c;因此占不允许有遍历行为 栈中进入数据称为--入栈&#xff08;push) 栈中弹出数据称为--出栈&#xff08…
阅读更多...
【Linux】虚拟机连不上外网 (1)，2024百度网络安全岗面试真题收录解析

【Linux】虚拟机连不上外网 (1)，2024百度网络安全岗面试真题收录解析

vi /etc/sysconfig/network-scripts/ifcfg-ens33 BOOTPROTOstatic ONBOOTyes IPADDR? NETMASK? GATEWAY? dns18.8.8.8 dns1144.144.144.144 这两个必填 自我介绍一下&#xff0c;小编13年上海交大毕业&#xff0c;曾经在小公司待过&#xff0c;也去过华为、OPPO等大厂…
阅读更多...
深入理解Armv9 DSU-110中的L3 cache

深入理解Armv9 DSU-110中的L3 cache

快速链接: 【精选】ARMv8/ARMv9架构入门到精通-[目录] &#x1f448;&#x1f448;&#x1f448; 关键词&#xff1a; DynamIQ cluster、DSU-110、DSU-120、DSU、cache、mmu、缓存、高速缓存、内存管理、MPAM 思考&#xff1a; 1、L1、L2、L3 cache的替换策略是怎样的&#xff…
阅读更多...
Android中的aidl接口及案例说明

Android中的aidl接口及案例说明

目录 一、什么是AIDL 二、AIDL语法规格 三、AIDL实例 客户端： 服务端： 一、什么是AIDL AIDL，即 Android Interface Definition Language，用于android不同进程间通信接口。同一个应用里面还是建议用正常接口实现功能即可。 官方说明：Android 接口定义语言 (AIDL) | …
阅读更多...
如何使用屏幕变式控制SAP系统操作界面字段的必输、显示或隐藏

如何使用屏幕变式控制SAP系统操作界面字段的必输、显示或隐藏

在SAP/ERP项目实施中经常会遇到要求把SAP系统操作的界面中某些字段设置为必输&#xff0c;显示或隐藏&#xff0c;遇到这种需求时&#xff0c;有些业务操作界面可以通过后台进行屏幕的字段状态设置解决&#xff0c;而有些业务的操作界面是没有屏幕字段的后台设置的&#xff0c;…
阅读更多...
DSP实时计算平台设计方案：912-基于6U CPCIe的双路光纤图像DSP实时计算平台

DSP实时计算平台设计方案：912-基于6U CPCIe的双路光纤图像DSP实时计算平台

基于6U CPCIe的双路光纤图像DSP实时计算平台 一、设备概述 设备基于6U CPCIe架构&#xff0c;通过背板交换实现4片信号处理板卡的互联传输&#xff0c;每个信号处理板卡支持双TMS320C6678&#xff0c;支持2路光纤的图像处理&#xff0c;实现FPGA的预处理和备份工…
阅读更多...
机器学习中的GBDT模型及其优缺点(包含Python代码样例)

机器学习中的GBDT模型及其优缺点(包含Python代码样例)

目录 一、简介 二、优缺点介绍 三、Python代码示例 四、总结 一、简介 GBDT&#xff08;Gradient Boosting Decision Tree&#xff09;是一种集成学习算法&#xff0c;被广泛应用于机器学习中的回归和分类问题。它由多个决策树组成&#xff0c;每个决策树都通过迭代逐渐提升…
阅读更多...
之前翻硬币问题胡思乱想的完善

之前翻硬币问题胡思乱想的完善

题目背景 小明正在玩一个“翻硬币”的游戏。 题目描述 桌上放着排成一排的若干硬币。我们用 * 表示正面&#xff0c;用 o 表示反面&#xff08;是小写字母&#xff0c;不是零&#xff09;&#xff0c;比如可能情形是 **oo***oooo&#xff0c;如果同时翻转左边的两个硬币&#x…
阅读更多...
Transformer位置编码详解

Transformer位置编码详解

在处理自然语言时候&#xff0c;因Transformer是基于注意力机制&#xff0c;不像RNN有词位置顺序信息&#xff0c;故需要加入词的位置信息来显示的表明词的上下文关系。具体是将词经过位置编码(positional encoding)&#xff0c;然后与emb词向量求和&#xff0c;作为编码块(Enc…
阅读更多...
最新文章

Copyright @ 2022~2023