p2f网站系统,网站建设项目报价,户外媒体网站建设免费,wordpress页面标题排序前段时候针对开发做的SQL语句写法方面注意点的培训#xff0c;特意总结了一下#xff0c;也共享一下。书写SQL需要注意的若干问题(MySQL版)一、基本问题1#xff0c;在系统中运行的SQL查询#xff0c;先考虑一下能不能在Slave上检索#xff0c;目前各个项目中Master上的不…前段时候针对开发做的SQL语句写法方面注意点的培训特意总结了一下也共享一下。书写SQL需要注意的若干问题(MySQL版)一、基本问题1在系统中运行的SQL查询先考虑一下能不能在Slave上检索目前各个项目中Master上的不可避免的查询量是其他所有的Slave总和还多。但也不是一味的都是在Slave上查询。系统上出过一次查询数据的情况在一个前后顺序执行的逻辑代码中先更新Master的数据再在Slave上查更新后的数据这样的操作很多时候因服务器和网络环境而出现查询结果不一致的情况。这样的就不能在Slave上查询了。2尽量不要输出没有用的列也不要输出已经明确的列增加了无用的数据传输量也是影响性能的。3尽量在每个查询中返回自己需要的那些行无关的不要返回。对简单查询是这样对复杂的包含很多子查询中的每个子查询更是这样尽量让每个子查询的结果集保留到最小再进行关联避免出现先关联后再取Distinct这样的操作。4尽量不要在程序里面有Select *这样的写法以后表字段的顺序变动都可能造成程序的问题。5对多表之间的连接必须用索引来作为连接列否则这样的查询就是一个全表扫描两边的关联字段一定要类型一致避免强制转换。mysql explain select count(*) From JHF_ALIVE_EXECUTION E , JHF_ALIVE_CONTRACT C where C.Trade_IDE.Trade_ID ;--------------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |--------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | E | ALL | NULL | NULL | NULL | NULL | 866 | || 1 | SIMPLE | C | ref | ALIVE_CONTRACT_02 | ALIVE_CONTRACT_02 | 42 | CFDMAIN.E.TRADE_ID | 1 | Using index |--------------------------------------------------------------------------------------------------------------------2 rows in set (0.00 sec)6不要在Where字句中对列使用函数那样会导致索引失效mysql show index from JHF_ALIVE_CONTRACT ;-------------------------------------------------------------------------------------------------------------------| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | | Index_type-------------------------------------------------------------------------------------------------------------------| JHF_ALIVE_CONTRACT | 0 | PRIMARY | 1 | CONTRACT_ID | A | 157 | | BTREE| JHF_ALIVE_CONTRACT | 1 | ALIVE_CONTRACT_01 | 1 | ORDER_ID | A | 157 | | BTREE| JHF_ALIVE_CONTRACT | 1 | ALIVE_CONTRACT_02 | 1 | TRADE_ID | A | 157 | | BTREE| JHF_ALIVE_CONTRACT | 1 | ALIVE_CONTRACT_03 | 1 | CUSTOMER_ID | A | 19 | | BTREE-------------------------------------------------------------------------------------------------------------------4 rows in set (0.00 sec)mysqlmysql explain select * From JHF_ALIVE_CONTRACT where Order_ID20090930CONT00002005 ;--------------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |--------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | JHF_ALIVE_CONTRACT | ref | ALIVE_CONTRACT_01 | ALIVE_CONTRACT_01 | 82 | const | 1 | Using where |--------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)主键检索最快的那种了。mysql explain select * From JHF_ALIVE_CONTRACT where substr(Order_ID,1,17) 20090930ORD000115 ;--------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |--------------------------------------------------------------------------------------------------| 1 | SIMPLE | JHF_ALIVE_CONTRACT | ALL | NULL | NULL | NULL | NULL | 94 | Using where |--------------------------------------------------------------------------------------------------1 row in set (0.00 sec)什么索引都没用上全表扫描。mysql explain select * From JHF_ALIVE_CONTRACT where Order_ID like 20090930ORD000115% ;--------------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |--------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | JHF_ALIVE_CONTRACT | range | ALIVE_CONTRACT_01 | ALIVE_CONTRACT_01 | 82 | NULL | 6 | Using where |--------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)like 也能发挥索引的效果。7使用like语句时对 “C%”是能利用索引的但对 “%C”是无效的。而且在前面这个固定字符串越多时效率越好也就尽量多匹配。见上例。8Not in是个危险的用法在程序中慎用必要时可以用left outerjoin来改写。9少用点or它很可能会使一个查询索引失效必要时可以用union all或者union来替代。10注意一下 Union all与union的区别。前者是两个结果集的不会经过任何处理进行相加而后者是要经过合并以后的内容。对两个毫不相关的集合合并时尽量用UNION ALL避免不必要的排序浪费系统资源。11在大表上不做Group by操作如果需要的话可以用大表的总结表。对一些避免不了的实时检索可以考虑用索引覆盖的方式来对所用到的字段全部建立索引的方式来加快查询速度。12对Group by ,distinct出来的结果已经是有序的了不需要再排序尽量使用已经排好序的数据免得再排序浪费资源如果要排序不要在Order by里面的使用表达式。13在java中尽量使用prepareStatement来替代Statement一个SQL提交给MYSQL后经历词义检查、语义检查、对象检查、获取存取路径、形成最终执行计划、生成执行代码但是如果是两个一样的SQL(一模一样多个空格都不行)这个过程就全部省了使用绑定变量(有的地方可能称主机变量就是用来替代值然后再设置这个值)能达到一模一样的效果DBMS在算存取路径的时候会估算一个值来替代这样能达到一个很好的效果。(如果不注意这一点那么你的系统离崩溃就不远了这点对程序员特别重要)但是也不是所有的情况都是这样对一个SQL“长时间固定不变的环境中”那么每次执行都是相同的SQL这时静态变量和绑定变量方式唯一的差别是获取存取路径方式的不同绑定方式是估算而写成变量的方式是精确的路径。实际中到底使用哪种1)一般都按照绑定变量来写。)如果在设计的时候就能明确该句在使用执行的环境再换成静态方式。其实 都用绑定变量这种方式来写没有什么坏处14不要轻易利用MySQL的自动类型转换看起来挺好使但用起来危害非常大因为它很可能会让看起来好端端的索引失效。15在数据库上经常在允许为NULL的字段上建立了索引注意想查询此字段上的is null或者is not null可能会使索引失效。16避免出现 跨库操作这样的SQL语句例如Use MAIN ;Insert intoJHF_ORDER select * From HISTORY.JHF_ORDER where id’33’ ;这样的SQL在Master上能正常运行的但是因为Slave的结构各种各样对不存在HISTORY库的SLAVE这个SQL就会导致同步中断而一般需要人工干预才能继续同步。17现有的数据库结构中各个Slave所忽略的表是不一样的对类似这样的SQL:Insert into TA select * From TB whereCode’ABC’在Master上执行没问题但如果某个Slave忽略了TB表的同步那么在这个Slave上的TA表的数据将也不会正常在程序中避免出现一个Insert/Update/Delete中关联多个表的情况很容易因为Slave同步部分表的原因而导致数据不一致。18对一个大的结果结进行排序是个非常费系统资源的操作。但也不能因为这点而不排序。对一个未使用任何排序操作的结果集的默认顺序是按照主键的顺序进行默认排序的没有主键或者自增长主键的是按照记录的插入先后顺序进行输出某些时候是满足需求的但是这样的排序是不可靠的在数据库进行过数据重整和索引重建或者后插入的数据的主键值不是按照一个固定的顺序来的时候就很可能打乱原始的顺序而出现不用时间的不用的检索结果。19关于批处理的SQL编写时要考虑SQL更新的速率和数据量的大小。 这主要是考虑到我们现在所使用的M/S同步机制。更新速度过快可能使数据库压力增大并且出现数据库同步延迟。更新太慢没有效率。总之一定要通过测试综合进行考虑找到平衡点以达到最好的效果。20不要在正式系统里面运行没有试过的SQL语句即使是Select语句。A)、不恰当关联造成笛卡儿结果集非常庞大让系统忙死在写入临时文件的操作中这个会发生在两个大表间关联的时候关联的条件是多对多的关系造成结果集非常庞大一时半会都执行不完这时不要慌关闭终端是解决不了问题的进入MySQL或者在客户端终端按照以下命令show processlist ;--à找到State处于Copy to tmp ..这样的SQL对应的Id号kill XXXX ;才算真正控制了这个SQL的执行。B)、要清楚“的确”存在能把数据库整死的纯查询SQL看起来不起眼但威力很大有些是因为MySQL本身的BUG例如我遇到的两个SELECT COUNT(*) FROM (SELECT Customer_ID FROM JHF_DEPOSITUNION ALLSELECT Customer_IDFROM JHF_WITHDRAWAL ORDER BY CustomerID) A (在MySQL 5.0.33中)因为在子查询中Order by了一个不存在的字段不是报语句的错误而是直接将MySQL数据库重启了。select A.CC,A.bid,A.ask,A.rateTime,(select ratetime From wxlTemp B where B.CCA.CC and B.bid A.bid and B.ask A.bid and B.ratetime A.ratetime Order by ratetime limit 1) as LastTimeFrom wxlTemp Awhere A.RateTime Order by A.CC,A.ratetime这个SQL也会导致MySQL数据库重启。二、有关分页相关的1.分页查询时通常一页记录为几十条每次只需要查询当页的记录。当有复杂的查询sql时我们要将sql分解提炼出必要的影响结果集的sql用于分页查询这个sql只包含一部分主要的表在分页查询执行后再查询这一页记录对应的其它表的记录。因为记录数只有一页了那么其它表的查询的性能将会很好这部分是需要在java程序中处理的。2.如果仅仅统计表记录数量那么就不要使用order by。3.对于分页查询通常需要显示符合条件的总记录数、页码、当页条数。这样就需要执行两次数据库查询一次是计算总记录数一次是检索当页全部记录。对于复杂sql建议将这两次查询使用的sql分开。这么做的原因是比如在FX项目中分页方法一般都是将sql直接进行解析根据from来拆分成统计记录数和返回结果集的sql。对于返回当页记录的sql来说一些where条件和表关联是必要的因为可能其中一些只是为了在select中包含部分表的字段但是对于统计记录数的sql来说只需要那些影响结果记录数的必要条件和关联的表即可。比如select * from tableA inner join tableB on(tableA.c1tableB.c1)left outer join tableC on (tableC.c2tableA.c2)tableA和tableB的记录是一对一的关系通用分页方法会将统计记录数的sql分解为类似下面这样select count(*) from tableA inner join tableB on(tableA.c1tableB.c1)left outer join tableC on (tableC.c2tableA.c2)但是tableB是不需要关联的因为不影响记录数。那么我们单独写一个统计记录数的sqlselect count(*) from tableA inner join tableB on(tableA.c1tableB.c1)二、如何避免出现锁冲突及死锁1对一个象Fx这样的分布系统同时操作注文约定逻辑几个表这样的模块有很多一定要在一个事务中确保所有的模块对操作相同的几个表的顺序都一致避免多个进程间对表产生死锁。2对由不同的模块更新相同的一批记录也可能存在记录间出现死锁的情况所以对事务操作比较密集的地方尽量对操作的记录进行按照一个统一的顺序进行比如升序或者降序。3对更新比较频繁的表一定要使用INNODB的表而不要使用MyISAM因为MyISAM的每一次更新都将是锁住整个的表而大大降低了更新的并发性能。4在现有的系统中我们使用的事务隔离级别是READ_COMMITED在一个事务中它会对更新的记录进行加锁这里的“更新的记录”比较微妙它锁定的范围是和更新的语句的where条件密切相关想要达到行锁的效果Update语句的条件一定要加上索引最好是主键或者唯一键因为这样的锁会很本分确定的记录比较明确。5要尽量保证事务不要过大小事务发生锁冲突的几率较小。三、如何优化对每个SQL语句在执行之前做一下EXPLAIN检查查看是否都使用了索引是否使用了有效的索引看是否扫描了很多行数据。http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain对索引的创建也要把握精而不滥的原则对特殊的表可以考虑只在Slave上建立。1索引的建立对提高检索能力很有用但是数据库维护它很费资源。2索引只使用开头的部分。key (a,b) .... where b5will not use index.3,建立一个对检索有用的索引index on gender is not agood idea例如在性别上建索引不是很有用。4对唯一建的索引加上UNIQUE。5避免出现无用的索引。(从来没被调用)6索引的顺序很重要。7不要在同列(s)上建立两个索引。8充分用别的组合索引的前面部分是个相当好的主意。9可以只对一个字段的前几个字段建立索引。10短一些的索引比较好整数最好。(Short keys are better, Integer best)11,有规律的值 比没有规律的随机的数要好。– access locality is muchbetter– auto_increment better thanuuid()12记得经常 优化表这样能压缩和排序索引项。OPTIMIZE TABLE compact andsort indexes13分析表能更新表的统计信息这样对检索很有好处。ANALYZE TABLE - updatestatistics14.利用索引并不一定能提高性能如果返回结果集数量很大甚至接近全表记录数时那么全表扫描的效率更高。通过索引再定位到物理记录这个过程会比较耗费时间。附录MySQL中通过show status对得到的值进行计算得到后的值大家可以参考。1,连接失败的监控.■监视点:连接失败的百分比。■公式: Aborted_connects*100 / Connections■正常范围:小于10%.■含义:应用程序连接服务器失败的比例,一般原因有:未授权访问数据库/密码错误/连接超时等.2,最大情况下的连接使用百分比.■监视点:最大情况下的连接使用百分比。■公式: Max_used_connections /max_connections■正常范围:小于75%.■含义:从开机到现在的最大连接情况,表示的是这段时间的峰值,对繁忙的系统这个很有参考意义.3,MyISAM索引缓存命中率■监视点: key_buffer_size的设置是否适当。■公式: 1-(Key_reads / Key_read_requests)■正常范围: 95%以上.■含义:增大key_buffer_size并且监控缓存利用率。当命中率到达了一个可接收的水平保存key_buffer_size值到MySQL配置文件中。需要MySQL运行一个合理时间后查看命中率才有意义。4,InnoDB缓存命中率■监视点: innodb_buffer_pool_size的设置是否适当。■公式: 100* (1 - (Innodb_buffer_pool_reads /Innodb_buffer_pool_read_requests))■正常范围: 95%以上.■含义:数据和索引在缓存中读取的比率。从内存读取要比磁盘读取块很多因此要尽量保持物理I/O最少。当使用InnoDB大多数的访问应该在内存中因此这个值要很高。5,InnoDB缓存写入等待率■监视点: innodb_buffer_pool_size的设置是否适当。■公式: 100* (Innodb_buffer_pool_wait_free /Innodb_buffer_pool_write_requests)■正常范围: 1%以下.■含义:为了最佳性能InnoDB不应等待页写入到InnoDB缓冲池中。6,InnoDB回滚日志写入的等待比率■监视点: innodb_log_buffer_size的设置是否适当。■公式: 100* (Innodb_log_waits /Innodb_log_writes)■正常范围: 1%以下.■含义:为了最佳性能InnoDB不应等待SQL操作写入到日志。7,线程缓存大小设定值是否合适.■监视点: thread_cache_size的设置是否适当。■公式: (1-Threads_created/Connections ) *100%■正常范围: 95%以上.■含义:每个MySQL连接都运行在它特有的线程中。线程建立比较耗时因此每个连接关闭的时候不是杀死线程。服务起能保存线程在线程缓存中稍后为新的连接使用,线程缓存命中率。如果这个值太小那么就要考虑增加线程缓存的大小。8,表打开操作是否频繁..■监视点: table_cache的设置是否适当。■公式: Opened_tables的值,服务器运行一段时间后的值,要是一直在增长,那么就是有问题.■正常范围: 0 .■含义: MySQL每次访问表就把它放在表缓存中。如何系统访问很多表那么在缓存中会更快一些。Opened_tables就是没有通过表缓存中打开表的数量,如果这个数值高或者增长的很快那么就需要增加table_cache的值。9,查询缓存碎片情况.■监视点:查询缓存中各个使用块的情况,如果单个块中有空闲的,那么此项监控就高.■公式: 100 * Qcache_free_blocks /Qcache_total_blocks■正常范围:低于70% .■含义:如果你有很多小的查询结果,这个值可能会很高,请考虑下面的选项1、减少query_cache_min_res_unit值2、执行FLUSH QUERY CACHE对查询缓存进行碎片整理。10,查询修剪(从缓存中删除因为内存不够)与插入查询缓存的比率。■监视点:从查询缓存中删除的总体量和插入的的比例.■公式: Qcache_lowmem_prunes / Qcache_inserts■正常范围:■含义:放入缓存的数量与 被迫从缓存中挤出去的数量的比值.被挤的情况有某个查询结果集太久没有复用,来了新的结果集,缓存中没有空了.也可能是,缓存的结果集涉及到的表更新比较频繁,在下次利用的时候,发现已经是脏的数据了,于是就挤出来,在重新装载.这个值能反映出查询缓存是不是一个稳定的查询缓存,有没有必要使用查询缓存.11,查询缓存命中率(从缓存中删除因为内存不够)与插入查询缓存的比率。■监视点:一个查询的结果能被复用的比例.■公式: Qcache_hits / (Qcache_inserts Qcache_hits)■正常范围:■含义:查询缓存应该为此一个高的命中率。高命中率表示其他的连接可以使用查询缓存中结果。低命中率说明没有足够的内存分配给它或者查询没有在服务器上再三的执行。12,sort_buffer_size的大小是否合适.■监视点:排序算法已经执行的合并的数量。如果这个变量值较大应考虑增加sort_buffer_size系统变量的值。■公式: Sort_merge_passes■正常范围:■含义:13,read_rnd_buffer_size的大小是否合适.■监视点:暂时无■公式:■正常范围:■含义:当排序后按排序后的顺序读取行时则通过该缓冲区读取行避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是这是为每个客户端分配的缓冲区因此你不应将全局变量设置为较大的值。相反只为需要运行大查询的客户端更改会话变量。14, read_rnd_buffer_size的大小是否合适.■监视点:表锁的次数.■公式:Table_locks_waited / (Table_locks_waited Table_locks_immediate)■正常范围:10%以内■含义:对MyISAM表,所表是发生在读和写他们两两之间的,是并发性很低的,所以如果这个值高的话,需要拷考虑进行表类型的更改.15, Percentage of full table scans .■监视点:全表扫描的比率.■公式:(Handler_read_rnd_next Handler_read_rnd) / (Handler_read_rnd_next Handler_read_rnd Handler_read_first Handler_read_next Handler_read_key Handler_read_prev)■正常范围:20%以内■含义:要尽力保持很小的值。设法隔离没使用索引的查询。使用慢查询日志记录哪些运行时间较长的查询。16, Select_full_join .■监视点:没有使用索引的联接的数量.■公式:Select_full_join■正常范围:20%以内■含义:没有使用索引的联接的数量。如果该值不为0,你应仔细检查表的索引。。17, binlog_cache_size的大小是否合适.■监视点:表锁的次数.■公式:Binlog_cache_disk_use / Binlog_cache_use■正常范围:10%以内■含义:增加这个值并且监控这个值。当命中率达到可以接受的水平将binlog_cache_size参数添加到MySQL配置文件中。18, tmp_table_size,max_heap_table_size的大小是否合适.■监视点:使用临时表的次数.■公式:Created_tmp_disk_tables / Created_tmp_tables■正常范围:50%以内■含义:如果这个值太高了那么就要考虑增加tmp_table_size和max_heap_table_size大小。临时表的TEXT or BLOBS字段要保存在磁盘上因此设法改变TEXT或者BLOBS字段类型。(完)
