MYSLQ必学必会

Posted by zhida.liao on February 1, 2019

This document is not completed and will be updated anytime.

索引

MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。

InnoDB的索引类型

InnoDB的索引有两类索引,聚集索引(Clustered Index)与普通索引(Secondary Index)。

两种索引类型的区别

在索引结构中,非叶子节点存储key,叶子节点存储value;

聚集索引:,叶子节点存储行记录(row),包括所有列字段;

画外音:所以,InnoDB索引和记录是存储在一起的,而MyISAM的索引和记录是分开存储的。

普通索引:叶子节点存储了PK的值;

普通索引的扫描过程

InnoDB的普通索引,实际上会扫描两遍:

  • 由其他字段的普通索引找到PK值;

  • 根据PK的聚集索引,找到行记录;

聚集索引分布

InnoDB的每一个表都会有聚集索引:

  • 如果表定义了PK,则PK就是聚集索引;

  • 如果表没有定义PK,则第一个非空unique列是聚集索引;

  • 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

索引的创建类型

UNIQUE唯一索引

不可以出现相同的值,可以有NULL值。

INDEX普通索引

允许出现相同的索引内容。

PRIMARY KEY主键索引

不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求。


索引的CURD

ALTER TABLE

适用于表创建完毕之后再添加。

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)索引名

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,当前的索引名就是该字段名。 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)
CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

--例:只能添加这两种索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)
CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意,下面创建了主键索引,这里就不用创建了 
  `username` varchar(64) NOT NULL COMMENT '用户名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名称,可要可不要,不要就是和列名一样 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';
索引的删除
DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 这两句都是等价的,都是删除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除
查看索引
show index from tablename;	
索引的更改

删除重建。


索引的选择

  • 维度高的列创建索引: 数据列中不重复值出现的个数,这个数量越高,维度就越高。
  • 对 where,on,group by,order by 中出现的列使用索引
  • 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键。
  • 为较长的字符串使用前缀索引。
  • 不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引。
  • 使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。

索引技巧

组合索引与前缀索引

单列索引

为一个字段添加索引,普通索引会先根据索引找到对应的主键,再根据主键回查记录。

覆盖索引

使用组合索引,当一条查询语句要查的字段,都在组合索引中。

sql只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。

组合索引

1: 单个索引

MySQL先根据索引返回一批数据,然后在服务层根据 where 条件进行筛选

2: 建立多个 单索引

MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引,其他是用不到的,也就是说还是一个全表扫描的过程。

3: 组合索引

建立索引:vc_Name,vc_City,i_Age,相当于分别建立了:

  • vc_Name,vc_City,i_Age
  • vc_Name,vc_City
  • vc_Name

mysql 组合索引 “最左前缀” 的结果, 简单的理解就是只从最左面的开始组合

前缀索引

如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。

SELECT COUNT(DISTINCT(LEFT(title,10)))/COUNT(*) FROM Arctic; — 这个值大于0.31就可以创建前缀索引,Distinct去重复

ALTER TABLE user ADD INDEX uname(title(10)); — 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度。

索引不生效的场景

  • 参与计算
  • 函数运算
  • 全匹配模糊查询
  • 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
  • 字符串与数字比较
  • or条件,左右两个字段,都必须建立索引,否则无一生效 , select * from table where name ='a' or age = 18 and sex = 'M' , sex不影响
  • in 不会让索引失效
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引  

索引的弊端

  • 不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新。

  • 但是,在互联网应用中,查询的语句远远大于DML的语句,甚至可以占到 80% - 90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引.

Explain

mysql在执行一条查询之前,会对发出的每条SQL进行分析,决定是否使用索引或全表扫描

MySQL EXPLAIN详解

select_type
  • SIMPLE: 简单查询,标识查询不包含任何子查询或者UNION语句
  • PRIMARY: 复杂查询的外层查询,一般都在第一行,代表这是一个复杂查询的最外层查询
  • SUBQUERY: 复杂查询的子查询,指不在FROM子句中的那些
  • DEPENDENT SUBQUERY: 复杂查询中,依赖外部查询的子查询
  • DERIVED: 在FROM子句中的子查询
type

