mysql优化策略

理想二旬
337 阅读8分钟

优化策略

优化一般由以下五点入手。

  1. 建表优化、
  2. 查询时尽量走索引、
  3. select语句优化、
  4. 查询条件优化
  5. DML(增删改)语句优化
一、建表优化

  1. 在表中建立索引，优先考虑where、order by使用到的字段。详细

    原因：

    根据where中的条件建立索引有时候可以避免回表操作。order by和索引的关系就是，当where子句中出现的字段在order by字段中也出现了，这种查出来的数据就是根据索引排序好的，order by子句对数据无需在次进行操作，因此当数据量很大时，就可以避免很多资源开销。

  2. 尽量使用数字型字段。如性别男1，女2，因为对数字的比较只需比较一次就行，而对字符串的比较会对字符一个一个的比较。

  3. 使用varchar、nvarchar代替char、nchar

    原因：

    首先知道varchar和char的区别varchar是一个可变长度，char是一个固定长度。char[7] 和varchar都存储tom，这个时候char占用了7个字节，varchar只占用3个字节。在存null的时候varchar不占用内存，而char开辟几个空间还是会占用几个空间。

二、查询时尽量走索引，避免不走索引的情况

  1. 尽量避免在字段开头进行模糊查询，因为这样会不使用索引，直接扫面全表，使查询效率降低。（当数据量较少时也可以使用）

    select * from user where username like '%三'

    如果非要在字段开头使用模糊查询，

    • 可以使用instr(str,substr)这个函数进行，在like'%xx'没有索引的情况下，比like稍快。（like使用索引的时候like快）文章
    • 使用Fulltext全文索引 文章1 *文章2

  2. 减少使用in 、not in ，因为不走索引，扫描全表（更新，这样说不全面，大家可以看这篇文章的讨论segmentfault.com/a/119000002… mysql是基于成本进行优化的，in走不走索引要看表的规模以及in中的限定个数。

    如：

    SELECT * FROM t WHERE id IN (2,3)` 
​
优化方式1：如果连续使用between and
​
SELECT * FROM t WHERE id BETWEEN 2 AND 3
​
优化方式2：如果是子查询，使用exists
​
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
EXISTS执行顺序：
​
1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
​
2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT * FROM B WHERE B.id = A.id
​
3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果
​

  3. 尽量避免使用or，不走索引，全表扫描

    如：

    SELECT * FROM t WHERE id = 1 OR id = 3

    优化方式：使用union代替or

    SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

  4. 尽量避免null值的判断，因为会扫描全表

    如：

    SELECT * FROM t WHERE score IS NULL

    优化方式：给字段添加默认值 0

    SELECT * FROM t WHERE score = 0`

  5. 不要再where条件中使用表达式、函数操作。 不走索引

    如：

    -- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9`

  6. 当数据量大的时候，避免使用where 1=1。

    这是为了方便拼接查询条件，但是用了后数据库引擎会放弃使用索引。

    优化方式：

    在拼接sql时进行判断，如果没有where条件就把where去掉，有where条件就加and。

  7. 查询条件不能用<> 或者 != ，不走索引。

    如果确实要用到 不等于，就根据业务需要对表的索引进行一下调整。

  8. order by的条件要和 where中的条件一样，否则order by不会使用使用索引进行排序。

三、select语句的优化

  1. 避免出现select *

    会影响mysql底层对执行计划的选择，查询出所有的列也会增加i/o和cpu的消耗，实际业务中要写明确具体的列。

  2. 避免出现不确定结果的函数

    特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、sysdate()等不确定结果的函数很容易导致主库与从库相应的数据不一致。

  3. 多表查询时小表在前，大表在后。

    from后的表是从左到右依次执行的，会先把第一个表的全表进行扫描，之后与后面的表的每行数据进行匹配，后面的大表可能不用扫面完毕就能与小表完成匹配。因此小表在前大表在后是做合适的

    例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

  1. 使用表的别名

    在多表查询的时候为每张表起一个别名，在列名前加上表名前缀，这样可以减少解析时间，提高效率。

  2. 调整where字句中的条件顺序

    mysql按从左到右，从上到下的解析顺序解析where条件，因此可以将过滤数据比较多的条件放前面，过滤数据比较少的条件放后面。

四、查询条件优化

  1. 复杂的查询，先用查询语句构建出一张中间表暂存数据，之后在查询想要的数据。

  2. 优化group by语句，使用order by NULL 禁止排序

    group by col1，col2，使用group by后即使没有对表进行order by显式排序，底层还是会对分组的数据进行一个排序，如果不需要他进行排序就可以在语句后面加上 order by NULL来禁止他的排序。

    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

  3. 在多表查询时能使用join就不要使用子查询

    使用join联结多个表，比使用子查询效率要高，因为可以免去子查询构建虚表的过程。MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤才能完成的查询工作。

  4. 优化union操作

    当不需要去重时，就不要加all这个关键字，这样比较高效。没有all关键字mysql底层会给临时表加上distinct这个关键字，此时会耗性能。

    高效：
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
​
UNION ALL 
​
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
​
​
低效：
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
​
UNION 
​
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

  5. 删除全表中的记录时，使用truncate关键字代替delete关键字

    使用delete关键字删除数据的时候会把记录写进二进制日志中，用来恢复数据。

    使用truncate删除数据不能恢复，进行删除善操作占用资源少。

五、DML增删改语句语句优化

  1. 大批量插入数据时，使用一条insert语句插入多个值

    当进行大量的插入操作的时候，使用插入多个值的insert语句，比使用一条语句插入一个值要快很多。

    方式一（慢）：

    insert into T values(1,2); 
​
insert into T values(1,3); 
​
insert into T values(1,4);

    方式二（快）：

    insert into T values(1,2),(1,3),(1,4);

使用方式二比较快的原因

语句短，io开销少。减少了sql语句的解析。

  1. 适当使用commit语句

    适当的使用commit提交事务，释放事务占用的资源。就比如使用delete删除大量数据的时候，定期使用commit达到释放资源的目的。

  2. 查询优先还是更新（insert、update、delete）优先

    MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。

    下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎，比如 MyISAM 、MEMROY、MERGE，对于Innodb 存储引擎，语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下：

    1）写入操作优先于读取操作。

    2）对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

    3）对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符，允许你修改它的调度策略：

    • LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；
    • HIGH_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语句的影响。

\

avatar
文章
阅读
粉丝