事务
- 事务是指将一系列数据操作捆绑成为一个整体进行统一管理，如果某一事物执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中永久的一部分。如果事务执行中遇到错误且必须取消或回滚，则数据将会全部恢复到操作前的状态，所有数据的更改将全部被清除。
事务的属性
- 原子性（atomicity）：同一事务的多个SQL是不可分割整体，执行过程中要么全部成功、要么全部失败。
- 一致性（consistency）：事务开始前和事物结束后，数据库中的数据处于一致状态。
- 隔离性（isolation）：事务与事务之间相互独立，互不干涉。
- 持久性（durability）：事务执行后将被永久的写入数据库。
如何执行事务
-
InnoDB存储引擎管理事务主要的通过UNDO日志和REDO日志实现，MyISAM存储引擎不支持事务。
UNDO日志：复制事务执行前的数据，用于在事务发生异常时回滚数据。
REDO日志：记录在事务执行过程中，对数据的更新操作，当事务提交时，该内容将被刷新到磁盘。 -
默认情况下每条SQL语句就是一个事务，即执行SQL语句后自动提交。为了达到将几个操作作为一个整体的目的，需要使用BEGIN或START TRANSACTION开启一个事务，或执行命令SET AUTOCOMMIT = 0，来禁止当前会话的自动提交。
- 开始事务：BEGIN 或 START TRANSACTION
显示地标记一个事务的起始点 - 提交事务：COMMIT;
标志一个事务成功提交。并释放资源。 - 回滚（撤销）事务：ROLLBACK;
清除自事务起始点至该语句所做的所有数据更改操作，将数据状态回滚到事务开始前，并释放资源。
-
在数据库操作中，为了保证并发读取数据的正确性，提出了事务的隔离级别。在MySQL中，事物的默认隔离级别是REPEATABLE-READ（可重读）隔离级别。
-
设置自定提交关闭或开启事务
MySQL默认开启自动提交。
SET AUTOCOMMIT = 0;
值为0：关闭自动提交
值为1：开启自动提交 -
编写事务时要遵守以下原则：
- 事务尽可能间断。
- 事务中访问的数据量尽可能减少。
- 查询数据时不要使用事务。
- 在事务处理过程中尽量不要出现等待用户输入的操作。
视图
- 什么是视图
- 视图是基于SQL语句的结果集的可视化的表，是一个虚拟表。对查询执行的大多数操作也可在视图上进行。使用视图的原因有两个：一个是安全考虑，另一个是符合用户日常业务逻辑，使他们更容易理解数据。
- 试图是一种查看数据库中一个或多个表中的数据的方法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。视图并不是数据库中存储的数据集合，他的行和列来自查询中引用的表。
- 视图的应用场景
- 筛选表中的行。
- 防止未经许可的用户访问敏感数据。
- 将多个物理数据表抽象为一个逻辑数据表。
- 视图的好处
- 对最终用户的好处
结果更容易理解，创建视图时，可以将列名更改为更有意义的名称
获取数据更容易 - 对开发人员的好处
限制数据检索更容易。
维护应用程序更容易。调试视图比调试查询更容易。
- 如何创建和使用视图
-
使用SQL语句创建视图 语法：CREATE VIEW 视图名 AS <SELECT 语句>
视图一般以view_xxx或v_xxx的样式来命名。
-
使用SQL语句删除视图 语法：DROP VIEW [IF EXISTS] 视图名 -
使用SQL语句查看视图数据 语法：SELECT 字段1 ， 字段2 ，... FROM 视图名 - 使用视图的注意事项
每个视图中可以使用多个表。
与查询类似，一个视图可以嵌套另一个视图，但最好不要超过3层。
对视图数据进行添加、修改、删除操作直接影响原表中的数据。
当视图来自多个表时，不允许添加和删除数据。
视图说明：从一个或多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。理论上它可以像普通的物理表一样使用，如增删改查操作。但是在实际开发中视图仅用作查询。 - 如何区分视图和表?
MySQL自动创建的数据库information_schema中存在一个包含视图信息的表views,可以通过views来查看所有视图的相关信息。SQL语法：USE information_schema; SELECT * FROM views[\G];
索引
什么是索引
- 索引提供指针以指向存储在表中指定的数据值，再根据指定的排序次序排列这些指针，通过搜索索引找到特定的值，再跟随指针到达包含该值的行。
- 索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。
- 通过MySQL可以快速有效地查找与键值相关联的字段，根据索引的存储类型，可以将索引分为B-树索引（BTREE）和哈希索引（HASH）。
索引的作用
- 通过使用索引可以大大提高数据库的检索速度，改善数据库性能。
- 通过创建唯一索引，可以保证插入的数据的每一行保持唯一性。
- 可以加速表和表之间的连接。
- 在使用分组和排序字句进行数据检索时，同样可以显著的减少时间
- 通过使用索引可以在查询过程中，使用查询优化器，提高系统性能。
索引分类
- 普通索引（INDEX）
允许在定义的列中插入重复和空值，它的唯一任务是加快对数据的访问速度。 - 唯一索引（UNIQUE）
唯一索引不允许两行具有相同的索引值。
如果创建了唯一约束，则自动创建唯一索引。 - 主键索引（PRIMARY KEY）
主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是非空、唯一的。当在查询中使用主键索引时，它还允许快速访问数据。 - 复合索引
在创建索引时，并不是只能对其中一列创建索引，与其创建主键一样，可以将多个列组合为索引，这种索引称为复合索引。
需要注意的是，只有在查询中使用了组合索引最左边的字段时，索引才会被使用，即第一个字段为前缀的集合。 - 全文索引（FULLTEXT）
全文索引的作用是在定义索引的列上支持值的全文查找，适合大型数据集，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型的列上创建，主要用于在大量文本文字中搜索字符串，此时使用全文索引的效率将大大高于使用SQL的LIKE关键字的效率。 - 空间索引
空间索引是对空间数据类型的列建立的索引，如GEOMETRY、POINT等。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
创建索引
- 建表添追加
- 建表后追加
-
ALERT TABLE 表名 ADD 索引类型（数据列名）
- 建表后追加
-
CREATE 索引类型 INDEX index_name table_name(column_name[length]......) index_name ：指定索引名。 table_name：指定需要创建索引的表名。 column_name：指定需要创建索引的列。 length：索引长度，可选。只有字符串类型才能指定索引长度，索引长度不能长于原字段长度。
- like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引 - 在列上进行运算会让索引失效。
删除索引
- DROP INDEX（或KEYS） table_name.index_name
- 删除表时，该表的所有索引将同时被删除。
- 删除表中的列时，如果删除的列时索引的组成部分，则该列也会从索引中删除。
查看索引
- 语法：SHOW INDEX FROM table_name[\G]
- 例如查询a表中的索引得出如下图：
- Table：表示创建索引的表
- Non_unique：表示索引是否唯一，1代表是非唯一索引，0代表唯一索引。
- Key_name：表示索引的名称
- Seq_in_index：表示该列在索引中的位置，如果索引是单列的，该值为1，组合索引为每列在索引定义中的顺序。
- Column_name：表示定义索引的列字段。
- Sub_part：表示索引的长度
- Null：表示该列是否能为空值
- Index_type：表示索引类型。
注：在查询语句后边加“ \G” ，表示将结果集按列显示。
什么地方创建索引
- 使用索引可以加快数据检索速度，但没有必要为每个列都建立索引。因为所以呢自身也需要维护，并占用一定的资源，可以按照以下标准选择建立索引
- 经常用作查询选择条件的列。
- 经常排序、分组的列。
- 经常用作连接的列（主键/外键）
- 查询时减少使用&*返回全部列，不要返回不需要的列。
- 索引应该尽量小，在字节数小的列上建立索引。
- WHERE子句中有多个条件表达式，包含索引列的表达式应置于其他条件表达式之前。
- 避免在ORDER BY 子句中使用表达式。
- 定期重新生成或重新组织索引，进行碎片整理。
数据库的备份和恢复
备份数据库
- mysqldump -u username -h host -p password dbname[tbname1 [,tbname2…]] > filename.sql
- 其中username：用户名
- host：主机地址、本机可以省略
- password：登录密码
- dbname：需要备份的数据库名
- tbname：需要备份的数据表，可以是多张表（如果备份整个数据库则可以省略此项）
- filename.sql：备份文件的名称
注：mysqldump 是DOS系统下的命令，无需进入MySQL命令行。
恢复数据库
- mysql -u username -p [daname] < filename.sql
注：再执行该语句之前，必须在Mysql服务器中创建新数据库，如果不存在新数据库，恢复数据库过程将会出错。 - 还可以通过复制文件进行数据库的备份和恢复，但需要注意复制文件前先执行FLUSH TANLE语句将所有数据写入导数据文件中，然后停止Mysql服务。
- 还可以通过可视化工具进行操作，相对简单。
Explain执行计划
执行计划是什么
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
- 语法：
Explain + SQL语句
- explain可以显示select，insert，update，delete，replace语句的信息
- explain和可解释性语句执行时，MySQL将显示来自优化器的有关执行语句的优化信息
- explain与不可解释的语句一起使用时，它将显示在命名连接中执行的语句的执行计划
- 对于select语句， explain产生可以使用来显示的其他执行计划信息
- explain对于检查涉及分区表的查询很有用
- 该FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出
执行计划的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
执行计划包含的信息
- id：表示执行顺序
- id相同，可认为是一组，执行顺序由上至下。
- id不同，如果是子查询，id会递增，id值越大，优先级越高。
- id相同、不同，同时存在。
-
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
查询类型分为以下几类：
-
table：显示这一行的数据是关于哪张表的
-
type：显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：ystem>const>eq_ref>ref>range>index>ALL
- system表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
- const表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
- range只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
- all Full Table Scan，将遍历全表以找到匹配的行。
- possible_keys：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
- key：此次查询确切使用到的索引。
- key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
- ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
- rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
- Extra：包含不适合在其他列中显示但十分重要的额外信息。
459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
