MySQL优化步 (InnoDB)

MySQL优化步 (InnoDB)

优化的优先级

• 数据库结构设计
• SQL语句
• 数据库存储引擎和选择和参数配置
• 系统选择及优化
• 硬件升级

图书

• MySQL必知必会
• 高性能MySQL
• 深入浅出MySQL
• MySQL排错指南
• MySQL管理之道-性能高估、高可用和监控（第一版）
• MySQL管理之道-性能高估、高可用和监控（第二版）
• MySQL技术内幕

---

安装向导中的选项

安装过程中的选择

---

调试方法

• show status：提供服务器状态信息
• show engine innodb status 查看innodb引擎的服务器状况
• show variables
• EXPLAIN
• show profile
• performance_schema
• show processlist
• sql_no_cache
• slow_query_log

show status

show [session|global] status

• session: 当前连接，默认为session
• global: 自数据库上次启动至今

常用

• Connections: 试图连接MySQL服务器的次数
• Uptime:服务器工作时间
• Slow_queries:慢查询的次数

show status like 'Com_%' /*表示语句执行次数*/

show variables

EXPLAIN

mysql> explain select * from table_name;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | table_name | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

字段说明id表示查询中执行select子句或操作表的顺序select_type查询中每个select子句的类型table输出结果集的表type表示MySQL在表中找到的所需行的方式，或者叫访问类型possible_keys表示查询时可能使用的索引key表示实际使用的索引，若没有使用索引，显示为NULLkey_len使用到索引字段的字节数（长度）ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值rows扫描行的数量Extra包含不适合在其他列中显示但十分重要的额外信息

id

1. id相同，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

字段说明SIMPLE简单的SELECT语句（没有使用 union 或者 子查询）PRIMARY查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARYUNION在一个UNION语句中第二或之后的SELECT语句DEPENDENT UNION在一个UNION语句中第二或之后的SELECT语句，取决于外层的查询UNION RESULTUNION的结果集SUBQUERY子查询中的第一个SELECTDEPENDENT SUBQUERY子查询中的第一个SELECT，取决于外面的查询DERIVED派生表的SELECT, FROM子句的子查询UNCACHEABLE SUBQUERY不能将结果缓存的子查询，必须重新计算外部查询的每一行UNCACHEABLE UNION在一个UNION中第二或之后的SELECT查询属于UNCACHEABLE SUBQUERY（请看UNCACHEABLE SUBQUERY)

说明：

• DEPENDENT：典型代表就是使用了相关子查询。（相关子查询：子查询里包含了一个同时在子查询里，又在外部查询的表的查询）
• DEPENDENT SUBQUERY: 它不同于UNCACHEABLE SUBQUERY的求值。对于DEPENDENT SUBQUERY，子查询对于外部上下文里每一个集合中不同的变星值仅仅重新计算一次。而对于UNCACHEABLE SUBQUERY，子查询对于外部上下文里的每一行都会重新计算一次.

type

常用的类型有（从左到右，性能从差到好）：

+-----+-------+-------+------+--------+----------------+------+
| ALL | index | range | ref  | eq_ref | const, system  | NULL |
+-----+-------+-------+------+--------+----------------+------+

字段说明ALL全表扫描，MySQL遍历全表来找到匹配的行index索引全扫描，MySQL遍历整个索引来查询匹配的行，index与ALL区别为index类型只遍历索引树。range索引范围扫描，只检索给定范围的行，使用一个索引来选择行，常见于<、<=、>、>=、between等操作符。ref使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行eq_ref类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件const, system当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量NULLMySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

explain extended和show warnings

explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过MySQL的show warnings命令得到。

官方说明

show profile

使用show profile和 show profiles我们可以更清楚的了解SQL执行的过程。

• 执行过程

1. set profiling = 1 # 打开profiling
2. 执行查询
3. show profiles;
4. 通过show profile for query N语句能够看到执行过程中线程的每个状态和消耗的时间。

performance_schema

• 可以代替show profile
• 5.5最好不用，性能有问题， 5.6以后建议开启

no_sql_cache

SQL_NO_CACHE的真正作用是禁止缓存查询结果，但并不意味着cache不作为结果返回给query。

使用方法：select no_sql_cache ...

