• 2009-09-25

    mysql 关于索引 - [mysql]

    MySQL索引 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

    CREATE TABLE mytable(

    ID INT NOT NULL,

    username VARCHAR(16) NOT NULL

    );

    我们随机向里面插入了10000条记录,其中有一条:5555, admin。

    在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描 所有记录,即要查询10000条记录。

    索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

    索引的类型包括:

    (1)普通索引
    这是最基本的索引,它没有任何限制。它有以下几种创建方式:

    ●     创建索引

    CREATE INDEX indexName ON mytable(username(length));

    如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

    ●     修改表结构

    ALTER mytable ADD INDEX [indexName] ON (username(length))

    ●     创建表的时候直接指定

    CREATE TABLE mytable(

    ID INT NOT NULL,

    username VARCHAR(16) NOT NULL,

    INDEX [indexName] (username(length))

    );

    删除索引的语法:

    DROP INDEX [indexName] ON mytable;

    (2)唯一索引
    它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

    ●     创建索引

    CREATE UNIQUE INDEX indexName ON mytable(username(length))

    ●     修改表结构

    ALTER mytable ADD UNIQUE [indexName] ON (username(length))

    ●     创建表的时候直接指定

    CREATE TABLE mytable(

    ID INT NOT NULL,

    username VARCHAR(16) NOT NULL,

    UNIQUE [indexName] (username(length))

    );

    (3)主键索引
    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

    CREATE TABLE mytable(

    ID INT NOT NULL,

    username VARCHAR(16) NOT NULL,

    PRIMARY KEY(ID)

    );

    当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

    (4)组合索引
    为了形象地对比单列索引和组合索引,为表添加多个字段:

    CREATE TABLE mytable(

    ID INT NOT NULL,

    username VARCHAR(16) NOT NULL,

    city VARCHAR(50) NOT NULL,

    age INT NOT NULL

    );

    为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

    ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

    建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

    如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

    usernname,city,age

    usernname,city

    usernname

    为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

    SELECT * FROM mytable WHREE username="admin" AND city="郑州"

    SELECT * FROM mytable WHREE username="admin"

    而下面几个则不会用到:

    SELECT * FROM mytable WHREE age=20 AND city="郑州"

    SELECT * FROM mytable WHREE city="郑州"

    (5)建立索引的时机
    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为 MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    SELECT t.Name

    FROM mytable t LEFT JOIN mytable m

    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    SELECT * FROM mytable WHERE username like'admin%'

    而下句就不会使用:

    SELECT * FROM mytable WHEREt Name like'%admin'

    因此,在使用LIKE时应注意以上的区别。

    (6)索引的不足之处
    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    ●     虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    ●     建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

    索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    (7)使用索引的注意事项
    使用索引时,有以下一些技巧和注意事项:

    ●     索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    ●     使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    ●     索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    ●     like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    ●     不要在列上进行运算

    select * from users where YEAR(adddate)<2007;将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成select * from users where adddate<‘2007-01-01’;

    ●     不使用NOT IN和<>操作

    NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以使用NOT EXISTS代替,id<>3则可以使用id>3 or id<3来代替。

    Tag:Mysql
  • 2009-09-22

    用nginx的反向代理做负载均衡 - [service]

    1. ngnix之所以比apache高效,得益于Nginx使用了最新的epoll(Linux 2.6内核)和kqueue(freebsd)网络I/O模型,而Apache则使用的是传统的select模型。目前Linux下能够承受高并发访问的 Squid、Memcached都采用的是epoll网络I/O模型。也就是说,在高并发服务器中,轮询(round-robin)I/O是最耗时间的操作。

    2. 反向代理工作原理
    反向代理服务器位于本地WEB服务器和Internet之间.
    当用户浏览器发出一个HTTP请求时,通过域名解析将请求定向到反向代理服务器(如果要实现多个WEB服务器的反向代理,需要将多个WEB服务 器的域名都指向反向代理服务器)。由反向代理服务器处理器请求。反向代理一般只缓存可缓冲的数据(比如html网页和图片等),而一些CGI脚本程序或者 ASP之类的程序不缓存。它根据从WEB服务器返回的HTTP头标记来缓冲静态页面。有四个最重要HTTP头标记:

    Last-Modified: 告诉反向代理页面什么时间被修改
    Expires: 告诉反向代理页面什么时间应该从缓冲区中删除
    Cache-Control: 告诉反向代理页面是否应该被缓冲
    Pragma: 告诉反向代理页面是否应该被缓冲.
    例如:在默认情况下,ASP页面返回” Cache-control: private.” ,所以ASP页面时不会在反向代理服务器缓存的。

    ----------------------------------------------------------------------------------

    3. ngnix最常见的用法之一就是作为反向代理使用,安装和配置相当简便,对于负载均衡它使用的是最简单的轮询算法(round-robin),不过效果还是很不错的。

    要用nginx做负载均衡的话,首先要在配置文件里面定义一组用来负载均衡的后端服务器(backend servers),例如:
    upstream backend {
      server 192.168.1.11;
      server 192.168.1.12;
      server 192.168.1.13;
    }

    那个server指令的语法是 server name [parameters],这里的name是服务器名,可以是域名、ip或者unix socket,也可以指定端口,例如:
    server 192.168.1.11:8080;

    server指令可用的参数有:

    weight —— 设置服务器的权重,默认值是1,权重值越大那么该服务器被访问到的几率就越大,例如 server 192.168.1.11 weight=5;

    max_fails和fail_timeout —— 这俩是关联的,如果某台服务器在fail_timeout时间内出现了max_fails次连接失败,那么nginx就会认为那个服务器已经挂掉,从而在 fail_timeout时间内不再去查询它,fail_timeout的默认值是10s,max_fails的默认值是1(这意味着一发生错误就认为服 务器挂掉),如果把max_fails设为0则表示把这个检查取消。
    举个例子:server 192.168.1.11 max_fails=3 fail_timeout=30s; 这表示,如果服务器192.168.1.11在30秒内出现了3次错误,那么就认为这个服务器工作不正常,从而在接下来的30秒内nginx不再去访问这个服务器。

    down —— 表示该服务器已经停用,例如server 192.168.1.11 down;

    backup —— 表示该服务器是备用服务器,只有其它后端服务器都挂了或者很忙才会访问到。

    关于upstream的更多信息请参考 http://wiki.nginx.org/NginxHttpUpstreamModule

    定义好了一组后端服务器,就该来设置nginx的反向代理配置了,例如:
    server {
      listen 80;
      server_name www.domain.com;
      location / {
        proxy_pass http://backend;
      }
    }

    关于nginx的proxy有很多配置参数的,具体可以参考 http://wiki.nginx.org/NginxHttpProxyModule
    这里有个配置可能比较有用:
    proxy_set_header Host $host;
    这表示传递给后端服务器的头部信息里面的Host信息保持跟客户端传过来的一致,如果不设置那么默认传给后端服务器的值是$proxy_host,所以不 设置可能会有问题,例如:后端服务器会处理两个域名的请求,例如www.a.com和www.b.com,这时如果nginx不把客户端的请求信息中的 host信息传给后端服务器,那么后端服务器完全不知道到底这个客户是想访问域名a还是域名b,从而导致传输数据可能不正确。
    如果你想把客户端的ip也传给后端服务器的话,可以用这个设置:
    proxy_set_header X-Real-IP $remote_addr;

    4. 负载均衡
    参考:http://blog.s135.com/nginx_php_v5/
  • 2009-09-14

    http_load 一个httpd 压力测试工具 - [service]

    到http://www.acme.com/software/http_load/ 下载http_load ,安装也很简单直接make;make instlall 就行。

    http_load的标准的两个例子是:

    1. http_load -parallel 5 -fetches 1000 urls.txt  
    2. http_load -rate 2 -seconds 300 urls.txt  

     

    例子只是个参考,参数其实可以自由组合,参数之间的选择并没有什么限制。比如你写成http_load -parallel 5 -seconds 300 urls.txt也是可以的。我们把参数给大家简单说明一下。-parallel 简写-p 含义是并发的用户进程数。

    -fetches 简写-f 含义是总计的访问次数

    -rate    简写-p 含义是每秒的访问频率

    -seconds简写-s 含义是总计的访问时间


    urls.txt 是一个url 列表,每个url 单独的一行。当然也可以直接跟一个url 而不是url 列表文件。
    实例:
    1. http_load -rate 5 -seconds 10 urls  
    2. 49 fetches, 2 max parallel, 289884 bytes, in 10.0148 seconds  
    3. 5916 mean bytes/connection  
    4. 4.89274 fetches/sec, 28945.5 bytes/sec  
    5. msecs/connect: 28.8932 mean, 44.243 max, 24.488 min  
    6. msecs/first-response: 63.5362 mean, 81.624 max, 57.803 min  
    7. HTTP response codes:  
    8.   code 200 -- 49  
     
    分析:
    1.49 fetches, 2 max parallel, 289884 bytes, in 10.0148 seconds
    说明在上面的测试中运行了49个请求,最大的并发进程数是2,总计传输的数据是289884bytes,运行的时间是10.0148秒

    2.5916 mean bytes/connection
    说明每一连接平均传输的数据量289884/49=5916

    3.4.89274 fetches/sec, 28945.5 bytes/sec
    说明每秒的响应请求为4.89274,每秒传递的数据为28945.5 bytes/sec

    4.msecs/connect: 28.8932 mean, 44.243 max, 24.488 min
    说明每连接的平均响应时间是28.8932 msecs,最大的响应时间44.243 msecs,最小的响应时间24.488 msecs

    5.msecs/first-response: 63.5362 mean, 81.624 max, 57.803 min

     6、HTTP response codes: code 200 -- 49
    说明打开响应页面的类型,如果403的类型过多,那可能要注意是否系统遇到了瓶颈。
    特殊说明:这里,我们一般会关注到的指标是fetches/sec、msecs/connect
    他们分别对应的常用性能指标参数Qpt-每秒响应用户数和response time,每连接响应用户时间。测试的结果主要也是看这两个值。当然仅有这两个指标并不能完成对性能的分析,我们还需要对服务器的cpu、men进行分析,才能得出结论