MySQL--索引

索引：
   索引是将数据库表中一列或多列的值按照一定的数据结构进行排序，一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。例如 employee 表的姓（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。

   数据库中的索引：将数据库字段中具有检索意义的字段按照一定的数据结构进行编排，并标明关键信息和所在的物理地址（就像书目录的页码一样，其形式就是 标题 物理地址）。

   索引的优点：查询的效率相当高。缺点需要占用磁盘空间，在删除或更新数据时索引需要动态维护，这需要花费时间。
MySQL的索引分为：普通索引，唯一性索引，全文索引，单列索引，多列索引，空间索引。
   1、普通索引：创建普通性索引时，不需要附加限制条件，其约束由字段本身的约束条件决定。
   2、唯一性索引：创建在使用UNIQUE约束的字段上，也就是该索引的必须是唯一的，主键就是特殊的唯一性索引。
   3、全文索引：只能创建在CHAR VARCHAR TEXT类型的字段上，查询较大字符串类型的字段，使用全文索引可以提高检索效率。
   4、单列索引：对单个字段创建的索引，就叫单列索引。
   5、多列索引：就是对多个字段创建的索引，但是只有当查询条件中使用了这些字段中的第一个字段时该索引才会被使用。

创建索引的原则：
    1、选择唯一性索引：也就是看看表中的字段那个一个字段或多个字段的组合具有唯一性，然后为该字段或这多个字段建立索引。
例如：学生信息表中，我们就应该选择学号字段来创建这个索引。
    2、为经常需要排序、分组和联合操作的字段创建索引。
    3、为经常作为查询条件的字段创建索引。
    4、限制索引数目： 索引需要占用一定的磁盘空间，而且在更新表和删除表的时候需要花时间去动态维护索引。
    5、尽量使用数据量少的索引。
    6、尽量使用前缀来索引。
    7、定期删除不用的索引文件。
我自己外加一点：对于一些表我们需要加入额外的字段，去满足索引的条件，例如约束为AUTO_INCREMENT的字段，而正如Ben Forta所说我们应该总是创建主键。
   
索引创建的方式：
1、创建表的时候创建索引
①一个普通索引：
 CREATE TABLE index_1 -- general index
(
    ID INT NOT NULL,
    name VARCHAR(20),
    INDEX (ID)     -- Create index for ID field.
);
②一个唯一性索引
CREATE TABLE index_2 -- unique index
(
    ID INT NOT NULL,
    name VARCHAR(20),
    CONSTRAINT con_id UNIQUE(ID),  -- 对需要创建唯一性索引的字段设置唯一性约束。
    UNIQUE INDEX index_1(ID DESC)     -- Create unique index for ID field. 对索引排序一下
);
③全文索引：
CREATE TABLE index_3 -- unique index
(
    ID INT NOT NULL,
    name VARCHAR(20), 
    CONSTRAINT con_id UNIQUE(ID),
    FULLTEXT INDEX index_3(name)-- Create fulltext index for ID field.
)ENGINE=MyISAM;  -- engine is MyISAM,becaues other engine doesn't support fulltext.
⑤单列索引：
CREATE TABLE index4
(
   name VARCHAR(20) NOT NULL,
   INDEX index4 (name(10))  -- 使用name的几个前缀字符创建索引。
);
⑥多列索引  -- 在检索用户名和密码时用得较多。
CREATE TABLE index4
(
   ID INT NOT NULL,
   name VARCHAR(20) NOT NULL,
   INDEX index4 (ID,name(10))
);
EXPLAIN SELECT name FROM index4 WHERE ID=101 AND name = 'Jack Warte 6';

2、在已经存在的表上创建索引
语法 CREATE [FULLTEXT|UNIQUE] INDEX xx（索引名） ON xx（表名） (xx);
ALTER TABLE xx(表名) ADD INDEX xx(索引名) （xx(字段)，……）；

3、删除索引
语法：DROP INDEX xx(索引名) ON xx(表名);

