Mysql进阶

Mysql进阶

Mysql架构

1.存储引擎

存储引擎包含InnoDB（默认），MyISAM，Archive,Memory等等存储引擎是针对表的，因此也叫表类型，可以在创建表的时候指定引擎，没有指定则使用默认引擎。
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
…
) ENGINE = INNODB [ COMMENT 表注释 ] ;

InnoDB和MyISAM对比如下：

2.索引

2.1索引分类

索引分类包含单列索引，唯一索引，主键索引，复合索引
索引分类还可以分为聚集索引和非聚集索引
聚集索引：
将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，有主键时主键索引就是聚集索引，没有主键时就使用第一个唯一索引，都没有时会自动生成一个rowid作为隐藏的聚集索引。
非聚集索引：
将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以有多个

回表查询

2.2索引使用

1. 使用联合索引，必须要遵守最左前缀法。查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会后续失效
2. 不要在索引列上进行运算操作， 否则索引将失效。
3. 头部模糊查询，索引失效。仅仅尾部则不会。如
   select … from name like 'Li%'有效
   select … from name like '%LI’无效
4. 但凡or的一侧没有索引，则索引失效
5. 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。注意：>=、<= 不会引起索引失效

2.3索引性能分析

explain语句

type依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。
system：表中仅有一行。
const：通过索引一次就找到 （主键或者唯一索引） 。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。
range: 对索引进行范围检索。
index: 物理文件全扫描，速度非常慢
all：遍历全表以找到匹配的行。
Extra 含义
Using where; Using Index 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition或者NULL 查找使用了索引，但是需要回表查询数据

2.5索引设计原则

• 针对数据量较大且查询比较频繁的表建立索引。
• 针对常作为查询条件、排序、分组操作的字段建立索引。
• 选择区分度较高的列作为索引。
• 字段长度较长的使用前缀索引
• 尽量使用联合索引，联合索引很多时候可以覆盖索引，提高查询的效率。
• 要控制索引的数量，索引越多维护索引的代价也就越大，会影响增、删、改的效率。
• 如果索引列不能存储null时创建表时使用not null来约束，当优化器知道每列是否包含空值时，它可以更好的确定哪个索引最有效的用于查询。

3.锁

• 全局锁：锁住所有表
• 表锁 ：锁住整张表
• 行锁 ：锁住单条行数据
• 全局锁：加锁命令flush tables with read lock;释放锁unlock tables ;应用场景数据库备份
• 表锁：表共享锁lock tables table_name read，表独享写锁lock tables table_name write;，元数据锁加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上，意向锁 查看是否锁表show open tables where in_use > 0;
• 行锁：每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类行锁/间隙锁/临键锁

4.主从复制/读写分离

主服务器写，从服务器读取

读写分离方案：

1. 程序代码进行路由控制如select insert进行控制
2. 利用代理服务器处理 Mysql-Proxy，代理服务器接到sql请求进行判断，但mysql官方不建议将该产品应用到生产环境
3. Amoeba变形虫，由阿里巴巴人员开发，不支持存储过程和事务

主从复制：mysql主从复制基于异步操作，利用自带的二进制文件实现，无需第三方工具自带功能
复制原理：

复制三个步骤：
1). MySQL master 将数据变更写入二进制日志( binary log)
2). slave将master的binary log拷贝到它的中继日志（relay log）
3). slave重做中继日志中的事件，将数据变更反映它自己的数据

5.分库分表

• 垂直分库分表
• 水平分库分表

5.1Mycat中间件

mycat是开源的、活跃的、基于java语言编写的MySQL 数据库中间件。可以像使用mysql一样来使用mycat。mycat可以用来分库分表，读写分离等。