MySQL知识汇总

数据库 MySQL
数据库
发布日期:   2020-10-30
文章字数:   10.6k
阅读时长:   38 分
阅读次数:  

MySQL知识汇总

目录

  1. 数据库的概念
  2. MySql逻辑架构
  3. 并发控制
  4. 事务
  5. MVCC多版本并发控制
  6. 存储引擎
  7. 索引
  8. MySql优化
  9. 其他知识点

参考资料

数据库的概念

（1）DB

数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。

（2）DBMS

数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器

（3）SQL

结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言

MySql逻辑架构

架构图

连接/线程管理

（1）每个客户端连接都会在服务器进程中拥有一个线程

（2）数据库连接池

  • 池化思想：预设资源
  • 本质是socket连接
  • 作用：维护连接的缓存，以便将来重用连接

（3）默认端口号：3306

关系型数据库

（1）关系型数据库
关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说，关系型数据库是由多张能互相连接的表组成的数据库。

·优点

  • 都是使用表结构，格式一致，易于维护。
  • 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
  • 数据存储在磁盘中，安全。

·缺点

  • 读写性能比较差，不能满足海量数据的高效率读写。
  • 不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间。
  • 固定的表结构，灵活度较低。
  • 常见的关系型数据库有 Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access 和 MySQL 等。

（2）非关系型数据库

非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

- 优点

  • 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
  • 速度快，效率高。NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
  • 海量数据的维护和处理非常轻松。
  • 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
  • 可以实现数据的分布式处理。

- 缺点

  • 非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
  • 非关系数据库没有事务处理，没有保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
  • 功能没有关系型数据库完善。

  • 常见的非关系型数据库有 Neo4j、MongoDB、Redis、Memcached、MemcacheDB 和 HBase 等。

  • 分类

  • 共同的特点

    1. 易扩展
      NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。无形之间，在架构的层面上带来了可扩展的能力。 [2]
    2. 数据量，高性能
      NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。一般MySQL使用Query Cache。NoSQL的Cache是记录级的，是一种细粒度的Cache，所以NoSQL在这个层面上来说性能就要高很多。
    3. 灵活的数据模型
      NoSQL无须事先为要存储的数据建立字段，随时可以存储自定义的数据格式。而在关系数据库里，增删字段是一件非常麻烦的事情。如果是非常大数据量的表，增加字段简直就是——个噩梦。这点在大数据量的Web 2.0时代尤其明显。 [2]
    4. 高可用
      NoSQL在不太影响性能的情况，就可以方便地实现高可用的架构。比如Cassandra、HBase模型，通过复制模型也能实现高可用。

查询缓存

（1）执行查询语句时，先查询缓存

（2）MySql8.0后移除，不太实用，可通过redis等高速缓存缓存结果。

（3）弊端：

  • 额外的开销，查询后缓存，失效后销毁。
  • 严格匹配才能命中缓存
  • 查询中不能包含不确定语句，否则不缓存

存储引擎

  • 作用：负责MySql中数据的存储和提取
  • 服务器通过API与存储引擎通信

并发控制

更详情可以参考：深入理解MySQL锁类型和加锁原理 - 掘金

读写锁

（1）共享锁–读锁

  • 共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。

  • 共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

  • 用法

    SELECT ... LOCK IN SHARE MODE;

    Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据

（2）排他锁–写锁

  • 排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。

  • 一个写锁会阻塞其他的写锁和读锁

  • 写锁比读锁有更高的优先级

  • 用法

    SELECT ... FOR UPDATE;

    Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

    在更新操作**(INSERT、UPDATE 或 DELETE**)过程中始终应用排它锁。

锁粒度

尽量只锁定需要修改的部分数据，而不是所有的资源。MySQL存储引擎可以实现自己的锁策略和锁粒度。

表锁

特点：

  • 锁定整张表
  • 不会出现死锁
  • 开销小
  • MySql为DDL类语句使用标所，忽略存储引擎的锁机制。

