MySql教程

Mysql锁及索引

本文主要是介绍Mysql锁及索引,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL工作流程

连接器->查询缓存->解析器->优化器->执行器

连接器
      当你在客户端输入 mysql –u $user –p $pwd 连接 mysql 的时候,接待你的就是连接器。连接器的作用就是和客户端建立连接、获取权限、维持和管理连接。

查询缓存
      建立连接后,就可以执行select语句了。首先MySQL会去查看查询缓存,看下之前是否已经执行过这条查询语句。如果命中缓存,就直接返回。否则就扔给解析器。

解析器
      MySQL需要知道你想做什么,因此我们就来到了解析器。解析器会做词法分析和语法分析。词法分析主要是分析每个词的含义,语法分析会判断你写的 SQL 语句是否满足 SQL 语法。

优化器
      经过解析器,MySQL就知道你想做什么了。但是在开始执行之前,还需要经过优化器的处理。优化器会优化你的SQL语句。生成最终的执行方案 (execution plan)。然后进入执行器阶段。

执行器
      执行器首先会判断你对这张表有没有相应的权限。如果没有,就报错。如果有,就调用相应的存储引擎接口,执行语句。然后将结果集返回给客户端。
MySQL 的存储引擎是插件式的。不同的存储引擎支持不同的特性。

# 查看MySQL支持哪些存储引擎
SHOW ENGINES;

# 查看默认存储引擎
SHOW VARIABLES LIKE ‘%storage_engine%’;

# 查看某张表的存储引擎
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='$db'
AND TABLE_NAME='$table';
#MyISAM
MySQL 5.5  之前默认的存储引擎
特点:
   a. 查询速度很快
   b. 支持表锁    <--重点
   c. 支持全文索引(正排索引、倒排索引)
   d. 不支持事务  <--重点
.frm # 存储表结构,是任何存储引擎都有的
.myd # 存放数据
.myi # 存放索引


聚集索引:索引和数据是一起存放的,分开存放的叫非聚集索引。

#InnoDB
MySQL 5.5 以及以后版本默认的存储引擎。没有特殊应用,推荐使用InnoDB引擎
特点:
   a. 支持事务   <--区别于MyISAM
   b. 支持行锁和表锁(默认支持行锁)  <--重点是行锁
   c. 支持MVCC(多版本并发控制)
   d. 支持崩溃恢复
   e. 支持外键一致性约束
使用 InnoDB 存储表,会生成两个文件.
.frm # 存储表结构,是任何存储引擎都有的
.ibd # 存放数据和索引
#Memory
特点:
   a. 所有数据都存放在内存中,因此数据库重启后会丢失
   b. 支持表锁
   c. 支持Hash和BTree索引
   d. 不支持Blob和Text字段
Memory由于数据都放在内存中,以及支持Hash索引,它的查询速度是最快的。

一般使用Memory存放临时表。

临时表:在单个连接中可见,当连接断开时,临时表也将不复存在。

​ 使用锁可以对有限的资源进行保护,解决隔离和并发的矛盾
锁的概念:
​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。

	MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking); BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁; InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

锁的分类:

MySQL大致可归纳为以下几种:

从对数据操作的粒度划分:

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率,并发度也最高。

从对数据操作的类型划分:

  • 读锁(共享锁):同一份数据,多个读操作可以同时进行而互不影响。
  • 写锁(排它锁):当前操作没有完成之前,它会阻断其他读锁和写锁。

MyISAM的表锁

	MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。	
lock table tableName read;#加读锁
lock table tableName  write;#加写锁
unlock tables;#释放锁
show open tables;#显示表的占用情况
1、如果A会话对表加了读锁之后,所有的会话都可以进行读操作,但是其他会话是不能进行写操作。
2、A会话对表加了读锁之后,A会话也不能进行写操作
3、A会话对某张表加了读锁之后,不能直接读其他表
4、如果一个会话对A表加了读锁,此时不能直接读B表,但是可以先对B表加读锁,然后再进行读B表。
5、对于某张表,可以多次加读锁。
6、对某张表加写锁后,其他会话是不能进行读写的,但是本会话是可以进行读写。
总结:
     读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞。
     特点:开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

InnoDB的行锁

#加读锁(Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的表,而且这些线程读取的是同一个版本的数据。
)
SELECT ... LOCK IN SHARE MODE;
#加写锁(Mysql会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。) 
SELECT ... for update;
1、左边会话开始事务,并对某行加读锁,其他会话可以进行读改行,但是不能写,但是可以写其他行。

