一些SQL面试题

https://www.cnblogs.com/yes153863294/p/13722051.html

https://www.sohu.com/a/417717245_466874

https://blog.csdn.net/qq_38374562/article/details/104146223

一、主键有四种：

(1). 自增序列；

(2). UUID()函数生成的随机值；

(3). 用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4). 基于一套机制生成类似自增的值，比如序列生成器；

二、各自的优缺点

(1)自增序列：

定义：从小到大 或从大到小的顺序模式增加新值；

优点：

（1）数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利。

（2）数字型，占用空间小，易排序，在程序中传递方便。存储空间一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；

（3）若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数： auto_increment _increment  和 auto_increment_offset；

缺点：

1、不支持水平分片架构，水平分片的设计当中，这种方法显然不能保证全局唯一。

2、自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

4、因为自动增长，在手动要插入指定ID的记录时会显得麻烦，尤其是当系统与其它系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦；

5、在系统集成或割接时，如果新旧系统主键不同是数字型就会导致修改主键数据类型，这也会导致其它有外键关联的表的修改，后果同样很严重；
6、若系统也是数字型的，在导入时，为了区分新老数据，可能想在老数据主键前统一加一个字符标识（例如“o”，old）来表示这是老数据，那么自动增长的数字型又面临一个挑战。

(2)UUID()函数：

定义：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

优点
1、全局唯一性、安全性、可移植性。

2、能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。

3、保证生成的ID不仅是表独立的，而且是库独立的，在你切分数据库的时候尤为重要

缺点
1、针对InnoDB引擎会徒增IO压力，InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。
2、UUID长度过长，一个UUID占用128个比特（16个字节）。主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

(3)使用用户注册的帐号名称，字符串类型

定义：其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4)序列生成器的架构

定义：类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

如何做选择？

若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；

用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；

若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择。

三、主键的数据类型选择

关于主键的类型选择上最常见的争论是用整型还是字符型的问题，关于这个问题《高性能MySQL》一书中有明确论断：
整数通常是标识列的最好选择，因为它很快且可以使用AUTO_INCREAMENT,如果可能，应该避免使用字符串类型作为标识列，因为很消耗空间，且通常比数字类型慢。

四、如何解决水平分片的需求

UUID

由于UUID出现重复的概率基本可以忽略，所以对分片是天生支持的。

独立的序列库

单独建立一个库用来生成ID，在Shard中的每张表在这个ID库中都有一个对应的表，而这个对应的表只有一个字段， 这个字段是自增的。当我们需要插入新的数据，我们首先在ID库中的相应表中插入一条记录，以此得到一个新的ID， 然后将这个ID作为插入到Shard中的数据的主键。这个方法的缺点就是需要额外的插入操作，如果ID库变的很大， 性能也会随之降低。所以一定要保证ID库的数据集不要太大，一个办法是定期清理前面的记录

复合标识符

这种做法是通过联合主键的策略，即通过两个字段来生成一个唯一标识，前半部分是分片标识符，后半部分是本地生成的标识符（比如使用AUTO_INCREMENT生成）

带分库策略的自定义序列表

这种做法可以基于上面提到的自定义序列表的方法的基础上，做一些技巧性的调整。即如下：

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

这里的id初始值设定上要求不同的分片取不同的值，且必须连续。同时将每次递增的步长设定为服务器数目。
比如有3台机器，那么我们只要将初始值分别设置为1，2，3. 然后执行下面的语句即可：

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 3) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

这就可以解决主键生成冲突的问题。但是如果在运行一段时间后要进行动态扩充分片数的时候，需要对序列初始值做一次调整，以确保其连续性，否则依然可能存在冲突的可能。当然这些逻辑可以封装在数据访问层的代码中。