MySQL数据库中的键和索引的概念

本篇内容主要讲解“MySQL数据库中的键和索引的概念”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL数据库中的键和索引的概念”吧!

1.键的概念

• 键：数据库中的键（key）又称为关键字，是关系模型中的一个重要概念，它是逻辑结构，不是数据库的物理部分；

• 唯一键：即一个或者一组列，其中没有重复的记录，可以唯一标示一条记录；

• 主键：属于唯一键，是一个比较特殊的唯一键，区别在于主键不可为空；

# id为主键  name是唯一键
create table Mark(id int not null primary key ,name varchar(250) unique key);
create table Mark(id int, name varchar(250), primary key(id), unique key(name));

• 外键：一张表外键的值一般来说是另一张表主键的值，因此，外键的存在使得表与表之间可以联系起来；

create table students_to_teacher(to_id int, stu_id int, tea_id int, foreign key(stu_id) references students(id), foreign key(tea_id) references teacher(id));

2.索引的概念

• 索引就像是一张表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据，保存索引数据的文件一般会与保存数据的目录分开；

• 索引应该构建在经常被用作查询条件的字段上；

3.索引类型

• 普通索引：加速查询；

# 创建普通索引
create index 索引名称 on 表名(列名)
# 删除普通索引
drop index 索引名称 on 表名
# 查看索引
show index from 索引名

• 唯一索引（UNIQUE KEY）：加速查询 + 列值唯一（可以有null）；

# 创建唯一索引
create unique index 索引名称 on 表名(列名)
# 删除唯一索引
drop unique  index 索引名称 on 表名

• 主键索引（PRIMARY KEY）：用于加速查询，只能有一个主键字段，不允许重复且不能为NULL；

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并；

• 全文索引：对文本的内容进行分词，进行搜索；

• 空间索引：指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构；

4.引优化策略

• 独立使用列，尽量避免其参与运算；

• 左前缀索引：查询字段的时候，条件过滤时，最左前缀精确匹配；

• 多列索引：AND 连接字段时适合多列索引，选择合适的索引次序，将选择性最高的放在左侧，范围匹配的放在右侧；

5.Innodb 存储引擎

• 所有的InnoDB表的数据和索引存储于同一个表文件中，但是表数据和表结构分离；

-rw-rw---- 1 mysql mysql    65 8月  27 14:31 db.opt
    -rw-rw---- 1 mysql mysql  8614 8月  27 14:31 students.frm
    -rw-rw---- 1 mysql mysql 98304 8月  27 14:31 students.ibd
    db.opt文件：主要用来存储当前数据库的默认字符集和字符校验规则
    students.frm文件：存放表结构的
    students.ibd文件：存储了当前表的数据和相关的索引数据
    因此，表数据和表结构分离, 每个表单独使用一个表文件来存储数据和索引

• Mariadb默认的存储引擎是XtraDB，但是为了与MySQL兼容，因此也取名做InnoDB，因为MySQL的默认存储引擎是InnoDB；

• 使用聚集索引（数据和索引在一起），也支持自适应hash索引，锁粒度为行级别，支持支持热备工具；

• 支持事务的存储引擎，适合处理大量的短期事务；

6.MyISAM 存储引擎

• 所有的MyISAM表的数据和索引存放在不同的文件中，表结构也分离；

-rw-rw---- 1 mysql mysql  10630 8月  27 13:12 user.frm
-rw-rw---- 1 mysql mysql    504 8月  27 13:15 user.MYD
-rw-rw---- 1 mysql mysql   2048 8月  27 14:30 user.MYI
user.frm：为表结构
user.MYD：为表数据
user.MYI：为表索引

• 支持全文索引（fulltext index），压缩，空间函数；

• 不支持事物，表级锁，适用于只读，读多写少；

7.MySQL的并发访问控制

• 任何的数据集只要支持并发访问模型就必须基于锁机制进行访问控制；

• 读锁：共享锁，允许给其他人读，不允许他人写；

• 写锁：独占锁, 不允许其他人读和写；

• 显示锁：用户手动请求读锁或写锁；

• 隐式锁：由存储引擎自行根据需要加的，无需我们管理；

• 给表施加锁机制

# lock tables 方式施加锁
lock tables 表名称 read   # 读锁
lock tables 表名称 write  # 写锁
# 给表解锁    
unlock tables;    
# flush tables 方式施加锁
flush tables 表名称 with read lock;   #读锁
flush tables 表名称 with write lock;  # 写锁

8.MySQL事务机制

• 一组原子性的SQL查询，或多个SQL语句组成了一个独立的单元，要么这一组SQL语句全部执行，要么全部不执行；

事物日志：

• 管理事物机制的日志；

• redo日志：记录SQL执行的语句，这些SQL语句还没有同步到磁盘上，没有修改数据。如果数据奔溃，可以通过撤销SQL执行的语句来进行还原。但是，如果已经同步到磁盘上的SQL语句而言，就只能使用undo来回滚之前的数据了；

• undo日志：记录没有执行SQL的样子，也就是记录修改数据之前的数据记录下来；

ACID机制：

• automicity：原子性，整个事物中的所有操作要么全部成功提交，要么全部失败回滚；

• consistency：一致性，数据库总是从一个一致性状态转化为另一个一致性状态；

