mysql学习之旅途（七）（高级部分）

mysql逻辑架构解析

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，
==插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。==这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。

2.5 Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
缓存是负责读，缓冲负责写。

3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

存储引擎简介

1 如何用命令查看
看你的mysql现在已提供什么存储引擎:

看你的mysql当前默认的存储引擎:
show variables like ‘%storage_engine%’;

性能下降sql慢

1.查询数据过多
2.关联了太多表，太多的join
3.没有利用到索引
4.服务器调优及各个参数设置。

sql执行顺序

手写

机读

总结

索引

mysql官方对索引的定义为：索引（Index）是帮助Mysql高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。可以简单的理解为“排好序的快速查找数据结构”

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。
B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式)
B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。
B+TREE:Innodb 所使用的索引

索引优势和劣势

优势：
1.提高数据检索的效率，降低数据库的IO成本。
2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：
1.实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
2.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
3.索引只是提高效率的一个因素，如果你的Mysql有大数据量的表，就需要花时间研究建立最优秀的索引或优化查询语句。

mysql索引分类

主键索引

设定为主键后数据库会自动建立索引，innodb为局簇索引。
语法：
随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
unsigned (无符号的)
使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。

CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);

单独建主键索引：
ALTER TABLE customer
add PRIMARY KEY customer(customer_no);

删除建主键索引：
ALTER TABLE customer
drop PRIMARY KEY ;

修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。
语法：

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
随表一起建立的索引 索引名同 列名(customer_name)
单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
删除索引：
DROP INDEX idx_customer_name ;

唯一索引

索引列的值必须唯一，但允许有空值。
语法：

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
删除索引：
DROP INDEX idx_customer_no on customer ;

复合索引

即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)，当表的行数远大于索引列的数目时可以使用复合索引
语法：

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
删除索引：
DROP INDEX idx_no_name on customer ;

SHOW INDEX FROM table_name\G
table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
customer 0 primary 0 id A 0 (NULL) (NULL) BTREE
customer 1 custm_no 1 custm_no A 0 (NULL) (NULL) YES BTREE
non_unique: 是否是唯一索引 1：是 0：不是
seq_in_index:列 在索引中的 序列。针对符合索引(一个索引对应多个列)。针对同一个复合索引 按照创建复合索引时的顺序进行排序
collation:
cardinality:
sub_part:
packed:
Null:是否允许 null 值
comment:
index_comment:

使用ALTER命令

有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

索引结构与检索原理

哪些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引。
4.频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
8.查询中统计或者分组字段
哪些情况不需要创建索引
1.表记录太少
2.经常增删改的表，提高了查询速度，同时却会降低更新表的素的，如对表进行insert,update,和delete。因为更新表时，mysql不仅要保存数据，还要保存索引文件。
3.where条件里用不到的字段不创建索引
4.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。