编码人生

SQL 基础

Mr.WyjJanuary 20, 2023About 70 min

SQL 基础

数据库强推文open in new window

数据库基本操作

  • 创建数据库 CREATE DATABASE <数据库名>；
  • 删除数据库 DROP DATABASE <数据库名>;
  • 选择数据库 USE <数据库名>；
  • 创建索引，创建表的时候直接指定 CREATE INDEX index_name ON table_name(column_list)
  • 为表增加索引 ALTER TABLE table_name ADD INDEX index_name(column_list)
  • 创建数据表 CREATE TABLE [if not exists] table_name(column_name, column_type)
  • 删除数据表 DROP TABLE table_name
  • 插入数据 INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN )
  • 查询数据 SELETE column_name1,column_name2 FROM table_name [WHERE Clause][LIMIT N][OFFSET M]
  • where 语句 SELECT field1, field2,...fieldN FROM table_name [WHERE condition1 [AND [OR]] condition2.....
  • update 更新 UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
  • delete 语句 DELETE FROM table_name [WHERE Clause]
  • Like 子句 SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
  • union 操作符 UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据） UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据） SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
  • order by 排序 SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC升序]]
  • group by 分组 SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
  • inner join、left join、right join SELETE 表1数据，表2数据 FROM 表1 INNER JOIN/LEFT JIOIN/RIGHT JOIN 表2 ON 表1参数=表2参数

SQL 教程open in new window

JDBC 基础

数据库水平切分，垂直切分

表的垂直拆分和水平拆分open in new window

水平切分

称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当一个表中的数据逐渐增多时，Sharding 时必然的选择，它可以将数据分布到集群的不同结点上，从而缓解单个数据库的压力。适合具有超大表的系统。

拆分原则：

通常情况下，我们使用取模的方式来进行表的拆分。比如一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表users1，users2，users3，users4。通过用 id 取模的方法把数据分散到四张表内 id % 4 + 1 = [1,2,3,4]，然后查询、更新和删除也是通过取模的方法来查询。

$_GET['id'] = 17,
17%4 + 1 = 2,
$tableName = 'users'.'2'
Select * from users2 where id = 17;

在 insert 时还需要一张临时表 uid_temp 来提供自增的 id,该表的唯一用处就是提供自增的 id。

insert into uid_temp values(null);

得到自增的 id 后，又通过取模法进行分表插入；但是进行水平拆分后的表，字段的列和类型和原表应该是相同的，但是要记得去掉 auto_increment 自增长

垂直拆分

专库专用。一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。通常按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用得分列切分到不同的表中。适合各业务之间耦合度非常低的系统。

通常我们按以下原则进行垂直拆分:

  1. 把不常用的字段单独放在一张表;
  2. 把 text，blob 等大字段拆分出来放在附表中;
  3. 经常组合查询的列放在一张表中;

数据库两种引擎以及区别？介绍一下什么时候用 Innodb 什么时候用 MyISAM。为什么查询操作 MyISAM 比 Innodb 快？

B+Tree 索引

B+Tree 树、B 树参考文章open in new window

MyISAM 索引实现：

索引参考文章open in new window

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。

MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。

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

InnoDB 索引实现：

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在二个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先找到主键值，然后再到主索引中进行查找。

如何选择:

  1. 是否要支持事务，如果要请选择 innodb，如果不需要可以考虑 MyISAM；
  2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用 InnoDB
  3. 系统奔溃后，MyISAM 恢复起来更困难，能否接受；
  4. MySQL5.5 版本开始 Innodb 已经成为 Mysql 的默认引擎(之前是 MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用 InnoDB，至少不会差。

区别：

  1. 锁机制:MyISAM 是表级锁，而 InnoDB 是行级锁；
  2. 查询表的行数不同：MyISAM：select count( _ ) from table,MyISAM 只要简单的读出保存好的行数，注意的是，当 count( _ )语句包含 where 条件时，两种表的操作是一样的；InnoDB 中不保存表的具体行数，也就是说，执行 select count( * ) from table 时，InnoDB 要扫描一遍整个表来计算有多少行；
  3. 外键支持：mysiam 表不支持外键，而 InnoDB 支持；
  4. 事务处理：MyISAM 是非事务安全型的，而 InnoDB 是事务安全型的（支持事务处理等高级处理）；
  5. （3）select ,update ,insert ,delete：如果执行大量的 SELECT，MyISAM 是更好的选择 如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用 InnoDB 表。

为什么 MyISAM 会比 Innodb 的查询速度快：

NNODB 在做 SELECT 的时候，要维护的东西比 MYISAM 引擎多很多；

  1. 数据块，INNODB 要缓存，MYISAM 只缓存索引块， 这中间还有换进换出的减少；
  2. innodb 寻址要映射到块，再到行，MYISAM 记录的直接是文件的 OFFSET，定位比 INNODB 要快
  3. INNODB 还需要维护 MVCC 一致；虽然你的场景没有，但他还是需要去检查和维护 MVCC ( Multi-Version Concurrency Control )多版本并发控制

数据库索引介绍一下。主键和索引的区别？

  • 普通索引: 普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。
  • 唯一索引: MySQL 数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
  • 全文索引: 全文索引类型为 FULLTEXT，在定义索引的列上支持值的全文查找，用于查找文本中的关键词。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
  • 聚集索引: 表数据按照索引的顺序来存储的，当在表中插入一行数据时，这行数据的存放的顺序（物理顺序）和建立索引的键值的逻辑顺序相同。  一个表只能包含一个聚集索引。
  • 非聚簇索引: 表数据存储顺序与索引顺序无关，当在表中插入一行数据时，这行数据存放的顺序（物理顺序）在表的最后，不会影响数据表中原有的存储顺序。一个表中可以创建多个非聚集索引，一个表最多可以创建 249 个非聚集索引。
  • 联合索引: 将一张表中多个列组成联合索引(col1,col2,col3)，其生效方式满足最左前缀原则。
  • 哈希索引: 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。 哈希索引可以以 O(1)时间进行查找，但是失去了有序性； InnoDB 存储引擎有一个特殊的功能叫"自适应哈希索引"，当某个索引值使用的非常频繁时，会在 B+Tree 索引上在创建一个哈希索引，这就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找
  • 空间数据索引: 空间索引是对空间数据类型的字段建立的索引。
  • 聚集索引和非聚簇索引区别与联系： 聚簇索引和非聚簇索引都是用 B+树实现的。但是聚簇索引中叶子节点存储的是一条完整的记录数据，非聚簇索引的叶子节点中存储的是指向主键的引用。
  • 主键和索引的区别：
  1. 主键一定是唯一性索引，唯一性索引并不一定就是主键
  2. 一个表中可以有多个唯一性索引，但只能有一个主键
  3. 主键列不允许空值，而唯一性索引列允许空值
  4. 主键可以被其他字段作外键引用，而索引不能作为外键引用

索引参考文章 1open in new window

索引参考文章 2open in new window

数据库强推文open in new window

索引了解嘛，底层怎么实现的，什么时候会失效，索引使用的注意点

  • B+树实现的。没有遵循最左匹配原则。 一些关键字会导致索引失效，例如  or，!=，not in，is null，is not null，like 查询是以%开头，隐式转换会导致索引失效。 对索引应用内部函数，索引字段进行了运算。

  • 索引使用注意点：

  1. 一般说来，索引应建立在那些将用于 JOIN,WHERE 判断和 ORDER BY 排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引
  2. 最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库
  3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
  4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
  5. in 和 not in 也要慎用，否则会导致全表扫描
  6. like %keyword 索引失效，使用全表扫描
  7. 如果在 where 子句中使用参数，也会导致全表扫描
  8. 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
  9. 在使用索引字段作为条件时，如果该索引是复合索引（多列索引），那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
  • 建立索引的原则：
  1. 定义有主键的数据列一定要建立索引。因为主键可以加速定位到表中的某一行
  2. 定义有外键的数据列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接
  3. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  4. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  5. 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度
  6. 在经常需要搜索的列上，可以加快搜索的速度

最左匹配原则介绍open in new window

索引使用注意open in new window

数据库的隔离级别

隔离级别：

  • 未提交读(Read Uncommitted) 事务中的修改，即使没有提交，对其它事务也是可见的。读取未提交的数据，也被称之为脏读。
  • 提交读(Read Committed) 一个事务只能读取已经提交的事务所做的修改，即一个事务所做的修改在提交之前对其它事务是不可见的。已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题。即事务 A 在两次查询的数据不一致，因为在两次查询之间事务 B 更新并提交了一条数据。
  • 可重复读(repeatable read) 保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可串行化(Serializable) 强制事务串行执行。可串行化完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

数据库的三范式？

第一范式(确保每列保持原子性)

数据库表中的所有字段都是单一属性，不可在分的，这个单一属性是由基本的数据类型所构成，如整数，浮点数，字符串等

数据库表的每一列都是不可分割的基本数据项，所有字段值都是不可分解的原子值

第二范式(确保表中的每列都和主键相关)

要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)

