mysql读书笔记

Mysql 中的 Null

逻辑比较中如果出现 null 整个值就是 null, 如 select 2=null 返回为 null

如果要查找为 null 的值应使用 is null, 而不能用 =null 如：

Select id from user where name is null;

包含 null 的表达式总会导出 null ，

如： SELECT NULL, 1+NULL, CONCAT('Invisible',NULL); 结果为 null

对于聚合函数如 count(),min(),max(),sum() 等，会忽略 null 。 Count(*) 除外

尽可能的避免索引字段允许为空

当使用一个 nullable 的列作为索引时，每个实体都需要额外的一个字节，在 MYISAM 中甚至会将固定大小的索引变为可变大小的（比如一个整数列的索引）。因此列中确实可以允许为空时可使一个 default 为 0 ，一个字符串 default 为空字符串等。

Varchar 用一到两个字节保存字符串长度 0-255 时用一个，大于 255 时用 2 个。当其进行更新操作而是原来的存储空间不再合适当前值时 MYISAM 可能会把行拆开， InnoDB 可能会分页。当最大长度远大于平均长度，并且很少发生更新用 varchar 时比较合适的。

Mysql 对 BLOB 和 TEXT 排序和其它类型有所不同，它不会按照字符串完整长度进行排序，而是按照 max_sort_length 规定的若干个字符将进行排序。 Mysql 也不能索引这些数据类型的完整长度。

临时表

Mysql 在进行一些内部操作的时候会临时创建一些数据库表。这些操作主要包括 group by ， distinct ，一些 order by 语句， union ，一些 from 语句中的子查询。例如：

1.               使用了 order by 和不同的 group by 子句，或 order by(group by)join queue 后非第一个表中的列，临时表将被创建。

2.               使用了 sql_small_result 选项， mysql 会用到 in-memory 临时表。（ sql_small_result 与 sql_big_result 是一对相对的关键次，必须与 group by distinct 一起使用， sql_small_result 告知优化其结果会很小让 mysql 使用临时表而不适用排序）。

3.               order by 与 distinct 一起使用可能会创建临时表。

出了直接创建磁盘临时表外，大量的磁盘临时表是由内存临时表转化来的。临时表示存在于内存中，由 MEMORY 引擎进行处理，在内存中今行处理，速度较快。而磁盘临时表中的创建，操作，销毁都是在磁盘上进行的，因此速度较慢。因此，要尽量避免磁盘临时表的产生。先来看一下磁盘临时表示如何产生的：

有些时候是不能使用 MEMORY 临时表而不得不使用 MYISAM 引擎来处理临时表。

1.               列中含有 BLOB 或 TEXT 字段，如果列中含有这些字段临时表将直接生成 MYISAM 表，因为这些类型的字段不能存在于 MEMORY 表中。

     值得注意的是 MEMORY 中总是使用固定长度的列来存储，如 varchar(255) 知存了 y,n 时内存中任然用 255 个字节来存储，因此要避免使用不必要的长度来存储内容。

2.               order by 中出现了大于 512byts 的列。

3.    使用 union （ all ）的前提下， select 子句中出现了大于 512byts 的列。

创建的 MEMORY 临时表过大时将转为 MYISAM 表存储到磁盘上。 Max_heap_table_size 确定了 memory 临时表的上限

索引

B-TREE INDEX

MYISAM 使用前缀压缩使索引变的更小，通过索引的指向实际行的物理位置。而 InnoDB 通过主键的值来引用实际行。一下类型查询可以用到 B-TREE 索引

1.       对索引全部匹配

2.       最左前缀

3.       匹配被索引值一个范围

4.       精确匹配一部分，匹配另一部分的范围

5.       只访问索引的查询

HASH INDEX

    它是建立在 hash table 基础上的，只对索引中的每一列的精确查找有用。

