mysql数据库原理与sql优化_MySQL数据库原理与sql优化

一、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优化