表示了Mysql究竟采取何种方法来访问数据,通常能够在一定程度上反映查询语句的性能。 可能出现的值有如下这些,性能从最差到最优(5.6版本):

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • ALL:全表扫描,最惨的性能,从数据表中逐行查找数据。除非使用了LIMIT或者在Extra列中有”Using distinct/not exists”字样

  • index:全表扫描的进阶版,按索引顺序全表扫描,通常性能和全表扫描没什么区别,除非Extra列中有”Using index”字样,那说明使用了覆盖索引,这种情况下要快于ALL,因为直接扫描索引就能获取数据,而索引通常比表小的多。如果所要查询的列是某个索引的一部分,通常会出现这种查询。

  • range:范围扫描,比index强一些,因为它是从索引的某个点开始的,用不着遍历全部索引。一些带有BETWEEN,各种比较符号的语句容易出现这种类型,但是要特别注意IN和OR,这也会显示成range,但是其性能跟index差不多。

  • index_subquery:索引替换子查询,如果有这样的语句SELECT * FROM table WHERE value IN(SELECT key_column FROM table where xxx),IN后面的语句会被索引直接代替,来提高效率。

  • index merge :同一个表的多个索引的范围扫描可以对结果进行合并,合并方式分为三种:union(), intersection, 以及它们的组合 MySQL 优化之 index merge(索引合并)

  • ref: 一种索引访问,它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引 或 (唯一性索引非唯一性前缀)时才会发生
  • eq_ref: 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)

  • const:当查询唯一地匹配一条记录,并且使用主键等于常数或者唯一索引等于某常数作为查询条件时,Mysql会视查询出来的值为常数,这种类型非常快。比如:SELECT * FROM tbl_name WHERE primary_key = 1;

  • system:表只有一行记录且为系统表,Mysql官方没有给出出现这个类型的例子
possible_keys

查询可能会用到的索引

key_len

最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。

key

显示查询最终使用到的索引,如果该索引没有出现在possible_key里,那么它可能是一个覆盖索引。

如果显示的是NULL,很遗憾没有任何索引被用到,说明查询的性能可能会很差。

ref

显示哪些列或者常量被用来跟key中显示的索引进行比较,从而获取结果。

rows

显示Mysql引擎认为它要获得结果预计要扫描的行数。这是一个估计值,可能不是很精确。注意这个值不是结果集的行数,还要知道有很多优化手段没能影响这个值,因此可能最终执行时不必读取这么多行记录。

如果有多行结果,将多行的rows相乘可以得到一条完整语句执行预计要扫描的行数。

filtered

filtered值只对index和all的扫描有效,比如全局扫描100万条数据,扫描到50万就命中了,也就是 50%

Extra
  • Using index:代表使用了覆盖索引。
  • Using where:代表使用了where条件句中的条件进一步地过滤,如果没有显示这个而且查询类型是ALL或者index,那说明SQL写的很差,需要优化。除非你真的就想要全表扫描。
  • Using temporary:代表为了得到结果,Mysql不得不创建一个临时表,将结果放在临时表里,在GROUP BY和ORDER BY使用的时候经常出现这个。
  • Using filesort:代表索引不能满足排序的需求,于是一种文件排序算法被使用,至于使用的是哪种算法(一共有3种算法),是在内存还是磁盘上进行排序(结果集比较小的情况下可能在内存中完成排序),这个字段不会告诉你这些信息。

参考

InnoDB,select为啥会阻塞insert?


连接

连接类型

INNER JOIN : 交集

LEFT JOIN: 左集合

RIGHT JOIN: 右集合

FULL JOIN: 并集

UNION: 合并多个select 语句, 列数量、顺序、数据类型 必须一致。

连接原理

  • 假定AB两张表, 数据量为 m , n
  • 相对左边的称为驱动表,相对在右侧的为匹配表
Nested-Loop Join

1: 常规扫描

A表全表扫描m条数据, 每条数据依次在B表中进行数据库全表查询,一共需要扫描 m*n

2: 对关联字段增加索引,并且索引为主键

A表全表扫描m条数据, 每条数据依次在B表中进行数据库查询,每次索引查询(如果是主键,理解为常量扫描), 一共需要扫描 1+m

Block Nested-Loop Join

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数

举例:

  • 外层循环的结果集是100行,使用NLJ 算法需要扫描内部表100次
  • 如果使用BNL算法,先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer
  • 然后在InnerLoop表(内部表)中直接匹配这10行数据,内存循环就可以一次与这10行进行比较, 这样只需要比较10次

  • join_buffer_size变量决定buffer大小。
  • 只有在join类型为all, index, range的时候才可以使用join buffer。
  • 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
  • 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
  • 在join之前就会分配join buffer, 在query执行完毕即释放。
  • join buffer中只会保存参与join的列, 并非整个数据行。

