MySQL优化

1、SQL索引优化

使用explain查询SQL的执行计划

explain返回各列的含义

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连续类型为const,eq_reg,ref,range,index和ALL(没有where从句，表扫描)。
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引
• key：实际使用的索引。如果为NULL，则没有使用索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：mysql认为必须检查的用来返回请求数据的行数
• extra列需要注意的返回值
• Using filesort:看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序，根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using temporary 看到这个的时候，也需要优化，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

2、Max()的优化方法
查询最后支付时间----优化MAX()函数

mysql> explain select max(payment_date) from payment \G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: payment

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 16086

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

ERROR: 

No query specified

可以查看到：type:ALL是表扫描操作;没有任何索引，rows非常大，IO效率非常低。
如何优化这个SQL：通常情况下建立索引：
create index idx_paydate on payment(payment_date);
这样在次执行
explain select max(payment_date) from payment;
可以看到Extra：select tables optimized away;可以通过索引进行操作，大大减少了IO操作

mysql> create index idx_paydata on payment(payment_date);

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select max(payment_date) from payment \G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: NULL

   partitions: NULL

         type: NULL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

     filtered: NULL

        Extra: Select tables optimized away

1 row in set, 1 warning (0.00 sec)

ERROR: 

No query specified

mysql> DROP INDEX idx_paydata on payment;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select max(payment_date) from payment \G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: payment

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 16086

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

ERROR: 

No query specified

mysql> 

3、Count()的优化方法（COUNT() 函数返回匹配指定条件的行数。）
在一条SQL中同时查出2006年和2008年电影的数量----优化count()函数
错误方式：
SELECT COUNT(release_year='2006' OR release_year='2008') FROM film;
无法分开计算2006和2008年的电影数量，结果是之和。
select count(*) FROM film WHERE release_year='2006' AND release_year='2007';
release_year不可能同时为2006和2007，因此有逻辑错误
正确的方式：

mysql> select count(release_year='2006' or null) as '2006film', count(release_year='2008' or null) as '2008film' from film;

+----------+----------+

| 2006film | 2008film |

+----------+----------+

|      999 |        1 |

+----------+----------+

1 row in set (0.00 sec)

mysql> 

count中‘or null’的作用是：当年份不是2006/2008时返回null，而null在count(id)中时不会计数的，count(*)会将null计算进去。

mysql> SELECT COUNT(release_year='2007') FROM film;          

+----------------------------+

| COUNT(release_year='2007') |

+----------------------------+

|                       1000 |

+----------------------------+

1 row in set (0.00 sec)

mysql> SELECT COUNT(release_year='2007' or null) FROM film;

+------------------------------------+

| COUNT(release_year='2007' or null) |

+------------------------------------+

|                                  0 |

+------------------------------------+

1 row in set (0.00 sec)

mysql> 

4.子查询的优化
通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。
我们新建一张表t1
create table t1(tid int);
插入一条数据insert into t1 values(1);
进行子查询：select * from t where t.id in (select * from t1.tid from t1);
返回t表id在t1表中的数据
优化成join的形式：
select t.id from t join t1 on t.id=t1.tid;
这两种形式返回的结果是一样的

需要注意的是：
如果在t1表中，添加一条数据：insert into t1 values(1);
然后在分别执行这两种形式的查询：
发现使用select * from t where t.id in (select * from t1.tid from t1);查询出来的结果是一条数据。而select t.id from t join t1 on t.id=t1.tid;是两条数据，说明有重复数据，我们可以使用distinct去重。

下面列出了可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行。

Inner join
产生的结果集中，是A和B的交集。
outer join
产生的结果集中，是A和B的并集。

select distinct t.id from t join t1 on t.id=t1.tid;这样就返回一条记录了

mysql> select * from t1;

+------+

| tid  |

+------+

|    1 |

|    1 |

+------+

2 rows in set (0.00 sec)

mysql> select * from t;

+------+

| tid  |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysql> select * from t where t.tid in (select t1.tid from t1);

+------+

| tid  |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysql> select * from t1 where t1.tid in (select t.tid from t);

+------+

| tid  |

+------+

|    1 |

|    1 |

+------+

2 rows in set (0.00 sec)

mysql> select t.tid from t join t1 on t.tid=t1.tid;

