MySQL 对于千万级的大表要怎么优化？

优化千万级大表是数据库性能调优中的一个重要课题，需要从多个维度进行综合考虑和实施。下面我将尽量详细地讲述 MySQL 对于千万级大表的优化策略，主要从以下几个方面展开：

一、 核心原则与目标

在开始具体的优化措施之前，明确我们的核心原则和优化目标至关重要：

降低 I/O 成本： 读取数据通常是性能瓶颈所在，减少磁盘 I/O 是首要任务。
提高 CPU 利用率： 确保 CPU 不被不必要的计算和锁竞争消耗。
减少锁竞争： 并发环境下，锁是影响性能的关键因素。
优化内存使用： 合理利用内存缓存，减少对磁盘的访问。
提升查询速度： 确保查询能够快速返回结果。
保证数据一致性与可用性： 在优化过程中不能牺牲数据的准确性和系统的可用性。

二、 数据模型与表结构优化

良好的表结构是优化的基础，特别是对于千万级大表。

1. 选择合适的数据类型：
数值类型： 尽量使用能够满足需求的最小数值类型（例如，`TINYINT` (1字节) 优于 `INT` (4字节) 如果数值范围允许）。避免使用 `VARCHAR` 来存储固定长度的数字，因为 `VARCHAR` 会增加额外的存储开销和查找开销。
字符串类型：
`VARCHAR` vs `CHAR`： `VARCHAR` 存储可变长度字符串，比 `CHAR` (固定长度字符串) 更节省空间，但可能增加查找开销，因为需要额外存储长度信息。对于长度变化大的字段，`VARCHAR` 是更好的选择。
字符集与编码： 选择合适的字符集（如 `utf8mb4` 是目前推荐的选择，可以支持 emoji 等特殊字符），但要避免不必要的字符集转换，这会消耗 CPU 资源。确定字段的最大长度，并为其分配足够但不过多的空间。
日期和时间类型： `DATETIME` 和 `TIMESTAMP` 的选择，`TIMESTAMP` 更节省空间，且会自动转换时区，但有范围限制。根据需求选择。
二进制类型： 对于存储大量二进制数据（如图片、文件），强烈建议将其存储在文件系统中，并在数据库中只存储文件的路径或引用，而不是直接存储在 BLOB/TEXT 字段中。
枚举 (ENUM) 和集合 (SET)： 在某些场景下，如果字段的取值范围有限且固定，可以使用 `ENUM` 或 `SET` 来节省空间，但要注意其灵活性相对较差。

2. 范式化与反范式化：
范式化： 通常建议遵循第三范式（3NF）来设计关系型数据库，减少数据冗余，提高数据一致性。
反范式化： 在读多写少的场景下，为了提高查询性能，可以适度进行反范式化。例如，将经常一起查询的字段合并到一个表中，或者引入冗余字段来避免复杂的 JOIN 操作。但这会增加写操作的复杂性和数据同步的成本，需要权衡。
反范式化的常用技巧：
冗余列： 在关联表中复制父表的部分信息，例如在订单表中存储客户名称（如果客户名称变化不频繁）。
汇总数据： 在订单表中存储每个订单的总金额，而不是每次都需要计算。
预计算结果： 对于复杂计算，可以预先计算并存储结果。

3. 垂直拆分（表字段拆分）：
将一个大字段表中字段数量过多的字段拆分到另一个表中。例如，将经常访问的少量字段放在主表中，不常用的、占用空间大的字段（如日志信息、用户详情中的某些大文本字段）放到另一个关联表中。
优点： 减少主表的数据行大小，提高扫描主表的效率；可以将不常用的字段放入单独的表中，避免在主表上进行不必要的 I/O。
缺点： 查询涉及多个表时，需要进行 JOIN 操作，增加查询复杂性。

