高性能mysql学习笔记

1. 常用mysql命令

• 启动关闭数据库

> net start mysql
> net stop mysql

• 登录

> mysql -hlocalhost -uroot -ppassword

• 查看数据库

> show databases
# 创建数据库
> create database databaseName
# 删除数据库
> drop database databaseName

• 选择使用某个数据库

> use databaseName

• 查看数据库中的表

> show tables
# 创建表
> create table tableName(
id int(11) not null auto_increment, 
primary key(id)
)
# 删除表
> drop table tableName
# 查看建表语句
> show crteat table tableName
# 查看表结构
> describe tableName

2. Explain解释器：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index（index说明了mysql使用索引扫描做了排序）和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引

key： 实际使用的索引。如果为NULL，则没有使用索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

3. mysql数据类型优化

• 整数类型（整数类型tinyint, smallint, mediumint, int, bigint分别占用1，2，3，4，8个字节空间)。整数类型可选unsigned属性使正数的上限提高一倍，有符号数与无符号数具有相同的存储空间以及相同的性能，整数类型可以指定宽度，但此宽度对存储大小以及性能并无影响，仅仅起到规定客服端的显示字符的个数；
• 实数类型（实数类型有float, double, decimal。mysql及指出精确类型也支持非精确类型），因为对浮点类型的计算需要额外的开销，所以尽量只在对小数进行精确计算时才使用decimal类型，当数据量较大时，可考虑用bigint代替decimal， 将小数对应的位数乘以相应的倍数
• 字符串类型（varchar-可变长字符串，char定长字符串），varchar的优点：varchar类型仅使用必要的空间相比定长类型char更节省空间 ，varchar的缺点：当存储相同大小的字符串时varchar类型比char类型多用一个或两个字节来保存字符串的长度，当对varchar类型的字段值进行更新操作时，可能由于修改之后的字段太长导致页分裂，影响性能。字符串类型可以指定宽度，其宽度指的时可以存储的字符个数（无论是数字、字母、还是汉字（在utf8编码模式下，一个汉字占用三个字节）
• 日期和时间类型（datetime(与时区无关占用8个字节)和timestamp（与时区有关占用4个字节））

4. 数据库设计三大范式

第一范式，每一列属性都是不可再分的属性，确保每一列的原子性

第二范式，每一行的数据只能与其中一列相关，一行数据只作一件事，主要针对联合主键的情况，（解决方式可对表进行拆分）

第三范式，数据不能存在传递关系，既每个属性都应该与主键有之间关系而不是间接关系

范式的优点：因为没有重复数据，所以更新操作较快。在非范式中通过distinct或group by查询信息的情景在范式中只需查询一张单独的表就行了

范式的缺点：稍微复杂一点的语句可能就需要关联查询

反范式的优点：避免了不需要的关联查询

反范式的缺点：数据冗余

5. 加快alter table的速度

mysql对表执行alter操作的方法是根据新的表结构创建一张新表，并将旧表的数据拷贝到新的表中，然后删除旧表，当表很大或索引很多时需要花费很长时间，

解决方式有两种，一种是在不提供服务的服务器上根据需求创建表并同步数据，进行主备切换，还有一种技巧是影子拷贝，通过创建一张要求的表结构的表，通过删除操作和重命名交换两张表

6. 创建高性能的索引

独立的列：在where条件中索引列必须是独立的，不能是表达式的一部分，否则无法使用索引

前缀索引：当索引列的长度太长时，为了提高索引的性能可以使用前缀索引。使用前缀索引时尽量保证前缀选择性接近完整列的选择性

索引选择性：不重复的索引值与数据表的记录总数的比较

多列索引：遵循最左前缀列匹配原则

聚簇索引：聚簇索引不是一个单独的索引类型，而是一种数据存储方式。innoDB中聚簇索引实际上在同一结构中保存了B-Tree索引以及数据行。当表中有聚簇索引时数据行实际上是存放在索引的叶子页上（innoDB通过主键聚簇数据， 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，innoDB会隐式的定义一个主键作为聚簇索引）优点：1. 降低一对多查询时的磁盘i/o次数，2. 通过聚簇索引查询时比非聚簇索引查找更快，缺点：1. 导致二级索引较大，二级索引的叶子节点包含的引用行的主键列。2. 聚簇索引时更新索引列的代价高，强制innoDB将被更新的行移动到新的位置

覆盖索引：要查询的列包含在索引列中，避免了二次查询，速度更快

索引和锁：索引可以使查询锁定更少的行，提高数据库的并发能力

7. mysql并发控制

事务操作流程，在mysql内部通过二阶段提交保证数据库binlog以及redo log之间的数据一致性，事务分为两个阶段，准备阶段（prepare）以及提交阶段（commit）.

准备阶段：写入undo日志以及redo日志，并将undo日志的状态置为TRX_UNDO_PREPARED
提交阶段：写入binlog日志，将redo的状态置为TRX_UNDO_TO_FREE或TRX_UNDO_TO_PURGE

当事务执行过程中宕机，mysql会通过binlog、redo、undo的状态判断数据是否因该提交，当binlog日志写入的时候提交事务，当binlog日志未写入undo日志的状态为TRX_UNDO_PREPARED时回滚事务

并发事务带来的问题：1、丢失修改 2、不可重复读 3、读脏数据 4、幻读

产生上述问题的原因主要是并发操作破坏了事务的隔离性

并发事务的主要技术有封锁、时间戳、乐观控制法和多版本并发控制

封锁：基本的锁类型有共享锁（读锁）、排他锁（写锁）。

一级封锁协议：事务在修改数据之前必须先对其加排他锁，知道事务结束后才释放，包括正常结束以及非正常结束（避免丢失修改）

二级封锁协议：在一级封锁协议的基础上增加事务在读取数据之前必须对其加共享锁，读完即可释放共享锁（避免读取脏数据）

三级封锁协议：在一级封锁协议的基础上增加事务在读取数据之前必须对其加共享锁，直到事务结束后才释放（避免不可重复度）

活锁和死锁：1、活锁：当事务T1封锁数据R，事务T2又请求封锁数据R，于是T2等待，事务T3也请求封锁事务R，当事务T1释放的时候首先批准了T3的请求，于是T2依然等待。（避免活锁的简单方式是使用先来先服务的策略）2、死锁：死锁产生的四大条件（互斥条件、请求保持条件、不可剥夺条件、循环等待）。避免死锁的原因（顺序加锁、一次性加锁-一次性加锁扩大了加锁范围。会降低系统并发度）

串行化执行的两段锁协议：第一阶段获取锁阶段、第一阶段释放锁阶段。（两段锁协议是串行化调度的充分条件）

意向锁：如果一个节点含有意向锁则说明它的下级节点正在被加锁，对任一节点加锁时必须对它的上级节点加意向锁，意向锁有（意向共享锁、意向排他锁、共享意向排他锁）

乐观控制法：不加任何锁，在事务提交时验证版本号

多版本并发控制：增加两个列创建版本以及删除版本，每次事务操作的时候将全局事务id作为数据的创建版本和删除版本，并将update操作分解为两步执行（insert，delete），每次夺取操作的时候只能读取创建时间小于当前版本号的数据