「MySQL」MySQL性能优化基础

Posted on | In

1. MySQL基础操作

1.1 MySQL备份与恢复

  1. 备份：在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。
    • 语法：mysqldump -u 用户名 -p 数据库名> 磁盘SQL文件路径
    • 由于mysqldump命令不是sql命令，需要在dos窗口下使用。
    • 仅仅只会备份数据库中的表和数据，恢复时需要先手动创建数据库。
  2. 恢复：先手动创建数据库：create database 数据库名
    • 然后dos窗口：mysql -u 用户名-p 导入库名< 磁盘SQL文件绝对路径

1.2 MySQL事务基础

  1. 特性(ACID)：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
  2. 事务隔离级别：读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  3. MYSQL事务处理主要有两种方法：
    1. 用 BEGIN, ROLLBACK, COMMIT来实现:
      • BEGIN 开始一个事务
      • COMMIT 事务确认
      • ROLLBACK 事务回滚
    2. 直接用 SET 来改变 MySQL 的自动提交模式:
      • SET AUTOCOMMIT=0 禁止自动提交
      • SET AUTOCOMMIT=1 开启自动提交
  4. 事务并发操作出现几种问题: 丢失修改数据、读“脏”数据、数据不一致

1.3 查看设置MySQL编码

  1. 查看：mysql> show variables like 'character%'
  2. 设置: # vi /etc/my.cnf：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    [mysqld] 
    character‐set‐server=utf8
    collation‐server=utf8_general_ci
    sql_mode='NO_ENGINE_SUBSTITUTION'
    [mysql] 
    default‐character‐set = utf8 
    [mysql.server] 
    default‐character‐set = utf8 
    [mysqld_safe] 
    default‐character‐set = utf8 
    [client] 
    default‐character‐set = utf8
  3. mysql的主配置文件: /etc/my.cnf
  4. 数据库文件存放位置: /var/lib/mysql
  5. 数据库的日志输出存放位置: /var/log/mysql
  6. 端口: Netstat –nltp 看是否能找到3306的端口

1.4 范式

概念：范式就是符合某一规范级别的关系模式的集合。共有7种范式：1NF⊃2NF⊃3NF⊃BCNF⊃4NF⊃5NF⊃6NF

  1. 第一范式(1NF, First Normal Form)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);
    • 例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;
  2. 第二范式(2NF, Second Normal Form)：一个表必须有主键,即每行数据都能被唯一的区分(2NF必须先满足第一范式);
  3. 第三范式(3NF, Third Normal Form)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有冗余字段(3NF必须先满足第二范式);
    • 备注：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;
    • 例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;

2. SQL语句优化

2.1 通过慢查日志发现有问题的SQL

  1. 查询次数多且每次查询占用时间长的sql
    • 通常为pt-query-digest分析的前几个查询；该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL
  2. IO大的sql
    • 注意pt-query-digest分析中的Rows examine项。扫描的行数越多，IO越大。
  3. 未命中的索引的SQL
    • 注意pt-query-digest分析中的Rows examine 和Rows Send的对比。说明该SQL的索引命中率不高，对于这种SQL，我们要重点进行关注。
  4. 通过explain查询分析SQL的执行计划, SQL的执行计划侧面反映出了SQL的执行效率，

2.2 常见SQL优化手段

  1. 函数Max()的优化
    • 在求max的字段建索引
  2. 函数Count()的优化：
    • Count(*):是包含null值；Count(id)：不包含null值
  3. 子查询的优化
    • 子查询是我们在开发过程中经常使用的一种方式，在通常情况下，需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系，要注意重复数据(distinct去重)。
    • 在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是Using（如果两个表的关联字段名是一样）。
  4. group by的优化:
    • 最好使用同一表中的列，在子查询中分组
  5. Limit查询的优化：Limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。
    • 优化步骤1：使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。
    • 优化步骤2：记录上次返回的主键， 在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）
    • 注意事项：主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足一页的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。

3. 索引的优化

3.1 索引基础概念

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

  • 索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的！
  • 创建索引：在执行create table语句时可以创建索引，也可以单独用create index或alter index来为表增加索引。不能用create index语句创建primary key索引。
  • 在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为primary key或unique索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
  • primary key索引和unique索引非常类似。事实上，primary key索引仅是一个具有名称PRIMARY的unique索引。
  • 查看索引：show index from tblname;或show keys from tblname;