聚集索引（ clustered index ）

    聚集索引不是一种单独的索引类型，而是一种存储数据的方式。 InnoDB 的聚集索引用相同的结构保存了 B-Tree 索引和数据行。

    当表中有聚集索引的时候，数据行保存在索引的叶子页中。“聚集”指数据行和相关的键值保存在一起。每个表只能有一个聚集索引因为一个行只能保存在一个地方（但是覆盖索引可以模仿多个聚集索引）。

    一般 InnoDB 按照主键进行聚集。

    如果没有主键 InnoDB 会试着以唯一非空索引来代替。

    如果没有这种索引， InnoDB 会定义隐藏的主键，并在上面进行索引。

InnoDB 只聚集在同一页面中的数据。

优点：

可以使相关联数据保存在一起。

数据访问快。因为聚集索引将索引和数据放到一块，所以一般来说取数据比没用聚集索引时快。

使用覆盖索引的查询，可以使用包含在叶子节点中的主键值。？？？？？？？？

缺点：

     聚集索引可大幅提升 i/o 密集负载的性能。如果数据能装入内存，那么其顺序也就无所谓了，这样聚集索引就没有什么意义了。

    插入速度严重依赖于插入顺序。按主键顺序插入 InnoDB 是最快速的插入方法。如果没有，在插入之后最好用 optimize table 命令重新组织一下表。

    更新聚集索引的列是比较 expensive 的，因为 InnoDB 会强制更新的行到新的位置

    聚集表可能会比全表扫描慢，尤其在表存储的比较稀疏，或因分页而没有顺序存储的时候，

     第二索引可能会比想象中的大，因为它的叶子节点包含它所指向行的主键列。

    第二索引需要两次索引查找。因为第二索引并不能直接找到所在的行，只能找到所在行的主键，然后再通过主键索引查找所在行。

    建立了聚集索引的表在有新行插入或某行的主键更新，该行必须被移动时就可能会进行分页。当行的键值要求要放到一个已经放满了数据的页时存储引擎就会进行分页。分页会占用更多的磁盘空间。

MYISAM 和 InnoDB 的数据布局

MYISAM 索引实现：

设表一共有三列， col1 为 primary key 。可以看出 MYISAM 索引文件只保存数据记录的地址。在 MyISAM 中主索引和辅助索引在结构上没有任何区别，只是主索引要求其值时唯一且不为空的，而辅助索引则没有这个要求。

InnoDB 索引实现：

InnoDB 数据存储结构

Mysql 将数据逻辑的放在 ib_data1 文件中，我们称之为表空间。当然也可以一个表对应一个物理文件，将 innodb_file_per_table 设置成 ON 即可。

表空间又划分成段，有数据段（ leaf node segment ），索引段（ none-leaf node segment ） , 回滚段 (rollback segment)

每段又划为成区， InnoDB 每次最多可以申请 4 个区，即 4M 的存储空间。

每个区又划分为页 ， 一个区划分成 64 页，每个页的大小是 16KB ，大小不能够改，这也固定了一个区的大小为 4M 。页是 MySQL 操作的最小逻辑单位。

InnoDB 是面向行的，这就意味着数据行存放在页中，每页最多能记录 7992 行数据。
MySQL 定义了不同作用的页类型，比如 B-Tree Page, Undo Log Page 等，我们最关心的

是 B-Tree Page( 数据页 ) 。实际数据就以这样的页逻辑实体存在于表空间 , 总是以 B+ 树结构索引组织的。

换句话就说，实际数据一行一行地存放在 B-Tree 页中，这些页都放在数据段 leaf node segment 中。 B-Tree Page 是 B+ 树的叶子节点 。

 

InnoDB 与 MyISAM 同样使用 B+Tree 作为索引结构，但具体存储方式却有很大差别。

1.              InnoDB 数据文件本身就是索引文件，按 B+Tree 组织，这个数的叶节点完整的保存了数据记录（这种索引就叫聚集索引），因此 InnoDB 表数据文件本身就是主索引。 MyISAM 的数据文件和索引文件是分离的，索引文件仅保存数据记录的地址。

