MySQL学习笔记

---

一，MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL是开源的，所以你不需要支付额外的费用。
• MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL使用标准的SQL数据语言形式。
• MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表 - 文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。

MySQL官网：https://www.mysql.com/
国内的基本教程：http://www.runoob.com/mysql/mysql-tutorial.html

---

二，存储引擎

插件式存储引擎时MySQL数据库最重要的特点之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适用不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制化。

创建新表时不指定存储引擎，那么系统就会使用默认存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。

查询当前数据库支持的存储引擎。

各种存储引擎的特性

1，InnoDB

InnoDB存储引擎提供了具有提交、回滚和奔溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据的索引。
存储引擎为InnoDB的表在使用过程中不同于使用其他存储引擎的特点：

• 外键约束
• 存储方式

2，MyISAM

MyISAM不支持事务，也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。

3，MEMORY

MEMORY存储引擎使用存在于内存中的内容来创建表。MEMORY类型的表访问非常地块，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

4，MRG_MyISAM

MRG_MyISAM存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MRG_MyISAM表本身并没有数据，对MRG_MyISAM类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

如何选择合适的存储引擎

在选择存储引擎时，应根据应用特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

1，InnoDB

用于事务处理应用程序，支持外键。如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。

2，MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎时合适的。

3，MEMORY

将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表的数据可以恢复。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问的结果。

4，MRG_MyISAM

用于将一系列等同的MyISAM表以逻辑方式组合在一起，并且作为一个对象引用它们。MRG_MyISAM表的优点在于可以突破单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MRG_MyISAM表的访问效率。这对于诸如数据仓储等比较合适。

---

三，视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图动态生成。

视图相对于普通的表的优势主要包括以下几项。

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单地实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列队视图没有影响；袁彪修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

物化视图

许多数据库管理系统（例如Oracle、SQL Server、PostgreSQL）都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。

普通视图的每一次查询实际上都是对源表的查询，物化视图第一次查询，跟普通视图一样，都是查询关联的数据表，然后将数据保存，下次再次查询时，就是直接从实例化视图查询，不用再查询关联的数据表。

实例化中的数据不会实时刷新，也就是说，当你表中数据发送变化时，实例化视图中的数据还是保持不变，因为数据已经实例化，不过可以手动刷新。

MySQL并不原生支持物化视图，但是可以使用开源工具Flexviews，也可以自己实现物化视图。Flexviews比完全自己实现的解决方案要更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成：

• 变更数据抓取（Change Data Capture，CDC）功能，可以读取服务器的二进制日志并且解析相关行的变更。
• 一系列可以帮助创建和管理视图的定义的存储过程。
• 一些可以应用变更到数据库中的物化视图的工具。

---

四，存储过程和函数

存储过程和函数时事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的，但是不方便调试。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用in、out、inout类型，而函数的参数只能是in类型。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。

---

五，触发器

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

---

六，索引

Mysql索引主要有两种结构：B+Tree索引和Hash索引
MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引
相关博客：
https://www.cnblogs.com/whgk/p/6179612.html
https://blog.csdn.net/qq_19557947/article/details/76951912

索引的设计

索引的设计可以遵循一些已有的原则，创建索引的时候尽量考虑符合这些原则，便于提升索引的使用效率，更高效地使用索引。

1，搜索的索引列，不一定是所要选择的列。即，最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。

2，使用唯一索引。考虑某列中值的分布，索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有M和F，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

3，使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
例如，有一个char(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘io较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值，这样就增加了找到列而不用读取索引中较多块的可能性。

4，利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
比如在col1+col2+col3字段上的联合索引能够被包含col1、（col1+col2）、（col1+col2+col3）的等值查询利用到，可是不能够被col2、（col2+col3）的等值查询利用到。

5，不要多度索引。不要以为索引越多越好。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL选择不到所使用的最好索引。只保持所需的索引有利于查询优化。

6，对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问最快，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

存在索引但不能使用索引的典型场景

有些时候虽然有索引，但是并不被优化器选择使用。

1，以%开头的like查询不能够利用B-Tree索引。

2，数据类型出现隐式转换的时候不会使用索引。

3，复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则，是不会使用复合索引的。

4，如果MySQL估计使用索引比全表扫描更慢，则不使用索引。

5，用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次扫描增加I/O访问，一次全表扫描过滤条件就足够了。

---

七，可视化工具

可视化工具推荐Navicat for MySQL，界面友好，有中文版。
中文破解版：
http://download.csdn.net/download/chen_victor/9952591

---

八，MySQL体系结构

MySQL实例由一组后台线程、一些内存块和若干服务线程组成。

默认情况下，MySQL有7组后台线程，分别是1个主线程，4组IO线程，1个锁线程，1个错误监控线程，一个purge线程。这些后台线程的主要功能如下：
master thread：主要负责将脏缓存页刷新到数据文件，执行purge操作，触发检查点。
insert buffer thread：主要负责插入缓冲区的合并操作。
read thread：负责数据库读取操作，可配置多个读线程。
write thread：负责数据库写操作，可配置多个写线程。
log thread：用于将重做日志刷新到logfile中。
purge thread：执行purge操作。
lock thread：负责锁控制和死锁检测等。
错误监控线程：负责锁控制和死锁检测等。

可以通过show engine innodb status命令查看这些线程的状态。

---

九，MySQL系列学习笔记

MySQL SQL优化
一致哈希算法
MySQL分区
MySQL锁机制
MySQL应用优化
MySQL日志
MySQL复制
MySQL Cluster集群
MySQL高可用架构
MySQL分布式事务

---

九，参考资料

《深入浅出MySQL 数据库开发、优化与管理维护》
《高性能MySQL》