间隙锁:进行锁定的时候,锁的是一个范围,再去执行写操作的时候,不能更新该范围的数据。

锁的总结:

不同的存储引擎支持不同的锁机制。

数据库中的锁从锁定的粒度上分可以分为行级锁、页级锁和表级锁。

MySQL的MyISAM引擎支持表级锁。

表级锁分为两种:共享读锁、互斥写锁。这两种锁都是阻塞锁。

可以在读锁上增加读锁,不能在读锁上增加写锁。在写锁上不能增加写锁。

默认情况下,MySql在执行查询语句之前会加读锁,在执行更新语句之前会执行写锁。

如果想要显示的加锁/解锁的话可以使用LOCK TABLES和UNLOCK来进行。

在使用LOCK TABLES之后,在解锁之前,不能操作未加锁的表。

范式设计

范式:设计数据库时必须遵循一定的规则

第一范式:保证每一列是原子的,不可再分的。

第二范式:确保表中的每列都和主键直接相关。

第三范式:确保每列都和主键列直接相关,而不是间接相关。

范式化设计的优缺点:

优点:

1)可以尽量得减少数据冗余
2)范式化的表通常比反范式化的表更小
3)范式化的数据库更新起来更加快;

不足:
1)范式化的表,在查询的时候经常需要很多join关联,增加让查询的代价。
2)更难进行索引优化

反范式化设计的优缺点:
优点:
1)可以减少表的关联
2)可以更好的进行索引优化

缺点:
1)存在数据冗余及数据维护异常
2)对数据的修改需要更多的成本

反范式化就是使用空间来换取时间

MyISAM InnoDB
是否支持事务
是否支持外键
行锁和表锁 表锁,即使操作一条记录,也会锁住整个表 行锁和表锁,操作时只锁某一行,不对其他行有影响
缓存 只缓存索引,不缓存真实数据 不仅缓存索引,还要缓存真实数据
表空间
关注点 性能 事务

可以根据以下的原则来选择 MySQL 存储引擎:

  • 如果要提供提交、回滚和恢复的事务安全(ACID 兼容)能力,并要求实现并发控制,InnoDB 是一个很好的选择。
  • 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存的 MEMORY 引擎中,MySQL 中使用该引擎作为临时表,存放查询的中间结果。
  • 如果数据表主要用来插入和查询记录,则 MyISAM 引擎提供较高的处理效率。

索引再谈

是否为主键

主键索引:以主键建立的索引树

辅助索引(非主键索引):以非主键建立的所以,唯一索引、普通索引、组合索引、全文索引。

根据结构进行划分

聚集索引:索引和数据是在一起存放的,两个文件 .frm .ibd, InnoDB

非聚集索引:索引和数据是分开存放的,三个文件.frm .myd .myi myISAM

查看表的存储引擎的方法

sudo cd /var/lilb/mysql
file customer.frm
file test myisam.frm
file test.memory.frm

InnoDB使用的聚集索引:数据与索引存在一起的。

主键建立索引树的话:

```sql
select * from student where id = x;#id name age

辅助索引树

select * from student where name = xxx;

select name from student where name = xxx;

select id from student where name = xxx;

select id,name from student where name = xxx;

select age from student where name = xxx;

回表:如果使用辅助索引进行查找的时候,假如某些列A不在辅助索引树上,此时需要通过辅助索引找到对应的主键,然后在主键索引树上找到上述列A,这就是回表。

索引覆盖:如果通过辅助索引树进行查找,而查找的这些列正好在辅助索引树上可以找到,此时就称为索引覆盖(覆盖索引)。

慢查询

慢查询日志:就是记录了查询比较慢(执行时间长)的SQL的日志。

mysql> show variables like '%long_query_tim%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> show variables like '%slow%';
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| log_slow_admin_statements | OFF                             |
| log_slow_slave_statements | OFF                             |
| slow_launch_time          | 2                               |
| slow_query_log            | ON                              |
| slow_query_log_file       | /var/lib/mysql/wangdao-slow.log |
+---------------------------+---------------------------------+
5 rows in set (0.00 sec)

mysql>
mysql>set global slow_query_log = ON;
mysql>set global  long_query_time = 10;

这篇关于Mysql锁及索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!