数据库

数据库基本概念

一、键

超键： 在关系中能唯一标识元组的属性集称为关系模式的超键。它是“(最小的)键的超集”，因此每个键都是超键。每个超键都满足键(候选键)的第一个条件：它函数决定了关系中所有其他属性。但超键不需要满足第二个条件：最小化。

候选键：最小的超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个表只能有一个主键，且主键的取值不能缺失，即不能为空值(Null)。

外键：在一个表中存在的另一个表的主键称此表的外键。

注：唯一标识元组的属性集就是超键，所以超键不唯一，并且，超键可以是一个属性也可以是多个属性，同时候选键是最小的超键，那么候选键也是不唯一的，但是一个元组，主键是唯一的，是从候选键中选择一个作为主键。

自增列作为主键：

如果我们定义了主键(PRIMARY KEY)，那么InnoDB(MySQL的数据库引擎之一，事务型数据库的首选引擎，支持ACID事务，支持行级锁定) 会选择主键作为聚集索引。

如果表使用自增主键，那么每次插入新的记录， 记录就会顺序添加到当前索引结点的后续位置，当一页写满，就会自动开辟一个新的页。

如果使用非自增主键(如身份证号或学号等)，由于每次插入主键的值近似随机，因此每次新记录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了新记录插到合适位置而移动数据，甚至目标页面可能已经被写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

二、在SQL中定义关系模式

最普遍的用于描述和操纵关系数据库的语言是SQL。

1. SQL中的关系

• 存储的关系，称为表。这是通常要处理的一种关系，它在数据库中存储，用户能够对其元组进行查询和更新。
• 视图，通过计算来定义的关系。这种关系并不在数据库中存储，它只是在需要的时候被完整或者部分地构造。
• 临时表，在执行数据查询和更新时由SQL处理程序临时构造。这些临时表会在处理结束后被删除而不会存储在数据库里。

2. 数据类型

VARCHAR、CHAR和TEXT

• CHAR：存储定长数据很方便，CHAR字段上的索引效率极高，必须在括号里定义长度，可以有默认值，比如定义CHAR(10)，如果存储的数据不到10就会用空格填充后边未满的空间来构成10个字符。且在检索的时候后边的空格会隐藏掉，所以检索出的数据要用trim函数过滤空格。
• VARCHAR：存储变长数据，但存储效率没有CHAR高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，如果数据存在空格，保存和检索的时候尾部的空格会保留。另外，varchar类型的实际长度是它的值的实际长度+1，因为varchar会使用一个结束符或字符长度值来标志字符串的结束。

SQL允许合理地在不同字符串类型之间作类型转换。CHAR和VARCHAR的存储数据都是unicode的字符数据。

• TEXT：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过指定的长度还是可以正常插入。存储或检索过程中，不存在大小写转换，不能和varchar类型进行"+"操作。

数据的检索效率是：CHAR > VARCHAR > TEXT

适用场景：

• 经常变化的字段用VARCHAR
• 知道固定长度的用CHAR
• 超过255字节的只能用VARCHAR或者TEXT
• 能用VARCHAR的地方不用TEXT

FLOAT、DOUBLE、DECIMAL(n, d)

• FLOAT类型数据可以存储至多8位十进制数，占4字节
• DOUBLE类型数据可以存储至多18位十进制数，占8字节
• DECIMAL(n, d)允许可以有n位有效数字的时间指数，小数点在右数第d位的位置。例如0123.45符合类型DECIMAL(6, 2)定义的数值。

3. 修改关系模式

删除：

• drop 直接删除表  drop table R;
• truncate 删除表中数据，再插入时自增长id又从1开始
• delete 删除表中数据，可以加where字句

DELETE语句执行删除的过程是每次从表中删除一行，并且同时该行的删除操作作为事务记录在日志中保存以便进行回滚操作。

TRUNCATE TABLE "表格名"，表格中的资料会完全消失，可是表格本身会继续存在。并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表相关的删除触发器。执行速度快。

当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

truncate、drop操作立即生效，原数据不放到rollback segment中，不能回滚。delete操作会被放到rollback segment中，事务提交后才生效。如果有相应的tigger，执行的时候将被触发。

三、数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

