mysql中索引，触发器，事务，存储引擎的理解

1、索引：作用于表中的某列，并将其进行排序，有助于快速地进行查询。

   索引是对数据库表中一个或多个列的值进行排序的数据结构，是用于提高在数据库表中访问数据的速度的数据库对象。其实索引相当于一本书的目录，如果没有索引，要想在数据库中查找某一特定的值就需要遍历整个数据库表，但是有了索引之后就可以在索引当中查找，有助于更快地获取信息；

   索引可分为聚集索引和非聚集索引；

聚集索引：是按照数据存放的物理位置为顺序的；

非聚集索引中，表数据存储顺序与索引顺序无关；一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种；如果一张表没有聚集索引，那么它被称为“堆集”，这样的表中的数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。

  一条索引记录中包含的基本信息有：键值（定义索引时指定的所有字段的值）+逻辑指针（指向数据页或另一索引页）；根据数据库的功能，可以在数据库设计器中创建三种索引：

 

（1）唯一索引：不允许其中任何两行具有相同索引值的索引；

  当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。

使用语句为：

CREATE UNIQUE INDEX 索引名称

ON 表名称 (列名称，如果为多个列用逗号隔开)

对于一个简单索引的创建，只需将唯一索引中的UNIQUE去掉就可以了；

 

（2）主键索引：数据库表经常有一列或多列组合，其值唯一标识表中的每一行，该列称为表的主键；

 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问；

 

（3）聚集索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引；如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

 虽然说建立索引的目的是加快对表中记录的查找或排序，但是为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

 数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 ；其各有优缺点：

 

1）优点：

创建索引可以大大提高系统的性能

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

可以大大加快数据的检索速度，这也是创建索引的最主要的原因；

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 

2）缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

 

 因此，对于索引的使用和建立，应该视情况而定，比如对于那些查询次数很少或者数据值也比较少的列就不必要建立索引，因为不仅不能提高多少查询速度，反而会耗费一定的空间和降低系统的维护程度。

    语法格式如下：

 

相关命令：

查看一张表上的索引  

Show  index from  表名 \G 是以列来显示

建立索引 

Alter table 表名   add  [ index  / unique index / fulltext / primary key / ]   【索引名（可选）】  （列名）

Alter table member add index  tel (tel);    普通索引  

Alter table member add unique   (email)  唯一索引  

Alter table member add fulltext (intro)  全文索引   

主键  索引  

Alter table member add  primary key   （列名）  不用写索引名 因为索引就这一个  

Alter  table member add  primary key  (id)  主键  

删除索引  

Alter table  表名 drop index   索引名        最后是写索引名

Alter table member drop index intro   删除索引

但是怎么删除主键索引呢  主键索引 没有索引名 直接写primary key  就好了 

Alter table member drop  primary key

导入导出的时候 就先删除 表的索引 然后在集中建立索引

索引创建原则 

不要过度索引 

索引尽量散列值

在where条件最频繁的值上加索引

全文索引

在mysql  默认设定中  对中文意义不大 

如果是大文章的话  没有用全文索引  找一个词 要用 like 那是一行一行的找 效率 很低很低的  

全文索引查找方式

Select * from  member  where match(列名)  against('查询内容');

Select * from member where  match(initr) against('databases');   

一般很多词 都是停止词  所以索引不出来   

可以查看某个单词的匹配度

Select match('intro') against('data')   from   member;

全文索引在默认情况下对中文的意义不大，全文索引是针对文章中每一个词 做索引的 

 

 

 

2、触发器：是数据库在进行某种操作之前或之后进行的操作。

（1）触发器是一种特殊类型的存储过程，它在指定的表中的数据进行变化的时候自动生效；触发器是一个特殊的事务单元，可以引用其他表中的列执行特殊的业务规则或数据逻辑关系。

 当出现错误时，可以执行rollback transaction操作将整个触发器以及触发它的T-SQL语句一并回滚（不需显示声明begin transaction）；唤醒调用触发器以响应INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚，即撤销。

（2）触发器类型【两种】：

AFTER触发器：这种触发器将在数据变动（insert、update、delete动作）完成以后才触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚变动的数据；

INSTEAD OF触发器：这种触发器将在数据变动以前被触发，并取代变动数据的操作（insert、update、delete操作），转而去执行触发器定义的操作；

在建立触发器时，还必须指定触发操作：insert、update、delete操作，至少指定一种，也可指定多种；

 

 (3) 创建触发器：

 

 

3、事务：是一个或一组逻辑单元，由多个SQL语句组成，可以对数据库上的对象进行操作。

（1）事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。

（2）相关属性：

①原子性(Atomicity)：事务中的所有元素作为一个整体提交或回滚，事务的个元素是不可分的，事务是一个完整操作。
②一致性(Consistemcy)：事物完成时，数据必须是一致的，也就是说，和事物开始之前，数据存储中的数据处于一致状态。保证数据的无损。
③隔离性(Isolation)：对数据进行修改的多个事务是彼此隔离的。这表明事务必须是独立的，不应该以任何方式以来于或影响其他事务。
④持久性(Durability)：事务完成之后，它对于系统的影响是永久的，该修改即使出现系统故障也将一直保留，真实的修改了数据库。

（3）三种模型：

 

隐式事务是指每一条数据操作语句都自动地成为一个事务，事务的开始是隐式的，事务的结束有明确的标记；

 

显式事务是指有显式的开始和结束标记的事务，每个事务都有显式的开始和结束标记；

 

自动事务是系统自动默认的，开始和结束不用标记；

 

（4）使用事务的语句：

 

开始事物：BEGIN  TRANSACTION

 

提交事物：COMMIT  TRANSACTION

 

回滚事务：ROLLBACK  TRANSACTION

 

（5）事务的保存点：

 

SAVE  TRANSACTION  保存点名称 ——自定义保存点的名称和位置

 

ROLLBACK  TRANSACTION  保存点名称 ——回滚到自定义的保存点

 

 

4、存储引擎：

（1）MySQL 中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能 力，通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)；

 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及需要数据结合什么性能和功能的时候能提供最大的灵活性。

（2）常用的存储引擎：

I 、  MyISAM

特性
不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

 

适用场景
不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少（阻塞问题）
以读为主
数据一致性要求不是非常高

 

最佳实践
尽量索引（缓存机制）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

 

II、   InnoDB

特性
具有较好的事务支持：支持4个事务隔离级别，支持多版本读
行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性：能缓存索引，也能缓存数据
整个表和主键以Cluster方式存储，组成一颗平衡树
所有Secondary Index都会保存主键信息

 

适用场景
需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

 

最佳实践
主键尽可能小，避免给Secondary index带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
避免主键更新，因为这会带来大量的数据移动

III、    NDBCluster

特性
分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
支持事务：和Innodb一样，支持事务
可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

 

适用场景
具有非常高的并发需求
对单个请求的响应并不是非常的critical
查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

 

最佳实践
尽可能让查询简单，避免数据的跨节点传输
尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点
在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

 

注：以上三个存储引擎是目前相对主流的存储引擎，还有其他类似如：Memory，Merge，CSV，Archive等存储引擎的使用场景都相对较少。

 

查看当前数据库中各表的引擎：

创建一个新表时，可以通过在CREATE语句中ENGINE或TYPE选项来告诉MySQL要创建什么类型的表：

如果省略掉ENGINE或TYPE选项，默认的存储引擎被使用。当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为默认。当不可用的类型被指定时，自动用InnoDB表来替代。

使用ALTERTABLE语句，把表从一个类型转到另一个类型：