MySQL面试，MySQL事务，MySQL锁，MySQL集群，主从，MySQL分区，分表，InnoDB

皮皮攻城狮

已于 2023-11-22 21:23:44 修改

阅读量790

点赞数 6

分类专栏： mysql SQL 文章标签： mysql 面试

于 2023-11-22 21:21:16 首次发布

本文链接：https://blog.csdn.net/weixin_44797327/article/details/134563105

版权

mysql 同时被 2 个专栏收录

21 篇文章 2 订阅

订阅专栏

SQL

4 篇文章 0 订阅

订阅专栏

文章目录

数据库-MySQL

数据库-MySQL

MySQL主从、集群模式简单介绍

主从模式、集群模式，都是在一个项目中使用多个mysql节点进行存储和读取数据。

当单机模式部署，不满足安全性、高可用、高并发等需求的时候，就需要考虑主从模式或者集群模式部署。

1、主从模式 Replication

主从模式，或者是叫主从架构、主从复制，有以下几种常见方案：一主一从、一主多从、多主一从、互为主备、级联复制等。
主数据库必须开启binary log（二进制）功能，因为主从同步所有的操作都是基于二进制文件来完成的。
数据同步模式有：

异步模式：主库将事务binlog事件写入到binlog文件中，此时主库只会通知一下dump线程发送这些新的binlog，然后主库就会继续处理提交操作，而此时不会保证这些binlog传到任何一个从库节点上。
半同步模式：主库只需要等待至少一个从库节点收到并且Flush binlog到relay-log文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全执行并且提交的反馈。
全同步模式：当主库提交事务之后，所有的从库节点必须全部收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。

1、主从复制指的是当主数据库中进行了update、insert、delete操作导致数据发生改变时，变化会实时同步到一个或者多个从数据库（slave）中。

2、默认情况下异步复制、无需维持长连接。

3、通过配置可以选择想要同步的库和表。

2、集群模式

集群最大的优点就是数据实时同步，高可用，每个节点的数据都是同步一致的，不像主从，有时会出现数据不一致，而高可用，任何一个节点宕机都不会影响业务。

集群模式有以下集中常见部署方式：

读写分离的集群模式：集群中有的节点只进行写入操作，有的节点只进行读取操作，每个节点的数据都是完全一致的。
分片（分库分表）集群模式：集群中所有的节点表结构一致，每个节点存储的数据不一样。分片算法主要有两种，一种是范围法（1-100条数据在节点A，101-200条数据在节点B），另一种是HASH法（对每条数据按照一定的算法分配到不同的存储节点）。
读写分离和分片模式组合应用，先进行分片模式部署，然后对每个分片进行读写分离模式部署。

在这里插入图片描述

3、主从模式部署注意事项

常用命令（执行命令之前stop服务，执行完再start）：

查看主节点状态：show master status\G;
查看从节点状态：show slave status\G;
停止同步：stop slave;
开启同步：start slave;
修改Master_Log_File：CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000026’, MASTER_LOG_POS=0;
修改master节点信息：CHANGE MASTER TO MASTER_HOST=‘192.168.203.141’, MASTER_PORT=33060,MASTER_USER=‘root’, MASTER_PASSWORD=‘123456’;
删除当前节点的 binlog 文件：PURGE BINARY LOGS TO ‘binlog.000001’;
数据的操作日志位置：SHOW BINARY LOGS;
数据的真实具体位置：SHOW GLOBAL VARIABLES LIKE “%datadir%”;

每个节点的slave_sql_running、Slave_IO_Running两个字段都是YES，集群状态才正常
主服务器查看主节点状态，显示的 File 字段，和从服务器查看从节点状态，显示的 Master_Log_File 字段，必须保持一致。
slave_sql_running为No的话，可能是主从库数据不同步，可以同步一下数据。

数据导出命令（在mysql服务器执行，不需要登录数据库）：

mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] --databases 数据库名 --tables 表名 >导出的文件名.sql

数据库导入命令（导入的时候需要指定数据库，保证指定的数据库存在）：

mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] < 导入的文件名.sql

UNION 和 UNION ALL 区别

一、区别1：取结果的交集

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

二、区别2：获取结果后的操作

