MySQL相关

1.将top命令的结果放在上面，让你说你从中了解到哪些信息。

 

2.将vmstar命令的结果放在上面，让你说出每个参数的含义。

 

3.请问目前系统中空闲内存为多少。

#free -m total        used           free    shared   buffers    cached Mem：        3520         3473         46        0       362      1381 -/+ buffers/cache:       1728           1791 Swap:        2055         1185           869

 

 

4.请简述下面几个参数的含义。

Active connections：291 serveraccepts handled requests 16630948 16630948 31070465 Reading： 6  Writing：179 Waiting： 106

 

 

5.iptables方面，请写出相应的iptables语句

1）本地80端口的请求转发到8080端口，当前主机IP为192.168.2.1

2）允许本机对外连接80端口，（本机能连外界服务器的80）

3）开放本机的3306端口

4）禁止外界ping本服务器

5）防止SYN攻击 轻量级预防

 

 

 

 

6.mysql高可用方案你知道哪几种，mysql备份方案有哪些，各有什么优缺点

 

 

7.写出apache2.x版本的两种工作模式，以及各自工作原理。如何查看apache当前所支持的模块，并且查看时工作在哪种模式下？

 

 

8.linux基础问题

1）linux怎么查看用户登录日志

2）Linux中utmp、wtmp、lastlog、messages各文件作用

3）列举下你熟悉的服务器性能查看命令

4）Linux服务器间怎么实现无密码登录。请列举操作步骤。

 

 

 

9.PHP类问题

1）如何查看php中安装的模块情况。安装好的php后如何动态添加php扩展模块

2）4G内存的服务器，php fastcgi子进程数配置多少个合适，说下你的理由

3）Nginx+php环境下nginx使用哪种方式连接gastcgi比较格式，说下理由

4）LNMP环境下，需要调整文件句柄数位65535，你会调哪些地方？

5）你知道哪些php代码加速器？各优点是什么？

 

 

 

 

10.软件类问题

1）keepalived和heartbeat优缺点，适用哪些场合。

2）Lvs haproxy nginx 各优缺点，适用哪些场合。

3）Apache nginx lighttpd各优缺点，适用哪些场合。

4）Squid varnish 各优缺点，适用哪些场合

5）Memcached redis 各优缺点，适用哪些场合。

6）介绍下lvs负载模式和调度算法，nginx负载均衡模块有哪几种算法。

 

 

 

 

 

11.设计一个pv为2千万的网站架构。（请用图画出网站架构）

 

 

 

 

网络知识部分…

1.简述OSI参考模型的七个层次。

 

 

2.请简述TCP协议的三次握手过程。

 

 

3.一个主机IP地址是202.110.14.137，掩码是255.255.255.224，要求计算这个主机所在网络的网络地址和广播地址。

 

 

信息安全部分…

1.IDC机房里发生ARP攻击，在服务器上抓包，通过分析数据包内容会看到什么现象？如何防止此类攻击。

 

 

2.IDC机房里有一台服务器上运行游戏服务，现在连接不上，从安全角度分析原因。

 

 

3.如何保证充值，计费数据库的安全性。

 

 

脚本部分（shell/python/perl均可）

1.从a.log文件中提取包含“WARNING”或“FATAL”，同时不包含“IGNOR”的行，然后提取以“：”分割的第五个字段。

 

2.添加一个新组为class01，然后添加属于这个组的30个用户，用户名的形式为stdxx，其中xx从01到30 。

 

3.在每个月的第一天备份并压缩/etc目录下的所有内容，存放在/root/backup目录里，且文件名为如下形式yymmdd_etc，yy为年，mm为月，dd为日，shell程序fileback存放在/usr/bin目录。

 

4.用shell变成，判断一文件是不是字符设备文件，如果是，将其拷贝到/dev目录下。

 

5.请用shell查询文件file1里面空看的所在行号。

 

6.用正则表达式匹配邮件地址和网站。

 

7.awk sed问题

1）在每一行后面增加一空行。

 

2）在匹配式样“regex”的行之后插入一空行。

 

3）计算行数（模拟“wc -l”）

 

4）在每一行开头处插入5个空格（使全文向右移动5个空格）

 

5）将“foo”替换成“bar”，并且只在行中未出现字串“baz”的情况下替换

 

6）将每两行连接成一行（类似“paste”）

 

7）显示包含“AAA”、“BBB”和“CCC”的行（固定次序）

 

8）为数字字串增加逗号分隔符号，将“1234567”改为“1,234,567”

 

9）只保留重复行中的第一行，其他行删除

 

10）删除8的倍数行

 

 

-

* 想知道一个查询用到了哪个index,如何查看?

* 如何强制mysql 使用某一个index ?

* 一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15

* 一个查询 select * from abc where a = 123 and b = 456 order by c limit 10; 请分析一下如何优化?需要哪些更多的信息?

