MySQL高级部分

最新推荐文章于 2024-08-11 13:56:31 发布

glenzhang(ty)

最新推荐文章于 2024-08-11 13:56:31 发布

阅读量345

点赞数 1

分类专栏：笔记文章标签： java 数据库

本文链接：https://blog.csdn.net/qdzjo/article/details/119112426

版权

本文详细介绍了MySQL的高级特性，包括存储引擎如InnoDB与MyISAM的区别，索引的创建与类型，事务的隔离级别，锁机制的行锁与表锁，MVCC的基本原理，以及视图、存储过程、函数和触发器的使用。此外，还探讨了SQL优化和执行计划分析，帮助读者深入理解MySQL的高级操作。

摘要由CSDN通过智能技术生成

文章目录

MySql高级

MySql高级

引擎

1.概述

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常见的存储引擎：1.MyIsam 2.InnoDB 3.Memory 4.Blackhole 5.CSV 6.Performance_Schema 7.Archive 8.Federated 9.Mrg_Myisam

功能	MyIsam	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

2.查看支持的引擎

SHOW ENGINES;

查看表引擎

SHOW TABLE STATUS LIKE '表名'

修改引擎

方式1：将mysql.ini中default-storage-engine=InnoDB,重启服务。
方式2：建表时指定CREATE TABLE 表名(…)ENGINE=MYISAM;
方式3：建表后修改ALTER TABLE 表名 ENGINE = INNODB;

3.InnoDB与MyISAM引擎区别

InnoDB

InnoDB：默认的存储引擎。

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

InnoDB引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束。

设计目标是处理大容量数据库系统。

MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引（全文检索），而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。

由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用InnoDB引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

适用场景：经常更新的表，适合处理多重并发的更新请求；支持事务；外键约束。只有它支持外键；支持自动增加列属性auto_incremet。

MyISAM

MyISAM没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT（插入）或UPDATE（更新）数据时即写操作需要锁定整个表，效率低。

适用场景：不支持事务的设计，不支持外键的表设计。

MyISAM极度强调快速读取操作。

MyISAM中存储了表的行数，于是SELECT COUNT（*） FROM TABLE时只需要直接读取已经保存好的只而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

总结：

MyISAM	InnoDB
非事务安全型的	事务安全型的
锁的粒度是表级的	支持行级锁定
支持全文类型索引	不支持全文类型索引
简单，效率高，适合小型应用	复杂，效率低
保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少麻烦
不安全	安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表
管理非事务表。提供高速存储和检索，以及全文搜索能力。适合需要执行大量select查询	用于事务处理应用程序，具有众多特性，包括ACID事务支持。适合需要执行大量的INSERT或UPDATE操作

索引

索引：在MySQL中，由数据表中一列或多列组合而成。创建索引的目的是为了优化数据库的查询速度。

1.为什么使用索引

在查询大量数据时，逐条查询效率太低，而索引类似于书的目录，在查找内容时借助索引，执行查询时不必扫描整个表就能快速地找到所需要的数据。

2.优点

大大加快数据的检索速度，降低数据库IO成本。
通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗。

3.缺点

占用磁盘空间大。
提高查询速度，但是降低了更新表的速度。例如对表进行INSERT,UPDATE 和 DELETE，因为更新表时，MySQL不仅要保存数据，还要保存索引文件，每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

4索引分类

主键索引：设定为主键后数据库会自动建立索引。

ALTER TABLE 表名 add PRIMARY KEY 表名(列名); 
删除建主键索引： 
ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

创建单值索引
CREATE INDEX 索引名 ON 表名(列名); 
删除索引： 
DROP INDEX 索引名 ON 表名;

唯一索引：索引列的值必须唯一，允许为null

CREATE UNIQUE INDEX 索引名 ON 表名(列名); 
删除索引 
DROP INDEX 索引名 ON 表名;

复合索引：即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对相同的多个列键索引），当表的行数远大于索引列的数目时可以使用复合索引
```
创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...); 
删除索引： 
DROP INDEX 索引名 ON 表名;
```