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