要求实体的属性完全依赖于主关键字。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式(确保每列都和主键列直接相关，而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

讲一下数据库 ACID 的特性？

  1. 原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚，回滚可以用回滚日志来实现

  2. 一致性：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。如果事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前的状态。

    一致性是指事务必须使数据库从一个一致性状态变成另一个一致性状态，也就是事务执行前后必须处于一致性状态。

    以转账为例，假设用户 A 和 B 两者的钱加起来是 5000，那么不管 A 和 B 之间如何转账，转多少次，事务结束后两个用户的钱加起来应该还得是 5000，这就是事务的一致性。

  3. 隔离性：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

  4. 持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能

mysql 主从复制？

  • 主从复制：指数据从一个数据库服务器的主节点上复制到其他一个或多个从节点的数据库服务器。MySql 数据库默认采用异步复制方式。从节点数据库不用一直访问主服务器也可以实现更新数据。 MySQL 的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的 binlog 日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的 SQL 语句重新应用到 MySQL 数据库中。 主要设计三个线程：

    1. binlog 线程：负责将主服务器上的数据更改写入二进制（Binary log）中；
    2. I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的重放（Replay log）中；
    3. SQL 线程：负责读取重放日志并重放其中的 SQL 语句

  • 读写分离：主服务器处理写操作以及实时性要求比较高的读操作，而从服务器负责读操作，读写分离提高性能的原因在于：

    1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
    2. 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
    3. 增加冗余，提高可用性 读写分离常用代理方式来实现，代理服务器接受来自应用层传来的读写请求，然后决定转发到拿个服务器。

主从复制参考文章open in new window

leftjoin 和 rightjoin 的区别？

left join(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录；right join(右联接)返回包括右表中的所有记录和左表中联结字段相等的记录。 left 保留左表的值，右表无值填 null，right 相反

leftjoin 和 rightjoin 参考文章open in new window

数据库优化方法

  • 语句优化 要提高 MySQL 的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间
  1. insert 语句
    1. 批量插入数据的时候，同时插入多条
    2. 将进程/线程数控制在 2 倍于 CPU 数目相对合适
    3. 采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）
    4. 考虑使用 replace 语句代替 insert 语句。
  2. delete 语句
    1. 如果不考虑数据的恢复，可使用 truncate table 语句删除数据，删除了不记录 mysql 日志文件
    2. 如果没有外键关联，innodb 执行 truncate 是先 drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于 delete 逐条删除行记录。如果表有外键关联，truncate table 删除表数据为逐行删除
    3. 使用optimize tableopen in new window来优化表。truncate table 删除表后，optimize table 尤其重要，特别是大数据数据库，表空间可以得到释放
  3. update 语句
    1. 尽量不要修改主键字段
    2. 当修改 VARCHAR 型字段时，尽量使用相同长度内容的值代替
    3. 尽量最小化对于含有 UPDATE 触发器的表的 UPDATE 操作
    4. 避免 UPDATE 将要复制到其他数据库的列
    5. 避免 UPDATE 建有很多索引的列。
    6. 避免 UPDATE 在 WHERE 子句条件中的列
  4. replace 语句: 根据应用情况可以使用 replace 语句代替 insert/update 语句： 使用 REPLACE 插入记录时，如果记录不重复（或往表里插新记录），REPLACE 功能与 INSERT 一样，如果存在重复记录，REPLACE 就使用新记录的值来替换原来的记录值，而 UPDATE 什么都不做。使用 REPLACE 的最大好处就是可以将 DELETE 和 INSERT 合二为一，形成一个原子操作。这样就可以不必考虑同时使用 DELETE 和 INSERT 时添加事务等复杂操作了。
  • 查找优化
  1. 是否向数据库请求了不需要的数据。也就是说不要轻易使用 select * from ，能明确多少数据就查多少个
  2. mysql 是否扫描额外的纪录。查询是否扫描了过多的数据。最简单的衡量查询开销三个指标如下：响应时间；扫描的行数；返回的行数。这三个指标都会记录到 mysql 的慢日志open in new window中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。 如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
    1. 使用索引覆盖扫描
    2. 改变库表结构。例如使用单独的汇总表。
    3. 重写这个复杂的查询。让 mysql 优化器能够以更优化的方式执行这个查询。
  3. 设计查询的时候考虑是否需要将一个复杂的查询分成多个简单的查询。分解关联查询让缓存的效率更高。
  4. 单表查询步步优化：
    1. 明确需要的字段，要多少就写多少字段
    2. 使用分页语句：limit start , count 或者条件 where 子句
    3. 如果是有序的查询，可使用 ORDER BY
    4. 开启查询缓存
    5. 使用索引
  5. 多表查询步步优化：
    1. 对查询进行优化，要尽量避免全表扫描
    2. 应尽量避免在 where 子句中对字段进行 null 值判断
    3. in 和 not in 也要慎用，否则会导致全表扫描
    4. 任何地方都不要使用 select _ from t ，用具体的字段列表代替 _
    5. 在 Join 表的时候使用相同类型的例，并将其索引
    6. 尽量使用数字型字段，只含数值信息的字段尽量不要设计为字符型
  • 索引优化
    1. 最左前缀匹配原则
    2. =和 in 可以乱序
    3. 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count( * )
    4. 索引列不能参与计算，保持列“干净”
    5. 尽量的扩展索引，不要新建索引
    6. 定义有外键的数据列一定要建立索引
    7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
    8. 对于定义为 text、image 和 bit 的数据类型的列不要建立索引
    9. 对于经常存取的列避免建立索引
  • 表的优化
  1. 单表设计与优化
    1. 设计规范化表，消除数据冗余
    2. 适当的冗余，增加计算列，在数据冗余和处理速度之间找到合适的平衡点
    3. 索引的设计
    4. 主键和外键的必要性
    5. 存储过程、视图、函数的适当使用，有助于减少应用程序中 SQL 复制的弊端
    6. 分割你的表，减小表尺寸
    7. 字段设计原则：数据类型尽量用数字型不用字符型、数据类型尽量小、尽量不要允许 NULL，除非必要，可以用 NOT NULL+DEFAULT 代替、少用 TEXT 和 IMAGE，二进制字段的读写是比较慢的、自增字段要慎用，不利于数据迁移
  2. 基于单表设计的多表设计原则
    1. 建立合适的表关系：一对一、一对多、多对一、多对多
    2. 适当的表拆分：水平拆分、垂直拆分

数据库强推文open in new window

慢日志+explain 查询分析 SQL 语句进行优化

通过开启慢日志功能来优化查询语句：

步骤：

  1. 查看慢查询日志是否开启：show variables like '%slow_query_log%'

     mysql> show variables  like '%slow_query_log%';

 +---------------------+----------------------------------------+
 | Variable_name       | Value                                  |
 +---------------------+----------------------------------------+
 | slow_query_log      | ON                                     |
 | slow_query_log_file | /usr/local/mysql/var/huosuSDK-slow.log |
 +---------------------+----------------------------------------+
 2 rows in set (0.00 sec)

  2. 开启慢日志：set global slow_query_log = 1

  3. 查看慢查询日志阙值：show global variables like '%long_query_time%'

  4. 设置慢查询日志阙值：set global long_query_time = 3，超出次设定值的 SQL 即被记录到慢查询日志，缺省值为 10s

  5. 查看多少 SQL 语句超过了阙值：show global status like '%Slow_queries%'

MySQL 提供的日志分析工具 mysqldumpslow，该工具是慢查询自带的分析慢查询工具，可以得到返回次数最多、访问次数最多的 SQL 语句等功能

c : 访问计数
l : 锁定时间
r : 返回记录
t : 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间

-t， 是 top n 的意思，即为返回前面多少条的数据；-s 是表示按照何种方式排序，常见用法：


mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log # 得到访问次数最多的 10 个 SQL

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log # 得到返回记录集最多的 10 个 SQL

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log # 得到按照时间排序的前 10 条里面含有左连接的查询语句

慢日志open in new window

慢日志+explain 查询分析open in new window

使用 EXPLAIN 关键字可以分析 SELECT 查询语句，通过分析 EXPLAIN 结果来优化查询语句。比较重要的字段：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
  1. select_type：查询中每个 select 子句的类型，有简单查询、联合查询、子查询
  2. key：实际使用的索引
  3. rows：扫描的行数
  4. type：显示查询使用了何种类型。从最好到最差的连接类型为 NULL、system、const、eq_ref、ref、range、index 和 ALL，得保证查询至少达到 range 级别，最好能达到 ref。
    1. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配；
    2. ref：非唯一性索引扫描，返回匹配某个单独值的所有行；
    3. range：只检索给定范围的行，使用一个索引来选择行；

当我们优化了一条 SQL 语句时后，可以通过 show profiles 看到其执行时间和效率。默认的是关闭的，可以通过 SET profiling = 1; 开启，之后运行一个查询语句之后使用 show profiles 进行查看。

为了防止 MySQL 缓存对查询结果的影响，我们可以临时关闭缓存，使用命令：

set global query_cache_size=0;
set global query_cache_type=0;

query_cache_size 表示缓存的大小，query_cache_type 表示缓存那种类型的 select 结果集。之后可以查看缓存是否关闭：

show variables like '%query_cache%';

MYSQL 中使用 SHOW PROFILE 命令分析性能的用法整理open in new window

Mysql 查询缓存open in new window

MySQL 字段类型

整型

在 MySQL 中支持的 5 个主要整数类型是 tinyint，smallint，mediumint，int 和 bigint。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的

数值数据类型要比字符串执行更快，尤其是在比较运算时，所以我们应该选择最简单的数据类型。区间小的数据类型占用空间更少，处理速度更快，如 tinyint 比 bigint 要快的多

整数类型很多比如 tinyint、int、smallint、bigint 等，那么我们要根据自己需要存储的数据长度决定使用的类型

为什么 MySQL 存储的值要分有符号和无符号呢？因为一个字节占 8bit，也就 1 个 bit 有 0 和 1 两种可能，8 个 bit 就是 2^8 = 256 种可能，也就是 0~255; 但如果是有符号的话，就得拿一个 1bit 来存储这个负号，本来 8bit 只剩 7bit，2^7 = 128，也就是 -128~127(正数部分包含一个 0);

浮点型

MySQL 支持的三个浮点类型是 float、double 和 decimal 类型。float 数值类型用于表示单精度浮点数值，而 double 数值类型用于表示双精度浮点数值。一般不会使用 double 类型

浮点数 float 在储存空间及运行效率上要优于精度数值类型 decimal，但 float 与 double 会有舍入错误而 decimal 则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。

如何选择：

  1. 如果你要表示的浮点型数据转成二进制之后能被 32 位 float 存储，或者可以容忍截断，则使用 float，这个范围大概为要精确保存 6 位数字左右的浮点型数据；比如 10 分制的店铺积分可以用 float 存储，小商品零售价格(1000 块之内)
  2. 如果你要表示的浮点型数据转成二进制之后能被 64 位 double 存储，或者可以容忍截断，这个范围大致要精确到保存 13 位数字左右的浮点型数据。比如汽车价格，几千万的工程造价
  3. 相比 double，已经满足我们大部分浮点型数据的存储精度要求，如果还要精益求精，则使用 decimal 定点型存储

MySQL 如何选择 float, double, decimalopen in new window

字符串类型

MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。一个英文占用 1 个字节，一个中文占用 2 个字节。

  • char 与 varchar

    char 固定长度的字符串保存类型，会去掉尾部的空格。在数据长度相近时使用 char 类型比较合适，比如 md5 加密的密码用户名等。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。char 范围大小为 0-255，varchar 并且都必须带有一个范围在 0-255 之间的指示器。

    varchar 是可变长度字符串类型，但他要在总长度上加 1 个字符，这个用来存储位置。由于 varchar 是根据储存的值来保存数据，所以可以大大节约磁盘空间，提高运行效率。

    如果数据经常进行更新修改操作，那么 char 更好些，因为 char 长度固定，性能上要快。如果数据经常被执行更新操作，由于 varchar 是根据内容来进行储存的，所以 MySQL 将做更多的工作来完成更新操作，如果新数据长度大于老数据长度一些存储引擎会进行拆分操作处理，因此性能不高。

    MyISAM 数据表，最好使用 char 固定长度的数据列代替可变长度的数据列。InnoDB 存储引擎：建议使用 varchar 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 char 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 char 平均占用的空间多于 varchar，因此使用 varchar 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

  • text 与 blob 对于字段长度要求超过 255 个的情况下，MySQL 提供了 text 和 blob 两种类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

    目前，varchar 也可以存储和 text 相同大小的数据，也就是 65535(64k) 个字节，二者区别：

    1. text 字段，MySQL 不允许有默认值。建立索引必须给出前缀索引长度
    2. varchar 允许有默认值，对索引长度没限制
    3. 根据存储的实现:可以考虑用 varchar 替代 text，因为 varchar 存储更弹性，存储数据少的话性能更高
    4. 如果需要非空的默认值，就必须使用 varchar
    5. 如果存储的数据大于 64K，就必须使用到 mediumtext， longtext，因为 varchar 已经存不下了
    6. 如果 varchar(255+) 之后，和 text 在存储机制是一样的，性能也相差无几
    7. 注意 varchar(255) 不只是 255byte，实质上有可能占用的更多

mysql 的 varchar 与 text 对比open in new window

时间类型

  • date、time 和 year

    MySQL 用 date 和 year 类型存储简单的日期值，使用 time 类型存储时间值。

  • datetime 和 timestamp

    • 二者可以把日期和时间作为单个的值进行存储。
    • timestamp 只占 4 个字节，datetime 以 8 个字节储存
    • 如果存进去的是 NULL，timestamp 会自动储存当前时间，而 datetime 会储存 NULL
    • 如果在时间上要超过 Linux 时间的，或者服务器时区不一样的就建议选择 datetime。
    • 如果是想要使用自动插入时间或者自动更新时间功能的，可以使用 timestamp。

MySQL 中 datetime 和 timestamp 的区别与选择open in new window

字段总结

  1. 最常用也就是 varchar(255)，char(255)，text，tinyint(4)，smallint(6)，mediumint，int(11)几种。
  2. 复合类型我们一般用 tinyint，更快的时间更省的空间以及更容易扩展
  3. 选择合理范围内最小的，选择相对简单的数据类型
  4. 不要使用 null。因为 MySQL 对 null 字段索引优化不佳，增加更多的计算难度，同时在保存与处理 null 类形时，也会做更多的工作，所以从效率上来说，不建议用过多的 null。有些值他确实有可能没有值，怎么办呢？解决方法是数值用整数 0，字符串用空来定义默认值即可。
  5. 保存数值类型最好不要用字符串数据类型。这样存储的空间显然是会更大，而且在排序时字符串的 9 是大于 22 的，其实如果进行运算时 MySQL 会将字符串转换为数值类型，大大降低效果，而且这种转换是不会走原有的索引的
  6. 字符串数据类型是一个万能数据类型，可以储存数值、字符串、日期等。
  7. 关于手机号，推荐用 char(11)，char(11)在查询上更有效率，因为手机号是一个活跃字段参与逻辑会很多。

常用字段举例

  • 姓名：char(20)
  • 价格：DECIMAL(7, 3)
  • 产品序列号：SMALLINT(5) unsigned
  • 文章内容: TEXT
  • MD5: CHAR(32)
  • ip: char(15)
  • time: int(10)
  • email char(32)

MySQL 对于大字段的存储

Compact 行格式：

Compact 格式没有将大数据全部放在数据页中，而是将一部分数据放在了外部存储页中，数据页存储 768 个字节

Dynamic 行格式：

MySQL5.1 后引入新的文件格式：Dynamic 和 Compressed，数据页只存储 20 字节，其余的都放在溢出段中

每个 innoDB 表中最好有一个自增列作为主键，使得新记录写入尽可能是顺序的。innoDB 数据如果是顺序写入的话，最理想的情况下，data page 的填充率是 15/16，一般是在 1/2 到 15/16 之间

text blob varchar 很长，如何存储以及优化

  1. 如果有多个大字段，尽量序列化，压缩之后存储在同一列中，避免多次 off-page
  2. 将大字段从主表中拆分出来
    1. 存储到键值中
    2. 存储在单独的一张字表中，压缩并且必须保证一行记录小于 8K

数据库架构

谈一下你对继承映射的理解。

在面向对象的程序领域中，类与类之间是有继承关系的，但是在关系数据库的世界中，表与表之间没有任何关键字可以明确指明这两张表的父子关系，表与表是没有继承关系这样的说法的。为了将程序领域中的继承关系反映到数据中，Hibernate 为我们提供了 3 中方案： 继承关系的映射策略有三种：

  1. 每个继承结构一张表，不管多少个子类都用一张表。
  2. 每个子类一张表，公共信息放一张表，特有信息放单独的表。
  3. 每个具体类一张表，有多少个子类就有多少张表。

第一种方式属于单表策略，其优点在于查询子类对象的时候无需表连接，查询速度快，适合多态查询；缺点是可能导致表很大。后两种方式属于多表策略，其优点在于数据存储紧凑，其缺点是需要进行连接查询，不适合多态查询。

说出数据连接池的工作机制是什么?

  1. 在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数；
  2. 当应用程序向连接池请求的连接数超过最大连接数量时,这些请求将被加入到等待队列中。

JDBC 中如何进行事务处理？

Connection 的三个方法与事务有关：

  1. setAutoCommit（boolean）：设置是否为自动提交事务，如果 true（默认值为 true）表示自动提交，也就是每条执行的 SQL 语句都是一个单独的事务，如果设置为 false，那么相当于开启了事务了；con.setAutoCommit(false) 表示开启事务。
  2. commit（）：提交结束事务。
  3. rollback（）：回滚结束事务。

SQL 注入？

  • 概念： 所谓 SQL 注入，就是通过把 SQL 命令插入到 Web 表单或输入域名或页面请求的查询字符串中，最终达到欺骗服务器执行恶意的 SQL 命令。具体来说，它是利用现有应用程序，将（恶意的）SQL 命令注入到后台数据库引擎执行的能力，它可以通过在 Web 表单中输入（恶意）SQL 语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行 SQL 语句。通过 SQL 语句，实现无帐号登录，甚至篡改数据库。

  • 分类： SQL 注入可以分为平台层注入和代码层注入。前者由不安全的数据库配置或数据库平台的漏洞所致；后者主要是由于程序员对输入未进行细致地过滤，从而执行了非法的数据查询

  • 举例： 比如在一个登录界面，如下输入可实现免帐号和密码登录：

    | 用户名      | 密码 |
| ----------- | ---- |
| ‘or 1 = 1-- |

    点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了，why?

    从理论上说，后台认证程序中会有如下的 SQL 语句： "SELECT _ FROM user WHERE username=' "+username+" ' AND password=' "+password+" ' "; 当输入了上面的用户名和密码，上面的 SQL 语句变成： SELECT _ FROM user WHERE username=' ' or 1=1 -- AND password=' ';

    分析：Where 语句后面的 username=' ' or 1=1 这个条件一定会成功；然后后面的--是注释符，它将后面的语句注释，所以语句永远都能正确执行，用户轻易骗过系统，获取合法身份。 如果是执行：SELECT * FROM user_table WHERE  username=' ' ;DROP DATABASE (DB Name) --' and password=' '将导致数据库被删除。

  • 解决方法：

  1. （简单又有效的方法）PreparedStatement 采用预编译语句集，它内置了处理 SQL 注入的能力，只要使用它的 setXXX 方法传值即可。 原理：sql 注入只对 sql 语句的准备(编译)过程有破坏作用，而 PreparedStatement 已经准备好了，执行阶段只是把输入串作为数据处理，而不再对 sql 语句进行解析,准备,因此也就避免了 sql 注入问题.
  2. 使用正则表达式过滤传入的参数
  3. 字符串过滤
  4. jsp 中调用该函数检查是否包函非法字符，防止 SQL 从 URL 注入
  5. JSP 页面判断代码，使用 javascript 在客户端进行不安全字符屏蔽

索引为什么能查的快？为什么而需要索引？索引的优缺点？

使用索引原因：

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID（相当于页码）快速找到表中对应的记录。

优点：

  1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

  1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引的底层使用什么实现的，为什么不用 B 树或者红黑树？

底层实现： B+树只有叶节点存放数据，其余节点用来索引，而 B-树是每个索引节点都会有 Data 域。Mysql 通过磁盘 IO 次数来衡量查询效率

B-树/B+树的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘 IO 次数，但是 B-树的每个节点都有 data 域（指针），这无疑增大了节点大小，说白了增加了磁盘 IO 次数（磁盘 IO 一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO 次数增多，一次 IO 多耗时），而 B+树除了叶子节点其它节点并不存储数据，节点小，磁盘 IO 次数就少。这是优点之一。

另一个优点是： B+树所有的 Data 域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的遍历操作。

AVL 树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘 IO 代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘 IO 频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B 树可以有多个子女，从几十到上千，可以降低树的高度

redo？undo？checkpoint？

参考open in new window

数据库数据存放的文件称为 data file；日志文件称为 log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理 disk，那性能就太低下了。数据库数据的缓存称为 data buffer，日志（redo）缓存称为 log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。

  • undo undo 日志用于记录事务开始前的状态，用于事务失败时的回滚操作；
  • redo redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已成功事务更新的数据。
  • 举例 例如某一事务的事务序号为 T1，其对数据 X 进行修改，设 X 的原值是 5，修改后的值为 15，那么 Undo 日志为<T1, X, 5>，Redo 日志为<T1, X, 15>

梳理下事务执行的各个阶段：

  1. 写 undo 日志到 log buffer；
  2. 执行事务，并写 redo 日志到 log buffer；
  3. 如果 innodb_flush_log_at_trx_commit=1，则将 redo 日志写到 log file，并刷新落盘。
  4. 提交事务。

  • 为什么没有写 data file，事务就提交了？ 因为 data buffer 中的数据会在合适的时间由存储引擎写入到 data file，如果在写入之前，数据库宕机了，根据落盘的 redo 日志，完全可以将事务更改的数据恢复。先持久化日志的策略叫做 Write Ahead Log，即预写日志。

  • checkpoint checkpoint 是为了定期将 db buffer 的内容刷新到 data file。当遇到内存不足、db buffer 已满等情况时，需要将 db buffer 中的内容/部分内容（特别是脏数据）转储到 data file 中。在转储时，会记录 checkpoint 发生的”时刻“。在故障回复时候，只需要 redo/undo 最近的一次 checkpoint 之后的操作。

mysql 写数据的时候,需要先将数据写到 buffer 里,再写到磁盘里,万一 MySQL 这时候突然挂了,怎么办?

根据 checkpoint 点执行数据库 redo、undo 操作

where 和 having by 区别

  • Where 是一个约束声明，是在结果返回之前起作用的，且 Where 中不能使用聚合函数
  • Having 是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在 Having 中可以使用聚合函数

树总结

共同点：

  1. 都是动态结构。在删除，插入操作的时候，都不需要彻底重建原始的索引树。最多就是执行一定量的旋转，变色操作来有限的改变树的形态。而这些操作所付出的代价都远远小于重建一棵树
  2. 查找的时间复杂度大体维持在 O(log(N))数量级上。可能有些结构在最差的情况下效率将会下降很快，比如 BST

二叉查找树(BST)：

  1. 性质：
    1. 若左子树不空，则左子树上所有结点的值均小于它的根结点的值；
    2. 若右子树不空，则右子树上所有结点的值均大于它的根结点的值；
    3. 左、右子树也分别为二叉排序树；
    4. 没有键值相等的节点
  2. 效率总结：
    1. 查找最好时间复杂度 O(logN)，最坏时间复杂度 O(N)；
    2. 插入删除操作算法简单，时间复杂度与查找差不多。
  3. 缺点：最差情况下竟然和顺序查找效率相当

平衡二叉查找树(AVL)：

  1. 概念：

    它是一棵空树或它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树。

  2. 效率总结：

    1. 查找的时间复杂度维持在 O(logN)，不会出现最差情况
    2. AVL 树在执行每个插入操作时最多需要 1 次旋转，其时间复杂度在 O(logN)左右。
    3. AVL 树在执行删除时代价稍大，执行每个删除操作的时间复杂度需要 O(2logN)。

  3. 缺点：

    1. 二叉平衡树的严格平衡策略以牺牲建立查找结构(插入，删除操作)的代价，换来了稳定的 O(logN)的查找时间复杂度;
    2. 数据库中的数据基本都是放在磁盘中的，每读取一个二叉树的结点就是一次磁盘 IO，性能就成了一个很大的问题了

红黑树(RBT)：

  1. 效率总结：
    1. 查找效率最好情况下时间复杂度为 O(logN)，但在最坏情况下比 AVL 要差一些，但也远远好于 BST，这是由于红黑树的性质最长路径长度不超过最短路径长度的 2 倍决定的。
    2. 插入和删除操作改变树的平衡性的概率要远远小于 AVL（RBT 不是高度平衡的）。因此需要的旋转操作的可能性要小，而且一旦需要旋转，插入一个结点最多只需要旋转 2 次，删除最多只需要旋转 3 次(小于 AVL 的删除操作所需要的旋转次数)。虽然变色操作的时间复杂度在 O(logN)，但是实际上，这种操作由于简单所需要的代价很小。
  2. 缺点：大量数据存储中，实现查询这样一个实际背景下，由于树深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下

B-树（B 树）：

  1. 性质(m 叉树)：

    1. 树中每个结点至多有 m 个孩子；
    2. 除根结点和叶子结点外，其它每个结点至少有[m/2]个孩子；
    3. 若根结点不是叶子结点，则至少有 2 个孩子；
    4. 所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息；
    5. 每个非终端结点中包含有 n 个关键字信息： (n，A0，K1，A1，K2，A2，......，Kn，An)。其中， a) Ki (i=1...n)为关键字，且关键字按顺序排序 Ki < K(i-1)；  b) Ai 为指向子树根的接点，且指针 A(i-1)指向子树种所有结点的关键字均小于 Ki，但都大于 K(i-1)； c) 关键字的个数 n 必须满足：[m/2]-1 <= n <= m-1

  2. B 树的查找：

    与二叉搜索树相似，但不是两路分支。他的查找分为两个基本的操作。

    1. 在 B 树中找结点；
    2. 在结点内找关键字。由于 B 树常存储在磁盘中，则前一个查找是在磁盘中进行的，而后一个查找是在内存中进行的，即找到目标结点后，将其读入内存，然后采用顺序或折半查找法查找等于 k 的关键字。这是一个内存与外存的交叉过程。

  3. 效率总结： 由于考虑磁盘储存结构，B 树的查找、删除、插入的代价都远远要小于任何二叉结构树(读写磁盘次数的降低)

  4. 缺点：在 B 数中我们不经要遍历节点，还要对每个节点中的关键字进行遍历，这样非常的糟糕，所以引入了 B+树

