为什么要优化？

随着数据量的增大， mysql服务性能差从而直接影响用户体验。

查询时结果显示的很慢等。

哪些方面可以优化？

1、优化硬件、操作系统

2、优化MySQL服务器

3、优化DB设计

4、优化SQL语句

5、优化应用

1、优化硬件、操作系统

-CPU，内存，硬盘

Linux操作系统的内核优化

内核相关参数(/etc/sysctl.conf)

-网络TCP连接

-加快资源回收效率

-增加资源限制

-改变磁盘调度策略

 

2、优化MySQL服务器

最大连接数：

max_connections=2000

默认：max_connections=151

指定MySQL可能的连接数量

#指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。

#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。

back_log=1024

默认：back_log=80

索引块的缓冲区大小

key_buffer_size = 32M

#索引块的缓冲区大小，对MyISAM表性能影响最大的一个参数.决定索引处理的速度，尤其是索引读的速度。默认值是8M，通过检查状态值Key_read_requests

#和Key_reads，可以知道key_buffer_size设置是否合理

 

默认：key_buffer_size=8M

 

 

MySQL执行排序使用的缓冲大小

sort_buffer_size = 16M

 

#是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

#如果不能，可以尝试增加sort_buffer_size变量的大小。

 

默认：sort_buffer_size=256K

 

 

MYSQL读入缓冲区大小

read_buffer_size = 64M

#是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

#如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

 

默认：read_buffer_size=64K

 

 

Join操作缓存大小

join_buffer_size = 16M

#应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的

#读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，

#而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，

#然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

 

默认：join_buffer_size=256K

MySQL的随机读缓冲区大小

read_rnd_buffer_size = 32M

#是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，

#提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

 

默认：read_rnd_buffer_size=256K

缓存排序索引大小

myisam_sort_buffer_size = 256M

#当对MyISAM表执行repair table或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”

 

myisam_sort_buffer_size=102M

缓存空闲的线程以便不被销毁

thread_cache_size = 384

#thread_cahe_size线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要重新建立新连接，

#则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。

 

默认：thread_cache_size=10

set global  max_connections=2000;#设置最大连接数
set global key_buffer_size=512*1024*1024;#设置索引块缓冲区大小
set global sort_buffer_size=128*1024*1024;#MySQL执行排序使用的缓冲大小
set global read_buffer_size=64*1024*1024;#MYSQL读入缓冲区大小
set global join_buffer_size=128*1024*1024;#Join操作缓存大小
set global read_rnd_buffer_size=32*1024*1024;#MySQL的随机读缓冲区大小
set global myisam_sort_buffer_size=256*1024*1024;#缓存排序索引大小
set global thread_cache_size=384;#缓存空闲的线程以便不被销毁
set global innodb_buffer_pool_size=1000*1024*1024;#内存

#查询
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'

3、优化DB设计

-参照范式进行设计（1级范式）

 

1NF

　　　　包含分隔符类字符的字符串数据。

　　　　名字尾端有数字的属性。

　　　　没有定义键或键定义不好的表。

　2NF

　　　　多个属性有同样的前缀。

　　　　重复的数据组。

　　　　汇总的数据，所引用的数据在一个完全不同的实体中。

　　　　BCNF- “每个键必须唯一标识实体，每个非键熟悉必须描述实体。

　4NF

　　　　三元关系（实体:实体:实体）。

　　　　潜伏的多值属性。（如多个手机号。）

　　　　临时数据或历史值。（需要将历史数据的主体提出，否则将存在大量冗余。）

 

-建立合适的索引

建索引的目的

加快查询速度。
减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。
消除磁盘排序，索引是排序的，走完索引就排序完成

1）B-Tree 索引

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型

2）Hash 索引

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

3）什么时候可以建索引

1）列无重复值，可以建索引：唯一索引和普通索引

2）聚集索引和非聚集索引都可以是唯一的。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。

3）建了索引性能得到提高

4）区分度高的列可以建索引，比如表示男和女的列区分度就不高，就不能建索引

4）什么时候不可以建索引

1.频繁更新的字段不适合建立索引

 

2.where条件中用不到的字段不适合建立索引

 

3.表数据可以确定比较少的不需要建索引

 

4.数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

 

5.参与列计算的列不适合建索引，如select * from where amount+1>10

 

6.查询返回的记录数不适合建立索引

 

7.查询的排序表记录小于40%不适合建立索引

 

8.查询非排序表的记录小于 7%不适合建立索引

 

9.表的碎片较多（频繁增加、删除）不适合建立索引

4、优化架构设计方案

• 加缓存

• DNS轮询

 

通过在DNS-server上对一个域名设置多个ip解析，来扩充web-server性能及实施负载均衡的技术 。

 

• LVS（负载均衡）

 

Linux Virtual Server，使用集群技术，实现在linux操作系统层面的一个高性能、高可用、负载均衡服务器 。

 

• nginx：一个高性能的web-server和实施反向代理的软件

 

一个高性能的web-server和实施反向代理的软件