* 数据库 load 非常高, 现在要添加一个新server来设置成master-slave模式，请问一般的步骤是什么? my.cnf里的相关的设置大概是什么样子（对如何拼写不做要求) ？

* 主从模式出现错误 1062,导致数据库不能同步,请问如何修复?

* 数据库不能停机，请问如何备份? 如何进行全备份和增量备份?

* 数据库性能下降，想找到哪些sql耗时较长，应该如何操作? my.cnf里如何配置?

 

聚集索引

聚集索引

•术语“聚集”指实际的数据行和相关的键值都保存在一起。每个表只能有一个聚集索引。但是，覆盖索引可以模拟多个聚集索引。存储引擎负责实现索引，因此不是所有的存储索引都支持聚集索引。当前，SolidDB和InnoDB是唯一支持聚集索引的存储引擎。

优点：

•可以把相关数据保存在一起。这样从磁盘上提取几个页面的数据就能把某个用户的邮件全部抓取出来。如果没有使用聚集，读取每个邮件都会访问磁盘。

•数据访问快。聚集索引把索引和数据都保存到了同一棵B-TREE中，因此从聚集索引中取得数据通常比在非聚集索引进行查找要快。

缺点：

•聚集能最大限度地提升I/O密集负载的性能。如果数据能装入内存，那么其顺序也就无所谓了。这样聚集就没有什么用处。

•插入速度严重依赖于插入顺序。更新聚集索引列是昂贵的，因为强制InnoDB把每个更新的行移到新的位置。

•建立在聚集索引上的表在插入新行，或者在行的主键被更新，该行必须被移动的时候会进行分页。

•聚集表可会比全表扫描慢，尤其在表存储得比较稀疏或因为分页而没有顺序存储的时候。

•第二（非聚集）索引可能会比预想的大，因为它们的叶子节点包含了被引用行的主键列。第二索引访问需要两次索引查找，而不是一次。 InnoDB的第二索引叶子节点包含了主键值作为指向行的“指针”，而不是“行指针”。 这种策略减少了在移动行或数据分页的时候索引的维护工作。使用行的主键值作为指针使得索引变得更大，但是这意味着InnoDB可以移动行，而无须更新指针。

 

索引类型

索引类型: B-TREE索引，哈希索引

• B-TREE索引加速了数据访问，因为存储引擎不会扫描整个表得到需要的数据。相反，它从根节点开始。根节点保存了指向子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点中值的上界和下界。最后，存储引擎可能无法找到需要的数据，也可能成功地找到包含数据的叶子页面。

•例：B-TREE索引 对于以下类型查询有用。匹配全名、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配一部分并且匹配某个范围中的另一部分；

B-TREE索引的局限：如果查找没有从索引列的最左边开始，它就没什么用处。不能跳过索引中的列，存储引擎不能优先访问任何在第一个范围条件右边的列。例：如果查询是where last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob=’1976-12-23’;访问就只能使用索引的头两列，因为LIKE是范围条件。

•哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中每一行的指针。

•因为索引只包含了哈希码和行指针，而不是值自身，MYSQL不能使用索引中的值来避免读取行。

•MYSQL不能使用哈希索引进行排序，因为它们不会按序保存行。

•哈希索引不支持部分键匹配，因为它们是由被索引的全部值计算出来的。也就是说，如果在（A，B）两列上有索引，并且WHERE子句中只使用了A，那么索引就不会起作用。

•哈希索引只支持使用了= IN（）和<=>的相等比较。它们不能加快范围查询。例如WHERE  price > 100;

•访问哈希索引中的数据非常快，除非碰撞率很高。当发生碰撞的时候，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较，以确定正确的数据。如果有很多碰撞，一些索引维护操作就有可能会变慢。

 

索引优化

曾经偷偷的面试了两个单位，都提到了Mysql的优化问题，所以以后要多多学习数据库的优化知识了。建设数据库的优化大概主要就是索引的优化了吧，因为我们不可能修改数据结构的情况下，提高数据库的查询效率似乎也只能用索引了。当然这也是建立在你sql语句写的比较科学的情况下，如果你的sql语句本身就写的比较垃圾，神仙也救不了你！
下边是在网上找到的一些资料，保留下来备用吧
1，创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2，复合索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3，索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4，使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5，排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6，like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7，不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
8，不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

分页

先看一下分页的基本原理（我拿的是CSDN那个百万级数据库来测试！）：

SELECT * FROM `csdn` ORDER BY id DESC LIMIT 100000,2000;
耗时： 0.813ms

分析：对上面的mysql语句说明：limit 100000,2000的意思扫描满足条件的102000行，扔掉前面的100000行，返回最后的2000行。

