sql语句优化_如何提高sql语句查询速度,sql语句优化 - CSDN
  • 二、MySQL 常用的SQL语句优化方法: 三、MySQL 常用的索引优化方法: 四、MySQL数据库的优化目标、常见误区和基本原则: 五、MySQL数据库的表结构优化: 六、MySQL数据库的缓存参数优化:   数据库最常用的...

    目录:

    一、优化SQL语句的一般步骤:

    二、MySQL 常用的SQL语句优化方法:

    三、MySQL 常用的索引优化方法:

    四、MySQL数据库的优化目标、常见误区和基本原则:

    五、MySQL数据库的表结构优化:

    六、MySQL数据库的缓存参数优化:

     


    数据库最常用的优化方式有:SQL语句和索引、数据库表结构、系统配置、硬件。

    优化效果:SQL语句和索引 > 数据库表结构 > 系统配置 > 硬件,但成本从低到高。

    数据库的优化方法小结:

    (1)设计符合范式的数据库。

    (2)选择合适的存储引擎。

    (2)SQL语句优化;

    (3)索引优化:高分离字段建立索引。

    (4)SQL表结构、字段优化。

    (5)数据库参数优化:IO参数、CPU参数。

    (6)延迟加载、设置缓存与缓存参数优化。

    (7)分库分表:垂直切分与水平切分。

    (8)分区:将表的数据按照特定的规则放在不同的分区,提高磁盘的IO效率,提高数据库的性能。

    (9)主从复制与读写分离:三个主要线程与bin-log文件、relay_log文件,主数据库负责写操作,从数据库负责读操作。

    (10)负载均衡。

    (11)数据库集群。

    (12)硬件。


     

    一、优化SQL语句的一般步骤:

    1、通过show status 命令了解各种SQL的执行效率,

    show [session | global] status;

    可以根据需要加上参数来显示session级(当前连接,默认)和global级(自数据库上次启动至今)的统计结果。

     show status like 'Com_%';  ---显示当前连接所有统计参数的值。

    Com_xxx表示每个xxx语句执行的次数,通常需要注意的是下面几个参数:

    Com_select/Com_insert/Com_update/Com_delete。

    2、定位执行效率较低的SQL语句:

    (1)通过show processlist命令实时查看当前SQL的执行情况;

    (2)通过慢查询日志定位出现的问题。

    可以参考这篇博客:MySQL数据库:通用查询日志和慢查询日志分析

    3、通过explain 或 desc分析低效SQL的执行计划:

    可以参考这篇博客:MySQL数据库:explain执行计划详解

    4、通过show profile 分析SQL:

    show profile 能帮我们了解时间都耗费到哪里去了。

    可以参考这篇博客:MySQL数据库:使用show profile命令分析性能

    通过secect @have_profiling命令能够看到当前MySQL是否支持profile;

    通过show profiles我们能够更清楚了解SQL执行的过程;

    通过show profile for query我们能看到执行过程中线程的每个状态和消耗的时间。

    5、通过trace分析优化器如何选择执行计划:

    MySQL5.6提供了对SQL的跟踪trace,能帮我们了解为什么优化器选择执行A计划而不是B计划,进一步理解优化器的行为。

    6、确定问题并采取相应的优化措施。


     

    二、MySQL 常用的SQL语句优化方法:

    https://mp.weixin.qq.com/s/WdMzkrgu9McpXXZWVChBTg

    1、写出统一的SQL语句:

    对于以下两句SQL语句,很多人人认为是相同的,但是,数据库查询优化器认为是不同的。

    • select * from dual
    • select * From dual

    虽然只是大小写不同,查询分析器就认为是两句不同的SQL语句,必须进行两次解析。生成2个执行计划。所以作为程序员,应该保证相同的查询语句在任何地方都一致,多一个空格都不行。

    2、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

    3、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

    4、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num is null

    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num=0

    5、避免在where子句中使用or来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。

    6、前导模糊查询将导致全表扫描:

    select id from t where name like ‘%c%’

    下面使用索引

    select id from t where name like ‘c%’

    7、not in 也要慎用,否则会导致全表扫描;对于连续的数值,能用 between 就不要用 in 了,尽量使用exists代替in。

    8、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num=@num

    可以改为强制查询使用索引:

    select id from t with(index(索引名)) where num=@num

    7、应尽量避免在 where 子句中对字段进行表达式与函数或其他表达式运算操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    (1)select id from t where num/2=100

    应改为:select id from t where num=100*2

    (2)select id from t where substring(name,1,3)=’abc’ –name以abc开头的id

    应改为:select id from t where name like ‘abc%’

    (3)select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id

    应改为:select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1

    8、Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    9、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    10、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引。如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

    11、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引。一个表的索引数较好不要超过6个。

    12、应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。

    13、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    14、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    15、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

    16、对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

    17、尽量使用表变量来代替临时表。

    18、考虑使用“临时表”暂存中间结果。临时表并不是不可使用,适当地使用它们可以使某些查询更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表,后面的查询就在tempdb中查询了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。但是,对于一次性事件,较好使用导出表。

    19、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    20、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

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

    22、尽量避免使用游标,因为游标的效率较差。与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

    23、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。

    24、尽量避免向客户端返回大数据量。

    25、尽量避免大事务操作,提高系统并发能力。

    26、用where子句替换Having子句:

    避免使用having子句,having只会在检索出所有记录之后才会对结果集进行过滤,这个处理需要排序,如果能通过where子句限制记录的数目,就可以减少这方面的开销。on、where、having这三个都可以加条件的子句,on是最先执行,where次之,having最后。

    27、使用Truncate替代delete:

    当需要删除全表的记录时使用Truncate替代delete。在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。

    28、使用表的别名:

    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

    29、使用union all 替换 union:

    当SQL语句需要union两个查询结果集合时,这两个结果集合会以union all的方式被合并,然后再输出最终结果前进行排序。如果用union all替代料union,这样排序就不是不要了,效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录。

    30、用where替代order by:

    ORDER BY 子句只在两种严格的条件下使用索引:
    (1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序;
    (2)ORDER BY中所有的列必须定义为非空; 

    低效: (索引不被使用) 
    SELECT DEPT_CODE FROM  DEPT  ORDER BY  DEPT_TYPE 
    高效: (使用索引) 
    SELECT DEPT_CODE  FROM  DEPT  WHERE  DEPT_TYPE > 0

    31、避免索引列的类型转换:

    假设EMP_TYPE是一个字符类型的索引列. 
    SELECT …  FROM EMP  WHERE EMP_TYPE = 123 
    这个语句被转换为: 
    SELECT …  FROM EMP  WHERE EMP_TYPE=‘123’ 
    因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型。

    32、优化Group by:

    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。

    低效: 
    SELECT JOB , AVG(SAL) 
    FROM EMP 
    GROUP by JOB 
    HAVING JOB = ‘PRESIDENT' 
    OR JOB = ‘MANAGER' 
    高效: 
    SELECT JOB , AVG(SAL) 
    FROM EMP 
    WHERE JOB = ‘PRESIDENT' 
    OR JOB = ‘MANAGER' 
    GROUP by JOB

    33、避免使用耗费资源的操作:

    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

    34、在运行代码中,尽量使用PreparedStatement来查询,不要用Statement。


     

    三、MySQL 常用的索引优化方法:

    这部分可以阅读这篇文章:MySQL数据库:索引(三):索引的使用及优化


     

    四、MySQL数据库的优化目标、常见误区和基本原则:

    1、优化目标:

    MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。

    (1)减少 I/O 次数:

    I/O永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段。

    (2)降低 CPU 计算:

    除了 IO 瓶颈之外,SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

    2、常见误区:

    (1)count(1)和count(primary_key) 优于 count(*):

    很多人为了统计记录条数,就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他们认为这样性能更好,其实这是一个误区。对于有些场景,这样做可能性能会更差,应为数据库对 count(*) 计数操作做了一些特别的优化。如在MyISAM引擎中,会对表的总行数进行记录,使用count(*)可以直接取出该值。

    (2)count(column) 和 count(*) 是一样的:

    实际上,count(column) 和 count(*) 是一个完全不一样的操作,所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录,只处理非空值。count(*) 是表示整个结果集有多少条记录,不会跳过null值。

    (3)select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量:

    实际上,大多数关系型数据库都是按照行(row)的方式存储,而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位,一般为4KB,8KB… 大多数时候,每个IO单元中存储了多行,每行都是存储了该行的所有字段(lob等特殊类型字段除外)。所以,我们是取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。

    当然,也有例外情况,那就是我们的这个查询在索引中就可以完成,也就是说当只取 a,b两个字段的时候,不需要回表,而c这个字段不在使用的索引中,需要回表取得其数据。在这样的情况下,二者的IO量会有较大差异。

    (4)order by 一定需要排序操作:

    我们知道索引数据实际上是有序的,如果我们的需要的数据和某个索引的顺序一致,而且我们的查询又通过这个索引来执行,那么数据库一般会省略排序操作,而直接将数据返回,因为数据库知道数据已经满足我们的排序需求了。实际上,利用索引来优化有排序需求的 SQL,是一个非常重要的优化手段。

    延伸阅读:MySQL ORDER BY 的实现分析 ,MySQL 中 GROUP BY 基本实现原理 以及 MySQL DISTINCT 的基本实现原理 这3篇文章中有更为深入的分析,尤其是第一篇。

    (5)执行计划中有 filesort 就会进行磁盘文件排序:

    有这个误区其实并不能怪我们,而是因为 MySQL 开发者在用词方面的问题。filesort 是我们在使用 explain 命令查看一条 SQL 的执行计划的时候可能会看到在 “Extra” 一列显示的信息。实际上,只要一条 SQL 语句需要进行排序操作,都会显示“Using filesort”,这并不表示就会有文件排序操作。

    延伸阅读:理解 MySQL Explain 命令输出中的filesort,我在这里有更为详细的介绍

    3、基本原则:

    (1)尽量少 join:

    MySQL 的优势在于简单,但这在某些方面其实也是其劣势。MySQL 优化器效率高,但是由于其统计信息的量有限,优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join,一方面由于其优化器受限,再者在 Join 这方面所下的功夫还不够,所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询,这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

    (2)尽量少排序:

    排序操作会消耗较多的 CPU 资源,所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。对于MySQL来说,减少排序有多种办法,比如:

    上面误区中提到的通过利用索引来排序的方式进行优化;

    减少参与排序的记录条数;

    非必要不对数据进行排序。

    (3)尽量避免 select *:

    很多人看到这一点后觉得比较难理解,上面不是在误区中刚刚说 select 子句中字段的多少并不会影响到读取的数据吗?
    是的,大多数时候并不会影响到 IO 量,但是当我们还存在 order by 操作的时候,select 子句中的字段多少会在很大程度上影响到我们的排序效率,此外,上面误区中不是也说了,只是大多数时候是不会影响到 IO 量,当我们的查询结果仅仅只需要在索引中就能找到的时候,还是会极大减少 IO 量的。

    (4)尽量用 join 代替子查询:

    虽然 Join 性能并不佳,但是和 MySQL 的子查询比起来还是有非常大的性能优势。

    (5)尽量少 or:

    当 where 子句中存在多个条件以“或”并存的时候,MySQL 的优化器并没有很好的解决其执行计划优化问题,再加上 MySQL 特有的 SQL 与 Storage 分层架构方式,造成了其性能比较低下,很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

    (6)尽量用 union all 代替 union:

    union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用 union all 而不是 union。

    (7)尽量早过滤:

    这一优化策略其实最常见于索引的优化设计中(将过滤性更好的字段放得更靠前)。
    在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候,我们最好是能够在一个表上先过滤好数据分好页,然后再用分好页的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间。

    (8)避免类型转换:

    这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换:

    (9)优先优化高并发的 SQL,而不是执行频率低某些“大”SQL:

    对于破坏性来说,高并发的 SQL 总是会比低频率的来得大,因为高并发的 SQL 一旦出现问题,甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL,由于频率低,即使遇到,最多就是让整个系统响应慢一点,但至少可能撑一会儿,让我们有缓冲的机会。

    (10)从全局出发优化,而不是片面调整:

    SQL 优化不能是单独针对某一个进行,而应充分考虑系统中所有的 SQL,尤其是在通过调整索引优化 SQL 的执行计划的时候,千万不能顾此失彼,因小失大。

    (11)尽可能对每一条运行在数据库中的SQL进行 explain:
    优化 SQL,需要做到心中有数,知道 SQL 的执行计划才能判断是否有优化余地,才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后,很明显的问题 SQL 可能已经很少了,大多都需要去发掘,这时候就需要进行大量的 explain 操作收集执行计划,并判断是否需要进行优化。


     

    五、MySQL数据库的表结构优化:

    由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了。反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能。此外,由于我们的内存是有限的,增加每个page中存放的数据行数,就等于增加每个内存块的缓存数据量,同时还会提升内存换中数据命中的几率,也就是缓存命中率。

    1、数据类型选择:

    数据库操作中最为耗时的操作就是 IO 处理,大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量,可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据,但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多,数据量较大的场景,因为精细化的数据类型设置可能带来维护成本的提高,过度优化也可能会带来其他的问题:

    (1)数字类型:非万不得已不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。同样,固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。对于整数的存储,在数据量较大的情况下,建议区分开 TINYINT / INT / BIGINT 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加unsigned定义。当然,如果数据量较小的数据库,也可以不用严格区分三个整数类型。

    int类型只增主键字段=>4字节=>每个字节8位=>32位,在CPU加载一条指令的时候,4字节是和CPU寄存器的运算有关,如:64位,由于之前的系统一般都是32位的,所以在运算4字节的数据是刚好的,效率最高,而现今我们系统基本都是64位的时候,其实没有更好的利用好CPU运算,所以在设计表字段建议,使用8字节的主键bigint,而不是直接使用int来做主键。

    (2)字符类型:非万不得已不要使用 TEXT 数据类型,其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段,建议使用 CHAR 类型,不定长字段尽量使用 VARCHAR,且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也会有不一样的存储处理。

    char(10) 不管该字段是否存储数据,都占10个字符的存储空间,char(10) 同时存在一个坑,就是存储abc数据后改数据库字段的值为“abc  7个空格 ”,在精准查询(where)就必须带上后面的7个空格。varchar 不存的时候不占空间,存多长数据就占多少空间。

    (3)时间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。

    (4)ENUM & SET:对于状态字段,可以尝试使用 ENUM 来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢?可以尝试使用SET类型,即使存在多种属性,同样可以游刃有余,同时还可以节省不小的存储空间。

    (5)LOB类型:强烈反对在数据库中存放 LOB 类型数据,虽然数据库提供了这样的功能,但这不是他所擅长的,我们更应该让合适的工具做他擅长的事情,才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。

    (6)字符编码:字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。

    ①纯拉丁字符能表示的内容,没必要选择 latin1 之外的其他字符编码,因为这会节省大量的存储空间;

    ②如果我们可以确定不需要存放多种语言,就没必要非得使用UTF8或者其他UNICODE字符类型,这回造成大量的存储空间浪费;

    ③MySQL的数据类型可以精确到字段,所以当我们需要大型数据库中存放多字节数据的时候,可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量,进而降低 IO 操作次数并提高缓存命中率。

    (7)适当拆分:

    有些时候,我们可能会希望将一个完整的对象对应于一张数据库表,这对于应用程序开发来说是很有好的,但是有些时候可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。

    2、上面几点的优化都是为了减少每条记录的存储空间大小,让每个数据库中能够存储更多的记录条数,以达到减少 IO 操作次数,提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解,因为这是典型的反范式设计,而且也和上面的几点优化建议的目标相违背。

    (1)适度冗余:

    为什么我们要冗余?这不是增加了每条数据的大小,减少了每个数据块可存放记录条数吗?确实,这样做是会增大每条记录的大小,降低每条记录中可存放数据的条数,但是在有些场景下我们仍然还是不得不这样做:

    ①被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段:这样的场景由于每次Join仅仅只是为了取得某个小字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新。

    (2)尽量使用 NOT NULL:

    NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。很多人觉得 NULL 会节省一些空间,所以尽量让NULL来达到节省IO的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。


     

    六、MySQL数据库的缓存参数优化:

    数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化:

    1、query_cache_size / query_cache_type (global):

    Query cache 作用于整个 MySQL Instance,主要用来缓存 MySQL 中的 ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache 功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache 中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。

    当然,Query Cache 也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache 可能会得不偿失。

    Query Cache的使用需要多个参数配合,其中最为关键的是 query_cache_size 和 query_cache_type ,前者设置用于缓存 ResultSet 的内存大小,后者设置在何场景下使用 Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size 一般 256MB 是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进行调整。query_cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表示完全不使用query cache,除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有显示要求才使用query cache(使用sql_cache)。

    2、binlog_cache_size (global):

    Binlog Cache 用于在打开了二进制日志(binlog)记录功能的环境,是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说,如果我们的数据库中没有什么大事务,写入也不是特别频繁,2MB~4MB是一个合适的选择。但是如果我们的数据库大事务较多,写入量比较大,可与适当调高binlog_cache_size。同时,我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够,是否有大量的binlog_cache由于内存大小不够而使用临时文件(binlog_cache_disk_use)来缓存了。

    3、key_buffer_size (global):

    Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了,尤其是在 MySQL 没有更换默认存储引擎的时候,很多朋友可能会发现,默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存,这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引,以尽可能提高性能。此外,当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意,由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中,而不会缓存表数据库块。所以,我们的 SQL 一定要尽可能让过滤条件都在索引中,以便让缓存帮助我们提高查询效率。

    4、bulk_insert_buffer_size (thread):

    和key_buffer_size一样,这个参数同样也仅作用于使用 MyISAM存储引擎,用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候,会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件:

    insert … select …
    insert … values (…) ,(…),(…)…
    load data infile… into… (非空表)

    5、innodb_buffer_pool_size(global):

    当我们使用InnoDB存储引擎的时候,innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了,他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小,类似于 MyISAM 存储引擎的 key_buffer_size 参数,当然,可能更像是 Oracle 的 db_cache_size。简单来说,当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。和key_buffer_size 对于 MyISAM 引擎一样,innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小,直接关系到 InnoDB存储引擎的性能,所以如果我们有足够的内存,尽可将该参数设置到足够打,将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中,直至全部。我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。

    6、innodb_additional_mem_pool_size(global):

    innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL 会记录 Warning 信息到数据库的 error log 中,这时候你就知道该调整这个参数大小了。

    7、innodb_log_buffer_size (global):

    这是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer,InnoDB 在写事务日志的时候,为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

    innodb_flush_log_trx_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0,1,2,解释如下:

    0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
    1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;
    2:事务提交会触发log buffer 到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

    此外,MySQL文档中还提到,这几种设置中的每秒同步一次的机制,可能并不会完全确保非常准确的每秒就一定会发生同步,还取决于进程调度的问题。实际上,InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制,可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。

    8、innodb_max_dirty_pages_pct (global):

    这个参数和上面的各个参数不同,他不是用来设置用于缓存某种数据的内存大小的一个参数,而是用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例(已经被修但还没有从内存中写入到数据文件的脏数据)。这个比例值越大,从内存到磁盘的写入操作就会相对减少,所以能够一定程度下减少写入操作的磁盘IO。但是,如果这个比例值过大,当数据库 Crash 之后重启的时间可能就会很长,因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时,过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。

    上面这几个参数是 MySQL 中为了减少磁盘物理IO而设计的主要参数,对 MySQL 的性能起到了至关重要的作用,下面是几个参数的建议取值:

    • query_cache_type : 如果全部使用innodb存储引擎,建议为0,如果使用MyISAM 存储引擎,建议为2,同时在SQL语句中显式控制是否是使用query cache;
    • query_cache_size: 根据 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大;
    • binlog_cache_size: 一般环境2MB~4MB是一个合适的选择,事务较大且写入频繁的数据库环境可以适当调大,但不建议超过32MB;
    • key_buffer_size: 如果不使用MyISAM存储引擎,16MB足以,用来缓存一些系统表信息等。如果使用 MyISAM存储引擎,在内存允许的情况下,尽可能将所有索引放入内存,简单来说就是“越大越好”;
    • bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句(上面有说明)来插入数据,可以适当调大该参数至16MB~32MB,不建议继续增大;
    • innodb_buffer_pool_size: 如果不使用InnoDB存储引擎,可以不用调整这个参数,如果需要使用,在内存允许的情况下,尽可能将所有的InnoDB数据文件存放如内存中,同样将但来说也是“越大越好”;
    • innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB~16MB,如果表特别多,可以调整到32MB,可以根据error log中的信息判断是否需要增大;
    • innodb_log_buffer_size: 默认是1MB,写入频繁的系统可适当增大至4MB~8MB。当然如上面介绍所说,这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB;
    • innodb_max_dirty_pages_pct: 根据以往的经验,重启恢复的数据如果要超过1GB的话,启动速度会比较慢,几乎难以接受,所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然,如果你能够忍受启动时间比较长,而且希望尽量减少内存至磁盘的flush,可以将这个值调整到90,但不建议超过90。

     

     

    参考文章:https://blog.csdn.net/yzllz001/article/details/54848513

    展开全文
  • 性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL语句,要设法对之进行...

    概要

    性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。


    为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL语句,要设法对之进行简化。

    常见的简化规则如下:

    1.  不要有超过5个以上的表连接(JOIN)
    2.  考虑使用临时表或表变量存放中间结果。
    3.  少用子查询
    4.  视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

    为了加快查询速度,优化查询效率,主要原则就是应尽量避免全表扫描,应该考虑在where及order by 涉及的列上建立索引

    建立索引不是建的越多越好,原则是:

    利用以上的基础我们讨论一下如何优化sql:

    1. 表的索引不是越多越好,也没有一个具体的数字,根据以往的经验,一个表的索引最多不能超过6个因为索引越多,对update和insert操作也会有性能的影响,涉及到索引的新建和重建操作。
    2. 建立索引的方法论为:

    语句优化

    1、sql语句模型结构优化指导

    a. ORDER BY + LIMIT组合的索引优化

    如果一个SQL语句形如:

    SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

    这个SQL语句优化比较简单,在[sort]这个栏位上建立索引即可。

    b. WHERE + ORDER BY + LIMIT组合的索引优化

    如果一个SQL语句形如:

    SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

    这个语句,如果你仍然采用第一个例子中建立索引的方法,虽然可以用到索引,但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

    c. WHERE+ORDER BY多个栏位+LIMIT

    如果一个SQL语句形如:

    SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

    对于这个语句,大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。


    2、复合索引(形如(x,y,uid)索引的索引)

    先看这样一条语句这样的:select* from users where area =’beijing’ and age=22;

    如果我们是在area和age上分别创建索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果area,age两列上创建复合索引的话将带来更高的效率。

    在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    例如我们建立了一个这样的索引(area,age,salary),那么其实相当于创建了(area,age,salary),(area,age),(area)三个索引,这样称为最佳左前缀特性。


    3、like语句优化

    SELECT id FROM A WHERE name like '%abc%'

    由于abc前面用了“%”,因此该查询必然走全表查询,除非必要,否则不要在关键词前加%,优化成如下

    SELECT id FROM A WHERE name like 'abc%'

    4、where子句使用 != 或 <> 操作符优化

    在where子句中使用 != 或 <>操作符,索引将被放弃使用,会进行全表查询。

    如SQL:SELECT id FROM A WHERE ID != 5
    
    优化成:SELECT id FROM A WHERE ID>5 OR ID<5

    5、where子句中使用 IS NULL 或 IS NOT NULL 的优化

    在where子句中使用 IS NULL 或 IS NOT NULL 判断,索引将被放弃使用,会进行全表查询。

    如SQL:SELECT id FROM A WHERE num IS NULL
    
    优化成num上设置默认值0,确保表中num没有null值,然后SQL为:SELECT id FROM A WHERE num=0

    6、where子句使用or的优化

    很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。

    如SQL:SELECT id FROM A WHERE num =10 or num = 20
    
    优化成:SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

    7、where子句使用IN 或 NOT IN的优化

    in和not in 也要慎用,否则也会导致全表扫描。

    方案一:between替换in

    如SQL:SELECT id FROM A WHERE num in(1,2,3)
    
    优化成:SELECT id FROM A WHERE num between 1 and 3

    方案二:exist替换in

    如SQL:SELECT id FROM A WHERE num in(select num from b )
    
    优化成:SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

    方案三:left join替换in

    如SQL:SELECT id FROM A WHERE num in(select num from B)
    
    优化成:SELECT id FROM A LEFT JOIN B ON A.num = B.num

    8、where子句中对字段进行表达式操作的优化

    不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算,否则系统将可能无法正确使用索引。

    如SQL:SELECT id FROM A WHERE num/2 = 100
    优化成:SELECT id FROM A WHERE num = 100*2
    
    如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc'
    优化成:SELECT id FROM A WHERE LIKE 'abc%'
    
    如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0
    优化成:SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'
    
    如SQL:SELECT id FROM A WHERE year(addate) <2016
    优化成:SELECT id FROM A where addate<'2016-01-01'

    9、任何地方都不要用 select * from table ,用具体的字段列表替换"*",不要返回用不到的字段  


    10、使用“临时表”暂存中间结果

    采用临时表暂存中间结果好处:

      (1)避免程序中多次扫描主表,减少程序执行“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。

      (2)尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

      (3)避免频繁创建和删除临时表,以减少系统资源的浪费。

      (4)尽量避免向客户端返回大数据量,若数据量过大,应考虑相应需求是否合理。


    11、limit分页优化

    当偏移量特别时,limit效率会非常低

    SELECT id FROM A LIMIT 1000,10   很快
    
    SELECT id FROM A LIMIT 90000,10 很慢

    优化方法:

    方法一:select id from A order by id limit 90000,10; 很快,0.04秒就OK。 因为用了id主键做索引当然快
    
    方法二:select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;
    
    方法三:select id from A order by id  between 10000000 and 10000010;

    12、批量插入优化

    INSERT into person(name,age) values('A',14) 
    INSERT into person(name,age) values('B',14) 
    INSERT into person(name,age) values('C',14)

    可优化为:

    INSERT into person(name,age) values('A',14),('B',14),('C',14)

    13、利用limit 1 、top 1 取得一行

    有时要查询一张表时,你知道只需要看一条记录,你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

    如SQL:SELECT id FROM A LIKE 'abc%'
    
    优化为:SELECT id FROM A LIKE 'abc%' limit 1

    14、尽量不要使用 BY RAND()命令

    BY RAND()是随机显示结果,这个函数可能会为表中每一个独立的行执行BY RAND()命令,这个会消耗处理器的处理能力。

    如SQL:SELECT * FROM A order by rand() limit 10
    
    优化为:SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

    15、排序的索引问题 

    Mysql查询只是用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作;

    尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。


    16、尽量用 union all 替换 union

    union和union all的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的cpu运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用union all而不是union


    17、避免类型转换

    这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换,直接导致mysql无法使用索引。如果非要转型,应该在传入参数上进行转换。

    例如utime 是datetime类型,传入的参数是“2016-07-23”,在比较大小时通常是 date(utime)>"2016-07-23",可以优化为utime>"2016-07-23 00:00:00"


    18、尽可能使用更小的字段         

    MySQL从磁盘读取数据后是存储到内存中的,然后使用cpu周期和磁盘I/O读取它,这意味着越小的数据类型占用的空间越小,从磁盘读或打包到内存的效率都更好,但也不要太过执着减小数据类型,要是以后应用程序发生什么变化就没有空间了。

    修改表将需要重构,间接地可能引起代码的改变,这是很头疼的问题,因此需要找到一个平衡点。


    19、Inner join 和 left join、right join、子查询

    第一:inner join内连接也叫等值连接是,left/rightjoin是外连接。

    SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
    
    SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
    
    SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

    经过来之多方面的证实inner join性能比较快,因为inner join是等值连接,或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接,如:

     SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

    推荐:能用inner join连接尽量使用inner join连接

    第二:子查询的性能又比外连接性能慢,尽量用外连接来替换子查询。

    Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

    A表的数据为十万级表,B表为百万级表,在本机执行差不多用2秒左右,我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询,然后根据uuid逐次执行子查询,如果外层表是一个很大的表,我们可以想象查询性能会表现比这个更加糟糕。

    一种简单的优化就是用innerjoin的方法来代替子查询,查询语句改为:

    Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000;  这个语句执行测试不到一秒;

    第三:使用JOIN时候,应该用小的结果驱动打的结果

    (left join 左边表结果尽量小,如果有条件应该放到左边先处理,right join同理反向),同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低,容易锁表和阻塞)。如:

    Select * from A left join B A.id=B.ref_id where  A.id>10;可以优化为:select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

    20、exist 代替 in

    SELECT * from A WHERE idin (SELECT id from B)
    
    SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)

    in 是在内存中遍历比较

    exist 需要查询数据库,所以当B的数据量比较大时,exists效率优于in.

    in()只执行一次,把B表中的所有id字段缓存起来,之后检查A表的id是否与B表中的id相等,如果id相等则将A表的记录加入到结果集中,直到遍历完A表的所有记录。

    in()适合B表比A表数据小的情况,exists()适合B表比A表数据大的情况。


    查询速度慢的原因:

      1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 
     
      2、I/O吞吐量小,形成了瓶颈效应。  

      3、没有创建计算列导致查询不优化。 
     
      4、内存不足  

      5、网络速度慢  

      6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)  

      7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)  

      8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
      
      9、返回了不必要的行和列  

      10、查询语句不好,没有优化 


    优化方面

    主要在下述限制结果集,合理的表设计,OLAP和OLTP模块分开,使用存储过程四个方面进行优化

    1.限制结果集

    要尽量减少返回的结果行,包括行数和字段列数。

    返回的结果越大,意味着相应的SQL语句的logical reads 就越大,对服务器的性能影响就越大。

    一个很不好的设计就是返回表的所有数据: Select * from tablename

    即使表很小也会导致并发问题。更坏的情况是,如果表有上百万行的话,那后果将是灾难性的。它不但可能带来极重的磁盘IO,更有可能把数据库缓冲区中的其他缓存数据挤出,使得这些数据下次必须再从磁盘读取。

    必须设计良好的SQL语句,使得其有where语句或TOP语句来限制结果集大小。

    2.合理的表设计

    SQL Server 2005将支持表分区技术。利用表分区技术可以实现数据表的流动窗口功能。在流动窗口中可以轻易的把历史数据移出,把新的数据加入,从而使表的大小基本保持稳定。

    另外,表的设计未必需要非常范式化。有一定的字段冗余可以增加SQL语句的效率,减少JOIN的数目,提高语句的执行速度。

    3.OLAP和OLTP模块要分开

    OLAP和OLTP类型的语句是截然不同的。

    OLAP往往需要扫描整个表做统计分析,索引对这样的语句几乎没有多少用处。索引只能够加快那些如sum,group by之类的聚合运算。因为这个原因,几乎很难对OLAP类型的SQL语句进行优化。

    OLTP语句则只需要访问表的很小一部分数据,而且这些数据往往可以从内存缓存中得到。

    为了避免OLAP 和OLTP语句相互影响,这两类模块需要分开运行在不同服务器上。因为OLAP语句几乎都是读取数据,没有更新和写入操作,所以一个好的经验是配置一台standby 服务器,然后OLAP只访问standby服务器。

    4.使用存储过程

    可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑,这样做有几个好处:

    1. 存储过程的执行计划可以被缓存在内存中较长时间,减少了重新编译的时间。
    2. 存储过程减少了客户端和服务器的繁复交互。
    3. 如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。

    总结:

    如何使一个性能缓慢的系统运行更快更高效,不但需要整体分析数据库系统,找出系统的性能瓶颈,更需要优化数据库系统发出的SQL 语句。

    一旦找出关键的SQL 语句并加与优化,性能问题就会迎刃而解。


    End

    展开全文
  • sql优化

    2019-09-23 21:25:52
    sql优化: 1、在表中建立索引,优先考虑where、group by使用到的字段。 2、尽量避免使用select *,返回无用的字段会降低查询效率。如下: SELECT * FROM t 优化方式:使用具体的字段代替*,只返回使用到的字段。 3、...

    1、数据分区
    对海量数据的查询优化,一种重要方式是如何有效的存储并降低需要处理的数据规模,所以我们可以对海量数据进行分区。例如,针对年份存取的数据,可以按照年进行分区,不同数据库有不同的分区方式,但处理机制却大体相同。例如SQLserver的数据分区将不同的数据存于不同的文件组中,而不同的文件组存在不同的磁盘分区下,这样把数据分区,减少磁盘IO和系统负荷。
    2、索引
    索引一般可以加速数据的检索数据,加速表之间的连接,对表建索引包括在主键上建立聚簇索引,将聚合索引建立在日期列上。
    索引优点很多,但是对于索引的建立,还需要考虑实际情况,而不能对每个列都建索引。如果表结构很大,你要考虑到建立索引和维护索引的开销,索引本身也占物理空间,动态修改表也要动态维护索引,如果这些开销大过索引带来的速度优化,那就得不偿失了。
    3、缓存机制
    当数据量增加时,一般的处理工具都考虑到缓存问题,缓存大小的设置也关系到数据处理的表现。例如,处理2亿条数据聚合操作时,缓存设置为100000条/buffer合理。
    4、加大虚存
    由于系统资源有限,而需要处理的数据量非常大,当内存不足时,适量增加虚存来解决。
    5、分批处理
    由于处理信息量巨大,可以对海量数据进行分批处理(类似云计算的MapReduce),然后再对处理后的数据进行合并操作,分而治之,这样有利于处理小数据。
    6、使用临时表和中间表
    数据量增加时,处理中要考虑提前汇总,这样做的目的是化整为零,大表变小表,分块处理完成后再利用一定的规则进行合并,处理过程中的临时表的使用和中间结果的保存都非常重要。如果对超海量的数据,大表处理不了,只能拆分为多个小表。如果处理过程中需要多步汇总操作,则可按汇总步骤一步一步来。
    7、优化查询语句
    查询语句的性能对查询效率的影响非常大。尽量早地缩小查询范围。
    8、使用视图
    视图是表的逻辑表现,不占用物理空间。对于海量数据,可以按一定的规则分散到各个基本表中,查询过程基于视图进行。
    9、使用存储过程
    在存储过程中尽量使用SQL自带的返回参数,而非自定义的返回参数,减少不必要的参数,避免数据冗余
    10、用排序来取代非顺序存储
    磁盘臂的来回移动使得非顺序磁盘存取变成了最慢的操作,但是在SQL语句中这个现象被隐藏了,这样就使得查询中进行了大量的非顺序页查询,降低了查询顺序。
    11、使用采样数据进行数据挖掘
    基于海量数据的数据挖掘方兴未艾,面对超海量数据,一般的挖掘算法往往采用数据抽样的方式进行处理,这样误差不会太大,大大提高处理效率和处理的成功率。一般采样时应注意数据的完整性,防止过大的偏差。
    sql优化:
    1、在表中建立索引,优先考虑where、group by使用到的字段。

    2、尽量避免使用select *,返回无用的字段会降低查询效率。如下:

    SELECT * FROM t

    优化方式:使用具体的字段代替*,只返回使用到的字段。

    3、尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE id IN (2,3)

    SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

    优化方式:如果是连续数值,可以用between代替。如下:

    SELECT * FROM t WHERE id BETWEEN 2 AND 3

    如果是子查询,可以用exists代替。如下:

    SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

    4、尽量避免使用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

    (PS:如果or两边的字段是同一个,如例子中这样。貌似两种方式效率差不多,即使union扫描的是索引,or扫描的是全表)

    5、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE username LIKE ‘%li%’

    优化方式:尽量在字段后面使用模糊查询。如下:

    SELECT * FROM t WHERE username LIKE ‘li%’

    6、尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE score IS NULL

    优化方式:可以给字段添加默认值0,对0值进行判断。如下:

    SELECT * FROM t WHERE score = 0

    7、尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t2 WHERE score/10 = 9

    SELECT * FROM t2 WHERE SUBSTR(username,1,2) = ‘li’

    优化方式:可以将表达式、函数操作移动到等号右侧。如下:

    SELECT * FROM t2 WHERE score = 10*9

    SELECT * FROM t2 WHERE username LIKE ‘li%’

    8、当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE 1=1

    优化方式:用代码拼装sql时进行判断,没where加where,有where加and。

    展开全文
  • SQL语句优化

    2018-07-19 14:35:46
     首先你要知道是否跟sql语句有关,然后使用sql性能检测工具--sql server profiler,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的 1.查看执行时间和cpu占用时间(查询后在消息中查看) set...

    一、找出导致性能慢的原因

          首先你要知道是否跟sql语句有关,然后使用sql性能检测工具--sql server profiler,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的

    1.查看执行时间和cpu占用时间(查询后在消息中查看)

    set statistics time on
    select * from dbo.Product
    set statistics time off

    2.查看查询对I/0的操作情况(查询后在消息中查看)

    set statistics io on

    select * from dbo.Product

    set statistics io off

    逻辑读取:数据缓存中读取的页数

    物理读取:从磁盘中读取的页数

    预读:查询过程中,从磁盘放入缓存的页数

    如果物理读取次数和预读次说比较多,可以使用索引进行优化。如果存在扫描表,或者扫描聚集索引,这表示在当前查询中你的索引是不合适的,是没有起到作用的,那么你就要修改完善优化你的索引。

    二、优化方案

    关于SQL语句优化方法:比如避免Select *语句;字段、子语句或子表的顺序。

    (一)操作优化方案

    1.避免Select *

    Selcet中每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。

    1)尽量避免select * 的存在,使用具体的列代替*,避免多余的列

    2)使用where限定具体要查询的数据,避免多余的行

    3)使用top,distinct关键字减少多余重复的行

    2. insert插入优化

    分析说明:insert into select批量插入,明显提升效率,可以尽量避免一个个循环插入。

    3. 优化修改删除语句

    如果你同时修改或删除过多数据,会造成cpu利用率过高从而影响别人对数据库的访问。如果采用单一循环操作,效率会更低。折中的办法就是,分批操作数据。

    delete product where id<1000
    delete product where id>=1000 and id<2000
    delete product where id>=2000 and id<3000

    (二)避免全表扫描

    在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描。Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE,它们会引起全表扫描。避免使用like和or语句。

    1. IS NULL 或IS NOT NULL操作(判断字段是否为空)

    索引不索引空值,单列索引中,任何包含null值的列都将不会被包含在索引中。对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空。用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。NOT我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。效率比较低,改为使用大于小于号控制检索,因为大于小于号允许使用索引。

    1. > 及 < 操作符(大于或小于操作符)

    大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,执行A>2与A>=3有区别,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。

    1. LIKE操作符

    LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询, 但是若检索查询含有某一字符串的记录时,效率会很低,如果只是匹配以某一段开始或结尾使用like查询,效率不会太低。

    1. Order by语句

    Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

    三、SQL语句编写注意问题

    1.   ORACLE共享内存SGA的原理

    ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接

    2.  SQL语句索引的利用

    (1) 对条件字段的一些优化

    采用函数处理的字段不能利用索引, 条件内包括了多个本表的字段运算时不能进行索引ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

    (2) WHERE子句中的连接顺序:

    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

    (3) 使用DECODE函数来减少处理时间:

    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

    (4) 整合简单,无关联的数据库访问:

    如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 。

    (5) 删除重复记录:使用子语句过滤掉一部分数据,可以最大程度的删除重复。

    6) 用TRUNCATE替代DELETE:

    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

    (7) 尽量多使用COMMIT:

    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少,COMMIT所释放的资源:回滚段上用于恢复数据的信息;被程序语句获得的锁;redo log buffer 中的空间

    (8) 用索引提高效率:

    索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快.索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的:

    (9) sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。

    (10) 在java代码中尽量少用连接符“+”连接字符串!

    (11) 用UNION替换OR (适用于索引列)

    对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

    (12) 用IN来替换OR

    这是一条简单易记的规则,但是实际的执行效果还须检验,

    (13)总是使用索引的第一个列:

    如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

    (14)  用WHERE替代ORDER BY:

    ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非空.WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

    (15) 避免改变索引列的类型:

    当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.内部发生的类型转换, 此字段上的索引将不会被用到, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先将数值类型转换为字符类型。

    (16) 如果检索数据量超过表中记录数的30%.使用索引将没有显著的效率提高;

    (17) 避免使用耗费资源的操作:

    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

    (18) 优化GROUP BY:

    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.

    (19) 慎用distinct关键字

    distinct在查询一个字段或者很少字段的情况下使用,会避免重复数据的出现,给查询带来优化效果。但是查询字段很多的情况下使用,则会大大降低查询效率。很明显带distinct的语句cpu时间和占用时间都高于不带distinct的语句。原因是当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程则会毫不客气的占用系统资源,cpu时间。

    (20) 慎用union关键字

    此关键字主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。用法

    <select 语句1>union<select 语句2>union<select 语句3>

    满足union的语句必须满足:1.列数相同。 2.对应列数的数据类型要保持兼容。执行过程:依次执行select语句-->合并结果集--->对结果集进行排序,过滤重复记录。union all,使用union all能对union进行一定的优化,因为union all不进行去重和排序的过程。需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存.

    (21) 判断表中是否存在数据

    select count(*) from product select top(1) id from product

    (22)连接查询的优化

    减少连接表的数据数量可以提高效率,如果必不可免的使用表的连接,首先要弄明白你想要的数据是什么样子的,然后再做出决定使用哪一种连接。

    各种连接的取值大小为:内连接结果集大小取决于左右表满足条件的数量;左连接取决与左表大小,右相反;完全连接和交叉连接取决与左右两个表的数据总数量

    (23)用Where子句替代having子句

    避免使用having子句,having只会在检索出所有记录之后才对结果集进行过滤。HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 可以通过WHERE子句限制记录的数目, (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,尽可能的使用where语句。where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。

    (24)exists代替in、NOT EXISTS替代NOT IN:

    Oracle中In子查询返回的结果不能超过1000条,使用exists为替代方案。ORACLE在执行in操作时,试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并.为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

     

    展开全文
  • SQL语句优化大全

    2018-11-09 20:14:19
    一、定位慢查询: 必要指令: show status like ‘uptime’;当前数据库运行多久 show session或global status like ...当前数据库运行多少次查询 ...当前数据库运行多少次更新 show session或global status like ‘...

    一、定位慢查询:
    必要指令:
    show status like ‘uptime’;当前数据库运行多久

    show session或global status like ‘com_select’;当前数据库运行多少次查询
    show session或global status like ‘com_update’;当前数据库运行多少次更新
    show session或global status like ‘com_delete’;当前数据库运行多少次删除
    show session或global status like 'com_insert;当前数据库运行多少次查询
    默认是session,意思是当前会话的操作次数
    global是所有的操作次数
    根据数据库的操作类型来决定使用什么存储引擎

    show status like ‘connections’;当有多少客户端连接数据库

    show status like ‘slow_queries’;查询有多少慢查询

    默认情况,mysql认为查询时间到达10秒才是一个慢查询
    show variables like ‘long_query_time’;显示慢查询定义时间
    set long_query_time=1;将慢查询定义时间设置为1秒
    查询方法:
    1.停止mysql服务,在计算机管理–>服务–>mysql中停止
    2.在默认情况下mysql不记录慢查询日志,需要在启动的时候指定
    bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]
    bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
    (在bin的上级目录打开cmd再执行)
    启用慢查询日志后,默认把日志文件放在my.ini文件中记录的位置
    在文件中搜索:“datadir=”;
    不要轻易更改此目录,会造成索引失效。
    3.set long_query_time=1;将慢查询定义时间设置为1秒
    4.show status like ‘slow_queries’;查询有多少慢查询
    5.查日志就可以把慢查询揪出来了
    二、索引:
    索引为毛这么快:
    1.没有索引时,需要全表检索,即使很早检索到数据也必须搜完所有表;
    2.加完索引后,会建立一个索引文件,这个索引文件会将索引字段的磁盘地址构建成一个二叉树的数据结构进行存储,搜索时会进行二分查找,一旦查找到要查的数据就不需要找更下级的数据了,这样就大大加大了查询速度。
    经过计算:检索10次可以检索1024条数据,检索30次可以检索10亿的数据
    主键索引:
    创建:
    1.当一张表,把某个列设置为主键时,则该列就是主键索引
    2.alter table 表名 add primary key (列名);
    查询:
    1.desc 表名; 该方法缺点是:不能够显示索引名
    2.show index(es) from 表名;
    3.show keys from 表名;
    唯一索引:
    1.当表的某列被指定为unique约束时,这列就是一个唯一索引
    create table ddd(id int primary key auto_increment , name varchar(32) unique);
    这时, name 列就是一个唯一索引.
    unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复.
    主键字段,不能为NULL,也不能重复.
    2.在创建表后,再去创建唯一索引
    create table eee(id int primary key auto_increment, name varchar(32));
    create unique index 索引名 on 表名 (列表…);
    全文索引:
    应用场景:针对文本(文件)的检索,全文索引仅针对MyISAM有用
    创建:

    CREATE TABLE articles (
           id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
           title VARCHAR(200),
           body TEXT,
           FULLTEXT (title,body)
         )engine=myisam charset utf8;
    

    使用:
    1.在mysql中fulltext 索引只针对 myisam生效
    2.mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
    3.使用方法是 match(字段名…) against(‘关键字’)
    4.全文索引一个 叫 停止词, 因为在一个文本中,如果使用常用词创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.
    普通索引:
    创建:
    先创建表再创建普通索引

    create index  索引名 on 表名 (列);
    

    索引删除及修改:
    删除
    alter table 表名 drop index 索引名;
    如果删除主键索引。
    alter table 表名 drop primary key [这里有一个小问题]
    修改
    先删除,再重新创建.
    索引的使用方法:
    在如下情况添加索引比较合适:
    a: 经常作为where条件被使用
    b: 该字段的内容不是唯一的几个值(sex)
    c: 字段内容不是频繁变化
    会导致索引失效的语句:
    1、使用like关键字模糊查询时,% 放在前面索引不起作用,只有“%”不在第一个位置,索引才会生效(like ‘%文’–索引不起作用)
    2、使用联合索引时,只有查询条件中使用了这些字段中的第一个字段,索引才会生效
    3、使用OR关键字的查询,查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,索引才会生效,否则索引不生效。
    4、尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
    5、对查询进行优化,应尽量避免全表扫描,首先应考虑在where以及order by涉及的列上建立索引。
    6、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
      select id from t where num/2=100
      应改为:
      select id from t where num=100*2
    7、尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
    8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
    9、并不是所有的索引对查询都有效,sql是根据表中的数据来进行查询优化的,当索引列有大量数据重复时,sql查询不会去利用索引,如一表中有字段
      sex,male,female几乎个一半,那么即使在sex上建立了索引也对查询效率起不了作用。
    10、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,
      因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,
      若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
    11、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
      这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
    12、mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。
       因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。
    13、order by 索引 ,不起作用的问题(除了主键索引之外):
      1、 如果select 只查询索引字段,order by 索引字段会用到索引,要不然就是全表排列;

    2、如果有where 条件,比如where vtype=1 order by vtype asc . 这样order by 也会用到索引!
    四种索引的使用场景:
    PRIMARY, INDEX, UNIQUE 这3种是一类
    PRIMARY 主键。 就是 唯一 且 不能为空。
    INDEX 索引,普通的
    UNIQUE 唯一索引。 不允许有重复。
    FULLTEXT 是全文索引,用于在一篇文章中,检索文本信息的。
    举个例子来说,比如你在为某商场做一个会员卡的系统。
    这个系统有一个会员表
    有下列字段:

    会员编号   INT
    会员姓名   VARCHAR(10)
    会员身份证号码   VARCHAR(18)
    会员电话   VARCHAR(10)
    会员住址   VARCHAR(50)
    会员备注信息  TEXT
    

    那么这个 会员编号,作为主键,使用 PRIMARY
    会员姓名 如果要建索引的话,那么就是普通的 INDEX
    会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)
    会员备注信息 , 如果需要建索引的话,可以选择 FULLTEXT,全文搜索。
    不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。
    用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
    三、搜索引擎的选择:
    1、MYISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问的速度快。重要性不高的数据,如论坛的发帖、回帖。
    2、Innodb:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间。重要性较高的数据,如订单表、工资表。
    3.Memory:存储在内存当中,速度快,但会占用和数量成正比的内存空间且数据在mysql重启时会丢失。经常变化、查询频繁的临时数据。
    四、explain分析SQL:
    Explain select * from emp where ename=“zrlcHd”会产生如下信息:
    select_type:表示查询的类型。
    table:输出结果集的表
    type:表示表的连接类型
    possible_keys:表示查询时,可能使用的索引
    key:表示实际使用的索引 key_len:索引字段的长度 rows:扫描出的行数(估算的行数)
    Extra:执行情况的描述和说明
    五、常用SQL优化:
    1.优化group by 语句
    默认情况,MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null禁止排序
    2.有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。
    3.如果想要在含有or的查询语句中利用索引,则or之间的每个条件列都必须用到索引,如果没有索引,则应该考虑增加索引
    select * from 表名 where 条件1=‘’ or 条件2=‘tt’

    展开全文
  • sql语句优化

    2016-06-01 14:53:27
    sql语句优化  性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL...
  • 三、sql语句优化

    2018-06-02 22:15:01
    3.1 优化sql语句的一般步骤explain 或是desc (解析sql 语句)查看sql语句执行情况慢查询日志通过慢查询日志找出select语句(查找是不是需要加索引和影响行数) 之后在配和explain 或是desc先看影响行数,然后看是否...
  • --SQL优化语句的一般步骤 --1.通过show status命令了解各种...定位执行效率较低的SQL语句 --3.通过EXPLAIN分析较低SQL的执行计划 --4.通过show profile分析SQL --5.通过trace分析优化器如何选择执行计划 --6...
  • sql语句优化方法

    2016-07-14 11:58:15
    查询速度慢的原因很多,常见如下几种:  ... 3、没有创建计算列导致查询不优化。     4、内存不足     5、网络速度慢     6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)   
  • 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果...
  • 文章目录SQL语句优化概述SQL语句优化是提高性能的重要环节SQL语句优化的一般性原则常见SQL优化方法共享SQL语句减少访问数据库的次数SELECT语句中避免使用`*`WHERE子句中的连接顺序利用DECODE函数来减少处理时间删除...
  • 刚开始碰到一个旧系统里面的一条SQL语句,查询要200多秒,一看就是left join了很多表,并且索引使用有问题,条件连表之后再判断等问题。通过查看了mysql高性能和mysql语句优化书籍和论坛做一下小结并且记录一下这些...
  • 多表查询SQL 语句优化

    2007-07-08 01:56:00
    导致数据库服务器常常从几万条甚至更多的数据记录中查找符合条件的记录,如果sql查询语句设计不好查询的复杂度就会直线上升,甚至是指数级上升,导致查询时间长甚至失去相应,这里讲两种从sql语句优化查询的方法。...
  • MySQL SQL语句优化技巧

    2016-10-18 16:38:14
    2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: ...
  • 一篇挺不错的关于SQL语句优化的文章,因不知原始出处,故未作引用说明! 1 前言 客服业务受到SQL语句的影响非常大,在规模比较大的局点,往往因为一个小的SQL语句不够优化,导致数据库性能急剧下降,小型机idle...
  • 我在对项目进行优化的时候,也是经常会都对sql语句进行优化,因为一个sql语句的执行速度,会影响我们的页面加载速度,以及对数据库的操作速度,在这里我会使用我们的慢查询日志来查看sql语句的执行之间,使用我们的...
  • 1. SQL语句执行过程 2. 优化器及执行计划 3. 合理应用Hints 4. 索引及应用实例 5. 其他优化技术及应用   1.SQL语句执行过程 1.1 SQL语句的执行步骤    1)语法分析,分析语句的语法是否符合规范,...
  • SQL语句是对数据库进行操作的惟一途径,对数据库系统的性能起着决定性的作用。对于同一条件下的SQL语句写法有很多,其中一些写法往往对性能又有很大影响。但是每个人掌握SQL语言的水平不同,如何才能保证写出高性能...
  • 下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法: 1、SQL语句尽量用大写的;  因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。 2、使用表的别名:   当在SQL语句中连接多个表时, ...
  • MySQL查询截取分析步骤:一、开启慢查询日志,捕获慢SQL二、explain+慢SQL分析三、show profile查询SQL语句在服务器中的执行细节和生命周期四、SQL数据库服务器参数调优一、开启慢查询日志,捕获慢SQL1、查看慢查询...
1 2 3 4 5 ... 20
收藏数 258,976
精华内容 103,590
关键字:

sql语句优化