表锁共享锁用法：

LOCK TABLE table_name [ AS alias_name ] READ

表锁排它锁用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

表锁解锁用法：

unlock tables;

行锁

特点

  • 锁定当前操作的行
  • 最大程度地支持并发处理
  • 开销大
  • 会出现死锁

InnoDB中的行锁分类

  • Record lock 对索引加锁，锁定行
  • Gap lock “间隙”加锁，锁定一个范围（不含索引项本身），防止幻读
  • Next-Key lock 结合上述两种，锁定索引项本身和索引范围，解决幻读问题

注意在InnoDB中，具体加锁的方式（行锁是加在了索引上）和底层原理

update user set age = 10 where name = 'Tom';

345.jpg

InnoDB的死锁

（1）InnoDB的行级锁是基于索引实现的，如果查询语句为命中任何索引，那么InnoDB会使用表级锁

（2）使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

（3）InnoDB产生死锁原因

不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退（InnoDB目前将持有最少行级排他锁的事务回滚），另一个则可以获取锁完成事务

（4）避免死锁：

  • 通过表级锁来减少死锁产生的概率；
  • 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
    同一个事务尽可能做到一次锁定所需要的所有资源。

事务

概念

逻辑上的一组操作，要么都执行，要么都不执行（即回滚，返回上一次正确状态）。

事务四大特性（ACID）

（1）原子性

事务是最小的执行单位，不可分割。整个事务中的所有操作要么都提交成功，要么全部失败回滚。

（2）一致性

执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。若事务最终没有提交，则事务中所做的修改不会保存到数据库中。

（3）隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库的数据是独立的。

四种隔离级别

  • READ UNCOMMITTED 未提交读/脏读。事务中的修改还没有提交，对其他事务也可见，即其他事务读取了未提交的数据。
  • READ COMMITTED 提交读/不可重复读。一个事务只能“看见”已经提交的事务所做的修改，可能执行两次查询，得到不一样的结果。
  • REPEATABLE READ 可重复读/幻读。是MySQL的默认事务隔离级别，保证了在同一个事务中多次读取同样记录的结果一致。可能发生幻读：一个事务在读取某个范围内的记录时，另一个事务在范围内插入记录，导致幻行。通过MVCC多版本并发控制解决问题。
  • SERIALIZEABLE 可串行化。最高的隔离级别，强制事务串行执行。本质是在读取的每一行数据上都加锁。

img

（4）持久性

一旦事务提交，则其所做的修改就会永久保存到数据库中。

事务日志

  • 存储引擎在修改表的数据时，只需要修改其内存的拷贝，再把修改行为记录到持久在硬盘的事务日志中。
  • 即修改数据，需要写两次磁盘。
  • 若系统崩溃，可通过日志自动恢复数据

MySQL中的事务

（1）MySQL默认采用自动提交模式。即如果不显式地开始一个事务，则每个查询都被当做一个事务执行提交操作。

（2）显式和隐式锁定

  • 隐式：事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放锁
  • 显式：在MySQL语句中使用LOCK TABLES或者UNLOCK TABLES。

MVCC多版本并发控制

特性

MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然不同数据库的实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

相关概念

快照 snapshot

（1）事务快照是用来存储数据库的事务运行情况

（2）生成时机

  • 在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来;

  • 在innodb中(READ COMMITTED级别), 事务中每条select语句都会创建一个快照(read view);

  • Read View 存放着一个列表，这个列表用来记录当前数据库系统中活跃的读写事务，也就是已经开启了，正在进行数据操作但是还未提交保存的事务。可以通过这个列表来判断某一个版本是否对当前事务可见。其中，有四个重要的字段：

    creator_trx_id：创建当前Read View所对应的事务ID

    m_ids：所有当前未提交事务的事务ID，也就是活跃事务的事务id列表

    min_trx_id：m_ids里最小的事务id值

    max_trx_id：InnoDB 需要分配给下一个事务的事务ID值（事务 ID 是累计递增分配的，所以后面分配的事务ID一定会比前面的大！）

