一. 数据切分

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分

1、垂直（纵向）切分

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

垂直切分的优点：

解决业务系统层面的耦合，业务清晰
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）

2、水平（横向）切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平切分的优点：

不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块

缺点：

跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量极大

水平切分后同一张表会出现在多个数据库/表中，每个库/表的内容不同。几种典型的数据分片规则为：

1、根据数值范围

按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1~9999的记录分到第一个库，10000~20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

这样的优点在于：

单表大小可控
天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

2、根据数值取模

一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

优点：

数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈

缺点：

后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

二. 分库分表带来的问题

分库分表能有效的环节单机和单库带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶颈，同时也带来了一些问题。下面将描述这些技术挑战以及对应的解决思路。

1、事务一致性问题

分布式事务

当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，没有简单的方案，一般可使用"XA协议"和"两阶段提交"处理。

分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

最终一致性

对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。

2、跨节点关联查询 join 问题

切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。

解决这个问题的一些方法：

1）全局表

全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

2）字段冗余

一种典型的反范式设计，利用空间换时间，为了性能而避免join查询。例如：订单表保存userId时候，也将userName冗余保存一份，这样查询订单详情时就不需要再去查询"买家user表"了。

但这种方法适用场景也有限，比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证，就像上面订单表的例子，买家修改了userName后，是否需要在历史订单中同步更新呢？这也要结合实际业务场景进行考虑。

3）数据组装

在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

4）ER分片

关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题。在1:1或1:n的情况下，通常按照主表的ID主键切分。如下图所示：

这样一来，Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了，Data Node2上也一样。

3、跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。如图所示：

上图中只是取第一页的数据，对性能影响还不是很大。但是如果取得页数很大，情况则变得复杂很多，因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体的排序，这样的操作时很耗费CPU和内存资源的，所以页数越大，系统的性能也会越差。

在使用Max、Min、Sum、Count之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。如图所示：

4、全局主键避重问题

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

1）UUID

UUID标准形式包含32个16进制数字，分为5段，形式为8-4-4-4-12的36个字符，例如：550e8400-e29b-41d4-a716-446655440000

UUID是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，由于UUID非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在InnoDB下，UUID的无序性会引起数据位置频繁变动，导致分页。

2）结合数据库维护主键ID表

在数据库中建立 sequence 表：

CREATE TABLE `sequence` (  
  `id` bigint(20) unsigned NOT NULL auto_increment,  
  `stub` char(1) NOT NULL default '',  
  PRIMARY KEY  (`id`),  
  UNIQUE KEY `stub` (`stub`)  
) ENGINE=MyISAM;

stub字段设置为唯一索引，同一stub值在sequence表中只有一条记录，可以同时为多张表生成全局ID。sequence表的内容，如下所示：

+-------------------+------+  
| id                | stub |  
+-------------------+------+  
| 72157623227190423 |    a |  
+-------------------+------+

使用 MyISAM 存储引擎而不是 InnoDB，以获取更高的性能。MyISAM使用的是表级别的锁，对表的读写是串行的，所以不用担心在并发时两次读取同一个ID值。

当需要全局唯一的64位ID时，执行：

REPLACE INTO sequence (stub) VALUES ('a');  
SELECT LAST_INSERT_ID();

这两条语句是Connection级别的，select last_insert_id() 必须与 replace into 在同一数据库连接下才能得到刚刚插入的新ID。

使用replace into代替insert into好处是避免了表行数过大，不需要另外定期清理。

此方案较为简单，但缺点也明显：存在单点问题，强依赖DB，当DB异常时，整个系统都不可用。配置主从可以增加可用性，但当主库挂了，主从切换时，数据一致性在特殊情况下难以保证。另外性能瓶颈限制在单台MySQL的读写性能。

flickr团队使用的一种主键生成策略，与上面的sequence表方案类似，但更好的解决了单点和性能瓶颈的问题。

这一方案的整体思想是：建立2个以上的全局ID生成的服务器，每个服务器上只部署一个数据库，每个库有一张sequence表用于记录当前全局ID。表中ID增长的步长是库的数量，起始值依次错开，这样能将ID的生成散列到各个数据库上。如下图所示：

由两个数据库服务器生成ID，设置不同的auto_increment值。第一台sequence的起始值为1，每次步长增长2，另一台的sequence起始值为2，每次步长增长也是2。结果第一台生成的ID都是奇数（1, 3, 5, 7 ...），第二台生成的ID都是偶数（2, 4, 6, 8 ...）。

这种方案将生成ID的压力均匀分布在两台机器上。同时提供了系统容错，第一台出现了错误，可以自动切换到第二台机器上获取ID。但有以下几个缺点：系统添加机器，水平扩展时较复杂；每次获取ID都要读写一次DB，DB的压力还是很大，只能靠堆机器来提升性能。

可以基于flickr的方案继续优化，使用批量的方式降低数据库的写压力，每次获取一段区间的ID号段，用完之后再去数据库获取，可以大大减轻数据库的压力。如下图所示：

还是使用两台DB保证可用性，数据库中只存储当前的最大ID。ID生成服务每次批量拉取6个ID，先将max_id修改为5，当应用访问ID生成服务时，就不需要访问数据库，从号段缓存中依次派发0~5的ID。当这些ID发完后，再将max_id修改为11，下次就能派发6~11的ID。于是，数据库的压力降低为原来的1/6。

3）Snowflake分布式自增ID算法

Twitter的snowflake算法解决了分布式系统生成全局ID的需求，生成64位的Long型数字，组成部分：

第一位未使用
接下来41位是毫秒级时间，41位的长度可以表示69年的时间
5位datacenterId，5位workerId。10位的长度最多支持部署1024个节点
最后12位是毫秒内的计数，12位的计数顺序号支持每个节点每毫秒产生4096个ID序列

这样的好处是：毫秒数在高位，生成的ID整体上按时间趋势递增；不依赖第三方系统，稳定性和效率较高，理论上QPS约为409.6w/s（1000*2^12），并且整个分布式系统内不会产生ID碰撞；可根据自身业务灵活分配bit位。

不足就在于：强依赖机器时钟，如果时钟回拨，则可能导致生成ID重复。