1、union: 会对获取的结果进行排序操作

2、union all: 不会对获取的结果进行排序操作

三、总结

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

分库分表

链接： MySQL-如何分库分表？一看就懂_mysql分库分表怎么实现-CSDN博客

一、为什么要分库分表
如果一个网站业务快速发展，那这个网站流量也会增加，数据的压力也会随之而来，比如电商系统来说双十一大促对订单数据压力很大，Tps十几万并发量，如果传统的架构（一主多从），主库容量肯定无法满足这么高的Tps，业务越来越大，单表数据超出了数据库支持的容量，持久化磁盘IO,传统的数据库性能瓶颈，产品经理业务·必须做，改变程序，数据库刀子切分优化。数据库连接数不够需要分库，表的数据量大，优化后查询性能还是很低，需要分。

二、什么是分库分表
分库分表方案是对关系型数据库数据存储和访问机制的一种补充。
分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库
分表：把一个表的数据放到多个表中，操作对应的某个表就行

三、分库分表的几种方式

在这里插入图片描述

1.垂直拆分

(1) 数据库垂直拆分

在这里插入图片描述

根据业务拆分，如图，电商系统，拆分成订单库，会员库，商品库

(2)表垂直拆分

在这里插入图片描述

根据业务去拆分表，如图，把user表拆分成user_base表和user_info表，use_base负责存储登录，user_info负责存储基本用户信息

垂直拆分特点
1.每个库（表）的结构都不一样
2.每个库（表）的数据至少一列一样
3.每个库（表）的并集是全量数据

垂直拆分优缺点

优点：
1.拆分后业务清晰（专库专用按业务拆分）
2.数据维护简单，按业务不同，业务放到不同机器上

缺点:
1.如果单表的数据量，写读压力大
2.受某种业务决定，或者被限制，也就是说一个业务往往会影响到数据库的瓶颈（性能问题，如双十一抢购）
3.部分业务无法关联join，只能通过java程序接口去调用，提高了开发复杂度

2、水平拆分

(1) 数据库水平拆分

在这里插入图片描述

如图，按会员库拆分，拆分成会员1库，会员2库，以userId拆分，userId尾号0-5为1库
6-9为2库，还有其他方式，进行取模，偶数放到1库，奇数放到2库

(2) 表水平拆分

在这里插入图片描述

如图把users表拆分成users1表和users2表，以userId拆分，进行取模，偶数放到users1表，奇数放到users2表

水平拆分的其他方式

range来分,每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了,优点：扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了缺点：大部分的请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据
hash分发,优点：可以平均分配每个库的数据量和请求压力缺点：扩容起来比较麻烦，会有一个数据迁移的这么一个过程
水平拆分特点
1.每个库（表）的结构都一样
2.每个库（表）的数据都不一样
3.每个库（表）的并集是全量数据

水平拆分优缺点

优点：
1.单库/单表的数据保持在一定量（减少），有助于性能提高
2.提高了系统的稳定性和负载能力
3.拆分表的结构相同，程序改造较少。

缺点:
1.数据的扩容很有难度维护量大
2.拆分规则很难抽象出来
3.分片事务的一致性问题部分业务无法关联join，只能通过java程序接口去调用

四、分库分表带来的问题
分布式事务
跨库join查询
分布式全局唯一id
开发成本对程序员要求高

五、分库分表技术如何选型
分库分表的开源框架
jdbc 直连层：shardingsphere、tddl
proxy 代理层：mycat，mysql-proxy（360）

jdbc直连层

在这里插入图片描述

jdbc直连层又叫jdbc应用层,是因为所有分片规则，所有分片逻辑，包括处理分布式事务
所有这些问题它都是在应用层，所有项目都是由war包构成的，所有分片都写成了jar包，放到了war包里面，java需要虚拟机去运行的，虚拟机运行的时候就会把war包里面的字节文件进行classLoder加载到jvm内存中，所有分片逻辑都是基于内存方进行操作的

proxy代理层

在这里插入图片描述

如图,proxy代理层，所有分片规则，所有分片逻辑，包括处理分布式事务都在mycat写好了,所有分片逻辑都是基于mycat方进行操作

jdbc直连层和proxy代理层优缺点