4. 水平拆分（数据分片）：
当单个表的数据量过大，即使进行了索引优化，查询性能仍然无法满足要求时，可以考虑将表的数据分散到多个物理表中，这些表通常有相同或相似的结构。
拆分策略：
Hash 拆分： 根据某个字段（如用户 ID）的 Hash 值进行拆分。可以确保数据均匀分布，但范围查询可能需要扫描多个分片。
Range 拆分： 根据某个字段的范围进行拆分。例如，按时间范围（日期、月份）拆分，或按 ID 范围拆分。对于范围查询非常友好，但如果数据分布不均，可能导致某些分片过大。
List 拆分： 根据字段的特定值进行拆分。例如，按省份、区域拆分。
组合拆分： 结合 Hash、Range、List 等多种策略。
实现方式：
应用程序逻辑控制： 在应用程序代码中根据拆分规则选择目标表进行读写。
数据库中间件： 使用如 ShardingSphere、MyCat 等数据库中间件，它们可以代理数据库连接，在应用层对 SQL 进行解析和路由，实现透明的拆分。
MySQL Partitioning： MySQL 内置的分区功能。虽然不是真正的水平拆分（数据仍然在同一个物理实例上），但可以将数据逻辑上分散到不同的分区，并对分区进行管理。对于管理和某些查询有帮助，但对于 I/O 密集型查询的提升有限，且存在一些限制（例如，不能对分区字段建立外键，某些操作会涉及所有分区）。
优点： 显著提高查询性能，分散 I/O 压力，易于管理和维护大量数据。
缺点： 增加了系统的复杂性，需要应用程序或中间件支持。跨分片查询和 JOIN 操作会比较复杂。

三、 索引优化

索引是提高查询速度的关键，对于千万级大表更是如此。

1. 识别慢查询并分析：
使用 `EXPLAIN` 命令分析查询语句的执行计划。
开启慢查询日志 (`slow_query_log`)，记录执行时间超过阈值的 SQL 语句。
使用 `ptquerydigest` 等工具分析慢查询日志。

2. 创建合适的索引：
覆盖索引 (Covering Index)： 如果一个查询所需的所有列都在索引中，那么 MySQL 可以直接从索引中获取数据，而无需回表查询，大大提高了效率。
例如：`SELECT user_id, user_name FROM users WHERE status = 1;` 如果创建了 `(status, user_id, user_name)` 的复合索引，则可以实现覆盖。
复合索引： 将多个经常一起出现在 WHERE 子句、ORDER BY 子句或 GROUP BY 子句中的列组合成一个复合索引。
索引列顺序： 将选择性最强的列（基数最高）放在索引的最前面，遵循“最左前缀”原则。如果 WHERE 子句是 `WHERE col1 = ? AND col2 = ?`，则 `(col1, col2)` 的索引会比 `(col2, col1)` 更有效。
前缀索引： 对于过长的字符串列，可以为它们创建前缀索引（例如，`CREATE INDEX idx_name ON users (email(10));`），只索引字符串的前 N 个字符。这可以减少索引大小，但可能降低查询精度，需要测试。
避免过度索引： 过多的索引会增加写操作（INSERT, UPDATE, DELETE）的开销，并且占用更多的磁盘空间和内存。只在确实有性能提升的查询上创建索引。
索引类型：
BTree 索引： MySQL 默认也是最常用的索引类型，适用于大部分情况。
Hash 索引： 适用于精确匹配查询（`=`），不适合范围查询和排序。在 InnoDB 中不常用。
全文索引 (FULLTEXT)： 适用于文本内容的搜索。
空间索引 (SPATIAL)： 适用于地理空间数据的查询。

3. 优化索引使用：
避免在索引列上使用函数： 例如 `WHERE YEAR(create_time) = 2023` 会导致索引失效，应改为 `WHERE create_time BETWEEN '20230101 00:00:00' AND '20231231 23:59:59'`。
避免 `OR` 条件中混用不同索引的列： 如果 WHERE 子句是 `WHERE indexed_col1 = ? OR indexed_col2 = ?`，MySQL 可能无法有效地使用索引，或者需要进行多次索引扫描和合并。考虑将 `OR` 分解为多个独立的查询再合并结果，或者使用 `UNION ALL`。
`LIKE` 查询的优化：
以通配符开头 (`LIKE '%abc'`) 会导致索引失效。
以通配符结尾 (`LIKE 'abc%'`) 可以使用索引。
全文索引是处理复杂文本搜索的首选。
`ORDER BY` 和 `GROUP BY` 的优化： 确保 `ORDER BY` 或 `GROUP BY` 的字段与索引的顺序匹配，可以直接利用索引进行排序，避免额外的排序操作。

4. 索引维护：
索引统计信息： MySQL 会维护索引的统计信息，用于优化器选择最佳执行计划。定期更新统计信息可能有助于优化。
索引碎片化： 随着数据的插入、删除和更新，索引可能产生碎片，降低查询效率。定期重建索引（`ALTER TABLE ... ENGINE=InnoDB;` 或者 `OPTIMIZE TABLE ...;`）可以减少碎片。注意，重建索引会锁定表，需谨慎操作或在低峰期执行。

