mysql常见优化手段,mysql简单优化

mysql常见优化手段,mysql简单优化,MySQL优化常用的19种有效方法(推荐!)

关于数据库优化,网上有很多资料和方法,但是很多质量参差不齐,有些总结的不够,内容比较冗杂。下面这篇文章主要介绍19种常用于MySQL优化的有效方法,有需要的朋友可以参考一下。

:

目录

1.解释2。SQL语句中IN的值不能太多。3.SELECT语句必须指示字段名称。4.当只需要一条数据时,使用limit 15。如果排序字段没有索引,尽量少排序。6.如果限制条件中的其他字段没有索引,尽量少用or7,用union all代替union8,不要用ORDER BY RAND()9,区分in和exists,not in和not exists10,使用合理的分页方法提高分页效率。11.分段查询。12.避免判断where子句中字段的空值。13.不建议使用%前缀进行模糊查询。14.避免在where子句中对字段进行表达式操作。15.避免隐式类型转换。16.对于联合索引,请遵守最左边的前缀规则。17.如有必要,可以使用force index强制查询转到某个索引。18.注意范围查询语句。19.连接优化综述。在本文中,我们将谈谈项目中常用的19种MySQL优化方法,具体如下:

1、EXPLAIN

优化MySQL,要利用好EXPLAIN来检查SQL执行计划。

这里有一个简单的例子,标记(1,2,3,4,5)我们想要关注的数据:

type列,连接类型。一个好的SQL语句至少要达到范围级别。消除所有级别。使用了key列,索引名称。如果没有选择索引,则该值为空。可以采用强制索引。key_len列,索引长度。rows列,条扫描线。这个值是估计值。extra列,详细描述。注意常见的不友好值如下:使用filesort,使用temporary。

2、SQL语句中IN包含的值不应过多

MySQL IN做了相应的优化,即IN中的所有常量都存储在一个数组中,这个数组的顺序很好。但是值多了,消耗就比较大。另一个例子:select id from t where num in(1,2,3)。对于连续值,使用between而不是in;或者用连接代替。

3、SELECT语句务必指明字段名称

SELECT*增加了很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构改变时,前分隔符也需要更新。因此,需要在select之后直接连接字段名。

4、当只需要一条数据的时候,使用limit 1

这是为了使EXPLAIN中的type列达到const type。

5、如果排序字段没有用到索引,就尽量少排序

6、如果限制条件中其他字段没有索引,尽量少用or

如果or两边的字段中有一个不是索引字段,其他条件也不是索引字段,会导致查询不索引。很多时候,用union all或union(必要时)代替“or”会得到更好的结果。

7、尽量用union all代替union

union all和union All的区别在于前者需要将结果合并后再进行唯一性过滤操作,这会涉及到排序,增加大量的CPU操作,增加资源消耗和延迟。当然,union all的前提是两个结果集中没有重复数据。

8、不使用ORDER BY RAND()

从“动态”订单中选择id,rand()限制为1000;

上述SQL语句可以优化如下:

select id from ` dynamic ` t1 join(select rand()*(select max(id)from ` dynamic `) as NID)T2 on t1 . id T2 . NID limit 1000;

9、区分in和exists、not in和not exists

select * from table A where id in(select id from table b)

上面的SQL语句相当于

select * from table A where exists(select * from table B,其中table B.id=table A.id)

IN和exists的区别主要是驱动顺序的变化(这是性能变化的关键)。如果存在,则将外层表作为驱动表,首先访问驱动表。如果在中,则首先执行子查询。所以IN适用于外观大,内在小的情况;EXISTS适用于外观很小但内部很大的情况。

关于not in和not exists,建议用not exists,这不仅是效率问题,也是逻辑问题。如何高效的写一条SQL语句而不是not exists?

原始SQL语句:

从a.id不在的表中选择colname …(从B表中选择b.id)

高效的SQL语句:

select colname…from A table Left join B table on where A . id=B . id where B . id为null

提取的结果集如下图所示。表A中的数据不在表B中:

10、使用合理的分页方式以提高分页的效率

从产品限制866613,20中选择id,名称

在使用上述SQL语句进行分页时,可能有人会发现,随着表数据的增加,直接使用limit分页查询会越来越慢。

优化方法如下:可以取上一页最大行数的id,然后根据这个最大id限定下一页的起点。例如,在该列中,上一页的最大id是86612。SQL可以写成如下形式:

从产品中选择id、名称,其中id 866612限制为20

11、分段查询

在用户选择的一些页面中,部分用户选择的时间范围可能过大,导致查询缓慢。主要原因是扫描线太多。此时,您可以按程序、逐段、循环查询,并组合显示结果。

如以下SQL语句所示,当扫描的行数超过数百万时,可以使用分段查询:

12、避免在where子句中对字段进行null值判断

null的判断会导致引擎放弃索引,扫描整个表。

13、不建议使用%前缀模糊查询

