SQL与数据库优化

SQL与数据库优化

文章内容:

• 1. 为什么要进行数据库的优化
• 2. mysql优化方案流程
• 3. SQL及索引优化 : 准备数据, 如何发现有问题的SQL
• 4. mysql慢查询日志分析工具:
  • mysqldumpslow
  • pt-query-digest
• 5. explain关键词分析SQL的执行计划
• 6. 索引优化 :
• 7. 数据库表结构的优化
• 8. 数据库相关的配置优化
• 9. SQL的执行顺序
• 10. mysql的执行引擎的介绍

1. 为什么要进行数据库优化

• 1. 避免网站页面出现访问错误

  • 由于数据库连接timeout产生页面5xx错误 , to many connection

  • 由于慢查询造成页面无法加载 : 由于数据库无法及时的返回数据, 导致页面一直无法返回

  • 由于阻塞造成数据无法提交 :

• 2. 增加数据库的稳定性
  • 很多数据库问题都是由于低效的查询引起的
• 3. 优化用户体验
  • 流畅页面的访问速度
  • 良好的网站功能体验

2. mysql数据库优化

​ 可以从哪几个方面进行数据库的优化？如下图所示：

A、SQL及索引优化
    根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
B、数据库表结构优化
    根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。
C、系统配置优化
    大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。
D、硬件配置优化
    选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，ＩＯ操作并不是减少阻塞。

注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。

3. SQL及索引优化

3.1 准备数据

在mysql官网中, 给我们提供了一个实例数据库, 直接将这个实例数据库导入即可

• 1. 页面地址: https://dev.mysql.com/doc/sakila/en/sakila-installation.html

• 2. 点击实例数据下载地址, 下载即可

• 3. 关于sakila-db.zip压缩包所包含的文件如下解释

sakila-schema.sql文件包含创建Sakila数据库结构所需的所有CREATE语句，包括表、视图、存储过程和触发器。
sakila-data.sql文件包含填充sakila-schema创建的结构所需的INSERT语句。以及必须在初始数据加载之后创建的触发器的定义。
sakila.mwb文件是一个MySQL工作台数据模型，您可以在MySQL Workbench中打开它来检查数据库结构。

• 4. 将 sakila-db.zip 上传到linux, 并解压

使用rz命令上传即可: 如果使用rz显示没有此命令, 输入 yum -y install lrzsz 下载即可

由于是一个zip文件, 需要下载 unzip  才能解压压缩包:  yum -y install unzip

解压压缩包: unzip sakila-db.zip

• 5. 在数据库中创建对应表和库

连接数据库: shell> mysql -u root -p123456
导入数据:  mysql> SOURCE /root/sakila-db/sakila-schema.sql;

• 6. 导入数据

mysql> SOURCE /root/sakila-db/sakila-data.sql;

• 7. 验证是否成功

USE sakila;  # 使用数据库
SHOW TABLES; # 查看所有表

SELECT COUNT(*) FROM film;

SELECT COUNT(*) FROM film_text;

3.2 查看表结构

可通过sqlyog查看基本结构关系

3.3 如何发现有问题的SQL

mysql提供了慢查询日志查看功能, 可以帮助查询某一条SQL执行的一些状态

• 1. 查询mysql是否开启慢查询日志

show variables like 'slow_query_log';

• 2. 开启mysql的慢查询日志:

show variables like 'slow_query_log'  ;
//查看是否开启慢查询日志

set global log_queries_not_using_indexes=on; // 将不使用索引的慢查询日志进行记录
set global slow_query_log=on;  //开启慢查询日志

show variables like 'slow_query_log_file';
//查看慢查询日志存储的位置

set global long_query_time=1;  
//大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉

• 3)检测是否已经开启:

Show databases;
Use sakila;
select * from store;  // 执行查询
select * from staff;  // 执行查询

监听日志文件，看是否写入 : 注意  后面的日志文件的路径需要通过查询slow_query_log_file参数
tail -100f /var/lib/mysql/node01-slow.log

---

mysql慢查询日志存储的格式说明:

说明：
    1、# Time: 190412 22:05:02    >>>      查询的执行时间
    2、# User@Host: root[root] @ localhost []  Id:     2 >>> 执行sql的主机信息
    3、# Query_time: 0.001274  Lock_time: 0.000164 Rows_sent: 7  Rows_examined: 7   >>>> SQL的执行信息：
            Query_time：SQL的查询时间
            Lock_time：锁定时间
            Rows_sent：所发送的行数
            Rows_examined：所扫描的行数
    4、SET timestamp=1555077861; >>>> SQL执行时间
    5、select * from shop;  >>>>> SQL的执行内容

---

如何通过慢查日志发现有问题的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.MySQL慢查日志分析工具

4.1 mysqldumpslow