引擎

InnoDB、MyISAM 区别

关于count(*)

知识点: MyISAM 会直接存储总行数, InnoDB 则不会,需要按行扫描。

潜台词是,对于select count(*) from t; 如果数据量大,MyISAM会瞬间返回,而InnoDB则会一行行扫描。

只有查询全表的总行数,MyISAM才会直接返回结果,当加了where条件后,两种存储引擎的处理方式类似。

关于事务

MyISAM不支持事务,InnoDB支持事务。

事务是选择InnoDB非常诱人的原因之一,它提供了commit,rollback,崩溃修复等能力,事务也非常耗性能,会影响吞吐量,建议只对一致性要求较高的业务使用复杂事务。

关于外键

知识点:MyISAM不支持外键,InnoDB支持外键。 在数据量大并发量大的情况下,都不应该使用外键,而建议由应用程序保证完整性。

关于行锁与表锁

知识点:MyISAM只支持表锁,InnoDB可以支持行锁。

分析:

  • MyISAM:执行读写SQL语句时,会对表加锁,所以数据量大,并发量高时,性能会急剧下降。

  • InnoDB:细粒度行锁,在数据量大,并发量高时,性能比较优异。

常见坑:

  • InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁。

  • 画外音:Oracle的行锁实现机制不同。

例子:

t_user(uid, uname, age, sex) innodb; uid PK 无其他索引

update t_user set age=10 where uid=1; 命中索引,行锁。

update t_user set age=10 where uid != 1; 未命中索引,表锁。

update t_user set age=10 where name=’shenjian’; 无索引,表锁。

启示:InnoDB务必建好索引,否则锁粒度较大,会影响并发。

参考

InnoDB,5项最佳实践,知其所以然?


除了粗粒度的划分为 细粒度行锁、表锁 之外

InnoDB可细分为七种锁

(1)共享/排它锁(Shared and Exclusive Locks) (2)意向锁(Intention Locks) (3)记录锁(Record Locks) (4)间隙锁(Gap Locks) (5)临键锁(Next-key Locks) (6)插入意向锁(Insert Intention Locks) (7)自增锁(Auto-inc Locks)

1: 共享/排它锁

简单的锁住太过粗暴,连“读任务”也无法并行,任务执行过程本质上是串行的。

共享锁与排他锁定义:
  • 共享锁(Share Locks,记为S锁),读取数据时加S锁

  • 排他锁(eXclusive Locks,记为X锁),修改数据时加X锁

共享锁与排他锁的作用
  • 共享锁之间不互斥,简记为:只有读读可以并行

  • 排他锁与任何锁互斥,简记为:写读,写写不可以并行

可以看到,一旦写数据的任务没有完成,数据是不能被其他任务读取的,这对并发度有较大的影响。

用法:
  • 共享锁 :(Share Locks,记为S锁): 通过在执行语句后面加上 lock in share mode 就代表对某些资源加上共享锁了

  • 排它锁: (eXclusive Locks,记为X锁): update,insert,delete语句会自动加排它锁

2:记录锁(Record Locks)

记录锁,封锁 索引记录,例如:

select * from t where id=1 for update;

它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行。

select * from t where id=1;

则是快照读(SnapShot Read),它并不加锁

3:间隙锁(Gap Locks)

间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。

例子: t(id PK, name KEY, sex, flag);

表中有四条记录:

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

这个SQL语句

select * from t 
    where id between 8 and 15 
    for update;

会封锁区间,以阻止其他事务id=10的记录插入。

画外音:为什么要阻止id=10的记录插入?

如果能够插入成功,头一个事务执行相同的SQL语句,会发现结果集多出了一条记录,即幻影数据。

间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。

4:临键锁(Next-Key Locks)

临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。

5: 意向锁

意向锁分为:

意向共享锁(intention shared lock, IS),它预示着,事务有意向对表中的某些行加共享S锁

意向排它锁(intention exclusive lock, IX),它预示着,事务有意向对表中的某些行加排它X锁

意向锁协议(intention locking protocol)并不复杂:

事务要获得某些行的S锁,必须先获得表的IS锁

事务要获得某些行的X锁,必须先获得表的IX锁

例子

事务A: 修改一行数据,获取了排它锁。 事务B: 获取数据表的表锁。