jdbc直连层性能高，只支持java语言，支持跨数据库
proxy代理层开发成本低，支持跨语言，不支持跨数据库

MySQL有哪些数据类型

1、整数类型**，

包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型**，

包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型**，

包括VARCHAR、CHAR、TEXT、BLOB
VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
VARCHAR存储的内容超出设置的长度时，内容会被截断。
CHAR是定长的，根据定义的字符串长度分配足够的空间。
CHAR会根据需要使用空格进行填充方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4、枚举类型（ENUM）**，

把不重复的数据存储为一个预定义的集合。
有时可以使用ENUM代替常用的字符串类型。
ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。
ENUM在内部存储时，其实存的是整数。
尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。
排序是按照内部存储的整数

5、日期和时间类型**，

尽量使用timestamp，空间效率高于datetime，
用整数保存时间戳通常不方便处理。
如果需要存储微妙，可以使用bigint存储。
看到这里，这道真题是不是就比较容易回答了。

MySQL中varchar与char有哪些区别？

1、固定长度 & 可变长度

VARCHAR
VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。
有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。

CHAR
CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上)。
同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

2、存储方式

VARCHAR
VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

CHAR
CHAR适合存储很短或长度近似的字符串。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

3、存储容量

CHAR
对于char类型来说，最多只能存放的字符个数为255，和编码无关，任何编码最大容量都是255。

VARCHAR
MySQL行默认最大65535字节，是所有列共享（相加）的，所以VARCHAR的最大值受此限制。

表中只有单列字段情况下，varchar一般最多能存放(65535 - 3)个字节，varchar的最大有效长度通过最大行数据长度和使用的字符集来确定，通常的最大长度是65532个字符（当字符串中的字符都只占1个字节时，能达到65532个字符）；

为什么是65532个字符？算法如下（有余数时向下取整）：

最大长度(字符数) = （行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数） / 字符集单字符最大字节数

NULL标识列占用字节数：允许NULL时，占一字节
长度标识字节数：记录长度的标识，长度小于等于255（28）时，占1字节；小于65535时（216）,占2字节
VARCHAR类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则。这些东西会很大程度上影响性能。

4.0版本及以下，MySQL中varchar长度是按字节展示，如varchar(20)，指的是20字节；
5.0版本及以上，MySQL中varchar长度是按字符展示。如varchar(20)，指的是20字符。
当然，行总长度还是65535字节，而字符和字节的换算，则与编码方式有关，不同的字符所占的字节是不同的。编码划分如下：

GBK编码：
一个英文字符占一个字节，中文2字节，单字符最大可占用2个字节。

UTF-8编码：
一个英文字符占一个字节，中文3字节，单字符最大可占用3个字节。

utf8mb4编码：
一个英文字符占一个字节，中文3字节，单字符最大占4个字节（如emoji表情4字节）。

假设当前还有6字节可以存放字符，按单字符占用最大字节数来算，可以存放3个GBK、或2个utf8、或1个utf8mb4。

Mysql的索引和主键的区别

1、主键一定是唯一性的索引，唯一性的索引不一定就是主键。

主键就是能够唯一标识表中某一行的属性或者是属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识一行记录，所以可以确保执行数据更新、删除的时候不会出现错误的。主键还经常和外键构成参照完整性约束，防止出现数据不一致。数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

2、一个表中可以有多个唯一索引，但是主键只能有一个。

3、主键列不允许为空值，而唯一性索引列允许空值。

4、主键也可以由多个字段组成，组成复合主键，同时主键也是唯一索引。

5、唯一索引表示索引值唯一，可以由一个或者几个字段组成，一个表可以由多个唯一索引。

数据库基础知识

为什么要使用数据库
数据保存在内存

优点：存取速度快

缺点：数据不能永久保存

数据保存在文件

优点：数据永久保存

缺点：1）速度比内存操作慢，频繁的IO操作。2）查询数据不方便

数据保存在数据库

1）数据永久保存

2）使用SQL语句，查询方便效率高。

3）管理数据方便

什么是SQL？
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

什么是MySQL?
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

数据库的事务的基本特性

事务是并发控制的基本单位，保证事务ACID的特性是事务处理的重要任务，而并发操作有可能会破坏其ACID特性。

