二级c语言题库app数据库查询优化-20条必备sql优化技巧
0、序⾔
本⽂我们来谈谈项⽬中常⽤的 20 条 MySQL 优化⽅法，效率⾄少提⾼ 3倍！
具体如下：
1、使⽤ EXPLAIN 分析 SQL 语句是否合理
使⽤ EXPLAIN 判断 SQL 语句是否合理使⽤索引，尽量避免 extra 列出现：Using File Sort、Using Temporary 等。
2、必须被索引
重要SQL必须被索引：update、delete 的 where 条件列、order by、group by、distinct 字段、多表 join 字段。
3、联合索引
对于联合索引来说，如果存在范围查询，⽐如between、>、<；等条件时，会造成后⾯的索引字段失效。kubernetes部署
对于联合索引来说，要遵守最左前缀法则：举列来说索引含有字段 id、name、school，可以直接⽤ id 字段，也可以 id、name 这样的顺序，但是 name; school 都⽆法使⽤这个索引。所以在创建联合索引的时候⼀定要注意索引字段顺序，常⽤的查询字段放在最前⾯。
meet your requirement
4、强制索引
必要时可以使⽤ force index 来强制查询⾛某个索引: 有的时候MySQL优化器采取它认为合适的索引来检索 SQL 语句，但是可能它所采⽤的索引并不是我们想要的。这时就可以采⽤ forceindex 来强制优化器使⽤我们制定的索引。
5、⽇期时间类型
对于⾮标准的⽇期字段，例如字符串的⽇期字段，进⾏分区裁剪查询时会导致⽆法识辨，依旧⾛全表扫描。
尽管 TIMESTAMEP 存储空间只需要 datetime 的⼀半，然⽽由于类型 TIMESTAMP 存在性能问题，建议你还是尽可能使⽤类型 DATETIME。（TIMESTAMP ⽇期存储的上限为2038-01-19 03:14:07，业务⽤ TIMESTAMP 存在风险；）
mysql语句的执行顺序6、禁⽌使⽤ SELECT *
SELECT 只获取必要的字段，禁⽌使⽤ SELECT *。这样能减少不必要的消耗（CPU、IO、内存、⽹络带宽），增加使⽤覆盖索引的可能性；当表结构发⽣改变时，表结构变更对前端程序基本⽆影响。
7、避免出现某些字段
SQL 中避免出现 now()、rand()、sysdate()、current_user() 等不确定结果的函数。在语句级复制场景下，引起主从数据不⼀致；不确定值的函数，产⽣的 SQL 语句⽆法使⽤QUERY CACHE。
8、where ⼦句
避免在 where ⼦句中对字段进⾏ null 值判断：对于 null 的判断会导致引擎放弃使⽤索引⽽进⾏全表扫描。
避免在where⼦句中对字段进⾏表达式操作：因为对字段就⾏了算术运算，这会造成引擎放弃使⽤索引。
9、like
禁⽌使⽤ % 前导查询，例如：like “%abc”，⽆法利⽤到索引。
剪切视频app哪个好
在⽇常中你会发现全模糊匹配的查询，由于 MySQL 的索引是 B+ 树结构，所以当查询条件为全模糊时，例如 %AB%、%AB，索引⽆法使⽤，这时需要通过添加其他选择度⾼的列或者条件作为⼀种补充，从⽽加快查询速度。仅AB%形式的可以避免通配符引起索引屏蔽。
10、⽤ IN 代替 OR
OR 两边的字段中，如果有⼀个不是索引字段，⽽其它条件也不是索引字段，会造成该查询不⾛索引的情况。很多时候都会使⽤ IN 进⾏替代，或者使⽤ union all 或者是
union（必要的时候）的⽅式来代替“or”也会得到更好的效果。但 SQL 语句中 IN 包含的值不宜过多，应少于 1000 个。过多会使随机 IO 增⼤，影响性能。
使⽤ IN 是因为 MySQL 对其做了相应的优化，即将 IN 中的常量全部存储在⼀个数组⾥⾯，⽽且这个数组是排好序的。但是如果数值较多，产⽣的消耗⽐较⼤。
再例如：
select id from t where num in(1,2,3)
对于连续的数值，能⽤ between 就不要⽤ in 了；再或者使⽤连接来替换。
11、禁⽌使⽤负向查询
禁⽌使⽤负向查询，例如：not in、!=、<>、not like。
12、范围查询
在对字符串类型的索引进⾏⼤于运算时，会导致全表扫描。所以应改为区间between区间范围运算。
13、order by/group by
另外 order by/group by 的 SQL 涉及排序，尽量在索引中包含排序字段，并让排序字段的排序顺序与索引列中的顺序相同，这样可以避免排序或减少排序次数。如果排序字段没有⽤到索引，就尽量少排序。
14、禁⽌使⽤ order by rand()
order by rand() 会为表增加⼏个伪列，然后⽤ rand() 函数为每⼀⾏数据计算 rand() 值，最后基于该⾏排序，这通常都会⽣成磁盘上的临时表，因此效率⾮常低。建议先使⽤rand() 函数获得随机的主键值，然后通过主键获取数据。
15、尽量⽤union all代替union
union 和 union all 的差异主要是前者需要将结果集合并后再进⾏唯⼀性过滤操作，这就会涉及到排序，增加⼤量的CPU运算，加⼤资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。
16、减少与数据库交互
尽量采⽤批量 SQL 语句，减少与数据库交互次数。
获取⼤量数据时，建议分批次获取数据，每次获取数据少于 5000 条，结果集应⼩于 1M。
17、复杂查询还是简单查询？
不要⽤⼀个SQL解决所有事情，可以分步骤做，省时、易理解、优化。且 MySQL 也⼗分擅长处理短⽽简单的 SQL，总体耗时会更短，⽽且也不会产⽣臃肿的 SQL，让⼈难以理解和优化。
拆分复杂 SQL 为多个⼩SQL，避免⼤事务。简单的 SQL 容易使⽤到 MySQL 的 QUERY CACHE；减少锁表时间特别是 MyISAM；可以使⽤多核 CPU。
18、删除全表数据
delete from table_name；会产⽣⼤量 undo 和 redo ⽇志，执⾏时间很长，可采⽤ TRUNCATE TABLE tablename;
19、字符集问题
col_utf8mb4 = col_utf8 关联类型都是 varchar ，但字符集不同，⽆法使⽤索引。使⽤过程中要特别注意。
20、count 优化
这也是⼀个被⾯试中经常会问到的问题，对于下⾯的四条 SELECT 语句：
select count(*) from table … ;
select count(1) from table … ;
select count(primary key) from table … ;
select count(index key) from table …;
哪⼀条的执⾏效率最⾼呢？这个问题需要具体问题具体分析，不能⼀概⽽论。这⾥举 SELECT count(
1) 这条 SQL 为例。
优化前和优化后，执⾏效率相差2倍。就添加了⼀个索引。
优化思路 : 是选择索引 key_len 最短的⼆级索引效率⾼，不要使⽤全表扫描（PK 聚族索引会全表扫描），因为索引 key_len 越短，读取页⾯越少，进⽽ IO_COST 越⼩。
⼩结
⼤量的更新/删除操作需要控制频度，例如：每秒操作2000⾏以下
使⽤ prepared statement 和绑定变量，可以提升性能并避免 SQL 注⼊
oracle11g价格程序应有捕获 SQL 异常的处理机制，必要时通过 rollback 显⽰回滚
尽量少使⽤ distinct、order by、group by、union 等 SQL，排序需求可以放到前端（分页的就不⽅便交给前端排序）。
⼤事务或者长查询的需求根据业务特点拆分
杜绝程序中在处理事务时夹杂 RPC，会造成资源长时间不释放。有很多锁超时、并发数上涨都是由于事务中有 RPC 造成的。
关注软件本⾝的优化同时，也需要关注硬件的性能指标和优化，以及硬件的发展⽅向。MySQL 属于 IO 密集型的应⽤，对存储硬件的 IO 性能要求⽐较⾼，在⾼并发的场景中，建议使⽤ PCI-e。
重点总结⼀下：SQL 的执⾏过程->查询优化器的⼯作原理->SQL 执⾏计划的解读->MySQL 慢查询⽇志和分析->SQL 常⽤的优化⼿段->SQL 编写规范->深⼊实际业务对数据库访问进⾏优化。
参考：
《数据库⾼效优化：架构、规范与SQL技巧》
《拉勾教育专栏：⾼性能MySQL实战》

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。