【无标题】MySQL第八章知识点总结

SQL优化

一、索引的定义

索引是与表或视图关联的独立的、物理的数据库结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键，这些键存储在一个结构(B树)中，以便MySQL可以快速有效地查找与键值关联的行(部分MySQL中的数据库引擎还可以支持HASH结构，这些数据库引擎不在我们学习的范围内)

数据库中的索引与书籍的目录非常相似，在一本书中利用目录可以快速查找到所需信息，不需要逐页翻阅整本书去寻找。同样，在数据库中索引使数据库程序无须对整个表进行扫描，就可以找到所需数据。书中的目录是一些词语列表，注明了包含这些内容的页码，数据库索引则是一列或多列生成的键。

索引的作用就是为了提高数据库从表或视图中查询数据的速度，改善数据库性能，如同书的目录一样。一般来说，目录所占的页数肯定比书的内容所占的页数少。索引也需要有空间来存放这些键，这些存放索引的空间在数据库中称为索引页。索引页是数据库中存储索引的数据页，存放键值以及指向数据行位置的指针。

二、索引的特点

索引的优点主要有以下几点：

1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

1. 可以大大加快数据的查询速度。

2. 在实现数据的引用完整性方面，可以加速表和表之间的连接。

3. 在使用分组和排序进行查询数据时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

2. 索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间(InnoDB 数据表上的索引是表空间的一个组成部分)，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

3. 当对表中的数据进行增加、删除、修改等操作时，索引也会动态的维护，这样就降低了数据的维护速度。

三、索引的分类

MySQL中的索引分为以下几类：

1.普通索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。 2.唯一索引 索引列的值必须唯一，但允许有空值(但只能有一个) 3.主键索引 主键索引是一种特殊的唯一索引，不允许有空值。主键索引一定是唯一索引，但是难一索引不一定是主键索引。 4.组合索引 组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。创建组合索引时需要注意以下三点： A.当需要频繁地将两列或多列作为一个整体进行搜索时，可以创建组合索引。 B. 创建组合索引时先列出唯一性最好的列。 C.组合索引中列的顺序和数量会影响查询的性能。 5.全文索引 全文索引类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者 TEXT 类型的列上设置，同时在MySQL中只有My/SAM存储引擎支持全文索引。 6.空间索引 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用 SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建控件索引的字段，必须设置NOT NULL约束，空间索引只能在存储引擎为MyISAM的表中创建。

EXPLAIN语句输出结果的各个关键字解释：

(1) solect tyne指定使用的SELECT查询类型，值SIMPLE的含义是：简单查询，不适用INI0N或子查询。其他可能的取值有：PRIMARY、UN1ON、SUBQUERY等。 (2) table行指定数据库读取的数据表的名字。 (3)联合查询所使用的类型，type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是： system>const>eq_ref> ref >fulltext>ref or null>index merge>unique_subquery>index subquer index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref. (4) possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段是否为合适的索引。 (5)key:显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL，如果值是：PRIMARY就说明使用了主键，如果是NULL则说明没有索引被使用。 (6)key_len:显示MySQL决定使用的键长度。如果键是NLL，长度就是NULL (7) ref：显示哪个字段或常数与key一起被使用。

（8）rows：这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

（9）filtered:它指返回结果的行占需要读到的行(rows列的值)的百分比。 (10)Extra:如果是Onlyindex，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。如果是where used，就是使用上了shere限制，如果是impossible shere表示用不着where，一般就是没查出来数据。

事务

什么是事务

事务的概念：

在MySQL环境中，事务是由一个单元的一个或多个SQL语句组成，这个单元的每个SQL语句是相互依赖的，而且单元作为一个整体是不可分割的。如果单元中的一个语句不能完成，整个单元就会回滚（撤销），所有影响到数据将返回到事务开始以前的状态，因此，只有事务中所有SQL语句执行成功，则才能说明这个事务被成功执行。使用一个简单的例子来理解事务：

向公司添加一名新员工，这个过程由3个基本步骤组成（如图所示）：

1.在员工数据库中为员工创建一条新记录（分配员工ID,分配岗位等）。

2.为新员工分配部门。

3.建立员工的工资和奖金记录。

如果其中一个步骤失败，则系统必须撤销在此之前的所有变化，删除所有不完整的记录的痕迹，以避免以后的不一致和计算错误。

 

在 开始事务 和 提交事务 之间所作的所有操作 构成一个事务， 其中任何一个步骤失败都会都会导致整个事务被撤销，系统会返回到以前的状态。

事务提交后，对数据的修改是永久的，即使系统出现故障也不会丢失数据。

事务的特点：

