sql性能优化以及性能测试(建议收藏) 2021-07-06 02:11 11285阅读 1赞 笛卡尔连接; 例1; 没有携带on的条件字句,此条slq查询的结构集等价于,a表包含的条数*b表包含的乘积 ```` select * from table a cross join table b; ```` 例2; 拥有携带on字句的sql,等价于inner join ```` select * from table a cross join table b on a.id=b.id; ```` 分页limit的sql优化的几种方法 规则;表包含的数据较少的数据量,作为驱动表(小表驱动大表,一般mysql的优化器会做出相应的优化的,但是为了防止一些抽风现象可以用STRAIGHT_JOIN,作用会强制使用左边的表作为驱动表) 例1: ```` select * from table c straight_join table d on c.id=d.id; ```` 方案一:覆盖索引: ```` select 主键字段或者创建过索引的字段 from table limit 300000,10 ```` 方案二:索引覆盖+inner (业界常用的优化方案) ```` select * from table a inner join ( select 创建索引的字段 from table limit 30000,10) b on b.创建索引的字段=a.创建索引的字段 (也可以更换为 using (创建索引的字段)) ```` 方案三:索引覆盖+子查询 先获取分页起始的最小值,然后再获取后10条 (业界常用的优化方案) ```` select * from table where 主键字段或者创建过索引的字段 >= (select 主键字段或者创建过索引的字段 from table 300000,1) limit 10; ```` 方案四:范围查询+limit语句 获取上一页的主键最大值,然后进行获取后面的数据; 例1; 上一页的最大主键值为100 ```` select * from table where id > 100 limit 10; ```` 方案五: 需要获取起始主键值和结束主键值 ```` select * from table where id between 起始主键值 and 结束主键值; ```` 方案六: 禁止传入过大的页码 (例如;百度就是采用这种方式) count 优化方案总结 例1; ```` /** * 1:如果不包含非主键的索引,就会使用主键索引 * 2:如果包含非主键的索引就会使用非主键索引; * 3:如果存在多个非主键索引,会使用key_len值较小的索引 * 为什么会有这种规律呢? * -innodb非主键索引:叶子结点储存的是:索引+主键 * 主键索引叶子结点储存的是:主键+表数据 * 在1page里面,非主键索引可以存储更多的条目,对于一张表,假如拥有10000000数据 * 使用非主键索引,扫描page 500,主键索引 100 非主键索引扫描的条目多,可以减少扫描的次数 * **/ select count(*) from table ```` 例2: ```` /** * count(字段) 只会针对该字段进行统计,使用这个字段上的索引(如果包含索引的情况) * count(子段) 会排出字段值为null的数据 * count(*) 不会排出字段值为null的数据 * count(*) 和 count(1) 没有区别 * 对于MyISAM引擎,如果 count(*) 没有where条件,查询效率会特别的快,因为把数据存储到MyISAM引擎里了 * 对于MySQL 8.0.13,InnoDB引擎,如果count(*) 没有where条件查询速度,也是特别的快,做出了相应的优化 * * **/ select count(某个字段) from table 会把此字段的值为null过滤掉,仅仅只统计字段值不为null的 ```` 例3: ```` //做完本条查询,去执行count的操作 select sql_calc_found_rows * from table limit 0,10; select found_rows() as count ; 通过此sql来获取count的结果(须在终端进行执行) ```` 缺点在mysql8.0.17这种用法已经被废弃,未来会被永久删除 例4; //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景 ```` select * from information_schema.TABLES where TABLE_SCHEMA='数据库名称' and TABLE_NAME ='表的名称'; ```` 例5: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景 ```` show table status where NAME='表的名称隔行' ```` 例6: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景 ```` 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 的优化: 原则利用索引,避免排序 //first_name,last_name已经在表里创建了组合索引,emp_no为主键; 例1: //此sql是不能利用到索引的,原因是:mysql的优化器,是根据成本计算的,如果全表扫描比使用索引,成本更低时会使用全表扫描 //如何鉴定是否使用索引避免了排序呢? 通过explain 查看sql的性能如果Extra的值为null时,说明是可以通过索引避免排序的.如果Extra的值是Using filesort 是不可以进行索引排序的 select * from table order by first_name,last_name; //此sql可以使用索引避免排序的 select * from table order by first_name,last_name limit 10; //此sql可以使用索引避免排序的 /** *[Bader,last_name,emp_no] *[Bader,last_name,emp_no] *[Bader,last_name,emp_no] *[Bader,last_name,emp_no] * **/ select * from table where fist_name='Bader' order by last_name; //此sql可以使用索引避免排序的 /** *[Bader,last_name,emp_no] *[Ba,last_name,emp_no] *[Bad,last_name,emp_no] *[Bade,last_name,emp_no] * **/ select * from table where fist_name<'Bader' order by last_name //此sql可以使用索引避免排序的 select * from table where fist_name='Bader' and last_name>'Peng' order by last_name //此sql可以使用索引避免排序的,原因排序的俩个字段,分别存在俩个索引中 select * from table order by first_name,emp_no; 索引失效的场景: 1: join 字段的类型不一致 2: 在=号的左边,进行加减操作 3: 4: 需要添加索引的几种场景: 1: 2: 3: 4: 5: 阿里规约一般join的表数,最好不要超过三张表; 如果超过的话就要就行做相应的拆分. 例1: select * from employees e left join dept_emp de on e.emp_no=de.emp_no left join departments d on de.dept_no=d.dept_no where e.emp_no=1001; 拆分后: select * from employees where emp_no='1001'; select * from dept_emp where emp_no='1001'; select * from departments where dept_no='d005'; 表的设计原则; 三范式: 1范式:表的字段都是原子性,既每个表的字段都是不可分割的,不是集合,数组,记录等非原子数据项 2范式:在第一范式的基础上,每一行数据的唯一性,非主键字段要完全依赖于主键字段. 3范式:在满足第二范式的基础上,不能存在传递依赖 文章版权声明:注明蒲公英云原创文章,转载或复制请以超链接形式并注明出处。
相关 sql性能优化以及性能测试(建议收藏) 笛卡尔连接; 例1; 没有携带on的条件字句,此条slq查询的结构集等价于,a表包含的条数*b表包含的乘积 select * from table a cross jo... 朱雀/ 2021年07月06日 02:11/ 1 赞/ 11286 阅读
相关 SQL性能优化 你在项目中碰到过什么问题 你是怎么解决的 我的个人回答:之前在做货品管理项目的时候,涉及到进销存单据的查询,会遇到查询很慢,甚至查询失败的情况,我一般都会查阅自己写的SQ 爱被打了一巴掌/ 2021年09月22日 19:38/ 0 赞/ 658 阅读
相关 Web前端性能优化建议 1、HTML CSS JS位置 一般需要将CSS放页面最上面,即HEAD部分,而将JS代码放页面底部。因为页面需要加载为CSS才进行渲染,而JS如果不是在页面加载之前就要执行 今天药忘吃喽~/ 2021年11月23日 07:34/ 0 赞/ 555 阅读
相关 jQuery性能的优化建议 一直在寻找有关jQuery性能优化方面的小窍门,能让我那臃肿的动态网页应用变得轻便些。找了很多文章后,我决定将最好最常用的一些优化性能的建议列出来。我也做了一个jQuery性能 落日映苍穹つ/ 2022年03月28日 07:28/ 0 赞/ 426 阅读
相关 【Redis】性能优化建议 1.使用连接池 > 【推荐】 使用带有连接池的数据库,可以有效控制连接,同时提高效率,标准使用方式: JedisPoolConfig jedisPoolConf 浅浅的花香味﹌/ 2022年11月01日 15:00/ 0 赞/ 164 阅读
相关 7000字前端性能优化总结 | 干货建议收藏 为什么要做性能优化?性能优化到底有多重要? 网站的性能优化对于用户的留存率、转化率有很大的影响,所以对于前端开发来说性能优化能力也是重要的考察点。 性能优化的点非常的多 谁践踏了优雅/ 2022年11月14日 00:56/ 0 赞/ 259 阅读
相关 Spark性能优化总结(建议收藏) 近期优化了一个[spark流量统计的程序][spark],此程序跑5分钟小数据量日志不到5分钟,但相同的程序跑一天大数据量日志各种失败。经优化,使用160 vcores + 4 野性酷女/ 2022年12月02日 10:54/ 0 赞/ 264 阅读
相关 SQL性能优化(代码测试) 一、SQL语句结构尽量简单化,不宜有太多的嵌套 嵌套查询举例: ![在这里插入图片描述][2020090719202830.png_pic_center] 优化后 深藏阁楼爱情的钟/ 2022年12月04日 09:20/ 0 赞/ 185 阅读
相关 还不赶紧收藏 , SQL 性能优化梳理 微信关注公众号 “java大数据修炼之道”,选择“设为星标” 优质文章和精品资源, 第一时间送达 ![format_png][] 关注公众号后台回复资源获取实战教学资 布满荆棘的人生/ 2022年12月23日 07:24/ 0 赞/ 175 阅读
相关 〖实打实干货〗52条SQL语句性能优化策略,建议收藏 点击上方蓝色“java大数据修炼之道”, 选择“设为星标” 每晚九点: 技术干货 ???? 必定奉上![format_png][] ![format_png 1][] > 客官°小女子只卖身不卖艺/ 2023年01月01日 13:51/ 0 赞/ 80 阅读
还没有评论,来说两句吧...