过程: 事务A已经持有了排它锁,事务B要先遍历每一行,查看是否有行锁记录,如果有,则阻塞事务B对表锁的获取,效率低下

采用意向锁

过程: 事务A获取排它锁之前,先获取排他意向锁,事务B获取表锁前,判断是否存在排他意向锁,如果有,则阻塞表锁的获取,效率高。

PS : 申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。

参考

InnoDB 的意向锁有什么作用? - 发条地精的回答 - 知乎 InnoDB并发插入,居然使用意向锁?

6:插入意向锁

插入意向锁,是间隙锁(Gap Locks)的一种(所以,也是实施在索引上的),它是专门针对insert操作的。

它的玩法是:多个事务,在同一个索引,同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此。

7:自增锁

自增锁是一种特殊的表级别锁(table-level lock),专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。

InnoDB提供了innodb_autoinc_lock_mode配置,可以调节与改变该锁的模式与行为。

例子:

事务A先执行,还未提交:

insert into t(name) values(xxx);

事务B后执行:

insert into t(name) values(ooo);

InnoDB在RR隔离级别下,能解决幻读问题,上面这个案例中:

(1)事务A先执行insert,会得到一条(4, xxx)的记录,由于是自增列,故不用显示指定id为4,InnoDB会自动增长,注意此时事务并未提交;

(2)事务B后执行insert,假设不会被阻塞,那会得到一条(5, ooo)的记录;

(3)事务A继续insert:

insert into t(name) values(xxoo); 会得到一条(6, xxoo)的记录。

(4)事务A再select:

select * from t where id>3;

得到的结果是:

4, xxx

6, xxoo

画外音:不可能查询到5的记录,再RR的隔离级别下,不可能读取到还未提交事务生成的数据。

对于AUTO_INCREMENT的列,连续插入了两条记录,一条是4,接下来一条变成了6,就像莫名其妙的幻影。

参考

InnoDB,select为啥会阻塞insert?

并发

通过并发控制保证数据一致性的常见手段有:

  • 锁(Locking)
    • 悲观锁
      • 普通锁
      • 读写锁
    • 乐观锁
  • 数据多版本(Multi Versioning)

提高并发的演进思路:

  • 普通锁,本质是串行执行

  • 读写锁,可以实现读读并发

  • 数据多版本,可以实现读写并发

乐观锁

SELECT balance,version FROM user WHERE id=1 AND balance>10;
UPDATE user SET balance=balance-10,version=last_version+1 WHERE id=1 AND version=last_version;

注意到UPDATE里的last_version为SELECT获取的本次读写的版本号.

不需要数据库事务的支持,SELECT操作和UPDATE操作的时间跨度再大也没有问题.

上述版本号的方法借鉴了Memcached的CAS(Check And Set)冲突检测机制,这是一个乐观锁,能保证高并发下的数据安全.

数据多版本

redo & undo

例如某一事务的事务序号为T1,其对数据X进行修改,设X的原值是5,修改后的值为15,

Undo日志为<T1, X, 5>, Redo日志为<T1, X, 15>。

  • undo日志用于记录事务开始前的状态,用于事务失败时的回滚操作;

  • redo日志记录事务执行后的状态,用来恢复未写入data file的已成功事务更新的数据。

逻辑日志

物理日志

image

MVCC

InnoDB的内核,会对所有row数据增加三个内部属性:

(1)DB_TRX_ID ,6字节,记录每一行最近一次修改它的事务ID;

(2)DB_ROLL_PTR,7字节,记录指向回滚段undo日志的指针;

(3)DB_ROW_ID,6字节,单调递增的行ID;

InnoDB为何能够做到这么高的并发?

回滚段里的数据,其实是历史数据的快照(snapshot),这些数据是不会被修改,select可以肆无忌惮的并发读取他们。

快照读(Snapshot Read),这种一致性不加锁的读(Consistent Nonlocking Read),就是InnoDB并发如此之高的核心原因之一。

这里的一致性是指,事务读取到的数据,要么是事务开始前就已经存在的数据(当然,是其他已提交事务产生的),要么是事务自身插入或者修改的数据。

什么样的select是快照读?

除非显示加锁,普通的select语句都是快照读,例如:

select * from t where id>2;

这里的显示加锁,非快照读是指:

select * from t where id>2 lock in share mode;

select * from t where id>2 for update;

通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)。

InnoDB并发如此高,原因竟然在这?

