mysql笔记

mysql
1.数据类型
　　整形：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间
　　浮点形：FLOAT 、 DOUBLE、DECIMAL
　　字符串：CHAR(定长) 和 VARCHAR（变长）VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除
　　时间：DATETIME（能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间，和时区无关） 和 TIMESTAMP（保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的　　　　秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年，和时区有关）
　　大数据：BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储
　　选择优化的数据类型：
　　　　一、更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；
　　　　二、简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
　　　　三、尽量避免NULL；
　　选择表示符：
　　　　整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT，如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。
　　　　对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：
　　　　一、因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。
　　　　二、SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
　　　　三、随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。

2.存储引擎
　　1.InnoDB:是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
　　　　一、实现了四个标准的隔离级别
　　　　二、主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
　　　　2.MyISAM 不支持事务。不支持行级锁
　　区别：
　　　　一、事务: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
　　　　二、并发: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
　　　　三、外键: InnoDB 支持外键。
　　　　四、备份: InnoDB 支持在线热备份。
　　　　五、崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
　　　　六、其它特性: MyISAM 支持压缩表和空间数据索引。
3.索引（B+树）：索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。（INNDB包括主索引和辅助索引，主索引的data域存的是完整的数据记录，辅助索引的data域存的是 主键值）
　　一、数据结构：B Tree 指的是 Balance Tree，也就是平衡树，B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

　　二、操作：
　　　　1.查找：进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。
　　　　2.更新：插入删除操作记录会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。
　　三、与红黑树比较
　　　　1.查找次数更少：平衡树查找操作的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。 红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，检索的次数也就更多。

　　　　2.利用计算机预读特性：为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。
四、索引：
　　1.B+Tree 索引： 是大多数 MySQL 存储引擎的默认索引类型。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。一个表只能有一个聚簇索引
　　2.哈希索引：哈希索引能以 O(1) 时间进行查找，但是失去了有序性
　　　　限制：1.无法用于排序与分组；2.只支持精确查找，无法用于部分查找和范围查找
　　3.全文索引：MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
　　　　1.全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。
　　　　2.InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
　　4.空间数据索引：MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。
　　　　1.必须使用 GIS 相关的函数来维护数据
五、索引优化：
　　1、独立的列：查找时保证查询条件是一个独立的列，否则无法使用索引，例如不能是计算、函数等
　　2、多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
　　3、索引列的顺序：让选择性最强的索引列放在前面，索引的选择性是指: 不重复的索引值和记录总数的比值
　　4、前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
　　5、覆盖索引：索引包含所有需要查询的字段的值。
六：索引有点：
　　1、大大减少服务器需要扫描的行数
　　2、帮助服务器避免进行排序和分组,也就不需要创建临时表
　　3、将随机IO变成了顺序IO(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)
七：索引的使用场景
　　1、对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
　　2、对于中到大型的表，索引就非常有效
　　3、但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。
　　4.性能优化
　　　　1、查询分析：Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
　　　　　　重要的字段：
　　　　　　一、select_type : 查询类型，有简单查询、联合查询、子查询等
　　　　　　二、key : 使用的索引
　　　　　　三、rows : 扫描的行数
　　　　2.优化：
　　　　　　一、减少请求数据量（切忌使用SELECT *语句）只返回必要的数据列、缓存重复查询的数据
　　　　　　二、减少服务端扫描的行数（最有效的方式是使用索引来覆盖查询）
　　　　　　三、重构查询方式：
　　　　　　　　1.切分大查询：一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
　　　　　　　　2.分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联
　　　　　　　　　　好处：
　　　　　　　　　　　　1.让缓存更高效。
　　　　　　　　　　　　2.分解成单表查询，这些缓存结果可能被其他查询用到，从而减少冗余记录的查询
　　　　　　　　　　　　3.减少锁竞争
　　　　　　　　　　　　4.在应用层进行连接，可以更容易对数据库进行拆分，从而做到高性能可伸缩
　　　　　　　　　　　　5.查询本身性能也可能会有锁提升

　　5.分表分库
　　　　1.水平切分：水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中
　　　　　　sharding策略：
　　　　　　　　1.哈希取模: hash(key) % NUM_DB
　　　　　　　　2.范围: 可以是 ID 范围也可以是时间范围
　　　　　　　　3.映射表: 使用单独的一个数据库来存储映射关系
　　　　　　sharding存在的问题以及解决方案：
　　　　　　　　1.事务问题：使用分布式事务来解决，比如 XA 接口
　　　　　　　　2.链接：可以将原来的 JOIN 分解成多个单表查询，然后在用户程序中进行 JOIN。
　　　　　　　　3.ID 唯一性:
　　　　　　　　　　一、使用全局唯一 ID: GUID
　　　　　　　　　　二、为每个分片指定一个 ID 范围
　　　　　　　　　　三、分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)
　　　　2.垂直切分：垂直切分是将一张表按列切分成多个表
　　6.主从复制以及读写分离
　　　　1.主从复制
　　　　　　主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。
　　　　　　　　binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
　　　　　　　　I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
　　　　　　　　SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。
　　　　2.读写分离 主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。（读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。）
　　　　读写分离能提高性能的原因在于:
　　　　　　1.主从服务器负责各自的读和写，极大程度缓解了锁的争用；
　　　　　　2.从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
　　　　　　3.增加冗余，提高可用性。
7.MySQL InnoDB的MVCC机制
　　　1.MySQL 中InnoDB中实现了事务（多版本并发控制MVCC+锁）， 其中通过MVCC解决隔离性问题。具体而言，MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现;
　　　2.MVCC，全称Multi-Version Concurrency Control，即多版本并发控制

8.SQL执行过程
　　过程：
　　　　1.MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理
　　　　2.将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言，然后选择最优的查询路径去查询
　　　　3.优化器执行选出最优索引等步骤后，通过执行器去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句

 

　　1.SQL接口：MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理
　　2.查询解析器：将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言，然后选择最优的查询路径去查询（查询优化器）
　　3.查询优化器： MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划。MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本（MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行）
　　　　IO成本：即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，一页的成本为1，所以IO成本和查询的页数相关
　　　　CPU成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。
　　4.存储引擎
　　　　查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的
　　5.执行器
　　　　执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行