高性能MySQL读书笔记。。。
在处理并发读或者写时,通过实现一个由两种类型的锁组成的锁系统来解决问题。
2.1 表锁 是最基本的锁策略,开销最小的策略。
2.2 行级锁 可以最大成都的支持并发处理(最大的锁开销).
事物就是一组原子性的sql,或者说是一个独立的工作单元。
ACID的概念:
在sql标准中定义了四种隔离级别
默认引擎为InnoDB,一般没有特殊需求直接使用即可。
优点:
缺点
优点:
缺点
完全的范式和反范式是不存在的,在实际操作中建议混用这两种策略,可能使用部分范式化的schema、缓存表、以及其他技巧。
实际上是预计计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新
如果应用在表中保存计算器,则在更新计数器时可能碰到并发问题,创建一个独立的表存储计数器通常是个好主意,这样可以使计数器表小且快,使用独立的表可以帮助避免查询缓存失效。
myql执行大部分修改表结构的操作方法是用一个新的结构创建一个空表,从旧表查询中所有数据插入新表,然后删除旧表
不是所有的ALTER TABLE操作都会引起表重建,有两种方式:
如果没有特别说明,一般说的都是B-Tree索引,它使用B-Tree数据结构来存储数据。
基于哈希表实现,只有精确匹配索引所有列才有效。 在mysql中只有Memory引擎显示支持哈希索引
MyISAM表支持空间索引,可以用作地理数据存储,和b-tree索引不同,这类索引无须前缀查询,空间索引会从所有维度来所有数据。
查找的是文本中的关键词,而不是直接比较索引中的值
索引可以让服务器快速定位到表的指定位置,但这不是索引的唯一作用。
索引的三个优点
如果查询中的列不是独立的,则mysql就不会使用索引
一个常见的错误就是,为每个列创建独立的索引
在多个列上建立索引大部分情况下并不能提高mysql的查询性能,mysql5.0之后的版本引入了一种“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。
索引合并有时候是一种优化的结构,但实际上更多时候说明了表上的索引建的很糟糕:
正确的顺序依赖于使用该索引的查询,并且需要同事考虑如何更好的满足排序和分组的需要。
在一个多列的B-Tree索引中,索引列的顺序意味着索引首先按照从左到右进行排序,所以索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
当不考虑排序和分组时,将选择性最高的列防在前面通常是很好的。然而性能不只是依赖于所有索引列的选择性,也和查询条件的具体值有关,也就是和值的分布有关。
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时,数据行实际上存放在索引的叶子页中。InnoDB将通过主键聚集数据,如果没有定义主键,会选择一个唯一的非空索引来替代,如果没有这样的索引,会隐式定义一个主键来作为聚簇索引。
优点:
缺点:
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为”覆盖索引”
如果查询只需要扫描索引而无须回表,能带来许多好处
MySQL有两种方式可以生成有序的结果,通过排序操作,或者按照索引顺序扫描;如果EXPLAIN出来的type列的值为“index”,则说明使用了索引扫描来做排序
MySQL可以使用同一个索引既满足排序又用于查找行,所以,如果可能,设计索引时应该尽可能的满足这两种任务
只有当索引列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,才能使用索引来对结构做排序。如果需要关联多表,则当只有ORDER BY子句引用的字段全部为第一个表时,才能使用。
MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以防到内存中,在某些情况下可以极大提高性能。
有效的分析方法:
在确定查询只返回需要的数据后,接下来应该看看为了返回结果是否扫描了过多的数据
对于MySQL,简单的衡量查询开销的指标如下:
响应时间=服务时间+排队时间
理想情况下扫描的行数和返回的行数应该时相同的,但实际上基本不可能。扫描的行数对返回的行数的比率通常很小,一般在1:1和10:1之间。
在评估查询开销的时候,需要考虑一下从表中找到某一行的数据的成本。
在EXPLAIN语句中的type列反应了访问类型,如果查询没有办法找到合适的访问类型,那么最好的解决办法通常就是增加一个合适的索引
一般MySQL能够使用如下方式应用where条件,从好到坏依次为:
在优化有问题的查询时,目标应该是找到一个更优的方式获得实际需要的结果,而不是一定总是需要从MySQL获取一样的结果集。
设计查询的时候一个需要考虑的问题,是否需要将一个复杂的查询分成多个简单的查询。
在MySQL从设计上让连接和断开都很轻量,在返回一个小的查询结果方面很高效。
在其他条件都相同的时候,使用尽可能少的查询当然时好的,但是有时候,将一个大查询分解为多个小查询时很有必要的。在设计应用时,如果也过查询能够胜任时还写成多个独立的查询时不明智的。
有时候我们需要对大查询分而治之,将大查询切分为小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果
删除旧数据就是一个很好的例子,定期清除大量数据时如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据,占满整个失误日志,耗尽系统资源,阻塞很多小的但是重要的查询。
对关联查询进行分解。简单的,可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。
通过分解关联查询的方式有如下的好处:
有两种不同的作用:
count(*)全表非常快,可以来加速一些特定条件的count查询
比如查询所有id大于5的城市
select count(*) from city where id >5
通过反转可以将扫描行数减少到5行之内
select (select count(*) form city) - count(*) from city where id <=5
有些业务场景并不要求完全精确count值,此时可以用近似值来代替,EXPLAIN出来的优化器估算的行数就是一个不粗的近似值,执行explain并不需要真正的去执行查询
在mysql层面还能做的就只有覆盖索引扫描,如果还不够,就需要考虑修改应用的架构,可以增加汇总表,或者增加外部缓存系统
子查询尽可能使用关联查询代替
使用索引优化是最有效的优化方法
在mysql中,当无法使用索引的时候,group by使用两种策略来完成:使用临时表或者文件排序来左分组
如果需要对关联查询左分组,并且是按照查找表中的某个列进行分组,那么通常采用查找表的标识列分组的效率会比其他列更好
分组查询的一个变种就是要求mysql对返回的分组结果在做一次超级聚合,很多时候建议在应用中实现超级聚合
优化分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描,而不是查询所有的列,然后根据需要左一次关联操作在返回所需的列,对于偏移量很大的时候,这样做的效率提升非常大
比如
select file_id,description from file order by title limit 50,5
最好改写成以下语句
select file_id,description
from file
inner join (
select file_id
from file
order by title
limit 50,5
)as lim using(file_id)
这里的延迟关联将会大大提升查询效率,它让mysql扫描尽可能少的页面,获取需要访问的记录了后在根据关联列在回到原表查询需要的所有列
分页的时候,另一个常用的技巧时在limit语句中加上sql_calc_found_rows提示(hint),这样就可以获得去掉limit以后的满足条件的行数,因此可以作为分页的总数
mysql总是通过创建并填充临时表的方式来执行union查询,因此很多优化策略在union查询中都无法使用。
除非确实需要服务器消除重复的行,否则就一定要使用union all,如果没有all关键字,mysql会给临时表加上distinct选项,这回导致对整个临时表的数据做唯一性检查
在以下场景不能使用用户自定义变量
注: