天天看点
返回

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

目录

 

一、mysql8.0源码编译过程

空间要求：

1.安装cmake

2.安装gcc

3.mysql8的编译

4.mysql的启动

二、mysql异步复制

什么是异步复制

1.基于二进制日志文件位置的复制（Binary Log File Position Based Replication）

1）原理

2）实现步骤

2.基于GTID的主从复制

1）原理

2）实现步骤

3.一主多从复制

1）原理

2）实现步骤

4.主从复制（异步复制）的作用与解决的问题：

三、全同步复制与半同步复制

1.全同步复制（组复制）

1）原理

2）实现步骤

2.半同步复制

1）原理

2）半同步的两种方案：After_commit与After_sync

3）半同步的实现过程

异步、半同步、组复制（全同步）的对比

四、mysql读写分离、分表操作

1.MySQLProxy读写分离原理 

2.为什么要读写分离？

3.什么时候需要读写分离？

4.主从复制与读写分离

五、MHA高可用

1.MHA介绍

2.实验步骤

1）配置半同步主从复制

2）配置免密登录

3）在server1/2/3中安装MHA node

4）安装MHA Manager并配置MHA

5）测试

六.索引

什么是索引？

索引有哪些优缺点？

索引有哪几种类型？

索引的数据结构（b树，hash）

索引设计的原则？

创建索引的原则（重中之重）

B树和B+树的区别

使用B树的好处

使用B+树的好处

百万级别或以上的数据如何删除

七.引擎

MySQL存储引擎MyISAM与InnoDB区别

MyISAM索引与InnoDB索引的区别？

InnoDB引擎的4大特性

存储引擎选择

一、mysql8.0源码编译过程

空间要求：

20G硬盘

1.安装cmake 

yum install jsoncpp-0.10.5-2.el7.x86_64.rpm

yum install jsoncpp-devel-0.10.5-2.el7.x86_64.rpm


yum install cmake3-3.6.1-2.el7.x86_64.rpm cmake3-data-3.6.1-2.el7.noarch.rpm -y ##因为有依赖性，同时安装



cd /usr/bin

ln -s /usr/bin/cmake3 /usr/local/bin/cmake ##制作软链接

camke --version ##查看版本
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

2.安装gcc

GCC原名为GNU C语言编译器（GNU C Compiler），只能处理C语言。但其很快扩展，变得可处理C++，后来又扩展为能够支持更多编程语言，如Fortran、Pascal、Objective -C、Java、Ada、Go以及各类处理器架构上的汇编语言等，所以改名GNU编译器套件（GNU Compiler Collection）

cd /etc/yum.repos.d

vim CentOS-SCL.repo


cat CentOS-SCL.repo


[base]

name=CentOS-os

baseurl=http://mirrors.aliyun.com/centos/7/os/x86_64/

gpgcheck=0



[centos-sclo-sclo]

name=CentOS-7 - SCLo sclo

baseurl=http://mirrors.aliyun.com/centos/7/sclo/x86_64/sclo/

gpgcheck=0


[centos-sclo-rh]

name=CentOS-7 - SCLo rh

baseurl=http://mirrors.aliyun.com/centos/7/sclo/x86_64/rh/

gpgcheck=0


yum install devtoolset-7-gcc*

scl enable devtoolset-7 bash


which gcc

gcc --version
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

3.mysql8的编译 

tar zxf mysql-boost-8.0.21.tar.gz
mkdir build 
cd build
 
cmake ../ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc -DENABLED_LOCAL_INFILE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_0900_ai_ci -DWITH_BOOST=/root/mysql-8.0.21/boost/boost_1_72_0/
 
make
make install

 

4.mysql的启动 

cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
vim /etc/my.cnf
useradd -M -d /usr/local/mysql/ -s /sbin/nologin mysql
mkdir -p /data/mysql
chown mysql.mysql /data/mysql/
vim ~/.bash_profile 
source ~/.bash_profile 
mysqld --initialize --user=mysql
mysql -p
 
mysql> alter user [email protected] identified by '[email protected]@0823';

 

二、mysql异步复制

主从复制分为两种方式：基于二进制文件位置的主从复制以及基于GTID的主从复制，而这两种复制的本质均为异步复制。

什么是异步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时强行将从提升为主，可能导致新主上的数据不完整。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

1.基于二进制日志文件位置的复制（Binary Log File Position Based Replication）

1）原理