B+树：

  1. 性质： 其定义基本与 B-树同，除了：
    1. 有 n 棵子树的结点中含有 n 个关键字；(B 树是 n 棵子树有 n+1 个关键字)
    2. 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接
  2. 与 B 树比较：
    1. B+树比 B 树更适合实际应用中操作系统的文件索引和数据库索引原因：B+树的磁盘读写代价更低；B+树的查询效率更加稳定
    2. B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+树更加适合在区间查询的情况，所以通常 B+树用于数据库索引，而 B 树则常用于文件索引。B 树由于 B 树的每一个节点都包含 key 和 value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

B+、B-树对比

  1. B-Tree 因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而 B+Tree 所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的 B-Tree 和 B+Tree 中，B-Tree 查找某个关键字的效率更高
  2. 由于 B+Tree 所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree 只需要找到该关键字然后沿着链表遍历就可以了，而 B-Tree 还需要遍历该关键字结点的根结点去搜索。
  3. 由于 B-Tree 的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而 B+Tree 非叶子结点只存储关键字信息，而每个页的大小是有限的，所以同一页能存储的 B-Tree 的数据会比 B+Tree 存储的更少。这样同样总量的数据，B-Tree 的深度会更大，增大查询时的磁盘 I/O 次数，进而影响查询效率。