综上

结合数据库和snowflake的唯一ID方案，可以参考业界较为成熟的解法：Leaf——美团点评分布式ID生成系统，并考虑到了高可用、容灾、分布式下时钟等问题。

5、数据迁移、扩容问题

当业务高速发展，面临性能和存储的瓶颈时，才会考虑分片设计，此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据，然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和QPS，以及业务发展的速度，进行容量规划，推算出大概需要多少分片（一般建议单个分片上的单表数据量不超过1000W）

如果采用数值范围分片，只需要添加节点就可以进行扩容了，不需要对分片数据迁移。如果采用的是数值取模分片，则考虑后期的扩容问题就相对比较麻烦。

三. 什么时候考虑切分

下面讲述一下什么时候需要考虑做数据切分。

1、能不切分尽量不要切分

并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。

不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

2、数据量过大，正常运维影响业务访问

这里说的运维，指：

1）对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的

2）对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。

3）大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力

3、随着业务发展，需要对某些字段垂直拆分

举个例子，假如项目一开始设计的用户表如下：

id                   bigint             #用户的ID
name                 varchar            #用户的名字
last_login_time      datetime           #最近登录时间
personal_info        text               #私人信息
.....                                   #其他信息字段

在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从10w激增到10亿，用户非常的活跃，每次登录会更新 last_login_name 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。

personal_info 属性是更新和查询频率较低的，并且text字段占据了太多的空间。这时候，就要对此垂直拆分出 user_ext 表了。

4、数据量快速增长

随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量

5、安全性和可用性

鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到100%的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。

四. 案例分析

1、用户中心业务场景

用户中心是一个非常常见的业务，主要提供用户注册、登录、查询/修改等功能，其核心表为：

User(uid, login_name, passwd, sex, age, nickname)

uid为用户ID,  主键
login_name, passwd, sex, age, nickname,  用户属性

任何脱离业务的架构设计都是耍流氓，在进行分库分表前，需要对业务场景需求进行梳理：

用户侧：前台访问，访问量较大，需要保证高可用和高一致性。主要有两类需求：
- 用户登录：通过login_name/phone/email查询用户信息，1%请求属于这种类型
- 用户信息查询：登录之后，通过uid来查询用户信息，99%请求属这种类型
运营侧：后台访问，支持运营需求，按照年龄、性别、登陆时间、注册时间等进行分页的查询。是内部系统，访问量较低，对可用性、一致性的要求不高。

2、水平切分方法

当数据量越来越大时，需要对数据库进行水平切分，上文描述的切分方法有"根据数值范围"和"根据数值取模"。

"根据数值范围"：以主键uid为划分依据，按uid的范围将数据水平切分到多个数据库上。例如：user-db1存储uid范围为0~1000w的数据，user-db2存储uid范围为1000w~2000wuid数据。

优点是：扩容简单，如果容量不够，只要增加新db即可。
不足是：请求量不均匀，一般新注册的用户活跃度会比较高，所以新的user-db2会比user-db1负载高，导致服务器利用率不平衡

"根据数值取模"：也是以主键uid为划分依据，按uid取模的值将数据水平切分到多个数据库上。例如：user-db1存储uid取模得1的数据，user-db2存储uid取模得0的uid数据。

优点是：数据量和请求量分布均均匀
不足是：扩容麻烦，当容量不够时，新增加db，需要rehash。需要考虑对数据进行平滑的迁移。

3、非uid的查询方法

水平切分后，对于按uid查询的需求能很好的满足，可以直接路由到具体数据库。而按非uid的查询，例如login_name，就不知道具体该访问哪个库了，此时需要遍历所有库，性能会降低很多。

对于用户侧，可以采用"建立非uid属性到uid的映射关系"的方案；对于运营侧，可以采用"前台与后台分离"的方案。

3.1、建立非uid属性到uid的映射关系

1）映射关系

例如：login_name不能直接定位到数据库，可以建立login_name→uid的映射关系，用索引表或缓存来存储。当访问login_name时，先通过映射表查询出login_name对应的uid，再通过uid定位到具体的库。

映射表只有两列，可以承载很多数据，当数据量过大时，也可以对映射表再做水平切分。这类kv格式的索引结构，可以很好的使用cache来优化查询性能，而且映射关系不会频繁变更，缓存命中率会很高。

2）基因法

分库基因：假如通过uid分库，分为8个库，采用uid%8的方式进行路由，此时是由uid的最后3bit来决定这行User数据具体落到哪个库上，那么这3bit可以看为分库基因。

上面的映射关系的方法需要额外存储映射表，按非uid字段查询时，还需要多一次数据库或cache的访问。如果想要消除多余的存储和查询，可以通过f函数取login_name的基因作为uid的分库基因。生成uid时，参考上文所述的分布式唯一ID生成方案，再加上最后3位bit值=f(login_name)。当查询login_name时，只需计算f(login_name)%8的值，就可以定位到具体的库。不过这样需要提前做好容量规划，预估未来几年的数据量需要分多少库，要预留一定bit的分库基因。

3.2、前台与后台分离

对于用户侧，主要需求是以单行查询为主，需要建立login_name/phone/email到uid的映射关系，可以解决这些字段的查询问题。

而对于运营侧，很多批量分页且条件多样的查询，这类查询计算量大，返回数据量大，对数据库的性能消耗较高。此时，如果和用户侧公用同一批服务或数据库，可能因为后台的少量请求，占用大量数据库资源，而导致用户侧访问性能降低或超时。

这类业务最好采用"前台与后台分离"的方案，运营侧后台业务抽取独立的service和db，解决和前台业务系统的耦合。由于运营侧对可用性、一致性的要求不高，可以不访问实时库，而是通过binlog异步同步数据到运营库进行访问。在数据量很大的情况下，还可以使用ES搜索引擎或Hive来满足后台复杂的查询方式。

五. 支持分库分表中间件

站在巨人的肩膀上能省力很多，目前分库分表已经有一些较为成熟的开源解决方案：

六. 参考

数据库分布式架构扫盲——分库分表（及银行核心系统适用性思考）

数据库水平切分架构实践-【架构师之路】公众号

