MySQL分享

最新推荐文章于 2024-12-24 10:20:55 发布
原创 最新推荐文章于 2024-12-24 10:20:55 发布 · 395 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

本文深入探讨了MySQL的索引，包括索引的数据结构、MyISAM与InnoDB的实现方式以及索引优化策略。介绍了索引的种类、建索引的原则和Explain的使用，强调了最左前缀原则、索引覆盖和索引下推的重要性，并讨论了常见不走索引的场景。此外，还提到了MySQL的扩展，如业务拆分、主从复制和分库分表的基本概念和策略。

MySQL分享

索引

什么是索引
  1. 想要快速查询数据，需要使用优秀的查找算法
  2. 优秀的查找算法依托的是优秀的数据结构
    • 二分查找，需要被检索的数据有序
    • 二叉树查找，需要是二叉树的数据结构
  3. 数据库的数据不可能完全满足各类数据结构
  4. 数据库系统维护满足特定查找算法的数据结构，并以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引
索引的数据结构
  1. Hash
    • 特性：把索引数据进行hash计算得到一个hash地址，相当于点对点查询，性能很好
    • 缺点：不支持范围查找
  2. B-Tree
    • 特性：
  3. b+Tree
索引的存取

索引文件一般较大，索引会存储在磁盘上

磁盘存取原理

索引的检索，需要磁盘的IO操作，存在机械运动耗费，时间消耗相对于内存存取是巨大的。

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的。
盘面

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用

磁盘预读

根据局部性原理，为了减少磁盘IO，磁盘往往不是按需进行读取，而是每次都会进行预读，顺序往后读取一定长度的数据到内存中（不用寻道，顺序旋转即可）

Mysql索引实现

MyISAM

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。如下图所示：
在这里插入图片描述

  • MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
  • MyISAM的辅助索引结构与主键索引结构一致，唯一的区别是辅助索引的key可以重复。

因此，MyISAM的索引方式也叫做“非聚集索引”

InnoDB

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM不同。

  • InnoDB的数据文件本身就是索引文件，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。如下图所示：
    在这里插入图片描述

  • 因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须要有主键，如果没有显式指定，MySQL会自动选择一个可以唯一标识数据行的列作为主键，如果不存在这样的列，则MySQL会自动生成一个long类型的隐含字段作为主键。

  • InnoDB辅助索引的data域存的是相应记录的主键值。也就是说，通过辅助索引检索数据，需先检索辅助索引树，获取主键值，在检索主键索引树，获取记录。

因此，InnoDB的索引方式也叫做“聚集索引”

索引的种类

  1. 单列索引
    1. 主键索引：键值唯一，不允许有空值，一张表只能有一个主键索引，一般设置为自增类型，减少索引树的自平衡的性能耗费
    2. 唯一索引：键值唯一，允许有空值
    3. 普通索引：无限制
  2. 组合索引：包含多个字段的索引，使用时遵循最左匹配原则
  3. 全文索引：在文本字段上进行模糊匹配时，避免普通索引’%str%'不走索引的限制，可以在文本字段上建立全文索引，使用方法如下：
ALTER TABLE TableName ADD FULLTEXT(column);
SELECT * FROM TableName WHERE MATCH(column) AGAINST('xxx', 'sss');

索引优化

建索引的原则

索引的存放需要额外的空间，并且对表数据的增删改操作，需要额外维护索引树，所以索引不能滥用

  1. 在经常需要搜索的列上建索引
  2. 在表与表连接的字段上建立索引
  3. 在经常需要排序、分组的字段上建索引
  4. 读多写少的表，建议优先考虑唯一索引
  5. 优先使用联合索引，并优化索引内字段顺序，尽可能减少索引的数量
  6. 在区分度较高的列上建索引
  7. 尽可能使用前缀来索引
  8. 不在很长的字段上建索引