不懂数据库索引的底层原理？那是因为你心里没点 b 树open in new window

各种树概念open in new window

动态查找树比较open in new window

B+、B-树open in new window

JDBC 进阶

JDBC 的反射，反射都是什么？

Jdo 是什么?

Statement 和 PreparedStatement 有什么区别？哪个性能更好？

使用 JDBC 操作数据库时，如何提升读取数据的性能？如何提升更新数据的性能？

数据库优化扩展

我以为我对 Mysql 索引很了解，直到我遇到了阿里的面试官open in new window

索引

概念：索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据

索引数据结构：Hash 索引和 B+ Tree 索引。InnoDB 引擎，默认的是 B+树

B+Tree 索引和 Hash 索引区别

  • 哈希索引适合等值查询，但是无法进行范围查询。因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。
  • 哈希索引不适合范围查询检索。因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了
  • 哈希索引没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）
  • 哈希索引不支持多列联合索引的最左匹配规则
  • 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

非主键索引查询数据时是否每次需要回表查询多次，覆盖索引的概念

不是的，通过覆盖索引也可以只查询一次

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

当一条查询语句符合覆盖索引条件时，MySQL 只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少 I/O 提高效率。

如，表 covering_index_sample 中有一个普通索引 idx_key1_key2(key1,key2)。 当我们通过 SQL 语句：select key2 from covering_index_sample where key1 = 'keytest' 的时候，就可以通过覆盖索引查询，无需回表。