mysql 自增id和UUID做主键性能分析，及最优方案_XDSXHDYY的博客-CSDN博客_pgsql uuid做主键性能

mysql 自增id和UUID做主键性能分析，及最优方案

1.为什么会想到用uuid做主键

(1).其实在innodb存储引擎下，自增长的id做主键性能已经达到了最佳。不论是存储和读取速度都是最快的，而且占的存储空间也是最小。

(2).但是在我们实际到项目中会碰到问题，历史数据表的主键id会与数据表的id重复，两张自增id做主键的表合并时，id一定会有冲突，但如果各自的id还关联了其他表，这就很不好操作。

(3).如果使用UUID，生成的ID不仅是表独立的，而且是库独立的。对以后的数据操作很有好处，可以说一劳永逸。

2.UUID优缺点

缺点： 1. 影响插入速度，并且造成硬盘使用率低
2. uuid之间比较大小相对于自增id比较大小要慢很多，影响查询速度。
3. uuid占空间大，如果你建的索引越多，影响越严重

优点：出现数据拆分、合并存储的时候，能达到全局的唯一性

3.最优方案

(1).InnoDB引擎表是基于B+树的索引组织表。

(2).B+树：B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

(3).InnoDB主索引：叶节点包含了完整的数据记录。这种索引叫做聚集索引。InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引

(4).聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

综合上述可得：

(1).如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。

(2).对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。为了全局的唯一性，应该用uuid做索引关联其他表或做外键。

4.如果非要使用uuid做主键，下面是小建议：

如果是主从即M-S模式，最好是不使用mysql自带函数uuid来生成唯一主键，因为主表生成的uuid要再关联从表时，需要再去数据库查出这个uuid，需要多进行一次数据库交互，而且在这个时间差里面主表很有可能还有数据生成，这样就很容易导致关联的uuid出错。如果真要使用uuid，可以在Java中生成后，直接存储到DB里，这时主从的uuid就是一样的了！

http://blog.csdn.net/XDSXHDYY/article/details/78994045

MySQL优化详解_倾城一笑stu-CSDN博客

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

一、存储引擎

在创建表的时候我们使用sql语句，Create table tableName () engine=myisam|innodb;

这里就指明了存储引擎是myisam还是innodb。存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择。地位如下图：

网络接口层：与客户端通信，比如传输数据等等。存储引擎层：存储数据的规则，方式。

本质：存储引擎就是特定的数据存储格式（方案）。

可以使用show engines命令来查看当前MySQL支持的存储引擎列表。

1、InnoDB存储引擎介绍

Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。

（1）存储格式

数据，索引集中存储，存储于同一个表空间文件中。

数据：记录行。索引：一种检索机制，也需要一定的空间，就相当于一本字典的目录。

示例：创建一个test数据库，新建一张student表，选择存储引擎为innodb, 然后打开mysql的data下的test目录，发现有以下3个文件。

其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm是表结构文件，仅存储了表的结构、元数据(meta)，包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件，包括了单独一个表的数据及索引内容。

如果往表里插入了新的数据，则在mysql的data目录下会生成ibdata1文件，这个文件是存储了所有innodb表的数据。

关于innodb引擎的详细介绍：

使用innodb引擎时，需要理解独立表空间、共享表空间。

独立表空间：每个表都会生成以独立的文件方式来存储，每个表都一个.frm的描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容，默认情况下它的存储在mysql指定的目录下。

独立表空间优缺点

优点：

每个表都有自己独立的表空间；每个表的数据和索引都会存储在各个独立的表空间中；可以实现单表在不同的数据进行迁移；表空间可以回收（除了drop table操作，表空不能自己回收）；drop table 操作自动回收表空间，如果对统计分析或是日值表，删除大量数据后可以通过：alter table tablename engin=innodb进行回缩不用的空间；对于使用inodb-plugin的innodb使用truncate table会使用空间收缩。；对于使用独立表空间，不管怎么删除，表空间的碎片都不会太严重。

缺点：

单表增加过大，如超过100G。对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

共享表空间：某一个数据库所有的表数据，索引文件全部都放在一个文件中，默认这个共享表空间的文件路径在data目录下，默认的文件名为 ibdata1,初始化为10M。

共享表空间优缺点

优点：可以将表空间分成多个文件存放在各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做了大量删除操作后表空间将有大量的空隙，特别是对统计分析、日值系统这类应用最不适合用共享表空间。

如何开启独立表空间？

查看是否开启独产表空间：

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

设置开启：

在my.cnf文件中[mysqld] 节点下添加innodb_file_per_table=1

或者通过命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改或者是

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

修改独占空表空间的数据存储位置

innodb_data_home_dir = "C:\mysql\data\"

innodb_log_group_home_dir = "C:\mysql\data\"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。【对数据来进行初始化的设置】

innodb_data_home_dir 代表为数据库文件所存放的目录

innodb_log_group_home_dir 为日志存放目录

innodb_file_per_table 是否使用共享以及独占表空间来

以上的几个参数必须在一起加入。

对于参数一些注意的地方

InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。

如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。

（2）数据按照主键顺序存储

插入时做排序工作，效率低。

（3）特定功能

事务、外键约束：都是为了维护数据的完整性。

并发性处理：

innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。

行级锁定：row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，Innodb会自带锁，不需要我们自己设置。

多版本并发控制, MVCC，效果达到无阻塞读操作。

（4）总结：innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。

ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。

擅长与处理，高速读与写。

（1）存储方式

数据和索引分别存储于不同的文件中。

（2）数据的存储顺序为插入顺序（没有经过排序）

插入速度快，空间占用量小。

（3）功能

a.全文索引支持。（mysql>=5.6时innodb 也支持）

b.数据的压缩存储。.MYD文件的压缩存储。

压缩前，数据是25600KB：

进行压缩：使用工具 myisamPack完成压缩功能：该工具mysql自带

进入到需要压缩表的数据目录，执行压缩指令 myisampack 表名。配置环境变量。

压缩后：

注意，压缩后，需要重新修复索引：

查看结果，发现现在的数据变成12741KB了，比之前的更小了：

