MySQL中SQL优化和架构设计的一些简单想法

然鲮廷

普通MySQL运行，数据量和访问量不大的话，是足够快的，但是当数据量和访问量剧增的时候，那么就会明显发现MySQL很慢，甚至down掉，那么就要考虑优化我们的MySQL了。
优化无非是从三个角度入手：
第一个是从硬件，增加硬件，增加服务器
第二个就是对我们的MySQL服务器进行优化，增加缓存大小，开多端口，读写分开
第三个就是我们的应用优化，建立索引，优化SQL查询语句，建立缓存等等
我就简单的说说SQL查询语句的优化。因为如果我们Web服务器比数据库服务器多或者性能优良的话，我们完全可以把数据库的压力转嫁到Web服务器上，因为如果单台MySQL，或者 Master/Slave 架构的数据库服务器都负担比较重，那么就可以考虑把MySQL的运算放到Web服务器上去进行。当然了，如果你Web服务器比数据库服务器差，那就把压力放在数据库服务器上吧，呵呵。
如果是把MySQL服务器的压力放在Web服务器上，那么很多运算就需要我们的程序去执行，比如Web程序中全部交给PHP脚本去处理数据。单台MySQL服务器，查询、更新、插入、删除都在一台服务器上的话，访问量一大，你会明显发现锁表现象，当对一个表进行更新删除操作的时候，就会拒绝其他操作，这样就会导致锁表，解决这个问题最简单直接的办法就是拿两台MySQL服务器，一台负责查询（select）操作，另外一台负责更改（update/delete/insert），然后进行同步，这样能够避免锁表，如果服务器更多，那么就更好处理了，可以采用分布式数据库架构和数据的散列存储，惊天动地私服下面我们会简单说一下。
一、SQL的优化和注意事项
现在我们假设我们只有一台MySQL服务器，所有的select/update/insert/delete操作都是在这上面进行的，我们同时有三台Web服务器，通过DNS轮巡来访问，那么我们如何进行我们应用程序和SQL的优化。
1. Where条件
在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，在写每一个where条件的时候都要仔细考虑，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，这样就会减少后一个where条件的查询时间。
有时候一些where条件会导致索引无效，当使用了Mysql函数的时候，索引将无效，比如：select * from tbl1 where left(name, 4) = ''hylr''，那么这时候索引无效，还有就是使用LIKE进行搜索匹配的时候，这样的语句索引是无效的：select * from tbl1 where name like ''%xxx%''，但是这样索引是有效的：select * from tbl1 where name like ''xxx%''，所以谨慎的写你的SQL是很重要的。
2. 关联查询和子查询
数据库一个很重要的特点是关联查询，LEFT JOIN 和全关联，特别是多个表进行关联，因为每个关联表查询的时候，进行扫描的时候都是一个笛卡尔乘积的数量级，扫描数量很大，如果确实是需要进行天龙八部私服关联操作，请给where或者on的条件进行索引。
关联操作也是可能交给应用去操作的，看数据量的大小，如果数据量不是非常大，比如10万条以下，那么就可以交给程序去处理（totododo提出笔误，特此修正），程序分别提取左右两个表的数据，然后进行循环的扫描处理，返回结果，这个过程同样非常耗费Web服务器的资源，那么就需要取决于你愿意把压力放在Web服务器上或者数据库服务器上了。
子查询是在mysql5中支持的功能，比如：select * from tbl1 where id in(select id from tbl1)，那样效率是非常非常低，要尽量避免使用子查询，要是我，绝对不用真封神私服，呵呵。
3.   一些耗费时间和资源的操作
SQL语句中一些浪费的操作，比如 DISTINCT、COUNT、GROUP BY、各种MySQL函数。这些操作都是比较耗资源的，我想应用最多的是count字句吧，如果使用count，尽量不要count（*），最好count一个字段，比如count（id），或者count(1)，（据totododo测试效率其实是一样的），同样能够起到统计的作用。如果不是十分必要，尽量不要使用distinct操作，就是提取唯一值，你完全可以把这个操作交给脚本程序去执行提取唯一值，减少MySQL的负担。group by 操作也是，确实需要分组的话，请谨慎的操作，如果是小批量的数据，可以考虑交给脚本程序去做。
至于MySQL的函数，估计很多常用，比如有人喜欢把截取字符串也交给MySQL去操作，或者时间转换操作，使用比较多的函数像 SUBSTR(), CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5() 等等，这些操作完全可以交给脚本程序去做，减轻MySQL的负担。
4. 合理的建立索引
索引的提升速度的一个非常重要的手段，索引在对一些经常进行select操作，并且值比较唯一的字段是相当有效的，比如主键的id字段，唯一的名字name字段等等。
但是索引对于唯一值比较少的字段，比如性别gender字段，寥寥无几的类别字段等，意义不大，因为性别是50%的几率，索引几乎没有意义。对于update/delete/insert非常频繁的表，建立索引要慎重考虑，因为这些频繁的操作同样对于索引的维护工作量也是很大的，最后反而得不偿失，这个需要自己仔细考虑。索引同样不是越多越好，适当的索引会起到很关键的作用，不适当的索引，反而减低效率维护，增加维护机战私服索引的负担。
5. 监控sql执行效率
在select语句前面使用EXPLAIN字句能够查看当前这个select字句的执行情况，包括使用了什么操作、返回多少几率、对索引的使用情况如何等等，能够有效分析SQL语句的执行效率和合理程度。
另外使用MySQL中本身的慢查询日志：slow-log，同样能够记录查询中花费时间比较多的SQL语句，好对相应的语句进行优化和改写。
另外在MySQL终端下，使用show processlist命令能够有效的查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。
二、数据库服务器的架构和分布想法
对于服务器的架构设计，这个其实是比较重要的，一个合理的设计，能够让应用更好的运行。当然，架构的设计，取决于你的应用和你硬件的实际情况。我就简单的说说几种不同的数据库架构设计方式，权当是一个个人的想法，希望能够有帮助。
1. 单台服务器开多进程和端口
单台MySQL服务器，如果使用长链接等等都无法解决负载太大，连接太多的问题，不凡考虑采用一台MySQL上使用多个端口开启多个MySQL守护进程的方法来缓解压力。当然，前提是你的应用必须支持多端口，并且你的cpu和内存足够运行多个守护进程。
优点 是能够很好的缓解暂时服务器的压力，把不同的操作放在不同的端口，或者把不同的项目模块放在不同的端口去操作，良好的分担单个守护进程的压力。
缺点 是数据可能会产生紊乱，同时可能会导致很多未知的莫名风云私服错误。呵呵
2. 使用Master/Slave的服务器结构
Mysql本身具有同步功能，完全可以利用这个功能。构建 Master/Slave 的主从服务器结构，最少只需要两台MySQL服务器，我们可以把 Master 服务器用户更新操作，包括 update/delete/insert，把Slave服务器用于查询操作，包括 select 操作，然后两机进行同步。
优点 是合理的把更新和查询的压力分担，并且能够避免锁表的问题。
缺点 是更新部实时，如果网络繁忙，可能会存在延迟的问题，并且任何一台服务器down掉了都很麻烦。
3. 使用分布式的散列存储
这种结构适合大数据量，并且负载比较大，然后服务器比较充足的情况。分布式存储结构，简单的可以是多台服务器，每台服务器功能是类似的，但是存储的数据不一样，比如做一个用户系统，那么把用户ID在1-10万以内的存储在A服务器，用户ID在10-20万存储在B服务器，20-3-万存储在C服务器，以此类推。如果每个用户访问的服务器不足，可以构建组服务器，就是每组用户拥有多台服务器，比如可以在某用户组建立两台MySQL服务器，一台Master，一台Slave，同样分离他们的更新和查询操作，或者可以设计成双向同步。同时，你的应用程序必须支持跨数据库和跨服务器的操作能力。
优点 是服务器的负载合理的被平摊，每台服务器都是负责一部分用户，如果一台服务器down掉了，不会影响其他用户ID的用户正常访问。同时添加节点比较容易，如果又增加了10万用户，那么又可以增加一个节点服务器，升级很方便。
缺点 是任何一台数据库服务器down掉或者数据丢失，那么这部分服务器的用户将很郁闷，数据都没了，当然，这个需要良好的备份机制。
另外，MySQL5.1已经有中文手册，第七章详细的讲解了MySQL优化的知识，值得学习：
说明：以上纯属我个人的一些不成熟的观点和想法，同时部分想法是没有经过试验的，不能保证准确性，所以请读者自己试验考察，也希望这些想法能够有一些帮助。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
如果大家有异议，可以在后面补充。我会随时更新惊天动地私服的。