• 第一范式：(确保每列保持原子性) 所有字段值都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。
• 第二范式：(属性完全依赖于主键) 要求数据库表中的每个实例或记录必须可以被唯一地区分。即数据表中必须有主键，可以唯一区分每个元组。
• 第三范式：(确保每列都和主键列直接相关，而不是间接相关)要求一个关系中不包含已在其它关系包含的非主关键字信息。例如部门信息表，部门编号作为主键，其它的属性都与部分相关，例如部门简介等。那么员工信息表中列出部门编号后，就不应该有部门信息表中的非主关键字的信息，例如就不应该有部门简介等属性。
• 巴斯-科德范式(BCNF)：任何非主属性不能对主键子集依赖。
• 第四范式：要求把同一表内的多对多关系删除。
• 第五范式：从最终结构重新建立原始结构。

四、常见的SQL语法

1. SQL的连接表达式

1）交叉连接：是笛卡尔积或“积”的同义词： cross join

通过保留字on来获得θ连接运算(先得到两个关系的积，在得到的关系中寻找满足条件的元组)：

join...on的意思是在积运算R×S的基础上再使用on后面的条件进行选择运算。

2）自然连接 natural join

与θ连接的不同之处：

• 自然连接时对两个关系中具有相同名字并且其值相同的属性作连接，除此之外再没有其他的条件
• 两个等值的属性只投影一个

3）外连接：它是一种通过在悬浮元组(不能和另外关系中的任何一个元组配对的元组)里填充空值来使之成为查询结果。

• R natural full outer join S ：先进行自然连接操作，再把来自R或S的悬浮元组加入其中，用空值null补齐那些出现在结果中但不具有值的属性。
• R natural left outer join S ：左外连接，只有左变量R的悬浮元组被补齐空值加入到结果中。
• R natural right outer join S ：右外连接，只有右变量S的悬浮元组被补齐空值加入到结果中。
• R  full(left/right) outer join S on 条件 ：θ(左/右)外连接，先进行θ连接，然后将那些不能匹配其他关系的元组用空值补齐。

在PostgreSQL中只有以下几种连接

• 交叉连接：cross join
• 自然连接：natural join
• 内连接：inner join...on...，相当于θ连接
• 左外连接：left outer join ... on ... 相当于θ左外连接
• 右外连接：right outer join ... on ... 相当于θ右外连接
• 外连接：full outer join ... on ... 相当于θ外连接

2. 消除重复：如果希望结果中不出现重复的元组，可以在保留字select后跟上distinct

select distinct name

代价：为消除重复，对元组进行排序的时间通常比执行查询的时间更长，谨慎使用。

3. 并、交、差中的重复

select语句中默认的是保留重复的元组，除非使用distinct保留字指明。

并、交、差的操作默认是消除重复的，要组织消除重复元组，必须在union, intersect和except后跟上保留字all。

4. 聚集操作符

sum, avg, min, max, count

count(*), count(1), count(column)的区别：

1）count(1)和count(*)会统计所有行数，包括null。效率差不多，都快于count(column)；

2）count(column)会对列具有的行数进行计算，不包括null；

3）count(distinct column)会对非null值进行去重统计。

例如：

5. 分组

group by子句可以对元组进行分组，后面跟着一个分组属性列表。select子句使用的聚集操作符仅应用在每个分组上。

6. HAVING子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

练习写sql网址：https://www.cnblogs.com/coder-wf/p/11128033.html

五、存储过程和函数

存储过程

1. 定义：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

2. 调用：

• 可以用一个命令对象来调用存储过程
• 可以供外部程序调用，比如：java程序

3. 优点：

• 存储过程是预编译过的，执行效率高
• 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯
• 安全性高，执行存储过程需要一定权限的用户
• 存储过程可以重复使用，可减少数据库开发人员的工作量

4. 缺点：移植性差

存储过程和函数的区别

存储过程是第一次编译之后就会被存储下来的预编译对象，之后无论何时调用它都会去执行已经编译好的代码。而函数每次执行都需要编译一次。

区别

• 存储过程中可以使用try-catch块和事务，而函数中不可以
• 函数有且只有一个输入参数和一个返回值，而存储过程没有这个限制
• 函数可以被存储过程调用而存储过程不可以被函数调用

六、视图和游标

视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对(可更新)视图进行增、改、查操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表，它使得我们获取数据更容易，相比多表查询。

• 视图(子查询)：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。
• 单表视图一般用于查询和修改，会改变基本表的数据。
• 多表视图一般用于查询，不会改变基本表的数据。

