sql的索引与性能优化相关

之前面试的时候，由于在简历上提到优化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 等级的顺序从高到低是：

1. const（最优）
2. eq_ref
3. ref
4. range
5. index
6. ALL（最差）

（1）对索引执行函数或计算会变成全表查找，即type=all降低效率。

 

可以看到type是all，最差的。 

 

可以进行以下修改，提高查找效率。

（2）联合索引，排序不影响效率。

 

（3）索引前后顺序，单独使用后索引是无效索引查询即type=all。

 

可以改一下复合索引顺序，col2放前面。

（4）模糊匹配，前面值确不确定影响是否是索引查询，即type=range/all，比如即ike语句的前导模糊查询不能使用索引。

 

（5） 查询条件增加，结果精确，但超出索引范围，会using where。即索引覆盖效率快于回表查询。

 

---------------------------------------------------------------------------------

4.外键相关

外键（Foreign Key）是数据库中的一种约束，用于确保表与表之间的参照完整性。它确保从表中的某个字段值必须在主表中有对应的有效记录。

外键的作用：

1. 数据一致性：外键保证从表中的数据引用主表中存在的有效数据，防止无效或错误的数据被插入。
2. 表间关系：通过外键，表与表之间建立关联，常见的一对多关系、父子关系等。
3. 防止孤立数据：外键还可以设置级联操作，确保删除或更新主表记录时，从表的数据被适当处理。

外键的基本概念：

• 主表：包含唯一标识（如主键）的表。
• 从表：引用主表主键或唯一字段的表。

外键的约束：

• 级联操作（CASCADE）：删除或更新主表记录时，自动删除或更新从表相关的记录。
• 限制删除（RESTRICT）：如果主表中的记录被引用，拒绝删除主表记录。
• 置空（SET NULL）：删除或更新主表记录时，将从表中相关的外键字段置为 NULL。