undo-log

（1）当我们对记录做了变更操作时就会产生undo记录

（2）Undo记录中存储的是老版本数据。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录

redo-log

存储变更后的数据

InnoDB实现的MVCC

InnoDB存储引擎在数据库每行数据的后面添加了三个字段

字段称之为系统版本号。

（1） 事务ID DB_TRX_ID

用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。

至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。

（2）回滚指针 DB_ROLL_PTR

指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。 如果一行记录被更新, 则 undo log record 包含 ‘重建该行记录被更新之前内容’ 所必须的信息

（3） 新行插入而单调递增的ID DB_ROW_ID

如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 但聚簇索引会使用DB_ROW_ID的值来作为主键; 如果我们有自己的主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID 了

更新步骤

image

（1）排他锁锁定该行

（2）记录redo-log

（3）将修改前的值复制到undo-log

（4）修改当前行的值，填写事务编号，使用回滚指针，指向Undo-log中的修改前的行

当前读和快照读

InnoDB存储引擎默认的事务隔离级别是REPEATABLE-READ。通过 “行排他锁+MVCC” 一起实现的, 不仅可以保证可重复读, 还可以防止幻读。

（1）当前读

如select … lock in share mode， select … for update ，insert，update，delete操作，在RR级别下，使用record lock 和 gap lock来实现的。

（2）快照读

如简单的select操作(当然不包括 select … lock in share mode, select … for update)，是通过MVVC(多版本控制)和undo log来实现的。

  • REPEATABLE-READ 此时在第一次读操作时，生成Read View数据（快照），所以可以重复读取，MVCC的活跃事务列表记录后面所有事务，所以可以读到历史版本数据。
  • READ COMMITTED 每次读取数据前都生成一个ReadView ，所以不能重复读取，MVCC的活跃事务列表记录此时后面的未提交的事务，只能读到新版本的数据。

参考：

工作环境

仅在两个隔离级别下工作，REPEATABLE READ(可重复读)和READ COMMITTED（提交读）。

存储引擎

查看表的存储引擎

show table status like “table_name”

InnoDB对数据的读写

InnoDB处理数据的过程是发生在内存中的，需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。

读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小默认为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，或者一次最少把内存中的16KB内容刷新到磁盘中。

所以当你用postman测试一个分页查询接口时，发现第一次打印耗时300 ~ 400ms，往后不停的查找下一页都是30 ~ 40ms，原因就是第一次请求接口时，读数据库的时候需要读磁盘，从磁盘加载16KB的数据到内存，往后下一页的数据都是从内存中获取，没有再读磁盘，除非在内存中的16KB的数据中找不到，才会再次读磁盘获取下一个16KB的数据到内存中。

MyISAM和InnoDB对比

MySQL 5.5版本后默认的存储引擎为InnoDB。

（1）锁机制

MyISAM只有表级锁，InnoDB支持行级锁（默认）和表级锁。

（2）事务

MyISAM不支持事务，InnoDB事务安全

（3）查询和添加速度

MyISAM每次查询具有原子性，执行速度比InnoDB快。

（4）外键

MyISAM不支持，而InnoDB支持

主键和外键参考：（转）关于数据库主键和外键（终于弄懂啦） - liuslayer - 博客园

（5）MVCC

MyISAM不支持，而InnoDB支持

（6）索引

  • MyISAM的叶节点是数据记录的地址，即为“非聚集索引”。

  • InnoDB叶节点是完整的数据记录，即为“聚集索引”，索引的Key为数据表的主键。所以InnoDB表数据文件本身就是主索引，其余索引都是辅助索引，data中记录了主键的值而不是地址。

    因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

    聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引（需要回表操作）：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

    聚集索引参考：聚集索引和非聚集索引（整理） - 布颜书 - 博客园

· 注意
“聚集索引”是一种数据存储方式，同时保存索引和数据行。一个表中只能有一个聚集索引。

索引

