字符集的相关操作

字符集相关操作_小怪吖的博客-CSDN博客

SQL大小写规范和sql_mode

SQL大小写规范和 sql_mode_小怪吖的博客-CSDN博客

MySQL的数据目录

MySQL8的主要目录结构

通过find命令查看有关的mysql的目录：

find / -name mysql

数据库文件的存放路径

MySQL数据库文件的存放路径：/var/lib/mysql

MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录被称为数据目录。

数据目录对应着一个系统变量datadir，在使用客户端与服务器端建立连接之后可通过SQL命令查看这个系统变量的值：

mysql> show variables like 'datadir';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

相关命令目录：/usr/bin(mysqladmin、mysqlbinlog、mysqldump等命令)和/usr/sbin

安装目录下非常重要的bin目录，里面存储了许多关于控制客户端程序和服务器程序的命令（许多可执行文件，比如mysql、mysqld、mysqld_safe等）。而数据目录是用来存储MySQL在运行过程中产生的数据，注意区分开二者。

配置文件目录：/usr/share/mysql-8.0(命令及配置文件)，/etc/mysql

查看当前计算机上有哪些数据库：

mysql>SHOW DATABASES;

可以看到有4个数据库是属于MySQL自带的系统数据库。

• mysql

  MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

• information_schema

  MySQL系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其它数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，也称为元数据。在系统数据库information_schema中提供了一些以innodb_sys开头的表，用于表示内部系统表。

  mysql> USE information_schema; 
  Database changed 
  
  mysql> SHOW TABLES LIKE 'innodb_sys%'; 
  +--------------------------------------------+
  | Tables_in_information_schema (innodb_sys%) |
  +--------------------------------------------+
  | INNODB_SYS_DATAFILES | 
  | INNODB_SYS_VIRTUAL | 
  | INNODB_SYS_INDEXES | 
  | INNODB_SYS_TABLES | 
  | INNODB_SYS_FIELDS | 
  | INNODB_SYS_TABLESPACES | 
  | INNODB_SYS_FOREIGN_COLS | 
  | INNODB_SYS_COLUMNS | 
  | INNODB_SYS_FOREIGN | 
  | INNODB_SYS_TABLESTATS | 
  +--------------------------------------------+
  10 rows in set (0.00 sec)
  

• performance_schema

  MySQL系统自带的数据库，这个数据库主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。

• sys

  MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。

数据库在文件系统中的表示

MySQL5.7版本，以dbtest1为例，此数据库下只有一张表emp1

• db.opt：主要存储了这个数据库使用的字符集和比较规则设置。
• xxx.frm：是用来存储这个表的结构的，比如说这个表的字段名、字段的属性、字段的大小。
• xxx.ibd：主要存储表的数据和索引，但是表中数据的存储根据不同的MySQL版本存储在不同的地方，就是下面的系统表空间和独立表空间。

MySQL8.0版本，同样以dbtest1为例，此数据库下只有一张表emp1

可以发现只有一个文件，它没有了xxx.frm文件，而是将表的结构和数据都写进了一个文件进行存储。

表在文件系统中的表示

InnoDB存储引擎模式

• 表结构：为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样：表名.frm

  .frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以二进制格式存储的，直接打开是乱码的。

• 表中数据和索引：为了更好的管理页，InnoDB提出了一个表空间或者文件空间的概念。（这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件）每一个表空间可以被划分为很多个页，表数据就存放在某个空间下的某些页里。

系统表空间(system tablespace)

默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，这个文件就是对应的系统表空间在文件系统上的表示。注意这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。

如果想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及大小，例如修改my.cnf配置文件：

[server] 
innodb_data_file_path=data1:512M;data2:512M:autoextend

在MySQL启动之后就会创建这两个512M大小的文件作为系统表空间，其中的autoextend表明这两个文件如果不够用会自动扩展data2文件的大小。

需要注意的一点是，在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，表中的数据都会被默认存储到这个系统表空间。

独立表空间(file-pre-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立的表空间，也就是说创建了多少个表，就有多少个独立表空间。使用独立表空间来存储数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，完整的文件名称：表名.ibd

比如：使用独立表空间去存储dbtest1数据库下的test表的话，那么在该表所在数据库对应的dbtest1目录下会为test表创建以下两个文件：test.frm test.ibd 其中test.ibd文件就用来存储test表中的数据和索引。

系统表空间与独立表空间的设置

可以自己指定使用系统表空间还是独立表空间来存储数据：

[server] 
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

默认情况：on就是独立表空间

mysql> show variables like 'innodb_file_per_table'; 
+-----------------------+-------+
| Variable_name | Value | 
+-----------------------+-------+
| innodb_file_per_table | ON | 
+-----------------------+-------+
1 row in set (0.01 sec)

其它类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，还新提出了一些不同类型的表空间，比如通用表空间(general tablespace)、临时表空间(temporary tablespace)等。

疑问：为什么.frm在MySQL8中不存在了？

Oracle官方将.frm文件的信息以及更多信息移动到叫做序列化字典信息（Serialized DictionaryInformation，SDI)，SDI被写在ibd文件内部。

查看表结构

到存储.ibd文件的目录下，执行下面的命令：

ibd2sdi --dump-file=emp1.txt emp1.ibd