2.              InnoDB 辅助索引叶节点存储的是相应记录主键值而不是地址值，这就解释了上面所说的“两遍索引查找”即：先检索辅助索引获得主键，再通过主键索引获得相应记录。这样做减轻了维护第二索引的负担，如数据行移动，或产生分页时不需要对辅助索引做额外的处理，当然这也使辅助索引文件变的很大（主键占用空间较多的话）。

InnoDB 主键插入优化

使用 InnoDB 存储引擎时，最好选择一个与业务无关的自增字段作为主键，从数据库优化的角度看这是必要的。这样不仅保证数据按顺序插入，还能在使用主键做连接（ join ）操作时有比较好的性能。

当使用自增主键，每次插入新数据时，记录就会顺序添加到当前索引后续位置，当一页写满，就会自动开辟新的一页，这样会形成一个紧凑的结构。而如果使用其他的，比如是随机的主键时，可能会插入到现有索引页的某个位置，此时 Mysql 不得不移动数据，这时可能目标页面已经被写回到磁盘上而从缓存中清除，此时又要从磁盘上读回，造成很大的开销，同时频繁的移动，分页造成大量碎片得到不够紧凑的索引结构，后续需要通过 optimize table 进行处理。

覆盖索引 (Covering Indexes)

Mysql 可以直接从索引中直接读取数据，而不必再到记录行中查找数据。

为排序使用索引扫描

Mysql 有两种方法获得排序结果：一种是使用 filesort ，另一种是顺序的扫描索引， EXPLAIN 中 type 的值为 index 说明使用了索引扫描。

只有在索引顺序与 order by 要排序的列的方向一致是，才能用到索引扫描排序。如果查询中使用了很多表，只有在 order by 子句中所有列引用的是第一个表中的列才可以使用索引扫描排序。

Order by 子句也有使用最左前缀的限制。不受最左前缀限制的特殊情况是 order by 前导列为常量。如 where 子句或 join 子句为这些列定义了常量。例：

Select rental_id from rental where rental_data=‘2011-7-26’ ORDER BY inventory_id AND customer_id 。索引为（ rental_data,inventory_id,customer_id ）

使用 join 时理论上 mysql 会使用索引排序，但实际上没有，可能是因为 mysql optimizer 将表连接的顺序改变了。

压缩索引（ packed (prefix-compressed)indexes ）

MyISAM 使用前缀压缩技术使索引变得更小。然而 InnoDB 并没有压缩索引，因为它的一些优化功能不能使用压缩的索引。

MyISAM 使用压缩索引来减少索引大小，以使更多索引可以进入内存，从而在有些情况下比较大的提高性能，它默认情况下会压缩字符串，不过也可以让它压缩整数。

MyISAM 在对索引块排序时，首先对第一个值进行全排列，然后记录所有相同前缀的字节数，加上不同的值作为后缀。例如第一个值为 perform ，第二个值为 performance 那么第二个值将被近似的存储为“ 7 ， ance ”。

在 create table 是可以用 pack_keys 控制压缩的方式。

压缩索引可以使用较少的空间，但也会使一些操作变的比较慢。因为每个值的压缩前缀依赖与它前面的值， MyISAM 不能再查找时使用二分查找，必须从头开始，顺序向前的性能尚可，但是反向扫描例如 order by desc 就不会很好的工作了。

索引和锁

索引在 InnoDB 中扮演着非常重要的角色，因为它可以使查询锁住更少的行，因为在 mysql5.0 中行在事务提交之前不会被解锁。

如果查询不去访问不需要的数据，那么他就会锁住比较少的行。

尽管 InnoDB 锁定行效率较高，内存使用较低，但还是要使用一些开销。锁定不需要的行会加剧锁竞争，降低并发性能。

InnoDB 只有在访问行的时候才会锁定它们，索引能够减少访问的行数，从而减少锁定。只有索引能在存储引擎级过滤掉不需要的行时才能起作用。如果不能那么在 InnoDB 取得行并返回给服务器级时再通过 where 来筛选不需要行时就不能避免锁定了。