​ 如何进行查看慢查询日志，如果开启了慢查询日志，就会生成很多的数据，然后我们就可以通过对日志的分析，生成分析报表，然后通过报表进行优化。

• 如何使用此工具:
  • 可通过命令 : mysqldumpslow -h 查看使用教程

mysqldumpslow -v  : 查看verbose(详细配置)信息

mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log : 查看慢查询日志的前10个分析结果

上图两条就是分析的结果，每条结果都显示是执行时间，锁定时间，发送的行数，扫描的行数

这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化锁表现的数据还是比较少

4.2 pt-query-digest

​ 作为一名优秀的mysql dba也需要有掌握几个好用的mysql管理工具，所以我也一直在整理和查找一些能够便于管理mysql的利器。以后的一段时间内，将会花一大部分的精力去搜索这些工具。

性 能的管理一直都是摆在第一位的，dba的很多工作管理层都看不到也没有办法衡量价值，但是如果一个系统慢的跟蜗牛一样，dba通过监控调优把系统从崩溃边 缘重新拉回到高铁时代。这种价值和触动应该是巨大的。（很多企业的领导认为系统跑不动了就需要换更快的CPU、更大的内存、更快的存储，而且这还不是少 数，所以DBA的价值也一直体现不出来，薪水自然也就不会很高）

mysql 的日志是跟踪mysql性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪；mk-query-digest这个工具网上号称mysql dba必须掌握的十大工具之首。

pt-query-digest 是用于分析mysql慢查询的一个工具，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。

• 1） 安装pt-query-digest 工具

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum install -y  percona-toolkit-2.2.16-1.noarch.rpm

• 2. 检测是否安装完毕

命令行中输入：pt-summary

显示如下图所示：说明安装成功！输入【[root@node03 mysql]# pt-query-digest --help】

• 3. pt-query-digest基本使用

1) 查看服务器信息
    命令：pt-summary
2) 查看磁盘开销使用信息
    命令：pt-diskstats
3) 查看mysql数据库信息
    命令：pt-mysql-summary --user=root --password=123456

4) 分析慢查询日志
    命令：pt-query-digest /data/mysql/data/db-3-12-slow.log
5) 查找mysql的从库和同步状态
    命令：pt-slave-find --host=localhost --user=root --password=123456
6) 查看mysql的死锁信息
    命令：pt-deadlock-logger --user=root --password=123456 localhost
7) 从慢查询日志中分析索引使用情况
    命令：pt-index-usage slow_20131009.log
8) 查找数据库表中重复的索引
    命令：pt-duplicate-key-checker --host=localhost --user=root --password=123456
9) 查看mysql表和文件的当前活动IO开销
    命令：pt-ioprofile
10) 查看不同mysql配置文件的差异
    命令: pt-config-diff /etc/my.cnf /etc/my_master.cnf
11) pt-find查找mysql表和执行命令，示例如下
    查找数据库里大于2G的表： pt-find --user=root --password=123456 --tablesize +2G
    查找10天前创建，MyISAM引擎的表：pt-find --user=root --password=123456 --ctime +10 --engine MyISAM
    查看表和索引大小并排序: pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort -rn
12) pt-kill 杀掉符合标准的mysql进程
    显示查询时间大于60秒的查询 : pt-kill --user=root --password=123456 --busy-time 60 --print
    kill掉大于60秒的查询 : pt-kill --user=root --password=123456 --busy-time 60 --kill
13) 查看mysql授权
    1、pt-show-grants --user=root --password=123456
    2、pt-show-grants --user=root --password=123456 --separate –revoke
14) 验证数据库复制的完整性
    命令: pt-table-checksum --user=root --password=123456

其他导出相关的命令

5. 通过explain查询分析SQL的执行计划

explain : SQL的执行计划侧面反映出了SQL的执行效率，具体执行方式只需要在执行的SQL前面加上explain关键词即可；

• 各个字段说明

1）、id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。
 
2）、select_type列常见的有：

A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个

B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个

C：union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

D：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

F：subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery

G：dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

H：derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
 
3）、table
显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。
 
4）、type
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref

D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值

H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range

K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
 
5）、possible_keys : 在查询一条SQL的时候, 这个SQL有可能会使用到多个索引,但是SQL在执行的时候, 只会选择其中的一个索引来使用

查询可能使用到的索引都会在这里列出来
 
6）、key : 两个以上的索引称为复合索引
查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
 
7）、key_len
用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
 
8）、ref
如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
 
9）、rows
这里是执行计划中估算的扫描行数，不是精确值
 
10）、extra
这个列可以显示的信息非常多，有几十种，常用的有

A：distinct：在select部分使用了distinc关键字

B：no tables used：不带from字句的查询或者From dual查询

C：使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中

E：using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

G：using sort_union，using_union，using intersect，using sort_intersection：
using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

H：using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

I：using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

J：firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个

K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
 
除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息
 