所以事务是针对并发而言的，即对数据在并发操作时保驾护航。

**原子性：Atomicity **

**原子性：**在我理解看来是，事务中各项操作，要么全部成功要么全部失败。很有江湖义气一说，同生共死。

一致性：Consistency

**一致性：**我理解的是更侧重结果，事务结束后系统状态是一致的。

隔离性：Isolation

隔离性：并发执行的事务彼此无法看到对方的中间状态。

持久性：Durability

持久性：当事务完成后，它对于数据的改变是永久性的，即使出现致命的系统故障也将一直保持。

在实际生产应用中针对 事务的隔离性 又划分出了几种隔离级别

在这里插入图片描述

并发事务处理带来的问题

更新丢失

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新

脏读：在一个事务处理过程中读取了另一个未提交事务中的数据。

在这里插入图片描述

解读：两个事务 A 和 B，首先 A 事务对数据 a 执行加 500 的操作 a = 1500，此时 B 事务读取数据 a 的值 1500，后 A 事务又对数据 a 执行减500 的操作 a = 1000 ，A 事务 commit 。

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
在这里插入图片描述

解读：两个事务 A 和 B，首先 A 事务对数据 a 进行查询 a = 1000，此时 B 事务对数据 a + 500 操作，并提交事。后 A 事务又对数据 a 进行查询 a = 1500 。

幻读：事务 A 将数据库中所有数据类型从默认的 true 改成 false，但是事务 B 就在这个时候插入了一条新记录，当事务 A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务的四大特性(ACID)

4.1、原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败

4.2、一致性（Consistency）

官网上事务一致性的概念是：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。

4.3、隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4.4、持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务的四大特性中最麻烦的是隔离性，下面重点介绍一下事务的隔离级别

事务的隔离级别

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。

5.1、事务不考虑隔离性可能会引发的问题

如果事务不考虑隔离性，可能会引发如下问题：

1、脏读

脏读指一个事务读取了另外一个事务未提交的数据。

这是非常危险的，假设Ａ向Ｂ转帐100元，对应sql语句如下所示
　　　　　1.update account set money=money+100 where name=‘B’;
　　　　　2.update account set money=money-100 where name=‘A’;
　　当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。

2、不可重复读

不可重复读指在一个事务内读取表中的某一行数据，多次读取结果不同。
　　例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户内存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。
　　不可重复读和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。
　　很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

3、虚读(幻读)

虚读(幻读)是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
　　如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。

5.2、事务隔离性的设置语句

MySQL数据库共定义了四种隔离级别：

Serializable(串行化)：可避免 **脏读、不可重复读、虚读（幻读）**情况的发生。
Repeatable read(可重复读) - 默认的隔离级别：可避免脏读、不可重复读情况的发生。
Read committed(读已提交)：可避免脏读情况发生。
Read uncommitted(读未提交)：最低级别，以上情况均无法保证。

mysql数据库查询当前事务隔离级别：select @@tx_isolation

*例如：*

mysql数据库默认的事务隔离级别是：Repeatable read(可重复读)

mysql数据库设置事务隔离级别：set transaction isolation level 隔离级别名

在java代码中使用：

        Connection connect = JdbcUtils.getConnection();
        //获取当前数据库的隔离级别
        System.out.println(connect.getTransactionIsolation());
        //设置数据库的隔离级别
        connect.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //取消事务的自动提交
        connect.setAutoCommit(false);

事务隔离级别如何在java代码中使用

链接：【JDBC】ACID、四种隔离级别与Java代码实现【附源码】_java代码如何查看oracle当前事务隔离级别-CSDN博客

用户AA为用户BB转账，如果未考虑事务，可能会导致数据的不一致状态。
（1）未考虑事务之前的转账操作：
（此时若没有异常，则转账会成功，若有异常出现，转账操作就会在还没成功之前被迫结束，导致不一致）

