MySQL笔记

  • 内容
  • 评论
  • 相关

索引

聚集索引

结构:B树结构(平衡树)

一个表只能包含一个聚集索引(可多个列)(一般为主键)

按照存储空间顺序存储 --> 效率高

 

应用场景:

  1. 包含大量非重复的列
  2. 使用下列运算符返回一个范围值的查询   Between ... And ... 、> 、 >= 、 < 、 <=
  3. 返回大型结果集的查询
  4. 经常被用作链接的列
  5. 对order by 或 group by 子句指定列进行索引

 

不适用于:

  1. 频繁更改的列
  2. 字节长的列

 

非聚集索引

结构:B树结构

非聚集索引和聚集索引的差别:

  1. 聚集索引按照物理位置顺序存储,非聚集索引不按照物理顺序存储;
  2. 非聚集索引的叶级结点不是存放数据的数据页,而是索引页。

 

应用场景:

  1. 包含大量非重复值的列
  2. 不返回大型结果集的查询
  3. 经常作为查询条件使用的列
  4. 经常作为链接分组条件的列

 

唯一索引

聚集索引和非聚集索引都可以是唯一索引

创建主键约束或唯一约束        -->      自动创建唯一索引

 

非空索引

普通索引

单列索引

组合索引

全文索引(fulltext)(只有MyISAM支持)

空间索引(只有MyISAM支持)

 

创建索引

语法:create [unique] [clustered | nonclustered] index 索引名 on 表名(列名[,...n])

unique:唯一索引

clustered:聚集索引

nonclustered:非聚集索引

 

删除索引

语法:drop index 索引名

 

explain

expalin显示了mysql如何使用索引来处理select语句以及连接表。

id

select_type:[simple,primary,union,subquery]

table:指定数据库的表名,被读取的先后顺序排列;

partitions

type:system:const,eq_ref,ref,range,index,all;(本数据表与其他数据表的关系)

possible_keys:搜索数据记录时可选用的各个索引;

key:实际选用的索引;

key_len:给出索引按照字节计算的长度,越小则越快;

ref:给出关联关系中另一个数据表的数据列的名字;

rows:预计会查询到的行数;

filtered

extra:提供了与关联操作有关的信息

 

事务四大特性ACID

原子性Atomicity:原子性是事务包含的所有操作要么全部成功,要么全部失败回滚。

 

一致性Consistency:一致性是事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。

 

隔离性Isolation:隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

 

持久性Durability:持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的。

 

事务的四个隔离级别

Read uncommitted

读未提交,顾名思义,就是一个事务可以读取另一个未提交事务的数据。      ==》   可能引发脏读、不可重复读、幻读

 

Read committed

读提交,顾名思义,就是一个事务要等另一个事务提交后才能读取数据。   ==》解决脏读     ==》可能引发不可重复读(一个事务范围内两个相同的查询却返回了不同数据)、可能引发幻读

 

Repeatable read

重复读,就是在开始读取数据(事务开启)时,不再允许修改操作。     ==》解决不可重复读     ==》可能引发幻读

 

Serializable 序列化

Serializable 是最高的事务隔离级别。在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。 ==》解决幻读

 

脏读:事务A读到事务B还未提交的数据。

不可重复读:事务A多次读取数据不一致。比如第一次查询余额是100,第二次查询变成0(两次查询的过程中,数据被另一个事务所更改)。侧重点是更新

幻读:事务A多次读取数据不一致,比如第一次查看订单有两个,加起来是100元,再次查询,有三个订单(新增订单),加起来是200元。侧重点是增删

 

 

不可重复读侧重于修改。

幻读侧重于新增或删除。

解决不可重复读的问题只需锁住满足条件的行。

解决幻读需要锁表。

 

粒度划分:表级锁、行级锁、页级锁

级别划分:共享锁、排它锁

使用方式划分:乐观锁、悲观锁

 

共享锁(读锁)

其他事务可读不可更新

select ...... lock in share mode;

 

排他锁(写锁)

其他事务不可加任何锁,不可以更新

select ...... for update

 

悲观锁

 

乐观锁

利用业务去控制所,如version、update_time字段。

 

表级锁

粒度最大

MyISAM和MEMORY存储引擎采用表级锁(table-level locking)

表级锁分为:表共享读锁(共享锁)、表独占写锁(排它锁)

lock table 表名[AS 别名] read

lock table 表名[low_priority] write

 

行级锁

粒度最小

innoDB默认采用行级锁

select ...... lock in share mode (共享锁)

select ...... for update (独占锁)

 

死锁

innoDB自动检测(外部锁、表锁并不能完全检测),innoDB lock wait timeout

 

页面锁

 

意向锁

innoDB特有,加行级锁自动加意向锁

 

net-key锁

 

间隙锁

 

GAP锁

 

MySQL存储引擎

innoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE

 

 

 

 

SQL优化

执行顺序

e.g.     select id, name, age from user where age>20 group by name having count(*)>1 order by id;

执行顺序:select  >  from  >  where  >  group by  >  having  >  order by

 

limit

e.g.     select id, name, age from user where 1=1 limit 100,10;

执行过程:MySQL并不是跳过前100条数据去取100-110的数据,而是取了0-110,然后舍弃0-99,返回100-110,当表数据量过大,取表后面的数据,则会大大影响性能,正确的方法应该是先找到id,然后根据id去查。

e.g.    SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

 

索引相关

①第一个字符为"%"不会使用索引,如"where name like '%ab'";第一个字符不为"%"才会使用索引,如"where name like 'ab%'"。

②多列索引,只有查询条件使用多列索引的第一个列,才会使用该多列索引。

③查询条件有or,只有or两边都是用索引列时才使用索引。

 

其他

尽量别使用子查询,用Join代替;

数据量大的时候别连表查,用单表查;

合理适当的添加冗余字段。

分析表:analyze table table_name

检查表:check table table_name

优化表:optimize table table_name

 

主从复制,读写分离

可能出现的问题

 

灾备切换

 

主从切换

可能出现的问题

 

MySQL Cluster

可能出现的问题

 

 

 

人生,

只有走出来的美丽,

没有等出来的辉煌。

喜欢 3

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Title - Artist
0:00