DBA笔记-第六部分（索引）

创建索引

show create table a;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------+

首先我有一个表a 里面有两个列 a和b ,要对a列创建一个普通索引，对b列创建唯一(不能重复)索引  (key和index一样)

alter table a add index idx_a(a);

alter table a add unique index idx_b(b);

show create table a;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 那么对于修改表时会不会阻塞，也就是哪些操作能执行，哪些不能执行呢？

首先msyql5.6以后会把修改索引记录到内存Redo日志中，这个日志大小是可以查看修改的，修改较大的表时如果这个日志大小不够，那么online的DDL操作会报错或失败。修改表之前建议调大这个全局变量，配置高的可以设置为1G ，但是在线上我们一般不使用alter table操作，因为主从会有延时（下面讲解）。

set global innodb_online_alter_log_max_size=512*1024*1024;

show variables like 'innodb%max%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| innodb_adaptive_max_sleep_delay  | 150000     |
| innodb_change_buffer_max_size    | 25         |
| innodb_compression_pad_pct_max   | 50         |
| innodb_file_format_max           | Barracuda  |
| innodb_ft_max_token_size         | 84         |
| innodb_io_capacity_max           | 2000       |
| innodb_max_dirty_pages_pct       | 75.000000  |
| innodb_max_dirty_pages_pct_lwm   | 0.000000   |
| innodb_max_purge_lag             | 0          |
| innodb_max_purge_lag_delay       | 0          |
| innodb_max_undo_log_size         | 1073741824 |
| innodb_online_alter_log_max_size | 134217728  |-- 就是这条，大小为128m，128*1024*1024
+----------------------------------+------------+

删除索引

删除索引idx_b,注意这些都是对表的修改，不要写成drop table  a index .......

alter table a drop index idx_b;

show create table a;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+

主从复制

 举个最简单的例子：

主服务器 (Master)
+----------------+
|    数据库      |
|                |
|  二进制日志    |
|                |
+----------------+
         |
         | 复制数据（Binary Log）
         |
从服务器 (Slave)
+----------------+
|    数据库      |
|                |
|  中继日志      |
|                |
+----------------+

为什么尽量不使用alter table

如果使用alter table 会导致出现五分钟时间差。mysql是逻辑备份，oracle是物理备份

1. 锁定问题
表锁定：

在执行 ALTER TABLE 时，MySQL 需要对整个表进行锁定。这个锁定可能会导致其他数据库操作（如查询和写入）被阻塞，影响系统的并发性能。对于高负载系统或业务高峰期，这种锁定会导致显著的性能下降。
行级锁定：

虽然某些操作可能只需要行级锁定，但仍然可能会对性能产生影响，特别是当操作需要对大量数据进行修改时。

2. 操作时间
长时间运行：

一些 ALTER TABLE 操作可能需要很长时间才能完成，特别是当表很大或者操作非常复杂时。在此期间，表可能会被锁定，导致应用程序无法访问数据。
影响性能：

即使操作可以在后台完成，数据库在执行这些操作时通常会消耗大量的I/O和CPU资源，可能会影响到其他数据库操作的性能。

3. 在线操作的复杂性
在线模式的限制：

虽然 MySQL 在 5.6 及更高版本中引入了“在线”模式的 ALTER TABLE（即 ALGORITHM=INPLACE），但并不是所有的 ALTER TABLE 操作都可以完全无锁地进行。某些操作仍然可能需要锁定表，或者在某些情况下无法以完全在线的方式进行。
版本和存储引擎：

不同的 MySQL 版本和存储引擎对 ALTER TABLE 支持的在线操作的能力不同。例如，InnoDB 存储引擎在较新的版本中对在线 DDL 操作支持较好，但在旧版本中可能存在限制。

4. 回滚和恢复
数据恢复：

如果 ALTER TABLE 操作失败，可能需要回滚，回滚过程可能会对数据库性能产生影响，并且可能导致数据不一致。
备份和恢复：

在执行 ALTER TABLE 之前，通常需要确保数据库有完整的备份，以防操作失败或发生其他问题时能够恢复数据。

5. 影响应用程序
应用程序停机时间：

如果操作时间较长，可能会导致应用程序在执行 ALTER TABLE 期间无法访问数据库，影响业务运营。
兼容性：

一些应用程序可能与表结构变化不兼容，特别是在执行重大变更时，例如添加或删除列，可能需要修改应用程序代码。

避免影响的方法
使用在线 DDL：

对于支持在线 DDL 的版本和存储引擎，可以使用 ALGORITHM=INPLACE 或 LOCK=NONE 来减少锁定和停机时间。
分阶段进行：

对于较大的表，可以考虑将 ALTER TABLE 操作分阶段进行，例如分批次添加索引或分段修改表结构。
非高峰时段操作：

尽量在数据库负载较低的非高峰时段进行结构修改，以减少对用户的影响。
备份和测试：

在进行生产环境的 ALTER TABLE 操作之前，确保进行充分的备份，并在测试环境中模拟操作，评估对性能的影响。