在使用 explain 时如果 extra 中有 usering where 则说明使用了查询结果返回了服务器级进行了 where 条件过滤。

更新索引统计

    Mysql 查询优化器在决定如何使用索引时会调用两个 API 来了解索引如何分布， records_in_range() 它接受一个范围结束点，并返回这个范围内的记录数（估计），第二个是 info() 它返回不同类型的数据，包括索引的基数（每个键值有多少记录）。

当存储引擎没有向查询优化器提供查询检查行的精确数量的时候，优化器会使用索引统计来估计行的数量。统计可以通过 analyze table 重新生成。查询优化器主要的开销指标是查询要访问的数据数量。如果统计没能生成，或者已经过时那么查询优化器会做出不好的决定。解决方法是 analyze table 。

不同存储引擎实现索引统计是不同的：

Memory 不保存索引统计。

MyISAM 将索引统计保存在磁盘上， analyze 执行完整的索引扫描统计来计算基数，这个过程中整个表将被锁住。

InnoDB 不会把索引统计保存到磁盘上，在第一次打开表的时候利用随机索引进行估计。 InnoDB 上的 analyze table 使用随机索引来统计，因此 InnoDB 统计不够精确。除非服务器运行了很长时间，一般不用手动的去做更新操作。 InnoDB 在做 analyze table 操作时不是阻塞性的，因此可在服务器运行过程中进行在线更新。

可以使用 show index from table_name 查询索引的基数。 Cardinality 列表示存储引擎估计索引中唯一值的数量。

减少索引和数据碎片

碎片的产生：

删除数据必然会在数据文件中造成不连续的空白空间，而当插入数据时，这些空白空间则会被利用起来。于是造成了数据的存储位置不连续，以及物理存储顺序与理论上的排序顺序不同，这种是数据碎片。

实际上数据碎片分为两种，一种是单行数据碎片，另一种是多行数据碎片。前者的意思就是一行数据，被分成 N 个片段，存储在 N 个位置。后者的就是多行数据并未按照逻辑上的顺序排列。

为了消除碎片可以使用 optimize table 消除碎片。但这种方法对 InnoDB 是无效的，因为 InnoDB 不能通过排序建立索引，即使删掉并重新创建索引也可能会产生索引（这取决于数据）可以使用 alert table 来去除空白空间。

可以使用 show table status from table_name 中的 Date_free 是否大于 0 查看是否有碎片。

查询优化

SQL 执行过程 :

1.       客户端将 sql 传到服务器端。

2.       服务器端检查查询缓存，如果已经存在则返回缓存中的结果。如果不存在则将 sql 传到下一步。

3.       服务器解析 sql ，预处理，并形成执行计划。

4.       执行引擎调用存储引擎 api 执行查询。

5.       服务器端将结果返回给客户端。

JOIN 优化

在处理子查询时 mysql 会把 from 子句中的查询结果放到临时表中。然后当成像平常表一样做连接。 Mysql 在执行 union 时也会使用临时表，并且它会重写所有的 Right outer join 为等价的 left outer join.mysql 不支持全外连接 - 最外连接和右外连接的并集 (full outer join) 。

Mysql 连接优化器，可以通过对查询重新排序，来减小开销，对连接进行重新排序通常是一种有效的优化手段。但是 mysql 的重新排序又是得不到最有的效果，这是可以用 straight_join 来按照我们自定的顺序来排序，不过这种情况比较少见。

当需要连接的表比较多时，比如 n 个表就有 n 的阶乘中排序方案，当 n 的阶乘比较大时，一般超过 optimizer_search_depth 时 mysql 就会避开全部查看，而使用贪婪（ greedy ）搜索（？）。

排序优化

Mql 在进行排序时，如果 order by 子句中排序的字段都来自第一个表，那么 mysql 会先对第一个表做排序，然后进行连接。 Explain 时 extra 中就会显示 using filesort ，如果不是就会将结果放到临时表中，对临时表中数据进行排序。 Extra 中显示的是 using temporary using filesort 。如果有 limit 子句它将在排序完成之后再执行。

