mysql

索引位置：

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。

B树：

B-树允许每个节点有更多的子节点即可（多叉树）。
B-树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。

空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。

时间局部性：

结构：

B+树

B+树是B-树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
2. 为所有叶子结点增加了一个链指针

结构：

 

 

锁：

 

乐观锁

总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。实现原理，更新时附加一个版本号。

悲观锁

总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁。

悲观锁分为两种：共享锁和排它锁

共享锁是其它事务可以读但是不能写
排他锁是只有自己得事务有权限对此数据进行读写

mysql在执行insert、update、delete会自动加锁，mysql对select却不会加锁。

在数据库的增、删、改、查中，只有增、删、改才会加上排它锁，而只是查询并不会加锁，只能通过在select语句后显式加lock in share mode或者for update来加共享锁或者排它锁。

Show profiles

Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况，可以用于SQL的调优测量。
Show profiles默认状态下是关闭的，执行set profiling=true;打开状态

 

 

 

注意： 可能会被performance Schema替代：

 

show engine innodb status\G;

可查看innodb引擎的运行时信息。

SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE

共享锁 (lock in share mode)

允许其它事务也增加共享锁读取
不允许其它事物增加排他锁 (for update)
当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁
共享锁，事务增加，都能读。修改是惟一的，必须等待前一个事务 commit，才可

排他锁 (for update)

事务之间不允许其它排他锁或共享锁读取，修改更不可能
一次只能有一个排他锁执行 commit 之后，其它事务才可执行
不允许其它事务增加共享或排他锁读取。修改是惟一的，必须等待前一个事务 commit，才可

行锁添加：

1、表中创建索引， select ... where 字段(必须是索引) 不然行锁就无效。

2、必须要有事务，这样才是 行锁(排他锁)

3、在select 语句后面 加 上 FOR UPDATE；

explain用法和结果分析

id

• id相同，执行顺序由上至下 
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在  id相同的可以认为是一组，同一组中从上往下执行，所有组中id大的优先执行

type

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种，从好到差依次是

system > const > eq_ref > ref > range > index > all

system 

表只有一行记录（等于系统表）

const 

表示通过索引一次就找到了

eq_ref 

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref

非唯一性索引扫描，返回匹配某个单独值的所有行

range 

只检索给定范围的行

index   

Full Index Scan，Index与All区别为index类型只遍历索引树

all 

 Full Table Scan 将遍历全表以找到匹配的行

rows

大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好 

建立索引的几大原则

选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

ALTER TABLE 表名 ADD KEY(字段名(N));

直至我们的辨识度（aoo）达到最接近最大辨识度(ayy)

最左前缀匹配原则，非常重要的原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

联合索引

当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

索引失效：

or的使用（并不是所有的or条件都是失效的）

or条件2边都创建索引，他还是走索引的，就如我第二张图所示，只有2边有一个没有创建索引，才会全表扫描。

多列索引

就是当使用多列索引时，查询条件中必须包含第一个，（不论第一个在查询中的哪个位置，必须的存在），否则索引失效。

like的使用

like中不能把%放在前面，只能放在后面，否则索引失效

列类型是否为字符串

如果列是字符串类型，一定要加上单引号

where条件中使用了 != 、 <>

where 后面使用表达式

日期格式化也会导致索引失效

where后面使用not in

in是走索引的。not in 不走索引。

in通常是走索引的，当in后面的数据在数据表中超过30%（上面的例子的匹配数据大约6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系

where 后面使用is not null

is null是走索引的。is not null 不走索引

对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！

重复度过高：

数据库中聚集索引只有一个，默认主键。其他用户创建的索引都是非聚集索引。
非聚集索引存储了对主键的引用，即通过索引确定叶子节点之后，还需要再次根据主键去查询数据。（所以会查询两次）
如果非聚集索引重复率高（即一个同样的值有多个主键），那么首先你会从索引中取一半主键值，然后根据主键值再去查询数据，增加了IO，所以特别耗时。

索引类型

普通索引（单列索引）

直接创建索引

CREATE INDEX index_name ON table_name(col_name);

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

复合索引（组合索引）

创建一个复合索引

create index index_name on table_name(col_name1,col_name2,...);

唯一索引

唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）

# 创建单个索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

# 创建多个索引

CREATE UNIQUE INDEX index_name on table_name(col_name,...);

主键索引

全文索引

索引的查询和删除

#查看:

show indexes from `表名`;

#或

show keys from `表名`;

#删除

alter table `表名` drop index 索引名;

标准sql执行顺序

Mysql事务：

数据库的事务是指一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败

事务的特性ACID

原子性（Atomicity）：

事务的原子性操作，对数据的修改要么全部执行成功，要么全部失败，实现事务的原子性，是基于日志的Redo/Undo机制

一致性（Consistent）

执行事务前后的状态要一致，可以理解为数据一致性

隔离性（Isalotion）

指事务之间相互隔离，不受影响。

持久性(Durable)

事务提交后，这个事务的状态会被持久化到数据库中，也就是事务提交，对数据的新增、更新将会持久化到书库中

原子性、隔离性、持久性都是为了保障一致性而存在的，一致性也是最终的目的。

redo和undo机制

Undo Log

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。

缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。因此引入了另外一种机制来实现持久化，即Redo Log。

Redo Log

Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。

Undo + Redo事务的简化过程

  假设有A、B两个数据，值分别为1,2.
  A.事务开始.
  B.记录A=1到undo log.
  C.修改A=3.
  D.记录A=3到redo log.
  E.记录B=2到undo log.
  F.修改B=4.
  G.记录B=4到redo log.
  H.将redo log写入磁盘。
  I.事务提交

Undo + Redo事务的特点

  A. 为了保证持久性，必须在事务提交前将Redo Log持久化。
  B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
  C. Redo Log 保证事务的持久性。
  D. Undo Log 保证事务的原子性。
  E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。

事务隔离级别

读未提交（READ UNCOMMITTED）

读未提交会读到另一个事务的未提交的数据，产生脏读问题

读提交 （READ COMMITTED）

读提交则解决了脏读的，出现了不可重复读，即在一个事务任意时刻读到的数据可能不一样，可能会受到其它事务对数据修改提交后的影响，一般是对于update的操作

可重复读 （REPEATABLE READ）

可重复读解决了之前不可重复读和脏读的问题，但是由带来了幻读的问题，幻读一般是针对inser操作。

第一个事务查询一个User表id=100发现不存在该数据行，这时第二个事务又进来了，新增了一条id=100的数据行并且提交了事务。

这时第一个事务新增一条id=100的数据行会报主键冲突，第一个事务再select一下，发现id=100数据行已经存在，这就是幻读。

串行化 （SERIALIZABLE）

串行化的执行流程相当于把事务的执行过程变为顺序执行

注意：

读未提交是没有加任何锁的，所以对于它来说也就是没有隔离的效果，所以它的性能也是最好的。对于串行化加的是一把大锁，读的时候加共享锁，不能写，写的时候，加的是排它锁，阻塞其它事务的写入和读取，若是其它的事务长时间不能写入就会直接报超时，所以它的性能也是最差的，对于它来就没有什么并发性可言。

中间件