11）、filtered
使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

5.1 具体慢查询优化的案例

• 1. 函数Max()的优化

例如: 查询订单的最后支付时间
    select max(payment_date) from payment;  优化前

查看执行计划 : explain select max(payment_date) from payment;

可以看到显示的执行计划，并不是很高效，可以拖慢服务器的效率，如何优化了？

创建索引 : create index inx_paydate on payment(payment_date);

索引是顺序操作的，不需要扫描表，执行效率就会比较恒定

• 2. 函数count(*) 的优化

需求：在一条SQL中同时查询2006年和2007年电影的数量
    错误写法 : 
        select count(*) from film where release_year='2006' or release_year='2007';
    这两种查询只能查询出2006年和2007年的总数量, 但是2006年和2007年分别是多少无法判断出来
    
    正确的编写方式:
        SELECT release_year, COUNT(*) FROM film GROUP BY release_year HAVING release_year='2006' OR release_year='2007';

区别：count（*）和count（id）

create table t(id int);

insert into t values(1),(2),(null);

count（*）：select count(*)from t;

​ 4

count（id）：select count(id)from t;

说明：

​ count（id）是不包含null的值

​ count（*）是包含null的值

​ 在创建表的时候, 尽可能的使用not null 的约束, 保证每一个字段默认值不是null值, 这样在执行count操作的时候, 就可以直接统计某一个字段的内容, 而不是统计所有的字段个数

• 3. 子查询的优化

​ 子查询是我们在开发过程中经常使用的一种方式，在通常情况下，需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系，要注意重复数据。

​ 在编写代码的时候, 如果使用子查询可能会比多表连接查询更加的简单, 所以在开发阶段, 可以使用子查询, 只要能够满足需要就可以,但是在正式上线的商业化使用的时候, 尽可能将子查询更改jon查询会更好的一点,join查询的效率要比子查询效率更高

​ 查看我们所创建的t表

create table t1(tid int);

​ 接下来我们创建一个t1表

//create table t1(tid int);

insert into t1 values(1);

​ 我们要进行一个子查询，需求：查询t表中id在t1表中tid的所有数据；

select * from t where t.id in (select t1.tid from t1);

​ 接下来我们用join的操作来进行操作

select id from t join t1 on t.id =t1.tid;

​ 通过上面结果来看，查询的结果是一致的，我们就将子查询的方式优化为join操作。

​ 接下来，我们在t1表中再插入一条数据

insert into t1 values (1);
select * from t1;

​ 在这种情况下，如果我们使用子查询方式进行查询，返回的结果就是如下图所示：

​ 如果使用join方式进行查找，如下图所示：

​ 在这种情况下出现了一对多的关系，会出现数据的重复，我们为了方式数据重复，不得不使用distinct关键词进行去重操作

​ 注意：这个一对多的关系是我们开发过程中遇到的一个坑，出现数据重复，需要大家注意一下。

总结: 需要将子查询更为 join 查询, 效率比较高的

• 4. group by的优化

需求：每个演员所参演影片的数量-（影片表和演员表）
    explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id;

优化后的SQL：
explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join ( select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using(actor_id);

说明：从上面的执行计划来看，这种优化后的方式没有使用临时文件和文件排序的方式了，取而代之的是使用了索引。查询效率老高了。

这个时候我们表中的数据比较大，会大量的占用IO操作，优化了sql执行的效率，节省了服务器的资源，因此我们就需要优化。

注意：

1) mysql 中using关键词的作用：也就是说要使用using,那么表a和表b必须要有相同的列。
    using(id)  类似于: on a.id = b.id
    1.1) 在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是using。

    1.2) 如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。

• 5. Limit查询的优化

Limit常用于分页处理，时长会伴随order by从句使用，因此大多时候使用Filesorts这样会造成大量的IO问题。

需求：查询影片id和描述信息，并根据主题进行排序，取出从序号50条开始的5条数据。
    select film_id,description from sakila.film order by title limit 50,5;

在查看一下它的执行计划：

对于这种操作，我们该用什么样的优化方式了？

优化步骤1：

​ 使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。

优化1: select film_id,description from sakila.film order by film_id limit 50,5;

查看一下执行计划

​ 那如果我们获取从500行开始的5条记录，执行计划又是什么样的了？

explain select film_id,description from sakila.film order by film_id limit 500,5 \G

随着我们翻页越往后，IO操作会越来越大的，如果一个表有几千万行数据，翻页越后面，会越来越慢，因此我们要进一步的来优化。

优化步骤2、记录上次返回的主键， 在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）

上次limit是50,5的操作，因此我们在这次优化过程需要使用上次的索引记录值

优化2: select film_id,description from sakila.film  where film_id >55 and film_id<=60 order by film_id limit 0,5;

结论：扫描行数不变，执行计划是很固定，效率也是很固定的

注意事项：

