开发人员学习mysql心得

前言

 

公司的mmo web game 玩家越来越多，每个表的数据量都突破千万级别了。虽然进行了分表映射处理，每个表还是有百万数据。每次在程序中要进行数据库操作都提心吊胆。一不小心可能就遇上性能瓶颈。

为此，这段时间特意系统学习的MySQL的相关特性。当然，我不是DBA，也不想抢DBA的饭碗。所以，只是站在开发人员的角度学习MySQL。在MySQL管理，内部性能调优，参数设置方面不会涉及很多。主要精力还是在数据库引擎的选择，表结构的优化，索引，锁，事务，查询优化等等。

参考资料主要有以下：《MySQL 5.1参考手册》《The.Definitive.Guide.to.MySQL.5.3rd.Edition》，以及网上的相关资料。

 

以下的内容只是个人的一些学习心得，可能肤浅，甚至错漏，欢迎指正。请勿随意转载，以免误人子弟。

 

 

数据库的一些基本概念

 

大学时候有门课叫《数据库系统》，当时觉得有些bored，老师在上面整天讲一些理论，都是关系数据理论，数据模型，实体关系等等，以至于学完那门课，我们连基本的SQL语句都不熟练。当时便觉得这门课没点价值，概念经过考试后又还给老师了。

 

经过一些项目的开发历练后，回头去翻翻那本书，发现很多东西豁然开朗。下面摘抄一些概念留作忘切的纪念吧。

 

数据的三种范畴：数据从现实生活进入到数据库实际经历了三个阶段，分别是：现实世界阶段、信息世界阶段和机器阶段。每个阶段有各自的表述。

 

E-R(Entity-Relationship) :包括实体、联系（一对一，一对多，多对多）、属性。

 

笛卡尔积：设D1,D2,……Dn为任意集合，定义D1，D2，……Dn的笛卡尔积为：

        D1*D2*……*Dn={（d1，d2，……dn）}

用数学语言来定义概念其实是很糟糕的事情，经常将简单的东西复杂化，让外行人望而生畏。

举个最简单的例子。

表A包括一个字段P      表B包括一个字段Q

p1                        q1

         p2                        q2

两者的笛卡尔积就是     

                        p1 q1

                        p1 q2

                        p2 q1

                        p2 q2

 

关系模型的完整性约束：

实体完整性：若属性A是关系R的主属性，则属性A不可以为空值  ----就是数据库中表的主键作用。

参照完整性：如果属性（或者属性组）F是关系R的外部关键字，它与关系S的主关键字K相对应，则对于关系R中每个元组在属性（或者属性组）F上的值必须为：

  或者取空值（F的每个属性均为空值）

  或者等于S中某个元组的主关键字的值。

（--------一言以蔽之：一个外键必须为空或者等于所对应的表的主键）

用户定义完整性：用户创建表时候设定的规则

 

每当数据库有任何改动时，都会坚持这些完整性约束。比如：你删除一个Foreign Key时，对应表的记录要怎么处理呢？

 

连接运算：

等值连接：计算需要连接表的笛卡尔积；选择满足条件（相等）的所有元组

自然连接：在等值连接的基础上去掉重复的属性。

 

索引：

普通索引：如果没有指定UNIQUE 或 CLUSTERED 等将建立普通索引

唯一索引：通过指定UNIQUE为表创建唯一索引，如果已经存在数据，再建索引，数据中存在不唯一将会导致索引创建失败。每次INSERT 或 UPDATE 操作也要进行唯一性检查

聚集索引：通过指定CLUSTERED建立聚集索引。聚集索引的索引值与关系中元组在物理上保持一致。一个表只能创建一个聚集索引。Primary Key约束将隐式创建聚集索引。最好在创建任何索引之前创建聚集索引。因为创建聚集索引，将重建现有的非聚集索引。在频繁更新的列上尽量不要建立聚集索引。

 

连接运算：

广义笛卡尔积： SELECT * FROM TABLE1 CROSS JOIN TABLE2 WHERE…

内连接：  [INNER] JOIN  ON  等效于传统的连接操作

外连接： SELECT * FROM TABLE LEFT|RIGHT|FULL|[OUTER] JOIN

         左连接包括第一个表的全部满足条件的记录

         右连接包括第二个表的全部满足条件的记录

         全连接包括所有表的全部满足条件的记录

 

事务的ACID 性质：

Atomicity原子性 Consistency一致性 隔离线 Isolation 持久性 Durability

 

并发事务包括：

丢失更新问题：比如买票，用户A，查询到某种票仍未售出，便买下。以此同时，用户B跟用户A同时也查到某张票未售出，在A买下后也埋下，导致一张票买了两次，铁老大赚了。

未提交依赖问题：又称为“Dirty Read”。查询一个已经被其他事务更新，但尚未提交的元组，将会引起未提交依赖问题。