现在大概列出如下望各位补充)
1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not null,除非这个字段需要null.（这个规则只适合字段为KEY的情形）
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.（CHAR 总是比VARCHR快）
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
（这条只适合MYISAM引擎的表，对于INNODB则在保存记录的时候关系不大，因为INNODB是以事务为基础的，如果想快速保存记录的话，特别是大批量的导入记录的时候）
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
(具体的表现为：MYISAM表的MERGE类型，以及MYISAM和INNODB通用的分区，详情见手册)
9).不会用到外键约束的地方尽量不要使用外键。
2.系统的用途
1).及时的关闭对MYSQL的连接。
2).explain 复杂的SQL语句。（这样能确定你的SELECT 语句怎么优化最佳）
3).如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.（在数据庞大的时候建立INDEX）
4).LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.
5).如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
不过有一个问题，truncate 不会在事务处理中回滚。因为她要调用create table 真封神私服 语句。
（Truncate Table 语句先删除表然后再重建，这个是属于文件级别的，所以自然快N多）
实测例子：
song2为INNODB表。
mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
|   500000 |
+----------+
1 row in set (0.91 sec)

mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)

6).能使用STORE PROCEDURE 或者 USER FUNCTION的时候.（ROUTINE总是减少了服务器端的开销）
7).在一条insert语句中采用多重纪录插入奇迹世界私服格式.而且使用load data infile来导入大量数据，这比单纯的indert快好多.（在MYSQL中具体表现为：INSERT INTO TABLEQ VALUES (),(),...();）
（还有就是在MYISAM表中插入大量记录的时候先禁用到KEYS后面再建立KEYS,具体表现语句：
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
而对于INNNODB 表在插入前先 set autocommit=0;完了后：set autocommit=1;这样效率比较高。）
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。
3.系统的瓶颈
1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.
====