1.在主库上把数据更改记录到二进制日志(BinaryLog)中(这些记录被称为二进制日志事件)

2.备库上的IO线程与master进行通信，将主库上的日志复制到自己的中继(RelayLog)日志中

3.备库上SQL线程会读取中继日志中的事件，将其重放到备数据库之上

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

2）实现步骤

master端：

（1） vim /etc/my.cnf  编辑主配置

log-bin=mysql-bin 
server-id=1

（2）创建用户repl并授予复制权限

mysql> CREATE USER 'repl'@'192.168.43.%' IDENTIFIED BY 'My12345.';##创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.43.%';##对用户授权

（3）查看master状态并获取主节点上二进制文件名称和位置

mysql> SHOW MASTER STATUS;
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

slave端：

（1）配置从节点   vim /etc/my.cnf

server-id=2 ##从节点中的配置

（2） 在从节点slave上配置主节点master的信息,并开启复制：

mysql> change master to
    -> master_host='172.25.11.2',
    -> master_user='repl',
    -> master_password='[email protected]@0823',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=1200,
    -> get_master_public_key=1;

mysql> START SLAVE;##开启复制
mysql> SHOW SLAVE STATUS\G ##查看slave状态
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

效果：

此时在master端写入数据，slave端会同步

 

2.基于GTID的主从复制

1）原理

使用GTID时，每个事务都可以在提交到原始服务器上并由任何从属应用时进行标识和跟踪。这意味着在启动新的从属服务器或故障转移到新的主服务器时，不必使用GTID来引用日志文件或这些文件中的位置，从而极大地简化了这些任务。由于基于GTID的复制完全基于事务，因此可以轻松确定主服务器和从服务器是否一致。只要在主服务器上提交的所有事务也都在从服务器上提交，则可以保证两者之间的一致性。

 

2）实现步骤

master端：

配置主节点：启用复制主节点，开启gtid服务   /etc/my.cnf

log-bin=mysql-bin ##基于二进制日志位置的主从复制
server-id=1
gtid_mode=ON ##打开gtid
enforce-gtid-consistency=ON

slave端：

（1）配置从站slave开启GTID的服务    /etc/my.cnf

server-id=2   ##基于二进制日志位置的主从复制
gtid_mode=ON  ##打开gtid
enforce-gtid-consistency=ON

（2） 配置从站slave使用基于GTID的自动定位

mysql> stop slave; ##停止基于二进制位置的复制

mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.43.10',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'My12345.',
    -> MASTER_AUTO_POSITION = 1;
    
mysql> START SLAVE; ##开启gtid的主从复制
mysql> SHOW SLAVE STATUS\G ##查看复制状态
mysql> show variables like 'gtid%'; 查看gtid是否开启

 

3.一主多从复制

1）原理

a --> b --> c，即b作为a的salve获取a的数据后，再作为c的主，将数据传输给c。

2）实现步骤

实现a、b的主从复制后，加入节点c使c作为b的从节点。具体步骤如下：

（1）在b上创建复制用户并授权，并用mysqldump将已存储的数据导出，scp至c中。

（2）在c中导入b的数据后，执行change master to 成为b的从，并开启复制。

 

4.主从复制（异步复制）的作用与解决的问题：

主从复制的作用

  1. 主数据库出现问题，可以切换到从数据库。
  2. 可以进行数据库层面的读写分离。
  3. 可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

  • 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
  • 负载均衡：降低单个服务器的压力
  • 高可用和故障切换：帮助应用程序避免单点失败
  • 升级测试：可以用更高版本的MySQL作为从库

 

三、全同步复制与半同步复制

1.全同步复制（组复制）

至少三个服务器，7个最为合适，效果为每个节点都可进行读写

1）原理

全同步复制又称组复制，指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

MySQL组复制为分布式状态机复制提供了服务器之间的强大协调。服务器属于同一组时，它们会自动进行协调。该组可以在具有自动主要选举的单主要模式下运行，其中一次仅一个服务器接受更新。或者，对于更高级的用户，可以在多主要模式下部署组，在该模式下，所有服务器都可以接受更新，即使它们是同时发布的。这种功能的代价是应用程序必须解决此类部署所施加的限制。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

2）实现步骤

master A：

1）vim /etc/my.cnf 配置组复制