​ 主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足5行的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。

总结:

​ 尽可能避免 filesorts(文件排序) 和 row的扫描(越少越好)

​ 因为这两种操作都会增加io操作

6. 索引优化

6.1 什么是索引

​ 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

​ 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的！

类似于之前学习过的倒排索引的技术

​ 提前建立好索引, 当查询的时候, 先查询索引, 找到这个索引对应表中的位置, 然后直接从这个位置将数据提取即可

6.2 如何创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

​ 1、ALTER TABLE : 用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)  // 普通索引
ALTER TABLE table_name ADD UNIQUE (column_list)   // 添加唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list)  // 添加主键索引

​ 说明：其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

​ 2、CREATE INDEX: 可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

​ 说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

​ 3、索引类型

​ 在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

​ PRIMARY KEY索引和UNIQUE索引非常类似。

​ 事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

​ 下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

​ 4、删除索引

​ 可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name ;  // 删除普通索引 和 唯一索引
ALTER TABLE table_name DROP INDEX index_name ;  // 删除普通索引 和 唯一索引
ALTER TABLE table_name DROP PRIMARY KEY ; // 删除主键

​ 其中，前两条语句是等价的，删除掉table_name中的索引index_name。

​ 第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

​ 如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

5、查看索引

mysql> show index from tblname; 
mysql> show keys from tblname;

6、什么情况下，使用索引了？

1、表的主关键字 : 主键
2、自动建立唯一索引 : unique  和 外键
3、表的字段唯一约束 : unique
4、直接条件查询的字段（在SQL中用于条件约束的字段） : 在where后面频繁使用字段
5、查询中与其它表关联的字段 : 外键
6、查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度） : 排序的字段
7、查询中统计或分组统计的字段 
    如果这个字段只是进行分组, 并没有进行统计计算, 那么是不需要添加索引, 因为分组后的类型比较少

-------------------------------------
8、表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）
9、经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）
10、数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）
11、经常和主字段一块查询但主字段索引值比较多的表字段 : 如果要加, 也是添加复合索引, 将多个合并在一块
12、对千万级MySQL数据库建立索引的事项及提高性能的手段
    如果数据库中数据是在是太多了, 那么添加索引的操作, 本质上不会将效率提高太多
    
    一个表: 存储 1千万  磁盘空间 1G    建立索引, 需要一个G索引空间
    如果数据太多了, 单独通过SQL来解决,可能效率非常的有限, 此时需要对整个的表结构进行优化

6.3 如何选择合适的列建立索引

• 1、在where从句，group by从句，order by从句，on从句中的列添加索引

• 2、索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO也会越大）

• 3、离散度大的列放到联合索引的前面

  • 离散度 : 指的就是这个字段上的数据 范围是比较大的

  例子：

  select * from payment where staff_id =2 and customer_id =584;

注意:

​ 是index （staff_id，customer_id）好，还是index（customer_id，staff_id）好

那我们怎么进行验证离散度好了？

A、我们先查看一下表结构

desc payment;

B、分别查看这两个字段中不同的id的数量，数量越多，则表明离散程度越大：因此可以通过下图看出：customer_id 离散程度大。

​ 结论：由于customer_id 离散程度大，使用index（customer_id，staff_id）好

C、mysql联合索引

​ ①命名规则 ：表名_字段名

1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则

​ ②什么是联合索引(复合索引)

1、两个或更多个列上的索引被称作联合索引，又被称为是复合索引。

2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
    
    为什么要把离散度大的字段放置在最前面, 这样保证在使用索引的时候, 匹配度会高
    
    所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

​

6.4 索引优化SQL的方法

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

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

​ SQL在执行的时候, 会自动的分析需要什么那几个索引, 但是在实际使用的时候, 只会选择其中的一个索引, 这样导致, 如果索引个数有很多 , 就会导致分析使用那个索引的时间会变得更长

2、如何找到重复和冗余的索引

​ 重复索引：重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中的 primary key和ID列上的索引就是重复索引

create table test(
    id int not null primary key, # 主键索引
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id)    # 建立唯一索引
)engine=innodb;

​ 冗余索引：冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key（name，id）就是一个冗余索引。

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    key(name,id)  # 联合 索引 
)engine=innodb;

​ 说明：对于innodb来说，每一个索引后面，实际上都会包含主键，这时候我们建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。

3、如何查找重复索引

​ 工具：使用pt-duplicate-key-checker工具检查重复及冗余索引

pt-duplicate-key-checker -uroot -padmin -h 127.0.0.1

4、索引维护的方法

​ 由于业务变更，某些索引是后续不需要使用的，就要进行删除。

​ 在mysql中，目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析；

pt-index-usage -uroot -padmin /var/lib/mysql/mysql-host-slow.log

​ 附：https://www.percona.com/downloads/

5、注意事项

​ 设计好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)三个索引，这被称为最佳左前缀特性。

    因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
    
    复合索引创建的时候:
        1) 先要满足离散度排序
        2) 将常用作查询的字段放置在最前面

3，索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引 : 长度要短一点

对字符串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

    如果这个索引已经在where条件后面已经使用了, 那么如果在排序的时候, 还要使用这个字段, 那么索引不会在这里生效了, 如果想要生效, 需要使用复合索引, 
    建议: 如果表的默认排序方式是可以满足需求, 那么不要认为添加排序操作

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
    like查询是一个非常消耗资源的查询方式, like方式的查询一般扫描的行数都是比较多

7，不要在列上进行运算 : 如果需要进行计算, 可以在业务层中进行分别的计算

select * from users where YEAR(adddate)

8，不使用NOT IN操作

NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

7. 数据库表结构优化

7.1 数据库设计规范

• 1. 数据库命名规范

  • 1.1) 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_’组成

  • 1.2) 命名简洁明确(长度不能超过30个字符);

    例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;

    ​ 除非是备份数据库可以加0-9的自然数：user_db_20151210;

• 2. 数据库表名命名规范
  • 2.1) 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_‘组成;
  • 2.2) 命名简洁明确,多个单词用下划线’_’分隔;

  例如：user_login, user_profile, user_detail, user_role, user_role_relation,user_role_right, user_role_right_relation

  注：表前缀’user_’可以有效的把相同关系的表显示在一起;

• 3. 数据库表字段名命名规范
  • 3.1) 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_‘组成;
  • 3.2) 命名简洁明确,多个单词用下划线’_’分隔;

  例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;

  • 3.3) 每个表中必须有自增主键,add_time(默认系统时间): 默认的排序的方式
  • 3.4) 表与表之间的相关联字段名称要求尽可能的相同;
    • 在进行多表查询的时候, 会有关联条件:
• 4. 数据库表字段类型规范
  • 4.1) 用尽量少的存储空间来存数一个字段的数据 : 能小不大的原则

  例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);

  • 4.2) IP地址最好使用int类型:

  • 4.3) 固定长度的类型最好使用char,例如：邮编;

  • 4.4) 能使用tinyint就不要使用smallint,int;

  • 4.5) 最好给每个字段一个默认值,最好不能为null;

• 5. 数据库表索引规范

  • 5.1) 命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;

  • 5.2) 为每个表创建一个主键索引;

  • 5.3) 为每个表创建合理的索引;

  • 5.4) 建立复合索引请慎重;

• 6. 简单熟悉数据库范式
  • 第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);

  例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;

  • 第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;

  备注：必须先满足第一范式;

  • 第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;

  备注1：必须先满足第二范式;

  备注2：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;

  例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;

7.2 数据库设计原则

• 1. 核心原则
  • 1.1) 不在数据库做运算;
  • 1.2) cpu计算务必移至业务层;
  • 1.3) 控制列数量(字段少而精,字段数建议在20以内)
  • 1.4) 平衡范式与冗余(效率优先；往往牺牲范式)
    • 业务查询数据的时候, 能够用单表解决, 就不要不用使用多表查询
  • 1.5) 拒绝3B(拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch)
• 2. 字段类原则
  • 2.1) 用好数值类型(用合适的字段类型节约空间)
  • 2.2) 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能)
  • 2.3) 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效)
  • 2.4) 少用text类型(尽量使用varchar代替text字段);
• 3. 索引类原则
  • 3.1) 合理使用索引(改善查询,减慢更新,索引一定不是越多越好)
  • 3.2) 字符字段必须建前缀索引;
    • 建立前缀索引: 控制长度即可, 默认如果不写长度,采用字段的长度
  • 3.3) 不在索引列做运算
  • 3.4) innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了)
  • 3.5) 不用外键(由程序保证约束)
    • 不要使用外键约束, 通过程序保证
• 4. sql类原则

  • sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库)

  • 简单的事务

  • 避免使用trig/func(触发器、函数不用客户端程序取而代之)

  • 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性)

  • OR改写为IN(or的效率是n级别);

  • OR改写为UNION(mysql的索引合并很弱智);

    select id from t where phone = ’159′ or name = ‘john’;
    =>
    select id from t where phone=’159′
    union
    select id from t where name=’jonh’
            避免负向%;
            慎用count(*);
            limit高效分页(limit越大，效率越低);
            使用union all替代union(union有去重开销);
            少用连接join;
            使用group by;
            请使用同类型比较;
            打散批量更新;

7.3 数据库结构的优化

7.3.1 选择合适的数据类型 :

数据类型的选择，重点在于“合适”二字，如何确定选择的数据类型是否合适了？

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

---

案例:

• 案例1 : int类型存储时间-时间转换
  • 使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。

创建表: 
create table test(
    id int auto_increment not null,
    timestr int ,
    primary key(id)
);
导入数据：
    insert into test (timestr) values (unix_timestamp('2018-05-29 16:00:00'));
    
unix_timestamp : 将时间转换为int类型
    将一个时间更为时间的毫秒值

​ 查询数据：如下图所示：

​ 时间进行转换：select FROM_UNIXTIME(timestr) from test;

结论：

​ 1、unix_timestamp()函数是将日期格式的数据转换为int类型

​ 2、FROM_UNIXTIME(timestr)函数是将int类型转换为时间格式

案例二：ip地址的存储

​ 172.125.125.125

​ 在我们的外部应用中，都要记录ip地址，大部分场合都是varchar（15）进行存储，就需要15个字节进行存储，但是bigint只需要8个字节进行存储，当数据量很大的时候（千万级别的数据），相差7个字节，但是不能小看这7个字节，给大家算一下。

​ 一个字段就多这么多，那如果我们这样的字段需要上万个字段了？是需要很多的存储空间的。

​ 使用bigint（8）来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换。

创建表：
create table sessions(
    id int auto_increment not null,
    ipaddress bigint,
    primary key (id)
);
导入数据：
    insert into sessions (ipaddress)values (inet_aton('192.168.0.1'));
转换：
    select inet_ntoa(ipaddress) from sessions;  

​ 检索:

特殊: 一个是ip地址 和 时间保存

​ 一般都可以采用存储为int类型,

总结: 在存储数据的时候, 如果这个数据可以转换为基本数据类型, 那么就不要使用复杂的类型

​ 这样可以提高效率, 但是提高sql的编写难度

7.3.2 数据库表的范式化优化

• 1. 表范式化

 范式化是指数据库设计的规范，目前说道范式化一般是指第三设计范式。也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

存在以下传递函数依赖关系：

（商品名称）->（分类）->（分类描述）

也就是说存在非关键字段 “分类描述”对关键字段“商品名称”的传递函数依赖。

不符合第三范式要求的表存在以下问题：

1、 数据冗余：（分类，分类描述）对于每一个商品都会进行记录。

2、 数据的插入异常

3、 数据的更新异常

4、 数据的删除异常（删除所有数据，分类和分类描述都会删除，没有所有的记录）

如何转换成符合第三范式的表（拆分表）：

​ 将原来的不符合第三范式的表拆分为3个表

​ 商品表、分类表、分类和商品的关系表

执行查询的时候, 会变得复杂:

​ 请查询酒水饮料下有多少个商品, 以及酒水饮料描述也要一并展示

• 2. 反范式化

​ 反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

如何查询订单信息？

select b.用户名，b.电话，b.地址，a.订单ID，sum（c.商品价格*c.商品数量）as 订单价格
    from 订单表 as a
    join 用户表 as b on a.用户ID=b.订单ID
    join 订单商品表 as c on c.订单ID=b.订单ID
    group by b.用户名，b.电话，b.地址，a.订单ID

​ 对于这样的表结构，对于sum（），group by会产生临时表，增加IO量。我们怎么优化都效率不高，那我们怎么样才能让它效率高了，就需要一些字段进行冗余。

​ 单表中增加了冗余字段，那SQL该怎么写了？

select a.用户名，a.电话，a.地址，a.订单ID,a.订单价格 
from 订单表 as a

​ 说明：表结构的设计直接涉及到SQL的查询效率及优化。

7.3.3 数据库表的垂直拆分

• 1. 垂直拆分定义 : 所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。
• 2. 垂直拆分原则

通常垂直拆分可以按以下原则进行：  根据具体的业务操作
    1、把不常用的字段单独存放到一个表中。
    2、把大字段独立存放到一个表中。: 比如 text字段
    3、把经常一起使用的字段放到一起。
例子：以film表为例

在该表中，title和description这两个字段占空间比较大，况且在使用频率也比较低，因此可以将其提取出来，将上面的一个表垂直拆分为两个表（film和film_ext）：如下所示：

7.3.4 数据库表的水平拆分

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

  以下面的peyment表为例来说明

desc payment;

• 2. 水平不拆分原因
  • 如果单表的数据量达到上亿条，那么这时候我们尽管加了完美的索引，查询效率低，写入的效率也相应的降低。
• 3. 如何将数据平均分为N份
  • 拆表的过程是比较耗时的工程, 一般要选择服务器不忙的时候, 进行处理

  通常水平拆分的方法为：  hash取模计算
  1. 对customer_id进行hash运算，如果要拆分为5个表则使用mod（customer_id，5）取出0-4个值。
  2. 针对不同的hashid把数据存储到不同的表中

• 4. 水平拆分面临的挑战

  1、跨分区表进行数据查询
      前端业务统计：查询 124574进行取模运算: 计算出这个id应该在那个表中存储的
          业务上给不同的用户返回不同的业务信息，对分区表没有大的挑战。
  2、统计及后台报表操作
      但是对后台进行报表统计时，数据量比较大，后台统计时效性比较低，后台就用汇总表，将前后台的表拆分开。
      