MyISAM存储引擎模式

• 表结构：在存储表结构方面，MyISAM和InnoDB一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件：表名.frm

• 表中数据和索引：在InnoDB中，索引即是数据。在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件的，同时表数据都存放在对应的数据库目录下。假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的dbtest1目录下会为test表创建以下三个文件：

  test.frm  存储表结构
  test.MYD  存储数据(MYData)
  test.MYI  存储索引(MYIndex)
  

  但是在MySQL8之后也和InnoDB一样去掉了.frm文件，将其放入sid文件中。

  test.sdi  存储元数据
  test.MYD  存储数据(MYData)
  test.MYI  存储索引(MYIndex)
  

小结：（举例：数据库a，表b）

1.如果表b采用InnoDB，data\a中会产生1个或者2个文件：

• b.frm：描述表结构文件，字段长度等
• 如果采用系统表空间模式，数据信息和索引信息都存储在ibdata1中
• 如果采用独立表空间存储模式，data\a中还会产生b.ibd文件（存储数据信息和索引信息）

此外：

1. Mys5.7中会在data/a的目录下生成db.opt文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不在提供db.opt文件。
2. MySQL8.0中不在单独提供b.frm，而是合并在b.ibd文件中。

2.如果表b采用MyISAM,data\a中会产生3个文件：

• MySQL5.7中：b.frm：描述表结构文件
• MySQL8.0中：b.xxx.sdi：描述表结构文件
• b.MYD(MYData)：数据信息文件，存储数据信息（独立表存储模式）
• b.MYI(MYIndex)：存储索引信息文件

视图在文件系统的表示

MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下，只会存储一个视图名.frm的文件。

参考：

• MySQL的数据目录

逻辑架构

客户端的MySQL驱动

​ 当系统需要和MySQL数据库进行通信前，需要先和数据库建立连接，而这个功能是由MySQL驱动底层帮我们完成的，建立完成之后，我们只需要发送SQL语句就可以执行CRUD。如下图所示：

​ 一次SQL请求就会建立一个连接，多个请求就会建立多个连接。java系统在通过MySQL驱动和MySQL数据库连接的时候是基于TCP/IP协议的，所以如果每个请求都新建连接和销毁连接，这样频繁的创建和销毁连接势必会大大降低系统的性能。

​ 为了解决上面的问题，采用了“池化”的思想，通过连接池维护一定数量的连接线程，当需要使用连接时，就直接从线程池中获取，使用完毕之后，再归还给线程池。通过线程池大大减少了不断创建与销毁线程的开销，也不需要我们去关心连接的创建与销毁，以及线程池是怎么维护这些连接的。常见的数据库连接池有Druid、C3P0。

MySQL架构的Server层

​ 通过上面的架构图可以得知，Server层中主要由连接器、查询缓存、解析器/分析器、优化器、执行器几部分组成，下面将主要描述下这几部分。

连接器

​ 客户端想要对数据库进行操作时，前提是与数据库建立好连接；而连接器就是用来负责跟客户端建立连接、获取权限、维持和管理连接的。

1. 连接方式：MySQL既支持短连接，也支持长连接。短连接就是操作完毕后，马上close关掉。长连接可以保持打开，减少服务器端创建和释放连接的消耗，后序程序访问的时候还可以使用这个连接。
2. 连接池：为了减少频繁创建和销毁连接造成的不必要的性能损失，这里采用了“池化”的思想，通过数据库连接池去管理连接。一般会在连接池中使用长连接。（TCP连接收到请求后，必须要分配一个线程专门与这个客户端交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。）

SQL Interface

​ 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT... FROM就是调用SQL Interface。MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。

查询缓存

MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0版本直接将查询缓存的整块功能删掉了。

MySQL默认不开启查询缓存的原因

1. 缓存中数据的存储格式是key（sql语句）- value（数据值），所以如果SQL语句只要存在一点不同之处就会直接进行数据库查询。
2. MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，那使用该表的所有高速缓存查询都将变为无效的并从缓存中删除。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

分析器/解析器

​ 分析器的工作主要是对要执行的SQL语句进行解析，最终得到抽象语法树，然后再使用预处理器判断抽象语法树中的表是否存在，如果存在的话，再接着判断select投影列字段是否在表中存在等。

1. 词法分析：用于将SQL拆解为不可再分的原子符号，称为Token。根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。

2. 语法分析就是根据词法分析拆解出来的原子符号，将SQL语句转换为抽象语法树。

   SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18

   经过词法分析、语法分析后得到的抽象语法树如下：

   

   注意，为了便于理解，抽象语法树的关键字Token用绿色表示，变量的Token用红色表示，灰色表示需要进一步拆分。

3. 预处理器：预处理器是用来对生成的抽象语法树进行语义校验，语义校验就是对查询的表、select投影字段进行校验，判断表、字段是否存在等。

优化器

​ 优化器的作用主要是将SQL经过词法解析/语法解析后得到的语法树，通过MySQL的数据字典和统计信息的内容，经过一系列运算，最终得出一个执行计划，包括选择使用哪个索引。