server_id=1  ##配置基本框架
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so' ##组复制设置
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="8acb9904-9452-11ea-b4ac-000c29bb3e1d"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.43.10:33061"
loose-group_replication_group_seeds= "192.168.43.10:33061,192.168.43.20:33061,192.168.43.30:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist='127.0.0.1,192.168.43.0/24'
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

2) 创建一个具有REPLICATION-SLAVE特权的MySQL用户

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

master B、C：

同样配置基本框架及组复制设置，并设定用户并授权

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

实现的效果：

此时在任意一个master中插入数据，三台mysql均可见，即处于组复制中的mysql均可读写。

2.半同步复制

1）原理

如果在主服务器端启用了半同步复制，并且至少有一个半同步从服务器，则在主服务器上执行事务提交的线程将阻塞并等待，直到至少一个半同步从服务器确认已接收到该事务的所有事件为止，或者直到发生超时。

从属仅在将事件写入其中继日志并刷新到磁盘后，才确认接收到事务的事件。如果发生超时而没有任何从属服务器确认该事务，则主服务器将还原为异步复制。当至少有一个半同步从属服务器赶上时，主服务器将返回到半同步复制。

必须在主服务器和从服务器上都启用半同步复制。如果在主服务器上禁用了半同步复制，或者在主服务器上但没有从属服务器上启用了半同步复制，则主服务器将使用异步复制。

 

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

2）半同步的两种方案：After_commit与After_sync

         After_commit是MSQL5.6半同步参数，区别于After_sync，After_sync是在接收ack确认以后主库在引擎层做提交，而After_commit是先在引擎层做提交后等待ACK确认。因此，在写入数据后并且在从库确认之前，其他的客户端可以看到在这一事务。

故障分析：

1.binlog 未发送到从库:

    事务B获取到事务A提交的内容， 此时宕机故障切换到slave，事务B获取到的内容却丢失了。事务A commit没有收到反馈信息(则需要业务判断了)。

2.binlog 已经发送给从库 :

    事务B获取到事务A提交的内容，故障切换到salve ，B仍然获取到A提交的内容，没毛病。事务A commit没有收到反馈信息，若重新执行该事务，则相当于执行两次A事务(则需要业务判断了)。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

      After_sync是MySQL5.7官方新加的用以解决MySQL5.6半同步缺陷的选项，也是官方推荐的方式。实际上，客户端发出commit请求后，在主库上写入binlog并推送给slave，slave接收到binlog并写入relaylog，发送ACK确认已经接收binlog后，master在引擎层commit，客户端接收commit完成，此时其他会话才可以看见已提交的数据。

    故障分析：假设master在接收ACK确认时宕机，因为在引擎层并没有提交，HA切换到从库，因为binlog已经写入从库的relaylog，因此不会造成数据丢失，个人认为是目前比较完美的解决方式。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

3）半同步的实现过程

master：

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎
知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

slave：

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

实现的效果：

在主服务器上更新数据，从服务器可以收到更新。若中断从服务器上的IO线程并在主服务器上更新数据，若超时后仍未收到从服务器的确认，主服务器会自动转为异步复制。此时再次开启从服务器上的IO线程，接收到更新。

如果想避免回到异步复制，可以将timeout设为无穷大

 

异步、半同步、组复制（全同步）的对比

1）复制速率：

异步 > 半同步 > 全同步

因为异步只管发送，不管slave是否接收。半同步需要至少一个slave的ack确认；而全同步需要所有ack确认才可完成复制。

 

2)  数据一致性:

全同步 > 半同步 > 异步

道理同上

 

四、mysql读写分离、分表操作

1.MySQLProxy读写分离原理 

MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

 

2.为什么要读写分离？

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。 

但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。 

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

 

3.什么时候需要读写分离？

 数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库 主从同步 。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是 表折分，或是搜索引擎。都是解决方法。

 

4.主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

 

 

五、MHA高可用

1.MHA介绍

MHA（Master High Availability）由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。MHA可以与半同步复制结合起来，可以大大降低数据丢失的风险。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

目前MHA主要支持一主多从的架构。要搭建MHA，要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。因为至少需要三台服务器。

 

2.实验步骤

一共需要四台主机

server1：192.168.43.10，MHA node,作为master

server2：192.168.43.20，MHA node,作为slave和备用master

server3：192.168.43.30，MHA node,作为slave

server4：192.168.43.40，MHA manager

1）配置半同步主从复制

master：创建用户并授权、安装半同步复制插件并激活、建立测试库

slave：用change master to配置复制复制方式、安装半同步复制插件并激活、重启IO线程

 