四、 查询优化

针对具体的查询语句进行优化是直接有效的手段。

1. 只选择需要的列： 避免使用 `SELECT `，明确列出需要的字段，可以减少网络传输和 I/O 开销，并可能实现覆盖索引。

2. 避免在 WHERE 子句中使用子查询（在某些情况下）：
如果子查询的返回结果集很大，可能会导致性能问题。可以尝试使用 JOIN 或临时表来代替。
示例：
```sql
慢
SELECT FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Beijing');
快（假设 customer_id 是 join key）
SELECT o. FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.city = 'Beijing';
```

3. 优化 JOIN 操作：
JOIN 类型： 理解 `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN` 的区别和性能影响。
JOIN 顺序： MySQL 的优化器通常会选择最佳的 JOIN 顺序，但有时手动指定可以提高性能。
JOIN 条件： 确保 JOIN 的列上有合适的索引。
使用 `EXISTS` 或 `NOT EXISTS` 替代 `IN` 或 `NOT IN`（在某些情况下）：
```sql
慢（当子查询返回大量数据时）
SELECT FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE is_active = 1);
快（当子查询返回较少数据或只需要判断是否存在时）
SELECT FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.is_active = 1);
```

4. 分页查询优化：
Offset 很大的分页问题： 当 `LIMIT` 后面跟着一个很大的 `offset` 时（例如 `LIMIT 1000000, 10`），MySQL 需要扫描并丢弃前面 1000000 条记录，这非常低效。
优化方案：
基于游标的分页： 使用上一页的最后一条记录的某个字段（通常是主键或有索引的字段）作为过滤条件。
```sql
第一页
SELECT FROM orders WHERE ... ORDER BY order_id LIMIT 10;
第二页，假设上一页最后一条记录的 order_id 是 12345
SELECT FROM orders WHERE order_id > 12345 AND ... ORDER BY order_id LIMIT 10;
```
延迟关联 (Deferred Join)： 先通过索引找到需要的主键 ID，然后再通过这些 ID 去主表查询详细信息。
```sql
SELECT o.
FROM orders o
INNER JOIN (
SELECT order_id
FROM orders
WHERE ...
ORDER BY order_id
LIMIT 1000000, 10
) AS sub ON o.order_id = sub.order_id;
```
这个方法对于大偏移量依然效率不高，但比直接使用 `OFFSET` 好一些。

5. 批量操作：
如果需要插入或更新大量数据，使用批量插入（`INSERT INTO ... VALUES (...), (...), ...`）或批量更新，而不是逐条执行，可以显著减少网络往返和数据库处理开销。

6. 避免使用 `SELECT INTO OUTFILE` 导数据到文件： 如果是简单的导出，这个方式可以直接从数据库服务器写到文件系统，效率高。但如果需要处理，在应用程序中处理更灵活。

五、 数据库服务器配置优化

合理的服务器配置对性能至关重要。

1. 内存配置：
`innodb_buffer_pool_size`： 这是 InnoDB 存储引擎最重要的参数，它决定了多少数据和索引可以被缓存到内存中。通常设置为物理内存的 70%80%，并且尽量不要超过物理内存的大小。
`key_buffer_size`： 如果你还在使用 MyISAM 存储引擎，这个参数是关键。
`query_cache_size`： 默认情况下是关闭的，在 MySQL 8.0 中已被移除。在旧版本中，如果查询命中率不高，开启它反而可能降低性能，因为它有查询缓存的维护开销。对于频繁更新的数据表，不建议开启。

2. I/O 配置：
`innodb_log_file_size` 和 `innodb_log_files_in_group`： 控制 InnoDB 的 Redo Log 的大小。较大的 Redo Log 可以减少 checkpoint 的频率，提高写性能，但如果发生崩溃恢复，可能需要更长时间。
`innodb_flush_log_at_trx_commit`：
`1`（默认）：每次事务提交都将 Redo Log 写入磁盘并刷盘，最安全但性能最低。
`2`：每次事务提交写入 Redo Log 到操作系统缓存，每秒刷盘一次。性能较高，但如果操作系统崩溃，可能会丢失最后几秒的事务。
`0`：每秒写一次日志到文件并刷盘，性能最高，但如果 MySQL 进程崩溃或宕机，可能会丢失最多一秒的事务。
对于对数据持久性要求非常高的场景，选择 `1`。如果能容忍极少量数据丢失以换取性能，可以考虑 `2`。
`innodb_flush_method`： 设置为 `O_DIRECT` 可以绕过操作系统的文件缓存，直接读写磁盘，在某些情况下可以避免双重缓存的开销，提高 I/O 性能，特别是在磁盘 I/O 瓶颈时。

