mysql 笔记

转自：http://snailxr.iteye.com/blog/1143508

Mysql中的Null

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

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

Select id fromuser 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在进行一些内部操作的时候会临时创建一些数据库表。这些操作主要包括groupby，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子句为这些列定义了常量。例：

Selectrental_id from rental where rental_data=‘2011-7-26’ ORDER BY inventory_id ANDcustomer_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中有useringwhere则说明使用了查询结果返回了服务器级进行了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 fromtable_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在进行排序时，如果orderby 子句中排序的字段都来自第一个表，那么mysql会先对第一个表做排序，然后进行连接。Explain时extra中就会显示usingfilesort，如果不是就会将结果放到临时表中，对临时表中数据进行排序。Extra中显示的是usingtemporary using filesort。如果有limit子句它将在排序完成之后再执行。

Count

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

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

SELECTCOUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROMitems

或

SELECTSUM(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：服务器产生的结果或错误信息转换为该变量定义的字符集。