问题就在这里，如果是limit 100000,20000，需要扫描120000行，在一个高并发的应用里，每次查询需要扫描超过100000行，性能肯定大打折扣。

在《efficient pagination using mysql》中提出的clue方式。

利用clue方法，给翻页提供一些线索，比如还是SELECT * FROM `csdn` order by id desc，按id降序分页，每页2000条，当前是第50页，当前页条目id最大的是102000，最小的是100000。如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转）。

那么在处理上一页的时候SQL语句可以是：
SELECT * FROM `csdn` WHERE id<=102000 ORDER BY id DESC LIMIT 2000; #上一页
耗时：0.015ms

处理下一页的时候SQL语句可以是：
SELECT * FROM `csdn` WHERE id>102000 ORDER BY id ASC LIMIT 2000; #下一页
耗时：0.015ms

这样，不管翻多少页，每次查询只扫描20行。效率大大提高了！

但是，这样分页的缺点是只能提供上一页、下一页的链接形式。

 

优化技巧

MySQL 自带 slow log 的分析工具 mysqldumpslow ，但是没有说明。本文通过分析该脚本，介绍了其用法。
slow log 是 MySQL 根据 SQL 语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。

只要在 my.cnf 文件中配置好：
log-slow-queries = [slow_query_log_filename]
即可记录超过默认的 10s 执行时间的 SQL 语句。
如果要修改默认设置，可以添加：
long_query_time = 5
设定为 5s 。

如果要记录所有 SQL 语句，可以写入：
log-long-format

# t=time, l=lock time, r=rows
# at, al, 以及 ar 是对应的平均值

mysqldumpslow 可以接受的参数有：
‘v+’, # verbose
‘d+’, # debug
‘s=s’, # 排序 (t, at, l, al, r, ar etc)
‘r!’, # 倒排序 (largest last instead of first)
‘t=i’, # 显示最高的 n 个查询
‘a!’, # 不把所有的数字以 N ，字符串以 ‘S’ 显示
‘n=i’, # abstract numbers with at least n digits within names
‘g=s’, # grep: only consider stmts that include this string
‘h=s’, # hostname of db server for *-slow.log filename (can be wildcard)
‘i=s’, # name of server instance (if using mysql.server startup script)
‘l!’, # don’t subtract lock time from total time

mysql explain的使用说明
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
如：explain select surname,first_name form a,b  where  a.id=b.id
分析结果形式如下：
引用

mysql> explain SELECT * FROM `whisper` WHERE to_id = 6696 AND del = 0  AND whisper=0 ORDER BY `send_time` DESC LIMIT 4;
+—-+————-+———+——+—————+——-+———+——-+——+—————————–+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra                       |
+—-+————-+———+——+—————+——-+———+——-+——+—————————–+
|  1 | SIMPLE      | whisper | ref  | to_id         | to_id |       4 | const |    1 | Using where; Using filesort |
+—-+————-+———+——+—————+——-+———+——-+——+—————————–+
1 row in set (0.00 sec)

EXPLAIN列的解释：
table 显示这一行的数据是关于哪张表的
type  这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数
rows MYSQL认为必须检查的用来返回请求数据的行数
Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra 列返回的描述的意义
Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each
Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

不同连接类型的解释（按照效率高低的顺序排序）
system 表只有一行：system表。这是const连接类型的特殊情况
const 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

mysql常用的hint（原创）

mysql常用的hint
对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。
[b]强制索引 FORCE INDEX[/b]
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。
[b]忽略索引 IGNORE INDEX[/b]
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。
[b]关闭查询缓冲 SQL_NO_CACHE[/b]
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
[b]强制查询缓冲 SQL_CACHE[/b]
SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

[b]优先操作 HIGH_PRIORITY[/b]
HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;
[b]滞后操作 LOW_PRIORITY[/b]
LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …
[b]延时插入 INSERT DELAYED[/b]
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有 空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增 的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

[b]强制连接顺序 STRAIGHT_JOIN[/b]
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
[b]强制使用临时表 SQL_BUFFER_RESULT[/b]
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。
[b]分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT[/b]
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

