常用的MYSQL优化

September 26, 2019 · 技术 · 718次阅读

字段,表结构

  • 对查询进行优化,避免全表扫描,首先考虑在 WHERE 和 ORDER BY 涉及的列上建立索引,索引的创建要考虑最左匹配原则

  • 避免模糊查询,模糊查询导致引擎放弃索引而进行全表扫描,模糊查询如:!= <>

  • 给num字段设定默认值0,避免num字段出现null,避免 WHERE 条件中使用null进行判断,这将导致全表扫描

  • 避免WHERE字句中使用 or、like、in等范围查询和函数,将导致全表扫描,可使用union all代替or查询,全文检索代替like,连续的数值可用between代替in

  • 避免在WHERE字句中使用表达式操作
  • 如果要在WHERE字句使用参数,可使用 with (index) 强制使用索引,否则将放弃索引进行全表扫描

  • 索引并非越多越好,大量的索引会占用磁盘空间,而且还会影响insert,delete,update等语句的性能,因为当表中的数据更改时索引也需要动态的维护

  • 避免对经常更新的表做更多的索引,并且索引中的列尽可能少;对经常用于查询的字段创建索引,避免添加不必要的索引

  • 数据量少的表或有大量重复数据的列尽量不要使用索引,否则查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果

  • 尽可能的用varchar/nvarchar代替char/nchar,首先变长字段存储空间小,可以节省存储空间,且对于查询来说,在一个相对较小的字段内搜索效率显然要高些,在某些知道数据固定长度的字段上可以使用char

  • 避免频繁创建和删除临时表,以减少系统表资源的消耗

  • 使用具体的 select 字段 代替 select *

  • 若数据量过大,考虑水平分表(按照数据分表)和垂直分表(按照结构分表)

避免重复插入数据的方法

insert

插入已存在数据时, id会自增,但是插入不成功,会报错

mysql> insert into users(name, age) values("小明", 25);
ERROR 1062 (23000): Duplicate entry '小明' for key 'name'

insert ignore

当插入已存在数据时,忽略新插入的记录,id会自增,不会报错

mysql> insert ignore into users(name, age) values("小明", 25);
Query OK, 0 rows affected, 1 warning (0.00 sec)

replace

替换已存在的数据,删除原来的记录,添加新的记录

mysql> replace into users(name, age) values("小明", 24);
Query OK, 2 rows affected (0.00 sec)

on duplicate key update

如果插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行UPDATE

//假设name字段被设置为unique
mysql>INSERT INTO users(name, age) VALUES ('小明', 23) ON DUPLICATE KEY UPDATE name='小红';
Query OK, 2 rows affected, 1 warning (0.00 sec)

待继续更新

喝杯水

标签:MYSQL

最后编辑于:2020/02/22 23:04

评论

  1. 2019-10-29 17:19

    非技术的路过。

    回复