3. 并发与连接：
`max_connections`： 根据服务器的实际能力和应用的需求来设置，过高可能导致资源耗尽。
`thread_cache_size`： 缓存线程，避免每次创建和销毁线程的开销。

4. 其他重要参数：
`sort_buffer_size`： 用于排序操作，每个连接都会分配。
`join_buffer_size`： 用于连接操作，当没有使用索引时，会分配给每个 join。
`read_rnd_buffer_size`： 读取行时使用的缓冲区，例如 `ORDER BY` 之后。

六、 架构与系统级优化

除了数据库本身，还可以从更高的层面进行优化。

1. 读写分离：
将读请求和写请求分发到不同的数据库实例（主库处理写，从库处理读）。
优点： 分担主库的压力，提高整体的吞吐量。
缺点： 需要应用层或中间件支持，可能存在主从延迟问题。

2. 数据库集群与高可用：
使用 MySQL Cluster、Percona XtraDB Cluster 或 Galera Cluster 来实现数据的高可用和负载均衡。

3. 缓存层：
在应用程序和数据库之间引入缓存层，如 Redis 或 Memcached，缓存热点数据，减少数据库的访问压力。这对于读多写少的场景尤为有效。

4. CDN（内容分发网络）：
如果应用涉及大量静态内容，可以利用 CDN 来加速内容的分发，减轻服务器压力。

5. 硬件升级：
SSD 固态硬盘： 相较于 HDD 机械硬盘，SSD 的随机 I/O 性能有质的飞跃，对于数据库这种 I/O 密集型应用至关重要。
更快的 CPU 和更大的内存： 提升服务器的整体处理能力。

七、 持续监控与调优

数据库优化是一个持续的过程，而非一劳永逸。

1. 性能监控： 使用专业的监控工具（如 Prometheus + Grafana, Zabbix, Percona Monitoring and Management (PMM) 等）持续监控数据库的各项指标，包括 CPU 使用率、内存使用率、I/O 吞吐量、慢查询数量、连接数、缓存命中率等。

2. 定期审查： 定期审查慢查询日志，分析新出现的性能问题。

3. 测试与验证： 在生产环境进行任何重大更改之前，务必在测试环境进行充分的测试和验证，模拟生产环境的负载，确保优化措施有效且不会引入新的问题。

总结：

优化千万级大表是一个系统性的工程，需要结合具体业务场景、查询模式和硬件环境来综合施策。通常的优化流程是：

1. 识别瓶颈： 找出性能问题的根源，是 CPU、I/O、内存还是锁？
2. 从数据模型开始： 检查表结构和数据类型。
3. 索引优化： 这是最直接有效的方式。
4. 查询优化： 针对性地改进慢查询。
5. 配置调优： 根据瓶颈调整服务器配置。
6. 架构升级： 如果上述方法仍然无法满足需求，考虑架构层面的改动。

重要的提示：

备份！备份！备份！ 在进行任何可能影响数据库结构或配置的更改之前，务必做好完整的数据备份。
小步快跑，逐步验证： 每次只做一项优化，然后观察效果，避免一次性进行太多更改导致难以定位问题。
理解你的数据和业务： 只有深入理解你的数据分布、查询模式以及业务需求，才能做出最有效的优化决策。

希望这份详细的指南能帮助你更好地优化千万级大表！

很多人第一反应是各种切分；我给的顺序是:

第一优化你的sql和索引；

第二加缓存，memcached,redis；

第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；

第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；

第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高；

有人也许要说第一步优化sql和索引这还用说吗？的确，大家都知道，但是很多情况下，这一步做的并不到位，甚至有的只做了根据sql去建索引，根本没对sql优化（中枪了没？），除了最简单的增删改查外，想实现一个查询，可以写出很多种查询语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点；即使精通mysql的话，除了纯技术面优化，还要根据业务面去优化sql语句，这样才能达到最优效果；你敢说你的sql和索引已经是最优了吗?

再说一下不同引擎的优化，myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,

innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；

