MySQL

This is my personal notes for MySQL.

MySQL数据类型

• 整型

  TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

  INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

• 浮点数

  FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

  FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

• 字符串

  主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

  VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

  在进行存储和检索时，会保留 VARCHAR 末尾的空格，而会删除 CHAR 末尾的空格。

• 时间和日期

  MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

  • 1 DATETIME

    能够保存从 1000 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

    默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

  • 2 TIMESTAMP

    和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

    它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

    MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

    默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

    应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

MySQL存储引擎

• InnoDB

  是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

  实现了四个标准的隔离级别，默认级别是可重复读（Repeatable Read）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻读。

  主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

  内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

  支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

• MyISAM

  设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

  提供了大量的特性，包括压缩表、空间数据索引等。

  不支持事务。

  不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

  可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

  如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

• 比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

  • 外键：InnoDB 支持外键。

  • 备份：InnoDB 支持在线热备份。

  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

  • 其它特性：MyISAM 支持压缩表和空间数据索引。

MySQL索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

• 索引类型

  • 1 B+Tree 索引

    是大多数 MySQL 存储引擎的默认索引类型。

    因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

    因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

    可以指定多个列作为索引列，多个索引列共同组成键。

    适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

    InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

  • 2 哈希索引

    哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

    无法用于排序与分组； 只支持精确查找，无法用于部分查找和范围查找。 InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

  • 3 全文索引

    MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

    查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

    全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

    InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

  • 4 空间数据索引

    MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

    必须使用 GIS 相关的函数来维护数据。

• 索引优化

  • 1 单个索引

    在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

    以下语句不能使用id的索引

      SELECT id FROM idList WHERE id + 1 = 5;
    

  • 2 多列索引

    在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 id 和 num 设置为多列索引。

    多列索引的使用需要符合最左匹配原则。(eg：多列索引col1、col2和col3，则 索引生效的情形包括 col1 或者 col1，col2 或者 col1，col2，col3)。

      SELECT id, num FROM table1
      WHERE id = 1 AND num = 2;
    

  • 3 索引列的顺序

    让distinct程度(count(distinct col)/count(*))最高的列排在前面

• 索引的使用条件

  • 1 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

  • 2 对于中到大型的表，索引就非常有效；

  • 3 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

MySQL表切分

• 水平切分

  水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

  当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

  

• 垂直切分

  垂直切分是将一张表按列切分成多个表, 解决表宽度问题，具体包括以下几种拆分手段：

  • 把不常用的字段单独放在同一个表中；

  • 把大字段独立放入一个表中；

  • 把经常使用的字段放在一起；

  例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

  

Mysql和Redis的比较

• 一、redis和mysql介绍

  Redis基于内存，读写速度快，也可做持久化，但是内存空间有限，当数据量超过内存空间时，需扩充内存，但内存价格贵。

  MySQL基于磁盘，读写速度没有Redis快，但是不受空间容量限制，性价比高。

  大多数的应用场景是MySQL（主）+Redis（辅），MySQL做为主存储，Redis用于缓存，加快访问速度。需要高性能的地方使用Redis，不需要高性能的地方使用MySQL。存储数据在MySQL和Redis之间做同步。

• 二、redis和mysql的区别

  两者的区别非常大。 具体如下：

  • 1 mysql是一个中小型的网络数据库，比oracle和sqlserver小， 但是并发能力远超过acess这样的桌面数据库。
  • 2 redis是一个支持网络、可基于内存亦可持久化的日志型、Key-Value数据库。
  • 3 可以认为redis比mysql简化很多。
  • 4 mysql支持集群。

  现在大量的软件使用redis作为mysql在本地的数据库缓存，然后再适当的时候和mysql同步。

• 三、为什么使用缓存？

  当网站的处理和访问量非常大的时候，我们的数据库的压力就变大了，数据库的连接池，数据库同时处理数据的能力就会受到很大的挑战，一旦数据库承受了其最大承受能力，网站的数据处理效率就会大打折扣。此时就要使用高并发处理、负载均衡和分布式数据库，而这些技术既花费人力，又花费资金。

• 四、什么是redis缓存到mysql？

  Redis其实就是说把表中经常访问的记录放在了Redis中，然后用户查询时先去查询Redis再去查询MySQL，确实实现了读写分离，也就是Redis只做读操作。由于缓存在内存中，所以查询会很快。对于一个sql语句格式的数据请求，首先计算该语句的MD5并据此得到结果集标识符，然后利用该标识符在Redis中查找该结果集。注意，结果集中的每一行都有一个相应的键，这些键都存储在一个Redis集合结构中。如果Redis中不存在这样一个集合，说明要找的结果集不在Redis中，所以需要执行相应的sql语句，在Mysql中查询到相应的结果集，然后按照上面所说的办法把结果集中的每一行以字符串或哈希的形式存入Redis。

• 五、如何redis数据和mysql数据同步？

  redis其读取速度快，但是redis 怎么和数据库同步呢，怎么能把数据库的所有数据存到redis里面，能使用户更快速的查找。

  • 方法1：mysql 同步到redis：解析mysql的binlog，然后做同步处理，可以使用的库有：open-replicator（https://github.com/whitesock/open-replicator）

  • 方法2：同步redis数据到mysql：（https://github.com/leonchen83/redis-replicator）

  后来发现，redis读取速度快，也没有必要把所有的数据都放到redis里面，redis里面只放使用频繁，用户操作量较大的数据，或者用户近期使用的数据。

  解决办法： 

  • 1：读取数据的时候先从redis里面查，若没有，再去数据库查，同时写到redis里面，并且要设置失效时间。  
  • 2：存数据的时候要具体情况具体分析，可以选择同时插到数据库和redis（要是存放到redis中，最好设置失效时间），也可以选择直接插到数据库里面，少考虑一些问题。