mysql知识点

事务：
1、属性：ACID
原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修 改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务 处理过程中的中间状态对外部是不可见的，反之亦然。
持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
2、并发事务带来的问题： 更新丢失(同时更新），脏读(一个更新，一个读），不可重复读（一个在修改或删除一个读），幻读（一个新加了满足条件的记录一个读）
3、事务的隔离级别：1、未提交读 2、已提交读 3、可重复读 4、可序列化
在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种：
1.在读取数据前，对其加锁，阻止其他事务对数据进行修改。
2.不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性：


引擎:不同的引擎有不一样的应用场景
MyISAM-----读取快，支持索引，字段管理，但不支持事务处理，外来键，容错等，5.5版本之前的默认引擎（表级锁）
三种不同的存储结构：静态型（没有变长的字段属性）、动态型、压缩型。
因碎片增加而降低数据访问性问题：
解决方法：1、尽可能使用静态数据类型
          2、经常使用optimize table table_name语句整理表的碎片
HEAP(MEMORY)-----拥有极高的插入、更新和查询效率，但不稳定
InnoDB-----使用于更新密集型，支持事务自动灾难恢复行级锁外键约束，5.5之后的默认引擎（行级锁）
Merge-----该引擎将一定数量的MyISAM表联合而成一个整体


索引：用于提高数据访问速度的数据库对象，适当的使用索引，可以很大的提高查询速度，但是会降低更新操作，主要有B+Tree索引（适合范围查找）和Hash索引（适合单记录查找）.
1、类型：
常规索引：常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。
主键索引：主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（ 如：编号），主键的数据类型最好是数值。
唯一索引：唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。
全文索引：全文索引（Full Text），可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。实际项目中，我们用到的是Coreseek。
外键索引：外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。
注意：只有InnoDB存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。 创建表的时候，可以给字段设置外键，如 foreign key(cate_id) references cms_cate(id)，由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。


2、方法：
B-Tree索引:不同的引擎有不一样的使用B-Tree，性能也不一样
哈希索引:哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。在MySQL中，只有Memory引擎显示支持哈希索引
空间数据索引（R-Tree）：MyISAM引擎支持空间索引，可以用作地理数据存储。和B-Tree索引不同，该索引无须前缀查询。
全文索引：全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几种索引的匹配方式完全不一样，它更类似于搜索引擎做的事情，而不是简单的where条件匹配。可以在相 同的列上，同时创建全文索引和B-Tree索引，全文索引适用于 Match Against 操作，而不是普通的where条件操作。


索引可以包含一个列（即字段）或多个列的值。如果索引包含多个列，一般会将其称作复合索引，此时，列的顺序就十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。


3、选择索引的数据类型
MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：


(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。


4、选择主键类型
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
(1)    整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。


(2)    字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。




5、Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引


PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` ) 


UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`col`)


INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name (`col`)


FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `col` )
组合索引   ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` ) 


Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。 
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。


组合索引最左字段用in是可以用到索引的，最好explain一下select。