索引

MySQL 中的索引，就相当于图书馆的检索目录，它是帮助 MySQL 系统快速检索数据的一种存储结构。我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。

单字段索引

创建单字段索引，一般有 3 种方式：

-- 1 直接给数据表创建索引
CREATE INDEX 索引名 ON TABLE 表名 (字段);
-- 2 创建表的同时创建索引的语法
CREATE TABLE 表名 (
字段 数据类型, …. { INDEX | KEY } 索引名(字段) 
)
-- 3 修改表时创建索引的语法
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段);

给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。建议在创建变的时候，就定义主键。

单字段索引的作用原理

需要用到 EXPLAIN 关键字(显示MySQL的运行计划)能够查看 SQL 语句的执行细节，包括表的加载顺序，表是如何连接的，以及索引使用情况等。
在这里插入图片描述

type=range：表示使用索引查询特定范围的数据记录。
rows=5411：表示需要读取的记录数。
possible_keys=index_trans：表示可以选择的索引是 index_trans。
key=index_trans：表示实际选择的索引是 index_trans。
extra=Using index condition;Using where;Using MRR：这里面的信息对 SQL 语句的执行细节做了进一步的解释，包含了 3 层含义：第一个是执行时使用了索引，第二个是执行时通过 WHERE 条件进行了筛选，第三个是使用了顺序磁盘读取的策略。
在查询的时候，可以先通过查询索引快速定位，然后再找到对应的数据进行读取，这样就大大提高了查询的速度

如何选择索引字段？

建议你在选择索引字段的时候，要选择那些经常被用做筛选条件的字段。
改用itemnumber 进行索引
在这里插入图片描述

组合索引

组合字段可以大大地发挥索引的优势。如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候， MySQL 会选择使用最优的索引来执行查询操作，最优解就是将索引进行组合。

-- 创建组合索引
-- 1 直接给数据表创建索引  和单索引一样  就是把多条索引括起而已
CREATE INDEX 索引名 ON TABLE 表名 (字段1 ,字段2 ..);
-- 2 创建表的同时创建索引的语法
CREATE TABLE 表名 (
字段 数据类型, …. { INDEX | KEY } 索引名(字段1,字段2 ..) 
)
-- 3 修改表时创建索引的语法
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段1,字段2 ..);

在这里插入图片描述

组合索引的原理

组合索引的多个字段是有序的，遵循左对齐的原则。
创建索引字段的顺序和筛选条件字段的顺序必须一样对应否则报错，还有如果其中一个条件如果中断了，则这个条件不能使用组合索引。
因为如果只用组合索引的一部分，效果没有单字段索引那么好。MySQL 还是会选择其他索引.

索引的删除

-- 删除索引
DROP INDEX 索引名 ON 表名;
--  有的索引不能用这种方法删除，比如主键索引，你就必须通过修改表来删除索引。 
ALTER TABLE 表名 DROP PRIMARY KEY；

索引的开销

存储空间的开销，是指索引需要单独占用存储空间。
数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索引能够指向正确的记录。
索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。

索引总结

索引可以非常显著地提高数据查询的速度，数据表里包含的数据越多，效果越显著。我们应该选择经常被用做筛选条件的字段来创建索引，这样才能通过索引缩小实际读取数据表中数据的范围，发挥出索引的优势。如果有多个筛选的字段，而且经常一起出现，也可以用多个字段来创建组合索引

事务

事务是什么？

事务是 MySQL 的一项功能，它可以使一组数据操作（也叫 DML 操作，是英文 Data Manipulation Language 的缩写，包括 SELECT、INSERT、UPDATE 和 DELETE），要么全部执行，要么全部不执行。

-- 事务的语法结构
START TRANSACTION 或者 BEGIN --（开始事务）
 一组DML语句 
 COMMIT -- （提交事务）  执行当前事务的全部操作，让数据更改永久有效
 ROLLBACK -- （ 事务回滚） 回滚当前事务的操作，取消对数据的更改。

事务有 4 个主要特征：

原子性：表示事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断。
一致性：表示数据的完整性不会因为事务的执行而受到破坏。
隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。
持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效。

如何确保操作的原子性和数据的一致性？

事务并不会自动帮你处理 SQL 语句执行中的错误，如果你对事务中的某一步数据操作发生的错误不做处理，继续提交的话，仍然会导致数据不一致
我们可以通过 MySQL 的函数 ROW_COUNT() 的返回，来判断一个 DML 操作是否失败，-1 表示操作失败，否则就表示影响的记录数。

在这里插入图片描述

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; – 定义SQL操作发生错误是自动回滚

在这里插入图片描述

我们要先通过“DELIMITER //”语句把 MySQL 语句的结束标识改为“//”（默认语句的结束标识是“;”）。这样做的目的是告诉 MySQL 一直到“//”才是语句的结束，否则，MySQL 会在遇到第一个“;”的时候认为语句已经结束，并且执行。这样就会报错，自然也就没办法创建存储过程了。
创建结束以后，我们还要录入“//”，告诉 MySQL 存储过程创建完成了，并且通过“DELIMITER ;”，再把语句结束标识改回到“;”。
**我们要把重要的关联操作放在事务中，确保操作的原子性，并且对失败的操作进行回滚处理。**只有这样，才能真正发挥事务的作用，保证关联操作全部成功或全部失败，最终确保数据的一致性.

事务的隔离性?

如果事务A 和事务B 同时对一表进行操作，但可能AorB会出错，导致操作的失败。所以需要用到MySQL 的另外一种机制：“锁”。MySQL 可以把 A 中被修改过而且还没有提交的数据锁住，让 B 处于等待状态，一直到 A 提交完成，或者失败回滚，再释放锁，允许 B 读取这个数据。这样就可以防止因为 A 回滚而导致 B 读取错误的可能了

锁的使用方式不同，隔离的程度也不同。 (MySQL 支持 4 种事务隔离等级。)

READ UNCOMMITTED：可以读取事务中还未提交的被更改的数据。
READ COMMITTED：只能读取事务中已经提交的被更改的数据。
REPEATABLE READ：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。
SERIALIZABLE：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。
一般来讲，使用 MySQL 默认的隔离等级 REPEATABLE READ，就已经够了。不过，也不排除需要对一些关键的数据操作，使用最高的隔离等级 SERIALIZABLE。
计算完成之后，你也不要忘记把隔离等级恢复到系统默认的状态
一方面，对于一些核心的数据更改操作，你可能需要较高的隔离等级，比如涉及金额的修改；另一方面，你要考虑资源的消耗，不能使系统整体的效率受到太大的影响。所以，要根据具体的应用场景，正确地使用事务。