1. 逻辑变换：例如SQL的where条件中存在8>9，那逻辑转换就是将语法树中存在的这种常量表达式直接进行化简，化简为false；除了化简还有常量表达式计算等。
2. 代价优化：就是通过付出一些数据统计分析的代价，来得到这个SQL执行是否可以走索引，以及走哪些索引；除此之外，再多表关联查询中，确定最终表join的顺序等。

在分析是否走索引查询时，是通过进行动态数据采样统计分析出来的；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走索引时，也要考虑到这方面的因素。

在执行的SQL语句前添加上explain关键字可以查看执行计划

执行器

​ MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。执行器最终就是根据一系列的执行计划去调用存储引擎提供的API接口去操作数据，完成SQL的执行。

​ 开始执行的时候，要先判断一下建立连接的对象对这个表有没有执行操作的权限，如果没有，就会返回没有权限的错误；如果有，就按照生成的执行计划进行执行。

引擎层

​ MySQL的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的MySQL还允许开发人员自定义存储引擎。

​ 插件式存储引擎层（Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取。

MySQL8.0.25默认支持的存储引擎如下：

存储层

​ 所有的数据，数据库、表的定义、表的每一行的内容、索引，都是存在文件系统上，以文件的方式存在的。并完成与存储引擎的交互。

数据库缓冲池(buffer pool)

​ InnoDB存储引擎是以页为单位来管理存储空间的，进行的增删改查操作其本质都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被使用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

​ 这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O的时间。这种策略对提升SQL语句的查询性能来说至关重要。如果索引的数据在缓存池里，那么访问的成本就会降低很多。

缓冲池Buffer Pool

InnoDB存储引擎中缓冲池包括了数据页、索引页、插入缓存、锁信息、自适应Hash和数据字典信息等

​ InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘I/O的开销。

缓冲池如何读写数据

​ 缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面放到缓冲池中再进行读取。

​ 当对数据库中的记录进行修改的时候，首先会修改缓存池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。注意不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上，可提升数据库的整体性能。（当缓存池不够用时，需要释放掉一些不常用的页，此时就可以强行采用checkpoint机制，将不常用的脏页回写到磁盘上，然后再从缓存池中将这些页释放掉。这里脏页指的是缓冲池中被修改过的页，与磁盘上的数据页不一致）

innoDB_buffer_pool_size变量可查看或修改缓冲池的大小

show variables like 'innodb_buffer_pool_size';

set global innode_buffer_pool_size = 268435456;

参考：

• 逻辑架构_小怪吖的博客-CSDN博客_
• SQL语句的执行过程_张维鹏的博客-CSDN博客_

存储引擎

​ 存储引擎是MySQL的核心，是数据库底层软件组织，数据库使用存储引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁级别、事务等功能。存储引擎是基于表的，而非数据库。

常用的存储引擎

1. InnoDB存储引擎

   ​ InnoDB是MySQL5.5版本之后的默认存储引擎，它是为了达到处理巨大数据量的最大性能而设计的，其CPU效率可能是任何其它基于磁盘的关系型数据库引擎不能匹敌的。InnoDB支持事务、提供行级锁，每个表的主键不能为空且支持主键自增长，支持外键完整性约束。

2. MyISAM存储引擎

   ​ 不支持事务、也不支持外键，使用标记锁控制并发的读写操作，支持全文索引。MyISAM引擎强调快速读取操作，主要用于高负载的select，对事务完整性没有要求的应用可以用这个引擎来创建表。

   MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型：

   1. 静态型：指定义的表列大小是固定的（即不含有：xblob、xtext、varchar等长度可变的数据类型）。使用静态格式的表的性能比较高，因为在维护和访问以预定格式存储的数据时，需要的开销比较低，但这种高性能是以空间为代价换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。优点：存储非常迅速，容易缓存，出现故障容易恢复；缺点：占用的空间通常比动态表多。

   2. 动态型：如果列定义为动态的（xblob、xtext、varchar等数据类型），这时MyISAM就自动使用动态型，虽然动态型的表占用了比静态型的表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变，则其位置很可能需要移动，这样就会导致碎片的产生，随着数据变化的增多，碎片也随之增加，数据访问性能会随之降低。

      对于因碎片增加而降低数据访问性这个问题，有两种解决办法：
      	1.尽可能使用静态数据类型；
      	2.经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失。如果存储引擎不支持optimize table table_name则可以转出并重新加载数据，这样也可以减少碎片。
      

   3. 压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用，因为每个记录是被单独压缩的，所以只有非常小的访问开支。

3. Memory存储引擎

   ​ Memory存储引擎通过在内存中创建临时表来存储数据。每个表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。由于所存储的数据保存在内存，如果mysqld进程发生异常、重启或计算机关闭等等都会造成这些数据的消失。默认使用Hash索引，也可以使用B+树型索引。Memory存储引擎主要用于内容变化不频繁，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

4. Archive存储引擎

   ​ Archive引擎提供了很好的压缩机制，它使用zlib压缩库，压缩比非常高，并且拥有高效的插入速度，支持insert、replace和select操作，但不支持update、delete，不支持事务，也不支持索引（5.5版本之后支持索引），所以查询性能较差一些，适合用于做仓库使用和数据归档，存储大量独立的、作为历史记录的数据，如记录日志信息，因为不会被经常读取。