视图的优点：

• 简化了操作，把经常使用的数据定义为视图
• 安全性，用户只能查询和修改能看到的数据
• 逻辑上的独立性，屏蔽了真实表的结构带来的影响

视图的缺点：

• 性能差：数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
• 修改限制：当用户试图修改视图的某些信息时，数据库必须把它转化成对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

游标

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

七、关系数据库和非关系数据库

1. 非关系型数据库的优势

• 性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
• 可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

2. 关系型数据库的优势

• 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持：使用对于安全性能很高的数据访问要求得以实现。

数据库事务

事务提交与事务回滚的概念见：https://www.php.cn/jishu/mysql/412479.html

一、数据库锁

乐观锁和悲观锁

1. 悲观锁：

特点：先获取锁，再进行业务操作。

即”悲观“的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select...for update操作来实现悲观锁。当数据库执行select...for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果视图选中同一行则会发生排斥(需要等待行锁被释放)，因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

select for update是为了在查询时，避免其他用户以该表进行插入，修改，删除等操作，造成表的不一致性。

例：

• select * from t for update 会等待行锁释放之后，返回查询结果。
• select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
• select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
• select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

SELECT…FOR UPDATE 语句的语法如下：
　　SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
其中：
　　OF 子句用于指定即将更新的列，即锁定行上的特定列。
　　WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。

2. 乐观锁

乐观锁，也叫乐观并发控制，它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，那么当前正在提交的事务会进行回滚。

乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。

实现乐观锁一般的做法是在需要锁的数据上增加一个版本号或者时间戳。具体见https://www.yisu.com/zixun/232613.html

3. 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

 

锁的具体种类

因为乐观锁是逻辑上的概念，不需要数据库特殊支持，所以这里的分类都是针对悲观锁的：

1. 按范围(行级、页级、表级)划分，悲观锁可以分为：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

数据库引擎与锁：

使用行级锁定的主要有InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster；

使用表级锁定的主要有MyISAM, MEMORY, CSV等一些非事务性存储引擎。

2. 按使用性质划分，悲观锁分为：

• 共享锁：也称读锁或S锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
• 排他锁：也称独占锁、写锁或X锁。如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读数据也能修改数据。

3. 死锁

表级锁不会产生死锁。所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是：让不同的Session加锁有次序。

解决死锁的方式：

• 查出的线程杀死kill
• 设置锁的超时时间
• 指定获取锁的顺序

二、事务

定义和特性

1. 定义：事务是对数据库中一系列操作进行统一的回滚或者提交操作，主要用来保证数据的完整性和一致性。

2. ACID特性

• 原子性(Atomicity)：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 一致性(Consistency)：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱， B却没收到。
• 隔离性(Isolation)：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
• 持久性(Durability)：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的数据。

事务并发产生的问题

1. 脏读：事务A读取了事务B更新的数据，还未提交，然后B回滚操作，那么A读取到的数据是脏数据。

2. 不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，前后两次读到的数据因更新结果不一致。

3. 幻读：幻读发生在两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。

从总的结果看，似乎两者都表现为两次读取的结果不一致：

• 不可重复读的重点是修改。同样的条件，你读取过的数据，再次读取出来发现值不一样了。
• 幻读的重点在于新增或删除。同样的提交，第一次和第二次读出来的记录数不一样。

从控制的角度看：

• 对于不可重复读，只需要锁住满足条件的记录
• 对于幻读，要锁住满足条件及其相近的记录

解决不可重复度的问题只需锁住满足条件的行，解决幻读需要锁表(因为幻读是因为增加或删除导致记录数不一样，所以得保证整个表都不能改变)。

事务的隔离级别

数据库事务的隔离级别有 4 种，由低到高分别为 Read uncommitted 、Read committed 、Repeatable read 、Serializable 。

当多个事务同时进行时,通过设置隔离级别来处理脏读、不可重复读、幻读事件。

1）read uncommitted | 0 未提交读，就是一个事务可以读取另一个未提交事务的数据，造成脏读。

• 将查询的隔离级别指定为 0。
• 可以读脏数据
• 读脏数据:一事务对数据进行了增删改,但未提交,有可能回滚,另一事务却读取了未提交的数据

2）read committed | 1 已提交读，就是一个事务要等另一个事务提交后才能读取数据。

