数据存储Innodb
创建数据库后会在data目录下创建文件夹
C:\Program Files\mysql-8.0.17-winx64\data\test_db
create database test_db;
创建表后会在文件夹生成一个.ibd文件tb_emp1包含tablespace id
mysql> create table tb_emp1(
-> id int(11),
-> name varchar(25),
-> deptId int(11),
-> salary float);
插入数据后数据存储在公共空间。
insert (1,1,1,1) into tb_emp1;
引擎
InnoDB不创建目录,使用 InnoDB时, MySQL将在 MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为 ib_logfile0和 ib_logfile的5MB大小的日志文件。
使用 MyISAM引擎创建数据库,将生产3个文件。文件的名字以表的名字开始,扩展名指出文件类型:fm文件存储表定义,数据文件的扩展名为MYD( MYData),索引文件的扩展名是MYI( MYIndex)。
索引
索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行,所有 MySQL列类型都可以被索引,对相关列使用索引是提高查询操作速度的最佳途径。innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。
优点
(1)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
(2)可以大大加快数据的查询速度,这也是创建索引的最主要的原因。
(3)在实现数据的参考完整性方面,可以加速表和表之间的连接。
(4)在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。
缺点
(1)创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
(2)索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理。
空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
(3)当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低。
使用 CREATE TABLE创建表时,除了可以定义列的数据类型,还可以定义主键约束、外键约束或者唯一性约束,而不论创建那种约束,在定义约束的同时相当于在指定列上创建了一个索引。
普通索引
最基本的索引类型,没有唯一性之类的限制,其作用只是加快对数据的访问速度。
INDEX(...)
唯一索引
创建唯一索引的主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表。它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯。
UNIQUE INDEX UniqueIdx(id)
单列索引
单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引前，面两个例子中创建的索引都为单列索引。
INDEX SingelIdxname(20))
组合索引
在多个字段上增加索引。
INDEX MultipleIdex(id,name,age(100))
组合索引可起几个索引的作用,但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从“最左前缀”:利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。例如这里由id、name和age3个字段构成的索引,索引行中按 id/name/age的顺序存放,索引可以搜索下面字段组合:(id,name,age)、(id,name)或者id。如果列不构成索引最左面的前缀MySQL不能使用局部索引,如(age)或(name,age)组合则不能使用索引查询.
全文索引
FULLTEXT(全文索引)可以用于全文搜索。只有 MyISAM存储引擎支持 FULLTEXT索引,并且只为CHAR、 VARCHAR和TEXT列创建索引。索引总是对整个列进行,不支持局部(前缀)索引。
FULLTEXT INDEX FullTxtIdx(info)
空间索引
只有 MyISAM存储引擎支持SPATIAL索引。
b+树性质
索引字段要尽量的小，b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高；索引的最左匹配特性（即从左往右匹配）。
存储过程
一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储程序可以分为存储过程和函数, MySQL中创建存储过程和函数使用的语句分别是:CREATE PROCEDURE和 CREATE FUNCTION。使用CAL语句来调用存储过程,只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。
视图
视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中,即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中,因此视图是一个虚表。视图在概念上与基本表等同,用户可以在视图上再定义视图。
作用
1.简单化
看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
2.安全性
通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上,但不能授权到数据库特定行和特定的列上。
3.逻辑数据独立性
视图可帮助用户屏蔽真实表结构变化带来的影响。
触发器
触发器(trigger)是个特殊的存储过程,不同的是,执行存储过程要使用CALL语句来调用触发器的执行不需要使用CALL语句来调用,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被 MySQL自动调用。比如当对 fruits表进行操作( INSERT、 DELETE或 UPDATE)时就会激活它执行。
优化
优化查询
优化查询语句、索引对查询速度的影响、使用索引查询、优化子查询。
优化数据库结构
将字段很多的表分解成多个表、增加中间表、增加冗余字段、优化插入记录的速度、分析表、检查表和优化表
优化 MySQL服务器
服务器硬件、 MySQL的参数
网友评论