5. Merge存储引擎

   ​ Merge存储引擎是将一定数量的MyISAM表结构完全相同的表联合成一个整体，Merge表本身没有数据，对Merge类型的表可以进行查询、更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

6. Berkeley存储引擎（BDB）

   ​ 该存储引擎支持COMMIT和ROLLBACK等其它事务特性，支持页级锁。该引擎在包括MySQL5.1及其以上版本的数据库中不再支持。

7. CSV（Comma-Separated Values逗号分隔值）

   ​ 逻辑上由逗号分隔数据的存储引擎。使用该存储引擎的MySQL数据表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。

8. Federated

   ​ 该存储引擎可以将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合数据库分布式应用。

9. Cluster/NDB

   ​ 高冗余的存储引擎，该存储引擎用于多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大、安全和性能要求高的场景。

10. BLACKHOLE（黑洞引擎）

    ​ 该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

11. PERFORMANCE_SCHEMA

    ​ 该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。

MyISAM与InnoDB存储引擎的区别

1. **事务支持：**MyISAM不支持事务处理，InnoDB支持事务处理。
2. **锁级别：**MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁但是InnoDB的行锁是通过给索引项加锁来实现的，即只有通过索引进行查询数据，InnoDB才使用行级锁，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况，但是表级锁不存在死锁。
3. **表主键与外键约束：**MyISAM允许没有任何索引和主键的表存在，不支持外键。InnoDB支持主键自增长且主键不能为空，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），支持外键完整性约束。
4. **索引结构：**MyISAM和InnoDB都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行的地址，但是InnoDB的主键索引保存的不是行的地址，而是保存该行的所有数据，而辅助索引的Data域保存的则是主索引的值。
5. **全文索引：**MyISAM支持FULLTEXT类型的全文索引，InnoDB不支持全文索引（5.6版本之后InnoDB存储引擎开始支持全文索引）
6. **存储结构：**MyISAM会在磁盘上存储三个文件（.frm、.MYD、.MYI）InnoDB把数据和索引存放在独立表空间里面，所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
7. 存储空间：
   1. MyISAM可被压缩，存储空间较小。支持三种不同的存储格式（静态表、动态表、压缩表）
   2. InnoDB需要更多的内存和存储空间，它会在主内存中建立其专业的缓冲池用于高速缓存数据和索引。
8. 表的具体行数：
   1. MyISAM保存有表的总行数，如果select count(*) from table;会直接取出该值，不需要进行全表扫描。
   2. Innod没有保存表的总行数，如果select count(*) from table;，需要遍历整个表，消耗相当大。
9. 适用场景：
   1. 如果需要提供回滚、崩溃恢复能力的ACID事务能力，并要求实现行级锁级别并发控制，InnoDB是一个好选择；
   2. 如果数据表主要用来查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率；

存储引擎的操作

1. 查看MySQL的存储引擎信息：mysql>show engines;

   

   Support列的值表示某种引擎是否能使用，DEFAULT表示该引擎为当前默认的存储引擎。

2. 查看数据库默认使用哪个引擎show variables like 'storage_engine';

   

3. 设置默认的存储引擎：

   1. 在MySQL的配置文件中（linux下为/etc/my.cnf），在mysqld后面增加default-storage-engine=INNODB即可。或者在启动数据库服务器时再命令行后面加上–default-storage-engine或–default-table-type选项 。

   2. 在创建表时指定存储引擎的类型：

      CREATE TABLE mytable (id int,title char(20))ENGINE = INNODB;

   3. 修改现有的表使用的存储引擎：

      ALTER TABLE mytable ENGINE = MyISAM;

      缺点：
      	这种转化方式需要大量的时间和I/O，mysql要执行从旧表到新表的一行一行复制，所以效率较低；在转化期间给原表加了读锁；从一种引擎到另一种引擎做表转化，属于原始引擎的专业特性都会丢失。
      

   4. 导出再导入：如果表建立的时候是MyISAM，现在要更改整个数据库表的存储引擎，一般要一个表一个表的修改，比较繁琐，可以采用先把数据库导出，得到SQL，把MyISAM修改成INNODB，再导入的方式。

参考：

• 存储引擎_张维鹏的博客-CSDN博客_
• 存储引擎_小怪吖的博客-CSDN博客

索引的数据结构

​ 索引就是一种数据结构，通过缩小一张表中需要查询的数据来加快搜索的速度。如果没有索引，数据库不得不进行全表扫描。好比书的目录，让你更快的找到内容。

索引的优点：

1. 大大减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。
2. 如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。
3. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点：

1. 当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。
2. 索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

提示：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

一个简单的索引设计方案

​ 根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？我们可以为快速定位记录所在的数据页建立一个目录，建这个目录必须完成下边这些事： 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值

假设：每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下好多记录）有了这个假设之后向表中插入3条记录，且这些记录已经按照主键值的大小串联成一个单向链表，如图所示：

从图中可以看出来,index_demo表中的3条记录都被插入到了编号为10的数据页中了。此时我们再来插入一条记录:

mysql> INSERT INTO index_demo VALUES(4，4， 'a ' );

因为页10最多只能放3条记录，所以我们不得不再分配一个新页：

注意，新分配的数据页编号可能并不是连续的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5>4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下:

这个过程表明了在对页中的记录进行增删改操作的过程中，必须通过一些诸如记录移动的操作来始终保持这个状态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程被称为页分裂。

