mysql 保存索引 create schema_mysql基础——引擎,索引,schema

前言：有些人由于缺乏对mysql的了解，使用上会出现一些误区：比如添加一个比较重要的字段就添加一个索引；比如认为颠倒语句的写法mysql就会跟着颠倒；也有些人就疯狂把业务逻辑塞到sql查询中，或者相反地，mysql把所有的数据查询出来在程序里面做类似于group by ,order by的操作。对于mysql的恐惧使用主要是因为缺乏对mysql的了解，其实mysql并没有想象的那么弱。

谈起mysql优化，有些人会立马想到语句优化，我之前也是这样，网上找了下explain了下就跟着做，却不知根本问题。后来才发现，了解了底层原理，上层的优化自然没有那么难了。

下面主要对引擎，索引和schema的个人认为重要的知识点做了汇总。大部分来自于《高性能mysql》，其中也摘抄了其他博客的一些讲解。更详细的还是推荐去看《高性能mysql》一书。

引擎

要了解mysql的优化就必须了解底层引擎的基本原理。其实mysql常用的引擎就是两种：innodb和myisam

innodb存储引擎

特点：

底层使用聚簇索引。聚簇索引对主键查询有很高的性能。不过其他二级索引中必须包含主键列。所以如果主键列很大的话，其他的所有索引都会很大。因此如果表上的索引比较多的话，主键应该尽可能小。关于聚簇索引之后会详细说明

InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Inter平台复制到PowderPC或者Sun SPARC平台。

*　作为事务型的存储引擎，innodb通过一些机制和工具支持真正的热备份。

myisam存储引擎

不支持事务和行级锁。

对整张表加锁而不是行。读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取查询的同时，也可以往表中插入新的记录

如果发现所有查询长期处于lock状态，则表锁就是罪魁祸首了

崩溃后无法完全恢复

适合只读，小表，可容忍修复操作时候使用

存储

会存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

选择合适的引擎

除非用到某些innodb不具备的特性，并且没有其他办法可以替代，否则都应该优先使用innodb引擎。

除非用到某些innodb不具备的特性，并且没有其他办法可以替代，否则都应该优先使用innodb引擎。

需要事务，innodb是目前最稳定而且经过验证的选择，如果不需要事务，且主要是select和insert操作，那么myisam是不错的选择。

innodb支持热备份

myism崩溃后损坏的概率比innodb高很多而且恢复速度也慢。

不要轻易相信“myisam比innodb快”之类的经验之谈，这个结论往往不是绝对的。在我们很多已知的场景中，innodb的速度都可以让myisam望尘莫及，尤其是使用到聚簇索引，或者需要访问的数据都可以放入内存的应用。

索引

对于索引这一章，有点难但是却是重中之重，这里是很多知识点都是mysql的精华。

索引的基本类型

B-TREE索引

BTREE

对于B树的简单入门，引用了http://www.ruanyifeng.com/blog/2014/07/database_implementation.html

要理解B树，必须从二叉查找树(Binary search tree)讲起。

二叉查找树是一种查找效率非常高的数据结构，它有三个特点。

(1)每个节点最多只有两个子树。

(2)左子树都为小于父节点的值，右子树都为大于父节点的值。

(3)在n个节点中找到目标值，一般只需要log(n)次比较。

二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。极端情况下，n个数据需要n次比较才能找到目标值。对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。

B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。

B树的特点也有三个。

(1)一个节点可以容纳多个值。比如上图中，最多的一个节点容纳了4个值。

(2)除非数据已经填满，否则不会增加新的层。也就是说，B树追求”层”越少越好。

(3)子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。比如上图中，父节点有两个值(7和16)，就对应三个子节点，第一个子节点都是小于7的值，最后一个子节点都是大于16的值，中间的子节点就是7和16之间的值。

这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。

为什么选择B树

之前一直有疑惑，数据库的数据是存放在磁盘的，却能有那么高效率的性能。如果每次查找数据都得去磁盘查找进行一次IO操作，那么无法想象多次获取数据的性能有多低。利用B树和局部热点的原理可以很好解决这个问题。

内存存取效率，跟次数有关，先读取A数据还是后读取A数据不会影响存取效率。而磁盘存取就不一样了，磁盘I/O涉及机械操作。磁盘是由大小相同且同轴的圆形盘片组成，磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不动，磁盘转动，但磁臂可以前后动，用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环(如图标红那圈)。磁道又划分为一个个小段，叫扇区，是磁盘的最小存储单元。