意义

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。

实现原理

B+Tree索引

image

（1）按顺序存储值，每一个叶子节点到根距离相同。

（2）B+Tree：叶节点指向下一个叶节点，方便叶节点遍历

B树与B+树与磁盘读取的联系_键盘上的青春-CSDN博客_b树 磁盘

深入理解数据库索引采用B树和B+树的原因_菜鸟不会飞-CSDN博客_b树和b+树

哈希索引

（1）基于哈希表实现，存储引擎会对所有的索引列计算一个哈希码

（2）在为单条记录查询时，选择哈希更快

（3）自适应哈希索引（InnoDB）：当索引值被使用非常频繁时，基于B-Tree索引上再建一个哈希索引。

（4）避免哈希冲突：在WHERE条件中带入哈希值和对应列值。

分类

（1）主键索引

  • 唯一性索引，必须指定为“PRIMARY KEY”。每个表只能有一个主键，主键也不能为空

  • 方式：

    CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) )
ALTER TABLE tablename ADD PRIMARY KEY (列的列表)

（2）全文索引

  • FULLTEXT (title,body)，查找的是文本中的关键词

    select * from articles where match(title,body) against ( 'database')

（3）普通索引

  • 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度
  • create index 索引名 on 表 (列1,列名2);

（4）唯一 索引

  • 索引列的所有值都只能出现一次，即必须唯一。
  • CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)

（5）组合索引

参考：普通索引和组合索引 - 简书

  • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；

  • 组合索引，即一个索包含多个列。

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age、usernname,city、 usernname

为什么没有 city，age这样的组合索引呢? 这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合

注意区别

  • 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
  • 唯一性索引列允许空值，而主键列不允许为空值。
  • 主键列在创建时，已经默认为空值 + 唯一索引了。
  • 主键可以被其他表引用为外键，而唯一索引不能。
  • 一个表最多只能创建一个主键，但可以创建多个唯一索引。
  • 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
  • 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

其他参考资料：索引的分类

主键索引和辅助索引的存储结构和查询过程

参考：MySQL的数据结构以及辅助索引的使用_MemoForward的博客-CSDN博客_mysql辅助索引

这里提供一个数据库表。我们假设：id为主键索引，name为辅助索引。

id name gender
1 cxy male
2 lhw female
3 tyy female
4 dsx male

InooDB索引结构是B+Tree，其索引就是一个个key。但是主键索引和辅助索引在叶子节点的存储结构上有一点不同。

  • 对于主键索引，其存储结构可能如下（仅做示意）

    image-20220309103430624

    因此若执行select * from this_table where id = 3，就会执行：

    image-20220309103513340

  • 辅助索引结构示意图：顺序是ASCII码

    image-20220309103535679

    可见，辅助索引中存储的是主键索引，而不是真正的数据值。
    因此若执行select * from this_table where name = cxy，就会执行

    image-20220309103556999

    从辅助索引回到主键索引树搜索的过程，即为回表

  • 注意

    • InooDB引擎必须要有主键，因为整个引擎都是以主键构成的B+Tree结构而存在的。如果我们不指定主键，mysql会自动给我们创建一个隐藏的主键。
    • InooDB的主键最好设置成：与业务无关且自增。 我们观察BTree的结构，一旦插入值，这个结构就要重建，很费时，如果主键是自增的，可以减少插入数据时BTree树的更新时间(降低数据写入的时间)。
    • 辅助索引如果不手动创建，MySQL是不会帮我们创建的，如果没有辅助索引，我们通过name来找数据的时候，搜索复杂度是O(n)，非常耗时。
    • 辅助索引并不是越多越好。 我们观察整个搜索流程会发现，辅助索引通过牺牲空间的方式来提升查询速度，如果辅助索引过多，必然会造成空间的浪费。

覆盖索引

参考：

SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。即避免回表查询

列如：

select id from user where age=3;

直接在age索引树中就能查询到id的值，不用再去id索引树中查找其他的数据，避免了回表。

