Schema设计优化和查询性能优化

高性能mysql总结
 Schema设计优化和查询性能优化

schema优化包括数据类型优化，索引优化。

1.1数据类型优化 BIT类型（位数据类型）

   Alter table操作，只修改.frm操作

   禁启用索引：

 alter table tablename disable keys;

alter table tablename enable keys;

1.2 索引优化:

     索引分为以下几类：B-Tree索引，哈希索引，R-Tree索引，全文索引等。

   B-Tree索引的查询类型：全值匹配；匹配最左前缀；匹配列前缀；匹配范围值；

精确匹配某一列并范围匹配另外一列。

哈希索引无法用于排序，不支持部分索引列匹配查找。

只支持等值比较查询。

R-tree:空间数据索引

2 查询性能优化

查询优化的目的是减少响应的时间，查询是有一系列的子任务组成，减少子任务的响应时间。

查询开销的三个指标： 响应时间，扫描行数，返回行数。

响应时间包括等待时间和执行时间。等待时间通常是由于I/O争用或者锁。

扫描行数和返回行数：通过explain解释查看访问（扫描）数据。使用where筛选返回数据。所使用的方法：索引覆盖扫描，改变库表结构或者重新写查询。

返回线程状态：show full processlist

Command代表当前查询的状态，有以下参数：

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

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

Locked：等待表锁。

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

Copying to tmp table[on dist]:线程正在执行查询，并将结果集复制到一个临时表。Group by,文件排训，Union

Sorting result:线程正在对结果集进行排序。

查询优化器的成本：show status like ‘Last_query_cost’;

 

查询优化器的作用主要是生成执行计划。执行计划失效的原因有以下几类：

1.       统计信息不准确，

2.       执行计划中的成本估算不同于实际执行成本。

3.       优化不一定是时间最少。

4.       不考虑并发执行查询。Mysql有固定的规则。Match()全文搜索子句。

查询优化类型

1.       重新定义关联表的顺序。Mysql按照嵌套循环来执行查询。

2.       将外连接转化成内连接

3.       使用等价变换规则

4.       优化count(),min(),max()

5.       覆盖索引扫描

6.       子查询优化

7.       提前终止查询 limit子句。

8.       等值传播

9.       列表IN（）的比较。

查询优化器的提示(hint)

  HIGH PRIORITY和LOW PRIORITY 访问同一个表的优先级。

  Delayed：insert和replace有效，插入的数据先放入缓冲区。

  STRAIGHT_JOIN ：让表按照出现的顺序关联

  Sql_small_result和sql_big_result：对group by 和distinct的结果大小预测，小就将临时表放入缓存，大就放入磁盘

  Sql_buffer_result ;告诉优化器将结果放入临时表

  Sql_cache和sql_no_cache；是否应该缓存在查询缓存中

  Sql_calc_found_rows=found_row()不告诉优化器关于执行计划，但是包含更多的信息。

  For update和Lock in share mode 对行加锁。

  Use_index,ignore_index和force_index 使用还是不使用索引。