压缩优势：节省磁盘空间，减少磁盘IO开销。特点：压缩后的表变成了只读表，不可写。

如果需要更新数据，则需要先解压后更新。利用工具：myisamchk –unpack 表名进行解压

解压后，变成了原来的25600KB

刷新表的状态：flush table myisam_2

c.并发性：

仅仅支持表级锁定，不支持高并发。

支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）

（4）关于Innodb 和 myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：高速查询及插入。擅长插入和查询。

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？

a.微博主要是插入微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？

a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；

b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；

c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。

3.其他存储引擎

（1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。

（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。

（3）插件式存储引擎：用C和C++开发的存储引擎。

4.锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。

操作方面：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）

写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：

行级：提升并发性，锁本身开销大

表级：不利于并发性，锁本身开销小。

二、字段类型选择

字段类型应该要满足需求，尽量要满足以下需求。

尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

1.列类型之数值

（1）整型

MySQL数据库支持五种整型类型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。

整型类型占用空间和取值范围

类型字节最小值最大值

TINYINT 1 有符号：-128 无符号：0 有符号：127 无符号：255

SMALLINT 2有符号：-32768无符号：0有符号：32767无符号：65535

MEDIUMINT 3有符号：-8388608无符号：0有符号：8388607无符号：16777215

INT/INTEGER 4有符号：-2147483648无符号：0有符号：2147483647无符号：4294967295

BIGINT 8 有符号：-9223372036854775808无符号：0 有符号：9223372036854775807无符号：18446744073709551615

五种整型的适用场景：

TINYINT，年龄，包含在0~255之间；

SMALLINT，端口号，包含在0~65535之间；

MEDIUMINT，中小型网站注册会员，1600万够用；

INT，身份证编号，42亿可以用很久；

BIGINT，Twitter微博量，几百亿

（2）浮点型(非精确)

MySQL数据库支持两种浮点类型：FLOAT(单精度)和DOUBLE(双精度)两种

浮点型(非精确)占用空间和取值范围

类型字节范围

FLOAT 4 正数范围：1.175494351E-38~3.402823466E+38，负数范围：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正数范围：1.7976931348623157E-308~2.2250738585072014E+308

负数范围：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定点型(精确)

浮点型由于内部的存储方式是数值，导致它在一定程度上取得的是近似值而非精确值。如果使用定点型，那么就可以精确取得小数部分，因为它内部存储方式是字符串形式。

定点型(精确)占用空间和取值范围

类型字节范围

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

创建一个定点型格式：DECIMAL(M,D)，表示小数点D位，整数部分M位及M位内。

2.列类型之日期

MySQL数据库中有五个可用的日期时间数据类型，分别为：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期时间类型占用空间和取值范围

类型字节最小值最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有几个特点：

a.当更新一条数据的时候，设置此类型根据当前系统更新可自动更新时间；

b.如果插入一条NULL，也会自动插入当前系统时间；

c.创建字段时，系统会自动给一个默认值；

d.会根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。

//查看当前时区

SHOW VARIABLES LIKE 'time_zone';

//设置为东九区，查询时间就会加1小时

SET time_zone='+9:00';

DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。

YEAR占用1个字节，包年年份，长度默认为4位，无法设置。

TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围的时是-838到838的原因，是因为TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。

综上考虑：使用datetime，当然也可以使用int（11）来保存时间戳。

关于INT(11)存放时间戳的优点如下：

a.INT占4个字节，DATETIME占8个字节；

b.INT存储索引的空间比DATETIME小，查询快，排序效率高；

c.在计算机时间差等范围问题，比较方便。

3.列类型之字符

字符集校对规则utf8_general_ci表示校对时不区分大小写，相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名，这里是通用，utf8表示编码。如果是gbk，可以使用gbk_chinese_ci，如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。

（1）CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下，长度为5的CHAR类型，最多可以存储15字节，也就是5个汉字的内容。因为一个汉字占3个字节。

由于CHAR类型是定长，MySQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。

（2）BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用两个字符，会乱码，半截会问号。因为是采用二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。

（3）八种适合文本内容的大数据类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

综上：短文本定长用char，变长用varchar，长文本用text

4.列类型之属性

无符号(UNSIGNED)和填充零(ZEROFILL)，还有是否为空、默认值、主键、自动编号。

严格模式

我们使用的是WAMP集成环境，默认安装的情况下，是非严格模式，用于部署阶段。而开发调试阶段，强烈建议使用严格模式，方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重启服务器即可。检查SQL_MODE状态

SELECT @@global.sql_mode;

三、范式与逆范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式1NF，原子性

第二范式2NF，消除部分依赖

第三范式3NF，消除传递依赖

1、范式

（1）第一范式：具有原子性，确保每列保持原子性。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
（2）第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。

所谓传递函数依赖，指的是如果存在"A →B →C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：

关键字段→非关键字段x →非关键字段y

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

先满足第一范式，再满足第二范式，才能满足第三范式。

2、逆范式

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

示例：假如有一个商品表Goods：

字段有Goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。

还有一个分类表Category：

字段有Cat_id(类别id), cat_name(类别名称)。

现在要查询类别id为3的商品的数量，例如分类列表查询：

分类ID 分类名称商品数量

3 计算机 567

可以使用下列sql语句：

Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;

但是，假如商品数量较大，那么就比较耗性能了。这时，我们可以考虑重新设计Category表：增加存当前分类下商品数量的字段。

Cat_id, cat_name, goods_count

每当商品改动时，修改对应分类的数量信息。

再查询分类列表时：Select * from category;

此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。

四、索引

1．索引概述

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

测试查询，添加索引前后比对执行时间：

2．索引种类

从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的差异：对索引关键字的要求不同。

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。

唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。

主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。

全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

关键字含义：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。命令:alter table exp add index (field1, field2);

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

3.索引操作

(1)创建主键索引

创建一个无符号整型且自动增长的列，然后设置成主键即可。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE id=1;

(2)创建普通或唯一索引

直接进入navicat设计表的第二栏，选择一个字段(比如user字段)，添加一个Nomral(普通索引)或Unique(唯一索引)。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';

//查看表所有索引情况

SHOW INDEX FROM think_user;