强制索引

参考：mysql优化之强制索引

在某些字段已经加了索引的情况下 ，仍然使用的是ALL扫描全表查资料，得出原因是因为mysql优化器认为走全表扫描更快那么此时就需要告诉优化器，强制使用XXX索引，可以增快查询速度。

explain
SELECT
	city_name,
	hourflag,
	eletricflag
FROM
	t_eletric_segment_info
force index(index_hourflag)
WHERE
	hourflag >= '2019032600'
AND
	hourflag <= '2019040123'

查询索引命令

  • show index from 表名
  • show keys from 表名

使用索引的代价

对DML(update、delete、insert)语句的效率，增删改会对索引影响，因为索引要重新整理。

注意事项

（1）存储引擎对索引的支持

image

（2）什么时候加索引

  • 查询作为查询条件字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引,即使频繁 Select * from emp where sex=’男’
  • 频繁更新字段，也不要定义索引。
  • 不会出现在where语句的字段不要创建索引

（3）索引失效

  • 模糊查询在like前面有百分号开头会失效。有很多的优化逻辑，其中一个是，使用全文索引代替like进行优化
  • 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

Explain命令

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

explain select * from emp where name = 'Jefabc';

img

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

id

SELECT识别符。这是SELECT的查询序列号

  1. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  2. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

查询中每个select子句的类型

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称。

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

  • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  • index: Full Index Scan，index与ALL区别为index类型只遍历索引树

  • range:只检索给定范围的行，使用一个索引来选择行

  • ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  • eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  • const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

  • NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null)

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

extra

该列包含MySQL解决查询的详细信息

  • **distinct**： 一旦mysql找到了与行相联合匹配的行，就不再搜索了
  • **Using index**：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。一遍出现在覆盖索引查询的情况。
  • Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了 ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
  • **Using where**：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
  • **Using temporary**：mysql需要创建一张临时表来处理查询，常见于排序和分组查询，常见 group by ，order by。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  • **Using filesort**：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

更具体可以参考：

数据库连接的耗时分析

Class.forName("com.mysql.jdbc.Driver");
String name = "shine_user";
String password = "123";
String url = "jdbc:mysql://172.16.100.131:3306/clever_mg_test";
Connection conn = DriverManager.getConnection(url, name, password);
conn.close();

  • 第 1 步：此时处于 MySQL 通信协议阶段，客户端发送关闭连接请求，而且不用等待服务端的响应。
  • 第 2 步：TCP 断开连接，4 次挥手完成连接断开。

这里是完整地完成了从数据库连接的建立到关闭，整个过程花费了：

747.284311 - 747.100954 = 0.183357s = 183.357ms

这里可能也有网络状况的影响，比上述的 225ms 少了，但是也几乎达到了 200ms 的级别。那么问题来了，想象一下这个场景，对于一个日活 2 万的网站来说，假设每个用户只会发送 5 个请求，那么一天就是 10 万个请求。对于建立数据库连接，我们保守一点计算为 150ms 好了，那么一天当中花费在建立数据库连接的时间有（还不包括执行查询和更新操作）：

100000 * 150ms = 15000000ms = 15000s = 250min = 4.17h

也就说每天花费在建立数据库连接上的时间已经达到「4 个小时」，所以说数据库连接池是必须的嘛。

而且当日活增加时，单单使用数据库连接池也不能完全保证你的服务能够正常运行，还需要考虑其他的解决方案。

一张数据库表的最大记录数

MySQL本身并没有对单表最大记录数进行限制，这个数值取决于你的操作系统对单个文件的限制本身。业界流传是500万行。超过500万行就要考虑分表分库了。

阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，MySql数据库一张表中能存储的最大数据量和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为，MySQL 为了提高性能，会将表的索引装载到内存中。InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。

但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

MySql扩展

分表分库

（1）垂直拆分（拆分列）
垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式）。例如分布式情况下，为将一个项目进行拆分成多个子模块，每个子模块有单独数据库（解耦数据）。

