连接查询
- 左连接-（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
- 右连接-（右外连接）以右表为基准进行查询,右表数据会全部显示出来,右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
聚合函数
SQL 中提供的聚合函数可以用来统计、求和、求最值等等。
- COUNT：统计行数量
- SUM：获取单个列的合计值
- AVG：获取某个列的平均值
- MAX：获取列的最大值
- MIN：获取列的最小值
SQL 关键字
- 分页-MySQL 的分页关键词 limit
SELECT * FROM student3 LIMIT 2,6; 查询学生表中数据，从第三条开始显示，显示6 条 - 分组-MySQL 的分组关键字：group by
SELECT sex, count(*) FROM student3 GROUP BY sex; - 去重-去重关键字：distinct
select DISTINCT NAME FROM student3;
SQL Select 语句完整的执行顺序
查询中用到的关键词主要包含如下展示，并且他们的顺序依次为
- form…
- on…
- left join…
- where…
- group by…
- avg()/sum()…
- having..
- select…
- order by…
- asc/desc…
- limit…
from: 需要从哪个数据表检索数
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组算结果
order by : 按照什么样的顺序来查看返回的数据
数据库三范式
- 第一范式：1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；
- 第二范式：2NF 唯一性，一张表只说一件事，是对记录的惟一性约束，要求记录有惟一标识，
- 第三范式：3NF 直接性，数据不能存在传递关系，即每个属性都跟主键有直接关系，而不是间接关系。
MyISAM 存储引擎
主要特点：
MySQL5.5 版本之前的默认存储引擎
支持表级锁（表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；不支持事务，外键。
适用场景：对事务的完整性没有要求，或以 select、insert 为主的应用基本都可以选用MYISAM。在 Web、数据仓库中应用广泛。
特点：
不支持事务、外键
每个 myisam 在磁盘上存储为 3 个文件，文件名和表名相同，扩展名分别是
.frm ——-存储表定义
.MYD ——–MYData，存储数据
.MYI ——–MYIndex，存储索引
InnoDB 存储引擎
主要特点：
MySQL5.5 版本之后的默认存储引擎；
支持事务；
支持行级锁（行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁）；
支持聚集索引方式存储数据。
事务特性
- 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
- 一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态
- 隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
- 持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。
事务隔离级别
- 读未提交（read Uncommited）:在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用， 安全性太差；
- 读已提交（read commited）:这是大多数数据库默认的隔离级别，但是不是 MySQL 的默认隔离级别；这个隔离级别满足了简单的隔离要求：一个事务只能看见已经提交事务所做的改变，所以会避免脏读问题；由于一个事务可以看到别的事务已经提交的数据，于是随之而来产生了不可重复读和虚读等问题（下面详细介绍这种问题，结合问题来理解隔离级别的含义）；
- 可重复读（Repeatable read）：这是 MySQL 的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
- 可串行化（serializable）：事务的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个；
索引的概念和优点
索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。
优点：
- 创建唯一性索引，保证数据库表中每一行数据的唯一性
- 大大加快数据的检索速度，这也是创建索引的最主要的原因
- 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
索引的分类
- 普通索引：最基本的索引，它没有任何限制。
- 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
- 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。
- 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
- 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。
索引的底层实现原理
索引结构
索引是在 Mysql 的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的, Mysql 目前提供了以下 4 种索引:
- B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.
- Hash 索引: 只有 Memory 引擎支持, 使用场景简单.
- R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.
- S-Full-text(全文索引): 全文索引也是 MyISAM 的一个特殊索引类型, 主要用于全文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.
BTree 结构
B+Tree 是在 BTree 基础上进行演变的, 所以我们先来看看 BTree, BTree 又叫多路平衡搜索树, 一颗 m 叉 BTree 特性如下:
- 树中每个节点最多包含 m 个孩子.
- 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil 函数指向上取整).
- 若根节点不是叶子节点, 则至少有两个孩子.
- 每个非叶子节点由 n 个 Key 和 n+1 个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.
B+Tree 结构
B+Tree 为 BTree 的变种, B+Tree 与 BTree 的区别:
- B+Tree 的叶子节点保存所有的 key 信息, 依 key 大小顺序排列.
- B+Tree 叶子节点元素维护了一个单项链表.
- 所有的非叶子节点都可以看作是 key 的索引部分.
由于 B+Tree 只有叶子节点保存 key 信息, 查询任何 key 都要从 root 走的叶子. 所以B+Tree 查询效率更稳定.
Mysql 中的 B+Tree
MySql 索引数据结构对经典的 B+Tree 进行了优化, 在原 B+Tree 的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的 B+Tree, 提高区间访问的性能.
如何避免索引失效
- 范围查询, 右边的列不能使用索引, 否则右边的索引也会失效.
- 不要在索引上使用运算, 否则索引也会失效.
- 字符串不加引号, 造成索引失效.
- 尽量使用覆盖索引, 避免 select *, 这样能提高查询效率.
- or 关键字连接
- 没有遵循最左匹配，索引失效
数据库锁
行锁和表锁
- 主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁行锁：访问数据库的时候，锁定整个行数据，防止并发错误。表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
- 行锁 和 表锁 的区别：表锁： 开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。行锁： 开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
悲观锁和乐观锁
- 悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
- 乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition 机制的其实都是提供的乐观锁。
MySql 优化
定位执行效率慢的 sql 语句
命令:show status like ‘Com____’,通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：
A：哪些步骤花费的成本比较高
B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
C：这条sql语句是否走索引
show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化这条 sql.
慢查询日志(常用的工具)，慢查询日志记录了所有执行时间超过参数 long_query_time 的 sql 语句的日志 ,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下,有个 slow_query.log 文件
优化索引
- 对查询频次较高, 且数据量比较大的表, 建立索引.
- 索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
- 使用唯一索引, 区分度越高, 使用索引的效率越高.
- 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率.
- 使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
- 如果 where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.
复合索引命名规则 index_表名_列名 1_列名 2_列明 3
比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)
避免索引失效
- 如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开始, 并且不能跳过索引中的列.
- 尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
- 不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数.计算表达式等, 都会是索引失效.
- 查询 like，如果是 ‘%aaa’ 也会造成索引失效.
- 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
Sql 语句调优
- 根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率.
- 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
- where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.
- 排序字段上, 因为排序效率低, 添加索引能提高查询效率.
- 优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.
- 优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
- 优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用 order by null 禁用.
select age, count(*) from emp group by age order by null - 尽量避免子查询, 可以将子查询优化为 join 多表连接查询.
合理的数据库设计
据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询, 遵循数据库三范式
注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：
- 保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
- 增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。
- 分割表-数据表拆分：主要就是垂直拆分和水平拆分。水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系
- 字段设计
- 表的字段尽可能用 NOT NULL
- 字段长度固定的表查询会更快
- 把数据库的大表按时间或一些标志分成小表