正在加载中……
最新消息:欢迎大家访问,导航分类不完善,如果找不到自己想要的文章,可以通过【关键词】来进行搜索,^_^!

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

数据库 REKFAN.COM 6087浏览 0评论

更多


普通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字段等等。http://blog.rekfan.com/articles/114.html
但是索引对于唯一值比较少的字段,比如性别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和内存足够运行多个守护进程。
优点 是能够很好的缓解暂时服务器的压力,把不同的操作放在不同的端口,或者把不同的项目模块放在不同的端口去操作,良好的分担单个守护进程的压力。 http://blog.rekfan.com/articles/114.html
缺点 是数据可能会产生紊乱,同时可能会导致很多未知的莫名错误。呵呵

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,同样分离他们的更新和查询操作,或者可以设计成双向同步。同时,你的应用程序必须支持跨数据库和跨服务器的操作能力。http://blog.rekfan.com/articles/114.html
优点 是服务器的负载合理的被平摊,每台服务器都是负责一部分用户,如果一台服务器down掉了,不会影响其他用户ID的用户正常访问。同时添加节点比较容易,如果又增加了10万用户,那么又可以增加一个节点服务器,升级很方便。
缺点 是任何一台数据库服务器down掉或者数据丢失,那么这部分服务器的用户将很郁闷,数据都没了,当然,这个需要良好的备份机制。

相关误区:

关于count(*)函数执行时间长,效率低的问题?

count函数的效率取决于你选择的存储引擎,MyISAM存储引擎,本身就存储了关于count函数的相关数据,不需要计算的,当你select count(*) from `tablename`的时候立即就返回了存储在此引擎中的相关数据,网上很多都说count(*)非常慢,纯属忽悠人,此存储引擎使用count(*)或者count(1),count(id)的效率是一样的。因为MySQL内部有表记录了每个表的记录数,这个语句SELECT count(*) FROM table_name实际上只查询一条记录。
上面的语句相当于
SELECT table_rows FROM information_schema.tables where table_name='table_name'

而innodb引擎就不同了,此引擎没有存储相关的数据,每次使用count函数的时候,都需要先计算,然后返回结果,这样如果数据量大了就非常慢了。
所以说,使用mysql的count函数的效率很低,不是很确切,也不能说是错的,因为它的效率取决于你数据表所使用的存储引擎。

本文转自:http://blog.csdn.net/pigqu/archive/2007/01/04/1473672.aspx ,略有修改
 


 

转载请注明:|REKFAN|系统运维| » MySQL中SQL优化和架构设计的一些简单想法

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址