Mysql大数据量高并发的数据库优化

Mysql大数据量高并发的数据库优化一、数据库结构的设计如果不能设计一个合理的数据库模型，不仅会增加客户端和服务器段程序的编程和维护的难度，而且将会影响系统实际运行的性能。所以，在一个系统开始实施之前，完备的数据库模型的设计是必须的。在一个系统分析、设计阶段，因为数据量较小，负荷较低。我们往往只注意到功能的实现，而很难注意到性能的薄弱之处，等到系统投入实际运行一段时间后，才发现系统的性能在降低，这时再来考虑提高系统性能则要花费更多的人力物力，而整个系统也不可避免的形成了一个打补丁工程。所以在考虑整个系统的流程的时候，我们必须要考虑，在高并发大数据量的访问情况下，我们的系统会不会出现极端的情况。（例如：对外统计系统在7月16日出现的数据异常的情况，并发大数据量的的访问造成，数据库的响应时间不能跟上数据刷新的速度造成。具体情况是：在日期临界时（00：00：00），判断数据库中是否有当前日期的记录，没有则插入一条当前日期的记录。在低并发访问的情况下，不会发生问题，但是当日期临界时的访问量相当大的时候，在做这一判断的时候，会出现多次条件成立，则数据库里会被插入多条当前日期的记录，从而造成数据错误。），数据库的模型确定下来之后，我们有必要做一个系统内数据流向图，分析可能出现的瓶颈。为了保证数据库的一致性和完整性，在逻辑设计的时候往往会设计过多的表间关联，尽可能的降低数据的冗余。（例如用户表的地区，我们可以把地区另外存放到一个地区表中）如果数据冗余低，数据的完整性容易得到保证，提高了数据吞吐速度，保证了数据的完整性，清楚地表达数据元素之间的关系。而对于多表之间的关联查询（尤其是大数据表）时，其性能将会降低，同时也提高了客户端程序的编程难度，因此，物理设计需折衷考虑，根据业务规则，确定对关联表的数据量大小、数据项的访问频度，对此类数据表频繁的关联查询应适当提高数据冗余设计但增加了表间连接查询的操作，也使得程序的变得复杂，为了提高系统的响应时间，合理的数据冗余也是必要的。设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。另外，最好不要用自增属性字段作为主键与子表关联。不便于系统的迁移和数据恢复。对外统计系统映射关系丢失（******************）。原来的表格必须可以通过由它分离出去的表格重新构建。使用这个规定的好处是，你可以确保不会在分离的表格中引入多余的列，所有你创建的表格结构都与它们的实际需要一样大。应用这条规定是一个好习惯，不过除非你要处理一个非常大型的数据，否则你将不需要用到它。（例如一个通行证系统，我可以将USERID，USERNAME，USERPASSWORD，单独出来作个表，再把USERID作为其他表的外键）表的设计具体注意的问题：1、数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片，降低查询效率。2、能够用数字类型的字段尽量选择数字类型而不用字符串类型的（电话号码），这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。3、对于不可变字符类型char和可变字符类型varchar都是8000字节,char查询快，但是耗存储空间，varchar查询相对慢一些但是节省存储空间。在设计字段的时候可以灵活选择，例如用户名、密码等长度变化不大的字段可以选择CHAR，对于评论等长度变化大的字段可以选择VARCHAR。4、字段的长度在最大限度的满足可能的需要的前提下，应该尽可能的设得短一些，这样可以提高查询的效率，而且在建立索引的时候也可以减少资源的消耗。二、查询的优化保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如Select*FROMT1语句，要用到几列就选择几列如：SelectCOL1,COL2FROMT1；在可能的情况下尽量限制尽量结果集行数如：SelectTOP300COL1,COL2,COL3FROMT1,因为某些情况下用户是不需要那么多的数据的。在没有建索引的情况下，数据库查找某一条数据，就必须进行全表扫描了，对所有数据进行一次遍历，查找出符合条件的记录。在数据量比较小的情况下，也许看不出明显的差别，但是当数据量大的情况下，这种情况就是极为糟糕的了。SQL语句在SQLSERVER中是如何执行的，他们担心自己所写的SQL语句会被SQLSERVER误解。比如：select*fromtable1wherename='zhangsan'andtID10000和执行:select*fromtable1wheretID10000andname='zhangsan'一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID10000来提出查询结果。所以，优化查询最重要的就是，尽量使语句符合查询优化器的规则避免全表扫描而使用索引查询。具体要注意的：1.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=02.应尽量避免在where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。3.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=204.in和notin也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and35.尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。6.见如下例子：Select*FROMT1WhereNAMELIKE‘%L%’Select*FROMT1WhereSUBSTING(NAME,2,1)=’L’Select*FROMT1WhereNAMELIKE‘L%’即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。6.必要时强制查询优化器使用某个索引，如在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：可以改为强制查询使用索引：selectidfromtwith(index(索引名))wherenum=@num7.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：Select*FROMT1WhereF1/2=100应改为:Select*FROMT1WhereF1=100*2Select*FROMRECORDWhereSUBSTRING(CARD_NO,1,4)=’5378’应改为:Select*FROMRECORDWhereCARD_NOLIKE‘5378%’Selectmember_number,first_name,last_nameFROMmembersWhereDATEDIFF(yy,datofbirth,GETDATE())21应改为:Selectmember_number,first_name,last_nameFROMmembersWheredateofbirthDATEADD(yy,-21,GETDATE())即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectidfromtwheresubstring(name,1,3)='abc'--name以abc开头的idselectidfromtwheredatediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id应改为:selectidfromtwherenamelike'abc%'selectidfromtwherecreatedate='2005-11-30'andcreatedate'2005-12-1'9.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。11.很多时候用exists是一个好的选择：electnumfromawherenumin(selectnumfromb)用下面的语句替换：selectnumfromawhereexists(select1frombwherenum=a.num)SelectSUM(T1.C1)FROMT1Where((SelectCOUNT(*)FROMT2WhereT2.C2=T1.C20)SelectSUM(T1.C1)FROMT1WhereEXISTS(Select*FROMT2WhereT2.C2=T1.C2)两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：IF(SelectCOUNT(*)FROMtable_nameWherecolumn_name='xxx')可以写成：IFEXISTS(Select*FROMtable_nameWherecolumn_name='xxx')经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：Selecta.hdr_keyFROMhdr_tbla----tbla表示tbl用别名a代替WhereNOTEXISTS(Select*FROMdtl_tblbWherea.hdr_key=b.hdr_key)Selecta.hdr_keyFROMhdr_tblaLEFTJOINdtl_tblbONa.hdr_key=b.hdr_keyWhereb.hdr_keyISNULLSelecthdr_keyFROMhdr_tblWherehdr_keyNOTIN(Selecthdr_keyFROMdtl_tbl)三种写法都可以得到同样正确的结果，但是效率依次降低。12.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。13.避免频繁创建和删除临时表，以减少系统表资源的消耗。14.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但