Mysql基础概念巩固(分库分表,存储引擎，索引,备份，锁，EXPLAIN性能分析)

一.分库分表概念介绍

 1.什么是分库分表

   简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散    存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

 2.什么场景下需要分库分表

    (1)单机存储容量遇到瓶颈.

    (2)连接数,处理能力达到上限.

分库分表解决的问题:

    分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原 来单体服务的数据库进行拆分.将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量小，从而达到提升数据库性能的目的。

分库分表注意事项:

    分库分表之前,要根据项目的实际情况 确定我们的数据量是不是够大,并发量是不是够大,来决定是否分库分表.数据量不够就不要分表,单表数据量超过1000万或100G的时候, 速度就会变慢(官方测试).

 3.分库分表的方式

分库分表包括： 垂直分库、垂直分表、水平分库、水平分表 四种方式。

垂直分库: 数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进行分类,分布到不同的数据库上面, 将数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果.

垂直分表: 将一个表按照字段分成多表，每个表存储其中一部分字段。

                   

 

垂直拆分带来的一些提升:

      (1)解决业务层面的耦合，业务清晰

      (2)能对不同业务的数据进行分级管理、维护、监控、扩展等

       (3)高并发场景下，垂直分库一定程度的提高访问性能

       (注意: 垂直拆分没有彻底解决单表数据量过大的问题)

水平分库:

    将单张表的数据切分到不同的数据库中，每个数据库具有相同的库与表，只是表中数据集合不同。

     简单讲就是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面, 例如将订单表 按照id是奇数还是偶数, 分别存储在不同的库中。

水平分表:

    针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。

二.如何实现分库分表

java技术栈分库分表常用的技术有MyCat,ShardingJDB等

1.Mycat

什么是MyCat?

    MyCat 是目前最流行的基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议的服务器，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL原生协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分库分表和读写分离，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

     MyCat对于我们Java程序员来说,就是一个近似等于 MySQL 的数据库服务器，你可以用连接 MySQL 的方式去连接 Mycat（除了端 口不同，默认的 Mycat 端口是 8066 而非 MySQL 的 3306，因此需要在连接字符串上增加端口信息）。

    我们可以像使用MySQL一样使用MyCat,Mycat 可以管理若干 MySQL 数据库,同时实现数据的存储和操作。

MyCat的下载与安装?

(1). jdk: 要求jdk必须是1.7 及以上版本

(2). MySQL: 推荐mysql5.5 版本以上

(3). MyCat: Mycat的官方网站：http://www.mycat.org.cn/

该网站不仅可以下载安装包，还有教我们怎样使用的资料，非常详细。

2.ShardingJDBC

什么是ShardingJDBC?

    ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成,我们只关注 Sharding-JDBC即可。

    官方地址：https://shardingsphere.apache.org/document/current/cn/overview/

    Sharding-JDBC 定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架的使用。适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。基于任何第三方的数据库连接池，如：DBCP, C3P0, Druid等。支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

Sharding-JDBC主要功能？

(1)数据分片

(2)读写分离

    通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

Sharding-JDBC入门小案例

 一. 搭建基础环境

1.需求说明

    创建数据库lg_order, 模拟将订单表进行水平拆分, 创建两张表pay_order_1 与 pay_order_2，这两张表是订单表拆分后的表,我们通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的落入pay_order_1表 ，为奇数的落入pay_order_2表, 再通过Sharding-Jdbc 进行查询。

2.创建数据库

CREATE DATABASE lg_order CHARACTER SET 'utf8'; DROP TABLE IF EXISTS pay_order_1; CREATE TABLE pay_order_1 ( order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT , user_id INT(11) , product_name VARCHAR(128),  COUNT INT(11) ); DROP TABLE IF EXISTS pay_order_2; CREATE TABLE pay_order_2 ( order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT , user_id INT(11) , product_name VARCHAR(128),  COUNT INT(11) );

3.创建SpringBoot项目引入maven依赖

sharding-jdbc以jar包形式提供服务，所以要先引入maven依赖。

<dependency>     <groupId>org.apache.shardingsphere</groupId>     <artifactId>sharding-jdbc-spring-boot-starter</artifactId>     <version>4.0.0-RC1</version> </dependency>