磁盘读取时，系统将数据逻辑地址传给磁盘，磁盘的控制电路会解析出物理地址，即哪个磁道哪个扇区。于是磁头需要前后移动到对应的磁道，消耗的时间叫寻道时间，然后磁盘旋转将对应的扇区转到磁头下，消耗的时间叫旋转时间。所以，适当的操作顺序和数据存放可以减少寻道时间和旋转时间。

来源： http://blog.csdn.net/tonyxf121/article/details/8393545

为了尽量减少I/O操作，磁盘读取每次都会预读，大小通常为页的整数倍。即使只需要读取一个字节，磁盘也会读取一页的数据(通常为4K)放入内存，内存与磁盘以页为单位交换数据。因为局部性原理认为，通常一个数据被用到，其附近的数据也会立马被用到。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页(在许多操作系统中，页得大小通常为4k)，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B- Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存)，渐进复杂度为O(h)=O(logmN)。一般实际应用中，m是非常大的数字，通常超过100，因此h非常小(通常不超过3)。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

来源： http://blog.csdn.net/l_griselda/article/details/8266014

由于读取是以页为单位，所以降低每一行数据就能使得页内塞入更多的行，在大量访问行的时候能明显减少IO次数。所以，降低行存储大小是多么重要。

那么，mysql行存储又取决于什么因素呢？

B-TREE索引

CREATE TABLEPeople(

last_name varchar(50)notnull,

first_name varchar(50)notnull,

dob datenotnull,

genderenum('m','f')notnull,

key(last_name,first_name,dob)

);

上表的结构大致如下：

下面是关于B-TREE索引的限制：

不是从最左列开始查找，则无法使用索引；

不能跳过索引中的列；

查询中某列是范围查询，则右边所有列都无法使用索引优化查找。

举个例子：

对表：

create table people(

last_name varchar(50)notnull,

first_name varchar(50)notnull,

dob datenotnull,

genderenum('m','f')notnull,

key(last_name,first_name,dob)

);

索引对如下类型的查询有效：

全值匹配：(last_name,first_name,dob)

匹配最左前缀：(last_name)

匹配列前缀：last_name like ‘j%’

匹配范围值：last_name between Allen and Barrymore

精确匹配某一列并返回匹配另外一列：last_name = KIM and last_name like ‘K%’，第一列全匹配，第二列范围匹配

只访问索引的查询：索引覆盖

其他索引

哈希索引

只有memory引擎才支持。

空间数据索引(R-TREE)：

全文索引

其他索引

索引的优点

大大减少服务器需要扫描的数据量

可以帮助服务器避免排序和临时表

可以把随机IO变为顺序IO

索引并不总是最好的工具，对于特大的表，还要利用分区技术等来优化查询

高性能的索引策略

前缀索引

多列索引

多个列上建立独立的单列索引大部分情况下并不能提高MYSQL的查询性能。 mysql5.0和更新版本引入一种叫索引合并的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的mysql只能使用其中一个单列索引，然而这种情况下没有哪一种独立的单列索引是非常有效的。

聚簇索引(Oracle叫索引组织表)

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-TREE索引和数据行。

innodb将通过主键聚集数据，如果没有定义主键，innodb会选择一个唯一的非空索引代替。如果没有这样的索引，则会隐式定义一个逐渐来作为聚簇索引。innodb只聚集在同一个页面中的记录。包含相邻键值的页面可能会相聚甚远。

聚集的数据有一些重要的优点：

1 可以把相关数据保存在一起。比如电子邮箱，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数数> 据也就能获得某个用户的全部邮件，避免每封邮件都要磁盘IO。(充分利用了上面讲到的B树特点)

2 数据访问更快。

3 使用覆盖索引扫描的查询可以直接使用页面中的主键值。

当然也有缺点：

聚簇数据最大限度地提高了I/o密集型应用的性能，但如果数据全部都放在内存中， 则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。

插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加栽完成后最好使用 OPTIMIZE TABLE命令重新组织一下表。(所以不能使用UUID作为主键)

更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位罝。

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临 “页分裂(page split)”的问题。当行的主键值要求必须将这一行插入到某个已满的 页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。 页分裂会导致表占用更多的磁盘空间。

聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存 储不连续的时候。这也是innodb使用count比较慢的原因。

二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了 引用行的主键列。

二级索引访问需要两次索引査找，而不是一次。顺便提一下，并不是所有的非聚簇索引都能做到一次索引查询就找到行。当行更新的时候可能无 法存储在原来的位置，这会导致表中出现行的碎片化或者移动行并在原位置保存“向前指针”，这 两种情况都会导致在查找行时需要更多的工作.

innodb和myisam的数据分布对比

myisam数据分布

MyISAM的数据分布非常简单，MyISAM按照数据插入的顺序存储在磁盘上。在行的旁边显示了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行(定长的吗？？)。这种分布方式很容易创建索引。并且，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为primary的唯一非空索引。如下图：

col2上建立索引和col2上建差不多，并没有什么区别。

innodb的数据分布(**)

因为Innodb存储数据如下：

这里感觉看上去和图5-5没有什么不同，但是仔细看会注意到这里显示的是整个表而不是只有索引。因为Innodb中，聚簇索引“就是”表，不想myisam那样需要独立的行存储。

聚簇索引的每一个叶子节点都包含了主键值，事务ID，事务回滚指针和所有的剩余列**，如果主键是列前缀索引，Innodb也会包含完整的主键列和剩下的其他列。

还有一点和myisam不同的是，Innodb二级索引和聚簇索引很不同。Innodb二级索引的叶子节点中存储的不是行指针，而是主键值，结合聚簇索引来搜索行数据。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。

二级索引如图：

下图容易看出Innodb和Myisam保存数据和索引的区别。

在Innodb表中按主键顺序插入行

使用整型索引

下文总结：最好避免随机的聚簇索引，特别是IO密集型的应用。对于特性应该结合B树的和预读原理来理解

举个例子，使用uuid作为聚簇索引会使得插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

这是由于当主键的值是顺序的，则InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的15/16，留出的部分空间用于以后修改)，下一条记录就会写入新的页中。一旦数据按照这样顺序的方式加载，主键页就会近似于被顺序的记录填满，这也是所期望的结果。

而当采用UUID的聚簇索引的表插入数据，因为新行的主键值不一定比之前的插入值大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置—-通常是已有数据的中间位置—-并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。

下面是总结的一些缺点：

写入目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O；

因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。

把这些随机值载入到聚簇索引以后，需要做一次optimize table来重建表并优化页的填充。

从这个案例可以看出，使用innodb时应该尽可能的按主键顺序插入数据，并且尽可能地使用单调增加的聚簇索引的值来插入新行。

顺序主键也有坏结果：对于高并发工作负载，在innodb中按主键顺序插入可能会造成明显的争用。

其实想想一下数据结构中的数组也就能感受到了。

索引覆盖(**)

如果一个索引包含所有需要查询的字段的值，我们就成为索引覆盖。索引覆盖只要扫描索引无需回表，会带来好处：

减少访问数据量：只访问了索引

对于IO密集型的范围查询会比随机从磁盘每行数据的IO要少得多。

一些存储引擎如myisam在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，用索引覆盖可较少之。

innodb的二级索引可以避免对主键索引的二次查询。

当发起一个索引覆盖查询时候，在explain的extra列可以看到using index 的信息。

很容易把extra的using index和type的index混淆。这两者完全不同，type列和索引覆盖毫无关系，它只是表示查询访问数据的方式，或者说是mysql查找行的方式。mysql手册中称之为连接方式(join type)

有些查询无法利用索引覆盖查询，可以用延迟关联的方式来利用索引覆盖。查询第一阶段mysql可以使用索引覆盖，在from子句的子查询中找到匹配的prod_id，然后根据这些id值在外层查询匹配需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法使用索引覆盖的好。

对于上面的优化效果取决于where条件匹配返回的行数。如果where过滤很多数据，那么优化效果会很好。

innodb二级索引叶子节点包含了主键的值，所以innodb的二级索引可以有效的利用这些主键来覆盖索引，要善于利用这“潜在的”索引。

使用索引扫描来排序 (*)

如果explain出来的type列的值为index，则说明mysql使用了索引扫描来排序(不要和extra列的using index 搞混了)

只有索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向一致，mysql才能用索引来对结果排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引排序。order by子句和查询型查找的限制是一样的：需要满足索引的最左前缀的要求.