联合索引多个字段顺序如何选择

在创建多列索引时，我们根据业务需求，where 子句中使用最频繁的一列放在最左边。

因为 MySQL 索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如 (key1,key2,key3)，相当于创建了(key1)、(key1,key2) 和 (key1,key2,key3) 三个索引，这就是最左匹配原则。

线上 MySQL 版本，MySQL5.6 中索引的优化索引下推(ICP)原理

概念：是 MySQL 中一个常用的优化，尤其是当 MySQL 需要从一张表里检索数据时。 如果没有 ICP，存储引擎将会根据 WHERE 子句的条件遍历整个表单数据，然后返回给 MySQL 服务器。启用 ICP，如果可以通过使用索引的列来满足 WHERE 条件，MySQL 服务器将 WHERE 条件的这部分推送到存储引擎。然后，存储引擎通过使用索引来确定推送的条件，并且通过这样的方式从表中读取行。

举例：

people 表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则 MySQL 会通过 zipcode='95054' 从存储引擎中查询对应的数据，返回到 MySQL 服务端，然后 MySQL 服务端基于 LIKE 条件来判断数据是否符合条件。

如果使用了索引下推技术，则 MySQL 首先会返回符合 zipcode='95054' 的索引，然后根据 lastname LIKE '%etrunia%'和 address LIKE '%Main Street%' 来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接拒绝掉。有了索引下推优化，可以在有 LIKE 条件查询的情况下，减少回表次数。