ps:很奇怪，为什innodb的索引叶子节点存的是主键而不是像mysism一样存数据的物理地址指针吗？如果存的是物理地址指针不就不需要二次查找了吗，这也是我开始的疑惑，根据mysism和innodb数据存储方式的差异去想，你就会明白了，我就不费口舌了！

所以innodb为了避免二次查找可以使用索引覆盖技术，无法使用索引覆盖的，再延伸一下就是基于索引覆盖实现延迟关联；不知道什么是索引覆盖的，建议你无论如何都要弄清楚它是怎么回事！

尽你所能去优化你的sql吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！

实用预警：回答将以实际项目经验为例，为你解读优化的奥义！

首先采用Mysql存储千亿级的数据，确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据，只是这个时候性能非常差，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。

针对大表的优化，主要是通过数据库分库分表来解决，目前比较普遍的方案有三个：分区，分库分表，NoSql/NewSql。实际项目中，这三种方案是结合的，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSql/NewSql存储为辅。

分区

首先来了解一下分区方案。分区表是由多个相关的底层表实现的。这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引。这个方案对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。

下面开始举例：以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。基本结构大致如下：

查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。这样的设计绝对是不行的，无论是插入，还是查询，都会让系统崩溃。

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看）， 一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，Mysql支持的分区方式有四种：

在我们的项目中，range分区和list分区没有使用场景，如果基于绑定编号做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询，因此，我们就剩下 HASH 分区和 KEY 分区了，HASH分区仅支持int类型列的分区，且是其中的一列。 KEY 分区倒是可以支持多列，但也要求其中的一列必须是int类型；看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。

这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么？

因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，mysql都会将所有的分区表查询一遍。进行第二次方案优化，既然 HASH 分区和 KEY分区要求其中的一列必须是int类型的，那么创造出一个int类型的列出来分区是否可以？分析发现，银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串，我们取其中的某一位拿出来作为表分区是否可行呢，通过分析发现，在这串数字中，其中确实有一位是0到9随机生成的，我们基于银行卡号+随机位进行KEY分区，每次查询的时候，通过计算截取出这位随机位数字，再加上卡号，联合查询，达到了分区查询的目的，需要说明的是，分区后，建立的索引，也必须是分区列，否则Mysql还是会在所有的分区表中查询数据。

通过银行卡号查询绑定关系的问题解决了，那么证件号呢，如何通过证件号来查询绑定关系。前面已经讲过，做索引一定是要在分区健上进行，否则会引起全表扫描。我们再创建了一张新表，保存客户的证件号绑定关系，每位客户的证件号都是唯一的，新的证件号绑定关系表里，证件号作为了主键，那么如何来计算这个分区健呢，客户的证件信息比较庞杂，有身份证号，港澳台通行证，机动车驾驶证等等，如何在无序的证件号里找到分区健。为了解决这个问题，我们将证件号绑定关系表一分为二，其中的一张表专用于保存身份证类型的证件号，另一张表则保存其他证件类型的证件号，在身份证类型的证件绑定关系表中，我们将身份证号中的月数拆分出来作为了分区健，将同一个月出生的客户证件号保存在同一个区，这样分成了12个区，其他证件类型的证件号，数据量不超过10万，就没有必要进行分区了。这样每次查询时，首先通过证件类型确定要去查询哪张表，再计算分区健进行查询。作了分区设计之后，保存2000万用户数据时银行卡表的数据保存文件就分成了10个小文件，证件表的数据保存文件分成了12个小文件，解决了这两个查询的问题，还剩下一个问题：业务编号怎么办？一个客户有多个签约业务，如何进行保存？这时候，采用分区的方案就不太合适了，它需要用到分表的方案。

分表

我们前面有提到过对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大时，操作系统对大文件的操作就会比较麻烦耗时，且有的操作系统就不支持大文件，这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，查询一个节点可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。所以综上我们就必须进行分表与分库的操作。

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：阿里的TDDL，DRDS和cobar，京东金融的sharding-jdbc；民间组织的MyCAT；360的Atlas；美团的zebra；其他比如网易，58，京东等公司都有自研的中间件。

这么多的分库分表中间件方案归总起来，就两类：client模式和proxy模式。

无论是client模式，还是proxy模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。个人比较倾向于采用client模式，它架构简单，性能损耗也比较小，运维成本低。

如何对业务类型进行分库分表。分库分表最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中，sharding column无疑最好的选择是业务编号。通过业务编号，将客户不同的绑定签约业务保存到不同的表里面去，根据业务编号路由到相应的表中进行查询，达到进一步优化sql的目的。