1.在MySQL中只有使用了 Innodb存储引擎 的数据表才支持事务，MyISAM不支持。

事务的属性包括 原子性，一致性，隔离性，永久性。

原子性：

原子性 意味着每个事务都必须看作一个不可分割的单元，假设一个事务由两个或多个操作组成，其中这些操作必须同时成功，则整个事务才成功，否则事务失败，失败将会返回该事务以前的状态。

在添加员工这个例子中，原子性指如果没有创建员工的工资表和部门，就不可能将员工插入到数据库表中。总而言之，原子执行是一个或者全部发送或者什么也没有发生的命题。

一致性：

事务

隔离性：

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以 防止多个事务并发执行时 由于多个事务的交叉执行 导致数据库中的数据的不一致。

比如：事务A正在不断的在修改表中的数据，那么事务B需要不断的查看表的数据，那么事务B每次查看的数据可能不一样。

持久性：

事务提交后，对数据的修改就是 永久的，即便系统故障也不会丢失。

一致性：

为了理解一致性，举个通俗易懂的例子：假设我要提现1500块钱提现到银行卡中，那么就需要至少需要分两步操作：

微信钱包-1500块钱。

银行卡+1500块钱

当第一步执行后，服务器出现问题导致第二步就无法完成，那么我们的微信钱包-1500快钱，银行卡上的钱没有增加，导致我白白丢失了1500块钱，这就导致数据“不一致"的问题.

事务的一致性与事务原子性有关，如果第二步执行失败，则会回滚到事务的开始，撤销第一步的操作，所以为了保持事务的一致性，在业务层上设计上，就要求我们合理的设计这两步操作，需要将上面的两个操作设计在一个事务内。

2.事务隔离级别

2.1 Read uncommitted（读未提交）\n提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。

示例：小鑫去商店买衣服，付款的时候，小鑫正常付款，钱已经打到商店老板账户，但是小鑫发起的事务还没有提交。就在这时，商店老板查看自己账户，发现钱已到账，于是小鑫正常离开。小鑫在走出商店后，马上回滚差点提交的事务，撤销了本次交易曹邹。

结果：小鑫未付钱买到了衣服，商店老板实际未收到小鑫的付款。

分析：商店老板查看自己的资金账户，这个时候看到的是小鑫还没有提交事务的付款。这就是脏读。

注意：处于该隔离级别的事务A与B，如果事务A使用事务B不提交的变化作为计算的基础，然后哪些未提交的变化被事务A撤销，这就导致了大量的数据错误变化。

2.2 Read committed （读已提交）

处于Read committed （读已提交）级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。

示例：小鑫卡里有1000元，准备与几个朋友聚餐消费，消费1000元，当他买单时（事务开启），收费系统检测到他卡里有1000元。就在检测完毕的时候，小鑫女朋友发现小明有私房钱，全部转走并提交。当收费系统准备扣款时，再检查小鑫卡里的金额，发现已经没钱了，付款不成功。小鑫此时就会很纳闷，明明有钱的呀，钱呢？

分析：该示例中同一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。该隔离级别可以解决脏读问题。

2.3 Repeatable read （重复读）

在开始读取数据（事务开启）时，不再允许修改操作

示例：还是小鑫有1000元，准备跟朋友聚餐消费这个场景，当他买单（事务开启）时，收费系统检测到他卡里有1000元，这个时候，他的女朋友不能转出金额。接下来，收费系统就可以扣款成功了，小鑫醉醺醺的回家，准备跪脱衣板。

分析：重复读可以解决不可重复读的问题，这句话有些别扭，大家可以仔细品一下。

写到这里，大家可能会产生疑问，什么情况下产生幻读呢？

示例来了：

小鑫在公司上班，女朋友告诉他，拿着他的卡去逛街消费。花了一千元，然后小鑫去查看他银行卡的消费记录（事务开启），看到确实是花了一千元。就在这个时候，小鑫女朋友又花三千元买了一些化妆品和衣服，即新增了一些消费记录。当小鑫打印自己银行卡消费记录单的时候（女朋友事务提交），发现花了四千元，似乎出现了幻觉，小鑫很心疼。这就是幻读

扩展：当我们开启一个事务以后，有如下的程序操作

第一步：更新A表id=1的记录

第二步：查询A表id=1的记录

第三步：使用第二步的查询结果作为依据继续业务逻辑

第四步：提交事务

问题来了：同一个事务中，事务未提交前，第二步的查询结果是第一步执行前的结果还是第一步执行后的结果呢？

答案：事务隔离级别是针对不通事务的，同一事务中的未提交的更新，在后续是可以查询到的。