查询优化器

一条 SQL 语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案的代价，找出成本最低的那一个

MySQL 锁

共享锁(S 锁)和排他锁(X 锁)

浅谈 MySQL 的七种锁open in new window

MySQL 中 InnoDB 上的锁分类open in new window

  • 事务拿到某一行记录的共享 S 锁，才可以读取这一行，并阻止别的事物对其添加 X 锁
  • 事务拿到某一行记录的排它 X 锁，才可以修改或者删除这一行
  • 共享锁的目的是提高读读并发
  • 排他锁的目的是为了保证数据的一致性

意向锁

意向共享锁(IS 锁)：表示一个事务倾向于对表中的部分行设置共享锁

意向排他锁(IX 锁)：表示一个事务倾向于对表中的部分行设置排它锁

意向锁意义

  • IX，IS 是表级锁，不会和行级的 X，S 锁发生冲突。只会和表级的 X，S 发生冲突
  • 意向锁是在添加行锁之前添加。
  • 如果没有意向锁，当向一个表添加表级 X 锁时，就需要遍历整张表来判断是否存行锁，以免发生冲突
  • 如果有了意向锁，只需要判断该意向锁与表级锁是否兼容即可。

例如，SELECT ... FOR SHARE 会设置 IS 锁，SELECT ... FOR UPDATE 会设置 IX 锁

