当前位置：首页 > news >正文

上海网站制作机构中企动力做的网站怎么登陆

news 2025/9/17 6:44:11

上海网站制作机构,中企动力做的网站怎么登陆,网站开发计什么科目,东莞企业网站咨询掌握高性能SQL的34个秘诀#x1f680;多维度优化与全方位指南本篇文章从数据库表结构设计、索引、使用等多个维度总结出高性能SQL的34个秘诀#xff0c;助你轻松掌握高性能SQL 表结构设计字段类型越小越好满足业务需求的同时字段类型越小越好字段类型越小代表着记录占…掌握高性能SQL的34个秘诀多维度优化与全方位指南本篇文章从数据库表结构设计、索引、使用等多个维度总结出高性能SQL的34个秘诀助你轻松掌握高性能SQL 表结构设计字段类型越小越好满足业务需求的同时字段类型越小越好字段类型越小代表着记录占用空间可能就越小页中存在的记录就可能越多相同IO次数加载的数据就可能更多字段越小建立索引时耗费的空间就越小如果该字段是主键那么它还会在二级索引上存储因此主键也是越小越好数字类型的选择数字类型包括整形、浮点型、定点数类型在不同的场景下可以选择不同的类型整形整形通常是tinyint ~ bigint 根据越小越好的原则对于存储枚举类型的字段使用tinyint进行存储0-x而不必使用字符串进行存储 int(1) 和 int(10) 占用的空间无区别只是int(10)在数据不满10位时进行补零善用无符号 UNSIGNED 可以提高一倍的容量比如一些不需要负数的场景主键、人的年龄 tinyint unsigned 255够用浮点型浮点型常用于存储有小数部分的数据其中包括float、double类型注意使用浮点数类型时可能发生精度丢失如果不想丢失精度可以选择定点数类型定点数类型 decimal 常用于存储有小数、需要计算且不能发生精度丢失的数据字符类型的选择常用的字符类型有char和varchar char存储固定字符当存储字符长度未满时使用空格填充因此它无法存储末尾空格在修改时它能够在原记录上进行修改 varchar相当于char空间换时间的版本它是可变长字段会多使用1-2个字节记录可变长长度 varchar(255)前1个字节255后2个字节但也不是长度不超过255就全部都使用255在某些存储引擎下会根据长度直接分配空间如memory使用临时表默认使用memory因此在临时表排序时可能会导致占用空间太多 varchar在面对频繁的修改时还可能造成重建记录、页分裂等问题固定长度、频繁的修改可以选择char 不定长、末尾要存储空格时可以选择varcharvarchar长度也要尽量小注意存表情使用utf8mb4字符编码在某些场景下整形替换字符存储会更省空间也可以考虑整形比如存储IP 具体内容感兴趣的同学可以查看这篇文章千万数据下varchar和char性能竟然相差30% 时间类型的选择根据越小越好原则只需要年、日期、时间时选择year、date、time 需要详细日期时可以选择datetime和时间戳的方式 datetime固定时间、无时区、可视化较好 timestamp时间戳有时区根据服务端时区、有时间范围限制、使用系统时区并发下性能没那么好、可视化不好使用整形存储时间戳性能好可以自由转换时区可视化不好不考虑时区、可视化要好大部分场景下可以使用datetime 考虑时区需要自由转换时区、追求性能、不注重可视化可以选择整形存储时间戳无符号int 目前够用具体内容感兴趣的同学可以查看这篇文章时间类型该如何选择千万数据下性能提升10%~30% 文本、文件类型的选择文本相关可以选择TEXT相关类型使用时最好与常用列进行垂直拆分避免内容太多影响其他列的查询文件相关可以存储到文件服务器后在数据库中使用字符类型varchar进行存储文件所在地址如果一定要存则使用BLOB相关类型存储二进制数据尽量满足主键递增主键最好考虑是递增的因为聚簇索引需要保证主键值的有序当主键递增时只需要在末尾增加记录即可当入库的主键值无序时可能会导致页分裂需要维护有序性的开销单机下可以使用主键自增分布式下可以使用全局自增的算法雪花算法等考虑打破范式增加冗余一般表结构的设计是遵循三范式的在某些场景下可以给一些需要关联的表增加冗余从而避免联表查询比如一条记录是由某个设备生成的该记录肯定需要保留字段去关联设备需求是知道该记录由哪个设备生成的即可因此查询时只需要关联查询设备名称而设备名称又不会经常改变对于追求性能的场景可以将设备名称冗余存储在记录上从而避免联表查询计算量太大考虑中间表对于需要大量计算的场景比如统计数据、每日排行榜等每次查询都经过大量计算来统计数据是不现实的通过增加中间表的方式先进行统计后续查询时直接查中间表比如定时任务统计每天数据量、每日排行计算后将结果不同类型的数据量、排行榜TOP100记录在中间表上后续有请求则直接查中间表索引为常用于查询的列建立索引索引带来的好处是在大数据量下能够快速检索到满足查询条件的记录索引会根据选择的列构建成一颗索引列有序的B树比如根据agestudent_name建立索引索引(agestudent_name)中只存储索引列(age、student_name)和主键(id) 并且索引中需要把age、student_name、id维护成有序整体上age有序age相等时student_name有序student_name相等时id有序列有较多where条件查询的语句时考虑为其建立索引为常要排序order by、group by列创建索引索引会维护列的有序性为 order by 的列建立索引时在索引上列本身就是保持有序的不会再使用临时表进行排序 group by 也会进行排序使用索引的好处同上在其基础上还会进行去重如果无法创建索引会使用sort buffer进行排序可以考虑调大sort buffer加快速度如果数据量太大的排序还会借助磁盘辅助排序这种场景下最好还是建立索引对排序感兴趣的同学可以查看这篇文章怎样处理排序⭐️如何优化需要排序的查询考虑为联表查询中被驱动表关联列适当建立索引在联表查询中关联的表越多时间复杂度会呈指数型增长其中每访问一次驱动表就可能访问多次被驱动表需要适当为被驱动表关联列建立索引加快查询被驱动表的速度 SELECTs1.*,s2.seat_code FROMstudent s1left JOIN seat s2 ON s1.id s2.student_id WHERE s1.student_name caicai 如这段SQL中s1使用左连接为驱动表s2为被驱动表 s1使用(student_name)索引s2暂时没索引可以考虑为s2的student_id建立索引由于只查询s2的seat_code也可以考虑建立(student_id,seat_code)联合索引使用覆盖索引避免查s2时回表再查seat_code 对连表查询感兴趣的同学可以查看这篇文章连接的原理⭐️4种优化连接的手段性能提升240% 考虑为字符串长度太长、开头能够区分的列建立前缀索引为太长的字符串列直接建立索引时会导致占用空间太大当列中存储的值前面部分为区别度较高的值时可以考虑为其建立前缀索引例如某产品编码长度20其中后面15个字符重复性很高前5个字符重复性低区分度高就可以考虑为前5个字符建立前缀索引需要注意的是前缀索引只存储该列前缀部分的值如果要获取列的完整信息就要进行回表列中重复值太多不建议建立索引当列中重复值太多时它在查询时的区分度不够其次在使用该索引时重复值太多cardinality太低如果要回表MySQL会认为回表开销太大重复值多、回表数量多从而导致它不偏向使用该索引回表开销回表需要查询聚簇索引由于二级索引中的主键值不一定有序因此回表时可能产生随机IO 业务唯一要加唯一索引业务上有唯一性的要求时要加唯一索引唯一索引的特点是记录唯一在进行写操作时需要保证记录唯一性不能使用change buffer等优化在频繁写的场景下性能会比非唯一二级索引略差 change buffer当索引页不在缓冲池时记录下本次写操作的内容等后续读到该记录时再将内容合并加载到缓冲池避免写的随机IO) 但在查询时唯一索引等值查询会比非唯一索引更快因为它不允许重复值而非唯一索引存在重复值在业务层通过先读再新增的方式保证唯一时在并发场景下还是会出现重复值除非读加锁但是加锁又会影响性能.... 不能因为唯一索引无法使用change buffer的优化就不使用唯一索引避免创建过多索引创建索引是需要考虑成本的并不是索引越多越好索引需要占用空间在进行写增/删/改操作时还要维护索引的有序性在进行查询时优化器还要基于使用不同的索引对成本进行估算避免冗余索引当存在(name)、age、(name,age)三个索引时(name)就成为了冗余索引因为使用(name)索引的好处(查询条件过滤、有序)使用(name,age)也可以达到需要注意的是如果查询SQL中没有age单独查询的where age 18都是基于先查name再查询age的where name caicai and age 18那么age也相当于冗余索引因为这种场景下使用(name,age)就足够注意左模糊匹配字符串的二级索引是根据该列字符排序规则进行排序当使用左模糊匹配like %xx时由于起始字符不确定导致不便在二级索引中进行检索对于这种场景如果数据量小考虑建立全文索引进行检索如果数据量大考虑使用其他善于全文检索的中间件如ES等MySQL全文索引耗内存注意最左匹配原则当使用联合索引时需要前一个索引列等值的情况下后一个索引列才会有序比如(a,b,c)中当a相等时b才有序当b相等时c才有序 where b9 时无法使用联合索引因为b不一定是有序的只有当a相等时b才有序 where a1 and b9中可以使用上索引中a、b两个列 where a1 and c9中只能使用上索引中的a由于b没有查询条件导致c不一定有序于是c无法使用索引但是在8.0高版本中推出索引跳跃扫描的优化在where a1 and c9中无法使用c的原因是c不是有序想要c有序就要让b相等于是索引跳跃扫描在这种场景下将遍历a1中有序的b由于b可能重复于是会对b去重在此基础上c就是有序的就能够使用上索引最后将每个遍历的b中满足c9的记录进行合并从而得到最终结果虽然有索引跳跃扫描的优化但开销还是大的需要优化注意表达式或隐式函数索引列不要使用表达式比如where age 2 10存储引擎层使用age索引时不认识age 2就会导致索引失效同理索引列也不能使用函数CAST(age AS CHAR) 8也会导致索引失效需要注意有时容易隐式给索引列加函数导致索引失效 code 10 code为字符串字符串会隐式使用函数向数字转换 CAST(code AS UNSIGNED) 10 从而导致索引失效对索引失效感兴趣的同学可以查看这篇文章完蛋我被MySQL索引失效包围了注意回表当使用二级索引时如果使用的查询条件不够有区别度is null、is not null、orNULL 默认情况下被认为重复值又或者该重复值太多cardinality太低都会导致MySQL认为要回表的记录太多从而不偏向使用索引导致索引失效注意优化器可能用错索引优化器会估算计算每个索引的成本当扫描数据量较大并且更新数据太频繁时会影响计算的成本从而导致优化器使用错索引这种情况下可以在空闲时手动更新统计 analyze table 或者强制使用索引 force index 使用避免select * select * 方便书写SQL易于偷懒虽然平时的开发中也会用到但是要知道它会带来开销: 占用网络带宽读取不必要的列通过网络返回给客户端数据量大的情况下是一笔不小的开销无法使用覆盖索引使用二级索引时会回表如果需要的列正好都在二级索引上那么就可以使用覆盖索引不用回表联表查询使用join buffer时会占用join buffer的空间join buffer是联表查询被驱动表无法使用索引时的优化占用其空间会导致联表性能下降常用explain 每次在书写业务的SQL时可以使用explain查看执行计划根据业务需求、执行计划判断该SQL是否满足当前场景的性能要求 explain中需要注意的几部分 type 避免出现全表扫描ALL最好使用const、ref、range等possible_keys 可能用到的索引和 key 实际用到的索引注意查看优化器是否选错索引联表查询时注意查看key_len 使用索引长度避免部分索引列未使用到注意附加信息extra中的排序、临时表等查询时少用is null、is not null、or、!... null默认被认为重复值is null、is not null、or、!会被认为重复值太多当重复值太多回表开销大MySQL会不偏向使用索引导致索引失效注意联表性能注意联表查询的时间复杂度是呈指数形式增长的联表越多性能越差但是有的B端又必须进行联表查询提供以下几点方案优化联表适当为被驱动表关联列建立索引如果使用索引后随机IO关联被驱动表是瓶颈考虑开启BKA无法建立索引的情况会使用join buffer优化尝试调大join buffer空间或减少select查询的列使用小表驱动大表能用内连接就用内连接让MySQL选择驱动表不经常变化的列做冗余避免联表统计全部数量尽量使用count(*) 在统计数量时都会使用count函数 count(主键/1/*)都会基于空间最小的二级索引进行统计统计快全局数量统计时尽量使用count(主键)/count(1)/count(*)等不要使用count(二级索引列)可能当初该列的索引确实是空间最小的但后面还可能建立比它空间更小的二级索引除非是指定统计该列行数 count(*)是定义的数据库标准统计行数的语法虽然几个写法使用起来都差不多但它会规范些注意优化深分页深分页问题是由于分页偏移量太大导致的问题 select * from student where age 18 limit 5000,10 使用二级索引 age 偏移量太多要回表的数据量太大导致索引失效可以使用以下六种方式优化深分页业务需求沟通避免出现深分页使用覆盖索引优化使用游标分页使用子查询使用in、子查询使用内连接、子查询对深分页感兴趣的同学可以查看这篇文章深分页怎么导致索引失效了提供6种优化的方案读写善用limit 查询时携带limit可以更快的返回结果避免额外的查询比如我只需要查询一条记录时limit 1不是指limit 10000,1 这种深分页哈在写操作(修改/删除)时携带limit会限制写的行数避免误操作数据数据量小且要查多次考虑冗余查询对于一些数据量小但是又要多次查询的场景可以考虑Java服务先冗余查询再进行处理避免多次查询的网络IO开销比如一些权限的树级目录无论是通过队列来广度优先搜索还是递归来深度优先搜索都需要多次查库频繁写考虑批处理客户端频繁的进行单次修改/删除/新增的操作不仅有网络IO开销还耗损MySQL服务端资源、无法使用批处理优化这种场景下调整为批量处理可以节约资源增大性能比如一些异步日志记录需要入库但又会频繁触发可以考虑改为异步的批量入库需要注意如果批处理操作中的数量很多考虑分批处理每批处理一部分避免成为长事务避免出现长事务在使用spring的声明式事务时用的很爽但稍微不注意就可能导致长事务比如一些没必要存在事务中的读操作或者在同一个事务中先进行写操作然后又去读数据一顿操作后才提交事务这可能导致写操作获取的行锁由于后续的读操作拉长事务导致获取锁的时间变长又或者一些读大量数据、写大量数据的操作可以将整个长事务拆分为多个小事务进行处理考虑事务中写操作执行顺序平台上有1W积分用户领取积分时是先对平台的积分进行扣减还是先对用户持有积分进行增加呢在对于事务中写操作的执行顺序应该让共享、竞争更大的资源靠后执行提交事务前尽可能的缩短它持有资源的时间应该把平台扣减积分放在提交事务前因为平台积分相当于共享资源大家都可以领取扣减考虑调整事务隔离级别 MySQL默认的事务隔离级别为RR可重复读在该隔离级别下能够防止脏读、不可重复读、大部分幻读但加的行锁和持有时间会比RC读已提交级别下要多和更久因此当业务只需要满足防止脏读的情况下可以调整隔离级别为RC增大并发性能具体加锁规则后续文章再进行讨论注意调整架构当业务上使用缓存、异步等多种优化手段后对于一些需要实时读写的操作还是要走DB而此时DB面对大量这种操作可能产生瓶颈当DB遇到瓶颈时我们需要分析清楚瓶颈并规划DB的架构比如瓶颈是由于并发量大连接池不够还是数据量太多查询太慢可以先将架构规划为读写分离架构从节点分摊主节点的压力当读写分离架构依旧无法满足业务时考虑分库分表提前分析好瓶颈再规划拆分策略常用的手段是并发量大分库数据量大分表而分库分表又会带来一系列需要解决的问题如分布式事务如何路由、联表、聚合等最后不要白嫖一键三连求求拉~ 本篇文章被收入专栏 MySQL进阶之路感兴趣的同学可以持续关注喔本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~ 有什么问题可以在评论区交流如果觉得菜菜写的不错可以点赞、关注、收藏支持一下~ 关注菜菜分享更多干货公众号菜菜的后端私房菜本文由博客一文多发平台 OpenWrite 发布

查看全文

http://www.tj-hxxt.cn/news/140048.html