有时候不满足最左前缀要求时候可以用前导列为常量来弥补不足。(这点和索引覆盖的技巧类似)

比如索引：

如果需要按不同方向排序，一个技巧是按存储列值的反转串或者相反数。

where和order by条件可以利用常量来组合形成最左前缀。

压缩(前缀压缩)索引

myisam能使用前缀压缩来减少索引大小，但是代价是变慢，正序扫描还行，但倒序扫描就不是很好了。

冗余和重复索引

重复索引是指在相同列上按相同类型的索引。这应该避免，发现了也该立即移除。比如：

冗余索引和重复索引有些不同。比如创建了(A,B) 在创建(A)就是冗余索引。而再创建(B,A)或(B)就不是了。

大多情况不需要冗余索引，但有时候会处于性能考虑需要它，因为扩展已有的索引会导致变得太大，从而影响其他索引查询性能。

索引和锁

索引可以让查询锁定更少的行。

innodb只有访问行才会对其加锁，但只有当innodb在存储引擎层能够过滤掉不需要的行时才有效。如果无法过滤掉无效的行，那么在innodb检索到数据并返回给服务层以后，mysql服务器才能应用where子句。

如果explain的extra列出现了using where，则表示mysql服务器将存储引擎返回行以后在应用where过滤条件。

使用索引的一些技巧

对于经常做范围查询的列，为了优化器使用尽可能多的索引列，最好放在列的最后，因为范围查询后面的索引无法利用。

范围条件和in对于使用explain来说都是range。但是这两种访问效率是不同的。对于范围条件，mysql无法在使用范围列后面的其他索引列，但是对于多个等值条件查询则没有这个限制，所以有了以上的优化方式。

优化排序

对于大量数据的排序，可以利用索引。

利用索引排序需要需要有limit。

另外对于order by limit 比较靠后的数据的时候，mysql需要花费大量时间扫描需要丢弃的数据。一个办法是限制用户翻页到这么后的页，实际上用户很少翻到最后。另一种优化方式是使用延迟关联。

维护索引和表

找到并修复损坏的表

可用check table来检查表是否发生损坏，用repair table命令来修复损坏的表，但不是所有引擎都支持。对不支持的，可以用不做任何操作的alter来重建表，比如：

alter table innodb_tbl engine=innodb;

当然也可以用其他工具数据导出后导入

如果innodb表损坏，那么一定发生严重错误，innodb一般不会出现损坏的。

更新索引统计信息

统计信息对于优化器的决定有很大的影响

直到mysql5.5，innodb也不再磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。它是通过抽样的方式来计算统计信息，首先随机的读取少量索引页面，然后以此为样本计算索引的统计信息。

每种存储引擎实现索引统计信息的方式不同，可以用analyze table来重新生成统计信息。这需要全索引扫描来计算索引基数，在整个过程中需要锁表。

减少索引和数据的碎片

B树索引可能会碎片化，降低查询效率。如果B树需要随机磁盘访问才能定位到叶子页，所以随机访问时不可避免的。然而如果叶子页在物理分布上是顺序而且紧密的，那么查询性能会更好。否则对于范围查询和索引覆盖等操作来说，速度可能会降低很多倍，对于索引覆盖扫描更加明显。

碎片包括三种：

碎片化—这种碎片指的是数据行被存储为多个片段中。即使查询也从索引中访问一行记录，碎片也会导致性能下降。

行间碎片—行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。

剩余空间碎片—剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费

总结

大多数索引用B-TREE来实现。

1、单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

2、按顺序访问范围数据是很快，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O快很多(特别是机械硬盘)。第二，如果服务器能够按需要顺序读取数据，那么就不需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按组进行聚合计算了。

3、索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列 ，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第一点已经写明单行访问是很慢的。

总的来说，编写查询语句时应该尽可能选择合适索引避免单行查找，尽可能用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖扫描。

如何判断索引是否合理呢？一般来说，可以按相应时间来对查询进行分析。找出耗时最长的查询或者给服务器带来最大压力的查询，然后检查这些查询的schema，sql和索引结构，判断是否有查询了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机IO访问数据，或者有太多回表查询那些不在索引中的列的操作。

schema与数据类型的优化

原则

更小通常更好

简单就好：比如IP不要用字符串而是整型存储

尽量避免null

整型类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别用8,16,24,32,64

