苏州网站设计制作,展厅设计装修,syntaxhighlighter wordpress,WordPress高端主题 熊目标知识
MySQL执行流程图 MySQL 优化成本路线图 优化成本#xff1a;硬件系统配置数据库表结构SQL及索引。优化效果#xff1a;硬件系统配置数据库表结构SQL及索引。
MySQL 五大优化原则
减少数据返回#xff1a;设置合理字段数据类型、启用压缩…目标知识
MySQL执行流程图 MySQL 优化成本路线图 优化成本硬件系统配置数据库表结构SQL及索引。优化效果硬件系统配置数据库表结构SQL及索引。
MySQL 五大优化原则
减少数据返回设置合理字段数据类型、启用压缩1、MySQL客户端和MySQL服务器传输数据量太大需要进行压缩减少带宽。2、MySQL 某几张表太大需要将表压缩减少磁盘空间占用。3、MySQL表中特定字段过大需要针对特定字段压缩。、通过索引访问等方式减少磁盘IO。返回更少数据返回业务所需要字段、设置数据分页以减少磁盘IO和网络IO。减少交互次数批量执行DML操作/增删改查、使用自定义函数和存储过程以减少与MySQL服务器交互。减少MySQL服务器CPU开销尽量避免数据库表的排序操作以及全表扫描查询减少MySQL 服务器CPU和内存占用。拓展资源使用分表操作、分库分表操作、增加并行操作最大程度使用CPU资源。
总结来说
查询尽量命中索引尽量避免全部扫描移除无效字段查询
select 语句执行顺序
select 语法格式
SELECT
DISTINCT select_list
FROM left_table
join_type JOIN right_table
ON join_condition
WHERE where_condition
GROUP BY group_by_list
HAVING having_condition
ORDER BY order_by_condition
LIMIT limit_number
执行顺序
FROM表名 # 选取表将多个表数据通过笛卡尔积变成一个表。
ON筛选条件 # 对笛卡尔积的虚表进行筛选
JOIN join, left join, right join...
join表 # 指定join用于添加数据到on之后的虚表中例如left join会将左表的剩余数据添加到虚表中
WHEREwhere条件 # 对上述虚表进行筛选
GROUP BY分组条件 # 分组
HAVING分组筛选 # 对分组后的结果进行聚合筛选SUM()等聚合函数 # 用于having子句进行判断在书写上这类聚合函数是写在having判断里面的
SELECT返回数据列表 # 返回的单列必须在group by子句中聚合函数除外
DISTINCT数据除重
ORDER BY排序条件 # 排序
LIMIT行数限制
SQL 优化策略实战 温馨提示SQL优化策略适用于查询数据量较大或单表数据量较大的场景下如果数据量较小没必要以此为准。 1、避免不走索引场景
避免在字段开头模糊查询会导致数据库引擎放弃索引进行全表扫描。
示例如下
select * from t_user t where t.name like %周%
优化建议尽量在字段后面使用模糊查询
select * from t_user t where t.name like 周%
优化建议和解释如下 将 %周% 改为 周%在 SQL 查询中尽量避免使用 % 作为 LIKE 操作符的开头这样会导致数据库无法使用索引强制数据库做全表扫描影响查询性能。将 %周% 改为 周% 可以有效利用索引提高查询性能。 添加索引如果表 t_user 的 name 字段没有索引可以考虑为 name 字段添加索引从而加快 LIKE 操作的性能。在此案例中由于 WHERE 子句中对 name 字段进行了模糊查询添加索引可以显著提升查询性能。
尽量避免使用in 和not in会导致引擎走全表扫描。
示例如下
select * from t_user t where t.id in (1, 2)
优化一建议使用or 查询替换in 查询
select * from t_user t where t.id 1 or t.id 2
优化建议和解释
原始的 SQL 查询语句使用了 IN 子句来查询指定的 id而优化后的 SQL 查询语句使用 OR 来连接多个 id 的条件这样数据库在执行查询时可以更有效地利用索引。
使用 OR 来连接多个 id 的条件可以让数据库优化器更好地进行索引选择避免了 IN 子句可能导致的全表扫描。此外OR 连接条件时也可以让优化器更好地使用联合索引。
因此将 IN 子句替换为 OR 来连接多个条件是一个简单却有效的 SQL 查询优化方法。 优化二建议如果是连续性数字可以考虑使用between *** and 代替
select * from t_user t where t.id between 1 and 2 示例如下
select * from t_user t where t.orgId in (select id from t_org o where o.isDelete 1)
优化一建议使用内联查询替换in 查询
select t.* from t_user t
join t_org o on t.orgId o.id
where o.isDelete 1
优化建议和解释
原查询中使用了子查询来获取符合条件的 orgId这样的写法会导致多次查询数据库效率较低。我们可以通过使用 JOIN 来连接 t_user 表和 t_org 表来实现同样的功能避免重复查询。使用 JOIN 来连接两个表可以利用索引来加速查询。在这个情况下我们可以为 t_org 表的 isDelete 字段创建索引这样在执行 JOIN 操作时能够更快地定位符合条件的数据。尽量避免在查询条件中使用函数或者子查询这样会增加查询的复杂度和开销影响查询性能。通过将条件拆分成简单的等值条件可以提高查询效率。
优化二建议使用existe 函数 替换 in 查询
select * from t_user t where exists (select 1 from t_org o where o.id t.orgId and o.isDelete 1)
尽量避免使用 or会导致数据库引擎放弃索引进行全表扫描。
示例如下
SELECT * FROM t WHERE id 1 OR id 3
优化建议使用union 代替or
SELECT * FROM t WHERE id 1UNION
SELECT * FROM t WHERE id 3
尽量避免进行null值的判断会导致数据库引擎放弃索引进行全表扫描。
示例如下
SELECT * FROM t WHERE t.phone is null
优化建议给字段添加默认值对字段取值进行判断。
SELECT * FROM t WHERE t.phone
尽量避免在where条件中等号的左侧进行表达式、函数操作会导致数据库引擎放弃索引进行全表扫描。
示例如下
SELECT * FROM t_score t WHERE t.score/10 9
优化建议左侧表达式运算移动至右侧
SELECT * FROM t_score t WHERE t.score 10*9
当数据量大时避免使用where 11的条件。通常为了方便拼装查询条件我们会默认使用该条件数据库引擎会放弃索引进行全表扫描。
示例如下
SELECT username, age, sex FROM t_user WHERE 11
优化建议代码拼装sql时进行判断没 where 条件就去掉 where 1 1有where条件就加 and。
SELECT username, age, sex FROM t_use
查询条件不能用 或者 !
示例如下
SELECT username, age, sex FROM T WHERE t.id ! 1
优化建议使用大于操作符替换!
SELECT username, age, sex FROM T WHERE t.id 1
优化建议和解释如下 尽量避免在 WHERE 子句中使用不等于!操作符因为对不等于操作符的查询往往需要进行全表扫描性能较差。改为使用大于操作符可以更好地利用索引。 示例如下:
SELECT username, age, sex FROM T WHERE t.username ! root
优化建议和解释如下 考虑给关联字段在本例中是 username 字段添加索引确保表 T 的 id 字段上有索引可以加快查询速度。
where条件仅包含复合索引非前置列
示例复合联合索引包含key_part1key_part2key_part3三列但SQL语句没有包含索引前置列key_part1按照MySQL联合索引的最左匹配原则不会走联合索引。
select * from table where key_part21 and key_part32
隐式类型转换造成不使用索引
SQL语句由于索引对列类型为varchar但给定的值为数值涉及隐式类型转换造成不能正确走索引。
select * from table where col_varchar123;
order by 条件要与where中条件一致否则order by不会利用索引进行排序
-- 不走age索引
SELECT * FROM t order by age;-- 走age索引
SELECT * FROM t where age 0 order by age;
正确使用hint优化语句
MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。
use index 在你查询语句中表名的后面添加 USE INDEX 来提供希望 MySQL 去参考的索引列表就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table use index (mod_time, name)...ignore index 如果只是单纯的想让 MySQL 忽略一个或者多个索引可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table ignore index (priority) ...force index 为强制 MySQL 使用一个特定的索引可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) SELECT语句其他优化
避免出现select *
避免出现不确定结果的函数
特别是针对主从复制这类业务场景由于原理上从库复制的是主库执行的语句使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。
多表关联查询时小表在前大表在后。
在MySQL中执行 from 后的表关联查询是从左往右执行的Oracle相反第一张表会涉及到全表扫描所以将小表放在前面先扫小表扫描快效率较高在扫描后面的大表或许只扫描大表的前100行就符合返回条件并return了。
示例表1有1000条数据表2有500万条数据如果全表扫描表2SQL会提示超时异常。
使用表的别名
当在SQL语句中连接多个表时请使用表的别名并把别名前缀于每个列名上。减少列名歧义引起的语法错误。
调整Where字句中的连接顺序
MySQL采用从左往右自上而下的顺序解析where子句。根据这个原理应将过滤数据多的条件往前放最快速度缩小结果集。
DML /增删改查语句优化
大批量数据插入
如果同时执行大量的插入建议使用批量插入INSERT语句这比循环插入INSERT语句快性能提示主要体现减少与MySQL 服务器交互次数。
避免重复查询更新的数据
在实际业务开发中经常出现的更新行同时又希望获得改行信息的需求MySQL并不支持PostgreSQL那样的update teturning语法在MySQL中可以通过变量实现。
示例如下更新一行记录的时间戳同时希望查询当前记录中存放的时间戳。
Update t set timenow() where id1; Select time from t where id 1;
优化建议使用MySQL变量重写上述功能
Update t set timenow () where id 1 and now: now (); Select now;
前后二者都需要两次网络来回但使用变量避免了再次访问数据表特别是当t表数据量较大时后者比前者快很多。
查询优先还是更新insert、update、delete优先
MySQL 允许改变语句调度的优先级它可以使来自多个客户端的查询更好地协作这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型判断应用是以查询为主还是以更新为主的是确保查询效率还是确保更新的效率决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎比如 MyISAM 、MEMROY、MERGE对于Innodb 存储引擎语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下
写入操作优先于读取操作。对某张数据表的写入操作某一时刻只能发生一次写入请求按照它们到达的次序来处理。对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符允许你修改它的调度策略
LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATEHIGH_PRIORITY关键字应用于SELECT和INSERT语句DELAYED关键字应用于INSERT和REPLACE语句。
如果写入操作是一个 LOW_PRIORITY低优先级请求那么系统就不会认为它的优先级高于读取操作。在这种情况下如果写入者在等待的时候第二个读取者到达了那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。
SELECT 查询的HIGH_PRIORITY高优先级关键字也类似。它允许SELECT 插入正在等待的写入操作之前即使在正常情况下写入操作的优先级更高。另外一种影响是高优先级的 SELECT 在正常的 SELECT 语句之前执行因为这些语句会被写入操作阻塞。如果希望所有支持
LOW_PRIORITY 选项的语句都默认地按照低优先级来处理那么 请使用--low-priority-updates 选项来启动服务器。通过使用 INSERTHIGH_PRIORITY 来把 INSERT 语句提高到正常的写入优先级可以消除该选项对单个INSERT语句的影响。
查询条件优化
对于复杂的查询使用中间临时表暂存数据
优化group by语句
默认情况下MySQL 会对GROUP BY分组的所有值进行排序如 “GROUP BY 字段1字段2....;” MySQL 同时开启隐藏排序如 “ORDER BY 字段1字段2...;”
查询如果包括 GROUP BY 但你并不想对分组的值进行排序你可以指定 ORDER BY NULL禁止排序。示例如下
SELECT 字段1, 字段2, COUNT(*) FROM table GROUP BY 字段1, 字段2 ORDER BY NULL ;
优化join语句
MySQL中通过子查询使用 SELECT 语句来实现一个单列的查询结果然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作同时也可以避免事务或者表锁死并且写起来也很容易。但是有些情况下子查询可以被更有效率的连接(JOIN)替代。
示例如下
select * from t_user t where t.orgId in (select id from t_org o where o.isDelete 1)
优化一建议使用内联查询替换in 查询
select t.* from t_user t
join t_org o on t.orgId o.id
where o.isDelete 1
优化union查询
MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行否则建议使用union all。原因在于如果没有all这个关键词MySQL会给临时表加上distinct选项这会导致对整个临时表的数据做唯一性校验这样做的消耗相当高。
示例如下
select id, name, age t where t.id 1
union
select id, name, age b where b.id 2
优化建议:
select id, name, age t where t.id 1
union all
select id, name, age b where b.id 2
拆分复杂SQL为多个小SQL避免大事务
简单的SQL容易使用到MySQL的QUERY CACHE减少锁表时间特别是使用MyISAM存储引擎的表可以使用多核CPU。
使用truncate代替delete
当删除全表中记录时使用delete语句的操作会被记录到undo块中删除记录也记录binlog当确认需要删除全表时会产生很大量的binlog并占用大量的undo数据块此时既没有很好的效率也占用了大量的资源。
使用truncate替代不会记录可恢复的信息数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外使用truncate可以回收表的水位使自增字段值归零。
使用合理的分页方式以提高分页效率
使用合理的分页方式以提高分页效率 针对展现等分页需求合适的分页方式能够提高分页的效率。
select * from t
where id 10000 and is_deleted 0
order by create_time asc
limit 0, 15
该种写法缺陷越翻到后面执行效率越差时间越长尤其表数据量很大的时候。
优化建议:
select t.* from (select id from t where t.id 10000 and t.is_deleted 0order by t.create_time asc limit 0, 15) a, t where a.id t.id;
建表优化
在表中建立索引优先考虑where、order by使用到的字段。尽量使用数字型字段如性别男1 女2若只含数值信息的字段尽量不要设计为字符型这会降低查询和连接的性能并会增加存储开销。查询数据量大的表 会造成查询缓慢。可以通过合理分页查询来解决。用varchar/nvarchar 代替 char/nchar 尽可能的使用 varchar/nvarchar 代替 char/nchar 因为首先变长字段存储空间小可以节省存储空间其次对于查询来说在一个相对较小的字段内搜索效率显然要高些。不要以为 NULL 不需要空间比如char(100) 型在字段建立时空间就固定了 不管是否插入值NULL也包含在内都是占用 100个字符的空间的如果是varchar这样的变长字段 null 不占用空间。