由于数据页的编号可能是连续的，所以在向表中插入许多条记录后，可能是这样的效果：

因为这些16KB的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，就需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

• 页的用户记录中最小的主键值，用key表示
• 页号，用page_no表示

以 页28为例，它对应 目录项2，这个目录项中包含着该页的页号28 以及该页中用户记录的最小主键值5 。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为20的记录，具体查找过程分两步：

1. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（12<20<209），它对应的页是9
2. 再去页9中查找对应的记录即可

InnoDB的索引方案

上文称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

• InnoDB是使用页来作为管理存储空间的基本单位，最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
• 因为时常会对记录进行增删，假设把页28中的记录都删除了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动以下，这样牵一发而动全身的操作效率很差。

所以，我们需要一种可以灵活管理所有目录项的方式。目录项其实跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢?使用记录头信息里的record_type属性，它的各个取值代表的意思如下：

• 0：普通的用户记录
• 1：目录项记录
• 2：最小记录
• 3：最大记录

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0。

• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。

相同点：两者用的是一样的数据页，都会为主键值生成Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

现在以查找主键为20的记录为例，根据某个主键值去查找记录的步骤可以大致拆分成以下两步：

1. 先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为12<20<209,所以定位到对应的记录所在的页就是页9。
2. 再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，如何处理呢? 这里我们假设一个存储目录项记录的页最多只能存放4条目录项记录，所以如果此时我们再向上图中插入一条主键值为320的用户记录的话，那就需要分配一个新的存储目录项记录的页:

从图中可以看出，当插入了一条主键值为320的用户记录之后需要两个新的数据页：

• 为存储该用户记录而新生成了页31
• 因为原先存储目录项记录的页30容量已满，所以不得不需要一个新的页32来存放页31对应的目录项。

现在因为存储目录项的页不止一个，所以如果想根据主键值查找一条用户记录大致需要3个步骤：

1. 确定目录项记录页
2. 通过目录项记录页确定用户记录真实所在的页
3. 在真实存储用户记录的页中定位到具体的记录

问题来了，在这个查询步骤的第1步中我们需要定位存储目录项记录的页，但是这些页是不连续的，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢?那就为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

如图，我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值 不小于320 的话，就到页32中查找更详细的目录项记录。

这个数据结构的名称是B+树

从图中可以看出，实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

聚簇索引与非聚簇索引

​ 聚簇索引要求表中数据存储的物理顺序与索引值顺序一致，一个基本表最多只能有一个聚簇索引，更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。

特点：

1. 使用记录的主键值大小进行记录和页的排序
   1. 页内的记录是按照主键的大小顺序排成一个单向链表
   2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
   3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
2. B+树的叶子节点存储的是完整的用户记录。（所谓完整的用户记录，就是指这个记录中存储了所有列的值，包括隐藏列）

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显示的使用INDEX语句去创建，InnoDB存储引擎会自动的创建聚簇索引。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的I/O操作

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，一般都定义一个自增的ID列为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，一般定义主键为不可更新
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制：

• 对于MySQL数据库目前只有InnoDB存储引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
• 由于数据物理排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
• 为了充分利用聚簇索引的聚簇特性，所有InnoDB表的主键列尽量选用有序的顺序ID，而不建议用无序的ID，比如UUID、MD5、Hash、字符串列作为主键无法保证数据的顺序增长。

非聚簇索引：表中记录的物理顺序与索引值的顺序不一致的索引组织，一个基本表可以有多个非聚簇索引。

将记录中的其它列的大小作为页中记录的排序规则，再建一棵B+树，效果如下图所示：

这个B+树与聚簇索引的B+树有几处不同：

• 此B+树的叶子节点存储的并不是完整的用户记录，而是其它列+主键的值
• 目录项记录中不再是主键+页号的搭配，而变成了其它列+页号的搭配

根据其它列大小排序的B+树只能确定我们要查找记录的主键值，所以如果想根据其它列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。

为什么还需要一次回表操作？直接把完整的用户记录放到叶子节点不可以吗？

​ 如果把完整的用户记录放到叶子节点是可以不回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录都再拷贝一遍，这就有点太浪费存储空间了。

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引或者辅助索引。

联合索引：即可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。

比方说想让B+树按照c2和c3的大小进行排序：

• 先把各个记录和页按照c2列进行排序
• 在记录的c2列相同的情况下，采用c3列进行排序
• B+数叶子节点处的用户记录由c2、c3和主键c1列组成

InnoDB的B+树索引的注意事项

1. 根页面位置万年不动

   • 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，由存储引擎默认创建）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
   • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
   • 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

   这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

2. 内节点中目录项记录的唯一性：把主键值添加到二级索引内节点的目录项记录中，就可以保证其唯一性

3. 一个页面最少存储2条记录

MyISAM的索引方案

1. 主键索引：MyISAM引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

   

   这里假设表一共有三列，我们以Col1为主键，则上图是一个MyISAM的主键索引示意图。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2. 在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。如果在Col2上建立一个辅助索引，则此索引的结构如下图所示：

   

   同样也是一棵B+树，data域保存数据记录的地址。

   因此，MyISAM中索引检索的算法为首先按照B+树搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据的记录。