意向锁的协议如下：

  • 在事务可以获取表中某行的共享锁之前，必须先在表上获取 IS 锁，或表上更强的锁
  • 在事务可以获取表中某些行的排它锁之前，必须先在表上获取 IX 锁

兼容性：

兼容性ISIXSX
IS兼容兼容兼容互斥
IX兼容兼容互斥互斥
S兼容互斥互斥互斥
X互斥互斥互斥互斥

插入意向锁

插入意向锁是间隙锁的一种，针对 insert 操作产生。目的是提高插入并发。

多个事物，在同一个索引，同一个范围区间进行插入记录的时候，如果插入的位置不冲突，不会阻塞彼此。

t1(id primary key,id1 int)

mysql> select * from t1;
+----+------+
| id | id1  |
+----+------+
| 10 |   10 |
| 20 |   20 |
| 30 |   30 |
+----+------+
3 rows in set (0.00 sec)

mysql> start transaction;                             mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)                  Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(11,11);                  mysql> insert into t1 values(12,12);
Query OK, 1 row affected (0.00 sec)                   Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;                              mysql> select * from t1;
+----+------+                                         +----+------+
| id | id1  |                                         | id | id1  |
+----+------+                                         +----+------+
| 10 |   10 |                                         | 10 |   10 |
| 11 |   11 |                                         | 12 |   12 |
| 20 |   20 |                                         | 20 |   20 |
| 30 |   30 |                                         | 30 |   30 |
+----+------+                                         +----+------+
4 rows in set (0.00 sec)                              4 rows in set (0.00 sec)

记录锁

是对索引记录的锁定。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 会阻止其他事务的插入、更新以及删除 t 表中 c1 = 10 的行。即使表中没有任何索引，MySQL 会自动创建一个隐式的 row_id 作为聚集索引来进行加锁。

间隙锁(gap 锁)

间隙锁，锁定的是索引记录之间的间隙，或是第一个索引之前以及最后一个索引之后的间隙，如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 会阻止其他事务将值 15 插入到 t.c1 中，无论该列是否存在任何这样的值，因为该范围内的任何存在的值都会被锁定。

间隙锁主要出现在 RR(可重复读) 隔离级别，避免出现幻读。

针对当前读，RR 隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

临键锁(Next-Key Locks)

临键锁是记录锁和间隙锁的组合，既锁住了记录也锁住了范围。临键锁的主要目的，也是为了避免幻读。如果把事务的隔离级别降级为 RC，临键锁就也会失效。

通常情况下，InnoDB 在搜索或扫描索引的行锁机制中使用临键锁(next-key locking)算法来锁定某索引记录及其前部的间隙(gap)，以阻塞其它用户紧跟在该索引记录之前插入其它索引记录。

DML 和 DDL 语句

DML：数据操纵语句，SELECT、INSERT、UPDATE、DELETE；

DDL：数据定义语言，CREATE、DROP、ALTER

DCL：数据控制语句，GRANT、RECVOKE

MVCC

数据库多版本并发控制open in new window

Mysql 中 MVCC 的使用及原理详解open in new window

并发控制主要通过锁和 MVCC 来实现，MVCC 即多版本并发控制。

概念：

多版本并发控制其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。

  1. 在插入操作时：记录的创建版本号就是事务版本号
  2. 在更新操作的时候，先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式
  3. 删除操作的时候，就把事务版本号作为删除版本号
  4. 在查询时要符合以下两个条件的记录才能被事务查询出来：
    • 删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。
    • 创建版本号小于或者等于当前事务版本号，就是说记录创建是在事务中（等于的情况）或者事务启动之前。

版本号可以减少锁的争用。另外，只有 RC 和 RR 两种事务隔离级别才能使用 MVCC

read-uncommited 由于是读到未提交的，所以不存在版本的问题，而 serializable 则会对所有读取的行加锁。

Innodb 中常见 SQL 语句设置的锁类型

Innodb 中常见 SQL 语句设置的锁类型open in new window

select … from

除了 serializable 隔离级别，这种 SQL 都是一致性非锁定读，不加锁；在 serializable 级别，这种 SQL 加 next-key 锁。

select … from … for update

