sql优化

最新推荐文章于 2024-10-04 22:00:00 发布

qq_37985878

最新推荐文章于 2024-10-04 22:00:00 发布

阅读量96

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/qq_37985878/article/details/105472646

版权

sql 优化

explain 展示的type是all的情况，和extra是Using filesort(没使用索引排序，使用磁盘排序),Useing templ..（没有用索引，生成了虚拟表）,Useing where（没用用到索引）需要优化

sql优化就是添加索引，查询的时候使用索引而不使索引失效，

force index 强制走索引


1.单个建立索引会影响内存空间，建议使用联合索引
2.联合索引：最左前缀法则
3.如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
4.不再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
5.针对于联合索引，范围索引查找之后的索引都会失效，比如 name = 'lili' and age > 20 and title = 'home'
6.尽量使用覆盖索引，不使用select *,覆盖索引就是select后面的字段要全是索引字段
7.mysql在使用不等于（!= 或 <>） 的使用无法使用索引导致全表扫描
8.is null，is not null 也无法使用索引
9.like以通配符开头（'%li'）mysql索引会失效
10.字符串不加单引号索引失效，因为mysql会使用case函数转型
11.少用in或者or，用它查询时，mysql不一定会使用索引，msyql内部优化器会根据检索比例，表大小等多个因素整理评估是否使用索引
12.当我们执行sql的时候mysql也会优化我们的sql，看是否走索引，msyql内存sql优化器trace
13.联合索引用order by，order by的时候也一定要使用遵循最左前缀法则，即where A = '1' ordery by C,这就没用用到索引，因为B这里断了，C就不能进行排序
14.联合索引使用order by排序的时候位置不能乱，如果乱了就不会使用索引
15.group by 和 order by 优化原则一样
16.分页优化
limit 9000,5 其实是找出了9005条，去掉了前面的9000条，留下了5条，这个往后越来越慢
解决思路：select * from A a join (Select id from A limit 9000,10) as on a.id = as.id

17.关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接sql时如果明确知道那张表是小表可以用straight_join写法固定连接驱动方式，省去msyql优化器自己判断的事件，大小表是参与计算的数据，而不是表中全部的数据
18.in
select * from A where id in ( select id from B)

		等价于
		for(select id from B ){
			select * from A  where A.id = B.id
		}
	当B表的数据集小于A表的数据集时，in优先于exists

19.exists
exists会先执行select * from A 然后拿结果集去B里面匹配每一行如果有返回true，如果返回true那么就把这一行的数据放到最终的结果集中

	select * from A  where exists (select 1 from B  where A.id = A.id)

Useing filesort文件排序原理详解

单路排序：是一次性取出满足条件的所有字段，然后在sort buffer中进行排序，
双路排序：是首选根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID（主键id）,然后在sort buffer中进行排序，排完序后需要再次取回其他需要的字段（主键id对应的值）

mysql通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序方式

如果比查询字段的总长度大，那么使用单路排序模式
如果比查询字段的总长度小，那么使用双路排序模式

如果内存大用单利，否则用双路，很扯淡，useing filesort尽量优化成Using index

jon底层原理

1.嵌套循环连接Nested-Loop-Join(NLJ)算法

	一次一行循环的从第一张表（驱动表，小表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表，大表）里取出满足条件的行，
	然后取出两张表的结果集，如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join 算法

2.基于块的嵌套循环连接Block Nested-Loop join(BNL)算法

		把驱动表的数据读到Join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟Join_buffer中的数据做对比。由于驱动表和被驱动表都没有索引，索引该关联查询的扫描次数为
		驱动表*被驱动表，
		
		因此mysql对于被驱动表的关联字段没有索引的关联查询，一般都会使用BNL算法，如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能高