Sql优化的方案

最新推荐文章于 2024-09-06 16:33:30 发布

叫我罗少

最新推荐文章于 2024-09-06 16:33:30 发布

阅读量405

点赞数 11

文章标签： sql 数据库

本文链接：https://blog.csdn.net/2301_76600558/article/details/132655728

版权

1，Sql通用的优化方

2，索引原理

概念

索引是解决SQL性能问题的重要手段之一，使用索引可以帮助用户解决大多数的SQL性能问题。**索引就是数据结构，通过这种数据结构可以大大提高mysql的查询效率

二叉树

BTree

数据库中的数据按页存储，每页大小默认16KB,假如每个节点的元素由索引(主键索引-以bigint-8字节),指针域(6个字节),数据组成（比如1kb）,存储千万数据树高接近6;

计算流程：
一个节点存储的元素个数:16*1024/(8+6+1024)约等于15（也就是15叉树）
如果存储的数据占1kb，那么每个节点存储的元素个数是15个元素，那么如果存储1000w数据树高多少？6
那么如何进一步较低树高呢？

B+Tree

B+树是B树的变体,基本与BTree相同

特点

非叶子节点不存储data,只存储key,可以增大度
叶子节点不存储指针
顺序访问指针，提高区间访问能力

如果一个B+tree的树高时3的话，那么非叶子节点2层，叶子节点1层；

非叶子节点:16*1024/(8+6)=1170

非叶子节点两层：1170个元素，如果是两层，那么元素数量：1170*1170=1,368,900

叶子节点：因为包含索引+指针+数据 -----16*1024/(8+6+1024)=15

总共：1,368,900*15=20,533,500

B+Tree**索引的性能分析

一般使用磁盘I/O次数评价索引结构的优劣

B+Tree的度非常高，因此h非常小 (一般为3到5之间)，性能就会非常稳定
B+Tree叶子节点有顺序指针，更容易做范围查询

Hash

使用hash结构存储索引，查找单行数据很快，但缺点也很明显。

缺点：

1.无法用于排序
2.只支持等值查找
3.存在Hash冲突

Hash索引只适用于某些特定场景，我们使用不多

mysql的索引实现

show engines 可以查看当前数据库支持的引擎、默认的引擎

默认的引擎是innodb 可通过 SET default_storage_engine=< 存储引擎名 >更改
面试时经常被问到的两个引擎：
MyISAM 和 innoDB引擎这两种引擎都是采用B+Tree和hash 数据结构实现的索引

MyISAM 和 innoDB的对比

总的来说：
    需要事务：那肯定用innoDB
    不需要事务：
        myisam的查询效率高，内存要求低，但因为采用表锁，不适合并发写操作，读多写少选它
        innoDB采用行锁，适合处理并发写操作，写多读少选它

InnoDB索引实现

索引特点：
   采用B+Tree 作为数据结构
   数据文件本身就是索引文件 (聚簇索引)
   表数据文件本身就是按照B+Tree组织的一个索引结构文件
   聚集索引-叶节点包含了完整的数据记录
   非主键索引的叶子节点指向主键

索引的分类

0.主键索引：mysql中表必须维护一个B+tree索引树，如果在表中没有指定主键列，数据库会通过一个隐藏列作为索引字段构建B+tree
1.普通索引index :加速查找
create index idx_ on 表(字段)
2.唯一索引:
   主键索引：primary key ：加速查找+约束（不为空且唯一）
   唯一索引：unique：加速查找+约束（唯一）
3.联合索引（组合索引）--》联合主键索引联合唯一索引联合普通索引
create index a,b,c 最左原则
   最左匹配原则
   where A=? and B=? and C=?
create index A on 表(A,B,C)
   -primary key(id,name):联合主键索引
   -unique(id,name):联合唯一索引
   -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好(一般不推荐使用大文本字段，比如text)。