SQL优化方法

索引优化:

索引数据结构:

二叉树: 在有序的数据下(1,2,3,…), 可能会变成链表, 没有起到优化作用, 还会占用空间

红黑树: 也叫二叉平衡树, 层级太高, 数据量小可以, 大就不行了

Hash表: 不方便范围查询, 等值查询效率确实高

B-Tree: 不方便范围查询, 每次查询都需要从根节点开始

B+Tree: 优化的B-Tree

1.将数据存储到了叶子节点, 并且是按顺序排序, 数据之间 通过双向链表连接

2.非叶子节点仅存储键值(索引), 没有数据, io次数变少, 查询性能变高

InnoDB----mysql的存储引擎

没有主键索引, mysql的InnoDB会使用一个隐藏的, 自动生成的主键来创建索引

这个隐藏的主键索引就是使用B+树的结构

聚合索引和非聚合索引:

聚合索引 --- InnoDB存储方式 frm 表结构, ibd 索引+数据

叶子节点上存放的是数据

非聚合索引 --- MyISAM存储方式 frm 表结构, MYD表数据, MYI表索引

叶子节点上存放的是存储指针(内存地址)

二级索引: 就是非聚集索引,

所有非主键索引都可以称之为二级索引,

除了聚集索引都称之为二级索引.

回表:

Select * from 表 where age = 35

会有二级索引(年龄) 查询,  该索引的叶子节点存储的是主键

查询到年龄为35的主键,

再返回到聚合索引(主键)中根据主键进行查询

覆盖索引:

Select id from 表 where age = 35

这样就不需要回表了

--> 表示只查询用到的数据就可以, 可以提高性能

索引下推:

扫描二级索引优化, 返回查询时, 减少回表次数

单列索引: 索引关联的列

联合索引: 多个索引(从左往右)--> 最左前缀原则: 先满足第一个索引, 再满足第二个, 以此类推..

(条件不可以跳, 当跳过了第一个, 后面相当于都是无序的, 因为后面所有的顺序都是基于第一个索引的)

优势:

减少开销: 三个索引三个树, 联合索引一个树, 减少空间开销

覆盖索引: 减少回表操作--> 减少返回不必要的数据

效率高:

索引优缺点:

优点:

提高查询效率

降低排序成本

缺点:

创建和维护索引需要耗费时间, 随着数据量的变化而变化

需要占用空间

降低表的增删改的效率, 每次修改表都需要动态维护

适用:

频繁作为查询条件的字段则创建索引

不适用:

字段值唯一性差

更新频繁的字段

不会出现在where条件中的字段

索引失效 --> 优化:

1.没有遵循最左前缀原则

--> 开头索引必须有, 中间索引不能落下(最左前缀原则)

2.使用函数, 计算, 类型转换(字符串不加引号)

--> 使用范围查询, like, 尽量使用后模糊, 前模糊会全表扫描, 非要用则强制使用索引 [force index(索引名称)]

3.范围之后失效

-->

4.不等, 空值, or, 比较运算符, not in, not exists 时可能导致索引失效, 进行全盘扫描

--> 强制使用索引

5.  --> 减少select语句, 不写*,  写覆盖索引(单列)

一般数据量大的表用索引, 效率会更明显

sql优化:

1.避免使用select *

说明: 需要哪些字段必须明确写明

使用select  * 的缺点:

增加查询分析器解析成本

无用字段增加网络消耗, 尤其是text类型的字段

2.小表驱动大表 (一对多)

说明: 指用数据量较小 索引比较完备的表, 使用该索引和条件多数据量大的表进行查询

3.使用连接查询代替子查询(并不是绝对)

4.使用 group by 字段A , 建议创建字段A的索引, 可以提高效率

说明: 因为group by是先排序后分组, 创建了索引则提前排好序, 直接往后查快很多

5.插入多条数据时, 建议使用动态sql --> 批量插入 (只需要进行一次io就可以)

6.多使用limit 限制查询数量 需要限制分页数量, 不然会性能降低, 如果非要分页数量多,则下面3中优化

Select * from 表 where limit 10000,10; 需要先分页查询一直查询到10000条数据, 效率较低

优化1: Select * from 表 where (id >= 10000) limit 10; 直接锁定到第10000条数据, 较快

优化2: select * from 表 where id in (子查询只查符合条件的id) limit 10; 利用id索引只查id 效率大大提高, 不适合in中数量过多

优化3:  select * from 表 inner join (子查询只查符合条件的id)

7.union all 代替 union

union all 获取所有数据不去重, 有重复数据;

union 获取所有数据并去重, 没有重复数据;

涉及到去重, 底层会进行一系列计算比较

非要用去重那么建议使用索引

8.join表不宜过多

导致系统负载增加, 维护难度大