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)缺点:数据都放在内存中,访问顺序就没那么重要了。插入速度严重依赖插入顺序。