根据查询的结果更新 mysql mysql查询更新语句

转载

jacksky 2024-08-07 14:38:36

文章标签 根据查询的结果更新 mysql 字段执行计划 SQL 文章分类 MySQL 数据库

mysql的性能优化包罗甚广：

索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等等。

这里的记录的优化技巧更适用于开发人员，都是从网络上收集和自己整理的，主要是查询语句上面的优化，其它层面的优化技巧在此不做记录

查询的开销指标：

执行时间检查的行数返回的行数

建立索引的几个准则:

1.合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的相应速度

2.索引越多,更新数据的速度越慢

3.尽量在采用MyISAM作为引擎的时候使用索引,而不是InnoDB(但是MyISAM不支持事务)

4.当程序与数据库结构/SQL语句已经优化到无法优化的程度,那就要考虑memcached分布式缓存以及elasticSearch搜索引擎

5.使用Explain来分析SQL语句的性能(SQL执行计划能看到每一步的开销)

1.count的优化

mysql总是知道表的行数,所以当没有where语句的时候直接使用select count(*)会更快
count(*) 会算出包含null记录的数量， count(field_name)只包含不含 null的数量(这也是很多时候两种count方式结果不一致的原因)， count()的时候尽量用后一种， count(null)返回0，即不会记录null记录数量

2.减少查询的列,行,返回最小数据集

通过搜索参数,尽量减少对表的访问行数,列,最小化结果集,从而减轻网络负担
在where后面使用索引条件时,尽量将索引放在选择的首列
以及对应的数据类型要与数据库字段的类型保持一致,避免数据类型自动转换

3.索引字段上进行运算会使索引失效

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如： SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2
尽量使用主键或有索引的列order by

4.避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

因为这会使系统无法使用索引,而只能直接搜索表中的数据。
例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

5.数字型字段不要设计成字符型

一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

6. 合理使用EXISTS,NOT EXISTS子句。

1.SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：
IF (SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’)
可以写成：
IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)

7. 能够用BETWEEN的就不要用IN

8. 能够用DISTINCT的就不用GROUP BY

9. 尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定，阻止其他用户访问该表。

10. 表级优化

1：表的范式优化
2：适当增减一些冗余，做反范式优化（以空间换取时间）
3：表的列非常多的时候使用垂直拆分
原则：
   -把不常用的单独字段放到一个表中
   -把大字段独立存放到一个表中
   -把经常一起用的字段放在一起
4：表的数据量非常大的时候使用水平拆分
方法:
- 根据某个字段进行hash预算，如果要拆分成5个表，用取余的方式取到0-4，分表保到相应的表中
- 针对不同的hashID把数据存到不同的表中
问题:
- 跨分区查询的问题
- 统计及后台报表操作
(前台使用分表查询，后台使用汇总表查询做汇总报表操作).

11. 能用UNION ALL就不要用UNION

12. 查看MySQL“慢查询日志”