一、MySQL 结构
二、MySQL 数据库引擎
1、数据库存储引擎主要是 算法+IO
算法:如何存数据,比如存在什么样的文件里,格式是什么样的等等
IO:和数据库交互时,IO的访问数据的方式,比如是根据物理地址访问,还是根据扇区连续索引依次访问等等
2、常见数据库存储引擎
了解三个常见的即可:
(1)ISAM
主要适用于,查询远大于更新的场景。优点,读取操作速度很快,但不支持事务,不支持容错恢复。目前用的比较少
(2)MyISAM
ISAM 增强版(mysql 5.5之前版本,默认的是此引擎)
保留了读取速度很快的特点,进行了大量扩展,如索引功能。但表损坏后无法修复(硬盘崩溃可以)、不支持事务
(3)InnoDB
有跨越性特性的引擎,造就了目前MySQL 灵活性的产品
虽然查询性能没有ISAM和myISAM高,但整体来说处理大量数据时的组大性能设计,整体性能很高,支持事务和外键(悲观锁,乐观锁都有),耗用存储资源也比较小。
5.5以上常用版本的默认引擎。
5.7版本中,InnoDB管理的数据文件是 .frm文件(表和数据)和 .idb(索引)文件
(4)Memory
数据存在内存中,只有结构信息存在磁盘中,所以读取速度极快,但死机之后数据丢失。但应用场景极少,一般如临时数据。
3、数据库引擎管理命令
三、数据库索引
1、索引介绍
索引优点:创建索引可以大大提升系统的查询性能
原理:索引的值换算成该行数据在物理磁盘上的存储位置
所以:创建唯一性索引,保证每行数据的唯一性,大大加快数据检索速度
索引的缺点:耗费时间、耗费空间、拖累速度
(1)索引的创建和维护(数据库负责维护)需要耗费时间,并且索引需要占用物理空间。
(2)对数据表中的数据增加、修改时,索引页需要动态维护,降低了数据更新的速度
PS:
什么样的地段适合建立索引:
(1)经常搜索的列上,可以加快搜索速度
(2)经常使用的外键,外键一般作为表间连接,可以加快连接速度
(3)经常使用在where子句的列上,加快条件判断速度
(where f1 and f2 ,必须在字段f1、f2上同时建立索引)
什么样的字段不适合建立索引:
(1)不经常使用或 数据值很少的列(如:人事表的性别)
(2)定义为text(大文本)、image、bit(文件)数据类型,因为数据量大或值很少
2、MySQL支持的索引类型,即索引的存储形式
(1)B-Tree索引
索引按照平衡树(balancetree)的结构来存储,和普通树结构不同的是主要压缩树的深度(越深索引越慢),广度比较大,索引时比较快
(2)Full-text索引
全文索引,存储结构也是,平衡树。主要把索引中的关键字进行了全数据排列,是解决需要用like查询效率低的问题,只能解决"**%"形式的like查询。
3、索引的建立和管理
MySQL通过命令行创建索引
四大索引(普通索引、唯一索引、全文索引、聚合索引)
(1)普通索引
最基础的索引,允许建立在任意的字段中,没有严格要求,可以有空数据。也是5.5以上默认的索引类型(5.5以上存储引擎是InnoDB,InnoDB默认索引时普通索引)。
creat index [index_name] on [table_name](column(length)) 直接创建方式
如:creat index name_index on users(name(3)) 给users表中的name列(取该列数据的前三位放到索引里,可以不写,即所有数据都放到索引里),创建索引名字为name_index,
查看:show index form user
alter table [table_name] and index [index_name](column(length)) 修改表结构的方式添加索引
查看索引
show index from [table_name]
show keys from [table_name]
(2)唯一索引
(3) 全文索引
仅三种字段允许创建全文索引
(4)组合索引
4 索引的优化
5、索引总结
四、SQL优化