（3）使用sql语句的方式建立索引----建表时就创建索引

注意：索引可以起名字，但是主键索引不能起名字，因为一个表仅仅可以有一个主索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。

（4）使用sql语句的方式建立索引----更新表时创建索引

注意：如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。 auto_increment属性，依赖于一个KEY。

（5）使用sql语句的方式删除索引，auto_increment依赖于KEY。

（6）Explain 执行计划

可以通过在select语句前使用 explain，来获取该查询语句的执行计划，而不是真正执行该语句。

删除索引时，再看执行计划：

从查询的行数可知，有索引时查询会快的多，因为它只需要查找一行，而没有索引时，会造成全表扫描。

注意：select语句才能获取到执行计划。（新版本5.6会扩展其他语句的执行计划的获取）

4.索引原则

如果索引不遵循使用原则，则可能导致索引无效。

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

第三个语句 empno-1就不是列独立：就不能用索引。类似函数等等。（write_time < unix_timestamp()-$gc_maxlifetime）

其他两个列独立可以使用：

（2）左原则

Like：匹配模式必须要左边确定不能以通配符开头。

假如业务逻辑上出现: field like ‘%keywork%’;类似查询，需要使用全文索引。

复合索引：一个索引关联多个字段，仅仅针对左边字段有效果。

示例：添加复合索引

对Ename的查询，使用了索引，结果如下：

Empno的查询没有使用索引，结果如下：

（3）OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

为后面的条件增加可以使用的索引后，再查看执行计划：

（4）MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引：如下图

弃用索引的主要原因：

查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。

综上归纳：

a、不要过度索引。索引越多，占用空间越大，反而性能变慢；

b.只对WHERE子句中频繁使用的建立索引；

c.尽可能使用唯一索引，重复值越少，索引效果越强；

d.使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；

e.充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。

f.索引存在，如果没有满足使用原则，也会导致索引无效：

5.索引的使用场景

（1）索引检索：检索数据时使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，则可能使用到索引。

例如，按照ename字段排序查询：

此时，没有任何索引。在ename字段上建立索引后：

不会用到查询检索索引是因为没有用where条件查询，而真实执行时，就会用到排序索引。

Tip：对比以上两个执行计划：

extra位置：

其中：extra额外信息。加了索引后就不用使用文件排序了。

Using filesort，表示使用文件排序（外部排序，内存外部）。

（3）索引覆盖

索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。

例如，利用名字检索：

可以在ename字段建立索引：

分析执行：

再增加一个索引：

完成相同的查询：

查询的字段刚好是复合索引包含的字段。所以就使用了复合索引。

说明，不是非要查询用到，才可以索引覆盖，只要满足要求都可以覆盖！

建立索引索引时，不要仅仅考虑where检索，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）

6.前缀索引

前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个前缀索引，就是王。

语法：

Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。

那么N究竟是多少？使用N长度所达到的辨识度，极限接近于使用全部长度的辨识度即可！

先计算最大的辨识度M：

公式：先计算总的记录数m，再求该字段不重复的记录数q，那么M=m/q。然后依次取得前N个字符，N逐步增加，进行对比，直到找到极限接近于M的，那么最后的N就是我们要找的N。

求得辨识度为1.4774.，也就是说一个前缀索引可以对应1.4774条记录。

然后依次取得前N个字符，进行对比，找到极限接近的：

可见，9 时，已经极限接近，提高长度，不能明显提升辨识度，因此可以使用前9个字符：

Tip：前缀索引不能用于索引覆盖！

7.全文索引

该类型的索引特殊在：关键字的创建上。是为了解决 like‘%keyword%’这类查询的匹配问题。（mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引）。

示例：

假如有一张表，表中有标题和内容两个字段，现在要查询标题或者内容包含 “database” 关键字的记录。

补充：text和varchar的区别是text的数据不存在记录里，一条记录的最大空间是65535.

形成的SQL如下：

Select * from articles where title like ‘%database%’ or body like ‘%database%’;

此时不能建立普通索引，查询不符合左原则，建立了也使用不了。

此时全文索引就可以发挥其作用了：

直接使用上面的SQL，需要使用特殊的全文索引匹配语法才可以生效： Match() against();

Tip: 该MYSQL提供的全文索引，不能对中文起作用！

使用Match() against() 返回关键字的匹配度（关键字与记录的关联程度）。

停止词 in：

发现in这个词，是不能被全文索引所检索到的。因为in这个词是不可以用在全文索引的关键词里的，没有谁会在一段文本里检索这样一个词。

思考：与 like %in% 是否相同？不同。

原因何在呢？全文索引，索引的的关键字，不是整个字段数据，而是从数据中提取的关键词。

8.索引结构-b-tree介绍

Hash、B-Tree（B树）两种数据结构。指的是mysql存储索引所采用的数据结构。其中，用户所维护的所有的索引结构 B-Tree结构。

B-Tree的结构如下：

每个节点，存储多个关键字。关键字也会对应记录地址

以上设计为了解决一次性磁盘IO开销，可以读取到更多的关键字数量。

每个关键字之间，存在子节点指针：

如果是复合索引：

关键字的排序先排左侧字段，在左侧字段相同的情况下，再排序右侧字段：

9.聚集索引(聚簇索引)

B+Tree(B-Tree的变种)

在innodb的存储引擎上，主键索引是与数据记录存储在一起的（聚簇在一起的）。

带来的问题：

Innodb的其他索引，非主键索引（二级索引）：

关键字对应的不再是记录的地址，而是记录的主键。

可见，检索需要二次检索。先检索到主键ID，再检索记录。

五、查询缓存query_cache

将select的结果，存取起来共二次使用的缓存区域：

MySQL提供的缓存区：

未开启前：

两次查询时间消耗一致。

开启查询缓存，通过变量控制：

开启并设置大小：

再次执行查询：

可见，第二次查询，使用了开启的缓存！

注意事项：查询缓存存在判断是严格依赖于select语句本身的：严格保证SQL一致。

如果查询时包含动态数据，则不能被缓存。

一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的：

注意：这里的缓存仅当数据表的记录改变时，缓存才会被删除。而不是依靠过期时间的。