津海漪

超分贝英语   www.yiEnglish.com  

国内效果最好的英语学习软件！
让你在不知不觉中提高听力、口语、写作和阅读水平。
超分贝英语彻底打破传统的英语学习培训模式。

henly5052

免费刷qq7钻外挂,刷qq7钻工具,最新刷qq7钻工具外挂|免费使用中!

开通7钻软件,免费开通7钻,黑客学习如何免费开通7钻和各种QQ业务

别说我骗人你先加我（QQ308501138）看看我的六钻美丽不
应用软件来点亮6个钻石把以下的软件安装后，刷新QQ就可以看到图表！可亮
稀饭提示最好先用一个试试看能否点亮！别贪心。
一个钻石回复代码6次后方能点亮你想点亮的钻要不就几个月要不就是终身免费的有没有业务就不太清楚了~！   
注：只有全部粘帖后才能都显示出来 要不就都不显示了 绝对真实

http://www.aicaiwz.com   

源奇忍

本文由久久牌技网提供：
       各位朋友：欢迎光临我们久久牌技网！您能关注我们久久牌技网，这充分说明了您对我们网站的信任。
        最近，很多朋友通过QQ或者电话与本站联系，大多数朋友都谈及曾因学习牌技而上当受骗的经历。针对这个问题，本站帮您分析一下为什么会上当受骗：
        绝大多数牌技爱好者总觉得牌技手法越神奇越好。比如：
        一、在不接触牌的情况下，把2、3、5瞬间变成A、A、A；
        二、任何牌不用偷换、不用洗、任意打色，即可将大牌控给自己，小牌给对方；
        三、任何牌，任对方怎么洗、切牌，均可随意发到自己想要的牌；
        四、任他人怎样洗，只需从中一抽，即可让自己拿到三条K、九点、牛牛等大牌；
        五、普通扑克、麻将在不做任何手脚的情况下，可以快速从背面认出每一张牌的花色、点数；
        六、普通色子在涂上特制的药水以后，就可以随心所欲，想    打几点就打几点；
        七、手洗以及自动麻将机，一人对三人，随心所欲，想要什    么牌就摸到什么牌；
        八、普通的一副杂乱无章的扑克牌，随意洗两下，可以发出    多家大牌；
        九、利用特制的进口隐形眼镜，可以透视任何普通扑克牌；
        十、一秒钟电脑分析仪，可以在极短的时间内很清任何一副普通牌的花色及点数；
          。。。。。。
        等等这样的广告语，在很多传授牌技的网站或者路边的小广告上，比比皆是，同时在网站里，再给自己冠以“中国民间著名的”魔术千术大师”、“中国民间魔术家协会会员“、”资深反千专家“、”亚洲赌神“等等不切实际的头衔。看了这样的广告语，您会心动吗？朋友们，这哪是牌技手法啊，简直就是“齐天大圣”转世啊。说实话，如果我有广告上那么厉害，我绝对不会外传这样的绝技，我会选择去澳门、美国的拉斯维加斯赌场，去全世界各地的大赌场。我估计，如果真有这样的本领，那他完全可以在一个月之内成全球的首富。
        假如您是想学这样的手法，请别联系我们，我们做不到，因为我们没有那么高的本领，只能让您失望了。
        事实上，千术手法确实存在，但是完全没有广告上吹嘘的那么神奇。很多同行，抓住牌技爱好者们的心理，大吹特吹，其目的很简单，就是为了让求学者对他的手法佩服的五体投地，从而收取高额的学费。
        以前，我也听到同行们闲聊的时候谈到过，大多数的同行们都觉得，不吹牛不行，做这一行不吹牛就做不下去。由此我们可以想象到，那些交了钱学习牌技手法的朋友们会学到一些什么了。
        那么，真正实用的牌技手法是什么样呢？归纳起来说，手法无非就是通过洗、发、控、认、偷等来达到出千的目的。这其中又能分出很多种，在实战中，出千者要根据各地玩牌的规矩来决定使用哪种手法，有些牌局甚至需要几种出千手法来配合起来才能达到出千的目的。比方说：发二张，这是一种出千的手法，但是在实战中，你必须要知道牌的点数才能知道哪一张牌是你所需要的牌，否则就算你会发二张的手法，也无法出千。大家可以仔细想想，我说的是不是有一定的道理。
        有一些朋友对千术手法也会一点，但总是在出千的时候破绽百出，别人很容易就能看出其中有诈。这样一来，他就会觉得千术手法不实用，其实并不是手法不实用，而是你平时在练习的过程中没能克服掉你手法中存在的破绽，导致习惯成自然，其实学习牌技手法，主要是学习手法中的要领，在练习的过程中，注意手法中的要领，这样你的手法才会达到一个完美的境界。
        总的说来，千术手法确实存在，但是完全没有部分广告上说的那么神乎其神，如果您在看到广告并被其中的广告语所打动，产生了学习的冲动，那就是你被骗的开始。话是这样说，信不信还是取决于您自己。
        本站承诺：本站教学所有内容绝对不会出现类似情况。如果您曾经被骗并且还有还想学习实用的牌技手法；如果您觉得我们网站很实在，不像是骗人的，可以与我们联系，相信我们不会让您失望。