两种存储引擎中索引的区别：

1. 在InnoDB存储引擎中，只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
2. InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引仅仅保存数据记录的地址。
3. InnoDB的非聚簇索引data域存储相应记录的主键值，而MyISAM索引记录的是地址。
4. MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址访问。
5. InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显示指定主键，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

Hash索引

​ MySQL中，只有Memory存储引擎支持hash索引，是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来，因此检索效率非常高，可以一次定位。类似于HashMap的底层。

Hash索引的缺点：

1. Hash索引仅能满足等值的查询，不能满足范围查询、排序。因为数据在经过Hash算法后，其大小关系就可能发生变化。
2. 当创建组合索引时，不能只适用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。
3. 当发生Hash碰撞时，Hash索引不能避免表数据的扫描。因为仅仅比较Hash值是不够的的，需要比较实际的值以判定是否符合要求。

InnoDB本身不支持Hash索引，但是提供自适应Hash索引。如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面所在位置。这样让B+树也具备了Hash索引的优点。

可以通过innnodb_adaptive_hash_index变量来查看是否开启了自适应Hash。

B-Tree

​ B树的英文是Balance Tree，也就是多路平衡查找树。简写为B-Tree（注意横杆为连接符，不代表减号）。它的高度远小于平衡二叉树的高度。

B树的结构如下图：

B树作为多路平衡查找树，它的每一个节点最多可以包括M个子节点，M称为B树的阶。每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了x个关键字，那么指针数就是x+1.

详情请阅读博客：

B树详解__Kim的博客-CSDN博客_

小结：

1. B树在插入和删除结点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。
2. 叶子节点和非叶子节点都存放数据，搜索有可能在非叶子节点结束。
3. 其搜索性能等价于在关键字全集内做一次二分查找。

B+树和B-树的差异：

1. 有k个孩子节点就有k个关键字。也就是孩子数量=关键字数，而B-树中，孩子数量=关键字数+1。
2. 非叶子节点的关键字也会同时存在子节点中，并且是在子节点中所有关键字的最大（或最小）。
3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序连接。

根据B-Tree和B+Tree的结构，可以发现B+树相比于B树，在文件系统或者数据库系统当中，更有优势，原因如下：（为什么说B+树比B-树更适合实际应用在操作系统的文件索引和数据库索引？）

1. B+树有利于对数据库的扫描：B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下问题，而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以范围查询、排序等操作，B+树有着更高的性能。
2. B+树的磁盘IO代价更低：B+树的内部节点的data域并没有存储数据，因此其内部节点相对于B树更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了。
3. B+树的查询效率更加稳定：由于B+树的内部节点只是叶子结点中关键字的索引，并不存储数据。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

思考题：为了减少I/O，索引树会一次性加载吗？

​ 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大。当利用索引查询的时候，是不可能将全部几个G的索引都加载进内存的，只能逐一加载磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘I/O？

​ InnoDB存储引擎中的页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+树中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估算，为方便计算，这里的k取值为10^3。也就是说一个深度为3的B+树索引可以维护10^310^310^3=10亿条记录。（这里假定一个数据页存储10^3条记录）

​ 实际情况中每个节点可能不能填充满，因此在数据库中，B+树的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

思考题：Hash索引与B+树索引的区别？

1. Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2. Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而B+树可以。对于联合索引来说，Hash索引在计算Hash值的时候是将所有键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3. Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，以%结尾的话就可以起到优化作用。

参考：

• 索引的数据结构(上)_小怪吖的博客-CSDN博客

• 索引的数据结构(下)_小怪吖的博客-CSDN博客

• 索引的实现原理_张维鹏的博客-CSDN博客_

InnoDB数据存储结构

详情请查阅以下博客：

InnoDB数据存储结构_小怪吖的博客-CSDN博客_

索引的创建与设计原则

常见的索引类型有：普通索引、唯一索引、主键索引、全文索引、组合索引。

1. 普通索引：在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。
2. 唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一性索引。
3. 主键索引：主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
4. 单列索引：在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。
5. 多列（组合、联合）索引：多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。使用组合索引时遵循最左前缀法则。
6. 全文索引：仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
7. 空间索引：使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间索引，而且索引的字段不能为空值。（了解）

创建索引