3.2 索引的创建与删除

  1. 创建普通索引：
    • 方式1：create index 索引名 on 表名(列名)
    • 方式2：alter table 表名 add index 索引名(列名)
    • 方式3：创建表的时候直接指定: ,index [索引名] (列名)
  2. 删除索引： drop index [索引名] on 表名
  3. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  4. 创建唯一索引：
    • 方式1：create unique index 索引名 on 表名(列名)
    • 方式2：alter table 表名 add unique 索引名(列名)
    • 方式3：创建表的时候直接指定: ,unique [索引名] (列名)

3.3 使用索引的场景

  1. 表的主键自动建立唯一索引
  2. 表的字段唯一约束
  3. 直接条件查询的字段（在SQL中用于条件约束的字段）
  4. 查询中与其它表关联的字段
  5. 查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度）
  6. 查询中统计或分组统计的字段
  7. 表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）
  8. 经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）
  9. 数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）
  10. 经常和主字段一块查询但主字段索引值比较多的表字段
  11. 对千万级MySQL数据库建立索引的事项及提高性能的手段

3.4 索引的维护及优化（重复及冗余索引）

增加索引会有利于查询效率，但会降低insert，update，delete的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引

  • 重复索引：重复索引是指相同的列以相同的顺序建立的同类型的索引，如在primary key再建立唯一索引就是重复索引
  • 冗余索引：冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，如对于innodb来说，每一个索引后面，实际上都会包含主键，这时候我们建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。
  • 工具：使用**pt-duplicate-key-checker工具检查重复及冗余索引**: pt-duplicate-key-checker -uroot -padmin -h 127.0.0.1
  • 索引维护的方法: 由于业务变更，某些索引是后续不需要使用的，就要进行删除。
  • 在mysql中，目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析；pt-index-usage -uroot -padmin /var/lib/mysql/mysql-host-slow.log

3.5 设计MySql索引的注意事项

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

  1. 创建索引
    • 对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。
    • 但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
  2. 复合索引
    • 比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
    • 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
    • 因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
  3. 索引不会包含有NULL值的列
    • 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  4. 使用短索引
    • 对字符串列进行索引，如果可能应该指定一个前缀长度。
    • 例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  5. 排序的索引问题
    • mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  6. like语句操作
    • 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。
  7. 不要在列上进行运算
    • select * from users where YEAR(adddate)
  8. 不使用NOT IN操作
    • NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

4. MYSQL数据库设计规范与原则

4.1 设计规范

  1. 命名规范
    • 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_’组成
    • 命名简洁明确,多个单词用下划线’_’分隔,长度不超过30个字符
    • 除非是备份数据库可以加0-9的自然数,如：'user_db_20191210'
    • 表前缀可以有效的把相同关系的表显示在一起,如：'user_'
    • 每个表中必须有自增主键
    • 表与表之间的相关联字段名称要求尽可能的相同
  2. 字段类型规范
    • 用尽量少的存储空间来存数一个字段的数据, 例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
    • IP地址最好使用int类型;
    • 固定长度的类型最好使用char,例如：邮编;
    • 能使用tinyint就不要使用smallint,int;
    • 最好给每个字段一个默认值, 最好不能为null;
  3. 索引规范
    • 命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;
    • 为每个表创建一个主键索引;
    • 为每个表创建合理的索引;
    • 建立复合索引请慎重;

4.2 设计原则

  1. 核心原则
    • 不在数据库做运算;
    • cpu计算务必移至业务层;
    • 控制列数量(字段少而精,字段数建议在20以内);
    • 平衡范式与冗余(效率优先；往往牺牲范式)
    • 拒绝3B(拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch);
  2. 字段类原则
    • 用好数值类型(用合适的字段类型节约空间);
    • 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
    • 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
    • 少用text类型(尽量使用varchar代替text字段);
  3. 索引类原则
    • 合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
    • 字符字段必须建前缀索引;
    • 不在索引做列运算;
    • innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
    • 不用外键(由程序保证约束);
  4. sql类原则
    • sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
    • 简单的事务;
    • 避免使用trig/func(触发器、函数不用客户端程序取而代之);
    • 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);
    • OR改写为IN(or的效率是n级别);
    • OR改写为UNION(mysql的索引合并很弱智);
    • 避免负向%;
    • 慎用count(*);
    • limit高效分页(limit越大，效率越低);
    • 使用union all替代union(union有去重开销);
    • 少用连接join;
    • 使用group by;
    • 请使用同类型比较;
    • 打散批量更新;

