我们做的网站是优化型结构,建设网站采用的网络技术,wordpress 定时插件,公众号开发是前端还是后端为什么 mysql 删了行记录#xff0c;反而磁盘空间没有减少#xff1f; 答#xff1a;
在 mysql 中#xff0c;当使用 delete 删除数据时#xff0c;mysql 会将删除的数据标记为已删除#xff0c;但是并不去磁盘上真正进行删除#xff0c;而是在需要使用这片存储空间时反而磁盘空间没有减少 答
在 mysql 中当使用 delete 删除数据时mysql 会将删除的数据标记为已删除但是并不去磁盘上真正进行删除而是在需要使用这片存储空间时再将其从磁盘上清理掉这是 MySQL 使用延迟清理的方式。
延迟清理的优点
如果 mysql 立即删除数据会导致磁盘上产生大量的碎片使用延迟清理可以减少磁盘碎片提高磁盘的读写效率如果删除数据时立即清理磁盘上的数据会消耗大量的性能。如果一个大表存在索引只删除其中一行整个索引结构就会发生变化
延迟清理的缺点
这些被标记为删除的数据就是数据空洞不仅浪费空间还影响查询效率。 mysql 是以数据页为单位来存储和读取数据如果一个表有大量的数据空洞那么 mysql 读取一个数据页可能被标记删除的数据就占据了大量的空间导致需要读取很多个数据页影响查询效率
如何回收未使用空间
optimize table 表名 索引的结构 答
索引是存储在引擎层而不是服务层所以不同存储引擎的索引的工作方式也不同我们只需要重点关注 InnoDB 存储引擎和 InnoDB 存储引擎中的索引实现以下如果没有特殊说明则都为 InnoDB 引擎。
mysql 支持两种索引结构 B-tree 和 HASH
B-tree 索引
B-tree 索引结构使用 B 树来进行实现结构如下图粉色区域存放索引数据白色区域存放下一级磁盘文件地址
B-tree 索引B 树实现的一些特点
B 树叶子节点之间按索引数据的大小顺序建立了双向链表指针适合按照范围查找使用 B 树非叶子节点只存储索引在 B 树中每个节点的索引和数据都在一起因此使用 B 树时通过一次磁盘 IO 拿到相同大小的存储页B 树可以比 B 树拿到的索引更多因此减少了磁盘 IO 的次数。B 树查询性能更稳定因为数据只保存在叶子节点每次查询数据磁盘 IO 的次数是稳定的 为什么索引能提高查询速度 答
索引可以让服务器快速定位到表的指定位置索引有以下几个优点
索引大大减少了服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机 IO 变为顺序 IO 前缀索引和索引的选择性 答
索引的选择性指的是不重复的索引值与数据表的记录总数的比值。
索引的选择性越高查询效率也越高因为选择性高的索引可以让 mysql 在查找时过滤掉更多的行。唯一索引的选择性是1这也是最好的索引选择性性能也是最好的
前缀索引
有时候为了提高索引的性能并且节省索引的空间只对字段的前一部分字符进行索引但是存在的缺点就是降低了索引的选择性
如何选择前缀索引的长度呢
前缀索引的长度选择我们要兼顾索引的选择性和存储索引的空间两个方面因此既不能太长也不能太短可以通过计算不同前缀索引长度的选择性找到最接近完整列的选择性的前缀长度通过以下 sql 进行计算不同前缀索引长度的选择性
select
count(distinct left(title, 6)) / count(*) as sel6,
count(distinct left(title, 7)) / count(*) as sel7,
count(distinct left(title, 8)) / count(*) as sel8,
count(distinct left(title, 9)) / count(*) as sel9,
count(distinct left(title, 10)) / count(*) as sel10,
count(distinct left(title, 11)) / count(*) as sel11,
count(distinct left(title, 12)) / count(*) as sel12,
count(distinct left(title, 13)) / count(*) as sel13,
count(distinct left(title, 14)) / count(*) as sel14,
count(distinct left(title, 15)) / count(*) as sel15,
count(distinct left(title, 16)) / count(*) as sel16,
count(distinct left(title, 17)) / count(*) as sel17,
count(distinct left(title, 18)) / count(*) as sel18,
count(distinct left(title, 19)) / count(*) as sel19,
count(distinct left(title, 20)) / count(*) as sel20,
count(distinct left(title, 21)) / count(*) as sel21
from interview_experience_article 计算结果如下
再计算完整列的选择性
select count(distinct title)/count(*) from interview_experience_article 计算结果如下 完整列的选择性是 0.6627而前缀索引在长度为 16 的时候选择性为sel160.6592就已经很接近完整列的选择性了此使再增加前缀索引的长度选择性的提升幅度就已经很小了因此在本例中可以选择前缀索引长度为 16
本例中的数据是随便找的一些文本数据类型是 text
如何创建前缀索引
alter table table_name add key(title(16))如何选择合适的索引顺序 答
来源于《高性能MySQL》第4版
对于选择合适的索引顺序来说有一条重要的经验法则将选择性最高的列放到索引的最前列
在通常境况下这条法则会有所帮助但是存在一些特殊情况
对于下面这个查询语句来说
select count(distinct threadId) as count_value
from message
where (groupId 10137) and (userId 1288826) and (anonymous 0)
order by priority desc, modifiedDate descexplain 的结果如下只列出使用了哪个索引
id: 1
key: ix_groupId_userId可以看出选择了索引groupId, userId看起来比较合理但是我们还没有考虑groupId、userId所匹配到的数据的行数
select count(*), sum(groupId10137), sum(userId1288826), sum(anonymous0)
from message结果如下
count(*): 4142217
sum(groupId10137): 4092654
sum(userId1288826): 1288496
sum(anonymous0): 4141934可以发现通过 groupId 和 userId 基本上没有筛选出来多少条数据
因此上边说的经验法则一般情况下都适用但是在特殊形况下可能会摧毁整个应用的性能
上边这种情况的出现是因为这些数据是从其他应用迁移过来的迁移的时候把所有的消息都赋予了管理组的用户因此导致这样查询出来的数据量非常大这个案例的解决情况是修改应用程序的代码区分这类特殊用户和组禁止针对这类用户和组执行这个查询 聚簇索引和非聚簇索引的区别非聚集索引一定回表查询吗 答
聚簇索引并不是一种单独的索引类型而是一种数据存储方式。
当表里有聚簇索引时它的数据行实际上存放在索引的叶子节点中。
聚簇表示数据行和相邻和键值存储在一起
InnoDB 根据主键来聚簇数据如果没有定义主键的话InnoDB 会隐式定义一个主键来作为聚簇索引
聚簇索引的优点
数据访问更快。聚簇索引将数据和索引保存在同一个 B-tree 中获取数据比非聚簇索引更快使用覆盖索引扫描的查询可以直接使用叶节点的主键值
聚簇索引的缺点
提升了 IO 密集型应用的性能。如果数据全部放在内存中的话不需要执行 IO 操作聚集索引就没有什么优势了插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到 InnoDB 表中最快的方式。 如果不是按照逐渐顺序加载数据在加载完之后最好使用 optimize table 重新组织一下表该操作会重建表。重建操作能更新索引统计数据并释放聚簇索引中的未使用的空间。 可以使用show table status like [table_name]查看优化前后表占用的存储空间更新聚集索引的代价很高。因为会强制 InnoDB 将每个被更新的行移动到新的位置基于聚簇索引的表在插入新行是或者主键被更新到只需要移动行的时候可能面临 页分裂 的问题当行的主键值需要插入某个已经满了的页中时存储引擎会将该页分裂成两个页面来存储也就是页分裂操作页分裂会导致表占用更多的磁盘空间聚簇索引可能会导致全表扫描变慢尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候二级索引也是非聚簇索引可能比想象的要更大因为在二级索引的叶子节点存储了指向行的主键列。二级索引访问需要两次索引查找而不是一次。 二级索引中叶子节点保存的是指向行的主键值那么如果通过二级索引进行查找找到二级索引的叶子节点会先获取对应数据的主键值然后再根据这个值去聚簇索引中查找对应的行数据。两次索引查找 二级索引是什么为什么已经有了聚集索引还需要使用二级索引 答
二级索引是非主键索引也是非聚集索引索引和数据分开存放也就是在非主键的字段上创建的索引就是二级索引。
比如我们给一张表里的 name 字段加了一个索引在插入数据的时候就会重新创建一棵 B 树在这棵 B 树中就来存放 name 的二级索引。
即在二级索引中索引是 name 值数据data存放的是主键的值第一次索引查找获取了主键值之后根据主键值再去聚集索引中进行第二次查找才可以找到对应的数据。
常见的二级索引
唯一索引普通索引前缀索引只适用于字符串类型的字段取字符串的前几位字符作为前缀索引。
为什么已经有了聚簇索引还需要使用二级索引 聚簇索引的叶子节点存储了完整的数据而二级索引只存储了主键值因此二级索引更节省空间。
如果需要为表建立多个索引的话都是用聚簇索引的话将占用大量的存储空间。 为什么在 InnoDB 表中按逐渐顺序插入行速度更快呢 答
向表里插入数据主键可以选择整数自增 ID 或者 UUID。
如果选择自增 ID 作为主键
那么在向表中插入数据时插入的每一条新数据都在上一条数据的后边当达到页的最大填充因子InnoDB 默认的最大填充因子是页大小的 15/16留出部分空间用于以后修改时下一条记录就会被写入到新的页中。
如果选择 UUID 作为主键
在插入数据时由于新插入的数据的主键的不一定比之前的大所以 InnoDB 需要为新插入的数据找到一个合适的位置——通常是已有数据的中间位置有以下缺点
写入的目标也可能已经刷到磁盘上并从内存中删除或者还没有被加载到内存中那么 InnoDB 在插入之前需要先将目标页读取到内存中。这会导致大量随机 IO写入数据是乱序的所以 InnoDB 会频繁执行页分裂操作由于频繁的页分裂页会变得稀疏并且被不规则地填充最终数据会有碎片
什么时候使用自增 ID 作为主键反而更糟
在高并发地工作负载中并发插入可能导致间隙锁竞争。