4.分片规则配置(水平分表)

   使用sharding-jdbc 对数据库中水平拆分的表进行操作,通过sharding-jdbc对分库分表的规则进行配置,配置内容包括：数据源、主键生成策略、分片策略等。

   application.properties

      基础配置      

spring.application.name = sharding-jdbc-simple server.servlet.context-path = /sharding-jdbc spring.http.encoding.enabled = true spring.http.encoding.charset = UTF-8 spring.http.encoding.force = true spring.main.allow-bean-definition-overriding = true mybatis.configuration.map-underscore-to-camel-case = true

      数据源:

# 定义数据源 spring.shardingsphere.datasource.names = db1 spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order?characterEncoding=UTF-8&useSSL=false spring.shardingsphere.datasource.db1.username = root spring.shardingsphere.datasource.db1.password = 123456

         配置数据节点:

#配置数据节点,指定节点的信息 spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db1.pay_order_$->{1..2}

表达式 db1.pay_order_$->{1..2}

$ 会被 大括号中的 {1..2} 所替换

会有两种选择: db1.pay_order_1 和 db1.pay_order_2

配置主键生成策略 

#指定pay_order表 (逻辑表)的主键生成策略为 SNOWFLAKE spring.shardingsphere.sharding.tables.pay_order.key- generator.column=order_id spring.shardingsphere.sharding.tables.pay_order.key-generator.type=SNOWFLAKE

使用shardingJDBC提供的主键生成策略,全局主键

为避免主键重复, 生成主键采用 SNOWFLAKE 分布式ID生成算法

配置分片算法

#指定pay_order表的分片策略，分片策略包括分片键和分片算法 spring.shardingsphere.sharding.tables.pay_order.table- strategy.inline.sharding-column = order_id spring.shardingsphere.sharding.tables.pay_order.table- strategy.inline.algorithm-expression = pay_order_$->{order_id % 2 + 1}

分表策略表达式: pay_order_$-> {order_id % 2 + 1}

{order_id % 2 + 1} 结果是偶数 操作 pay_order_1表

{order_id % 2 + 1} 结果是奇数 操作 pay_order_2表

打开SQL日志 

# 打开sql输出日志 spring.shardingsphere.props.sql.show = true

步骤总结

1. 定义数据源

2. 指定pay_order 表的数据分布情况, 分布在 pay_order_1 和 pay_order_2

3. 指定pay_order 表的主键生成策略为SNOWFLAKE，是一种分布式自增算法，保证id全局唯一

4. 定义pay_order分片策略，order_id为偶数的数据下沉到pay_order_1，为奇数下沉到在

pay_order_2

Sharding-JDBC操作公共表

什么是公共表?

  公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。

  可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表的数据维护。

主要配置如下: 

# 例如指定district为公共表 spring.shardingsphere.sharding.broadcast-tables=district # 主键生成策略 spring.shardingsphere.sharding.tables.district.key-generator.column=id spring.shardingsphere.sharding.tables.district.key-generator.type=SNOWFLAKE

 

Sharding-JDBC读写分离与主从配置

  为了实现Sharding-JDBC的读写分离，首先，要进行mysql的主从同步配置。

我们直接使用MyCat讲解中,在虚拟机上搭建的主从数据库.

1. 配置数据源 （注意都是依自己的ip,用户名，密码，具体业务而言）

# 定义多个数据源 spring.shardingsphere.datasource.names = db1,db2,db3,m1,s1 spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.000.00.00:3306/test? characterEncoding=UTF-8&useSSL=false spring.shardingsphere.datasource.m1.username = root spring.shardingsphere.datasource.m1.password = 123456 spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.000.00.00:3306/test? characterEncoding=UTF-8&useSSL=false spring.shardingsphere.datasource.s1.username = root spring.shardingsphere.datasource.s1.password = 123456

2. 配置主库与从库的相关信息

ms1 包含了 m1 和 s1。

spring.shardingsphere.sharding.master-slave-rules.ms1.master-data-source-name=m1 spring.shardingsphere.sharding.master-slave-rules.ms1.slave-data-source-names=s1

3. 配置数据节点

#配置数据节点 spring.shardingsphere.sharding.tables.products.actual-data-nodes = ms1.products

3.MyCat与ShardingJDBC的区别

(1) mycat是一个中间件的第三方应用，sharding-jdbc是一个jar包。

(2) 使用mycat时不需要修改代码，而使用sharding-jdbc时需要修改代码。