• 将查询的隔离级别指定为 1。
• 避免脏读,但可以出现不可重复读和幻读
• 不可重复读:一事务对数据进行了更新或删除操作,另一事务两次查询的数据不一致
• 幻读:一事务对数据进行了新增操作,另一事务两次查询的数据不一致

3）repeatable read | 2 可重复读，在事务开启时，不再允许修改操作在同一事务里。

• 将查询的事务隔离级别指定为 2。
• 避免脏读,不可重复读,允许幻读(问题在于只能封锁已经存在的项，以后还是有可能会有新插入的项)

4）serializable | 3 可序列化

• 将查询的隔离级别指定为 3。
• 串行化读，事务只能一个一个执行，避免了脏读、不可重复读、幻读
• 执行效率慢（我遇到过一种情况，用时是隔离级别1的30倍），使用时慎重

 MySQL的MVCC(多版本并发控制)：https://www.cnblogs.com/myseries/p/10930910.html

数据库索引

一、索引

定义

众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。

MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

作用：协助快速查询、更新数据库表中数据。

为表设置索引要付出代价：

• 占用数据库的存储空间
• 在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

聚集索引和非聚集索引

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据，由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能有一个聚集索引。但该索引可以包含多个列(组合索引)，就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理上相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚类索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。

同样，如果对从表中检索的数据进行排序时经常要用到的一列，则可以将该表在该列上聚集(物理排序)，避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员ID列emp_id查找特定雇员的最快速的方法，是在emp_id列上创建聚集索引或PRIMARY KEY约束。

 

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。

1.创建表的时候指定主键（注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）

create table t1(
    id int primary key,
    name nvarchar(255)
)

2.创建表后添加聚集索引

SQL Server：
create clustered index clustered_index on table_name(colum_name)
MySQL：
alter table table_name add primary key(colum_name)

我的疑问：创建聚集索引时会对表进行排序吗？还是只是把物理顺序排成索引列的顺序？

答：与存储引擎有关系。对于InnoDB，在创建索引的时候就会按照索引列排好序存储在内存中，select出的结果也是按照索引列排序的。

对于MyISAM表

MySQL Select默认排序是按照物理存储顺序显示的。不进行额外排序，也就是说select出的顺序会显示为插入顺序。

InnoDB表

select时不加order by时，MySQL会尝试以尽可能快的方法返回数据，所以返回的数据有可能以主键、索引的顺序输出，这里并不会真的排序，主要是由于主键、索引本身就是排序放到内存里的。

原文链接：https://blog.csdn.net/bigtree_3721/article/details/51339895

InnoDB:普通索引(也是辅助索引，其实也是一种非聚集索引)的叶子节点存储的是聚集索引的键，查找过程是先找到该索引key对应的聚集索引的key(主键)，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

举个例子说明下：

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`username` VARCHAR(255),

`score` INT,

