本文共 7158 字，大约阅读时间需要 23 分钟。

1、宽表还是窄表？怎么做选择？一张表多大合适？

    宽表

字段比较多的表，包含的维度层次比较多，造成冗余也比较多，毁范式设计，但是利于取数统计。适合做数据仓库、大数据等

    窄表

往往对于OLTP比较合适，符合范式设计原则；就性能角度来讲，一般窄表优于宽表，而且窄表逻辑更加清晰。所以一般推荐用面向业务一般用窄表来实现。

那么究竟一张表多少字段合适呢？有没有一个界限来判断宽表还是窄表呢？

mysql的物理存储结构如下：

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。

mysql物理存储的结构，由段-区-页-行组成：

一个区是1M大小，由连续的64个16k的页组成，每个页又由N行组成。

在mysql内存加载过程中，数据加载的最小单位是页。所以每个页中存储的行越多，则数据加载的页会越少，查找性能越高。

假设一页16k=160行，则一行=100字节，100字节=10个字段=>1个字段=10字节，所以这里看下1行存储10个10字节的字段，这样一页能存储160行数据。所以到这里，可以根据实际的业务场景预估你单表所需要的实际字段数量及每个字段的长度，合理进行字段设计；数据量越大越不适合宽表设计，字段应该尽可能的少。对于互联网公司来说，基本是要告别宽表，使用者窄表，一方面是为了性能，一方面也是方便了维护。

innodb关于数据行大小的限制

我们知道innodb的页块大小默认为16kb，表中数据是存放在B-tree node的页块中，但如果表中一行的数据长度超过了16k，这时候就会出现行溢出，溢出的行是存放在另外的地方，存放该溢出数据的页叫uncompresse blob page。

innodb采用聚簇索引的方式把数据存放起来，即B+树结构，因此每个页块中至少有两行数据，否则就失去了B+树的意义（每一个页中只有一条数据，整个树成为了一条双向链表），这样就得出了一行数据的最大长度就限制为了8k。

    当插入的一行数据不能在一个数据页块中存放时，为了保证该页至少能存放两行数据，innodb将会自动部分数据溢出到另外页中，一部分数据将存放在数据页块中，其大小为该列的前768字节，同时接着还有偏移指向溢出页。

    如上面所说大字段的前768字节会存放在数据页块中，那么如果有10个大字段（如varchar（1000），text，blob同varchar同样存储前768字节），同样会超过一行数据8k的限制（10*768<8000,11*768>8000）。如果插入的值超过8000字节，则会报错（BLOB或TEXT同理）

                                                                                                   innodb的表数据结构（B+树）

回顾一下Innodb是如何查找数据的？

要查找一条数据，怎么查？

如 select * from user where id=5;

这里 id 是主键，我们通过这棵 B+ 树来查找，首先找到根页（每个页都有一个编号），你怎么知道 user 表的根页在哪呢？

其实每张表的根页位置在表空间文件中是固定的，即 page number=3 的页，找到根页后通过二分查找法，定位到 id=5 的数据应该在指针 P5 指向的页中，那么进一步去 page number=5 的页中查找，同样通过二分查询法即可找到 id=5 的记录

总结一下：

1、InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值 + 指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值 + 指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

如果每个节点就一个数据，那么整个叶节点就是一个双向链表（图中是单向链表，实际是双向的）了，就失去了树的意义。

2、SQL优化

sql的优化的关键是是要确定优化的方向，也就是定位性能的缺陷：

一、分析SQL语句的执行计划

什么是执行计划呢？

简单来说，就是SQL在数据库中执行时的表现情况，通常用于SQL性能分析、优化等场景。

MySQL逻辑架构分为三层，如下图：

客户端：

    如，连接处理、授权认证、安全等功能；

核心服务：

    MySQL大多数核心服务均在这一层，包括查询解析、分析、优化、缓存、内置函数（如，时间、数学、加密等），所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等；

存储引擎

    负责MySQL中的数据存储和读取中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异；

查询缓存：对于select语句，MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会like返回结果，跳过了解析、优化和执行截断。MySQL查询缓存保存查询返回的完整结果。并不是什么情况下查询缓存都会提高系统性能的。缓存和失效都会带来额外的消耗，所以只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。这根具体的服务器压力模型有关。关于是否开启查询详见这篇文章：

注意 ：1. 查询缓存不返回旧的数据。当表更改后，查询缓存值的相关条目被清空。

              2. 如果你有许多mysql的 服务器更新相同的MyISAM 表，在这种情况下查询缓存不起作用。

              3. 查询缓存不适用于服务器方编写的语句。

explain

显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：如：explain select * from test1