(3) Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而Sharding-JDBC 是基于 JDBC 的扩展，是以 jar 包的形式提供轻量级服务的。

4.分库分表带来的问题

一. 分库分表带来的问题

   关系型数据库在单机单库的情况下,比较容易出现性能瓶颈问题,分库分表可以有效的解决这方面的问题,但是同时也会产生一些 比较棘手的问题。

二.事务一致性问题

   当我们需要更新的内容同时分布在不同的库时, 不可避免的会产生跨库的事务问题. 原来在一个数据库操作, 本地事务就可以进行控制, 分库之后 一个请求可能要访问多个数据库,如何保证事务的一致性,目前还没有简单的解决方案。

三.跨节点关联的问题

  在分库之后, 原来在一个库中的一些表,被分散到多个库,并且这些数据库可能还不在一台服务器,无法关联查询。解决这种关联查询,需要我们在代码层面进行控制,将关联查询拆开执行,然后再将获取到的结果进行拼装。

四.分页排序查询的问题

   分库并行查询时,如果用到了分页 每个库返回的结果集本身是无序的, 只有将多个库中的数据先查出来,然后再根据排序字段在内存中进行排序,如果查询结果过大也是十分消耗资源的。

五. 主键避重问题

   在分库分表的环境中,表中的数据存储在不同的数据库, 主键自增无法保证ID不重复, 需要单独设计全局主键。

六. 主键避重问题

   在分库分表的环境中,表中的数据存储在不同的数据库, 主键自增无法保证ID不重复, 需要单独设计全局主键。

七.公共表的问题

   不同的数据库,都需要从公共表中获取数据. 可以在每一个库都创建这个公共表, 所有对公共表的更新操作,都同时发送到所有分库执行. ShardingJDBC可以帮助我们解决这个问题。

三.MySql的备份与恢复

 1.备份的种类

  MySQL备份主要分为两类:

冷备份

   什么是冷备份?

      冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

   冷备份优点:

       (1)是操作比较方便的备份方法（只需拷贝文件）

       (2)低度维护，高度安全

   冷备份缺点:

   (1)在实施备份的全过程中，数据库必须要作备份而不能作其它工作。

   (2)若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢。

   (3)不能按表或按用户恢复。

  冷备份实战:

   (1) 关闭SELinux

    修改  selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退。    

vim /etc/selinux/config SELINUX=disabled

修改后需要重启

reboot  # 重启命令

   (2) 找到MySQL数据文件位置,停止MySQL服务

SHOW VARIABLES LIKE '%dir%'; -- 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/    service mysqld stop -- 停止mysql

  (3) 进入到  /mysql 目录, 执行打包命令 将数据文件打包备份

cd /var/lib/            # 进入其上级目录 tar jcvf /root/backup.tar.bz2 mysql/   # 打包压缩到 root目录下

  (4)删除掉数据目录下的所有数据

-- 删除原目录 rm -rf /var/lib/mysql/

  (5)恢复数据 (使用tar命令)

-- 解压 tar jxvf backup.tar.bz2 mysql/ -- 把备份的文件移动到/var/lib/里面去替代原来的mysql mv /root/mysql/ /var/lib

  (6) 启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功

service mysqld start

热备份:

 什么是热备份?

    热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。

热备份的优点:

   (1)可在表空间或数据文件级备份，备份时间短。

   (2)备份时数据库仍可使用。

   (3)可达到秒级恢复（恢复到某一时间点上）

冷备份的缺点:

   (1)不能出错，否则后果严重。

   (2)因难维护，所以要特别仔细小心，不允许“以失败而告终”。

冷备份实战:

   mysqldump 备份工具

     mysqldump是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）可以用于备份。热备可以对多个库进行备份，可以对单张表或者某几张表进行备份。

备份单个数据库:

(1) 创建文件夹 , 备份数据

 

[root@localhost ~]# mkdir databackup [root@localhost ~]# cd databackup [root@localhost databackup]# mysqldump -uroot -p testdatabase> testdatabase.sql

(testdatabase是数据库名)

(2) 模拟数据丢失，删除数据库,然后重新创建一个新的库.

DROP DATABASE testdatabase; CREATE DATABASE testdatabase CHARACTER SET 'utf8';

(3)恢复数据

[root@localhost databackup]cd databackup [root@localhost databackup]# mysql -uroot -p testdatabase< testdatabase.sql