2）配置免密登录

MHA manager端（server4）生成秘钥并发送至1、2、3

 

3）在server1/2/3中安装MHA node

 

4）安装MHA Manager并配置MHA

 

5）测试

手动离线切换：

down掉master后，手动在manager端设置新的master

masterha_master_switch --master_state=dead --conf=/etc/masterha/masterha.cnf --
dead_master_host=192.168.43.10 --dead_master_ip=192.168.43.10 --dead_master_port=3306 --
new_master_host=192.168.43.20 --new_master_port=3306 --ignore_last_failover

手动在线切换：

旧的master依然存活，在master端直接切换新的master

masterha_master_switch --conf=/etc/masterha/masterha.cnf --master_state=alive --
new_master_host=192.168.43.10 --new_master_port=3306 --orig_master_is_new_slave --
running_updates_limit=10000**使原来的master变为slave，并限制10s

 

自动切换：

manager端设置自动切换（此进程仅生效一次）

nohup masterha_manager --conf=/etc/masterha/masterha.cnf &> /dev/null &

此时挂掉server1中的master，会自动切换

 

绑定vip切换：

 

六.索引

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  • 空间方面：索引需要占物理空间。

索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 

    ALTER TABLE table_name ADD UNIQUE (column);

      创建唯一索引
  • 可以通过 

    ALTER TABLE table_name ADD UNIQUE (column1,column2);

      创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过

    ALTER TABLE table_name ADD INDEX index_name (column);

     创建普通索引
  • 可以通过

    ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

     创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

  • 可以通过

    ALTER TABLE table_name ADD FULLTEXT (column);

     创建全文索引

索引的数据结构（b树，hash）

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

1）B树索引

mysql通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

查询方式：

主键索引区:PI(关联保存的时数据的地址)按主键查询,

普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快

B+tree性质：

1.）n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.）B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5.）B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

2）哈希索引

简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

索引设计的原则？

  1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  2. 基数较小的类，索引效果较差，没有必要在此列建立索引
  3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
  4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向左匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）区分度低的字段不适合做索引，比如性别

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

B树和B+树的区别

  • 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
  • B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
    知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎

使用B树的好处

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

  1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
  2. 然后删除其中无用数据（此过程需要不到两分钟）
  3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
  4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

 

七.引擎

MySQL存储引擎MyISAM与InnoDB区别

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有以下：

  • Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
  • MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB区别

知识总结（15）mysql面试考点之源码编译、主从复制(异步复制)、全同步复制、半同步复制、读写分离、MHA高可用一、mysql8.0源码编译过程 二、mysql异步复制三、全同步复制与半同步复制 四、mysql读写分离、分表操作五、MHA高可用六.索引七.引擎
  MyISAM Innodb
存储结构 每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间 MyISAM可被压缩，存储空间较小 InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式 数据和索引是分别存储的，数据

.MYD

 ，索引

.MYI

 		数据和索引是集中存储的，

.ibd
记录存储顺序 按记录插入顺序保存 按主键大小有序插入
外键 不支持 支持
事务 不支持 支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） 表级锁定 行级锁定、表级锁定，锁定力度小并发能力高
SELECT MyISAM更优  
INSERT、UPDATE、DELETE   InnoDB更优
select count(*) myisam更快，因为myisam内部维护了一个计数器，可以直接调取。  
索引的实现方式 B+树索引，myisam 是堆表 B+树索引，Innodb 是索引组织表
哈希索引 不支持 支持
全文索引 支持 不支持

MyISAM索引与InnoDB索引的区别？

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

  • 插入缓冲（insert buffer)
  • 二次写(double write)
  • 自适应哈希索引(ahi)
  • 预读(read ahead)

存储引擎选择

如果没有特别的需求，使用默认的

Innodb

即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

面试必看
上一篇: 知识总结（16）docker面试常问点（什么是docker、docker优缺点、docker与虚拟机的区别、docker如何实现隔离）什么是docker？docker的应用环境？docker的原理与组成docker与虚拟机的区别？docker镜像构建方法docker镜像优化方法docker三种原生网络docker自定义网络docker容器间的通信docker跨主机的容器网络docker是如何实现隔离的？
下一篇: 项目CMDBLVS-DR keepalived nginxtomcat+memcache会话保持mysql主从复制redis+mysql+gearmand zabbix监控NGINX SaltstackAnsible部署负载均衡高可用集群

继续阅读