exlpain详解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UfkvaNOV-1613809182253)(en-resource://database/715:0)]

  1. id ： sql执行顺序标识
  2. select_type ： 表示查询中每个子查询的类型
    • SIMPLE(简单SELECT，不使用UNION或子查询等)
    • PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
    • UNION(UNION中的第二个或后面的SELECT语句)
    • DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
    • UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
    • SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
    • DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
    • DERIVED(派生表的SELECT, FROM子句的子查询)
    • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
  3. table：所访问数据库中表名称
  4. type：对表的访问方式
    • 常用的类型有（性能从好到差）： system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>range>index>ALL
    • system ：表里只有一条记录
    • const：表示通过索引一次就找到了记录
    • eq_ref：当连接使用索引的所有部分，并且索引是主键或唯一的非空索引时，将使用该索引
    • ref：使用了主键索引或者唯一索引的最左前缀的一部分，或者使用了非唯一索引的等值查询
    • fulltext：使用了全文索引
    • ref_or_null：类似ref，但是使用索引的列允许为空
    • index_merge：表示使用了索引合并的优化方法
    • range：只检索指定范围的行，使用一个索引来选择行
    • index：只遍历索引树（索引树比主键索引树小，速度快一些）
    • ALL：遍历全表（主键索引树）
  5. possible_keys : 显示可能应用在这张表的索引，如果查询涉及到的字段有索引，则会被列出来，但不一定会被用到
  6. key：实际用到的所有，为null则说明没有使用到索引
  7. key_len：表示使用索引键的长度。使用组合索引时，可以根据这个值来判断用到了哪些字段索引。（后文有补充key_len的计算）
  8. ref：显示将哪些列或常量与键列中命名的索引进行比较，以便从表中选择行
  9. rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
  10. filtered：表数据的过滤比例
  11. Extra：输出解析查询的重要附加信息（有些信息输出时说明SQL需要优化）
    • Using filesort：说明MySQL会对数据使用一个外部的索引排序，以为这需要添加索引，优化查询效率
最左前缀原则 索引覆盖 索引下推

新建一个简单的表测试一下
在这里插入图片描述

最左前缀原则
顾名思义，就是多列索引，最左优先原则，我们通过例子理解一下

  1. 全匹配
    在这里插入图片描述

可以看到，MySQL查询优化器会自动调整条件顺序，索引都用到了索引
2. 匹配1，2列或者匹配2，3列
在这里插入图片描述

匹配1，2列根据最左匹配原则，可以用到索引
匹配2，3列无法用到索引
3. 匹配1，3列或单独匹配第2列
在这里插入图片描述

匹配1，3列，可以使用到索引，但是从key_len可以看出来，只使用到了索引的一部分
单独匹配第2列，无法使用到索引

因此，在建立联合索引的时候，应该慎重考虑索引内字段的顺序
如果通过调整索引内顺序，可以少维护一个索引，那么这个顺序是优先考虑采用的
如果遇到由联合索引查询的场景又有单独字段查询的场景，可以结合字段顺序及字段占用空间综合考虑

索引覆盖
当我们使用索引查询数据时，我们需要查询的字段已经包含在辅助索引树上，就不需要根据主键ID回到主键索引树上查询数据，减少回表次数。合理利用覆盖索引，可以显著提升性能。

索引下推
当我们在索引树上遍历数据的过程中，可以对索引字段先进行判断，过滤掉不满足条件的数据，减少回表次数。

常见的不走索引的场景

  1. where语句中包含or
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wfb5L9Z5-1615257810151)(en-resource://database/731:1)]
    如果or左右两侧都命中相同的索引，这是所有也是有效的
    即使左右两列分别都建有所有，索引也是失效的
    所以应该尽量避免使用or语句，可以根据情况用union all或in来代替

  2. where语句中索引列使用了负向查询，可能会导致索引失效
    负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等
    负向查询并不绝对会索引失效，MySQL优化器的会判断走索引和全表扫描的成本高低，来选择是否走索引
    但相对来说都是低效操作，应尽量避免

  3. 在索引列上使用内置函数或者再索引列上运算，一定会使索引失效
    因为索引树上存的都是原值，使用函数或者运算都会改变原值，从而无法命中索引

  4. 隐式类型转换，一定会使索引失效（常常被忽略）
    mysql类型转换的过程用到了函数

  5. like通配符可能会使索引失效
    参考最左前缀原则，'str%'可以使用到索引，其他情况会使索引失效

  6. 联合索引中，查询条件的顺序问题导致的索引失效
    考虑减少索引的数量，可以通过调整索引内顺序来适配多个查询场景

  7. 某些场景下，MySQL会放弃走索引，直接扫描全表
    比如，在区分度很低的列上建索引，并且查询需要回表，mysql可能就会放弃走索引，直接扫描全表

ps：索引字段可以为null，使用is null可以使用到索引，is not null时，不能使用到索引
但是，null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性，同等条件下性能低
而且，null值需要更多的存储空间，需要额外的标识位
所以，外面尽量把字段设置成不可为空，并提供默认值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FGVD0KL6-1615257810153)(en-resource://database/733:1)]

常用的索引优化
扩展问题
varchar长度设置问题
  1. 把长度设置成4的倍数，达到内存对齐的目的。
    解读：varchar类型，会占用一个或者两个字节来存储字段的实际长度，所以长度应该设置成(2n)-1或(2n)-2。
    另外，InnoDB读取数据是按页读取的，所以不存在内存对齐的问题。
  2. 把长度设置的很大
    解读：因为varchar是变长的类型，varchar(20)和varchar(100)存储’abc’时，都是只会占用三个字符的空间
    但是，如果查询时使用到了临时表，Mysql会有内存预分配机制，读数据前会根据你字段设置的最大长度预分配内存（因为事先不知道字段的实际长度），
    所以，varchar长度设置的过大会占用更大的内存，应该按需设置
    ps：会用到临时表的操作
    • union查询
    • order by和group by的子句不一样
    • order by 中使用了distinct关键字
    • group by 列没加索引
    • …
常用类型的key_len计算
类型长度备注
tinint2
int4
bigint8
decimal(M,D)M字节(D+2 , 如果M < D)？
charlength * 3如果设置可以为空，则再+1
varcharlength * 3 + 2因为是可变类型，所以要+2, 如果可以为空，则再+1, 长度小于256，则用一个字节记录长度,长度大于256，则用两个字节记录长度
date4
datetime6
timestamp4
主键索引 唯一索引 普通索引 性能比较

主键索引
主键索引是聚集索引，索引上保存着完整的数据行，相对于普通索引，可以就按少查询的回表次数，索引优先选择使用主键索引

唯一索引和普通索引
查询的过程：
- 唯一索引：在索引树上匹配到一条数据后，立即返回查询结果
- 普通索引：在索引树上匹配到一条数据后，继续向后匹配，判断下一条数据是否匹配，匹配失败后返回查询结果
实际上普通索引比唯一索引查询数据的时候，多一次数据匹配的操作
但是，MySQL查询是按页把数据从磁盘中读到内存里操作，所以查询时的数据匹配都是在内存里操作的，性能影响很小。
有一种情况，比如正好查询的这条数据是一页数据里的最后一条，需要匹配下一条数据的话，就需要从磁盘里把下一页数据读到内存里进行匹配，这样性能的影响就会很大，但是这种概率非常小。
所以，综合来看，查询的时候，唯一索引和普通索引的效率相差无几。

插入或更新的过程：
- 普通索引：找到位置直接插入或更新
- 唯一索引：先查找校验数据是否唯一，然后找到位置后插入或更新

change buffer:
当更插入或新一个数据页的时候，如果数据页在内存中，则直接在内存里插入或更新数据页数据。如果数据页不在内存中，在不影响数据一致性的前提下，InnoDB会将这些插入或更新操作缓存到change buffer中,这样就不需要从磁盘中读入这个数据页进行插入或更新了，下次遇到这个数据页读到内存的时候，就把changebuffer中的插入或更新merege到这个数据页上面。

PS：虽然是这个叫changeBuffer，类似缓存的东西， 但是也是会持久化到硬盘中的。 merege操作除了在访问数据页的时候会触发，在系统后台也会定期merege ，在数据库正常关闭(shutdown)的过程中，也会执行merge操作。

因此，用上change buffer后，插入或更新操作时减少了磁盘的读写，性能提升很大。

但是，change buffer 不能被唯一索引用到，因为唯一索引插入或更新数据时，需校验数据的唯一性，所以必须要把数据页读到内存里才能判断出来。因此，数据页已经读到内存里，直接插入或更新数据页就行，不需要使用change buffer了。

Mysql 扩展

业务拆分

在业务初始阶段，为了加快服务的上线和业务的快速迭代，往往都采用集中式的架构。随着业务系统的扩大，系统变得越来越复杂，越来越难以维护，开发效率变得越来越低，并且对资源的消耗也变得越来越大，通过硬件提高系统性能的方式带来的成本也越来越高。

随着业务规模的增大，访问量的增大，我们不得不对业务进行拆分。每一个模块都使用单独的数据库来进行存储，不同的业务访问不同的数据库，将原本对一个数据库的依赖拆分为对4个数据库的依赖，这样的话就变成了4个数据库同时承担压力，系统的吞吐量自然就提高了。

主从复制

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。

MySQL中复制的优点如下：

  • 同时提供读写的性能 - 所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。可以提高写入性能（因为主库专用于更新），同时显著提高了越来越多的从库的读取速度。
  • 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
  • 数据分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。

原理：

  1. 主服务上的任何修改操作，都会把修改记录到二进制日志文件 binary log。
  2. 从服务器连接上主服务器并读取主服务器的binary log写入到本地的realy log里。
  3. 从服务器溶蚀会开启一个本地线程定时检查realy log，如果有更新，则会立即更新本地数据。
  4. 每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。
分库分表
为什么要分库分表

当单表的数据量达到千万级别时，查询将会变得非常耗时，如果再涉及到千万级大表的连表查询，那性能更是灾难级别的。
所以，当数据来达到千万级别时，就要开始考虑分库分表策略了

常用分库分表策略

  1. 水平分库

    • 策略：以表里某个字段为依据，按照一定的策略，将一个库中的数据拆分到多个库中。
    • 特性：每个库的结构都一样，数据都不一样，所有库的数据总和就是全量数据。
    • 场景：多个库可以提高系统的并发量，环境IO和CPU的压力

  2. 水平分表

    • 策略：以表里某个字段为依据，按照一定的策略，将一个表中的数据拆分到多个表中。
    • 特性：每个表的结构都一样，数据都不一样，所有表的数据综合就是全量数据。
    • 场景：分成多表可以解决单表数据量过大的问题，提高单表的操作效率，减轻CPU压力。但是无法解决系统并发量高的问题。

  3. 垂直分库

    • 策略：以表为依据，根据业务归属的不同，把表拆分到不同的库里。
    • 特性：每个库的结构都不一样，数据也不一样，所有库的数据总和是全量数据。
    • 场景：垂直分库往往都是配合业务模块的服务化，拆分不同的业务模块成单独的服务，单独的库。

  4. 垂直分表

    • 策略：以表字段为依据，根据表字段的活跃性，把表字段拆分到不同的表中（可以用主表和扩展表的形式）；
    • 特性：所有表的结构和数据都不一样，但是表之间会根据某个字段进行关联，所有表数据的总和就是全量数据。
    • 场景：表的数据量不大，但是字段多（特别是有大字段的时候），可以根据热点和非热点数据分表，提高热点数据的查询效率。需查询全量数据时，应该时业务层做数据拼接处理，避免数据库join操作。

  5. 一起使用：垂直拆分和 水平拆分都在使用

    • 垂直拆分主要是做业务上的分割，把业务的各个子系统都规划好，能解耦就解耦。
    • 水平分库分表。将大表数据拆到若干个库中或表里。
常用的路由规则

  1. 范围路由
    选取有序的数据列，按范围把数据分段，放到不同的表里
    优点：

    • 可以随着数据量的增长，平滑的增加库或表，原有的数据不需要动
    • 单表的大小可控
    • 使用分片字段范围查找时，效率高
      缺点：
    • 存在热点数据，可能会被频繁读写，历史数据却很少用到

  2. hash算法
    选取某列或者某几列的值进行hash运算，然后根据hash结果把数据分在不同的表上。
    优点：

    • 各表的数据分布比较均匀
      缺点：
    • 后期如果需要扩容比较麻烦，所有数据得重新分配位置

  3. 路由表
    就是新建一张专门得路由表来记录路由信息。
    优点：

    • 使用非常灵活，扩容时非常方便
      缺点：
    • 多一个查询路由表得操作，影响性能，
    • 路由表得数据来会非常大，本身就会成为性能瓶颈
分布式数据库中间件

待续

mysql索引详解
学Java，找哪吒
07-07 11万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql分享-explain讲解
10-29
mysql分享-explain讲解,重点让用户看懂explain如何分析mysql的慢查询语句
mysql 数据库使用分享（多图解析）
Java_zhujia的博客
11-16 671
创建数据库： CREATE DATABASE [IF NOT EXISTS] mydb1;删除数据库： DROP DATABASE [IF EXISTS] mydb1;
高性能mysql技术分享
01-27
mysql性能优化，并发控制，MySql的存储引擎，数据类型优化
mysql可以分享的技术_技术分享【MySQL】
weixin_35261213的博客
01-19 710
1、查询语句是如何执行的？1、连接1、建立连接2、验证权限，修改了权限，创建新的连接才会生效。3、SQL执行的临时内存2、查询缓存1、先查询缓存，更新操作会导致所有缓存失效。2、MySQL 8.0功能去掉3、分析词法解析，语法解析4、优化1、决定使用哪个索引，比方说根据统计信息预估扫描行数，是否使用临时表，需不需要排序等。2、决定join的各个表的连接顺序5、执行1、调用引擎接口，查找到第一条符合...
mysql分享会_数据库分享会(内容涉及 MySQL、Redis 技术)
weixin_33034651的博客
02-03 155
活动安排活动时间： 5月25日13：00-18：00(周日下午)报名截至5月23日活动人数：200人活动主题： 数据库(内容涉及 MySQL、 Redis 技术)活动主办： 谷歌开发者社区、互动出版网活动协办： 华章图书活动地点： 北京市西城区百万庄大街22号，机械工业出版社10层报告厅(地铁6号车公庄西站800米)。演讲嘉宾京东网 张成远京东分布式数据库系统核心开发工程师, 主要关注技术领域：...
MYSQL分享.pptx
03-23
MYSQL分享 MYSQL是一种开放源代码的关系数据库管理系统，由瑞典的MySQL AB公司开发，现在是甲骨文公司（Oracle）旗下的产品。MYSQL具有高性能、低成本和高可靠性，广泛应用于Internet上的中小型网站中。随着MYSQL的...
MySQL 查询优化案例分享
秋元的博客
12-24 1348
在日常开发中，SQL 查询性能直接影响到系统的响应速度和用户体验。随着数据量的增长，慢查询可能成为系统的瓶颈。本文将通过实际案例，分享几种常见的 MySQL 查询优化方法，帮助开发者快速定位和优化慢查询，提升数据库性能。希望通过以上案例分享，帮助开发者在实际项目中有效优化 MySQL 查询，提升系统整体性能。通过开启慢查询日志，可以记录执行时间超过指定阈值的 SQL 查询，帮助分析系统中的瓶颈。分页查询性能较差，用户访问较深的分页时响应时间缓慢。中近一年的订单数据，查询速度缓慢。操作，执行时间过长。
MYSQL数据库技术分享PPT课件.pptx
10-10
MYSQL数据库技术分享PPT课件.pptx
MySQL调优技术分享PPT.pptx
05-21
【主 题】 MySQL调优技术 【主要内容】B+树介绍、单表性能调优、多表性能调优以及SQL语句效率对比。 【应用场景】1、B+树作为MySQL索引底层实现的数据结构，需要我们了解其工作机制； 2、从创建表要注意的点以及SQL...
mysql分享-2021-06-15
桃子的博客
06-16 667
mysql分享1:创建表2:插入数据3：删除数据表4:查找数据1:单表查询2:多表联表查询3:简单的内联方式5:union联接结果集6:索引6.1:创建表的时候就创建了索引6.2 建完表之后，单独创建索引6.3 唯一索引6.3.1 添加唯一索引的几种方式7:使用小窍门7.1 数据迁移，迁移老数据7.2 拼接参数concat:拼接参数concat_ws:增加分隔符来拼接参数7.3 group 之后对应组函数处理7.4 慢查询 1:创建表 create table a{ id int not null aut
mysql分享
三十岁“开始”学Java
11-03 283
MYSQL,你会想到啥 想到啥，管我啥事，DBA去管。我只写sql,这里告诉你，你的sql执行结果可能跟你想的不一样。一般在没有并发或者不是高并发情况下，是没有问题，这是mysql帮你做好了 。 mysql->数据存储- 数据存到哪 如何保存， 数据格式是怎么样的 如何保证数据不丢失 如何做到数据复制 怎么提供api供各种语言操作 怎么与各种客户端或者说服务器进行通信 怎么处理不同语言...
MYSQL史上最全学习分享
优快云新星计划JAVA赛道TOP5、优快云内容合伙人、JAVA领域优质创作者、资深JAVA开发深耕JAVA领域。
08-26 2189
MYSQL史上最全学习分享
mysql笔记分享
彭大帅的博客
07-14 183
总有一个命令你需要：#mysql -u root -p 密码 --使用root用户登录 usemysql> show databases;--查看数据库 mysql> use mysql; --进入mysql这个数据库 mysql> show tables; --看看库中有哪些表 mysql> describe user;--看看表中的结mysql> create database auth；--创建数据库auth mysql> create table users (user_name CHAR(1
mysql数据库分享点滴
百岁
06-24 165
今天元云大师 给我们做了数据库分享，感觉很有用呀，这里作了分享笔记，供大家参考   Mysql 性能分析关键命令： Set profiling = no Show profiles Select sql_no_cache Show profile all for query n     在执行查询过程中 sending data步骤中有以下开销 1.         服务器数据...
mysql 数据库使用分享
我是香菜
09-26 1954
三个关键字 create ,alter ,drop。
mysql学习分享
weixin_52589052的博客
02-28 122
字符串函数 substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起。 正常查询 mysql> select bTypeName from category where bTypeId=10; Substr截取字符串 mysql> select substr(bTypeName,1,7) from category where bTypeId=10; concat(str1,str2,str3…) 拼接。 把多个字段拼成一个字段输出 my
mysql 分享_雷林鹏分享：MySQL 连接
weixin_32025789的博客
02-03 173
使用mysql二进制方式连接您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。实例以下是从命令行中连接mysql服务器的简单实例：[root@host]# mysql -u root -pEnter password:******在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。以上命令执行后，登录成功输出结果如下:Welco...
丁成云师姐的-MySQL分享
王川的博客
10-17 779
这篇博客放的有点长了，直到今天才来写； 也是最近事情有点多！三个月，三项考试，而且每一项都挺重要，只要考试过了，那么里面的知识肯定也就会了，不然也不会过，加油吧！ MySql,之前只是听说过，也是从来都没有用过，只知道它是数据库的一种，很重要！ 在记录它之前呢，先说一下自己感觉在那一堂课中觉得蛮重要的一样东西，回答问题的三要素。 -01.它是是么？ -02.为什么要用它？ -0
mysql分享文件
最新发布
12-05
<think> 首先，用户的问题是关于“MySQL如何实现文件分享”。用户提到了之前的引用，但明确说“上述引用仅供参考”，并提醒我要参考用户上一次的问题和我的回答。回顾对话历史： - 系统级指令要求我使用特定的格式：行内数学表达式用 `$...$`，独立公式用 `$$...$$`，确保LaTeX语法正确，用中文回答，生成相关问题，并在回答中引用的段落末尾添加引用标识。 - 用户的问题是基于之前的引用：引用[1]是关于Python代码将TXT转换到MySQL，引用[2]是关于一个PHP+MySQL文件分享系统。用户现在问的是“MySQL如何实现文件分享”。 用户说：“我想查找关于使用MySQL分享文件的相关信息”，所以核心是如何用MySQL实现文件分享。 关键点：MySQL是关系型数据库，通常用于存储结构化数据，而不是直接存储文件。文件分享通常涉及存储文件内容或路径，并通过应用程序访问。 从引用[2]来看，它描述了一个PHP+MySQL系统，用于文件分享。系统包括上传和下载文件，管理文件名、密码等。MySQL在这里存储文件元数据（如文件名、路径、状态），而文件内容可能存储在文件系统中或作为BLOB在数据库中。 所以，回答应该解释MySQL在文件分享中的作用：通常存储元数据，而不是直接存储文件内容。 结构我的回答： 1. **解释MySQL的角色**：MySQL主要用于存储文件的相关信息（元数据），如文件名、路径、所有者、权限等。文件内容可能存储在数据库的BLOB字段或外部文件系统中。 2. **实现方式**： - **存储文件路径**：更常见的方式，MySQL存储文件的路径，文件实际存储在服务器磁盘上。 - **存储文件内容**：使用BLOB（Binary Large Object）类型存储文件内容。但这不是最佳实践，因为这会增加数据库大小和性能问题。 3. **步骤示例**： - 创建数据库表来存储文件信息。 - 使用PHP或其他语言处理文件上传和下载。 - 基于引用[2]，可以描述一个简单的系统。 4. **代码示例**：提供一个简单的PHP代码片段，展示如何上传文件到MySQL（存储路径或BLOB）。 5. **注意点**：讨论优缺点，如存储路径更高效，存储BLOB适合小文件。 6. **生成相关问题**：在末尾添加相关问题。 7. **引用标识**：由于参考了用户提供的引用，需要在相关部分添加引用标识。用户说引用[2]是关于PHP+MySQL文件分享系统，所以我可以在描述时添加[^2]。 确保用中文回答。 最后，生成相关问题。 完整回答结构： - 引言：解释MySQL如何用于文件分享。 - 主要方法：存储路径 vs. 存储BLOB。 - 实现步骤：简单描述。 - 代码示例：提供一个PHP代码片段。 - 注意点。 - 相关问题。 </think>### MySQL实现文件分享的方法 MySQL是一种关系型数据库管理系统，主要用于存储结构化数据（如文本、数字、日期等），并不直接支持文件分享。但在文件分享系统中，MySQL通常扮演存储**文件元数据**（如文件名、路径、权限、用户信息等）的角色，而文件内容本身可能存储在数据库的BLOB（Binary Large Object）字段中或外部文件系统中。以下是两种常见实现方式及其步骤，基于用户提供的引用进行说明（引用[2]描述了一个PHP+MySQL文件分享系统[^2]）。 --- ### 1. **存储文件路径（推荐方法）** 这是更高效的方式：MySQL只存储文件在服务器磁盘上的路径，文件内容实际保存在外部目录中。系统通过路径访问文件。 **优点**：数据库负载小，适合大文件分享；**缺点**：需要管理文件系统权限。 #### 实现步骤： 1. **创建MySQL表**：存储文件元数据。 ```sql CREATE TABLE files ( id INT AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255) NOT NULL, -- 文件名 filepath VARCHAR(255) NOT NULL, -- 文件在服务器上的路径 user_id INT, -- 所有者ID status TINYINT DEFAULT 0, -- 状态（0=可下载，1=不可下载） created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 2. **文件上传逻辑**（PHP示例）： - 用户上传文件到服务器指定目录。 - MySQL插入文件路径和元数据。 ```php <?php // 连接MySQL $conn = new mysqli("localhost", "root", "Zjh!1997", "file_share_db"); if ($conn->connect_error) die("连接失败: " . $conn->connect_error); // 处理上传文件 if ($_SERVER["REQUEST_METHOD"] == "POST") { $target_dir = "uploads/"; $filename = basename($_FILES["file"]["name"]); $target_path = $target_dir . $filename; // 保存文件到服务器 if (move_uploaded_file($_FILES["file"]["tmp_name"], $target_path)) { // 存储元数据到MySQL $sql = "INSERT INTO files (filename, filepath, user_id, status) VALUES ('$filename', '$target_path', 1, 0)"; if ($conn->query($sql) === TRUE) { echo "文件上传成功！路径已存储。"; } } } $conn->close(); ?> ``` 3. **文件下载逻辑**： - 用户请求下载时，系统从MySQL查询文件路径。 - PHP读取文件并输出给用户。 ```php <?php $file_id = $_GET['id']; $sql = "SELECT filepath FROM files WHERE id = $file_id AND status = 0"; $result = $conn->query($sql); if ($result->num_rows > 0) { $row = $result->fetch_assoc(); $filepath = $row["filepath"]; header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename="' . basename($filepath) . '"'); readfile($filepath); // 发送文件内容 } ?> ``` 4. **管理功能**：如引用[2]所述，管理员可在后台修改文件名、状态等（例如设置`status=1`禁止下载）[^2]。 --- ### 2. **存储文件内容（BLOB字段）** 将文件内容直接存入MySQL的BLOB字段。 **优点**：数据集中管理；**缺点**：数据库性能下降，仅适合小文件（如<10MB）。 #### 实现步骤： 1. **创建MySQL表**：包含BLOB字段。 ```sql CREATE TABLE files_blob ( id INT AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255) NOT NULL, filedata LONGBLOB, -- 存储二进制文件内容 user_id INT, status TINYINT DEFAULT 0 ); ``` 2. **文件上传逻辑**（PHP示例）： ```php <?php $conn = new mysqli("localhost", "root", "Zjh!1997", "file_share_db"); $filename = $_FILES["file"]["name"]; $filedata = file_get_contents($_FILES["file"]["tmp_name"]); // 插入BLOB数据 $stmt = $conn->prepare("INSERT INTO files_blob (filename, filedata, user_id) VALUES (?, ?, 1)"); $stmt->bind_param("sb", $filename, $filedata); // "sb": string, binary $stmt->send_long_data(1, $filedata); // 处理大二进制数据 $stmt->execute(); ?> ``` 3. **文件下载逻辑**： ```php <?php $file_id = $_GET['id']; $sql = "SELECT filename, filedata FROM files_blob WHERE id = $file_id"; $result = $conn->query($sql); if ($result->num_rows > 0) { $row = $result->fetch_assoc(); header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename="' . $row['filename'] . '"'); echo $row['filedata']; // 输出二进制内容 } ?> ``` --- ### 关键注意点 - **安全与权限**：始终验证用户身份（如使用Session），防止未授权访问。引用[2]提到管理员可修改文件状态（如`status=1`禁用下载）[^2]。 - **性能优化**：对于大文件，优先使用“存储路径”方法。避免频繁BLOB操作以防数据库瓶颈。 - **私有化部署**：如引用[2]所述，系统可部署在自有服务器，替代公共网盘[^2]。 - **文件大小限制**：MySQL的BLOB字段有大小限制（LONGBLOB最大4GB），需配置PHP的`upload_max_filesize`。 ---
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值