• isolation： 隔离性，事物不会相互影响，一个事物所作出的操作在提交之前，是不能为其他事物所见，隔离有多种级别，主要是为了并发；

• durability：持久性，事物一旦提交，其所作的修改会保存在数据库中，不能丢失；

9.MySQL 查询缓存机制

• 缓存的是查询语句的整个查询结果，是一个完整的select语句的缓存结果；

• 哪些查询可能不会被缓存 ：查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级别的权限表、有着不确定值的函数；

1.query_cache_min_res_unit：查询缓存分配内存块的最小的分配单位，较小的值较少内存浪费，但是会导致更加平凡的内存分配操作 ，较大的值会导致浪费
2.query_cache_limit：能够缓存的最大查询结果，对有较大结果的查询语句，建议在select中使用SQL_NO_CACHE
3.query_cache_size：查询缓存总共可用的内存空间，单位是字节，必须是1024整数倍
4.query_cache_type：ON , OFF , DEMAND
5.query_cache_wlock_invalidate：如果某个数据表被其他的连接锁定，是否仍然可以从查询缓存中返回结果，默认值为off，表示可以返回数据，on为不允许

• 缓存命中率

缓存命中率计算公式： Qcache_hits / (Qcache_hits+Com_select)

10.MySQL 日志分类

• 查询日志 ：query log ，一般不启用；

general_log = {ON|OFF}  # 是否启用查询日志
general_log_file = /logs/mysql/general_log  # 当log_output为FILE类型时，日志信息的记录位置；
log_output = {TABLE|FILE|NONE} 
log_output = TABLE,FILE

• 慢查询日志：slow_query_log ，用于对执行速率较慢的SQL语句就像过滤，有利于SQL代码的优化；

1.执行时长超出指定时长的操作 
show global variables like 'long_query_time'; 查看指定的时长
set global long_query_time = 自定义时长
2.slow_query_log = {ON|OFF}：是否启用慢查询日志
set global slow_query_log = ON
3.slow_query_log_file = mariadb1-slow.log
# 过滤条件
4.log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
5.log_slow_rate_limit = 1 指定记录速率
6.log_slow_verbosity = 指定内容级别

• 错误日志：error log ，必须启用，二进制日志可以反应MySQL数据库的错误信息，用于调试；

# 错误日志信息产生的来源
    mysqld启动和关闭过程中输出的信息；
    mysqld运行中产生的错误信息；
    event scheduler运行一个event时产生的日志信息
    在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息；
# 如何开启错误日志
    log_error = /path/to/somefile
    log_warnings = {ON|OFF}：是否记录警告信息于错误日志中；

• 二进制日志：binary log，用于通过’重新执行’日志文件中的记录的事件（SQL语句）来生成数据副本，也就是用于主从复制；

# 日志记录的格式分类
    基于“SQL语句”记录: statement
    基于“行”记录：row
    “混合模式” ：mixed，系统自行判断
# 二进制日志文件的构成
    日志文件：mysql-bin.文件序号 
        例如： mysql-bin.000001
    索引文件：mysql-bin.index 
        例如：mysql-bin.index

• 中继日志：relay log ，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的时间；

• 事务日志：transaction log ，事物日志由事物型存储引擎自行管理和使用，无需手动管理；

11.MySQL 备份策略的注意点

• 可容忍丢失多少数据；

• 恢复需要在多长时间内；

• 备份的对象: 数据、二进制日志和InnoDB的事务日志、SQL代码（存储过程和存储函数、触发器、事件调度器等）、服务器配置文件；

• 备份类型

1.站在数据集是否完整的角度上
    完全备份，部分备份 
2.站在完全备份的基础上
    增量备份，差异备份 
3.站在是否影响数据集读写的角度上
    热备份：在线备份，读写操作不受影响；
    温备份：在线备份，读操作可继续进行，但写操作不允许
    冷备份：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务
    MyISAM存储引擎: 能够实现温备
    InnoDB存储引擎: 能够实现热备
4.站在数据存储角度上
    物理备份：直接复制数据文件进行的备份
    逻辑备份：从数据库中“导出”操作数据的SQL语句，再执行，实现备份

12.备份策略需要考虑的因素

• 持锁的时长；

• 备份过程时长；

• 备份负载；

• 恢复过程时长；

13.数据库备份具体解决方案

• 数据：完全备份 + 增量备份

• 备份：物理 + 逻辑

14.备份工具介绍

• mysqldump：逻辑备份工具，适用于所有存储引擎，温备；但是对InnoDB存储引擎支持热备；

• scp, tar 等文件系统工具：物理备份工具，适用于所有存储引擎；冷备；完全备份，部分备份，不适用于Innodb存储引擎；

• lvm2的快照：几乎热备；借助于文件系统工具实现物理备份；

• mysqlhotcopy： 几乎冷备；仅适用于MyISAM存储引擎

15.MySQL 半同步复制模型

• 所谓的半同步复制指的是一台主节点有多个从节点，在众多的从节点之中有一个从节点在收到主节点的二进制日志信息之后，存储在中继日志中，执行中继日志后，给主节点一个反馈信息，直接点收到这个反馈信息之后，返回给执行这句SQL的ORM语句，表示数据已经存储完毕；

到此，相信大家对“MySQL数据库中的键和索引的概念”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！