这种 SQL 加 X 类型的 next-key 锁。

update … where …

RR 及其以上隔离级别下，该语句会加 next-key 锁；在 RC 级别，只加 record 锁。

delete from … where …

RR 及其以上隔离级别下，该语句会加 next-key 锁；在 RC 级别，只加 record 锁。

insert … values …

只会在它插入的行上加 X 锁，而不会加 next-key 锁。

什么情况下 innodb 加表锁

InnoDB 行锁是通过索引上的索引项来实现的。InnoDB 这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB 将使用表锁

行级锁变为表级锁情况如下：如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

MySQL 索引命中规则

规则很多，例如：

  1. 没有查询条件或者查询条件没有索引
  2. 查询条件使用函数在索引列上，或者索引列参与计算
  3. 隐式转化导致索引失效，例如 mysql 隐式的将数值类型转换成了字符串类型，以匹配表
  4. <>、!=、like 模糊查询 % 在前
  5. 联合索引不遵循最左匹配原则

一、单表操作

1. 查找最晚入职员工的所有信息

max()

返回一列中的最大值

select * from employees
where hire_date =
(select max(hire_date) from employees)

2.查找入职员工时间排名倒数第三的员工所有信息

select distinct

返回唯一不同的值open in new window

order by

默认根据指定的列对结果集进行升序排序，降序可使用 desc 关键字

limit m,n

从第 m+1 条开始，取 n 条数据；

limit n

从第 0 条开始，取 n 条数据，是 limit(0,n)的缩写。

select * from employees
where hire_date = (
    select distinct hire_date order by hire_date desc limit 2,1
)

3. 查找薪水涨幅超过 15 次的员工号 emp_no 以及其对应的涨幅次数 t

count(*)

返回表中的记录数

count(column_name)

返回指定列的值的数目（NULL 不计入）

having

增加 having 子句原因是，where 关键字无法与合计函数一起使用

group by

结合合计函数，根据一个或多个列对结果集进行分组。合计函数例如count，sum，avg，max，min等

select emp_no, count(emp_no) as t
from salaries
group by emp_no
having t > 15

4. 找出所有员工当前(to_date='9999-01-01')具体的薪水 salary 情况，对于相同的薪水只显示一次,并按照逆序显示

order by

group by 代替 distinct

select distinct s.salary
from salaries s
where s.to_date = '9999-01-01'
order by s.salary desc

对于大表（大数据量）一般不用 distinct，使用 group by

select s.salary
from salaries s
where s.to_date = '9999-01-01'
group by s.salary
order by s.salary desc

5. 从 titles 表获取按照 title 进行分组，每组个数大于等于 2，给出 title 以及对应的数目 t。

count、group by和 having 搭配使用

select title, count(title) as t from titles
group by title
having t >= 2

6. 从 titles 表获取按照 title 进行分组，每组个数大于等于 2，给出 title 以及对应的数目 t。注意对于重复的 emp_no 进行忽略。

count 内部搭配 distinct 使用

select distinct title, count(distinct emp_no) as t
from titles
group by title
having t >= 2

7. 查找 employees 表所有 emp_no 为奇数，且 last_name 不为 Mary 的员工信息，并按照 hire_date 逆序排列

where 中使用条件判断

select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc

8. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

order by、limit m,n

select emp_no, salary 
from salaries 
where to_date='9999-01-01'
order by salary desc
limit 1,1

二、多表操作

1. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号

select s.*, d.dept_no
from salaries s, dept_manager d
where s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01'
AND s.emp_no = d.emp_no

join

如果表中有至少一个匹配，则返回行

select s.*, d.dept_no
from salaries s /*inner*/ join dept_manager d on s.emp_no = d.emp_no
where s.to_date = '9999-01-01'
  AND d.to_date = '9999-01-01'

2. 查找所有已经分配部门的员工的 last_name 和 first_name

inner join

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

select e.last_name, e.first_name, d.dept_no
from dept_emp d inner join employees e
on d.emp_no = e.emp_no

3. 查找所有员工的 last_name 和 first_name 以及对应部门编号 dept_no，也包括展示没有分配具体部门的员工

left join

从左表那里返回所有的行，即使在右表中没有匹配的行。

select e.last_name, e.first_name, d.dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no

4. 查找所有员工入职时候的薪水情况，给出 emp_no 以及 salary， 并按照 emp_no 进行逆序

salaries.emp_no 不唯一（因为号码为 emp_no 的员工会有多次涨薪的可能,注意到 salaries.from_date 和 employees.hire_date 的值应该要相等

inner join

order by

select e.emp_no, s.salary
from employees e
inner join salaries s
on e.emp_no = s.emp_no
and e.hire_date = s.from_date
order by s.emp_no desc

5. 获取所有部门当前 manager 的当前薪水情况，给出 dept_no, emp_no 以及 salary，当前表示 to_date='9999-01-01'

因为同一 emp_no 在 salaries 表中对应多条涨薪记录，而当 s.to_date = '9999-01-01'时是该员工当前的薪水记录

inner join

select d.dept_no, d.emp_no, s.salary
from dept_manager as d
inner join salaries as s
on d.emp_no = s.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'

6. 获取所有非 manager 的员工 emp_no

is null

判断某一列字段是否为 null 值

left join 替换 in

INopen in new window 操作符允许我们在 WHERE 子句中规定多个值。

在实际表查询中，尽量不用 in、not in操作符，可能会使索引失效

select emp_no from employees
where emp_no
not in (select emp_no
        from dept_manager)

select e.emp_no from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where dept_no is null

7. 获取所有员工当前的 manager，如果当前的 manager 是自己的话结果不显示，当前表示 to_date='9999-01-01'。结果第一列给出当前员工的 emp_no,第二列给出其 manager 对应的 manager_no

<>

inner join

select e.emp_no, m.emp_no
from dept_emp e inner join dept_manager m
on e.dept_no = m.dept_no
where e.to_date = '9999-01-01'
and m.to_date = '9999-01-01'
and e.emp_no <> m.emp_no

8. 获取所有部门中当前员工薪水最高的相关信息，给出 dept_no, emp_no 以及其对应的 salary

inner join

group by

select d.dept_no, s.emp_no, max(s.salary) as salary
from salaries as s
inner join dept_emp as d
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by d.dept_no

9. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

出现出前，要将二张表中的 to_date 字段约束

select t.title, avg(s.salary) 
from titles t inner join salaries s 
on t.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
group by title

10. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
0  Words
Comments
  • Latest
  • Oldest
  • Hottest
Powered by Waline v2.13.0
Default footer
Copyright © 2023 Mr.Wyj