本文共 5898 字，大约阅读时间需要 19 分钟。

一、存储引擎

MySQL中有哪些存储引擎？

常见的两种存储引擎：InnoDB与MyISAM 区别： 1、Innodb是支持事务的存储引擎，多应用于插入和更新操作，行锁设计比较合理（最大区别就在锁的级别上），适合大数据，高并发 2、MyISAM是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发

二、字符集及校对规则

常见的字符集有哪些及校对规则？

字符集是一套符号和编码。校对规则是用于比较字符的一套规则。 以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 注意： utf8_general_ci 按照普通的字母顺序，而且不区分大小写（比如：a B c D） utf8_bin 按照二进制排序（比如：A排在a前面，B D a c）

三、索引

Mysql索引使用的数据结构主要有哈希索引和BTree索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

Mysql的BTree索引使用的是B数中的B+树，但对于主要的两种存储引擎的实现方式是不同的。   MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。   InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶子节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

四、缓存

my.cnf加入以下配置，重启Mysql开启查询缓存

query_cache_type=1query_cache_size=600000

Mysql执行以下命令也可以开启查询缓存

set global  query_cache_type=1;set global  query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。

缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

五、事务

六、锁

七、大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；

1、读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；

2、缓存：

使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

3、垂直分区：

根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

5、水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。 水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL 并发能力没有什么意义，所以 水品拆分最好分库

八、MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。 c. mysql库主从读写分离。 d. 找规律分表，减少单表中的数据量提高查询速度。 e。添加缓存机制，比如memcached，apc等。 f. 不经常改动的页面，生成静态页面。 g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

九、如何进行SQL优化

1、选择正确的存储引擎

InnoDB适合于大量的写操作且支持事务操作 MyISAM适合于一些需要大量查询的应用，不支持事务 2、优化字段的数据类型 记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间 3、为搜索字段添加索引 索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引 4、避免使用Select * 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利 5、使用 ENUM 而不是 VARCHAR ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR 6、尽可能的使用 NOT NULL 除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值 7、固定长度的表会更快 如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理 固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键 并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间

十、锁的优化策略

① 读写分离

② 分段加锁 ③ 减少锁持有的时间 ④ 多个线程尽量以相同的顺序去获取资源 这些都不是绝对原则，都要根据情况，比如不能将锁的细粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁

十一、索引的底层实现原理和优化

B+树，经过优化的B+树

主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引

15、MySQL：

使用数据库中间件MyCat实现读写分离，分库分表 分库分表：1000-2000左右并发

数据库设计三大范式指的是什么？

①字段不可分 ②有主键，非主键字段依赖主键 ③非主键字段不能互相依赖

MYSQL数据库设计规范有哪些？

1、数据库、表和字段命名规范：字母、数字和下划线，多个单词用下划线分隔 2、索引规范，每个表要创建一个主键索引 3、不在数据库做运算，字段一般控制在20个以内。 4、避免使用null字段，尽量使用varchar代替text类型。

数据库密码加密方式有了解吗

利用Bcrypt方式，密码长度必须是8到56位字符，之后会被转化为448位密钥，调用genSalt方法进行设置加密等级，调用hash方法进行密码加密。

创建数据库：

字符集：utf8、数据库排序规则：utf8_general_ci、默认引擎InnoDB mysql基本支持所有字符集，默认为latin1拉丁字符集，多数选用utf8，支持全部字符，占用空间大，只想在中国使用可以使用gbk utf8_general_ci在做校对时速度比较快 InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键

DBMS中的并发控制任务：确保在多个事务同时存取数据库中同一个数据时不破坏事务的隔离性、统一性

解决手段：使用乐观锁和悲观锁 memcache/hibernate/tair 悲观锁就是每次拿数据都上锁，乐观锁是在更新数据时上锁； 悲观锁：利用DB本身的锁机制for update处理，顾名思义，就是很悲观，每次去拿数据时都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到他拿到锁，容易造成长时间的等待。 传统的DB里面有好多这样的锁机制，比如行锁、表锁、读锁、写锁，都是在操作之前先上锁。 乐观锁：利用hibernate自己实现的，采用版本控制的方式处理并发。 乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实就是提供乐观锁。

分库分表：就是把一个数据库或表进行物理分割

读写分离：读数据在一个数据库，写数据在另一个数据库中 复制两个mysql文件夹，修改端口号

十二、分布式事务

1、什么是本地事务？

本地事务就是用关系数据库来控制事务，关系数据库通常都具有ACID特性，传统的单体应用通常会将数据全部存储 在一个数据库中，会借助关系数据库来完成事务控制 2、什么是分布式系统？ 部署在不同节点上的系统通过网络交互来完成协同工作的系统 比如： 充值加积分业务：充值系统加钱，积分系统加积分 下单减库存业务：订单系统修改订单信息，库存系统减少库存 下单后自动选课：订单系统修改订单信息，选课系统新增选课信息

3、问题描述

如何实现两个分布式服务（订单服务、学习服务）共同完成一件事即订单支付成功自动添加学生选课的需求？ 这里的关键是如何保证两个分布式服务的事务的一致性，尝试解决上边的需求，在订单服务中远程调用选课接口，伪代码如下：

订单支付结果通知方法｛ 	更新支付表中支付状态为“成功”。 	远程调用选课接口添加选课记录。 ｝

存在如下问题：

• 1、如果更新支付表失败则抛出异常，不再执行远程调用，此设想没有问题
• 2、如果更新支付表成功，远程调用超时会拉长本地数据库事务时间，影响数据库性能
• 3、如果更新支付表成功，远程调用添加选课成功（选课数据库commit成功），最后更新支付表commit失败，此时出现操作不一致

CAP理论：分布式系统在设计中只能在一致性、可用性和分区容忍性中满足两个，无法兼顾三个

一致性：服务A、B、C三个节点存储了用户数据，三个节点需要同一时刻保持数据一致 可用性；ABC三个节点中其中一个宕机不会影响整个集群对外服务 分区容忍性：主要解决由于网络分区导致的数据不完整及无法访问等问题 网络分区：多个系统节点之间出现网络中断、网络延迟等现象导致数据分散在不同节点上 为什么分布式系统不能兼顾C、A、P？ 在保证分区容忍性的前提下，如果要保证数据的一致性就要实现每个节点的数据一致，如果要提高系统的可用性就要增加多个节点，节点越多可用性越好，但是一致性越差 在分布式系统设计中AP的应用较多，即保证分区容忍性和可用性，牺牲数据的强一致性（写操作后立刻读取到最 新数据），保证数据最终一致性。比如：订单退款，今日退款成功，明日账户到账， 只要在预定的用户可以接受的 时间内退款事务走完即可