MySQL逻辑分层，存储引擎，sql优化，索引优化以及底层实现方法

本篇内容介绍了“MySQL逻辑分层，存储引擎，sql优化，索引优化以及底层实现方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：主机域名、虚拟主机、营销软件、网站建设、北仑网站维护、网站推广。

一 ， 逻辑分层　　

连接层：连接与线程处理，这一层并不是MySQL独有，一般的基于C/S架构的都有类似组件，比如连接处理、授权认证、安全等。

服务层：包括缓存查询、解析器、优化器，这一部分是MySQL核心功能，包括解析、优化SQL语句，查询缓存目录，内置函数（日期、时间、加密等函数）的实现。

引擎层：负责数据存储，存储引擎的不同，存储方式、数据格式、提取方式等都不相同，这一部分也是很大影响数据存储与提取的性能的；对存储层的抽象。

存储层：存储数据，文件系统。

二 ， 存储引擎

查看数据库支持的存储引擎：show engines;

如果要想查看数据库默认使用哪个引擎，可以通过使用命令： show variables like '%storage_engine%';

指定数据库对象的引擎：

create table tb(
id int(4) auto_increment ,
name varchar(5),
dept varchar(5) ,
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8 ;

查看建表语句:show create table default_table;

MySQL 存储引擎 MyISAM 与 InnoDB 如何选择？

虽然 MySQL 里的存储引擎不只是 MyISAM 与 InnoDB 这两个，但常用的就是它俩了。可能有站长并未注意过 MySQL 的存储引擎，其实存储引擎也是数据库设计里的一大重要点，那么博客系统应该使用哪种存储引擎呢？

下面我们分别来看两种存储引擎的区别。

• 一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

• 二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

• 三、InnoDB支持外键，MyISAM不支持

• 四、MyISAM是默认引擎，InnoDB需要指定

• 五、InnoDB不支持FULLTEXT类型的索引

• 六、InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表

• 七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引

• 八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

• 九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

通过以上九点区别，结合个人博客的特点，推荐个人博客系统使用MyISAM，因为在博客里主要操作是读取和写入，很少有链式操作。所以选择MyISAM引擎使你博客打开也页面的效率要高于InnoDB引擎的博客，当然只是个人的建议，大多数博客还是根据实际情况下谨慎选择。

三， sql优化

3.1.1  mysql 内部实现索引原理（B+Tree）

3.1.1.1 ，  二叉树

3.1.1.2 ，  B-Tree　　　　　　　　

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree： 
　　　　　　　　

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】　

2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。

3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。

5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

3.1.1.3 ，  B+Tree(查询任意数据的次数是 n)

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。

2. 所有叶子节点之间都有一个链指针。

3. 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示： 

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

上面都应该知道B+Tree 了吧，所以我们在建立索引时，会生成一个B+Tree  如果我们在只查询索引字段时，sql 语句就直接去B+Tree 查，不会再去数据表中查了，这样提升性能是很重要的。 还有就是对于总是修改的字段不要对他建立索引，因为字段修改了，B+Tree 结构就要重构，这要是会降低性能的。

3.1.1 索引分类：

mysql索引的四种类型：主键索引、唯一索引、普通索引和全文索引。通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

主键索引: 主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

alert table tablename add primary key (`字段名`)

  唯一索引:    索引列的所有值都只能出现一次，即必须唯一，值可以为空。

alter table table_name add primary key (`字段名`);

   普通索引 :   基本的索引类型，值可以为空，没有唯一性的限制。

alter table table_name add index (`字段名`);

   全文索引:
        全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。        全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技术处理中文。

3.2.2索引的机制

1.为什么我们添加完索引后查询速度为变快？

    传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍

    在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

2.索引的代价
    2.1创建索引是为产生索引文件的，占用磁盘空间
    2.2索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

3.在哪些column上使用索引？
    3.1较频繁的作为查询条件字段应该创建索引
    3.2唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
    3.3更新非常频繁的字段不适合作为索引
    3.4不会出现在where子句中的字段不该创建索引

总结: 满足以下条件的字段，才应该创建索引.
a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值 c: 字段内容不是频繁变化。

3.2.2、SQL解析顺序

接下来再走一步，让我们看看一条SQL语句的前世今生。

首先看一下示例语句

SELECT DISTINCT  .....   FROM .....  JOIN  .....  ON   .....  WHERE.....   GROUP BY    .....   HAVING  .....   ORDER BY   .....   LIMIT .....

　 然而它的执行顺序是这样的

FROM   .....  ON  .....    JOIN  .....   WHERE     ..... GROUP BY     .....  HAVING  .....    SELECT    DISTINCT   .....  ORDER BY   .....  LIMIT   .....

   3.2.3 如何建立索引　　　　　　　　

　一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况.

3.2.4     使用索引时，有一些技巧：

1.索引不会包含有NULL的列

只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.　索引要建立在经常进行select操作的字段上。而经常修改的字段，没没必要建立索引了，因为，你建立了索引会生成一个B+树，你修改了该索引的字段后，这个B+树就需要修改，反而对性能不是很好。

　3. 复合索引 : 复合索引，不要跨列或无序使用（最佳左前缀）

4.like语句操作: 一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

    5. 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

   6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

   7.索引要建立在经常进行select操作的字段上。

  这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

   8.索引要建立在值比较唯一的字段上。

    9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

   10.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

 三， sql性能问题

　　a.分析SQL的执行计划  : explain   ，可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况

　　b.MySQL查询优化其会干扰我们的优化（mysql服务层有一个sql优化器），可以对我们写的sql进行优化，这是我们控制不了的。

　　查询执行计划：  explain +SQL语句     explain SELECT * from book ;

id : 编号
select_type ：查询类型
table：表
type:索引类型 system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提：有索引 一般能达到range 就行。
possible_keys ：预测用到的索引
key：实际使用的索引
key_len：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra:额外的信息 下面是他可能发出的情况

i). using filesort ： 性能消耗大；需要“额外”的一次排序（查询）  。常见于 order by 语句中。 解决：where哪些字段，就order by那些字段2

ii). using temporary:性能损耗大 ，用到了临时表。一般出现在group by 语句中。 解决： 避免：查询那些列，就根据那些列 group by .

iii). using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）
只要使用到的列 全部都在索引中，就是索引覆盖using index

iii).using where （需要回表查询）。

假设age是索引列
　　　　但查询语句select age,name from ...where age =...,此语句中必须回原表查Name，因此会显示using where.  解决 吧name  也添加到索引中去。

“MySQL逻辑分层，存储引擎，sql优化，索引优化以及底层实现方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！