1.硬件
磁盘IO优化
常用RAID级别介绍（引用百度百科的内容）
磁盘阵列（Redundant Arrays of Independent Disks，RAID），
RAID0：也称为条带，把多个磁盘链接成一个硬盘使用，这个级别IO最好。
RAID 0最简单的实现方式就是把N块同样的硬盘用硬件的形式通过智能磁盘控制器或用操作系统中的磁盘驱动程序以软件的方式串联在一起创建一个大的卷集。
在使用中电脑数据依次写入到各块硬盘中，它的最大优点就是可以整倍的提高硬盘的容量。如使用了三块80GB的硬盘组建成RAID 0模式，那么磁盘容量就会是240GB。
其速度方面，各单独一块硬盘的速度完全相同。最大的缺点在于任何一块硬盘出现故障，整个系统将会受到破坏，可靠性仅为单独一块硬盘的1/N。
RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
RAID1称为磁盘镜像，原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，
在不影响性能情况下最大限度的保证系统的可靠性和可修复性上，只要系统中任何一对镜像盘中至少有一块磁盘可以使用，甚至可以在一半数量的硬盘
出现问题时系统都可以正常运行,当一块硬盘失效时，系统会忽略该硬盘，转而使用剩余的镜像盘读写数据，具备很好的磁盘冗余能力。虽然这样对
数据来讲绝对安全，但是成本也会明显增加，磁盘利用率为50%，以四块80GB容量的硬盘来讲，可利用的磁盘空间仅为160GB。另外，出现硬盘故障的
RAID系统不再可靠，应当及时的更换损坏的硬盘，否则剩余的镜像盘也出现问题，那么整个系统就会崩溃。更换新盘后原有数据会需要很长时间同步镜像，
外界对数据的访问不会受到影响，只是这时整个系统的性能有所下降。因此，RAID 1多用在保存关键性的重要数据的场合。
RAID 1主要是通过二次读写实现磁盘镜像，所以磁盘控制器的负载也相当大，尤其是在需要频繁写入数据的环境中。为了避免出现性能瓶颈，使用多个磁盘控制器就显得很有必要。
RAID 0+1：名称上我们便可以看出是RAID0与RAID1的结合体。在我们单独使用RAID 1也会出现类似单独使用RAID 0那样的问题，即在同一时间内只能向一块磁盘写入数据，不能充分利用所有的资源。为了解决这一问题，我们可以在磁盘镜像中建立带区集。因为这种配置方式综合了带区集和镜像的优势，所以被称为RAID 0+1。把RAID0和RAID1技术结合起来，数据除分布在多个盘上外，每个盘都有其物理镜像盘，提供全冗余能力，允许一个以下磁盘故障，而不影响数据可用性，并具有快速读/写能力。RAID0+1要在磁盘镜像中建立带区集至少4个硬盘。
RAID5：把多个（最少3个）硬盘合并成1个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对文汇报数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。
2.系统配置
2.1修改Linux操作系统配置
(1)修改Linux打开文件数量的限制
[root@localhost ~]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 11921
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 11921
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
其中open files是指打开文件数量的限制，默认为1024，可以修改/etc/security/limits.conf文件
添加以下内容
* soft nofile 65535
* hard nofile 65535
这样文件limits.conf就变成[root@localhost ~]# cat /etc/security/limits.conf # /etc/security/limits.conf##Each line describes a limit for a user in the form:##<domain> <type> <item> <value>##Where:#<domain> can be:# - an user name# - a group name, with @group syntax# - the wildcard *, for default entry# - the wildcard %, can be also used with %group syntax,# for maxlogin limit##<type> can have the two values:# - "soft" for enforcing the soft limits# - "hard" for enforcing hard limits##<item> can be one of the following:# - core - limits the core file size (KB)# - data - max data size (KB)# - fsize - maximum filesize (KB)# - memlock - max locked-in-memory address space (KB)# - nofile - max number of open files# - rss - max resident set size (KB)# - stack - max stack size (KB)# - cpu - max CPU time (MIN)# - nproc - max number of processes# - as - address space limit (KB)# - maxlogins - max number of logins for this user# - maxsyslogins - max number of logins on the system# - priority - the priority to run user process with# - locks - max number of file locks the user can hold# - sigpending - max number of pending signals# - msgqueue - max memory used by POSIX message queues (bytes)# - nice - max nice priority allowed to raise to values: [-20, 19]# - rtprio - max realtime priority##<domain> <type> <item> <value>##* soft core 0#* hard rss 10000#@student hard nproc 20#@faculty soft nproc 20#@faculty hard nproc 50#ftp hard nproc 0#@student - maxlogins 4* soft nofile 65535* hard nofile 65535# End of file
重启系统
[root@localhost security]# reboot
Broadcast message from root@localhost.localdomain
(/dev/pts/1) at 0:45 ...
The system is going down for reboot NOW!
重启后再看结果
[root@localhost ~]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 11921
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 65535
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 11921
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
说明打开文件数量的限制已经从1024变成65535
(2)修改Linux内核参数
修改文件/etc/sysctl.conf
net.ipv4.tcp_max_syn_backlog = 65535 #队列SYN最大半连接数，默认1024
net.ipv4.tcp_max_syn_backlog=65535 #TCP支持的队列数
net.ipv4.tcp_fin_timeout = 10 #系統TIMEOUT 时间，默认60
net.ipv4.tcp_max_tw_buckets = 8000 #表示系统同时保持TIME_WAIT的最大数量，如果超过这个数字，TIME_WAIT将立刻被清除并打印警告信息
net.ipv4.tcp_tw_recycle = 1 #表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭
net.ipv4.tcp_tw_reuse = 1 #表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭
输入下面的命令，让内核参数生效
[root@localhost ~]# sysctl -p
2.2修改MySQL数据库系统配置
通过修改文件/etc/my.cnf
[mysqld]
innodb_buffer_pool_size=1G
innodb_log_buffer_size=10M
innodb_log_file_size=10M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
(1)innodb_buffer_pool_size
innodb_buffer_pool_size 参数用来设置 Innodb 最主要的 Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间。
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)
系统默认为8M，一般是 50%～80%的系统内存，具体算法如下：
假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G，MySQL 最大连接数为 500，同时还使用 了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？
内存分配为如下几大部分：
a) 系统使用，假设预留 800M；
b) 线程独享，约 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，组成大概如下:sort_buffer_size：1MB join_buffer_size：1MB read_buffer_size：1MB read_rnd_buffer_size：512KB thread_statck：512KB
c) MyISAM Key Cache，假设大概为 1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；
上线之初，这个值可调小一点，后面再根据系统运行情况慢慢增加。
(2)innodb_log_buffer_size
innodb log缓冲的大小，日志最长每秒钏就会刷新到磁盘，所以一般不用太大，默认为1M。
如果是写入比较多的大事务（比如每秒写入成千上万条数据），则可适当调高，调到8M~16M。
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 1048576 |
+------------------------+----------+
1 row in set (0.00 sec)
(3)innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间,默认5M。
mysql> show variables like 'innodb_log_file_size%';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)
(4)innodb_flush_log_at_trx_commit
默认值为1.
如果值为0，log buffer中的日志会每隔一秒钟写入日志文件，同时刷新到磁盘，但不会提交事务,当系统崩溃，最多丢失一秒钟的事务数据。
如果值为1，log buffer中的日志会在每次提交事务时写入日志文件，同时刷新到磁盘，这是事务安全的，满足ACID级别的要求。
如果值为2，log buffer中的日志会在每次提交事务时写入日志文件，但不会立刻刷新到磁盘，而是每隔一秒钟刷新到磁盘，当操作系统崩溃或断电时会丢失最后一秒钟的事务数据，但不会影响InnoDB的崩溃修复，因此崩溃修改忽略最后一秒钟的事务数据。
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
(5)InnoDB中文件IO的线程数
默认值是4，通常情况下小于4，如果在Windows，设置一个更大的值IO的性能会更好。
在Unix上，调大这个值没意义，InnoDB总是会使用默认值4
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.10-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_%_io_threads';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like '%_io_threads';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_file_io_threads | 4 |
+------------------------+-------+
1 row in set (0.00 sec)
(5)独立表空间参数innodb_file_per_table
MyISAM引擎的数据库会分别创建三个文件：表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。
然而当你使用InnoDB的时候，一切都变了。InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，
增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table参数，可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。
独立表空间
优点：
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动。
4.空间可以回收（drop/truncate table方式操作表空间不能自动回收）
5.对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加比共享空间方式更大。
结论：
共享表空间在Insert操作上有一些优势，但在其它都没独立表空间表现好。
当启用独立表空间时，请合理调整一下 innodb_open_files 参数。
3.数据库表结构
3.1正确使用数据类型
尽量使用占用空间比较小的数据类型保存数据
3.2当表比较大时，将不常用或大字段拆分成独立的表
4.SQL及索引
explain语句：explain能够获取MySQL执行select语句的过程，包括表的连接情况和结果的排序情况。
EXPLAIN SELECT * FROM student WHERE student_id = 12345;
explain的属性介绍如下
4.1 id
explain语句的主键，如果只有一条select语句，则为1；如果有两条select语句，则为2；以此类推。
4.2 select_type
选择的类型，取值如下：
SIMPLE |
简单查询，没有使用合并或子查询 |
PRIMARY |
最外层的查询 |
UNION |
在一个合并里面的第二或更深层的查询语句 |
DEPENDENT UNION |
在一个合并里面的第二或更深层的查询语句，依赖外部查询 |
UNION RESULT |
合并的结果 |
SUBQUERY |
子查询里面的第一次查询 |
DEPENDENT SUBQUERY |
子查询里面的第一次查询，依赖外部查询 |
DERIVED |
派生表查询（在From语句里面的子查询） |
4.3 table
输出的行所归属的表。
4.4 type
连接类型，性能从最好到最差的排序如下；
(1)system
表只有一行，这是const连接类型的特殊情况。
(2)const
表中最多只匹配一行，通常匹配主键索引或唯一索引时会遇到。
(3)eq_ref
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
(4)ref
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 。
(5)ref_or_null
(6)index_merge
(7)unique_subquery
(8)range
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
(9)index
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
(10)ALL
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
4.5 possible_keys
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
4.6 key
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
4.7 key_len
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
4.8 ref
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
4.9 rows
rows：MYSQL认为必须检查的用来返回请求数据的行数
4.10 Extra
Extra：关于MYSQL如何解析查询的额外信息,这里可以看到的坏的例子是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:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
上面就是有关SQL语句性能优化的内容，更多关于索引优化的内容请参见本人写的另一篇博文： Mysql 索引