MySQL

一，连接层功能：

1，TCP/IP或者Socket的链接方式

2，验证用户名密码功能

3，连接线程(客服)：接收SQL语句，返回执行结果

 

二，SQL层

1，语法检查模块，检查上层发过来的SQL语句，进行语法检查

2，权限检查模块，检测当前登录用户是否有权限操作数据库对象

3，语义检查模块，识别语句种类

4，解析器，解析出SQL语句所有可能的执行方式，这些方式被称为“执行计划”

5，优化器，基于执行代价(系统资源的消耗作为维度（cpu、内存、IO），管理员可以通过间接的方法，干扰优化器的选择）

6，执行器，按照优化器选择的“最优”的执行计划，执行SQL语句。得出结论：某某磁盘的某某位置，发送给存储引擎层。

7，提供查询缓存：有局限性。一般会用Redis产品来替代

8，记录查询日志

 　　

SQL语句的分类：

DDL：数据定义语言

DCL：数据控制语言

DML：数据操作语言

DQL：数据查询语言

 

三，存储引擎层：

和“磁盘”（文件系统）打交道的层次

根据SQL层执行的结果，去磁盘上找到对应的数据。结构化成表的模式，返回给用户。

  

MySql逻辑结构组成：

一，库（库名字，库属性）

 二，库（库名字，列（列名字，列属性）表属性，表数据）

 

MySQL索引

　　MySQL索引分类

存储引擎管理

一，索引管理以及原理

1，索引的种类

B树（B+tree  B*tree b-tree）

B-tree：针对某个明确值的查询

查找顺序：根节点——>枝节点——>叶子节点

B+tree：针对明确的范围的查询

缺点：无法同时包含回表查询以及其它列的查询

 

辅助索引：（B TREE）

alter table t1 add index idx_num(telnum);

怎么生成的：

根据创建索引时，指定的列的值，进行排序后，存储在叶子节点中

优点：

1，优化了查询，减少CPU 内存  IO消耗

2，减少了文件排序

 

覆盖索引：(联合索引)

通过一个索引列把需要查询的列都添加到索引树里面去

直接在索引树里面直接有，减少或尽量避免回表查询操作

优点：减少回表查询的操作的几率

聚集索引 ：

同一等级情况下效率最高

大部分情况下辅助索引和聚集索引结合使用

辅助索引：where 条件   有id列的时候，比如id=28

随机读? 顺序读

建表时，如果有主键列，自动生成聚集索引

没有主键列，会去找unique列

优点：

减少回表查询

将随机IO转换成顺序IO

加强了缓存的应用，查询效率提升

 

2，R树

3，hash索引

4，全文索引

2，B树索引的类型

　　1，聚簇索引（cluster index）：一般是基于主键的

　　2，辅助索引（普通辅助索引：回表查询；覆盖索引；不回表查询；唯一性索引）

　　3，唯一索引

3，索引管理

索引的作用：在mysql数据库中，索引是用来优化查询的

除缓存之外，数据的查询有两种方式：

1，全表扫描

2，索引扫描

优化器索引扫描要优于全表扫描

索引管理：

索引就是一张表

创建索引的语句：

为某张表基于某列建索引：alter table 表名 add index idx_name(列名)

 

　　MySQL不走索引的情况（开发规范）

重点关注：

1，没有查询条件，或者查询条件没有建立索引

　　select * from tab ;全表扫描

　　select * from tab where 1=1;

　　在业务数据库中，特别是数据库量比较大的表，是没有全表扫描这种需求的，当然我们也应该避免这种情况

　　　　1，对用户查看是非常痛苦的

　　　　2，对服务器来说是毁灭性的

　　　　(1) select * from tab

　　　　SQL改写成以下语句：

　　　　select * from tab order by price limit 10 ; 　　　　　　需要在price列上建立索引

　　　　(2) select * from tab where name=‘zhangsan‘;　　　name列没有索引

2,查询结果集是原表中的大部分数据，应该是25%以上。

　　查询的结果集，超过了总数行数25%，优化器就会觉得没有必要走索引了。

　　假如：tab表 id，name    　　id:1-100w，id列有索引

　　select * from tab where id>50000;