mysql随笔

1. 对于如何选择存储引擎：除非需要用到某些innoDB不具备的特性，并没有其他办法可以替代，否则都应该优先选择innoDB引擎。

2.innoDb的特性：可扩展能力和并发能力强，崩溃后数据可找回。占用空间相对较大。

3.日期和时间类型：通常也尽量使用timestamp,它比datetime空间效率更高。（若为时间戳，可存int类型，通常这样用）

高性能索引策略：

1.独立的列：索引不是表达式的一部分，也不能是函数的参数。

2.选择合适的索引列顺序：当不需要考虑排序和分组时，将选择性最高的列放到索引最前列。

3.用避免创建冗余索引和重复索引。

多列索引：

1）当服务器对多个索引做相交操作时（有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

2）当服务器需要对多个索引要做联合操作时（多个or条件），通常需要耗费大量cpu和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量的数据的时候。

查询性能优化：

1.一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：

   1）在索引中使用where条件来过滤不匹配的记录，这是存储引擎层完成的。

   2）在使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层完成的，但无需再回表查询记录。

   3）从数据表中返回数据，然后过滤不满足条件的记录。这是在mysql服务器层完成，mysql需要先从数据表读出记录然后过滤。

2. 如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

   1）使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。

   2）改变库表结构。例如使用单独的汇总表。

   3）重写这个复杂的查询，让mysql优化器能够已更优化的方式执行这个查询。

 3. 切分查询：有时候对于一个大查询我们需要“分而治之”，将大查询切分为小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分的查询结果。

 4. 分解关联查询：对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

    1）这样可以让缓存的效率更高。

    2）将查询分解后，执行单个查询可以减少锁的竞争。

    3）在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

    4）查询本身效率也可能会有所提升。

    5）可减少冗余记录的查询。

    6）这样相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。-

5. mysql执行一个查询的过程：

   1）客户端发送一条查询给服务器。

   2）服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

    3）服务器端进行sql解析、预处理，再由优化器生成对应的执行计划。

    4）mysql根据优化器生成的执行计划，调用存储引擎的api来执行查询。

    5）将结果返回给客户端。

6. mysql各个状态的含义：

   1）sleep : 线程正在等待客户端发送新的请求。

   2）query : 线程正在执行查询或者正在将结果发送给客户端。

   3）locked : 在mysql服务器层，该线程正在等待表锁。

   4）analyzing and statistics : 线程正在收集存储引擎的统计信息，并生成查询的执行计划。

   5) copying to tmp table [ on disk ] :  线程正在执行查询，并将其结果都复制到一个临时表中，这种状态一般要么是在做group by 操作，要么是文件排序操作，或者是union操作。如果这个状态后面还有"on disk"标记，那表示mysql正在将一个内存临时表放在磁盘上。

   6）sorting result:  线程正在对结果集进行排序。

   7）sending data : 这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

7. mysql可以处理的优化类型：

  1）重新定义关联表的顺序。

  2）将外连接转化成内连接。

  3）使用等价变换规则。

  4）优化count()、min()、max()。

  5） 预估并转化为常数表达式。

  6）覆盖索引扫码。

  7）子查询优化。

  8）提前终止查询。（例用limit）

  9）等值传播。

  10）列表in()的比较。（对于in()列表中有大量取值的时候，mysql的处理速度会很快）

8. 聚簇索引的优缺点：

  1）优点：可以把相关数据保存到一起，数据访问更快，且使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

  2）缺点：数据都放在内存中，访问顺序就没那么重要了。插入速度严重依赖插入顺序。