MYSQL 索引 优化以及延伸

一：索引(mysql新建表时默认主键为索引)

4.1：数据库事物： 是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。在关系型数据库中，一个事物可以是一个SQL，一组SQL或者一个程序，事物是用来保证数据更新在某一环节失败，而恢复操作前的状态。
比如事物作用于方法上，当方法执行失败，事物就会回滚到原来的状态。

4.1.1：原子性： 事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
4.1.2：一致性： 是指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。保证数据库一致性是指当事务完成时，必须使所有数据都具有一致的状态。在操作表时，dbms会根据操作的表设置一张临时表保存操作前未提交的数据提供使用，保证数据一致性。
4.1.3：隔离性： 事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立。

脏读： A事务读取B事务尚未提交的更改数据，并在这个数据的基础上操作。如果恰巧B事务回滚，那么A事务读到的数据根本是不被承认的。
不可重复读： 不可重复读是指A事务读取了B事务已经提交的更改数据 幻象读： A事务读取B事务提交的新增数据，这时A事务将出现幻象读的问题，
幻象读一般发生在计算统计数据的事务中幻象读和不可重复读是两个容易混淆的概念，前者是指读到了其它已经提交事务的新增数据，而后者是指读到了已经提交事务的更改数据（更改或删除），为了避免这两种情况，采取的对策是不同的，防止读取到更改数据，只需要对操作的数据添加行级锁，阻止操作中的数据发生变化，而防止读取到新增数据，则往往需要添加表级锁——将整个表锁定，防止新增数据。
第一类丢失更新 ： A事务撤销时，把已经提交的B事务的更新数据覆盖了 第二类丢失更新 ：
　A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失

4.1.4：持久性： 事务结束后，事务处理的结果必须能够得到固化。

4.2：MYSQL数据库引擎 数据库引擎是用于存储、处理和保护数据的核心服务。

4.2.1：ISAM： ISAM引擎建立的表数据查询的次数远远大于更新的次数，所以，执行操作查询的速度很快，不用占用大量的内存和存储资源，但是它不支持事物。

4.2.2：MYISAM MYISAM是MYSQL的ISAM扩展格式和默认的数据库引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT
COUNT(*) FROM
TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。除了提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE
TABLE命令，来恢复被更新机制所浪费的空间。
MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。

4.2.3：Innodb引擎 Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT
COUNT(*) FROM
TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

1.普通索引：
基本的索引，它没有任何限制
创建：
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

删除：
DROP INDEX index_name ON table
2：唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

创建：
CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

3：组合索引（最左前缀）
SQL查询有时查询多列为条件，就可以建立组合索引
创建：
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。

4：聚簇索引、非聚簇索引
索引是存储引擎负责实现，所以不是所有的存储引擎都支持聚簇索引，InnoDB存储引擎支持聚簇索引。索引（Index）是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引

MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

INNODB引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚族索引。这个字段长度为6个字节，类型为长整形。一个表只能有一个聚簇索引,并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

4.3.1：两种类型其实是两种不同的存储方式。
4.3.2：MYSQL中没有区分，主键索引就是聚簇索引，