5. 数据库结构的优化

5.1 选择合适的数据类型

数据类型的选择，重点在于“合适”二字

  1. 使用可以存下你的数据的最小的数据类型。（时间类型数据：可以使用varchar类型，可以使用int类型，也可以使用时间戳类型）
  2. 使用简单的数据类型，int要比varchar类型在mysql处理上简单。（int类型存储时间是最好的选择）
  3. 尽可能的使用not null定义字段。（innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
  4. 尽量少用text类型，非用不可时最好考虑分表。

5.2 数据库表的范式化优化

  1. 表范式化
    • 范式化是指数据库设计的规范，目前说道范式化一般是指第三设计范式。也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。
  2. 反范式化
    • 反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

5.3 数据库表的垂直拆分

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。

  • 垂直拆分原则
    1. 把不常用的字段表单独存放到一个表中。
    2. 把大字段独立存放到一个表中。
    3. 把经常一起使用的字段放到一起。

5.4 数据库表的水平拆分

表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的

  1. 水平拆分原因
    • 如果单表的数据量达到上亿条，那么这时候我们尽管加了完美的索引，查询效率低，写入的效率也相应的降低。
  2. 如何将数据平均分为N份
    1. 对customer_id进行hash运算，如果要拆分为5个表则使用mod（customer_id，5）取出0-4个值。
    2. 针对不动的hashid把数据存储到不同的表中。
  3. 水平拆分面临的挑战
    1. 夸分区表进行数据查询
      • 前端业务统计：业务上给不同的用户返回不同的业务信息，对分区表没有大的挑战。
    2. 统计及后台报表操作
      • 但是对后台进行报表统计时，数据量比较大，后台统计时效性比较低，后台就用汇总表，将前后台的表拆分开。

6. 数据库系统配置优化

数据库是基于操作系统的，目前大多数MySQL都是安装在linux系统之上，所以对于操作系统的一些参数配置也会影响到MySQL的性能

6.1 操作系统的优化

网络方面的配置，要修改/etc/sysctl.conf

  1. 增加tcp支持的队列数
    • net.ipv4.tcp_max_syn_backlog = 65535
  2. 减少断开连接时，资源回收(tcp有连接状态)
    • net.ipv4.tcp_max_tw_buckets = 8000
    • net.ipv4.tcp_tw_reuse = 1
    • net.ipv4.tcp_tw_recycle = 1
    • net.ipv4.tcp_fin_timeout = 10
    • 说明： TCP是有连接状态，通过netstat查看连接状态，经常会看到timeout状态或者timewait状态连接，为了加快timewait状态的连接回收，就需要调整上面的四个参数，保持TCP连接数在一个适当的状态。

6.2 打开文件数的限制

打开文件数的限制，可以使用ulimit –a查看目录的各个限制，可以修改/etc/security/limits.conf文件

  • limits.conf中增加以下内容以修改打开文件数量的限制（永久生效）
    • *Soft nofile 65535
    • *Hard nofile 65535
  • 如果一次有效，就要使用ulimit –n 65535即可。（默认情况是1024）
  • 除此之外最好在MySQL服务器上关闭iptables，selinux等防火墙软件

6.3 MySQL配置文件优化

Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/my.cnf或/etc/mysql/my.cnf

  • MySQL查找配置文件的顺序可以通过以下命令获得：
    • /usr/sbin/mysqld --verbose --help | grep -A 1 'default options'
    • 注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的。
6.3.1 my.cnf常用 连接请求 参数
  1. max_connections：最大连接数
    • 如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，MySQL会为每个连接提供连接缓冲区，连接数越多就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
    • 数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。
    • max_used_connections / max_connections * 100% （**理想值≈ 85%**）: 响应的连接数/最大连接数
  2. back_log：能暂存的连接数量
    • 如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
    • 默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。
  3. interactive_timeout：服务器关闭交互式连接前等待活动的秒数
    • 默认数值是28800，可调优为7200。
  4. wait_timeout：服务器关闭非交互连接之前等待活动的秒数
    • 指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