不一致分析问题：又称为“不可重复读”,很多应用可能需要检验功能，往往需要进行多次读数据进行校验和分析，由于其他事务的干扰，导致每次读的数据不一致

幻象读问题：当事务A读数据时，事务B同时进行插入或者删除数据，导致A再次读的时候，发现多了或者少了数据。

 

封锁机制：

1.       共享锁S，也称作读锁。读的时候拒绝其他用户的任何修改操作。可以在同一个记录上加多个共享锁

2.       独占锁：X又叫做排它锁，写锁，在进行插入修改或者删除时候，上锁，拒绝其他用户的任何锁，不拒绝一般的查询操作

3.       更新锁：只是更新时候上锁。拒绝其他锁，不拒绝一般的查询操作

 

隔离级别：

未提交读（READ UNCOMMITTED）:事务隔离级别的最低级，仅可保证不读取物理损坏的数据，这是四个隔离级别中限制最小的级别

提交读（READ COMMITTED）: 保证不读取“Dirty” 数据

可重复读（REPEATABLE READ）:可以保证读一致性，避免不一致分析问题

可串行化（SERIALIZABLE）: 事务隔离的最高级别，事务之间完全隔离；如果食物在可串行化隔离级别上运行，则可保证任何并发重叠事务均是串行的

规范化：

第一范式：1NF

关系的所有分量都必须是不可分的最小数据项。

第二范式：2NF

每个非主属性都完全函数依赖于关键字。---例如关键字（A，B）-》c，d  而 A-》C也成立就不满足第二范式了

第三范式：3NF

属于第二范式，且所有分主属性不传递依赖于关键字。

BC范式：

属于第三范式，并且不存在主属性对其他属性的函数依赖。

第四范式：

属性间不允许有非平凡、且非函数依赖的多值依赖。

 

模式分解的准则：

模式分解具有无损连接线；

模式分解能够保持函数依赖。

 

 

MySQL数据库引擎

 

MySQL提供了各种插件式数据库引擎，开发者可以根据不同的需求为每个数据库表选择不同的引擎。我们先看看各种数据库引擎支持的特性

  

现在主流的数据库引擎应该是MyISAM和InnoDB，分别具有以下特性：

MyISAM

是默认存储引擎。它基于更老的ISAM代码，增加了很多有用的扩展

1.       所有数据值先存储低字节。使得数据机和操作系统分离。这样做也不会严重的影响速度，数据行中的字节一般是未联合的，从一个方向读未联合的字节并不比从方向读占用更多的资源。

2.       大文件支持（63位文件长度）

3.       删除、更新和插入混合操作时，产生更少的碎片。这是因为通过合并相邻被删除的块，当下一个块被删除时，就扩展到下一块自动完成。

4.       每个MyISAM表默认的最大索引数是64

5.       最大的key长度是1000字节。当key长度超过250字节时，一个超过1024字节的key块被使用

6.       BLOB和TEXT列可以被索引

7.       NULL值被允许在索引列，占每个键的0-1个字节

8.       数字键值的高字节先存储

9.       可以将数据文件和索引文件放在不同的目录

10.   MyISAM表使用BTREE索引，粗略计算索引文件的大小为（keyLength+4）/0.67

11.   字符串索引时被空间压缩的。如果第一个字符串索引部分是字符串，它也被加前缀压缩。

InnoDB

InnoDB支持事务

锁的粒度是行级别

所有InnoDB的索引是B树，其中索引记录被存储在树的叶子节点。一个索引页默认的大小事16k。当新纪录被插入，InnoDB试着将索引记录插入和更新剩下十六分之一的空白页。如果索引记录以连续的顺序被插入，结果索引页大约是15/16满。

支持外键关联

Crash Recovery 效果良好

MyISAM中，每张表都存在各自的文件中。而InnoDB存储所有数据和索引在一个表空间中，包括若干个文件。这些文件不能变小了。只能通过mysqldump备份，而不是直接拷贝文件备份

不支持全文索引

Record size：数据最多占8000字节。当TEXT和BloB不受限制，因为他们只是存了开始的512字节在数据表文件中。其他的内容存在单独的master空间表中

由于支持事务的原因，导致count操作比MyISAM要慢

在InnoDB中尽量避免直接使用LOCK TABLE….READ/WRITE 而是用SELECT…IN SHARE MODE 或者SELECT … FOR UPDATE

MySQL本身的数据库关联表也不支持InnoDB

 

MyISAM通常适用于空间时间效率要求较高，查询为主

而InnoDB适用于事务操作，安全性要求高，多用户高并发操作（实时修改更新）

 

Heap引擎不支持xxxTEXT 或者xxxBLOB，只能进行等式查找。AUTO_INCREMENT也不支持。索引只能建在NOT NULL。一般应用于少量数据的高速查询。

 