innodb_flush_log_at_trx_commit

0: 如果 innodb_flush_log_at_trx_commit 的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作。

1: 当设为默认值1的时候,每次提交事务的时候,都会将log buffer刷写到日志。

2: 如果设为2,每次提交事务都会写日志,但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是,并不能保证100%每秒一定都会刷到磁盘,这要取决于进程的调度。

默认值1是为了保证完整的ACID。当然,你可以将这个配置项设为1以外的值来换取更高的性能,但是在系统崩溃的时候,你将会丢失1秒的数据。设为0的话,mysqld进程崩溃的时候,就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

刷写的概念 刷写其实是两个操作,刷(flush)和写(write),区分这两个概念(两个系统调用)是很重要的。在大多数的操作系统中,把Innodb的log buffer(内存)写入日志(调用系统调用write),只是简单的把数据移到操作系统缓存中,操作系统缓存同样指的是内存。并没有实际的持久化数据。

所以,通常设为0和2的时候,在崩溃或断电的时候会丢失最后一秒的数据,因为这个时候数据只是存在于操作系统缓存。之所以说“通常”,可能会有丢失不只1秒的数据的情况,比如说执行flush操作的时候阻塞了。

总结 设为1当然是最安全的,但性能页是最差的(相对其他两个参数而言,但不是不能接受)。如果对数据一致性和完整性要求不高,完全可以设为2,如果只最求性能,例如高并发写的日志服务器,设为0来获得更高性能。

innodb_flush_log_at_trx_commit配置

事务

每一种级别都规定了: 一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的

隔离级别

READ UNCOMMITTED(读未提交)

对于事务未提交的数据,其他事务依然可以读取。

READ COMMITTED(读已提交)
  • 大多数数据库系统默认的隔离级别都是 READ COMMITTED(但 MySQL 不是)。
  • 一个事务所做的修改,在提交之前,对其他事务都是不可见的。
REPEATABLE READ(可重复读)
  • 事务开启时,不允许其他事务进行 update 操作,这样事务A读取的数据都是一致的,称为可重复读
  • MySQL默认的事务隔离级别
SERIALIZABLE(可串行化)

错误场景

不可重复读

事务A多次读取数据

事务B修改了数据 Update

事务A多次读取的数据不一致

脏读

读取到其他事务未提交的数据

幻读

A事务读取范围数据

B事务在该范围中插入数据 Insert

A事务读取范围数据出现之前没有看到的数据

Mysql的默认的事务隔离级别是?脏读、幻读、不可重复读又是什么? 高性能MySQL读书笔记-事务 InnoDB并发如此高,原因竟然在这?

优化

查看mysql优化过后的,实际执行语句

注意标点符号

EXPLAIN EXTENDED
SELECT * FROM `table_01`  where business_type  = '111'  ;
SHOW  WARNINGS

查看索引篇 explain

merge_index

group by

group by实质是先排序后进行分组。

利用GROUP BY统计大数据时,应当将查询与统计分离,优化查询语句。

1) 分组字段不在同一张表中

2) 分组字段没有建索引,多个字段时建联合索引(联合索引的字段顺序要与ORSER BY中的字段顺序一致)

4)分组字段中加函数导致索引不起作用(这种一定要避免、本文不对这种情况展开说明)

5)分组字段中含有TEXT或CLOB字段(改成VARCHAR字段)

  1. 如果GROUP BY 的列没有索引,产生临时表.
  2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
  3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
  4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
  5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
  6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

order by

mysql支持两种方式的排序,filesort和index,index效率高,它指mysql扫描索引本身完成排序。filesort方式效率低

order by时select *是一个大忌,只query需要的字段:当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text或blob类型时,会用改进后的算法–单路排序,否则用老算法–多路排序

多个条件字段,要使用一致的排序规则,默认是ASC升序

ORDER BY中字段中联合索引的所有字段DESC或ASC要统一,否则索引不起作用


redo补充

  • 事务开启
  • 数据修改
  • 先写入日志文件
  • 写入内存数据
  • 事务提交
  • 将日志文件数据刷入磁盘中 (可以设置刷盘的事件)

参考网站

MYSQL性能优化的最佳20+条经验 [《高性能Mysql》] MySQL 索引优化全攻略 MySQL Join的底层实现原理-分为三种 推荐:将原理分成两种类型性能优化之Block Nested-Loop Join(BNL) explain 详解