6.3.1 my.cnf常用 缓冲区 参数
  1. key_buffer_size: 指定索引缓冲区的大小
    • 它决定索引处理的速度，尤其是索引读的速度。
    • 它只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值
  2. query_cache_size ：查询缓存的内存
    • 使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
    • 通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
    • 与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。
      • query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。
      • query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。
      • query_cache_min_res_unit指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。
  3. record_buffer_size：顺序扫描缓冲区大小
    • 每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
    • 默认数值是131072(128K)，可改为16773120 (16M)
  4. read_rnd_buffer_size：随机读缓冲区大小
    • 当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。一般可设置为16M
  5. sort_buffer_size：排序扫描缓冲区大小
    • 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
    • 默认数值是2097144(2M)，可改为16777208 (16M)。
  6. join_buffer_size：联合查询缓冲区大小
    • 联合查询操作所能使用的缓冲区大小。
    • record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100
  7. table_cache：表高速缓存的大小
    • 表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
    • 1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。
  8. max_heap_table_size：用户可以创建的内存表(memory table)的大小
    • 这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
    • 这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
  9. tmp_table_size：临时表的大小
    • 通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
    • 每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数
    • 比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，应该相当好了
    • 默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
  10. thread_cache_size：可以复用的保存在中的线程的数量
    • 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
    • 通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。默认值为110，可调优为80。
  11. thread_concurrency：同一时间运行的线程系统提示所需数量的线程
    • 推荐设置为服务器 CPU核数的2倍，
    • 例如双核的CPU, 那么thread_concurrency的应该为4；2个双核的cpu, thread_concurrency的值应为8。默认为8
    • 这个参数已经在5.7.2版本的MySQL中被移除
6.3.1 my.cnf常用 配置InnoDB的 参数
  1. innodb_buffer_pool_size：缓冲池大小
    • 对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
    • InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
    • 根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。
  2. innodb_flush_log_at_trx_commit：主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个
    • 设置为0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
    • 设置为1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；
    • 设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
    • 实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
    • 根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
  3. innodb_log_buffer_size：log缓存大小
    • 一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。可设置为4M或8M。
  4. innodb_additional_mem_pool_size：内存池大小
    • 该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
    • 根据MySQL手册，对于2G内存的机器，推荐值是20M，可适当增加。
    • innodb_thread_concurrency=8，推荐设置为 2*(NumCPUs+NumDisks)，默认一般为8
  5. skip-name-resolve：禁止域名解析
    • 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
    • 但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

7. MySQL的执行顺序

MySQL的语句一共分为11步，最先执行的总是FROM操作，最后执行的是LIMIT操作。

  • 其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。
  • 如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。
    • ⑧select
    • ⑨distinct <字段名>
    • ①from <表名>
    • ③<连接类型>join <表名>
    • ②on<连接条件>
    • ④where<查询条件>
    • ④group by<分组字段>
    • ⑥with{cube|rollup}
    • ⑦having<查询条件>
    • ⑩order by<排序字段>
    • ⑪limit<分页数量>

查询处理的每一个阶段分析：

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON: 对虚表VT1进行ON筛选，只有那些符合<连接条件>的行才会被记录在虚表VT2中。
  3. JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
  4. WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合<where查询条件>的记录才会被插入到虚拟表VT4中。
  5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
  6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
  7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having查询条件>的记录才会被 插入到虚拟表VT7中。

8. MySQL执行引擎

8.1 MyISAM存储引擎

  • 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
  • 支持3种不同的存储格式，分别是：静态表；动态表；压缩表
  • 静态表：
    • 表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。
  • 动态表：
    • 记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能
  • 压缩表：
    • 因为每个记录是被单独压缩的，所以只有非常小的访问开支

8.2 InnoDB存储引擎

  • 提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。 
  • InnoDB存储引擎的特点：支持自动增长列，支持外键约束

8.3 MEMORY存储引擎

  • Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。 
  • MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
  • Hash索引优点： 
    • Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

     

  • Hash索引缺点：
    • 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
  • Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

8.4 MERGE存储引擎

  • Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。