索引、主键和唯一性的区别：
   索引是建立在一个或多个字段上的，创建索引后表的数据就按索引的一定规则排列。用于提高查询速度。
   主键是一个表中特定记录的唯一标识，用来唯一标识一个记录（行），在创建主键时DBMS会自动为这个主键字段建立一个唯一性索引。
表中导入大量数据时为什么会很慢？
 在已经建立索引的表中插入数据时，每插入一条记录，DBMS按照表中的索引对这条记录进行排序，所以插入速度会很慢。解决这个问题的方法是：先插入数据，在创建索引。

视图：
   什么是视图？作用是什么？怎样使用？
视图是虚拟的表，与包含数据的表不一样，视图只包含使用动态检索数据的查询（也就是视图的定义），他不包含表中应该有的任何列或数据，TA包含的是SQL查询。从存在磁盘上的视图文件来看确实是这样。视图封装了SQL语句。（其实就是把那个中间表换一种说法：视图），对这个视图的操作大多数和表一样，具体的查一下资料。
   视图的一些常见应用：
   1、重用SQL语句。
   2、简化复杂的SQL操作。
   3、使用的是表的一部分，而不是整个表。
   4、保护数据，可以给用户授予特定部分的访问权限而不是整个表的访问权限。

触发器：
什么是触发器，为什么要用触发器，怎样用？
1、触发器：是MySQL响应DELETE INSERT UPDATA而自动执行的一条MySQL语句（或位于BIGIN 和 END 语句之间的一组语句）。
2、创建触发器：
在创建触发器时，需要给出4条信息。
①唯一的触发器名。
②触发器关联的表。
③触发器应该响应的活动。
④触发器何时执行。
语法1触发一条语句：CREATE TRIGGER xx（触发器名） AFTER|BEFORE DELETE|INSERT|UPDATA ON xx（表名） FOR EACH ROW xx（执行的语句）；
语法2同时触发多条语句：CREATE TRIGGER xx（触发器名） AFTER|BEFORE DELETE|INSERT|UPDATA ON xx（表名） FOR EACH ROW
       BEGIN
       xx;
       ……;
       END;

 

管理事务处理（Manage Transaction processing）
   最经典的一个例子：银行转账
   如果A账户中的金额刚被发出，而B账户还没来得及接收就发生停电，这会给银行和个人带来很大的经济损失。采用事务处理机制，一旦在转账过程中发生意外，则程序将回滚，撤销之前的转账操作。
    一个事务以START TRANSACTION开始，以COMMIT 和 ROLLBACK结束。
   一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。如果事务遇到错误必须取消或回滚，则这个事务中的所有之前成功操作都将被撤销。

    事务处理是一种机制，用来管理必须成批执行的Mysql操作，以保证数据库不包含不完全的操作。利用事务处理可以保证一组操作不会中途停止，他们或者作为整体执行，或者完全不执行。如果没有错误发生，则将执行整个语句，如果在执行中途发生错误则进行回退，也就是撤销之前的操作，以保证数据库恢复到一个安全的状态。

  Transaction processint用来管理 INSERT UPDATE DELETE,你不能回退SELECT，不能回退CREATE DROP，事务处理块中可以使用这两个语句，但如果执行回退CREATE DROP不会撤销操作。
 
 ①主动回退： ROLLBACK:从ROLLBACK回退到START TRANSACTION出。也就是撤销所有在事务语句块中执行的INSERT UPDATE DELETE语句。
 ②如果执行过程中出错，则系统自动回退。
 ③如果我们在事务处理语句块中使用了COMMIT(提交)，在提交语句COMMIT和START TRANSACTION之间的INSERT UPDATE DELETE语句如果执行过程中没有出错，则提交之后提交语句COMMIT和START TRANSACTION之间的INSERT UPDATE DELETE语句将不能被撤销。如果执行过程中出错，系统自动撤销COMMIT和START TRANSACTION之间的INSERT UPDATE DELETE语句，也就不会提交。