六、分区分表

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

其实每个分区，就是独立的表。都要存储该分区数据的数据，索引等信息。

创建分区：在创建表时，指定分区的选项：

Create table table_name (定义)

Partition by 分区算法 (参数) 分区选项。

例如：Partition by key (id) partitions 5;

采用key取余算法，根据id的值进行取余，即对5取余，然后分配到5个区里。

分区结果如下：myisam下

Innodb下

Tip：分区与存储引擎无关，是MySQL逻辑层完成的。

可以通过变量查看当前mysql是否支持分区：

1.分区算法

MySQL提供4种分区算法：取余：Key，hash 条件：List，range 。

参与分区的参数字段需要为主键的一部分。

（1）KEY – 取余，按照某个字段进行取余

分成5个区，就是对5取余。将id对5取余。

（2）Hash – 取余，按照某个表达式的值进行取余

示例：学生表分区，按照生日的月份，划分到12个表中。

注意：Key，hash都是取余算法，要求分区参数（括号里的），返回的数据必须为整数。

（3）List – 条件 – 列表，需要指定的每个分区数据的存储条件。

示例：按照生日中的月份，分成春夏秋冬四个分区。

List，条件依赖的数据是列表形式。

（4）Range - 条件 – 范围，条件依赖的数据是一个条件表达式。

逻辑：按照生日的年份分成不同的年龄段。

2.分区的管理与选择

（1）取余：key，hash

增加分区数量： add partition partitions N

减少分区数量： COALESCE partition N

采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为会重新分配数据到新的分区。

（2）条件：list，range

添加分区

删除分区：

Drop partition partition_name;

注意：删除条件算法的分区，会导致分区数据丢失。添加分区不会。

（3）选择分区算法

平均分配：就按照主键进行key(primary key)即可(非常常见)

按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型

3.分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。（需要手动分表）

分表是分区之前用的，MYSQL5.1后，就开始用分区代替分表了。分表很少用了。

（1）水平分表

创建结构相同的N个表；

再创建用于管理学生ID的表student_id：(该表是为了提供自增的ID)

PHP客户端逻辑：

Merge，mrg_myisam

是MySQL提供一个可以将多个结构相同的myisam表，合并到一起的存储引擎：

（2）垂直分表

一张表中存在多个字段。这些字段可以分为常用字段和非常用字段，为了提高查表速度，我们可以把这两类字段分开来存储。主要目的，减少每条记录的长度。

通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text，blog等大字段拆分出来放在附表中; 经常组合查询的列放在一张表中;

例如学生表可以分成：

基础表(Student_base)和额外表(Student_extra)，两张表中记录为1:1的关系。

基础信息表Student_base

Id name age

额外信息表Student_extra

Id 籍贯政治面貌

七、服务器架构介绍

服务器架构，不仅仅是用一台MySQL

主从复制：

Mysql服务器内部支持复制功能，仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果：主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。

读写分离，负载均衡：

php不再操作MYSQL数据库服务器，而是去操作读写分离、负载均衡服务器，只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡，读写分离是指该服务器会判断客户端的操作是读还是写，从而选择操作mysql主服务器还是从服务器。负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器。

上面的架构可以提升整体服务器的效率，高性能。

同时，服务器架构需要保证，高可用（稳定），7x24不宕机。因此需要增加一些冗余服务器以便备用。时时检测正在用的服务器。

八、SQL优化

1.对于并发性的SQL

少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。

2.大量数据的插入

多条 insert或者Load data into table（从文件里载入数据到表里）

建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。

针对于myisam，步骤：

Alter table table_name disable keys; 禁用索引约束

大量的插入

Alter table table_name enable keys; 启用

针对innodb，步骤：

Drop index, drop constraint 删除索引及约束，要保留主键

Begin transaction|set autocommit=0; 开启事务，不让他自动提交

[数据本身已经按照主键值排序]

大量的插入

Commit;

Add index, add constraint

3.分页

分页假定Limit offset, size; size = 10;

Page	offset
5	40, 10
50	490, 10
5000	4990, 10
500000	499990, 10

Limit 的使用，会大大提升无效数据的检索（被跳过），因为是先检索，检索会检索全部，再取得想要的。好的做法是使用条件等过滤方式，将检索到的数据尽可能精确定位到需要的数据上。

4.随机选一些数据，不要使用Order by Rand()

上面的查询，会导致每条记录都执行rand()，成本很高！

建议，通过mt_rand()，先确定的随机主键，再从数据表中获取数据。

九、慢查询日志的使用

定位执行较慢的查询语句方案。

show variables like 'slow_query%'; show variables like '%long_query%';

Slow_query_log = 0|1

Long_query_time = N 超过该时间临界点，就为慢查询。

开启日志

set global slow_query_log=1; set long_query_time=0.5；

执行SQL，查看：

史上最简单MySQL教程详解（进阶篇）之事务处理_Newtol的博客-CSDN博客

为什么需要事务处理？

我们前面接触到的增删改查几乎都是单独的进行的操作，但是在实际的开发过程中，我们可能会涉及到一系列的连续对数据库的操作时，例如：某人要从自己的A银行卡往B银行卡转账1000元。正常的流程：

流程A银行从账户中扣除1000B银行收到1000转账B银行往账户中增加1000End

但是，假设在这个过程中，当A银行从账户中扣除了1000元，但是B银行因为某种原因未能收到这1000元的转账？如果当B银行收到了这笔转账，但是往用户账户增加1000元时，发生了故障导致未能增加1000元？我们应该怎么做？肯定有人想，把A银行已经扣除的钱回滚回去，然后等待下一次执行不就行了嘛？所以，事实上的流程应该是这样的：

流程A银行A银行从账户中扣除1000B银行是否收到转账？B银行往账户中增加1000账户增加1000是否成功？End往A银行增加已经扣除的1000元yesnoyesno

这样，我们就能保证，当在转账这个流程中出现了任何问题，我们都能让数据回滚到最开始的状态，这种处理方式我们就称为事务处理。也就是说：使用事务处理，就能保证所有的处理要么都成功，要么都失败。在进行多个联合数据的处理时，事务处理是必不可少的。（PS:现实生活中关于转账实际上要比这个复杂很多，我们仅仅是将转账的流程作为一个例子进行举例说明）

