Mysql从入门到精通学习笔记1-mysql性能优化

一、性能优化目标

        通过合理安排资源，调整系统参数使Mysql运行更快、更节省资源，增加系统反应速度；优化访问包含查询速度优化、数据库结构优化、Mysql服务器优化等；

二、查询速度优化

查询优化分两步，第一步分析查询语句执行情况，分析语句执行瓶颈，第二步查询语句尽可能使用索引，避免全表查询

2.1分析查询语句方式

   EXPLAIN   SELECT select_options;  例子：EXPLAIN  select * from user;

  分析结果，确认查询语句是否使用索引，使用索引的类型级别是什么？ 连接类型尽可能最佳类型

表的连接类型最佳类型---最差类型顺序为：

system：系统表，表中只有一行数据；

const： 常量， 单表主键或者唯一键查询条件，最多只会有一条记录匹配，由于是常量，实际上只须要读一次；

eq_ref ：  表连接查询，查询条件是主表主键或者唯一键，最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问，使用“=”操作符号；

ref ：     表连接查询，查询条件使用普通索引，使用“=”，“<=>”操作符；

ref_or_null ： 同ref，但是查询条件包含NULL值的行

index_merge： 该类型表示使用了索引合并优化，查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据；

unique_subquery  替换子查询，唯一索引查找函数，可以完全替换子查询，效率更高；子查询中的返回结果字段组合是主键或者唯一约束 。

index_subquery    替换子查询，非唯一索引查找函数，可以完全替换子查询，效率更高;

range： 只检索索引范围扫描，操作符 =、<>、<、<=  between in 等等；

index:全索引扫描

ALL:全表扫描最差

2.2索引对查询速度的影响

mysql提升性能最有效的方式：对数据库设计合理的索引；没有索引，查询语句会扫描表中所有记录，在数据量比较大情况下，这样查询速度比较慢，使用索引，查询语句可以根据索引快速定位待查询的记录，从而减少查询的记录数，达到提高查询速度的目的；

2.3使用索引查询

2.3.1使用like关键字查询语句，like查询如果第一个字符是“%”，不会使用索引

2.3.2使用多列索引的查询语句 ，最左匹配，多列索引，查询条件中只有第一个字段被使用，索引才会生效

2.3.3使用or关键字查询语句，只有前后两个条件中都是索引时候，查询才会使用索引；

总结：

   a.尽量在where order by涉及的列，建立索引

   b.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

   c.in 和 not in 也要慎用，否则会导致全表扫描，例如字段是char 传整型数组

   d.如果在 where 子句中使用参数，也会导致全表扫描,  应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描,应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描等

  e. mysql   is null 走索引，is not null是不走索引的

2.3.4 优化子查询

 子查询可以使得查询语句很灵活，但是执行效率不高，因为mysql会为内层查询建立临时表，使用完毕撤销，这样会影响性能，单库情况下可以用连接查询代替；

三、优化数据库结构

尽可能遵循数据库设计范式，但是有些情况下，反范式设计往往更合适，实践出真知；

3.1  将字段很多的表分解成多个表【可以将经常使用的字段和不经常使用的字段分别存储】

3.2 增加中间表【新建中间表，将需要经常联合查询的数据，放入中间表，进行查询优化】

3.3 增加冗余字段【冗余存储字段，可以减少的连接查询】

3.4 优化插入记录的速度

a.空表批量导入数据，可以禁用索引和唯一性检查，导入后再新建索引

b.批量导入数据

c.使用load data  infle批量导入，禁用唯一检查、外键检查

3.5 分析表、检查表、优化表

mysql提供的语句，分析表主要分析关键字的分布，检查表检查表是否存在错误、优化表消除删除挥着更新造成的空间浪费

四、优化mysql服务器

4.1优化服务器硬件

   配置较大内存，通过增大系统缓冲器容量，使得数据在内存停留的时间更长，已减少磁盘IO

   合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行性处理。

   配置高速磁盘系统，减少系统读盘等待时间，提高响应速度。

   配置多处理器，mysql是多线程的数据库，多处理器可以同时执行多个线程。

4.2优化mysql参数，提高系统资源利用率

 key_buffer_size表示索引缓冲区大小

table_cache  标识同时打开的表树

query_cache_size 查询缓存区，和query_cache_type使用

sort_buffer_size 排序缓存区大小

read_buffer_size 表示每个线程扫描表分配缓冲区大小

read_rnd_buffer_size 每个线程保留缓冲区大小

innodb_buffer_pool_size innoDB表和索引的最大缓存

max_connections  数据库的最大连接数

innodb_flush_log_at_trx_commit  表示缓冲区的数据写入日志文件

back_log      表示mysql暂时停止回答新请求之前的短时间内，多少个请求会被存在堆栈中

interactive_timeout  服务器在关闭连接前等待行动的秒数

sort_buffer_size  每个需要进行排序的线程分配的缓存区的大小

thread_cache_size 表示可以服用的线程的数量

wait_timeout  表示服务器在关闭一个连接时等待行动的秒数；