mysql可以指定宽度，比如INT(11)，但对大多数应用没什么意义：它不会限制值的合法方位，只是规定了mysql的一些交互工具，用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

一个通常的误区就是INT后面的位数

实数类型

float和double类型支持标准浮点运算进行近似计算。decimal类型用于存储精确的小数。

有多种方法可以指定浮点列所需要的精度，这会使得mysql悄悄选择不同的数据类型，或者在存储时对值进行取舍，这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。

浮点类型存储同样范围的值时，通常比decimal使用更少的空间，float使用4个字节存储。double占用8个字节，相比float有更高的精度和更大的范围。

因为需要额外空间和计算开销，所以应尽量只对小数进行精确计算时才使用decimal——比如存储财务数据。

字符串类型

varchar：

存储变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为他需要必要的空间。

它需要使用1或2个额外字节记录字符串的长度

由于变长的，在update时可能使行变得比原来更长，这导致需要额外的工作。当页没有更多空间可以存储，myisam会把行拆成不同的片段存储，innodb则需要分裂页来操作。

varchar(5)和varchar(200)存储hello开销一样，使用更短的列有什么优势呢？

mysql通常会分配固定大小内存块来保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕，在利用磁盘临时表进行排序时也同样糟糕。所以最好是分配真正需要的空间。

所以说，varchar确实能省空间，但是不能分配过于慷慨；另外如果是经常update也可能会带来额外开销。

char：

定长：当存储char值时，mysql会删除所有末尾空格。

blob和text类型

mysql把每个blob和text值当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理。当blob和text值太时，innodb会用专门的外部存储区域来存储，此时每个值在行内需要1-4个字节存储指针，然后再外部存储区域存储实际的值。

blob和text家族之间仅有的不仅是blob类型存储的是二进制数据，没有排序规则或字符集，而text类型有字符集和排序规则。

磁盘临时表和文件排序：

对blob或text排序会使用myisam磁盘临时表，会有严重性能开销，所以尽量避免使用。如果实在无法避免，可用substring将列转为字符串，就可以用内存临时表了。

日期和时间类型(需要看书补充)

mysql最小时间粒度是秒。不过也可以用微妙级别的粒度进行临时运算，比如使用bigint存储，然后存储时间戳，或者用double存储秒后的小数。

datetime

存储1001到9999年，精度为妙。它把日期封装格式为YYYYMMDDHHMMSS的整数中和时区无关，8字节存储空间。

timestamp

存储UNIX时间戳，只用4字节存储，存储范围比datetime小很多，只有1970到2038年。

通常尽量使用timestamp，因为他比datetime空间效率更高，有时候人们会存储UNIX时间戳，但是不会带来任何利益，而且不方便处理，所以不推荐这么做。

位数据类型

bit,set

mysql有少数几种存储类型使用紧凑的位存储数据。所有这些类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

选择标识符

最好使用整数作为标识符。

最好避免字符串作为标识符，因为他们很消耗空间，并且通常比数字类型慢。myisam默认还对字符串使用压缩索引，这会导致查询慢得多。

对于完全随机的字符串也要多加注意，比如MD5,SHA,或者UUID产生的字符串。这些函数生成的新值会任意分布在很大的空间内，会导致insert 和一些select语句变得很慢：

因为插入值会随机地写到索引的不同位置，所以使得INSERT语句更慢。这会导

致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚族索引碎片。

SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方

随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作 的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分 特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不 命中。

如果存储UUID值，则应该移除符号，或者更好的做法是，用UNHEX()函数转换 UU1D值为16字节的数字，并且存储在一个BINARY(16>列中。检索时可以通过HEXO 函数来格式化为十六进制格式。

UUID生成的值与加密散列函数例如SHA1生成的值有不同的特征：UUID值虽然分 布也不均匀，伹还是有一定顺序的。尽管如此，但还是不如递增的整数好用。

对于自动生成的schema，比如ORM框架生成的，要注意有没性能问题

mysql schema设计中的陷阱

太多的列

简单说，mysql从服务器层将缓冲内容解码成各个列需要时间。myisam定长行结构和服务器层的行结构正好匹配，所以不需要转换，但是myisam的变长行结构和Innodb的行结构总是需要转换。转换代价依赖于列的数量。

太多的关联

过度使用枚举

范式和反范式

两者的优缺点就不说了。

要混合使用范式和反范式。