使用事务处理需要准备什么？

其实，我们在上一篇文章：史上最简单MySQL教程详解（进阶篇）之存储引擎介绍及默认引擎设置就已经有过说明，想要使用处理，我们必须将我们的数据表的存储引擎设置为【InnoDB】

事务处理的特性

事务其实就是并发控制的基本单位；相信我们都知道，事务是一个序列操作，是指作为单个逻辑工作单元执行的一系列操作，其中的操作要么都执行，要么都不执行，它是一个不可分割的工作单位；数据库事务的 ACID 四大特性是事务的基础，了解了 ACID 是如何实现的，我们也就清除了事务的实现，接下来我们将依次介绍数据库是如何实现这四个特性的。

原子性（Atomicity）

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）

一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致的状态。

隔离性(Isolation)

隔离性是指当多个用户并发访问数据库时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）

持久性(Durability)

持久性是指一个事务一旦被提交了，那么对于数据库中的数据改变就是永久性的，即便是在数据库系统遭遇到故障的情况下也不会丢失提交事务的操作。

事务处理

自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，也就说用户不用意识此事，执行 SQL 语句后就会马上执行【COMMIT 】操作。特别是当存储引擎为MyISAM的情况下，因为它本身就是不支持事务处理的。只要执行了命令，所有的命令就会被提交。因此要显式地开启一个事务务须使用命令【BEGIN】或【START TRANSACTION】后，才不会自动提交，只有明确执行了【COMMIT】命令后才会被提交，再次之前也就可以执行【ROLLBACK】这样的命令了。

查看自动提交模式

我们想要查看当前提交的模式可以使用【SELECT】语句，语法如下：

SELECT@@AUTOCOMMIT;

运行结果如下：

mysql> SELECT@@AUTOCOMMIT;
+————–+
| @@AUTOCOMMIT |
+————–+
| 1 |
+————–+
1 row in set (0.00 sec)

我们可以看到我们目前自动提交的模式是开启的（0：为开启；1：开启）

修改自动提交模式

修改自动提交模式使用的是【SET】语句：

将自动提交模式设置为OFF的语法是：SET AUTOCOMMIT =0;
将自动提交模式设置为ON的语法是：SET AUTOCOMMIT =1;

简单事务回滚

使用事务回滚主要是使用三个命令，他们的语法如下：

命令	解释
BEGIN(或：START TRANSACTION);	开启事务
COMMIT;	提交整个事务
ROLLBACK;	回滚到事务开始的状态

下面我们就演示一下简单的事务回滚，具体流程如下：

开始将数据表设置为【InnoDB】查看【test】表中的数据删除【test】表中的数据查看【test】表中的数据是否被删除回滚操作确认是否恢复数据

运行流程和结果如下：

