优化思路：

• 开启慢查询日志，查看哪些sql耗时长
• 查看执行慢的sql的执行计划（为优化提供方向）
• 优化查询sql（怎么优化）
• 使用【show profils】查看问题sql的使用情况（使用方法是啥）
• 调整操作系统参数优化（怎么调整）
• 升级服务硬件（什么条件下升级）

慢查询日志?

慢查询日志默认关闭的，开启的方法是mysql etc目录下的配置文件中my.cnf文件中修改参数slow_query_log=on或则是slow_query_log=1开启，开启后需要重启mysql。开启后会在var/lib/mysql生成mysql(跟hostname)-slow.log。其中会记录查询时间比较长的sql语句。其中时间比较长可以用long_query_time设置阈值（默认10s），慢查询日志可能重复的数据比较多，有个mysqldumpslow可以对慢查询日志进行排序。

例如：得到按照时间排序的前10条里面含有左连接的查询语句：

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

其中-s表示用什么方式进行排序：al 平均锁定时间、ar平均返回记录时间、at平均查询时间、c计数、l锁定时间、r返回记录、t查询时间

-t是top n的意思，返回前面多少条的数据

-g 后面可以跟正则表达式，大小写不敏感。

最后的慢查询日志的文件路径。

查看执行计划（explain）?

explain结果如上，id: 表示查询分配的唯一标识符、select_type: 查询的类型、table: 查询的表、partitions: 匹配的分区 、type: join 类型、 possible_keys: 此次查询中可能选用的索引、 key: 此次查询中确切使用到的索引、 ref: 哪个字段或常数与 key 一起被使用、 rows: 显示此查询一共扫描了多少行，这个是一个估计值、filtered: 表示此查询条件所过滤的数据的百分比 、extra: 额外的信息。

id相同执行顺序自上而下；id不同的话，若是有子查询，id会自增，id越大，优先级越高；id相同和不同同时存在，优先级高的先执行，相同的自上而下执行。

select_type

1. simple表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。
2. primary 一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。
3. subquery 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
4. union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表 select_type都是union
5. union result 包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
6. dependent union 与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
7. dependent subquery 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
8. derived from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

table

查询的表名，有如下几种情况： 如果查询使用了别名，那么这里显示的是别名 如果不涉及对数据表的操作，那么这显示为null 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于 这个查询产生。 如果是尖括号括起来的<union M,N>,也是一个临时表，表示这个结果来自于union查询 的id为M,N的结果集。

partitions

分区表（对于非分区表值为null）。 5.7之后的版本默认会有 partitions 和 filtered两列，但是5.6版本中是没有的，需要 使用explain partitions select ……来显示带有partitions 的列， 使用explain extended select ……来显示带有filtered的列。

type （可以看到sql有哪些问题）

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

1. system(系统表，特殊的const)
2. const:使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
3. eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
4. ref :非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单 独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
5. fulltext:全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代 价，优先选择使用全文索引
6. ref_or_null:与ref方法类似，只是增加了null值的比较。实际用得不多。
7. unique_subquery:用于where中的in形式子查询，子查询返回不重复值唯一值
8. index_subquery 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询 去重。
9. range:用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询 去重。
10. index_merge:表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方 排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
11. index :select结果列中使用到了索引，type会显示为index
12. all：select结果列中使用到了索引，type会显示为index

最少得到range这个值，all最差，代表全表扫描。all之上都可用索引。

possible_keys：此次查询中可能选用的索引，一个或多个；

key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的 select_type这里只会出现一个。

key_len：key_len越小 索引效果越好。计算where条件后的，跟查询字段没关系。

ref：如果是使用的常数等值查询，这里会显示const；如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows：这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原 因是InnoDB里面使用了MVCC并发机制）

filtered：filtered列指示将由mysql server层需要对存储引擎层返回的记录进行筛选的估计百分比，也就是说存储 引擎层返回的结果中包含有效记录数的百分比。最大值为100，这意味着没有对行进行筛选。值从100减 小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示将与下表联接的行数。例如，如果 rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。

extra ：这个列包含不适合在其他列中显示单十分重要的额外的信息。

优化查询sql

1、索引优化

• 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的 业务场景：查询多还是增删多？
• 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询 条件放到最左边。
• 尽量使用覆盖索引，SELECT语句中尽量不要使用*。
• order by、group by语句要尽量使用到索引
• 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多 的索引键值。
• 太长的列，可以选择建立前缀索引
• 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
• order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排 序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

2、LIMIT优化

• 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描

• 处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率 会非常差。 LIMIT OFFSET , SIZE； LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。 解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写 rows;

3、其他查询优化

• 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描 的，以少关联多就可以减少这个扫描次数。
• 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时 候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描;
• 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）;
• JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。
• WHERE条件中尽量不要使用not in语句（建议使用not exists）;
• 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

使用【show profiles】查看问题sql的使用情况

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。Profiler默认关闭，可以在mysql下使用set profiling=1 开启。

开启后可以通过show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行 语句的资源使用情况。

show profiles：查看已经分析过的sql语句列表；

show profile ：具体某一条sql语句进行分析；

升级服务硬件

1、缓冲区优化

• 将数据保存在内存中，保证从内存读取数据 设置足够大的 innodb_buffer_pool_size （总内存的四分之三或则五分之四），将数据读取到内存中。

2、降低磁盘写入次数

• 对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
• 使用足够大的写入缓存 innodb_log_file_size （0.25*innodb_buffer_pool_size）
• 设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系。

3、服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高 速磁盘、提升CPU性能等。

• CPU的选择: 对于数据库并发比较高的场景，CPU的数量比频率重要。
• 对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好