MySql 基础到进阶优化
-
读写锁
读锁是共享的,或者说是互不阻塞,多个客户端在同一时刻可以读取同一个资源,写锁是排他的一个写锁会阻塞其他的写锁和读锁.
-
表锁
表锁是mysql中最基本的锁策略,并且是开销最小的策略.
-
行级锁
行级锁可以支持最大程度的并发处理,同时也带来了最大的锁开销.
-
事务
ACID 原子性,一致性,隔离性,持久性
- 原子性 - 一个事物必须被视为一个不可分割的最小工作单元,整个事务中所有操作要么全部提交成功,要么全部失败回滚,对于一个事物来说,不可能只执行其中的以部分操作,这就是事物的原子性.
- 一致性 - 数据库总是从一个一致性的状态转换到另外一个一致性的状态.
- 隔离性 - 通常来说,一个事物所做的修改在最终提交以前,对其他事物是不可见的.
- 持久性 - 一旦事物提交,所做的修改就会永久保存到数据库中.
-
隔离级别
- read uncommitted (未提交读) 在read uncommitted级别,事物中的修改,即使没有提交,对其他事物也是可见的.事物可以读取未提交的数据,也被称为脏读(Dirty Read).
- read committed (提交读) 大多数数据库系统默认的隔离级别都是read committed(但是mysql不是).read committed 满足隔离性的简单定义: 一个事物开始时,只能看见已经提交的事物所做的修改.一个事物从开始直到提交之前,所做的任何修改对其他事物都是不可见的,这个级别的也叫做不可重复读(nonrepeatable),因为执行两次同样的查询,可能获得的结果不一样
-
repeatable read(重读)
- 它解决了脏读问题.该级别保证了同一个事物中多次读取同样记录的结果是一致的.它是mysql默认的隔离级别.但是这个还是无法解幻读(phantom read)的问题.所谓幻读,就是指当某个事物在读取某个范围内的记录时,另外一个事物又在该范围内插入了新的记录,当之前的事物在此读取该范围的记录时,会产生幻行.
-
serializable
- serializable 是最高级别的隔离级别.它通过强制事物串行执行,避免了前面说的幻读.简单来说,serializable会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题.
-死锁
死锁是指两个或者多个事物在统一资源上相互占用,并请求锁定对方占用的资源,从而导致的恶性循环的现象.当多个事物视图以不同的顺序锁定资源时,就可能会产生死锁的情况.
mysql 中的事物
mysql中提供了两种事务型的存储引擎: InnoDB 和 NDB Cluster
-
自动提交(autocommit)
mysql默认采用自动提交模式.也就是说,如果不是显式地开始一个事物,则每个查询都被当作一个事物执行提交操作.在当前连接中,可以通过设置autocommit变量来启用或者禁用自动化个提交模式
选择优化的数据类型
-
更小的更好
一般情况下,应该尽量使用可以正确存储数据的最小类型
-
简单就好
简单的数据类型付出的代价更低
- 尽量避免NULL
NULL的列使索引,索引统计和值比较都更为复杂
MySql为了兼容性支持很多别名,例如integer,bool,numeric.其实它们都是基本类型
整数类型
有tinyint,smallint,mediumint,int,bigint.
MySql可以为整数类型指定宽度,但是它不会限制合法范围,对于存储和计算来说,INT(1)和INT(20)是相同的
实数类型
实数是带有小数部分的数字
例如在财务存储中,数据量较大时,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可.假设存储财务数据要精确到万分之一,则可以把所有的金额乘以一百万,然后将结果存储在BIGINT里,同时避免了浮点存储计算不精准和DECIMAL精确计算但是代价高的问题
字符串类型
VARCHAR类型用于存储可变长字符串
CHAR类型是定长的
BLOB 和 TEXT 类型
两者都是为存储很大的数据而设计的字符串类型数据,分别采用二进制和字符方式存储
最好的解决方案是尽量避免使用,如果实在无法避免,有一个技巧实在所有用到BLOB字段的地方都使用SUBSTRING(colum,length)将列值转换为字符串(在 ORDER BY 子句中也适用)
日期和时间类型
Mysql能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型),MySql也同样可以使用微秒来进行临时运算.
-
DATETIME
这个类型能保存的最大范围值,从1001年到9999年,精度为秒,它把日期和时间封装格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节存储空间 -
TIMESTAMP
它保存了从1970年1月1日午夜以来的秒数.只使用了4个字节
如果想使用更小级别的时间值,可以使用BIGINT来存储时间戳
UUID
如果存储UUID赢移除-符号,用UNHEX()转换UUID为16字节的数字,并且存储在BINARY(16)的列中.检索时可以通过HEX()函数来格式化为十六进制格式
特殊类型数据
例如IPv4地址,可以使用无符号整数存储,MySql提供INET_ATON()和INET_NTOA()函数来转换
MyS schema设计中的陷阱
-
太多的列
-
太多的关联
MySql限制了每个关联操作最多只能有61张表,单个查询最好在12个表以内
- 避免用 NULL
可以使用0,某个字符串,或者空字符串作为代替
- 更快的读,更慢的写
为了提升读查询的速度,经常会使用一些额外索引,增加冗余列,甚至是创建缓存表和汇总表,这些方法会增加写查询的负担,也需要额外的维护任务,但在设计高性能数据库时,都是常见技巧
快速创建MyISAM索引
为了高效载入数据到MyISAM表中,常用技巧是先禁用索引,载入数据,然后重新启用索引
ALTER TABLE test.load_data DISABLE KEYS;
-- load data
ALTER TABLE test.load_data ENABLE KEYS;
该技巧对唯一索引无效
创建高性能索引
MySql只能高效实用索引的最左前缀列
索引类型
B-Tree索引
B-Tree 意味着通常所用的值都是按顺序存储的,并且每一个叶子页到根的距离相同,B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始搜索.
B-Tree对索引列使顺序组织存储的,所有很适合查找范围数据
B-Tree索引适用于全键值,键值范围或者键前缀查找
- 如果不是按照索引最左开始查找.则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找
全值匹配指的是和索引中的所有的列进行匹配
哈希索引
哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才能有效.
对于每一行数据,存储引擎都会对所有的索引计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样.哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针
在MySql中,只有Memory引擎显式支持哈希索引,这也是Memory引擎表的默认索引类型
- 哈希索引质保函哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行.
- 哈希索引数据并不是按照索引值顺序存储的,所以无法用于排序
- 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的
- 哈希索引只支持等值比较查询,包括
=IN(),也不支持任何范围查询 例如WHERE price > 100 - 访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)
- 如果哈希冲突很多的话,一些索引维护操作的代价也会很高
空间数据索引(R-Tree)
MyISAM 表支持空间索引,可以用作地理数据存储.和B-Tree索引不同,这类索引无须前缀查询.空间索引会从所有维度来索引数据.查询时,可以有效地使用任意维度来组合查询.
全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键字,而不是直接比较索引中的值.全文搜索和其他几类索引的匹配方式完全不一样.
查询性能优化
查询性能低下最基本的原因是访问的数据太多.某些查询可能不可避免地需要筛选大量数据.大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化.对于低效的查询,我们可以通过两个步骤来分析:
- 确认应用程序是否检索大量超过需要的数据
- 确认MySQL服务器层是否在分析大量超过需要的数据行
有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃.这会给MySql服务器带来额外的负担,并增加网络开销.
- 避免查询不必要的记录
- 多表关联时不要返回不必要的字段
- 不要总是取出全部列
- 避免重复查询
- 分解关联查询
MySql的查询状态
-
Sleep 线程正在等待客户发送新的请求
-
Query 线程正在执行查询或者正在将结果发送给客户端
-
Locked 在MySql服务器层,该线程正在等待表锁
-
Analyzing and statistics 线程正在收集存储引擎统计信息,并且生成查询的执行计划
-
Copying to tmp table [on disk] 线程正在执行查询,并且将结果集都复制到一个临时表汇总,如果带有
on disk标记,那表示MySql正在讲一个内存临时表放到磁盘上 -
Sorting result 线程正在对结果集进行排序
-
Sending Data 线程可能在多个状态之间传送数据,或者正在生成结果集,或者在向客户端发送数据
查询优化器
-
重新定义关联表的顺序
-
将外连接转化成内连接
并不是所有的OUTER JOIN语句都必须以外连接的方式执行,例如:WHERE条件,库表结构都可能会让外连接等价于一个内连接 -
使用等价变换规则
MySql可以使用一些等价变换来简化并规范表达式.它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断.例如,(5=5 AND a>5)将被改写而成a>5
- 优化 COUNT(),MIN()和MAX()
索引和列是否为空通常可以帮助MySql优化这类表达式.例如,要找某一列的最小值,只需要查询对应B-Tree索引最左端的记录.类似的,没有任何WHERE条件的COUNT(*) 查询通常也可以使用存储引擎提供的一些优化
- 预估并且转化为常数表达式
当MySql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理
关联子查询
一般建议使用左外连接(LEFT OUTER JOIN)来代替子查询
UNION的限制
有时,MySql无法将限制条件从外层下推到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询优化上.
如果希望UNION的各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句.
优化COUNT()查询
- 简单优化
select count(*) from test where id > 5
我们可以这样优化
select (select count(*) from test) - count(*) from test where id <=5
这其中的子查询会被当做一个常数来处理
优化LIMIT分页
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描.
select id from test order by name limit 50,5;
可以有以下几种方式
select id from test where position between 50 and 54 order by position;
select * from test where id < 999 order by id desc limit 20;
扫一扫
449
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