show full processlist

可以用语句SELECT * FROM information_schema.processlist代替show full processlist并可以过虑想要的数据。

字段说明id不用说了吧，一个标识，你要kill一个语句的时候很有用。user显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time此这个状态持续的时间，单位是秒。state显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。info显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

slow_query_log

使用show variables like '%slow%';查看是否启用慢查询。

参数说明slow_query_log指定是否开启慢查询日志long_query_time设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10sslow_query_log_file指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.logmin_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志log_queries_not_using_indexes不使用索引的慢查询日志是否记录到索引

打开慢查询配置

slow_query_log = 1 
slow_query_log_file
long_query_time = 2
log_queries_not_using_indexes
log_slow_queries=/data/3306/slow-log.log

慢查询日志分析工具

• mysqldumpslow

• • mysqldumpslow -s -r -t 10 slow-mysql.log

  • • -s order (c,t,1,r,at,al,ar) 指定按哪种排序方式输出结果
    • -t top 指定取前几条作为结束输出

• pt-query-digest

• • pt-query-digest --explain h=127.0.0.1,u=root, p=password slow-mysql.log

---

优化数据库（最重要）

目的

• 减少数据冗余
• 尽量避免数据维护中出现插入、更新、删除异常
• 节约数据存储空间
• 提高查询效率

数据库设计步骤

• 需求分析：全面了解产品的存储需求
• 逻辑设计：设计数据的逻辑存储结构，数据实体之间的逻辑关系，解决数据冗余和数据维护异常
• 物理设计：根据数据库特点进行表结构设计（关系型数据库、非关系型数据库、存储引擎）
• 维护优化：根据实际情况对索引、存储结构等进行优化

范式

遵循三范式就可以设计出符合需求的数据库结构，符合，不是最优的。

• 1NF：

• • 数据库表中的字段都是单一属性的，不可再分。
  • 单一属性的列是由基本的数据类型所构成的
  • 设计出来的表都是简单的二维表

• 2NF：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

• 3NF：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

在设计数据库中要最大程序的遵守三范式，特别是对于OLTP型的系统。

注意：三范式最大的问题在于查询时通常需要Join很多表，而这会导致查询效率很低，所以有时候基于性能考虑，我们需要有意违反三范式，适度的做冗余。

最常见的反范式化的数据的方法是复制或者缓存，在不同的表中存储相同的特定列，使用触发器更新缓存值。 另一个从父表冗余一些数据到的理由是排序的需要。

通过触发器

表设计

1. 更小的通常更好，一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
2. 简单的数据类型，简单数据类型通常需要更好的CPU周期。
3. 尽量避免使用NULL，通常情况下最好指定列为NOT NULL，除非直接的城崾存储NUL值。注意：把可为NULL的列改为NOT NULL带来的恨不能提升比较小，没有必要首先在现有的schema中查找并修改掉这种情况。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
4. 避免太多的列。
5. 避免太多的关联，单个查询最好在12个表以内做关联，最多为61个表。

不在数据库中存储图片、文件等 所有字段均定义为NOT NULL ，除非你真的想存Null 字段类型在满足需求条件下越小越好，使用UNSIGNED存储非负整数 ，实际使用时候存储负数场景不多 使用TIMESTAMP存储时间 使用varchar存储变长字符串 ，当然要注意varchar(M)里的M指的是字符数不是字节数；使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ，这种方式只能存储IPv4，存储不了IPv6 使用DECIMAL存储精确浮点数，用float有的时候会有问题 少用blob text

字段类型的选取

整数类型

类型存储空间存储范围存储范围数值TINYINT8-27~ 27-1-128_{127SMALLINT16-215} 215-1-32768_{32767MEDIUMINT24-223} 223-1-8388608_{8388607INT32-231} 231-1-2147483648_{2147483647BIGINT64-263} 263-1-9223372036854775808~9223372036854775807

有符号和无符号使用相同的存储空间，并具有相同的功能，如果不允许负值，大致可以使正数的上限提高一倍。

使用整数来存储IPV4，它实际是32位无符号整数，不是字符串，使用小数点将地址发成四段只是为了让人员阅读，通过INET_ATON()和INET_NTOA()函数进行转换。