EXPLAIN列的解释：

table：

• 显示这一行的数据是关于哪张表的

type：

• 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。

各个连接的含义：

const，通过索引一次命中，匹配一行数据；

eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用语主键或唯一索引扫描；

ref，非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列；

range，只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；

index，只遍历索引树；

all，全表扫描；

system，表中只有一行记录，相当于系统表；

• 通常优化至少到range级别，最好能优化到ref

possible_keys：

• 指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能；

key：

• 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：

• 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：

• 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：

• MYSQL认为必须检查的用来返回请求数据的行数

Extra：

• 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。
• using filesort，MySQL会对数据使用一个外部索引排序，而不是按照表内索引顺序进行读取，若出现改值，则应优化SQL语句；

  using temporary，使用临时表缓存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by，若出现该值，则应优化SQL；

  using index，表示select操作使用了覆盖索引，避免了回表查询；简单来说：使用到了索引 , 并且所取的数据完全在索引中就能拿到

  using where，where子句用于限制哪一行；简单来说：表示使用到了索引 , 但是也进行了where过滤

  using join buffer，使用连接缓存；

  distinct，发现第一个匹配后，停止为当前的行组合搜索更多的行；

二：show profiles

定义：显示sql执行过程中各个环节的消耗情况，例如cpu使用情况，打开表、检查权限、执行优化器、返回数据等分别用了多长时间，可以分析语句执行慢的瓶颈在哪。如果要使用这个命令首先要设置profiling为on，mysql默认设置为off;

1、查看当前profiling的值：

      select @@profiling

2、设置打开profiling

      set profiling =1;

      or       set profiling=on;

3、设置关闭profiling

      set profiling=0;

      or       set profiling=off;

4、show profiles 默认显示最近15条的sql执行情况，15这个数字由profiling_history_size常量决定，你可以配置为0到100的数字最          大支持100，如果设置为0则类似于关闭profiling选项。除了show profile 和show profiles 两个命令不会被记录之外，其余的语句        都会被记录，即使是语法错误的sql也会被记录。show profile 命令默认获取最新一条执行的sql的消耗分析，如果想指定获取某        一条sql的profile 用如下命令：

5、show profiles 先获取要分析的query_id，然后执行show profile for query query_id;

      show profiles以及show profile 命令同时也支持 limit语句。show profile 命令 默认只显示Duration 列总消耗时间，如果要显示更        多可以设置 all参数

6、show profile [type];

type 参数支持如下：

| ALL ：表示所有的列 | BLOCK IO ：数据块的输出出入操作次数 | CONTEXT SWITCHES ： 上下文切换次数 | CPU：cpu使用时长 包括系统时长和用使用时长 | IPC ：发送和接收数据的 次数 | PAGE FAULTS：重要的和次要的错误次数 | SOURCE：执行相应操作的mysql源文件，包括源文件方法名，地址、所在行； | SWAPS：swap次数

提示：实际上这些数据全都存在mysql的表里，你可以执行如下查询一样可以获取相同的信息。

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = query_id;

二、SQL语句

避免全表扫描：尽量避免用in、not in、is null、or、like、左值运算、函数运算

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。    

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null  可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：     select id from t where num=0   

3、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    

4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：     select id from t where num=10 or num=20     可以这样查询：     select id from t where num=10     union all     select id from t where num=20

5、in 和 not in 也要慎用，否则会导致全表扫描，如：    

select id from t where num in(1,2,3)     对于连续的数值，能用 between 就不要用 in 了：     select id from t where num between 1 and 3  

6、下面的查询也将导致全表扫描：    

select id from t where name like '%abc%'    

7、7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    

select id from t where num/2=100     应改为:     select id from t where num=100*2      8、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：     select id from t where substring(name,1,3)='abc'--name以abc开头的id     应改为:     select id from t where name like 'abc%'    

9、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11、 不要写一些没有意义的查询，如需要生成一个空表结构：    

select col1,col2 into #t from t where 1=0     这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：     create table #t(...)          12、很多时候用 exists 代替 in 是一个好的选择：     select num from a where num in(select num from b)     用下面的语句替换：     select num from a where exists(select 1 from b where num=a.num)  

13、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。    

14、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，    

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。     一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。    

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

16、尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，    

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 

17、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

18、避免频繁创建和删除临时表，以减少系统表资源的消耗

19、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

20、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，    

以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 

22、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

23、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25、尽量避免大事务操作，提高系统并发能力；

26、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

27、使用复合索引

28、避免索引失效

29、避免回表查询

30、不要索引上左任何运算操作

31、小表驱动达标。小表查询条件放在where子句的左边

转载地址：http://mweof.baihongyu.com/