查询是数据库技术中最常用的操作。查询操作的过程比较简单，首先从客户端发出查询的SQL语句，数据库服务端在接收到由客户端发来的 SQL语句后， 执行这条SQL语句，然后将查询到的结果返回给客户端。虽然过程很简单，但不同的查询方式和数据库设置，对查询的性能将会有很在的影响。
因此，本文就在MySQL中常用的查询优化技术进行讨论。讨论的内容如：通过查询缓冲提高查询速度；MySQL对查询的自动优化；基于索引的排序；不可达查询的检测和使用各种查询选择来提高性能。
一、 通过查询缓冲提高查询速度
一般我们使用SQL语句进行查询时，数据库服务器每次在收到客户端发来SQL后，都会执行这条SQL语句。但当在一定间隔内(如1分钟内)，接到完 全一样的SQL语句，也同样执行它。虽然这样可以保证数据的实时性，但在大多数时候，数据并不要求完全的实时，也就是说可以有一定的延时。如果是这样的 话，在短时间内执行完全一样的SQL就有些得不偿失。
幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。
我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单，只需要将query_cache_type设为1即可。在设 置了这个属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过 期，那么就直接取查询结果返回给客户端。但在写SQL语句时注意，MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句:
1.    SELECT * from TABLE1
2.
3.    SELECT * FROM TABLE1
上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。
虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或者并不经常使用(可能一天就执行一两次)。这样就需要把 缓冲关了。当然，这可以通过设置query_cache_type的值来关闭查询缓冲，但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法:
1.    SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
以上的SQL语句由于使用了SQL_NO_CACHE，因此，不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
我们还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
1.    SELECT SQL_CALHE * FROM TABLE1

二、MySQL对查询的自动优化
索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:
1.    CREATE TABLE SALES
2.
3.    (
4.
5.    ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
6.
7.    NAME VARCHAR(100) NOT NULL,
8.
9.    PRICE FLOAT NOT NULL,
10.
11.    SALE_COUNT INT NOT NULL,
12.
13.    SALE_DATE DATE NOT NULL,
14.
15.    PRIMARY KEY(ID)，
16.
17.    INDEX (NAME)，
18.
19.    INDEX (SALE_DATE)
20.
21.    );
假设这个表中保存了数百万条数据，而我们要查询商品号为1000的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:
SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN ’2004-01-01′ AND ’2005-12-31′;
如果这种商品的数量非常多，差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使 用索引，就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例)，速度会变慢，这样还不如对整个表进行扫描。因 此，MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。
对于MySQL来说，上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了，这个比例是MySQL的开发人员根据他们的经验得出的。然而，实际的比例值会根据所使用的数据库引擎不同而不同。
三、 基于索引的排序
MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。我们可以看看如下的SQL语句:
1.    SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对SALES表建立复合索引:
1.    ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。
四、 不可达查询的检测
在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value < 100 AND value > 200。我们永远无法找到一个既小于100又大于200的数。
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:
1.    SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行 SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用 EXPLAIN进行测试:
1.    EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”
上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:
1.    EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
我们可以看到，table项是空，这说明MySQL并没有对SALES表进行操作。
五、 使用各种查询选择来提高性能
SELECT语句除了正常的使用外，MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分，我将介绍几个常用的查询选项。
1. STRAIGHT_JOIN:强制连接顺序
当我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如下列的SQL语句中，TABLE1和TABLE2并不一定是谁连接谁:
1.    SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …
如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:
1.    SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
2. 干预索引使用，提高性能
在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。
限制使用索引的范围
有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。
1.    SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …
从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。
限制不使用索引的范围
如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。
1.    SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。
强迫使用某一个索引
上面的两个例子都是给MySQL提供一个选择，也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。
1.    SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。
3. 使用临时表提供查询性能
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。
1.    SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。
1.    SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

 

数据库优化

1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not   null,除非这个字段需要null.
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
2.系统的用途
1).尽量使用长连接.
2).explain   复杂的SQL语句。
3).如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
4).LIMIT语句尽量要跟order   by或者   distinct.这样可以避免做一次full   table   scan.
5).如果想要清空表的所有纪录,建议用truncate   table   tablename而不是delete   from   tablename.
6).能使用STORE   PROCEDURE   或者   USER   FUNCTION的时候.
7).在一条insert语句中采用多重纪录插入格式.而且使用load   data   infile来导入大量数据，这比单纯的indert快好多.
8).经常OPTIMIZE   TABLE   来整理碎片.
9).还有就是date   类型的数据如果频繁要做比较的话尽量保存在unsigned   int   类型比较快。
3.系统的瓶颈
1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.

 

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1. IS NULL 与 IS NOT NULL

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起 来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个 叫比尔.克林顿（Bill Cliton）的职工。

下面是一个采用联接查询的SQL语句，

select * from employss where first_name||''||last_name ='Beill Cliton'

上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。

Select * from employee where first_name ='Beill' and last_name ='Cliton'

遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需 要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本：

select * from employee where first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1) and last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)

3. 带通配符（%）的like语句

同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：

select * from employee where last_name like '%cliton%'

这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索 引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。 在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%'

4. Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

5. NOT

我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

… where not (status =’VALID’)

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：

… where status <>’INVALID’

再看下面这个例子：

select * from employee where salary<>3000;

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000;

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。

6. IN和EXISTS

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

第一种格式是使用IN操作符：

… where column in(select * from … where …);

第二种格式是使用EXIST操作符：

… where exists (select ‘X’ from …where …);

我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

第二种格式中，子查询以‘select ‘X’开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。

通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。