（2）水平拆分（拆分行）
水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如时间规则、地点、日志等，还有哈希算法。

（3）id主键处理方式

  • UUID 适用于生成文件名，不适合作为主键，因为太长了。
  • 不同步自增id，如主奇从偶
  • redis生成id，但是让系统更复杂

读写分离

（1）主从复制

  • 原理，主数据库将二进制日志文件（bin-log）传给从数据库。
  • 触发时刻：从数据库监控主数据库二进制文件的变化，发生变化则通过IO线程进行二进制文件的复制和传输。
  • 意义
      - 数据丢失和备份
  - 读写分离
  - 负载均衡，高可用

（2）读写分离

  • 定义：主数据库负责助理事务，从数据库负责读操作
  • 意义
          - 分摊服务器压力，提高机器的系统处理效率
      - 增加冗余，提高系统可用性，可以在主数据库宕机式进行数据恢复

系统扩展

（1）垂直扩展（scale-up），在单机器上加存储、加内存、加CPU

（2）水平扩展（scale-out）， 加机器数量，做集群

MyCat 中间件

（1）作用：分发请求，保证安全、读写分离、高可用、负载均衡

（2）原理：对数据进行分片处理，所有的分片数据库集群构成完整的数据库存储

· 阿里的rds系统
即开即用的关系型数据库服务，提供了强大丰富的功能，保证高可用性、安全性、高性能。

其他知识点

存储过程和函数

（1）就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。

（2）存储过程特性：

  • 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
  • 函数的普遍特性：模块化，封装，代码复用；
  • 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行。

（3）demo

· 创建一个存储过程

create procedure user_porced ()
begin
select name from users;
end;

· 调用存储过程

call porcedureName ();

· 传参存储过程

create PROCEDURE user_porcedPa(
 in a int(10)
)
 BEGIN
 select * from users where age>a;
 END;
> call user_porcedPa(10);

数据库备份

（1）手动方式

  • cmd控制台:
    在环境变量中配置mysql环境变量
    mysqldump –u -账号 –密码 数据库[表名1 表名2..] > 文件路径

    案例: mysqldump -u -root root test > d:\temp.sql

  • 如果你希望备份是，数据库的某几张表
    mysqldump -u root -proot test dept > f:\temp.dept.sql

  • 如何使用备份文件恢复我们的数据.
    mysql控制台
    source d:\temp.dept.bak

（2）自动方式

把备份数据库的指令，写入到bat文件, 然后通过任务管理器去定时调用 bat文件.

mytask.bat 内容是:

@echo off

f:\xxx\mysqldump -u root -proot test dept > f:\temp.dept.sql

创建执行计划任务执行脚本。

数据库范式

（1）作用：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

（2）三大范式

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。

还有 BCNF。

数据库的第一范式，第二范式，第三范式，BCNF范式理解_sofency’Blog-CSDN博客_数据库第三范式

慢查询

（1）使用show status使用show status查看MySQL服务器状态信息，包括了慢查询的次数

（2）慢查询定义：MySQL默认10秒内没有响应SQL结果,则为慢查询。可以去修改MySQL慢查询默认时间。

  • 查询慢查询时间
    show variables like 'long_query_time';
    • 修改慢查询时间
      set long_query_time=1;但是重启mysql之后，long_query_time依然是my.ini中的值

（3）在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以，将慢查询定位到日志中。

SQL语句执行慢的原因

偶尔很慢

（1） 系统在刷新“脏页”中

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

  • redolog写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。

  • 内存不够用了: 如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。

（2）未获取到锁

要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了。

一直很慢

(1) 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2) 数据库选错了索引。

一条SQL语句的执行过程

MySQL数据库的高可用

MySQL数据库的高可用方案总结

文章作者: 小小千千
文章链接: https://jason-qianhao.github.io/2020/10/30/mysql-zhi-shi-hui-zong/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 小小千千 !
数据库 MySQL
赏
评论
  目录