Count

Count 有两种工作方式，统计行的数量和值的数量。值是指非空的。若果 count() 括号中是某列的名字或其它表达式， count 统计的是值的数量。因此括号内表达式如果为空的话将不被加到 count 中。如果需要统计有多少行，则应使用 count(*) 。在 MyISAM 表中查询整个表的行数量是非常快的，因为存储引擎保存表中行的数量。但是如果加上 where 子句后就没有什么优势了。

使用下面方法分别统计数量：

SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items

或

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0))  AS red FROM items;

IF(expr1,expr2,expr3) 如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ，则 IF() 的返回值为 expr2; 否则返回值则为 expr3 。 IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

MYSQL 高级特性

缓存

表中的任何改变都会引起关于该表查询缓存的失效。

Mysql 会检查某个查询是否存在于缓存中。即使查询中有不确定的结果如 NOW() 函数等。因为检查是在解析查询语句前发生的，而只有解析后才能查看是否有不确定的结果。当然，如果会有不确定的结果，那么查询结果是不会保存在缓存中的。结果集太大也不会存到缓存中。

缓存设置 维护

query_cache_type ：值有 0 （ OFF ）无缓冲， 1 （ ON ）使用缓冲， 2 （ DEMAND ）根据需要使用。若不需要缓冲则可使用：

SELECT SQL_NO_CACHE * FROM my_table WHERE ... 

SQL_CACHE: 使用缓冲。

Query_cache_size: 默认值为 0. 设置时应是 1024bytes 的整数倍。

Query_cache_min_res_unit: 当查询结果需要缓存是要分配的最小内存数。

Query_cache_limit: 一次查询的结果大小超过这个值时就不缓存。因为服务器查询前不知道结果的大小，所以会结果会逐条缓存起来，因此，如果知道结果集很大查询时应加上 sql_no_cach. 如果大小查过了这个值时 Qcache_not_cached 的值会增加 1 。

query_cache_wlock_invalidate ：默认值为 OFF ，允许用户读取其它连接已锁定表缓存的数据。变为 ON 则，会阻止读取数据，但有可能增加锁等待。

碎片的产生：

当查询进行的时候， Mysql 把查询结果保存在 qurey cache 中，但如果要保存的结果比较大，超过 query_cache_min_res_unit 的值 ，这时候 mysql 将一边检索结果，一边进行保存结果，所以，有时候并不是把所有结果全部得到后再进行一次性保存，而是每次分配一块 query_cache_min_res_unit 大小的内存空间保存结果集，使用完后，接着再分配一个这样的块，如果还不不够，接着再分配一个块，依此类推，也就是说，有可能在一次查询中， mysql 要 进行多次内存分配的操作。当一块分配的内存没有完全使用时， MySQL 会把这块内存 Trim 掉，把没有使用的那部分归还以重复利用。比如，第一次分配 4KB, 只用了 3KB ，剩 1KB ，第二次连续操作，分配 4KB ，用了 2KB ，剩 2KB ，这两次连续操作共剩下的 1KB+2KB=3KB ，不足以做个一个内存单元分配， 这时候，内存碎片便产生了。

使用 flush_query_cache ，可以消除碎片。如果 Qcache_free_blocks 值过大，可能是 query_cache_min_res_unit 值过大，应该调小些。

可用 RESET QUERY CACHE 清除缓存。

Mysql 字符集

在 mysql 中，每一个层次的字符集默认都会继承上个层次的字符集，如创建一个新数据库时它的字符集继承自 character_set_sever ，创建表示继承数据库的字符集，创建列时继承表的字符集。如果某一层字符集使用自己设定的那么它不会受其上一层设置值的影响。

character_set_client ：服务器假定客户端发送的是该变量设定的字符集，

character_set_client ：当服务器从客户段接受到命令后，翻译为用该变量设置的字符集。

character_set_result ：服务器产生的结果或错误信息转换为该变量定义的字符集。