mysql> ALTER TABLE user ENGINE = InnoDB; //修改存储引擎
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user; //查看是否修改成功
+——-+—————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+—————————————————————————————————————————————————————————————————————————-+
| user | CREATE TABLE user (
userId char(5) CHARACTER SET latin1 NOT NULL,
name varchar(30) DEFAULT NULL,
sex char(1) CHARACTER SET latin1 DEFAULT ‘0’,
PRIMARY KEY (userId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+—————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)
mysql> BEGIN; //开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM user; //确认数据
+——–+——–+——+
| userId | name | sex |
+——–+——–+——+
| 1 | 张三 | 0 |
| 2 | 李四 | 1 |
| 3 | 李四 | 0 |
| 4 | 王五 | 0 |
| 5 | 小张 | 1 |
| 6 | 张三丰 | 1 |
| 7 | NULL | 0 |
+——–+——–+——+
7 rows in set (0.00 sec)
mysql> DELETE FROM user; //删除数据
Query OK, 7 rows affected (0.00 sec)
mysql> SELECT * FROM user; //查看是否删除成功
Empty set (0.00 sec)
mysql> ROLLBACK; //回滚
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM user; //查看是否回滚成功
+——–+——–+——+
| userId | name | sex |
+——–+——–+——+
| 1 | 张三 | 0 |
| 2 | 李四 | 1 |
| 3 | 李四 | 0 |
| 4 | 王五 | 0 |
| 5 | 小张 | 1 |
| 6 | 张三丰 | 1 |
| 7 | NULL | 0 |
+——–+——–+——+
7 rows in set (0.00 sec)

可以看到，我们原本已经删除掉的数据就被我们回滚成功了。如果我们将【ROLLBACK】换成【COMMIT】，那么数据就被删除，再也不可恢复。

部分回滚

我们上面介绍的简单事务回滚是回滚到事务最开始的状态，其实在事务的处理过程中，我们可以通过保存节点（SAVEPOINT）,来进行事务回滚。流程如下：

开始开启事务执行SQL命令保存节点执行SQL命令4回滚(判断是否有保存节点)回滚到开启事务前结束yesno

运行结果如下：

mysql> BEGIN; //开启事务
Query OK, 0 rows affected (0.00 sec) //插入一条新的数据
mysql> INSERT INTO user VALUE(8,”WANG”,1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user; //查看是否插入成功
+——–+——–+——+
| userId | name | sex |
+——–+——–+——+
| 1 | 张三 | 0 |
| 2 | 李四 | 1 |
| 3 | 李四 | 0 |
| 4 | 王五 | 0 |
| 5 | 小张 | 1 |
| 6 | 张三丰 | 1 |
| 7 | NULL | 0 |
| 8 | WANG | 1 |
+——–+——–+——+
8 rows in set (0.00 sec)
mysql> SAVEPOINT sk; //保存节点
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user VALUE(9,”HONG”,0); //插入第二条数据
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user; //查看是否插入成功
+——–+——–+——+
| userId | name | sex |
+——–+——–+——+
| 1 | 张三 | 0 |
| 2 | 李四 | 1 |
| 3 | 李四 | 0 |
| 4 | 王五 | 0 |
| 5 | 小张 | 1 |
| 6 | 张三丰 | 1 |
| 7 | NULL | 0 |
| 8 | WANG | 1 |
| 9 | HONG | 0 |
+——–+——–+——+
9 rows in set (0.00 sec)
mysql> ROLLBACK TO SAVEPOINT sk; //回到保存节点
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM user; //查看是否回退成功
+——–+——–+——+
| userId | name | sex |
+——–+——–+——+
| 1 | 张三 | 0 |
| 2 | 李四 | 1 |
| 3 | 李四 | 0 |
| 4 | 王五 | 0 |
| 5 | 小张 | 1 |
| 6 | 张三丰 | 1 |
| 7 | NULL | 0 |
| 8 | WANG | 1 |
+——–+——–+——+
8 rows in set (0.00 sec)

我们可以看到，我们就成功的进行了部分事务的回滚。

注意事项

下面的四条语句在事务处理中是不可进行事务回滚的,也就是这四条语句一旦执行，就立刻被提交

DROP DATABASE;
DROP TABLE;
DROP;
ALTER TABLE;

史上最简单MySQL教程详解（进阶篇）之存储引擎介绍及默认引擎设置_Newtol的博客-CSDN博客_mysql如何设置引擎

什么是存储引擎？

与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。MySQL数据库在实际的工作中其实分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

MySQL存储引擎种类

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB	5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

接下来我们就介绍两种在实际开发中使用最多的两种引擎【MyISAM】和【InnoDB】。

MyISAM 引擎

这种引擎是MySQL最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

当然不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。例如我创建了一个【test】表，那么就会生成以下三个文件：

文件名	说明
test.frm	存储表定义
test.MYD	存储数据
test.MYI	存储索引

InnoDB引擎

InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
使用InnoDB是最理想的选择：

更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。
事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎。
自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
外键约束：MySQL支持外键的存储引擎只有InnoDB。
支持自动增加列AUTO_INCREMENT属性。

存储引擎操作

查看存储引擎

想要查看我们的数据表使用了什么存储引擎，我们就需要使用到我们以前已经介绍过的【show】命令，语法如下：

SHOW CREATE TABLE 表名;

例如我们查看我们之前创建好的【user】表：

SHOW CREATE TABLE user;
+——-+—————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+—————————————————————————————————————————————————————————————————————————-+
| user | CREATE TABLE user (
userId char(5) CHARACTER SET latin1 NOT NULL,
name varchar(30) DEFAULT NULL,
sex char(1) CHARACTER SET latin1 DEFAULT ‘0’,
PRIMARY KEY (userId)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+——-+—————————————————————————————————————————————————————————————————————————-+
1 row in set (0.02 sec)

就可以看到【 ENGINE】显示我们使用的引擎为【MyISAM】

存储引擎的变更

变更我们存储表的存储引擎将使用到的是我们前面使用过的【ALTER】命令，语法如下：

ALTER TABLE 表名 ENGINE=新引擎;

例如，我们将【user】表的引擎更改为【InnoDB】:

mysql> ALTER TABLE user ENGINE = InnoDB;
Query OK, 7 rows affected (0.50 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user;
+——-+—————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+—————————————————————————————————————————————————————————————————————————-+
| user | CREATE TABLE user (
userId char(5) CHARACTER SET latin1 NOT NULL,
name varchar(30) DEFAULT NULL,
sex char(1) CHARACTER SET latin1 DEFAULT ‘0’,
PRIMARY KEY (userId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+—————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

我们就成功的将【user】表的存储引擎改为了【InnoDB】

修改默认引擎

要使用事务功能，就必须将表设置为InnoDB引擎，有的版本在安装时，是未激活InnoDB引擎的，所以需要我们手动进行激活。首先我们进入MySQL的安装目录，找到my.ini文件。例如，博主使用的是WampServer的集成开发环境，目录如下：
这里写图片描述
我们右键选择记事本打开后，找到【default-storage-engine】这一行，如图：

将其修改为【default-storage-engine=InnoDB】即可，我们就成功的将默认的引擎修改为【InnoDB】。

这里，我们就讲解完了关于MySQL数据库存储引擎的有关知识，同时也为我们下一步讲解史上最简单MySQL教程详解（进阶篇）之事务处理做好了铺垫，因为要实现事务管理，需要将表的存储引擎更改为【InnoDB】

posted @ 2021-12-13 00:42 CharyGao 阅读(1721) 评论(0) 收藏举报

刷新页面返回顶部

硅基文明

代码改变不了世界，但是改变世界需要代码。

mysql分库分表，自增id和UUID做主键性能分析，优化，引擎

数据库分库分表思路

一. 数据切分

1、垂直（纵向）切分

2、水平（横向）切分

1、根据数值范围

2、根据数值取模

二. 分库分表带来的问题

1、事务一致性问题

分布式事务

最终一致性

2、跨节点关联查询 join 问题

1）全局表

2）字段冗余

3）数据组装

4）ER分片

3、跨节点分页、排序、函数问题

4、全局主键避重问题

1）UUID

2）结合数据库维护主键ID表

3）Snowflake分布式自增ID算法

综上

5、数据迁移、扩容问题

三. 什么时候考虑切分

1、能不切分尽量不要切分

2、数据量过大，正常运维影响业务访问

3、随着业务发展，需要对某些字段垂直拆分

4、数据量快速增长

5、安全性和可用性

四. 案例分析

1、用户中心业务场景

2、水平切分方法

3、非uid的查询方法

3.1、建立非uid属性到uid的映射关系

3.2、前台与后台分离

五. 支持分库分表中间件

六. 参考

mysql 自增id和UUID做主键性能分析，及最优方案

1.为什么会想到用uuid做主键

2.UUID优缺点

3.最优方案

4.如果非要使用uuid做主键，下面是小建议：

为什么需要事务处理？

使用事务处理需要准备什么？

事务处理的特性

原子性（Atomicity）

一致性（Consistency）

隔离性(Isolation)

持久性(Durability)

事务处理

自动提交

查看自动提交模式

修改自动提交模式

简单事务回滚

部分回滚

注意事项

什么是存储引擎？

MySQL存储引擎种类

MyISAM 引擎

InnoDB引擎

存储引擎操作

查看存储引擎

存储引擎的变更

修改默认引擎