PRIMARY KEY(`id`), KEY(`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

聚集索引clustered index(id), 普通索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score(用查找到的聚集索引的key去索引树上查找score)：

select username, score from t1 where username = '小明'

 

 这也就是为什么InnoDB不建议主键过长，因为辅助索引存储的主键的值，如果主键过长，会使整个辅助索引过大。

非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

按照定义，除了聚集索引以外的索引都是非聚集索引，可以细分成普通索引，唯一索引，全文索引。

非聚集索引的存储结构与聚集索引是一样的，不同的是在叶子结点的数据部分存的不是具体的整条数据，而是数据的地址。即叶子结点存储的还是数据的索引，这也就是为什么索引相邻，而物理存储不一定相邻的原因。

Myisam中所有的索引叶节点存储的都是索引和数据地址。

Myisam(非聚集索引)

若以这个引擎创建数据库表Create table user （…..），它实际是生成三个文件：

user.myi   索引文件     user.myd数据文件     user.frm数据结构类型。

当我们执行  select * from user where id = 1的时候，它的执行流程。

　　(1)查看该表的myi文件有没有以id为索引的索引树。

　　(2)根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。

　　(3)根据数据地址去myd文件里面找到对应的数据返回出来。

原文链接：https://www.cnblogs.com/wlwl/p/9465583.html

最左匹配原则

联合索引的最左匹配原则。

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>, <, between, like)就会停止匹配。

解释与原理见：https://www.cnblogs.com/lanqi/p/10282279.html

总而言之就是索引B+树是根据一个值来构建的，就会选择索引组中最左边的值来构建，所以比如所以(a,b)，在B+树中，a的值是有序的，b是无序的，是相对a有序的，所以只给出b=2这个条件或者给出a>1,b=2这种条件是无法根据索引匹配数据的。

二、索引优化

1. 一般来说，应该在这些列上创建索引：

• 在经常需要搜索的列上，加快搜索速度；
• 在主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 在经常需要连接的列上。这些列主要是一些外键，加快连接速度；
• 在经常需要根据范围进行搜索的列上，因为索引已经排序，其指定的范围是连续的；
• 在经常需要排序的列上，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用WHERE子句中的列上，加快条件的判断速度。

总结就是：唯一、不为空、经常被查询到的字段

2. 对于有些列不应该创建索引：

• 对于那些在查询中很少使用或者参考的列不应该创建索引；
• 对于那些只有很少数据值的列；
• 对于那些定义为text,image和bit这种数据量很大的数据类型的列；
• 当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，减低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

三、索引的优缺点

优点：

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

缺点：时间上、空间上、对表更新时的维护

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

问题：多加索引一定会好吗？

看起来似乎建立的索引越多，对于一个给定的查询来说，索引就越有可能起到作用。但是，如果更新是最频繁发生的操作，则对于索引的创建应该采取非常保守的策略。每个对于关系R的更新操作都会迫使同时更新R的修改过的属性或者属性集上的任何索引。这样，不仅要读取和写回修改过的R的页，还必须花费额外代价读取和回写存储索引的页。尽管有时更新操作是对数据库采取的主要操作，在频繁访问的属性上建立索引仍然可以获得性能改进。因为实际上，一些更新操作本身也包含了对数据库的查询操作(比如，带有select-from-where子查询的插入或者是带条件的删除等操作)。

还可以再说一些索引的缺点。

四、索引底层结构

索引的底层结构通常是B树，B+树和hash结构

B树(B-树)

平衡二叉树的查找效率是非常高的，并可以通过降低树的深度来提高查找的效率。但是当数据量非常大，树的存储的元素数量是有限的，这样会导致二叉查找树的深度过大而造成磁盘IO读写过于频繁，进而导致查询效率低下。

B数是一种自平衡树数据结构，是二叉搜索树的一般化，因为节点可以有两个以上的子节点。B树非常适合读取和写入相对较大的数据块。通常用于数据库和文件系统。

m阶B树的定义：

• 根节点至少有两个子节点
• 每个中间节点都包含k-1个元素和k个子节点，其中m/2<=k<=m
• 每个叶子节点都包含k-1个元素，其中m/2<=k<=m
• 所有叶子节点都位于同一层
• 每个节点的元素从小到大排列，节点当中k-1个元素正好是k个子节点包含的元素的值域划分

B树的插入：

针对m阶高度H的B树，插入一个元素时，首先在B树中是否存在，如果不存在，即在叶子节点处结束，然后在叶子节点中插入该新元素。

• 若该节点元素个数小于m-1，直接插入；（因为一个节点最多有m-1个元素）
• 若该节点元素个数等于m-1，引起节点分裂；以该节点中间元素为分界，取中间元素(偶数个数，中间元素随机选取)插入到父节点中；
• 重复上面动作，知道所有节点符合B树规则；最坏的情况一直分裂到根节点，生成新的根节点，高度增加1.

B树的删除：

首先查找B树中需要删除的元素，如果该元素在B树中存在，则将该元素在其节点中进行删除；删除该元素后，首先判断该元素时候有左右节点，如果有，则上移孩子节点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”，这是为了满足定义中的最后一条)到父节点中，然后是移动后的情况；如果没有直接删除。

• 删除后或者移动后某节点中元素数目小于(m/2)-1(m/2)向上取整，则需要看其某相邻兄弟节点是否丰满；
• 如果丰满（结点中元素个数大于(m/2)-1），则向父节点借一个元素来满足条件(之后丰满的兄弟节点会补充父节点，这也是为什么要看兄弟节点是否丰满)；
• 如果相邻的兄弟都不丰满，即其节点数目都等于(m/2)-1，则该节点与其相邻的某一兄弟节点进行“合并”(父节点中在需要合并的两个节点元素之间的元素下移到子节点，进行两个节点的合并)成一个节点。
• 重复以上过程直到符合B树规则。

插入、删除的更多细节及示例见：https://www.cnblogs.com/lianzhilei/p/11250589.html

m阶B+树具有如下特征：

• 有k个子节点的中间节点包含k个元素(B树是k-1个元素)，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
• 所有的叶子节点中包含了全部元素信息，以及指向含这些元素记录的指针，且叶子节点本身依关键字的大小从小到大顺序连接。
• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大(或最小)元素。

插入和删除过程与B树大同小异。

B+树的优势：

• 单一节点存储更多的元素(B树每个结点都存储数据，B+树则没有，数据量相同的情况下，B+树比B树更“矮胖”)，使得查询的IO次数更少。
• 所有查询都要查找到叶子节点(B树有可能查到中间节点，因为每个结点都存储数据)，查询性能稳定。
• 所有叶子节点形成有序链表，便于查询范围。(B树每找一个元素都要中序遍历，直到找到范围内所有元素。而B+树只需要在链表上做遍历即可)。

原文链接及更多细节见：https://blog.csdn.net/qq_26222859/article/details/80631121

MySQL使用B+树，其中MyIsam是非聚集索引，InnoDB是聚集索引。区别见：https://blog.csdn.net/qq_20143059/article/details/82809712

Hash索引

哈希索引能以O(1)时间进行查找，但失去了有序性：

• 无法用于排序和分组；
• 只支持精确查找，无法用于部分查找(like)和范围查找。

InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+树索引之上再创建一个哈希索引，这样就让B+树索引具有哈希索引的一些优点，比如快速的哈希查找。

五、索引失效

使用索引时，不满足最左匹配原则就会使索引失效，原理见上“最左匹配原则”。

一下这些情况，执行引擎将放弃使用索引而进行全表扫描(这些都与最左匹配的原理有关)：

• 在where子句中使用! = 或 < >操作符
• 在where子句中使用or来连接条件，可以改成union
• 在where子句字段进行null值判断时
• 在where子句中like的左模糊(以%开头)
• 在where子句中对有索引的字段进行表达式或函数操作
• 如果执行引擎估计使用全表扫描要比使用索引快，则不使用索引

使用数据库自带的explain进行索引失效的判断

判断一条语句在执行的时候有没有走索引，我们可以再该语句之前增加  explain 关键字，在使用了 explain 关键字后，可以向你显式的表明，该语句的性能。

各个标识的意思：

• type: 主要衡量该检索的性能（all代表全表扫描，index代表全索引）

• key: 显示 Mysql 实际决定使用的键（索引），null代表未走索引。

更多explain实例见：https://www.jianshu.com/p/d5b2f645d657

数据库引擎和主从复制

一、存储引擎

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

InnoDB和MyISAM是数据库最常见的两种存储引擎。

InnoDB

从MySQL5.5.5以后，InnoDB是默认的事务型存储引擎。只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+Next-Key Locking防止幻读。

主索引是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能由很大提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作；自动创建的自适应哈希索引；能够加速插入操作的插入缓冲区等。

支持真正的在线热备份(备份时不影响数据读写)。其他存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

InnoDB中，创建的表的表结构存储在.frm文件中（在MYSQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为数据表名.frm，如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构）。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

更多存储细节见：https://blog.csdn.net/abc123lzf/article/details/100055300

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小，可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较：

• 事务：InnoDB是事务型的，可以使用Commit和Rollback语句。
• 并发：MyISAM只支持表级锁，而InnoDB还支持行级锁。
• 外键：InnoDB支持外键。
• 备份：InnoDB支持在线热备份。
• 崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM支持压缩表和空间数据索引。

选择使用

• 如果应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM的性能更优，占用的空间更少。
• 如果应用程序一定要使用事务，无疑要选择InnoDB引擎。但要注意，InnoDB的行级锁是有条件的，在where条件没有使用主键时，照样会锁全表(因为不确定是在哪一行操作)。
• 现在一般都选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率低，MyISAM对于读写密集型应用一般不会去选用。

删除自增主键的不同结果

一张自增表里面总共有7条数据，删除了最后2条数据，重启MySQL数据库，又插入了一条数据，此时id是几？

　　一般情况下，我们创建的数据库表引擎是InnoDB，新增一条记录，如果不重启MySQL，这条记录的id是8，如果重启，id是6.因为InnoDB表只把自增主键的最大id记录到内存中，所以重启数据库或者对表optimize操作，都会使最大id丢失。

　　但是，如果我们使用的引擎是MyISAM，那么这条记录的Id就是8.因为MyISAM会把自增主键的最大id记录到数据文件里，重启MySQL后，自增主键的最大id也不会丢失。

　　注：如果在这7条记录里删除的是中间的几个记录(比如删除的是3,4两条记录)，重启MySQL数据库后，insert一条记录后，id都是8.

optimize作用：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。 

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

MEMORY

　　MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

　　MEMORY存储引擎的表对应一个磁盘文件，该文件的文件名与表名相同，为frm类型，该文件只存储表的结构，而其数据文件都存在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

　　MEMORY默认使用哈希索引。查找速度比使用B型树索引快。想用B树索引可以在创建索引时指定。

　　注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

二、主从复制和读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

 

 主从复制

主从复制的作用：

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。

1. 主(Master)从(Slave)复制的工作过程

• 在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。
• Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程(I/O)，IO线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。
• Sql slave thread(sql从线程)处理该过程的最后一步，sql线程从中继日志中读取事件，并顺序执行该日志中的SQL事件，更新slave数据，使其与master中的数据一致。中继日志通常会位于OS缓存中，所以中继日志的开销很小。

 https://zhuanlan.zhihu.com/p/50597960

 

2. mysql支持的复制类型

• 基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，执行效率高。
• 基于行的复制。把改变的内容复制过去，而不是把命令在服务器上执行一遍。
• 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

3. 主从复制的几种方式

• 同步复制：master的变化必须等待slave-1, slave-2,....,slave-n完成后才能返回。这样显然不可取，也不是mysql复制的默认设置。比如，在web前端页面，用户增加了一条记录，需要等待很长时间。
• 异步复制：主库执行完操作后，写入binlog日志后，就返回客户端，不去验证binlog有没有成功复制到从库。如果主库提交一个事务并写去binlog后，从库还没有从主库得到binlog时，主库宕机了或因磁盘损坏等故障导致该事务的binlog丢失了，那从库就不会得到这个事务，造成主从数据不一致。mysql的默认设置。
• 半同步复制：当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到binlog并成功写入Relay-log中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的binlog，另一份保存在其中一个从库的relay-log中，从而保证了数据的安全性和一致性。由google为MySQL引入的。

另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那MySQL会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。

读写分离

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

并不是一有性能问题就上读写分离，而是应该先优化，例如优化慢查询，调整不合理的业务逻辑，引入缓存查询等。只有确定系统没有优化空间后才考虑读写分离集群。

 

 

引入的系统复杂度问题

问题一、主从复制延迟

 

 

问题二、 分配机制

 如何将读写操作区分开来，然后访问不同的数据库服务器？

 1. 客户端程序代码封装实现

基本实现：在代码中抽象一个数据库访问层，实现读写操作分离和数据库服务器连接的管理。

特点：

• 实现简单，而且可以根据业务做更多定制化的功能；
• 每个编程语言需要自己实现一次，无法通用。如果一个业务包含多个编程语言写的多个子系统，则重复开发的工作量较大；
• 故障情况下，如果主从发生切换，则可能需要所有系统都修改配置并重启。

 

 2. 服务器中间件封装

基本实现：

• 独立一套系统出来，实现读写操作分离和数据服务器连接的管理。
• 中间件对业务服务器提供SQL兼容的协议，业务服务器无需自己实现读写分离。

特点：

• 对于业务服务器，访问中间件和访问数据库没有区别。
• 能够支撑多种编程语言
• 数据库中间件需要支持完整的SQL语法和数据库服务器的协议，实现比较复杂。
• 所有数据库操作都经过中间件，对中间件性能要求较高。
• 数据库主从切换对业务服务器无感知，数据库中间件可以探测数据库服务器的主从状态。

 

 

 原文链接：https://www.jianshu.com/p/eba38b1ff43c

随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？

【分库】垂直切分

把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同的库上。

如果数据库时因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合（比如用户、产品、订单之间没什么关系），那么规则简单明了、容易实施的首选就是分库。

优点：实现简单，库与库之间界限分明，便于维护。

缺点：不利于频繁跨库操作（比如用户与订单在不同的库，但是如果想要得到谁下的订单，就需要跨库操作），单表数据量大的问题解决不了。

【分表】水平切分

它是将同一个表中的记录拆分到多个结构相同的表中。这多个表可以存在一到多个库中(一般都会存在多个库中)。分表又分成垂直分表和水平分表。

垂直分表：将本来可以在同一个表的内容，人为划分为多个表。（所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的。）分后的表之间有一对一的对应关系。操作不同的业务就操作不同的表。例如把一个用户的联系方式，工作和基本信息拆分成不同的表，当想修改用户的基本信息时就去修改基本信息的表。

 

 

水平分表：也称为数据分片，是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

分表优点：能解决分库的不足点。（单表数据量太大，分库解决不了）

分表缺点：实现起来比较复杂，特别是分表规则的划分，程序的编写，以及后期的数据库拆分移植维护。

一般都是先分库再分表，结合使用，取长补短。但是缺点是架构很大，很复杂，应用程序编写也比较复杂。

水平切分策略：

哈希分表：通过一个原始目标id或者是名称按照一定的hash算法计算出数据存储的表名。

按范围分表：可以是ID范围也可以是时间范围。例如用户1到一百万用一张表，一百万到两百万用一张表。当数据有很强的实时效性，例如微博的数据，可以按月分割。

其中哈希分表，哈希一致性是最靠谱的一种方案，能保证数据库均匀。其他的方案按时间分表这种可能会是数据库不均匀，这个月数据一百万条，下个月可能就三百万条了。

 

 数据库优化

一、优化方向

 

 从上图可以看出来，数据表结构、SQL、索引是成本最低，且效果最好的优化手段。

数据库优化从以下几个方面优化：

• 数据库设计——三大范式、字段、表结构
• SQL调优
• 存储过程（模块化编程，可以提高速度）
• 数据库索引
• 分表分库（水平切分，垂直切分）
• 主从复制、读写分离
• 对MySQL配置优化（配置最大并发数my.ini，调整缓存大小）
• 定时清除不需要的数据，定时进行碎片管理

二、具体优化方案

数据库设计

1. 三大范式（上文有讲到）

根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询。

2. 字段、表结构

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
• VARCHAR的长度只分配真正需要的空间
• 尽量使用整数代替字符串类型
• 单表不要有太多字段，建议在20以内
• 避免使用NULL字段，很难查询优化且占用额外索引空间
• 不建议使用select * fron t，用具体的字段代替"*"，不要返回用不到的任何字段。尽量避免向客户返回大数据量，若数据量过大，应该考虑相应需求是否合理。
• 表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能
• select count(*) from table;这样不带任何条件的count会引起全表扫描

索引

见上文“索引优化”和“索引失效”

主从复制

见上文

分表分库

数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。如果一张表中的数据量达到了千万甚至上亿级别的时候，不管是建索引，优化缓存等，都会面临巨大的性能压力。

分表场景

• 根据经验，mysql 表数据一般达到百万级别，查询效率就会很低。

• 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

SQL调优

SQL调优最常见的方式是，由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的SQL，然后使用explain、profile等工具来逐步调优，最后经过测试达到效果后上线。

【开启慢查询】

1. 定义：MySQL默认设置10s没有返回结果的，属于慢查询，并存到日志中（在my.ini可以指定慢查询日志目录）。

2. 开启慢启动

• slow_query_log慢启动开启状态。
• slow_query_log_file慢查询日志存放的位置（这个目录需要MySQL的运行账号的可写权限，一般设置为MySQL的数据存放目录）。
• long_query_time查询超过多少秒才记录。

以上三个参数可以在数据库的配置文件中设定开启，也可以在mysql命令行通过set命令开启。当在配置文件中开启慢查询日志记录之后，就会在指定的存放目录生成日志文件。

【分析慢查询——explain】

当我们获得慢查询的日志之后，查看日志，观察那些语句执行是慢查询，在该语句之前加上explain再次执行，explain 会在查询上设置一个标志，当执行查询时，这个标志会使其返回关于在执行计划中每一步的信息，而不是执行该语句。它会返回一行或多行信息，显示出执行该计划中的每一部分和执行次序.

explain通常用于查看索引是否生效，explain执行语句返回的重要字段：

• type：显示是搜索方式（全表扫描或者索引扫描）

• key：使用的索引字段，未使用则是null