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）对数据的修改需要更多的成本

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

可以根据以下的原则来选择 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锁及索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！