sql性能优化以及性能测试

快来打我* 2024-04-07 14:17 122阅读 0赞

笛卡尔连接;  
例1; 没有携带on的条件字句,此条slq查询的结构集等价于,a表包含的条数\*b表包含的乘积

> 1.  `select * from table a cross join table b;`

例2; 拥有携带on字句的sql,等价于inner join

> 1.  `select * from table a cross join table b on a.id=b.id;`

分页limit的sql优化的几种方法  
规则;表包含的数据较少的数据量,作为驱动表(小表驱动大表,一般mysql的优化器会做出相应的优化的,但是为了防止一些抽风现象可以用STRAIGHT\_JOIN,作用会强制使用左边的表作为驱动表)  
例1:

> 1.  `select * from table c straight_join table d on c.id=d.id;`

方案一:覆盖索引:

> 1.  `select 主键字段或者创建过索引的字段 from table limit 300000,10`

方案二:索引覆盖+inner (业界常用的优化方案)

> 1.  `select * from table a`
> 2.  `inner join (`
> 3.  `select 创建索引的字段 from table limit 30000,10) b`
> 4.  `on b.创建索引的字段=a.创建索引的字段 (也可以更换为 using (创建索引的字段))`

方案三:索引覆盖+子查询 先获取分页起始的最小值,然后再获取后10条 (业界常用的优化方案)

> 1.  `select * from table`
> 2.  `where 主键字段或者创建过索引的字段`
> 3.  `>=`
> 4.  `(select 主键字段或者创建过索引的字段 from table 300000,1)`
> 5.  `limit 10;`

方案四:范围查询+limit语句 获取上一页的主键最大值,然后进行获取后面的数据;

例1; 上一页的最大主键值为100

> 1.  `select * from table`
> 2.  `where id > 100`
> 3.  `limit 10;`

方案五: 需要获取起始主键值和结束主键值

> 1.  `select * from table`
> 2.  `where id between 起始主键值 and 结束主键值;`

方案六: 禁止传入过大的页码 (例如;百度就是采用这种方式)

count 优化方案总结

例1;

> 1.  `/**`
> 2.  `* 1：如果不包含非主键的索引，就会使用主键索引`
> 3.  `* 2：如果包含非主键的索引就会使用非主键索引；`
> 4.  `* 3：如果存在多个非主键索引，会使用key_len值较小的索引`
> 5.  `* 为什么会有这种规律呢？`
> 6.  `* -innodb非主键索引：叶子结点储存的是：索引+主键`
> 7.  `* 主键索引叶子结点储存的是：主键+表数据`
> 8.  `* 在1page里面，非主键索引可以存储更多的条目，对于一张表，假如拥有10000000数据`
> 9.  `* 使用非主键索引，扫描page 500，主键索引 100 非主键索引扫描的条目多，可以减少扫描的次数`
> 10. `*`
> 11. `**/`
> 12. `select count(*) from table`

例2:

> 1.  `/**`
> 2.  `* count(字段) 只会针对该字段进行统计，使用这个字段上的索引（如果包含索引的情况）`
> 3.  `* count(子段) 会排出字段值为null的数据`
> 4.  `* count(*) 不会排出字段值为null的数据`
> 5.  `* count(*) 和 count(1) 没有区别`
> 6.  `* 对于MyISAM引擎，如果 count(*) 没有where条件，查询效率会特别的快，因为把数据存储到MyISAM引擎里了`
> 7.  `* 对于MySQL 8.0.13，InnoDB引擎，如果count(*) 没有where条件查询速度，也是特别的快，做出了相应的优化`
> 8.  `*`
> 9.  `*`
> 10. `**/`
> 11. `select count(某个字段) from table 会把此字段的值为null过滤掉,仅仅只统计字段值不为null的`

例3:

> 1.  `//做完本条查询,去执行count的操作`
> 2.  
> 3.  `select sql_calc_found_rows * from table limit 0,10;`
> 4.  `select found_rows() as count ; 通过此sql来获取count的结果(须在终端进行执行)`

> 1.  `缺点在mysql8.0.17这种用法已经被废弃,未来会被永久删除`

例4; //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景

> 1.  `select * from information_schema.TABLES`
> 2.  `where`
> 3.  `TABLE_SCHEMA='数据库名称'`
> 4.  `and`
> 5.  `TABLE_NAME ='表的名称';`

例5: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景

> 1.  `show table status where NAME='表的名称隔行'`

例6: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景

> 1.  `explain select * from table`

例7: 优化案例; 目前有一张数量非常大的表,需要统计id值大于100的有多少条

一般写法: select count(\*) from table where id>100;

mysql8.18版: 逆向思维的写法: select count(*)-(select count(*) from table where id <100) from table

order by 的优化: 原则利用索引,避免排序

> 1.  `//first_name,last_name已经在表里创建了组合索引,emp_no为主键;`

例1:

> 1.  `//此sql是不能利用到索引的,原因是:mysql的优化器,是根据成本计算的,如果全表扫描比使用索引,成本更低时会使用全表扫描`
> 2.  `//如何鉴定是否使用索引避免了排序呢? 通过explain 查看sql的性能如果Extra的值为null时,说明是可以通过索引避免排序的.如果Extra的值是Using filesort 是不可以进行索引排序的`
> 3.  `select * from table order by first_name,last_name;`
> 4.  
> 5.  `//此sql可以使用索引避免排序的`
> 6.  `select * from table order by first_name,last_name limit 10;`
> 7.  
> 8.  `//此sql可以使用索引避免排序的`
> 9.  `/**`
> 10. `*[Bader,last_name,emp_no]`
> 11. `*[Bader,last_name,emp_no]`
> 12. `*[Bader,last_name,emp_no]`
> 13. `*[Bader,last_name,emp_no]`
> 14. `*`
> 15. `**/`
> 16. `select * from table where fist_name='Bader' order by last_name;`
> 17. 
> 18. 
> 19. 
> 20. `//此sql可以使用索引避免排序的`
> 21. `/**`
> 22. `*[Bader,last_name,emp_no]`
> 23. `*[Ba,last_name,emp_no]`
> 24. `*[Bad,last_name,emp_no]`
> 25. `*[Bade,last_name,emp_no]`
> 26. `*`
> 27. `**/`
> 28. `select * from table where fist_name<'Bader' order by last_name`
> 29. 
> 30. 
> 31. `//此sql可以使用索引避免排序的`
> 32. `select * from table where fist_name='Bader' and last_name>'Peng' order by last_name`
> 33. 
> 34. 
> 35. `//此sql可以使用索引避免排序的,原因排序的俩个字段,分别存在俩个索引中`
> 36. `select * from table order by first_name,emp_no;`

索引失效的场景:

1: join 字段的类型不一致

2: 在=号的左边,进行加减操作

需要添加索引的几种场景:

阿里规约一般join的表数,最好不要超过三张表; 如果超过的话就要就行做相应的拆分.

例1:

> 1.  `select * from employees e`
> 2.  `left join dept_emp de on e.emp_no=de.emp_no`
> 3.  `left join departments d on de.dept_no=d.dept_no`
> 4.  `where e.emp_no=1001;`

拆分后:

> 1.  `select * from employees where emp_no='1001';`
> 2.  `select * from dept_emp where emp_no='1001';`
> 3.  `select * from departments where dept_no='d005';`

表的设计原则;  
三范式:  
1范式:表的字段都是原子性,既每个表的字段都是不可分割的,不是集合,数组,记录等非原子数据项  
2范式:在第一范式的基础上,每一行数据的唯一性,非主键字段要完全依赖于主键字段.  
3范式:在满足第二范式的基础上,不能存在传递依赖