在数据库应用中，主键是一个唯一的识别符，并且新行被以主键的升序来插入，这是个常见的情况。因此，到集束索引的插入不需要从一个磁盘随机读。

另一方面，第二索引通常是非唯一的，到第二索引的插入以相对随机次序发生。这可能会导致大量的随机磁盘I/O操作，而没有一个被用在InnoDB中的专用机制。

“如果一个索引记录应该被插入到一个非唯一第二索引，InnoDB检查第二索引页是否在缓冲池中。如果是，InnoDB直接插入到索引页。如果索引页没有在缓冲池中被发现，InnoDB插入记录到一个专门的插入缓冲结构。插入缓冲被保持得如此小以至于它完全适合在缓冲池，并且可以非常快地做插入。

插入缓冲周期地被合并到数据库中第二索引树里。把数个插入合并到索引树的同一页，节省磁盘I/O操作，经常地这是有可能的。据测量，插入缓冲可以提高到表的插入速度达15倍。 ”—摘自参考手册，翻译得一般，不知所云。

 

 

优化数据库结构：

1.    MySQL将数据和索引数据保存在不同的文件中

2.    使数据尽可能小。

3.    尽可能使用最有效（最小）的数据类型

4.    尽可能使用较小的整数类型使表更小

5.    如果可能，声明列为NOT NULL.它使任何事情更快而且每列可以节省一位

6.    对于MyISAM表，如果没有变长数据，使用固定尺寸的记录格式

7.    InnoDB表中使用更紧凑的存储格式。

8.    每张表的主索引应该尽可能短。

9.    只创建你确实需要的索引。索引对查询有效，对插入和更新却是灾难。

10.如果可能在一个索引的头几个字符上有唯一的前缀，仅仅使用该前缀作为索引就行啦。

11.当索引一个BLOB或者TEXT列，你必须为索引指定前缀长度。

 

MySQL如何使用索引

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

字符串总是自动地压缩前缀和结尾空格

以下情况索引将被用到：

快速找出匹配一个Where字句的行。

删除行。如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引

执行联接时，从其他表检索行

对具有索引列进行Max（）或Min（）操作

对关键字的最左面前缀进行排序或者分组

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; 如果col1和col2上存在一个多列索引，可以直接读出相应行。如果存在单列索引，优化器试图通过决定哪个索引更快来使用

如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较

Hash索引还有一些其它特征：

·         它们只用于使用=或<=>操作符的等式比较(但很快)。它们用于比较操作符，例如发现范围值的<。

·         优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目）。

·         MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。

·         只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行）。

 

行级锁定的优点：

·         当在许多线程中访问不同的行时只存在少量锁定冲突。

·         回滚时只有少量的更改。

·         可以长时间锁定单一的行。

行级锁定的缺点：

·         比页级或表级锁定占用更多的内存。

·         当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

·         用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

在以下情况下，表锁定优先于页级或行级锁定：

·         表的大部分语句用于读取。

·         对严格的关键字进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：

·                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

·                DELETE FROM tbl_name WHERE unique_key_col=key_value;

·         SELECT 结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语句。

·         在整个表上有许多扫描或GROUP BY操作，没有任何写操作。

¡       VARCHAR(N),N<4经常被优化成CHAR(N)

¡       CHAR（N），N>3经常被优化成VARCHAR(N)，当且仅当表中存在其他var变量时

¡       索引对于InnoDB表尤其重要，因为这样可以使得行锁的优势得到重复体现

¡       Where column！= 索引无效

¡       经过函数出来的列索引无效

¡       Join连接运算中，只有主键和外键的数据类型一致时，索引方有效

¡       Like 和regexp 使用索引开头不能带%

¡       Indexes are used with ORDER BY operations only if the records do not have to be previously selected by other criteria. (Unfortunately, an index rarely helps to speed up ORDER BY with queries in which the records are taken from several tables.)

¡       对于列的值都是大部分一致的，索引无效

¡       MyISAM 适用于选择密集和插入密集的表。在大量数据中筛选迅速，甚至在高流量环境中也是如此。并发插入特性允许同时选择和插入数据。例如很适合管理邮件和web服务器日志数据

¡       MyISAM分为静态、动态和压缩。静态的效率最高，但是空间需求较大。而动态性能下降，某个字段的内容发生改变，其位置就需要移动，这会导致碎片的产生。随着数据集中的碎片增加，数据访问性能就会相应降低。有两种修复方法：尽可能使用静态数据，经常使用OPTIMIZE TABLE 语句，整理表的碎片

¡       MyISAM压缩适用于整个生命周期都只读的表。用myisampack将其转换成压缩包减少空间

¡       COUNT(*)按主键进行计算 count（col）忽略null