备份某个表:

(1) 备份 表数据

[root@localhost databackup]# mysqldump -uroot -p testdatabase table1 table2 > testdatabase.sql

直接将MySQL数据库压缩备份

(1) 备份数据

mysqldump -uroot -p testdatabase | gzip > testdatabase.sql.gz

 

(2) 恢复数据

gunzip < testdatabase.sql.gz | mysql -uroot -p testdatabase

 

四.MySql存储引擎

什么是存储引擎?

     存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。就像汽车的发动机一样, 存储引擎好坏决定的数据库提供的功能和性能。存储引擎非常多，比较常见的存储引擎：InnoDB和MyISAM

存储引擎的应用场景?

 InnoDB：

(1)当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。

(2)更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求

MyISAM：

(1)以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务。

(2)对数据一致性要求不是非常高的业务（不支持事务）

(3)硬件资源比较差的机器可以用 MyiSAM （占用资源少）

InnoDB和MyISAM区别?  

(1)事务和外键

InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作

MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

(2)锁机制

InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现

MyISAM支持表级锁，锁定整张表

(3)索引结构

InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。

MyISAM使用非聚集索引（非聚簇索引），索引和记录分开

(4)并发处理能力

MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，写阻塞。MyISAM适合以读为主的业务。

InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发, 更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求。

(5)存储文件

InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；

MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

五.Mysql索引

什么是索引?

    索引就是排好序的,帮助我们进行快速查找的数据结构.

    简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能.

    专业一点来说，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的种类?

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

从应用层次划分：普通索引、唯一索引、主键索引、复合索引

从索引键值类型划分：主键索引、辅助索引（二级索引）

从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

CREATE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD INDEX [索引的名字] (字段名); CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名); CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只

能有一个主键。

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引

用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替

多个单一索引，相比多个单一索引复合索引所需的开销更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...); ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...); CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对

更新操作效率有很大影响。

2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索

引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提

高。

全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有

MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名); CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE两种。和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb'); -- * 表示通配符,只能在词的后面 SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*'  IN BOOLEAN MODE);

索引的优势和劣势?

优点

(1)提高数据检索的效率,降低数据库的IO成本

(2)通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

缺点

(1)创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

(2)索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间

(3)当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

创建索引的原则

(1)在经常需要搜索的列上创建索引，可以加快搜索的速度。

(2)在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。

(3)在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。

(4)在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

(5)在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

(6)在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

索引的原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。

一. HASH结构

   Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

  对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，

并且不同键值的行计算出来的哈希码也不一样.图示如下:

 

 

Hash索引的缺点：

(1)哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

(2)哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

(3)哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

Hsah索引的优点:

 (1) 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引

 (2)访问哈希索引的数据非常快，除非有很多哈希冲突

二. B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B+Tree结构

(1)非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。

(2)叶子节点包含了所有的索引值和data数据

(3)叶子节点用指针连接，提高区间的访问性能。

 

B树的搜索:从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找，没有命中会进入子节点重复查找，直到所对应的节点指针为空，或已经是叶子节点了才结束。

另外一篇博客阐释了B树和B+树:https://blog.csdn.net/illovesoftware/article/details/116207829

六.Mysql锁机制

1.锁的概念

    数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

2.MySql锁的分类

MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁机制。

(1)MyISAM和MEMORY存储引擎采用的表级锁，

(2)InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

(3)BDB采用的是页面锁，也支持表级锁

按照数据操作的类型分

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分

表级锁（偏读）：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。

表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。

特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低

行级锁（偏写）：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

    使用MySQL行级锁的两个前提:

        (1)使用 innoDB 引擎

        (2)开启事务 (隔离级别为  Repeatable Read )

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

按照操作性能可分为乐观锁和悲观锁

乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

七. EXPLAIN性能分析

1.简介

    使用  EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：

（1） 读取顺序。（对应id）

（2）数据读取操作的操作类型。（对应select_type）

（３）哪些索引可以使用。（对应possible_keys）

（４）哪些索引被实际使用。（对应key）

（５）表直接的引用。（对应ref）

（６）每张表有多少行被优化器查询。（对应rows）

２.入门案例

explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。

例如:在数据库随意创建一张表course后执行如下sql

explain select * from course;

 

写在最后：随后会写一篇分库分表搭建的过程，所谓的踩坑指南，哈哈！