+------+

| tid  |

+------+

|    1 |

|    1 |

+------+

2 rows in set (0.00 sec)

mysql> select t1.tid from t1 join t on t.tid=t1.tid;

+------+

| tid  |

+------+

|    1 |

|    1 |

+------+

2 rows in set (0.00 sec)

mysql> select distinct t1.tid from t1 join t on t.tid=t1.tid;

+------+

| tid  |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysql> 

5、优化group by查询

explain SELECT actor.first_name,actor.last_name,count(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;
using可用在join语句相同字段连接，起到和ON相同作用，inner join 和left join中都可以使用

示例：LEFT JOIN 正常写法：
SELECT t1.id,t2.name FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE ....
其实也可以这么写：
SELECT t1.id,t2.name FROM t1 LEFT JOIN t2 USING(id) WHERE ....

上面的explain执行结果，可以看到extra为Using temporary，Using filesort 用到了临时表,文件排序的方式对表进行了全表扫描.
我们应该避免这种方式。改写如下：
Explain SELECT actor.first_name,actor.last_name,c.cnt FROM sakila.actor INNER JOIN(SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);

6、优化limit查询
limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts，这样会造成大量的IO问题
SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;
这个语句进行explain操作，发现会进行全表扫描，并且有文件排序的方式

优化方式： (film_id是升序连续排列)
步骤1.使用有索引的列或主键进行Order By操作
SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5
使用这种方式可以得到相同的结果，但是explain执行计划却完全不同，type=index,rows=55
这种方式并不是最优的，比如将limit 50,5改为limit 500,5;rows=505，rows会随着limit而改变，如果有上万条数据，那么响应速度回变慢

步骤2.记录上次返回的主键，在下次查询时使用主键过滤
SELECT film_id,description FROM sakila.film WHERE film_id>55 and film_id<=60 ORDER BY film_id LIMIT 1,5;
这种方式rows=5，并且不会随着limit的增长而增长。

7、索引优化
在where从句，group by从句，order by 从句，on从句中出现的列
索引字段越小越好
离散度越大的列放到联合索引的前面
SELECT * FROM payment WHERE staff_id=2 AND customer_id =584;
是index(staff_id,customer_id)好?还是index(customer_id,staff_id)好?

首先我们先看一下payment表的数据结构是什么样的
desc payment;
然后我们使用语句:select count(distinct customer_id),count(distinct staff_id) from payment;这样我们可以看出他们的离散程度。他们的唯一值越多，说明他们的离散度越好，他们的可选择性就越高。
这个查询可以看出 customer_id=599，而staff_id=2;说明customer_id离散度更高一些，可选择性更高，因此要建立联合索引时就把customer_id放在最前面。
所以应该使用index(customer_id,staff_id)

8、如何选择合适的列建立索引?
索引不是越多越好。
通常情况下建立索引可以优化我们的查询效率，但是会降低我们的写入效率，也就是说建立索引会增强查询，会影响insert，update，delete这种写入操作。但是往往不是这样的，过多的索引不但会影响我们的写入效率，同时也会影响查询，这是由于数据库进行查询分析的时候，首先要选择哪个索引进行查询，如果索引越多，分析的过程就越慢，这样就会影响查询的效率。因此我们要维护和删除索引。

索引的维护及优化---重复及冗余索引
重复索引是指相同的列以相同的顺序建立同类型的索引，如下表primary key 和ID列上的索引就是重复索引(主键已经是一个唯一索引了)：

create table test(id int not null primary key,name varchar(10) not null,title varchar(50) not null ,unique(id))engine=innodb;
冗余索引是指多个索引的前缀列相同，或者是在联合索引中包含了主键的索引，下面这个例子中key(name,id)就是一个冗余索引。

create table test(id int null primary key,name varchar(50) not null,title varchar(50) not null,key(name,id))engine=innodb;
查找重复及冗余索引
SELECT a.TABLE_SCHEMA AS '数据库名',a.table_name as '表名',a.index_name AS '索引1',b.INDEX_NAME AS '索引2',a.COLUMN_NAME AS '重复列名' FROM STATISTICS a JOIN STATISTICS b ON a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME AND a.SEQ_IN_INDEX=b.SEQ_IN_INDEX AND a.COLUMN_NAME=b.COLUMN_NAME WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME;
工具：使用pt-duplicate-key-checker工具
删除不用的索引，目前mysql中只能使用慢查询日志配合pt-index-usage工具来进行索引使用情况的分析。

9、数据库结构优化：
选择合适的数据类型
数据类型的选择，重点在于"合适"二字，如何确定选择的数据类型是否合适？
a)使用可以存下你的数据的最小的数据类型
b)使用简单的数据类型。Int要比varchar类型在mysql处理上简单
c)尽可能的时候not null定义字段
d)尽量少用text类型，非用不可是最好考虑分表
使用bigint来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换

CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL,ipaddress BIGINT,PRIMARY KEY(id));
INSERT INTO session(ipaddress) VALUES(INET_ATON('192.168.0.1'));
SELECT INET_NTOAA(ipaddress) FROM sessions;

表的范式化和反范式化
范式化是指数据库设计的规范，目前说到范式化一般是指第三范式，也就是要求数据库中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式
反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，已达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

表的垂直拆分
所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：
a)把不常用的字段单独存放到一个表中。
b)把大字段独立存放到一个表中。
c)把经常一起使用的字段放到一起。

表的水平拆分
表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完整一致的。
常用的水平拆分方法为：
1.对id进行hash运算，如果要拆分成5个表则使用mod(id,5)取出0-4个值
2.针对不同的hashID把数据存到不同的表中。
挑战：1.快分区表进行数据查询2.统计及后台报表操作

10、操作系统配置优化
数据库是基于操作系统的，目前大多数Mysql都是安装在Linux系统之上，所以对于操作系统的一些参数配置也会影响到Mysql的性能，下面就列出一些常用的系统配置
网络方面的配置，要修改/etc/sysctl.conf
#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
#减少断开连接时，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

#打开文件数的限制，可以使用ulimit -a查看目录的各位限制，可以修改/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制
soft nofile 65535
hard nofile 65535
除此之外最好在Mysql服务器上关闭iptable,selinux等防火墙软件

11、mysql配置文件
Mysql可以通过启动时指定配置文件参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf，在windows系统配置文件可以是位于C:/windows/my.ini文件，Mysql查找配置文件的顺序可以通过以下方法获得
$/usr/sbin/mysqld --verbose --help|grep -A l 'Default option'
注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的
Mysql配置文件--常用参数说明
innodb_buffer_pool-size：非常重要的一个参数，用于配置Innodb的缓冲池，如果数据库中个只有Innodb表，则推荐配置量为总内存的75%.
innodb_buffer_pool_instances:Mysql5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池
Innodb_log_buffer_size:innodb log 缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大
innodb_flush_log_at_trx_commit:关键参数，对innodb的IO效率影响很大。默认值为1,可以取0,1,2三个值，一般建议设为2，但如果数据安全性要求比较高则使用默认值1.
innodb_read_io_threads,innodb_write_io_threads：决定了Innodb读写的IO进程数，默认为4
innodb_file_per_table:关键参数，控制Innodb每一个表使用独立的表空间，默认为off，也就是所有表都会建立在共享表空间中
innodb_stats_on_metadata:决定了Mysql在什么情况下会刷新innodb表的统计信息

12、第三方工具：Percon Configuration Wizard

13、服务器硬件优化：
如何选择CPU：是选择单核更快的CPU还是选择核数更多的cup?
1.MySQL有一些工作只能用到单核CPU
2.Mysql对CPU核数的支持并不是越多越快
mysql5.5使用的服务器不要超过32核
磁盘IO优化：
常用RAID级别介绍：
RAID0:也称为条带，就是把多个磁盘链接成一个硬盘使用，这个级别的IO最好，缺点就是一个磁盘坏掉那么所有数据就会丢失
RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同，IO效果不如RAID0，安全性好
RAID5：也是把多个(最少3个)硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。
一般的OLTP型的数据库使用RAID1+0：就是RAID1和RAID0的结合。同时具有两个级别的优缺点。一般建议数据库使用这个级别。
SNA和NAT是否合适数据库：
1.常用于高可用解决方案
2.顺序读写效率高，但是随即读写不如人意
3.数据库随即读写比率很高。

学习自视频：http://www.imooc.com/learn/194