比如像“%name”或者像“%name%”,这种查询会导致索引失效和全表扫描。但是你可以用“姓名%”。

那如何查询%name%?

如下图所示,虽然在secret字段中添加了一个索引,但结果并不用于explain:

那么如何解决这个问题呢?回答:使用全文索引。

Select id,fnum,FDST from dynamic _ 201606其中用户名类似“%张三%”经常用在我们的查询中;有了这样的语句,普通的索引就无法满足查询需求。幸运的是,在MySQL中,有一个全文索引可以帮助我们。

创建全文索引的SQL语法是:

ALTER TABLE ` dynamic _ 201606 ` ADD full text INDEX ` idx _ user _ name `( ` user _ name `);

使用全文索引的SQL语句有:

select id,fnum,fdst from dynamic _ 201606 where match(user _ name)against(布尔模式下为'张三');

注意:在需要创建全文索引之前,请联系DBA以确定是否可以创建全文索引。同时要注意查询语句的编写与常用索引的区别。

14、避免在where子句中对字段进行表达式操作

例如:

select user_id,user_project from user_base其中年龄* 2=36;

对中的字段执行算术运算,这将导致引擎放弃使用索引。建议改为:

select user_id,user_project from user_base其中年龄=36/2;

15、避免隐式类型转换

当where子句中的列字段类型与传入参数的类型不一致时,会发生类型转换。建议首先确定where中的参数类型。

16、对于联合索引来说,要遵守最左前缀法则

例如,索引包含字段id、名称和学校,可以直接使用,也可以按照id和名称的顺序使用,但是名称;学校不能用这个指标。所以在创建联合索引时一定要注意索引字段的顺序,把常用的查询字段放在最前面。

17、必要时可以使用force index来强制查询走某个索引

有时候MySQL优化器会使用它认为合适的索引来检索SQL语句,但可能它使用的索引并不是我们想要的。此时,可以使用forceindex来强制优化器使用我们的索引。

18、注意范围查询语句

对于联合索引,如果存在范围查询,如、等之间的查询,则以下索引字段将变得无效。

19、关于JOIN优化

左JOIN A表是驱动表,内JOIN MySQL会自动找出数据少的表,右JOIN B表是驱动表。

注意:

1)MySQL中没有full join,可以用以下方式来解决:

select * from A left join B on B.name=A . name其中B . name为null union all select * from B;

2)尽量使用inner join,避免left join:

参与联合查询的至少有两个表,一般大小有差异。如果连接方式是inner join,MySQL会自动选择一个小表作为驱动表,不需要其他过滤条件,但是left join遵循左侧驱动右侧的原则,即left join左侧的表名就是驱动表。

3)合理利用索引:

从动表的索引字段用作on的限制字段。

4)利用小表去驱动大表:

从原理图中可以直观的看出,如果能减少驱动表,就可以减少嵌套循环中的循环次数,从而减少总的IO量和CPU操作次数。

5)巧用STRAIGHT_JOIN:

Inner join是MySQL选择的驱动表,但是有一些特殊情况需要选择另一个表作为驱动表,比如group by、order by等“使用filesort”和“使用temporary”。STRAIGHT_JOIN强制连接顺序。STRAIGHT_JOIN左边的表名是驱动表,右边是从动表。使用STRAIGHT_JOIN的前提是查询是内连接,也就是内连接。其他链接不建议使用STRAIGHT_JOIN,否则可能导致查询结果不准确。

这种方法有时可以将时间缩短三倍。

总结

以上就是本文关于MySQL优化常用的19种有效方法。有关MySQL优化常用方法的更多信息,请搜索我们以前的文章或继续浏览下面的相关文章。希望大家以后能多多支持我们!

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

相关文章阅读

  • 使用php连接mysql数据库,php连接数据库的方法
  • 使用php连接mysql数据库,php连接数据库的方法,一文详解PHP连接MySQL数据库的三种方式
  • pymysql菜鸟教程,pymysql 使用
  • pymysql菜鸟教程,pymysql 使用,pymysql模块使用简介与示例
  • mysql锁实现,mysql锁算法
  • mysql锁实现,mysql锁算法,MySQL锁机制与用法分析
  • mysql连接报错10061,mysql连接错误10060
  • mysql连接报错10061,mysql连接错误10060,MYSQL无法连接 提示10055错误的解决方法
  • mysql连接报10060错误,mysql连接报错10055
  • mysql连接报10060错误,mysql连接报错10055,MySQL连接异常报10061错误问题解决
  • mysql辅助索引和主键索引,mysql 主键 外键 索引
  • mysql辅助索引和主键索引,mysql 主键 外键 索引,MySQL索引之主键索引
  • MySQL语句大全,mysql常见语句总结
  • MySQL语句大全,mysql常见语句总结,MySQL语句整理及汇总介绍
  • mysql触发器的使用方法实验报告,mysql中触发器的使用
  • 留言与评论(共有 条评论)
       
    验证码: