JavaSE

MySql进阶-优化篇

2021-05-17  本文已影响0人  AIGame孑小白

MySQL架构

客户端层：提交sql语句

cli;jdbc;navicat;sqlyog

MySqlServer层

1.连接器：用来控制用户的连接，比如：连接池

show processlist;//查看连接池

2.分析器：词法分析和语法分析，形成抽象语法树(AST)

3.调优器：
CBO：基于成本的优化
RBO：基于规则的优化

4.执行器

存储引擎层：不同的存放位置，不同的文件格式

InnoDB存储引擎：有事务，默认支持行锁（但是也支持表锁）InnoDB一般锁的是索引，如果一张表内没有索引，那么就默认表级锁
.idb->实际存储的数据
.frm->存储表结构和存储表的位置

MyISAM存储引擎：索引文件和数据文件分开存放：没有事务，默认支持表锁
.frm
.MYD->Data
.MYI->Index

日志文件

  1. binLog：归属于服务层（默认不开启）

  2. redoLog：归属于InnoDB存储引擎

  3. undoLog：归属于InnoDB存储引擎

一般要开启binLog是为了防止数据丢失，开启后可以做数据恢复(会损失一点性能)

MySql的存储引擎

ISAM：(Indexed Sequential Access Method)索引序列访问方法，是一种数据表格的管理方法，设计之时就考虑到了查询远大于更新次数，因此他的查询速度很快并且不占用大量内存和储存资源，但是他有两个缺点：

ISAM升级版(优化查询)

MyISAM：是MySql对ISAM的扩展和缺省的数据库引擎(在MySql5.5以后默认为InnoDB了)，MyISAM还提供了一种表格锁定机制，优化多个并发的读写操作，但是它在做更新操作后可能会出现内存碎片，所以经常需要使用Optimize Table 表名，来整理碎片，在整理碎片的过程中该表是被锁定的状态，外界无法查询或更新，整理完成后才能正常使用，并且MyISAM也有个缺陷就是在表损坏后也不能恢复数据。

如果使用该引擎会生成三个文件：

数据库优化

数据库在优化的时候

我们通过优化策略，会发现越往后受影响面积越广，越往前受面积越小：

索引

所以一般我们要优化数据库应该是首先考虑Sql语句优化以及索引优化，对于修改引擎是一件十分慎重的事情。

InnoDB

InnoDB：比ISAM和MyISAM的速度慢，但是包含了对事物和外来键的支持，MySql官方对InnoDB的解释：

MyISAM与InnoDB的区别

  1. InnoDB 支持事务，MyISAM 不支持。对于 InnoDB 每一条 SQL 语言都默认封装成事务； 自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin transaction 和commit 之 间，组成一个事务；
  2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
  3. InnoDB 是聚集索引(对各个表的索引进行排列组合，以便于在查找的时候效率更高)，数据文件是和索引绑在一起的，必须要有主键。(在使用该引擎的时候，即便没有设定默认主键，InnoDB也会自动根据你的表中的唯一性约束定位默认主键，如果连这个也没有，那么InnoDB会根据每个数据随机生成一个索引数)，通过主键索引 效率很高。但是辅助索引(我们自己create index的索引)需要两次查询(即使是查询两次也不会影响效率，因为索引本身可以指定到唯一数据，即使是两次查询，速度也会非常快)，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  5. Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 较高；

整理一下

MyISAM InnoDB
索引类型 非聚簇索引 聚簇索引
支持事务 否 是
支持表锁 是 是
支持行锁 否 是
支持外键 否 是
支持全文索引 是 是(5.6后支持)
适合操作类型 大量select 大量insert,delete,update

其他的引擎

操作数据库引擎

查看当前支持的所有存储引擎

mysql> show engines;
MyISAM
MEMORY
InnoDB
BerkeleyDB
BLACKHOLE
EXAMPLE
ARCHIVE
CSV
ndbcluster
FEDERATED
MRG_MYISAM
ISAM
12 rows in set (0.00 sec)

查看当前默认的存储引擎

mysql> SHOW VARIABLES LIKE '%storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

注意：不同的表可以使用不同的存储引擎

查看创建表时使用的引擎

mysql> show create table student;
CREATE TABLE `student` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1 row in set (0.00 sec)

修改表的存储引擎

mysql> ALTER TABLE student ENGINE = InnoDB;
Query OK, 110 rows affected (0.03 sec)
Records: 110  Duplicates: 0  Warnings: 0

数据库的索引

为什么要建立索引？

这是因为，创建索引可以大大提高系统的查询性能：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

在InnoDB创建表的过程中，首先确定表的主键，如果没有就找具有唯一性约束的数据做为主键，否则就自己为各个数据随机生成索引数，其目的就是为了提高查询效率，实际上索引最后会被换算成磁盘的存储位置，那么根据索引就能直接查询到数据。

索引这么高效，为什么不对每个表的每个列都创建索引？

增加索引也有许多不利：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

什么样的字段适合创建索引?

一般来说，应该在具备下述特性的列上创建索引：
1.在经常需要搜索的列上，可以加快搜索的速度
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度(在做互联网项目的时候，一般表之间有外键但是不建立外键约束，是为了分库分表)
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序,其指定的范围是连续的
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
6.在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度

什么样的字段不适合创建索引？

一般来说，不应该创建索引的这些列具有下述特点：
1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2.对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
上一篇 下一篇

猜你喜欢

热点阅读