Mysql数据库优化全过程

1.存储引擎

什么是存储引擎

存储引擎:可以看作是数据表存储数据的一种格式，不同的格式具有的特性也各不相同。

举例说明:只有innodb存储引擎支持事务、外键、行级锁等特性，而myisam则支持压缩机制等特性。

存储引擎的特点:本身是mysql数据库服务器的底层组件之一，最大的特点是采用“可插拔”的存储引擎架构。

“可插拔”的理解:指的是对正在运行的mysql服务器依然可根据实际需求使用特定语句加载（插入，install plugin语句）或卸载（拔出，uninstall plugin语句）所需的存储引擎文件。

存储引擎的选择

查看当前支持的存储引擎

show engines;

执行以上sql语句，运行的结果中含有6个字段。

• engine (存储引擎)、support (是否支持)
• comment (注释说明)、transactions (是否支持事务)
• xa (是否支持分布式事务)和savepoints ( 是否支持事务的保存点设置)

1. innodb存储引擎

innodb存储引擎:在mysql 5.7版本中被指定为默认的存储引擎。

特性:是mysql中第一个提供外键约束的表引擎，尤其对事务处理的能力，是mysql其他存储引擎无法与之比拟的。

优势:用于完成事务、回滚、崩溃修复和多版本并发控制的事务安全处理。

缺点:读写效率一般。

存储模式

• 存储格式:默认innodb数据表都共用一个表空间文件ibdata1，每张数据表都会在对应的数据库下创建一个与表同名的结构文件(数据库/表名.frm)

ibdata1 的位置:通常位于data目录下，与数据库文件处于同级目录。

ibdata1 的作用:集中存储数据和索引。

设置数据表的独立表空间文件:全局变量innodb_ file_ _per_ table。

#查看默认是否共用同一个表空间文件.
show variables like 'innodb_file_per_table';
#开启每个表独立的表空间文件
set global innodb_file_per_table=on;

例：

2. myisam存储引擎

myisam存储引擎:在mysql5.5以前的版本中是mysql的默认存储引擎。

• 特性:是基于isam存储引擎发展起来的，不仅解决了isam的很多不足，还增加了很多有用的扩展。例如，数据的全文索引、压缩与加密、支持复制与备份的恢复等。
• 优势:与innodb相比，myisam的优点是处理速度快。
• 缺点:innodb相比，myisam的缺点是不支持事务的完整性和并发性。

myisam数据表的文件扩展名分别为frm、myd和myi，文件名与表名相同。

myisam表的数据移植非常方便，只需将数据库下表中对应的3个文件复制到另一个数据库下即可。

3. mrg_myisam存储引擎

mrg_myisam存储引擎:相同myisam存储引擎表的集合，也被称为merge。

• 特性:所有合并的表必须具有相同顺序的字段与索引的应用。
• 优势:可快速拆分大型只读表，执行搜索效率更高等。
• 缺点:索引读取速度较慢、只针对myisam存储引擎的表进行合并、实际应用较少等。

4. memory存储引擎

memory存储引擎:在是mysql中一种特殊的存储引擎。

• 特性:在memory存储引擎的表中，所有数据都保存在内存中，一旦程序出错或服务器断电都会导致数据的丢失。
• 优势:数据的处理速度快。
• 缺点:不适合持久保存数据，而且也不能存储太大的数据。

对读写速度快，数据量小、不需要持久保存的临时数据是理想的选择。

5. csv是存储引擎

csv是存储引擎:是采用文本方式存储数据的一种存储引擎，数据在文件中通过逗号分隔保存。

结构:数据表会分为后缀frm（存储表结构信息）、csv（存储表内容）和csm(存储表的状态、数据量等元数据）3个文件存储，文件名与数据表名相同。

一是不支持索引和分区，二是表中所有字段必须含有not null属性。

6. archive存储引擎

• archive存储引擎:适合保存数量庞大、长期维护但很少被访问的数据。
• 特性:数据存储时会用zlib压缩库进行压缩，在记录被请求时会实时进行解压。
• 提示: archive存储引擎仅仅支持查询和插入操作，并且因不支持数据索引，查询效率较低。

7. blackhole存储引擎

blackhole存储引擎:被称为“黑洞”存储引擎。

• 特性:写入的数据都会消失，就像被“黑洞”吞噬了一样。
• 作用:利用此特性可以将其作为转发器或过滤器。
• 举例:将主服务器中的大量数据经过过滤后搬到从服务器，可将bl ackhole的数据表作为过滤器使用，且不会保存任何数据，但是会在二进制日志中记录下所有sql语句，然后可复制并执行这些语句，将结果保存到从服务器中。

8. performance_schema存储引擎

performance_schema存储引擎: mysql .5.7中performance_ schema数据库中所有数据表的存储引擎。

• 特性:用户不能为数据表创建此类型的存储引擎。
• 作用:主要用于收集数据库服务器性能参数。

9. federated存储引擎

federated存储引擎:默认情况下在mysql中不可用。

• 启动:利用“–federated"选项。
• 特性:创建从远程mysql服务器访问数据的表，本地的federated表只保存结构信息(后缀为frm)，远程服务器同时要保存结构信息和数据文件，所有的增删改查操作都通过访问远程服务器后，才将结果返回给本地的服务器。

2.索引

1. 索引概述

• 索引:是一种特殊的数据结构，可以看做是利用mysql提供的语法将数据表中的某个或某些字段与记录的位置建立一一个对应的关系，并按照一定的顺序排序好。
• 且的:就是为了快速定位指定数据的位置。

2. 索引分类

• 普通索引:是mysql 中的基本索引类型，使用key或index定义，不需要添加任何限制条件，作用是加快对数据的访问速度。
• 唯一性索引:由unique index定义，创建唯一性索引的字段需要添加唯一性约束，用于防止用户添加重复的值。
• 主键索引:由primary key定义的一种特殊的唯一性索引， 用于根据主键自身的唯一性标识每条记录，防止添加主键索引的字段值重复或为null。

若在innodb表中数据保存的顺序与主键索引字段的顺序一致时，可将这种主键索引称为“聚簇索引”。一般聚簇索引指的都是表的主键

一张数据表中只能有一个聚簇索引。

• 全文索引:由fulltext index定义，用于根据查询字符提高数据量较大的字段查询速度。此索引在定义时字段类型必须是char、varchar或text中的一种，在mysql 5.7版本中，仅mylsam和innodb存储引擎支持全文索引。
• 空间索引:由spatial index定义在空间数据类型字段上的索引，提高系统获取空间数据的效率。仅myisam和innodb存储引擎支持空间索引，还要保证创建索引的字段不能为空。

根据创建索引的字段个数，还可以将它们分为单列索引和复合索引：

• 单列索引:指在表中单个字段上创建的索引，可以是普通索引、唯一索引、主键索引或者全文索引，只要保证该索引对应表中一个字段即可。
• 复合索引:是在表的多个字段上创建一个索引，且只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

3. 索引的操作

创建索引

• create table方式，与数据表同时创建。

#方式1: create table创建数据表时添加索引
create table数据表名(
字段名数据类型[约束条件]
...
primary key [索引类型] (字段列表)[索引选项],
{index|key} [索引名称] [索引类型] (字段列表)[索引选项],
unique [indexikey] [索引名称] [索引类型] (字段列表)[索引选项],
{fulltext|spatial} [index|key] [索引名称] (字段列表)[索引选项]
)[表选项];

• alter table方式，对已创建的数据表进行添加。

#方式2: alter table向已创建的数据表添加索引
alter table 数据表名
add primary key[索引类型] (字段列表) [索引选项]
|add {index|key} [索引名称] [索引类型] (字段列表) [索引选项]
|add unique [index|key] [索引名称] [索引类型] (字段列表) [索引选项]
|add fulltext [index|key] [索引名称] (字段列表)[索引选项]
iadd spatial [index|key] [索引名称] (字段列表)[索引选项], ...;

• create index方式，对已创建的数据表进行添加，不能添加主键索引。

#方式3: create index向已创建的数据表添加索引.
create [unique|fulltext|spatial] index 索引名称
[索引类型] on 数据表名(字段列表)[索引选项][算法选项|锁选项]

索引可设置选项

主键索引不能设置索引名称，其他索引的名称也可以省略，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名。

索引字段信息

所谓索引覆盖指的是查询的字段恰好是索引的一部分或与索引 完全一致，那么查询只需要在索引区上进行，不需要到数据区检索数据的情况。

这种查询的的特点是速度非常快，但同时也会增加索引文件的大小，只有此索引的使用率尽可能高的情况下，索引覆盖才有意义。否则，在使用时应该避免此情况的发生。

删除索引

主键索引在删除时，需要考虑该主键字段是否含有auto_increment属性，若有则需在删除主键索引前删除该属性，否则程序会报以下的错误提示信息。

• 删除含有auto increment属性的主键索引

#删除主键字段的auto_ increment属性
alter table 数据表 modify 字段名字段类型
#删除主键索引
alter table 数据表 drop primary key 或 drop index、'primary' on 数据表

当使用drop index删除主键索引时，其后的primary由于是mysql中的保留字，因此必须使用反引号(‘) 包裹。

删除命令：

#语法1
alter table 数据表 drop index 索引名
#语法2
drop index 索引名 on 数据表 [算法选项][锁选项]

例：

3.锁机制

简单的说，锁机制就是为了保证多用户并发操作时，能使被 操作的数据资源保持一致性的设计规则。

锁的分类

表级锁(如myisam、memory存储引擎）

• 表级锁:是mysql中锁的作用范围( 锁的粒度)最大的一种锁。
• 锁定范围:是用户操作资源所在的整个数据表。
• 优势:有效的避免了死锁的发生，且具有加锁速度快、消耗资源小的特点。
• 缺陷:因其锁定的粒度大，在并发操作时发生锁冲突的概率也大。

行级锁(如innodb存储引擎特殊)

• 行级锁:是mysql 中锁的作用范围最小的一种锁。
• 锁定范围:仅锁定用户操作所涉及的记录资源。
• 优势:能减少锁定资源的竞争，较高并发处理能力，提升系统的整体性能。
• 缺陷:因其锁定的粒度过小，每次加锁和解锁所消耗的资源也会更多，发生死锁的可能性更高。

根据锁在mysql中的状态也可将其分为“隐式”与“显式”：

• “隐式”锁指的是mysql服务器本身对数据资源的争用进行管理，它完全由服务器自动执行。
• “显式”锁指的是用户根据实际需求，对操作的数据显式的添加锁，同样在使用完数据资源后也需要用户对其进行解锁。

表级锁

• 表级锁:根据操作的不同可以分为读锁和写锁。
• 读锁:表示用户读取(如select查询)数据资源时添加的锁，其他用户不可修改或增加数据资源，但是可以读取该数据资源，因此读锁也可称为共享锁。
• 写锁:表示用户对数据资源执行写(如insert、update、delete等)操作时添加的锁，除了当前添加写锁的用户外，其他用户都不能对其进行读/写操作，因此写锁也可以称为排他锁或独占锁。

myisam存储引擎表:是mysql 数据库中最典型的表级锁。

“隐式”读的表级锁:当用户对myisam存储引擎表执行select查询操作前，服务器会“自动”地为其添加-一个表级的读锁。

“隐式”写的表级锁:执行insert、 update、delete等写操作前，服务器会“自动”地为其添加一个表级的写锁。

“隐式”读的表级锁与“隐式”写的表级锁添加的优先级顺序:

• 默认服务器“自动”添加“隐式”锁时，表的更新操作优先于表的查询操作。.
• 添加写锁时，若表中没有任何锁则添加，否则将其插入到写锁等待的队列中。
• 添加读锁时，若表中没有写锁则添加，否则将其插入到读锁等待的队列中。

表级锁语法：

lock tables 数据表名 read [local]| write,...

• lock tables可以同时锁定多张数据表。
• read表示表级的读锁，添加此锁的用户可读但不能写，其他用户可以读取，执行写操作会进入等待队列。
• write表示表级的写锁，添加此锁的用户可执行读/写操作，在释放锁之前，不允许其他用户访问与操作。

表级锁的问题:锁定的粒度大，多用户访问会造成锁竞争，降低并发处理能力。

从数据库优化的角度来考虑:尽量减少表级锁定时间，提高多用户的并发能力。

如何释放“显式”表级锁: unlock tables语句。

行级锁

innodb存储引擎的锁机制相对于myisam存储引擎的锁复杂一些。

innodb存储引擎既有表级锁又有行级锁。

innodb表级锁的应用与myisam表级锁的相同。

“隐式”行级排他锁:当用户对innodb存储引擎表执行insert、update、delete等写操作前，服务器会“自动”地为通过索引条件检索的记录添加行级排他锁。

“隐式”行级排他锁要如何解锁:直到操作语句执行完毕，服务器再“自动”地为其解锁。

“隐式”行级排他锁的生命周期:语句的执行时间可以看作是“隐式”行级锁的生命周期，且该生命周期的持续时间–般都比较短暂。延长“隐式”行级排他锁的生命周期:通常情况下，若要增加行级锁的生命周期，最常使用的方式是事务处理，让其在事务提交或回滚后再释放行级锁，使行级锁的生命周期与事务的相同。

对于innodb表来说，若要保证当前事务中查询出的数据不会被其他事务更新或删除，利用普通的select语句是无法办到的，此时需要利用mysql提供的“锁定读取”的方式为查询操作显式的添加行级锁。

select 语句 for update|lock in share mode
# for update:表示在查询时添加行级排他锁
# lock in share mode:表示在查询时添加行级共享锁

用户在向innodb表显式添加行级锁时，innodb存储引擎首先会“自动”地向此表添加一个意向锁，然后再添加行级锁。

• 意向锁是一个隐式的表级锁，多个意向锁之间不会产生冲突且互相兼容。
• 意向锁是由mysql服务器根据行级锁是共享锁还是排他锁，自动添加意向共享锁或意向排他锁，不能人为干预。
• 意向锁的作用:就是标识表中的某些记录正在被锁定或其他用户将要锁定表中的某些记录。相对行级锁，意向锁的锁定粒度更大，用于在行级锁中添加表级锁时判断它们之间是否能够互相兼容。
• 好处:就是大大节约了存储引擎对锁处理的性能，更加方便的解决了行级锁与表级锁之间的冲突。

表级的共享/排他锁与意共享排他锁之间的兼容性关系

默认当innodb处于repeatable read (可重复读)的隔离级别时，行级锁实际上是一个next-key锁，它是由间隙锁(gap lock)和记录锁(record lock)组成。

• 记录锁(record lock)就是前面讲解的行锁。
• 间隙锁(gap lock)指的是在记录索引之间的间隙、负无穷到第1个索引记录之间或最后1个索引记录到正无穷之间添加的锁。

4.分表技术

分表技术： 将单张数据表根据不同的需求进行拆分，从而达到分散单表压力的目的，提升数据库的访问性能。

分表分类

水平分表：将一张数据表中的全部记录分别存储到多张数据表中，因此水平分表在创建时，必须保证各数据表涉及到的字段全部相同。水平分表使单张表的数据能够保持在一定的量级。

• 优势:在操作时又因其表结构完全相同，只需增加获取对应分表名称的运算，就可以提高系统的稳定性和负载能力。
• 缺点:水平分表使得数据分散存储，加大了数据维护难度。

垂直分表：将同一个业务的不同字段分别存储到多张数据表中，因此垂直分表在创建时，各数据表仅通过一个字段进行连接，其他字段都不相同。

• 垂直分表后业务逻辑更加的清晰，方便数据进行整合与扩展，还可以根据实际需求实现动静分离，为各分表选择不同的存储引擎(如查询操作多可以使用myisam等)。
• 缺点:需要管理冗余字段、查询所有数据需要进行连接。

5.分区技术

分区概述

分区技术:就是在操作数据表时可以根据给定的算法，将数据在逻辑上分到多个区域中存储。在分区中还可以设置子分区，将数据存放到更加具体的区域内。

• 分区技术可以使一张数据表中的数据存储在不同的物理磁盘中，相比单个磁盘或文件系统能够存储更多的数据，实现更高的查询吞吐量。
• 若在where子句中包含分区条件，系统只需扫描相关的一个或多个分区而不用全表扫描，从而提高查询效率。
• 同一个分区表的所有分区必须使用相同存储引擎。当建表时未指定存储引擎，在创建分区时必须设置存储引擎。

分区操作

create table 数据表名称
[(字段与索引列表)][表选项]
partition by 分区算法(分区字段)[partitions分区数量]
[subpartition by子分区算法(子分区字段)[subpartitions子分区数量]]
[(
partition分区名[values值][其他选项][(subpartition 子分区名 [其他选项])],
...
)];

分区是在表选项后添加partition by实现。

一个表最多仅可以创建1024个分区。

分区算法有4种，分别为list、range、hash和key。

创建list分区

分区创建完成后，会在数据文件data/mydb目录下看到对应的分区数据文件

p_list#p#p1.idb
p_list#p#p2.idb

创建hash分区

使用hash算法为p_hash表创建了3个分区，分区文件的序号默认从0开始，当有多个分区时依次递增加1。

例如，以上创建的分区序号依次为0、1和2。

#已创建的数据表没有创建分区，添加分区的方式
alter table数据表名称partition by分区算法...;
#已创建的数据表含有分区，添加分区的方式
#list或range分区
alter table数据表名称add partition (分区选项，..;, .
#hash或key分区
alter table数据表名称partitions 数量;

#删除hash、key分区
alter table数据表名称coalesce partition数量;
#删除range、list分区
alter table数据表名称drop partition分区名称; .

• 删除hash与key算法分区时，会将该分区内的数据重新整合到剩余的分区。
• 删除range与list算法分区时，会同时删除分区中保存的数据。
• 当数据表的分区仅剩一个时，不能通过以上的方式删除，只能利用drop table的方式删除表。

若在开发中仅要清空各分区表中的数据，不删除对应的分区文件，可以使用以下的语句实现。

alter table 数据表名称 truncate partition{分区名称|all}

6.数据碎片与维护

在mysql数据库中，delete删除一条记录时，仅删除了数据表中保存的数据，而记录占用的存储空间会被保留。

长期删除数据、添加数据的过程中，索引文件和数据文件都将产生“空洞”，形成很多不连续的碎片，造成数据表占用空间变大，但表中记录数却很少的情况发生。

修复数据表的数据及索引碎片时，会把所有的数据文件重新整理一遍。因此，若数据表的记录数比较大，也会消耗一定的资源，所以不能频繁的对数据碎片进行维护，可根据实际的情况按周、月或季度等进行操作。

7.总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。