9、MySQL逻辑架构介绍

theoforce

　　一、概述:
　　MySQL按照逻辑划分主要分为三层：服务层，核心层和存储引擎层其各层在整个架构中所扮演的角色如下：
　　服务层：为客户端做连接处理，授权和安全认证相关工作
　　核心层：查询解析，分析，优化SQL语句，缓存结果等等
　　存储引擎层：存储和提取数据及事物的处理
　　角色分担
　　客户端：MySQL客户端指的是不同程序语言编写的前端应用程序，及所调用的API接口(Java语言：JDBC是一种用于执行SQL语句的javaAPI)，php语言的PDO扩展为PHP访问数据库定义了一个轻量级的异质性的接口，它提供了一个数据访问抽象层，这样无论使用什么数据库都可以通过一致的函数执行查询和提取数据。
　　服务层：MySQL属于单进程多线层结构。
　　核心层：完成数据的查询，缓存及优化的操作。
　　存储引擎层：MyISAMM，InnoDB，CLuster和Memory
　　存储引擎层是最贴近文件系统的功能层。
　　MySQL总体架构：
　　二、MySQL常用的存储引擎
　　查看当前MySQL所支持的存储引擎
　　mysql> show engines;
　　2.1 MyISAM存储引擎：
　　特点：支持全文索引，
　　能够对整张表进行加锁，不支持行锁，及锁粒度较大。
　　缺点：不支持事物，及没有rollback功能。
　　应用：日志系统，读表的操作，没有事物第并发的网站
　　2.2 InnoDB存储引擎：
　　特点：MySQL5.7之前的版本不支持全文索引，5.7及以后的支持
　　支持行级锁，锁粒度小
　　支持ACID(事物完整性和异质性)
　　独有的聚集索引主键设计方式，可大发提升并发读写性能
　　支持外键，支持崩溃数据的自我修复
　　注意问题
　　a) 所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键，对保证性能很有帮助；
　　b) 杜绝使用text/blob，确实需要使用的，尽可能拆分出去成一个独立的表；
　　c) 时间建议使用 TIMESTAMP 类型存储；
　　d) IPV4 地址建议用 INT UNSIGNED 类型存储；
　　e) 性别等非是即非的逻辑，建议采用 TINYINT 存储，而不是 CHAR(1)；bool
　　f) 存储较长文本内容时，建议采用JSON/BSON格式存储；
　　2．3memory存储引擎：
　　（1）memory存储引擎相比前面的一些存储引擎，有点不一样，其使用存储在内存中的数据来创建表，而且所有的数据也都存储在内存中。
　　（2）每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。
　　（3）memory存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，如果读者希望使用B树型，则在创建的时候可以引用。
　　（4）memory存储引擎文件数据都存储在内存中，如果mysqld进程发生异常，重启或关闭机器这些数据都会消失。所以memory存储引擎中的表的生命周期很短，一般只使用一次。
　　主：表级锁：分为读锁和写锁，其中读锁是共享的，可以供多人使用，而写锁是排他的，当当前表被写锁锁定的时候，其他无法读取和修改，如果是读锁则是可以其他程序可以查看。
　　注意：行级锁可能会造成“死锁”现象
　　原因分析：
　　1、MySQL行级锁并不是直接锁记录，而是锁索引，索引分为主键和非主键索引两种，如果一条SQL语句操作了主键索引，那么MySQL就会锁定这个主键索引，如果SQL语句操作的事非主键索引，那么MySQL会先锁定这个非主键索引，再去锁定主键索引。
　　2、在update和delete操作时MySQL不仅会锁定所有where条件扫描过的索引,还会锁定相邻索引(被修改的字段)。
　　3、MySQL锁定状态
　　show processlist命令可以查看当前那些线程正在运行，如果用户拥有SUPER权限可以查看所用线程，如果线程的状态有update或init某个表，则说明此事进程的status为updateing data或在sending data。
　　对于“too many connections”的错误信息，并且想要了解正在发生的情况则show processlist则非常有用。
　　相关查看命令：
　　show processlist；默认显示100条信息
　　show full processlist；显示全部线程信息
　　show open tables；查看哪些表是打开的。
　　查看服务器状态
　　mysql> show status like '%lock%';
　　查看innodb运行时的信息
　　mysql> show engine innodb status\G
　　查看服务器配置参数：
　　mysql> show variables like '%timeout%'；
　　四、指定MySQL存储引擎
　　4.1 修改配置文件是设置默认存储引擎
　　vim /etc/my.cnf
　　default-storage-engine=innodb
　　保存之后重启存储引擎
　　创建表时指定存储引擎
　　create table ( ) ennine=Innodb default