CREATE TABLE table_name [colame data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或DESC指定升序或者降序的索引值存储。

创建普通索引：

CREATE TABLE book( 
book_id INT , 
book_name VARCHAR(100), 
authors VARCHAR(100), 
info VARCHAR(100) , 
comment VARCHAR(100), 
year_publication YEAR, 
INDEX idx_year_publication(year_publication) 
);

创建唯一索引：

CREATE TABLE test1( 
id INT NOT NULL, 
name varchar(30) NOT NULL, 
UNIQUE INDEX uk_idx_id(id) 
);

创建主键索引：

CREATE TABLE student ( 
id INT(10), 
student_no VARCHAR(200), 
student_name VARCHAR(200), 
PRIMARY KEY(id) 
);
#删除主键索引
ALTER TABLE student drop PRIMARY KEY;
#修改主键索引：必须先删除掉原索引，再新建索引

创建组合索引：

CREATE TABLE test3( 
id INT(11) NOT NULL, 
name CHAR(30) NOT NULL, 
age INT(11) NOT NULL, 
info VARCHAR(255), 
INDEX multi_idx(id,name,age) 
);

在已经存在的表上创建索引：

1. 使用ALTER TABLE语句创建索引

   ALTER TABLE table_name ADD [UNIQUE | FULLTEXT |SPATIAL] [INDEX | KEY] [index_name] (col_name[length])

2. 使用CREATE INDEX创建索引

   CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX index_name ON table_name (col_name[length],...)[ASC | DESC]

删除索引：

1. 使用ALTER TABLE删除索引（添加AUTO_INCREAMENT约束字段的唯一索引不能被删除）

   ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引（删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除）

   DROP INDEX index_name ON table_name;

​ 降序索引是以降序存储键值。虽然在语法上，从MySQL4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL8.x版本才开始真正支持降序索引（仅限于InnoDB存储引擎）

​ 从MySQL8.x开始支持隐藏索引，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index(强制使用索引)，优化器也不会使用该索引），确定将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

注意：主键不能被设置为隐藏索引。当表中没有显示主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

索引默认是可见的，在使用CREATE TABLE，CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或者INVISIBLE关键字设置索引的可见性。

索引的设计原则

​ 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。

索引的使用场景：

1. 在哪些列上面创建索引：

   • 字段的数值有唯一性的限制（业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引）

   • WHERE子句中经常出现的列上面创建索引，加快条件的判断速度

   • 按范围存取的列或者group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间

   • DISTINCT字段需要创建索引

   • 经常用于连接的列上添加索引，这些列主要是一些外键，可以加快连接的速度。注意：该字段在多张表中的类型必须一致，类型不一致会使用函数转换，从而导致索引失效。

   • 使用列的类型小的创建索引（类型大小指的是该类型表示的数据范围的大小。）

     • 数据类型越小，在查询时进行的比较操作就越快
     • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
     • 此规则对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其它所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

   • 使用字符串前缀创建索引。假设字符串很长，那存储一个字符串就需要占用很大的存储空间。在为这个字符串建立索引时，那就意味着在对应的B+树中存在以下两个问题：

     • B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大，
     • 如果B+树索引在索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

     可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。

   • 区分度高（散列性高）的列适合作为索引

2. 不在哪些列创建索引？

   • 只有很少数据值的列不应该增加索引。由于这些列的取值很少，例如性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
   • 在查询中很少使用的列不应该创建索引。由于这些列很少使用到，增加了索引，反而降低了系统的维护速度和增大了空间需求。
   • 当添加索引造成修改成本的提高远远大于检索性能的提高时，不应该创建索引。
   • 定义为text、image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
   • 数据量小的表也最好不要使用索引

参考：

• 索引的实现原理_张维鹏的博客-CSDN博客_
• 索引的创建与设计原则_小怪吖的博客-CSDN博客

性能分析工具的使用

参考：

• 性能分析工具的使用(上)_小怪吖的博客-CSDN博客

EXPLAIN详解

参考：

• 分析查询语句 EXPLAIN 详解_小怪吖的博客-CSDN博客
• explain执行计划详解_张维鹏的博客-CSDN博客_

索引优化与查询优化

虽然SQL查询优化的技术很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两部分。

• 物理查询优化是通过索引和表连接方式等技术来进行优化。
• 逻辑查询优化就是通过SQL等价变换提升查询效率，就是换一种执行效率更高的查询写法。

索引优化

• 最左前缀法则：在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

  结论：MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，联合索引不会被使用。
  

• 主键自增：自定义的主键列id拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

• 在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描：

  select * from doc where YEAR(create_time) <= '2016';         	-- 不能使用索引
  select * from doc where create_time<= '2016-01-01';            	-- 可以使用索引
  select * from order where date < = CURDATE();                   -- 不能使用索引
  select * from order where date < = '2018-01-2412:00:00';     	-- 可以使用索引
  select id from t where substring(name,1,3)=’abc’                -- 不能使用索引
  select id from t where name like ‘abc%’                         -- 可以使用索引
  select id from t where num/2=100                                -- 不能使用索引
  select id from t where num=100*2                                -- 可以使用索引
  

• 强制类型转换会导致全表扫描：字符串不加单引号会导致索引失效，因为MySQL会自动做类型转换，相当于在索引列上进行了操作。

  如果phone字段是varchar类型，则下面的SQL不能命中索引，因为内部发生了类型转换。
  select * from user where phone=13800001234;       -- 不能使用索引
  
  select * from user where phone='13800001234';     -- 可以使用索引
  

• 范围条件右边的列不能使用索引（范围列可以用到索引）：索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。金额查询，日期查询都是范围查询。应将查询条件放置在where语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

• is null可以使用索引，is not null无法使用索引。

  结论：最好在设计数据表的时候就将字段设置为NOT NULL约束，比如可以将INT类型的字段默认设置为0，将字符类型的默认设置为空字符串。同理，在查询中使用not like也无法使用索引，会导致全表扫描。（其实也不一定无法使用索引，而是要依据成本）

• like语句的前导模糊查询不使用索引

  select * from doc where title like '%XX'；   --不能使用索引
  select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引
  

• OR前后存在非索引的列，会导致索引失效。因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

• 数据库和表的字符集统一使用utf8mb4，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

• WHERE子句中索引列使用参数，也会导致索引失效：因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

  select id from t where num = @num  --不能使用索引
  select id from t with(index(索引名)) where num = @num  --可以改为强制查询使用索引
  

• 单表索引建议控制在5个以内：索引不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，同时也会占用空间。

关联查询优化

• 进行join联表查询的字段需要建立索引，join最好不要超过三个表，需要join的字段，数据类型必须一致：多表关联查询时，保证被关联的字段需要有索引。left join是由左边决定的，左边的数据一定都有，所以右边是关键点，建立索引要建在右边。当然如果索引在左边，可以用right join。

• 多张大数据量的表进行join连接查询时，最好在join之前过滤数据：在多个表进行join连接查询的时候，最好在一个表上先过滤好数据，然后再用过滤好的结果集与另外的表join，这样可以尽可能多的减少不必要的IO操作，大大节省IO操作所消耗的时间。

• LEFT JOIN时，选择小表作为驱动表，大表作为被驱动表，减少外层循环的次数。（所谓小结果集：即是表按照各自的条件过滤，当过滤完成后，计算参与join的各个字段的总数据量，数据量小的就应该作为驱动表）

Hash Join（8.0新特性）

​ Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

• 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
• 在表很大的情况下并不能完全放于内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
• 它能够很好的工作在没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join只能应用于等值连接（如WHERE A.COL1 = B.COL2）,这是由Hash的特点决定的。

子查询优化

​ 使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

• 执行子查询时，MySQL需要为内存查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，在撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
• 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
• 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，尽量使用JOIN查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。

select 
    b.member_id,b.member_type, a.create_time,a.device_model 
from 
    member_operation_log a 
inner join 
    (select member_id,member_type from member_base_info where `status` = 1) as b 
on 
    a.member_id = b.member_id;

排序优化

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。

INDEX a_b_c(a,b,c) 

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

当范围条件和group by或者order by的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

GROUP BY优化

​ group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件使用到索引，也可以直接使用索引。group by先排序再分组，遵循索引的最左前缀法则。WHERE效率高于HAVING，能写在WHERE限定的条件就不要写在HAVING中。ORDER BY、GROUP BY和DISTINCT这些语句较为耗费CPU。

优化分页查询

EXPLAIN SELECT * FROM student LIMIT 2000000,10;

此种语句需要MySQL排序前2000010条记录，仅仅返回2000000后的10条记录，其它记录丢弃，查询排序的代价非常大。

优化方案一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其它列内容

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a 
WHERE t.id = a.id;

优化方案二：该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

索引覆盖

​ 索引覆盖：被查询列被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用进行回表操作，加快查询速度。（简单说就是，索引列+主键包含SELECT到FROM之间查询的列）

索引下推

索引下推是MySQL5.6版本推出的功能，用于优化查询。

• 不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务层，服务层然后判断数据是否符合条件。
• 使用索引下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务层将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务层传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务层。

所以，索引下推就是存储引擎查询数据时，根据查询条件过滤掉一些记录，减去回表的次数，也可以减少MySQL服务层从存储引擎接收的数据量。

扩展change buffer：

​ 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

​ 将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作。

如果能够将更新操作先记录在change buffer中，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读取内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。唯一索引的更新不能使用change buffer，实际上也只有普通索引可以使用。（唯一索引在插入一行数据时会执行一次查询操作判断表中是否已经存在这条记录，判断是否违反唯一性约束，所以必须把数据页的数据读入内存，因此不能使用change buffer）

其它SQL语句优化

• in和exist都可以用于子查询，那么MySQL中in和exists有什么区别呢？

  1. 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
  2. in在内表查询或者外表查询过程都会用到索引；exists仅在内表查询时会用到索引。
  3. 一般来说，当子查询的结果集比较大，外表较小时使用exists效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。
  4. 对于not in和not exists，后者效率比前者高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用索引。not exists的子查询中可以用到表上的索引。

• 只返回必要的列，用具体的字段列表代替select*语句。原因：

  1. MySQL在解析的过程中，会通过查询数据字典将*按序转换成所有列名，这会大大的耗费资源和时间。
  2. 无法使用索引覆盖

• LIMIT 1对优化的影响：针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

• 多使用COMMIT：只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

• 尽量使用数字型字段：若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

• 写出统一的SQL语句：对于以下两句语句，很多人都认为是相同的。不过数据库查询优化器认为是不同的，虽然只是大小写不同，但必须进行两次解析，生成两个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

  select * from alone;
  select * From alone;
  

参考：

• SQL优化与索引优化_张维鹏的博客-CSDN博客
• 索引优化与查询优化(上)_小怪吖的博客-CSDN博客
• 索引优化与查询优化(下)_小怪吖的博客-CSDN博客

表结构优化

详情查阅以下博客：

表结构优化_张维鹏的博客-CSDN博客

参数优化

详情查阅以下博客：

参数优化_张维鹏的博客-CSDN博客

MySQL的性能优化总结

详情查阅以下博客：

MySQL数据库的性能优化总结_张维鹏的博客-CSDN博客

主键如何设计以及自增ID的问题

详情查阅以下博客：

淘宝数据库，主键如何设计以及自增ID的问题_小怪吖的博客-CSDN博客