mysql的innodb优化举例_mysql库表优化实例

一、SQL优化

1、优化SQL一般步骤

1.1 查看SQL执行频率

SHOW STATUS LIKE 'Com_%';

Com_select：执行SELECT操作的次数，一次查询累加1。其他类似

以下参数只针对InnoDB存储引擎，累加算法略有不同

Innodb_rows_read：SELECT查询操作插入的行数

Innodb_rows_inserted/updated/deleted：执行INSERT/UPDATE/DELETE操作的行数

通过以上参数，可以了解当前数据库应用是查询为主还是写入数据为主。

对于事务型的应用。通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常的频繁的数据库，可能意味着应用编写存在问题。

基本情况了解：

Connections：试图连接MySQL服务器的次数。

Uptime：服务器工作时间

Slow_queries：慢查询的次数

1.2 定位执行效率比较低的SQL语句

- 通过慢查询日志定位慢SQL，用--log-slow-queries[=file_name]选项启动时，mysqld会写一个所有执行时间超过long_query_time秒的SQL语句的日志文件。

- 使用SHOW FULL PROCESSLIST; 查看当前MySQL在进行的线程，同时对一些锁表操作进行优化。

1.3 通过EXPLAIN分析慢SQL

语法：EXPLAIN SQL语句

结果：

- select_type：表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等。

- table：输出结果的表名

- type：表示MySQL在表中找到所需行的方式，或者叫访问类型

常见的有：ALL index range ref eq_ref const,system NULL，从左到右，性能由最差到最好。

type=ALL：全表扫描。

type=index：索引全扫描，MySQL遍历整个索引来查询。

type=range：索引范围扫描，常见于、 >=、 between。

type=ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录。

type=eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique index作为关联条件。

type=const/system：单表中最多有一个匹配行，查询起来非常迅速，一般主键primary key或者唯一索引unique index进行的查询，通过唯一索引uk_email访问的时候，类型type为const；而从我们构造的仅有一条记录的a表中检索时，类型type为system。

type=NULL：MySQL不用访问表或者索引，就能直接得到结果。

类型type还有其他值，如ref_or_null(与ref类似，区别在于条件中包含对NULL的查询)、index_merge(索引合并优化)、unique_subquery(in的后面是一个查询主键字段的子查询)、index_subquery(与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询)

- possible_keys：表示查询时可能使用的索引。

- key：表示实际使用的索引。

- key_len：使用到索引字段的长度。

- rows：扫描行的数量

- Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

Using where：表示优化器除了利用索引来加速访问之外，还需要根据索引回表查询数据。

1.4 通过show profile分析SQL

查看当前MySQL是否支持profile

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：set profiling=1;

使用方法：

- 执行统计查询：

- 查找上述SQL的query ID：

- 查找上述SQL执行过程中每个线程的状态和消耗时间：

Sending data状态表示MySQL线程开始访问数据并行把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

- 查看详细信息并排序：

SELECT

STATE,

SUM(DURATION) AS TR,

ROUND(

100 * SUM(DURATION) / (

SELECT

SUM(DURATION)

FROM

information_schema.PROFILING

WHERE

QUERY_ID = 3

),

2

) AS PR,

COUNT(*) AS Calls,

SUM(DURATION) / COUNT(*) AS "R/Call"

FROM

information_schema.PROFILING

WHERE

QUERY_ID = 3

GROUP BY

STATE

ORDER BY

TR DESC;

进一步获取all、cpu、block io、context switch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间，例如，选择查看CPU的耗费时间。

此时可获取到sending data时间主要消耗在CPU上

提示：InnoDB引擎count(*)没有MyISAM执行速度快，就是因为InnoDB引擎经历了Sending data状态，存在访问数据的过程，而MyISAM引擎的表在executing之后直接就结束查询，完全不需要访问数据。

2、索引问题

索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。

2.1 存储引擎的分类

- B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引。

- HASH索引：只有Memory引擎支持。

- R-Tree索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型。

- Full-text：全文索引是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始对其支持。

MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符进行索引，但是在排序Order By和分组Group By操作的时候无法使用。前缀索引创建例子：create index idx_title on film