//未考虑事务的转账操作，用户AA给用户BB转账100
@Test
public void test01(){
    String sql = "update user_table set balance = balance - 100 where user = ? ";
    updateTable(sql,"AA");
//        System.out.println(10/0);//模拟转账过程中出现的异常
    String sql1 = "update user_table set balance = balance + 100 where user = ? ";
    updateTable(sql1,"BB");
}
public void updateTable(String sql,Object...args) {
    Connection connect = null;
    PreparedStatement ps = null;
    try {
        //1.获取数据库的连接
        connect = JdbcUtils.getConnection();
        //2.预编译sql语句，返回prepareStatement的实例
        ps = connect.prepareStatement(sql);
        //3.填充占位符
        for(int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //4.执行sql
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.关闭资源
        JdbcUtils.closeConnection(ps,connect);
    }
}

（2）考虑事务之后的转账操作：
（我们取消数据库的默认提交操作，当转账操作成功结束时，对结果手动进行提交；若转账未结束时出现异常，我们可以对转账事务进行回滚操作，不管成功与否，数据总是一致的）

public void updateTable(Connection connect, String sql,Object...args) {
    PreparedStatement ps = null;
    try {
        //1.预编译sql语句，返回prepareStatement的实例
        ps = connect.prepareStatement(sql);
        //2.填充占位符
        for(int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //3.执行sql
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //4.关闭资源
        JdbcUtils.closeConnection(ps,null);
    }
}
//考虑事务之后的转账操作，用户AA给用户BB转账100
@Test
public void test02(){
    Connection connect = null;
    try {
        connect = JdbcUtils.getConnection();
        String sql = "update user_table set balance = balance - 100 where user = ? ";
        //1.取消事务的自动提交
        connect.setAutoCommit(false);
        updateTable(connect,sql,"AA");
        System.out.println(10/0);//模拟事务处理过程中出现的异常
        String sql1 = "update user_table set balance = balance + 100 where user = ? ";
        updateTable(connect,sql1,"BB");
        //2.事务正常结束后的提交
        connect.commit();
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //3.事务操作过程中出现异常时的回滚操作
            connect.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    } finally {
        if (connect!=null){
            try {
                connect.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

若此时Connection没有被关闭，还可能被重复使用，则需要恢复其自动提交状态，主要针对数据库连接池时的操作。
setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

Java代码演示及隔离级别的设置

数据库中事务的隔离级别设置结束后，若重启数据库的服务，则所有的数据库设置都会变成默认。

/**
 * @author wds
 * @date 2021-12-14 9:38
 */
public class TransactionTest01 {
    //模拟事务A对数据库中数据的查询操作
    @Test
    public void testTransactionSelect() throws Exception{
        Connection connect = JdbcUtils.getConnection();
        //获取当前数据库的隔离级别
        System.out.println(connect.getTransactionIsolation());
        //设置数据库的隔离级别
        connect.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //取消事务的自动提交
        connect.setAutoCommit(false);
        String sql = "select user,password,balance from  user_table where user = ?";
        PreparedStatement ps = connect.prepareStatement(sql);
        User user = queryForTable(connect, User.class, sql, "CC");
        System.out.println(user);
        System.out.println(connect.getTransactionIsolation());
    }

    //模拟事务B对数据库中数据的修改操作
    @Test
    public void testTransactionUpdate() throws Exception {
        Connection connect = JdbcUtils.getConnection();
        //取消事务的自动提交
        connect.setAutoCommit(false);
        String sql = "update user_table set balance = ? where user = ?";
        updateTable(connect,sql,50000,"CC");
        //线程休眠15秒
        Thread.sleep(15000);
        System.out.println("修改结束...");
    }
    //考虑事务之后的通用的增删改操作
    public void updateTable(Connection connect, String sql,Object...args) {
        PreparedStatement ps = null;
        try {
            //1.预编译sql语句，返回prepareStatement的实例
            ps = connect.prepareStatement(sql);
            //2.填充占位符
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //3.执行sql
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JdbcUtils.closeConnection(ps,null);
        }
    }
    //考虑事务之后不同数据表的通用的单行查询操作
    public <T> T queryForTable(Connection connect, Class<T> clazz, String sql,Object...args) {
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.预编译Sql
            ps = connect.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //2.执行sql操作
            resultSet = ps.executeQuery();
            //查询结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //查询结果集的列数
            int columnCount = metaData.getColumnCount();
            if(resultSet.next()){
                //newInstance()只能调用无参构造方法，创建当前类的对象
                T t = clazz.newInstance();
                //返回结果集中的每一个列
                for(int i=0;i<columnCount;i++){
                    //获取每个列的列值，通过resultSet
                    Object columnValue = resultSet.getObject(i + 1);
                    //通过ResultSetMetaData
                    //获取每个列的列名
                    String columnName = metaData.getColumnName(i + 1);
                    //获取每个列的别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //通过反射，将对象指定名getColumnName的属性设置为指定的属性值：columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeConnection(ps,null,resultSet);
        }
        return null;
    }
}

5.3、使用MySQL数据库演示不同隔离级别下的并发问题

同时打开两个窗口模拟2个用户并发访问数据库

1、当把事务的隔离级别设置为read uncommitted时，会引发脏读、不可重复读和虚读

A窗口
　　　　set transaction isolation level read uncommitted; --设置A用户的数据库隔离级别为Read uncommitted(读未提交)
　　　　start transaction; --开启事务
　　　　select * from account; --查询A账户中现有的钱，转到B窗口进行操作
　　　　select * from account --发现a多了100元，这时候A读到了B未提交的数据（脏读）

B窗口
　　　　start transaction; --开启事务
　　　　update account set money=money+100 where name=‘A’;–不要提交，转到A窗口查询

2、当把事务的隔离级别设置为read committed时，会引发不可重复读和虚读，但避免了脏读

A窗口
　　　　set transaction isolation level read committed;
　　　　start transaction;
　　　　select * from account;–发现a帐户是1000元，转到b窗口
　　　　select * from account;–发现a帐户多了100,这时候，a读到了别的事务提交的数据，两次读取a帐户读到的是不同的结果（不可重复读）
　　B窗口
　　　　start transaction;
　　　　update account set money=money+100 where name=‘aaa’;
　　　　commit;–转到a窗口

3、当把事务的隔离级别设置为repeatable read(mysql默认级别)时，会引发虚读，但避免了脏读、不可重复读

A窗口
　　　　set transaction isolation level repeatable read;
　　　　start transaction;
　　　　select * from account;–发现表有4个记录，转到b窗口
　　　　select * from account;–可能发现表有5条记录，这时候发生了a读取到另外一个事务插入的数据（虚读）
　　B窗口
　　　　start transaction;
　　　　insert into account(name,money) values(‘ggg’,1000);
　　　　commit;–转到a窗口

4、当把事务的隔离级别设置为Serializable时，会避免所有问题

A窗口
　　　　set transaction isolation level Serializable;
　　　　start transaction;
　　　　select * from account; --转到b窗口

B窗口
　　　　start transaction;
　　　　insert into account(name,money) values(‘ggg’,1000); --发现不能插入，只能等待a结束事务才能插入

数据库三大范式是什么

在这里插入图片描述

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog有有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

row 级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

数据类型

MySQL存储引擎MyISAM与InnoDB区别

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

常用的存储引擎有以下：

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

MyISAM索引与InnoDB索引的区别？
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
InnoDB引擎的4大特性
插入缓冲（insert buffer)

二次写(double write)

自适应哈希索引(ahi)

预读(read ahead)

存储引擎选择
如果没有特别的需求，使用默认的Innodb即可。

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

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

MYsql如何查询到一条数据的，简述其查询原理，BufferPool缓存机制事务

链接：【mysql学习篇】InnoDB存储引擎事务的实现和BufferPool缓存机制详解-CSDN博客

在这里插入图片描述

Mysql的InnoDB是如何使用索引的，其索引的原理是什么

链接：Mysql(InnoDB)索引原理及的使用_innodb 索引空间、索引数据-CSDN博客

索引是存储引擎实现的，InnoDB的索引结构是B+树，大部分的引擎也都支持B+树索引

在这里插入图片描述

B+树索引的优点

在这里插入图片描述

层级少，2200W的数据只有3层
叶子节点存数据+指针(利于排序)
支持范围查询
支持排序(区间指针)
索引全部出现在最下层，即便是他们作为了上层分支

InnoDB到底支持Hash索引吗？
InnoDB在特定条件下会自适应Hash索引，用户不能手动创建，可以理解为“索引的索引”，可以加快索引查询速度

相比B+树的优缺点：

不支持范围查询，但指定查询效率更高(一般只需一次检索)
哈希冲突问题使用链表解决
无法排序，哈希值计算结果没有顺序关系

MySQL-索引，innoDB,B+树索引

链接： (5条消息) MySQL索引常见面试题（2022版）_mysql索引优化_未来很长，别只看眼前的博客-CSDN博客

链接2： (6条消息) 数据库MySQL-索引（含常见面试题）_mysql索引面试题_懒羊羊z的博客-CSDN博客

MySQL 索引失效如何解决？

以下是索引失效的原因：

为什么要建立索引？

当在非常大的表中进行查询，如果数据库进行全表遍历的话那么速度是会非常慢的，而我们的索引则可以建立一个b+树的结构，可以自上而下的去进行查询（有点像二分查找），可以在一定程度避免走全表查询，这样查询的速度是非常快的；

①一般情况下扫描索引的速度是远远大于扫描全表的速度的；

②索引是天然有序的，具备B+树的快速检索（类似二分查找）

③索引天然聚合（存储的数据是去重了的），在一些操作(分组，排序等)中不会再产生中间表；

哪些情况适合建立索引？

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

那么哪些情况下适合建立索引呢？：

1. 频繁作为where条件语句查询的字段

这是因为在频繁查询的字段列创建索引可以避免查询数据的时候走全表扫描，这样查询的速度就会大大增加；

2. 关联字段需要建立索引

关联的字段一般都是通过主键来进行两张表的关联，主键大部分情况下都是主键；如果关联的两个主键都没有索引，那么我们一般优先考虑在被驱动表中的字段建立索引，因为在外连接的查询中被驱动表是需要被多次重复扫描的，那么让它走索引查询是会快很多的，可以避免更多次数的全表扫描；

3. 排序字段可以建立索引

这是因为B+树结构的索引是天然有序的！

4.分组字段可以建立索引，因为分组的前提是排序

5.统计字段可以建立索引，例如count(),max()

这是因为索引是天然聚合的，就是存放在b+树的数据是已经去重的数据了，存储的数据还是比较紧凑的，那么通过B+树的双向指针可以更快的找到要统计的数据，而且在加了索引的列的统计的时候MySQL是不会产生中间表来专门去重了，可以减少不必要的性能开销；（在没有索引的列的统计，分组的SQL语句中，MySQL都是会创建临时表来存储数据的）

哪些情况不适合建立索引？

1.频繁更新的字段不适合建立索引 (因为数据比较大的表的索引的创建是非常耗时的，而且如果一个字段被频繁更新那么我们还需要频繁的维护这个树的结构，这个开销是非常大的)

2.参与列计算的列不适合建索引，因为计算后的列的值最后不一定是有序的，不是有序的那么就会导致索引失效。

3.表数据可以确定比较少的不需要建索引

4.数据重复且分布比较均匀的的字段不适合建索引，因为说不定你对这种索引字段的查询的速度还没有全表扫描快，例如性别，真假值；

5.where条件中用不到的字段不适合建立索引，因为索引是可以帮助我们在查询的时候大大的提高查询效率，但是在增加，删除操作确实异常消耗性能的，因为需要不断的维护B+树的结构（有序你就需要维护），你查询的时候都不需要使用到这个字段了，那还建立这个字段的索引列干啥？等着吃你系统的性能嘛？

为什么索引使用的是 B+ 树？——重点

①因为B+树是把数据都存放在叶子节点中的（在innodb存储引擎中一个b+树的节点是一页(16k)），那么在固定大小的容量中 B+树的非叶子节点中就可以存放更多的索引列数据，也就意味着B+树的非叶子节点存储的数据的范围就会更大，那么树的层次就会更少，IO次数也就会更少；

②B+树的叶子节点维护了一个双向链表，它更有利于范围查询。

③B+树中的叶子节点和非叶子节点的数据都是分开存储的，分别存放在叶子节点段和非叶子节点段，那么进行全表扫描的时候，就可以不用再扫描非叶子节点的数据了，并且这是一个顺序读取数据的过程（顺序读比随机读的速度要快很多很多），扫描的速度也会大大提高；

链接：聚簇索引和非聚簇索引到底有什么区别？_聚簇索引和非聚簇索引区别_Linux小百科的博客-CSDN博客

索引分为哪几类？

从大类来分：分为聚簇索引和非聚簇索引；

从具体的种类来分有：

主键索引: 也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。

普通索引：就是普普通通的索引。

唯一索引：索引的值不能重复。

复合索引：在工作中用得比较频繁的一个索引；

当有多个查询条件时，我们推荐使用复合索引。比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

创建方式： 复合索引中的索引的顺序是非常重要的；

alert table test add idx_a1_a2_a3 table (a1,a2,a3)

使用复合索引可以极大的减少回表的带来的性能开销；（体现在复合索引可以进行更多的索引覆盖（因为你索引的个数明显更加多了呀），即便是回表也是携带更少的主键进行回表查询（与MySQL5.7后的索引下推有关））

复合索引是基于第一个索引的，比如你建立了一个（a,b,c）的复合索引，那么你不能跳过a索引直接去查询b索引，因为在建立（a,b,c）这个复合索引的时候，是会创建（a），（a,b），（a,b,c）这三个索引的，你会发现它们都是基于a索引的； (并不会单独的创建(a,c)这个索引)

hash索引：hash天然快（最快o(1),最慢o(n),树化(lon(n))），但是天然无序；

空间索引；

全文索引

聚簇索引和非聚簇索引的区别

什么是聚簇索引？（重点）

聚簇索引就是将数据(一行一行的数据)跟索引结构放到一块，InnoDB存储引擎使用的就是聚簇索引；

注意点：

1、InnoDB使用的是聚簇索引（聚簇索引默认使用主键作为其索引），将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2、若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据(一行一行的数据)跟索引结构放到一块，因此一个表仅有一个聚簇索引，其他辅助索引可能是只有几个列的数据和索引放在一起！

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护（有序就一定需要维护）这个顺序；

聚簇索引中的索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个6个字节大小的row_id来作为主键，这个主键会作为聚簇索引中的索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

非聚簇索引

非聚簇索引在 InnoDB 引擎中，也叫二级索引

在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询，因此我们可以得出

聚簇索引和非聚簇索引的区别主要有以下几个：

聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。
聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。
聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。

什么叫回表？（重点）

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

MySQL索引失效的几种情况（重点）

①like查询以%开头，因为会导致查询出来的结果无序；

②类型转换，列计算也会可能会让索引失效，因为结果可能是无序的，也可能是有序的；

③在一些查询的语句中，MySQL认为走全表扫描比索引更加快也会导致索引失效；

④如果条件中有or并且or连接的字段中有列没有索引，那么即使其中有条件带索引也不会使用索引 (这是因为MySQL判断即便你开始走了索引查询，但是它发现查询中有Or ，也就是说or 后面的还是需要走全表扫描（因为or会导致后面的数据是无序的），所以MySQL还不如一开始就直接走全表扫描，这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】，这叫索引合并（取二者的并集）;

⑤复合索引不满足最左原则就不能使用全部索引

MySQL索引优化手段有哪些？

① 尽可能的使用复合索引而不是索引的组合；

②创建索引尽量让辅助索引进行索引覆盖 而不是回表；

③在可以使用主键id的表中，尽量使用自增主键id，这样可以避免页分裂；

④查询的时候尽量不要使用select * ，这样可以避免大量的回表；

⑤尽量少使用子查询，能使用外连接就使用外连接，这样可以避免产生笛卡尔集；

⑥能使用短索引就是用短索引，这样可以在非叶子节点存储更多的索引列降低树的层高，并且减少空间的开销；

什么叫回表？（重点）

什么叫索引覆盖？（重点）

如果一个查询是先走辅助索引的，那么通过这个辅助索引就直接获取到我们想要的全部数据了，不需要进行回表，这个过程就叫做索引覆盖；

索引-索引-索引

什么是索引？

索引是一种特殊的文件(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树索引）

创建索引的三种方式，删除索引
第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

需要取消自增长再行删除：

alter table user_index

– 重新定义字段

MODIFY id int,
drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

创建索引时需要注意什么？
非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
使用索引查询一定能提高查询的性能吗？为什么
通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索
百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

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