MySQL基础学习

Mysql 引擎基础知识

• MyISAM: 默认的插件式存储引擎， 在web数据仓库的环境下，使用最广泛的存储引擎之一。
• InnoDB： 用于事务处理程序， 包括ACID事务， 提供行级锁
• BDB：可代替InnoDB， 支持COMMIT ROLLBACK
• Memory：将所有数据保存在内存中，提供快速访问
• Archive：为大量很引用的历史归档、安全审计信息提供存储 与检索
• Federated： 从多个服务器建立一个逻辑数据库，适合分布式与数据集市环境
• Cluster/NDB： 簇式查询引擎，审核高查询要求，高可用性。

各存储引擎间的区别

• 核心功能：

  1. 支持的字段和数据类型
  2. 锁定类型
  3. 索引
  4. 处理

• MyISAM： 需要定期运行 OPTIMIZE TABLE 来优化空间
  缺点： 表损坏后不可恢复
  优点： 取数据效率高

• InnoDB： 数据安全， 但处理效率差

锁

分为表级锁， 页级锁， 行级锁

• 表级锁（MyISAM）：
  粒度最大，实现逻辑简单，带来的系统负面影响小，可以很好地避免死锁，但并行度会降低（因为在执行事务前就会施加表级锁，而行级锁由InnoDB自动捕获，表级锁在执行sql时由BDB捕获，因此可能发生资源竞争）

• 行级锁(InnoDB)
  目前所有已实现的各大数据库中粒度最小，所以发生资源争用的概率也最小，但消耗大，也最容易发生死锁

• 页级锁(BDB)
  开销在表级锁与行级锁之间，并发度也在表级锁和行级锁之间，可能会发生死锁

各级别锁适用的场景：

表级锁：以查询为主，少量以索引条件更新
行级锁：大量以索引条件的并发更新，同时，也有查询需求（在线事务处理）
页级锁：

Mysql的表级锁实现逻辑

mysql的表级锁定主要通过四个队列来实现：

• current read-lock queue (lock -> read)
• pending read-lock queue (lock -> read_wait)
• current write-lock queue (lock -> write)
• pending reak-lock queue (lock -> write_wait)

通过以上四个队列实现了两种模式：

1. 表共享读锁
2. 表独占写锁

在实现过程中：不需要显式加锁

mysql优化建议： 尽量减少锁定时间，让可能并发的操作尽可能并发
即：

1. 减少复杂查询，拆成几个简单查询
2. 建立高效索引
3. 控制字段类型
4. 优化表数据文件

mysql的行级锁

1. 共享锁
2. 排他锁

共享锁：
又名读锁，只能读，不能写；
可以叠加共享锁，不可以再叠加别的锁
用法：sql 后 + LOCK IN SHARE MODE 结果集数据会加共享锁

排他锁：
某个事务加了排他锁，则只能该事务进行读写，其他事务也不能再加锁

意向共享锁、意向排他锁：
这两个锁均用于额外的表级锁，在加共享锁时为表加意向共享锁；可以有多个锁
加排他锁时，为表加意向排他锁只可以有一个

InnoDB行级锁的实现方式：

InnoDB的行锁是通过给索引项添加锁而实现的，只有通过索引进行数据检索才会用到行锁，否则全部为表锁。

若不同行使用了相同的索引键，会发生锁冲突；
有多个索引时，可以使用不同的索引锁定不同的行；
即使条件中使用了索引，Mysql进行执行计划优化时仍然可能使用表锁。

间隙锁

当使用范围查询时，mysql会为这个范围加锁，不论表中是否实际存在该行。

	目的：1. 防止幻读
	     2. 满足恢复与复制的要求 

死锁

Mysql 不会出现死锁，因为他的逻辑是要么一次获得全部的锁，要么等待。

而InnoDB是逐步获得锁，因此，较易发生死锁，但是，InnoDB拥有锁检测机制，可以很快地通过该机制发现死锁，通过回滚小事务来解除死锁。

但只能发现引擎内部的死锁，无法识别跨表的死锁。

MySQL基础学习

• 修改SQL提示符

连接客户端时通过参数指定

shell>mysql -uroot -proot --prompt

连接上客户端后，通过prompt 命令修改

mysql>prompt 提示符

参数	描述
\D	完整日期
\d	当前数据库
\h	服务器名称
\u	当前用户

MySQL常用指令

• 显示当前服务器版本
• SELECT VERSION();
• 显示当前日期
• SELECT NOW();
• 显示当前用户
• SELECT USER();

MySQL语句的规范

• 关键字与函数名称全部大写
• 数据库名称、表名称、字段名称全部小写
• SQL语句必须以分号结尾

操作数据库

创建数据库

CREATE {DATABASE | SCHEMA} [ IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

---