A:原子性,事务是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。
C:一致性,一致性是指在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
I:隔离性,多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。
D:持久性,意味着在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
事务的隔离级别:读已提交:(脏读、不可重复读、幻读)
读未提交:(不可重复读、幻读)
可重复读:(幻读)
串行化
幻读是什么,数据库如何处理幻读,如何解决幻读?幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
innodb引擎使用行级锁和间隙锁解决了幻读问题,也称为Next-Key Locking
主键索引和唯一索引有什么区别主键索引是唯一性索引,唯一性索引并不一定是主键
主键列在创建时,默认 空值 + 唯一索引
一个表最多只能创建一个主键,可以创建多个唯一索引
主键索引上存储数据信息,唯一索引存储主键信息
慢查询优化处理1.sql是否走索引,如果没有走索引,考虑加入索引,使用explain查看sql执行过程。
explain指令各个字段含义:
1.id
sql执行序列号,表面sql中各个语句的执行顺序。
2.select_type
select类型,它有以下几种值
2.1 simple 它表示简单的select,没有union和子查询
2.2 primary 最外面的select,在有子查询的语句中,最外面的select查询就是primary,上图中就是这样
2.3 union union语句的第二个或者说是后面那一个.现执行一条语句,explain
select * from uchome_space limit 10 union select * from uchome_space limit 10,10
会有如下结果
第二条语句使用了union
2.4 dependent union UNIOn中的第二个或后面的SELECt语句,取决于外面的查询
2.5 union result UNIOn的结果,如上面所示
还有几个参数,这里就不说了,不重要
3 table
输出的行所用的表,这个参数显而易见,容易理解
4 type
连接类型。有多个参数,先从最佳类型到最差类型介绍 重要且困难
4.1 system
表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计
4.2 const
表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快
记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const,看下面这条语句
explain SELECT * FROM `asj_admin_log` limit 1,结果是
虽然只搜索一条数据,但是因为没有用到指定的索引,所以不会使用const.继续看下面这个
explain SELECt * FROM `asj_admin_log` where log_id = 111
log_id是主键,所以使用了const。所以说可以理解为const是最优化的
4.3 eq_ref
对于eq_ref的解释,mysql手册是这样说的:"对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。看下面的语句
explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid
得到的结果是下图所示。很明显,mysql使用eq_ref联接来处理uchome_space表。
目前的疑问:
4.3.1 为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成
explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid
结果还是一样,需要说明的是uid在这两个表中都是primary
4.4 ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。
看下面这条语句 explain select * from uchome_space where uchome_space.friendnum = 0,得到结果如下,这条语句能搜出1w条数据
4.5 ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
上面这五种情况都是很理想的索引使用情况
4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
4.7 unique_subquery
4.8 index_subquery
4.9 range 给定范围内的检索,使用一个索引来检查行。看下面两条语句
explain select * from uchome_space where uid in (1,2)
explain select * from uchome_space where groupid in (1,2)
uid有索引,groupid没有索引,结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显
explain select * from uchome_space where friendnum = 17
这样的语句是不会使用range的,它会使用更好的联接类型就是上面介绍的ref
4.10 index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。
4.11 ALL 对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记 const的表,这通常不好,并且通常在它情况下 很差。通常可以增加更多的索引而不要使用 ALL,使得行能基于前面的表中的常数值或列值被检索出。
5 possible_keys 提示使用哪个索引会在该表中找到行,不太重要
6 keys MYSQL使用的索引,简单且重要
7 key_len MYSQL使用的索引长度
8 ref ref列显示使用哪个列或常数与key一起从表中选择行。
9 rows 显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引
10 Extra 该列包含MySQL解决查询的详细信息。
10.1 Distinct MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。一直没见过这个值
10.2 Not exists
10.3 range checked for each record
没有找到合适的索引
10.4 using filesort
MYSQL手册是这么解释的“MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERe子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。”目前不太明白
我的理解:如果存在排序并且取出的列包括text类型会使用到using filesort,这会非常慢
10.5 using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解,就是说明是否使用了索引
explain select * from ucspace_uchome where uid = 1的extra为using index(uid建有索引)
explain select count(*) from uchome_space where groupid=1 的extra为using where(groupid未建立索引)
10.6 using temporary
为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
出现using temporary就说明语句需要优化了,举个例子来说
EXPLAIN SELECt ads.id FROM ads, city WHERe city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BY ads.id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- -------------------------------
1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using temporary; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
这条语句会使用using temporary,而下面这条语句则不会
EXPLAIN SELECT ads.id FROM ads, city WHERe city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BYcity.ads_id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ---------------------------
1 SIMPLE city ref ads_id,city_id city_id 4 const 2838 100.00 Using where; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
这是为什么呢?他俩之间只是一个order by不同,MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。EXPLAIN 结果中,第一行出现的表就是驱动表(Important!)以上两个查询语句,驱动表都是 city,如上面的执行计划所示!
对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序 (Important!)
因此,order by ads.id desc 时,就要先 using temporary 了!
驱动表的定义
wwh999 在 2006年总结说,当进行多表连接查询时, [驱动表] 的定义为:
1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表];
2)未指定联接条件时,行数少的表为[驱动表](Important!)。
永远用小结果集驱动大结果集
今天学到了一个很重要的一点:当不确定是用哪种类型的join时,让mysql优化器自动去判断,我们只需写select * from t1,t2 where t1.field = t2.field
通俗解释:select * from table order by field
其中filed建普通索引,这种情况会使用到using temporary,因为虽然这时候使用到了索引,但因为扫描的是全表,mysql优化器会判断:反正是搜索全表而且要排序,因为这时候要回行,我还不如不沿着索引找数据,直接全部检索出所有数据来在排序。如果语句这么写 select * from table where field > 1 order by field.mysql优化器就会这么判断:这时候不是搜全表,我需要先根据where条件,沿着索引树搜出想要的相应索引数据,在回行(一边找索引一边回行)。这时候就不需要临时表了
10.7 using where
WHERe子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。(这个说明不是很理解,因为很多很多语句都会有where条件,而type为all或index只能说明检索的数据多,并不能说明错误,useing where不是很重要,但是很常见)
如果想要使查询尽可能快,应找出Using filesort 和Using temporary的Extra值。
10.8 Using sort_union(...) , Using union(...) , Using intersect(...)
这些函数说明如何为index_merge联接类型合并索引扫描
10.9 Using index for group-by
类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。
实例讲解
通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECt语句。
联合索引,避免回表
众所周知,联合索引的叶子节点存储了索引字段的值,因此,合理的设计联合索引,可以避免回表操作。比如:我的表中有四十个字段,但是查询频率比较高的只有a,b,c三个字段,这时候,如果我想进一步提升效率,我可以将abc三个字段设计一个联合索引,查询的速度就会有进一步提升。
深度分页: 指的是在分页查询的时候,页数标记的特别高(如查询第10000页的数据),这样,如果正常查询的sql会出现 select * from table_name limit 10000,10 的sql出现,会造成极大的性能问题。因此深度分页问题需要有一定的优化。
方案一:从业务方面搞定,因为很多业务根本用不到那么多分页,那么就可以限制最大分页的页数,比如百度的最大显示页码就只有76页。
业务方面如果搞不定,只能通过技术手段搞定,那么我们就分析一下问题出在哪?
我们能够想到,性能瓶颈出在了limit页码过大的问题上,我们知道,索引的结构(B+树)有一个特性,就是叶子节点之间依靠双向链表连接,这个特性主要是针对范围查询做的优化,因此在进行分页查询的时候,我们会直接通过链表进行查询,问题就出在了这里,由于页码过大,而且查询字段过多,每次查询时候需要回表,所以链表在遍历的时候时间过长,造成了性能瓶颈。因此引申出了下面两个优化方案。
方案二:分页查询时将id作为查询条件传过来,然后往后查十条,这样可以先通过索引锁定到第一条数据,然后通过链表往后遍历十条数据,从而达到优化的效果。
如果你的已有的方案不是那么方便改,可以考虑下边的方案。
方案三:在sql中先分页查询到id(不需回表查询速度较快),然后在进行表关联进行分页查询,具体sql如下(类似):
select table.* from table_name as table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;
1
突破瓶颈,分库分表
分库
每个数据库是有一个最大连接数的,超过这个连接数会查询超时,所以,在你的项目的并发量超过一定量级之后,你就需要考虑分库了,至于具体分多少个库,看实际QPS量有多少,一个库默认的连接数是100,按照具体QPS推算需要分出多少个库。
分表
分表又分为水平分表和垂直分表。
水平分表: 是将一张表的字段拆分到两张表上,从而达到单表数据存储量降低的目的。一般应用的场景都是按照业务含义拆分,可以拆分成多张一对一的表,各个表通过主键关联,比如将sku表拆分成sku基础信息表,sku扩展表,sku状态表,等等。
垂直分表: 是单表数据过多后,会导致数据查询时候读盘次数增加,从而查询效率降低,这时候就考虑将数据分到多张表上,通常做法是确定一个拆分键,将拆分键进行一定的算法(比如:hash运算后按照分表数取模),最终落到某张表中。
基本上单表量级超过两千万就可以考虑分表了,分多少表按照 最大量级除以500-1000万 左右数据计算。至于为什么最大阈值是两千万,可以看下 mysql B+树 索引机制 及 InnoDB一棵B+树可以存放多少行数据? 深入了解一下。
有一种慢查询的情况是出在了一种峰值查询的时候,就是有时候会出现某个时间段有些业务几种出,但是定时任务过于集中,导致某一个短暂的时间段QPS暴增,从而数据库扛不住压力,出现了慢查询甚至于数据库崩溃,由于是峰值的问题,扩容机器又很浪费,不扩容又扛不住压力,为了避免这类问题,可以考虑引入缓存机制进行削峰操作,通过缓存机制短暂的保存数据库信息,让峰值的请求都压在缓存上,从而间接的避免了慢查询的情况。
当然,无论是使用缓存中间件Redis,或者内存中间件MemoryCache都可以解决这类问题。
以慢打快,异步处理
同样是慢查询出现定时任务批量处理数据的情况,但是定时任务过于集中,导致某一个短暂的时间段QPS暴增,这时候更好的方案是通过mq异步处理数据,通过控制mq的消费密度来减轻qps,从而达到减轻数据库的压力,以时间换空间,以慢打快,效果奇好。
如果是在查询效率提升不上去,就考虑更换查询中间件吧,使用大数据的处理方式,比如,落ElasticSearch查询。