8. 数据库系统配置优化

8.1 定义

​ 数据库是基于操作系统的，目前大多数MySQL都是安装在linux系统之上，所以对于操作系统的一些参数配置也会影响到MySQL的性能，下面就列出一些常用的系统配置。优化包括操作系统的优化及MySQL的优化

8.2 优化配置参数-操作系统

• 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连接数在一个适当的状态。

• 2. 打开文件数的限制 :

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

8.3 优化配置参数- MySQL配置文件优化

• 1. MySQL配置文件修改

Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/my.cnf 或者是 /etc/mysql/my.cnf在Windows系统配置文件可以是位于C://windows//my.ini文件，MySQL查找配置文件的顺序可以通过以下方法获得。

/usr/sbin/mysqld --verbose --help | grep -A 1 'default options'

执行后的结果如下图所示：

注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的。

• 2. MySQL配置文件-常用参数说明
  • 2.1) 连接请求的变量

  max_connections : 
      MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
  数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。
      show variables like 'max_connections'; 最大连接数
      show status like 'max_used_connections';响应的连接数
      
  如下：
      show variables like 'max_connections';

show variables like 'max_used_connections';

说明：理想值设置为多大才合适了？

max_used_connections / max_connections * 100% （理想值≈ 85%）

​ 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

back_log :
    MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
    
    back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
    
    当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。
    
默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。



interactive_timeout :
    一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。
    默认数值是28800，可调优为7200。

• 2. 缓冲区变量

2.1) key_buffer_size
    
    key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。
    
    key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

举例如下：
    show variables like 'key_buffer_size';

​ key_buffer_size为512MB，我们再看一下key_buffer_size的使用情况：

show global status like 'key_read%';

​ 一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

​ key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，设置在1/1000左右较好

​ 默认配置数值是8388608(8M)，主机有4GB内存，可以调优值268435456(256MB)。

2.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是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。
    
举例如下：
    show global status like 'qcache%';

查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
    如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
    
查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
    查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
    
查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
    示例服务器查询缓存碎片率＝20.46％，查询缓存利用率＝62.26％，查询缓存命中率＝1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
    

2.3) record_buffer_size
    每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

    默认数值是131072(128K)，可改为16773120 (16M)
    
2.4) read_rnd_buffer_size
    随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。一般可设置为16M

2.5) sort_buffer_size
    每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
默认数值是2097144(2M)，可改为16777208 (16M)。

2.6) join_buffer_size :联合查询操作所能使用的缓冲区大小。
    record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100

2.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。

2.8) max_heap_table_size
    用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
    这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
    
2.9) tmp_table_size
    通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
    show global status like 'created_tmp%';

    每次创建临时表，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堵塞
    
2.10) thread_cache_size
    可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
    通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。默认值为110，可调优为80。
    
2.11) thread_concurrency
    推荐设置为服务器 CPU核数的2倍，例如双核的CPU, 那么thread_concurrency的应该为4；2个双核的cpu, thread_concurrency的值应为8。默认为8

2.12) wait_timeout
    指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。

• 3. 配置InnoDB的几个变量

3.1) innodb_buffer_pool_size
    对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
    根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。
    show status like 'innodb%';

3.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.3) innodb_log_buffer_size
    log缓存大小，一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。可设置为4M或8M。

3.4) innodb_additional_mem_pool_size
    该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
    根据MySQL手册，对于2G内存的机器，推荐值是20M，可适当增加。
    innodb_thread_concurrency=8
    推荐设置为 2*(NumCPUs+NumDisks)，默认一般为8
    
3.5) skip-name-resolve
    # 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，
    # 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求
#skip-networking
    
back_log = 600
    # MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，
    # 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
    # 如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到
max_connections时，新来的请求将会被存在堆栈中，
    # 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
    # 另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。
    # 你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。

max_connections = 1000
    # MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

max_connect_errors = 6000
    # 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

open_files_limit = 65535
    # MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个，
    # 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。

table_open_cache = 128
    # MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64
    # 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；
    # 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上

max_allowed_packet = 4M
    # 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
    # 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。

binlog_cache_size = 1M
    # 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K

max_heap_table_size = 8M
    # 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变

tmp_table_size = 16M
    # MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。
    # 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
    # 如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果

read_buffer_size = 2M
    # MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
    # 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 8M
    # MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，
    # MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

sort_buffer_size = 8M
    # MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。
    # 如果不能，可以尝试增加sort_buffer_size变量的大小

join_buffer_size = 8M
    # 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

thread_cache_size = 8
    # 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，
    # 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，
    # 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)
    # 根据物理内存设置规则如下：
    # 1G —> 8
    # 2G —> 16
    # 3G —> 32
    # 大于3G —> 64

query_cache_size = 8M
    #MySQL的查询缓冲大小（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，
    # 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。
    # 通过检查状态值’Qcache_%’，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，
    # 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，
    # 这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲

query_cache_limit = 2M
    #指定单个查询能够使用的缓冲区大小，默认1M

key_buffer_size = 4M
    #指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，
    # 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，
    # 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，
    # 至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意：该参数值设置的过大反而会是服务器整体效率降低

ft_min_word_len = 4
    # 分词词汇最小长度，默认4

transaction_isolation = REPEATABLE-READ
    # MySQL支持4种事务隔离级别，他们分别是：
    # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
    # 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #超过30天的binlog删除
log_error = /data/mysql/mysql-error.log #错误日志路径
slow_query_log = 1
long_query_time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 #不区分大小写
skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启
default-storage-engine = InnoDB #默认存储引擎

innodb_file_per_table = 1
    # InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
    # 独立表空间优点：
    # 1．每个表都有自已独立的表空间。
    # 2．每个表的数据和索引都会存在自已的表空间中。
    # 3．可以实现单表在不同的数据库中移动。
    # 4．空间可以回收（除drop table操作处，表空不能自已回收）
    # 缺点：
        # 单表增加过大，如超过100G
    # 结论：
        # 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files

innodb_open_files = 500
    # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300

innodb_buffer_pool_size = 64M
    # InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
    # 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
    # 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
    # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
    # 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
    # 所以不要设置的太高.

innodb_write_io_threads = 4
innodb_read_io_threads = 4

    # innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
    # 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64

innodb_thread_concurrency = 0
    # 默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_purge_threads = 1
    # InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。
    # 从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单
    # 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

innodb_flush_log_at_trx_commit = 2
    # 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。
    # 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1
    # 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。
    # 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。
    # 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘
    # 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。
    # 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
# 总结
# 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

innodb_log_buffer_size = 2M
    # 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间

innodb_log_file_size = 32M
    # 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
    # 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

innodb_max_dirty_pages_pct = 90
    # innodb主线程刷新缓存池中的数据，使脏数据比例小于90%

innodb_lock_wait_timeout = 120
    # InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

bulk_insert_buffer_size = 8M
    # 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。

myisam_sort_buffer_size = 8M
    # MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区

myisam_max_sort_file_size = 10G
    # 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出

myisam_repair_threads = 1
    # 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)

interactive_timeout = 28800
    # 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）

wait_timeout = 28800
    # 服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，
    # 取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时）
    # MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，
    # 应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，
    # 最终肯定会达到MySQL Server的连接上限数，这会报’too many connections’的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。
    # 在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，
    # 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。

[mysqldump]
quick
max_allowed_packet = 16M #服务器发送和接受的最大包长度
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

附录：

1、查看innodb的相关参数信息 : show variables like ‘innodb%’;

2、查看innodb的相关参数状态 : how status like ‘innodb%’;

9. MySQL的执行顺序

​ MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

select [distinct] 字段列表 from 表 inner join 表2 on 关联条件 where 条件 group by  分组的字段  having 聚合函数条件 order by 排序字段 [desc | asc] limit start,rows

下面我们来具体分析一下查询处理的每一个阶段:

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

总结: 每执行一个关键词, 就会产生一张虚拟表,而这个虚拟表就表示执行完这个关键词中内容后, 得到的结果数据, 如果后面没有关键词了, 那么直接将会这个虚拟表展示给用户即可

10. MySQL执行引擎

10.1 MyISAM存储引擎

​ 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表

​ 支持3种不同的存储格式，分别是：静态表；动态表；压缩表

静态表：

​ 表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

动态表：

​ 记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

压缩表：

​ 因为每个记录是被单独压缩的，所以只有非常小的访问开支

10.2 InnoDB存储引擎

​ 该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎的特点：支持自动增长列，支持外键约束

mysql默认的存储引擎: InnoDB

10.3 MEMORY存储引擎

​ MEMORY方案基于内存的存储数据: 将数据存储在内存中

​

​ Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
​ MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

Hash索引优点：

​ Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

Hash索引缺点：

​ 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

​ Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

10.4 MERGE存储引擎

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

面试:

​ 如何对数据库进行优化: 学历检测+笔试+面试

​ 笔试: 比较轻松

​ 1) SQL相关的优化:

​ 建立合适的索引或者建立合适复合索引提高查询的效率

​ 将子查询更改为 join查询

​ 将not in 改为 not exit

​ 2) 表的结构优化:

​ 1) 选择合适的表的数据结构: 能小不大原则

​ 2) 使用拆分表的策略: 垂直拆分 和 水平拆分

​ 3) 修改操作系统和 mysql相关的配置:

​ 4) 提高服务器的硬件配置 : CPU 内存 磁盘

​