河南海华工程建设监理公司网站,高端论坛网站建设,网站改版 总结,wordpress自动生成密码之前面试的时候#xff0c;由于在简历上提到优化sql代码#xff0c;老是会被问到sql索引和性能优化问题#xff0c;用这个帖子学习记录一下。
1.为什么要用索引
------------------------------------------------------------------------------------------------------…之前面试的时候由于在简历上提到优化sql代码老是会被问到sql索引和性能优化问题用这个帖子学习记录一下。
1.为什么要用索引
-------------------------------------------------------------------------------------------------------------------
想象一下你在一个超级大的图书馆里找一本书。图书馆有数百万本书而这些书按类别和作者名字乱七八糟地堆放在各个角落。你需要找一本特定的书比如《哈利·波特与魔法石》。如果没有任何排序规则你就只能从最前面一排一排地看书直到找到你要的书这样的查找过程非常慢对吧而且如果书很多查找起来就像大海捞针一样困难。
现在假设图书馆给每本书都加上了“书名索引”——它将每本书的书名按字母顺序排列成一张目录。这时如果你想找《哈利·波特与魔法石》你只需要翻看目录迅速定位到“H”的位置接着直接找到那本书速度快得多了。
这个目录就像数据库中的索引。
------------------------------------------------------------------------------------------------------------------
为什么用索引
1. 提高查询效率
没有索引的情况下数据库就像你在没有书名索引的图书馆里找书一样。查询时数据库需要全表扫描逐行检查每条记录像从头到尾翻看每本书一样。对于大表比如数据量上百万、上千万的表这种做法非常慢尤其在复杂的查询时效率低下。
举个例子
假设我们有一个包含百万条数据的表Customers 表每条数据记录一个客户的姓名、联系方式等信息。现在我们执行一个查询
SELECT * FROM Customers WHERE CustomerName John Doe;如果没有索引数据库就需要检查每一行数据查找匹配的记录。这就像在没有索引的图书馆里逐本翻书找《John Doe》的资料一样效率极低。
2. 通过索引加速查询
如果你为 CustomerName 列创建了索引就像为图书馆建立了一个目录表查找表所有的客户名字按字母顺序排列。当你查询 CustomerName John Doe 时数据库会直接跳到字母 J 的位置迅速定位到包含 John Doe 的记录而无需扫描整个表。
CREATE INDEX idx_customer_name ON Customers (CustomerName);当执行以下查询时
SELECT * FROM Customers WHERE CustomerName John Doe;
数据库就像翻开目录快速定位到John Doe的位置避免了逐行扫描所有数据查询速度大大提高。
3. 避免全表扫描的影响
没有索引时查询变得非常低效特别是对于大表。假设 Customers 表有上千万条记录每次查询时都需要扫描整个表执行的时间可能需要几秒钟甚至几分钟。而如果表有索引这个过程就像使用目录快速定位书籍数据库能在毫秒级时间内找到结果。
---------------------------------------------------------------------------------------------------------------------
2.sql索引原理和演化过程
首先是二分树法查找 普通的二叉树的缺点新的数据节点有可能一直插入同一边甚至形成一个链表 作为改进平衡二叉树出现了。 平衡二叉树的缺点是数据量增大的话这棵平衡二叉树就会变得很高一次查询需要多次IO操作。
然后是B树
B树相比于之前的二叉树一个节点可以存储多个数据并按序排列。而且B树也可以开超过2个的分叉使得这棵树更加扁平化也就需要更少的IO操作。有序排列的树配合二分法 B树也有一些缺点1.查找性能不够稳定 2.不适合做范围查找
作为B树增强版B树出现了
可以看到只有最下面一层节点存储数据之前上面的节点可以用来多存储指向其他节点的指针中间节点可以分更多叉整棵树变得更扁平也减少IO次数。
最后再把叶子节点用指针连接起来解决范围查询的问题。 --------------------------------------------------------------------------------------------------------------
3.索引与性能优化
之前说过了合理运用索引能提高查找效率下面是五个利用索引联系起性能优化的例子利用EXPLAIN关键字来看select语句的性能type。
type 等级的顺序从高到低是
const最优eq_refrefrangeindexALL最差
1对索引执行函数或计算会变成全表查找即typeall降低效率。 可以看到type是all最差的。 可以进行以下修改提高查找效率。 2联合索引排序不影响效率。 3索引前后顺序单独使用后索引是无效索引查询即typeall。 可以改一下复合索引顺序col2放前面。
4模糊匹配前面值确不确定影响是否是索引查询即typerange/all比如即ike语句的前导模糊查询不能使用索引。 5 查询条件增加结果精确但超出索引范围会using where。即索引覆盖效率快于回表查询。 ---------------------------------------------------------------------------------
4.外键相关
外键Foreign Key是数据库中的一种约束用于确保表与表之间的参照完整性。它确保从表中的某个字段值必须在主表中有对应的有效记录。
外键的作用
数据一致性外键保证从表中的数据引用主表中存在的有效数据防止无效或错误的数据被插入。表间关系通过外键表与表之间建立关联常见的一对多关系、父子关系等。防止孤立数据外键还可以设置级联操作确保删除或更新主表记录时从表的数据被适当处理。
外键的基本概念
主表包含唯一标识如主键的表。从表引用主表主键或唯一字段的表。
外键的约束
级联操作CASCADE删除或更新主表记录时自动删除或更新从表相关的记录。限制删除RESTRICT如果主表中的记录被引用拒绝删除主表记录。置空SET NULL删除或更新主表记录时将从表中相关的外键字段置为 NULL。
