MYSQL从0到1（八）：优化

这两天看了享学课题的公开课，挺好的，总结一下

针对面试题：当服务器运行慢了，你对MYSQL采取什么优化措施？

1、硬件方面：服务器硬件：机械硬盘效率相比固态硬盘约10倍差距，尽量采取固态硬盘。

2、软件方面:  MySQL服务器优化，Linux（稳定），Windows相对不稳定，运行时可能产生垃圾，所有尽量安装在Linux上。

3、程序员的对SQL语句的优化：

（1）首先，我们要定位究竟是哪里慢了？开启慢查询日志

         MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

如何开启慢查询

，这里我针对当前数据库：设置slow_query_log的值来开启（当前数据库开启，如果MySQL重启后则会失效）

show variables  like '%slow_query_log%';  查看慢查询是否开启 OFF / ON
set global slow_query_log=1;              开启慢查询
show variables like 'long_query_time';    查看查询限制时间，默认10秒 
set global long_query_time=4;             设置限制时间为4秒，大于4秒的SQL才会记录到慢查询日志

慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log

在MySQL里面执行SQL语句，然后我们去检查对应的慢查询日志。

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 +日志文件路径

（2）发现了最慢的SQL，进行优化

1、表格设计方面：反范式化设计

1.1 范式化设计

范式化设计的优点:减少冗余。对新增删除修改友好，对查询不友好，需要很多的拼接。

有时候我们稍微违反下范式化设计，改善我们的查询。以空间换时间，适当冗余。

2、可以把子查询改为联结查询

3、索引优化

（1）首先明白什么是索引?

索引（Index）是帮助MySQL高效获取数据的数据结构，举例书目录（树形）

（2）分类：

   普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

   唯一索引：索引列的值必须唯一，但允许有空值

   复合索引：即一个索引包含多个列

问题一：我们都知道索引能加快查询，但真的能加快查询吗?

关键看两个：1、建立了索引是否能被用到？2、使用充分地使用到了索引？----执行计划：EXPLAIN + SQL

执行计划是什么

   使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

主要看三点：

key :代表是否有用到索引

如果没使用索引，值为NULL，使用了覆盖索引，则该索引与查询的select字段重叠。

key_len : 可以计算索引是否被是否充分

如果是衡量索引是否充分使用，越长越好，如果是来检验表的物理设计，越短越好。

type : type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是 system>const>eq_ref>ref>range>index>ALL

• system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
• const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快
•       eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

• range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

• all：Full Table Scan，将遍历全表以找到匹配的行

建立索引十个策略：（索引顺序的策略）

策略1.尽量全值匹配 ：建立了复合索引 name, age, pos

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

策略2.最佳左前缀法则：以火车为例子理解

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

一定有火车头，如果中间车厢断了，后面的车厢也就用不了了。

策略3.不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

策略4.范围条件放最后

存储引擎不能使用索引中范围条件右边的列

策略5.覆盖索引尽量用 

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

策略6.不等于要甚用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

策略7.Null/Not 有影响

注意null/not null对索引的可能影响

策略8.Like查询要当心

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

策略9.字符类型加引号

字符串不加单引号索引失效

策略10.OR改UNION效率高

最后总结：