MySQL原理:存储引擎、索引和事务

一、存储引擎

MySQL里的存储引擎，“存储”指的是存储数据，“引擎”是发动机的核心部分,习惯上常用引擎来代指发动机，这里指数据库的核心。

简单来说，存储引擎就是数据的存储结构，由实际业务决定。

MySQL常见的四种存储引擎：

1.MyISAM

不支持事务，支持全文索引，不支持外键，B+树，表锁，对于一些在线分析处理操作速度快。数据和索引分离。

文件组成由 myd  存放数据的  myi存放索引的

2.InnoDB

支持事务，不支持全文索引，支持外键，B+树，行锁，主要是面向在线事务处理方面的应用，特点是行锁设计，并支持外键。Innodb采用聚集索引的方式。没有主键，没有唯一键，为每一行生产一个6字节的行id,作为主键。索引当作数据的一部分存储。

3.Memory

将数据放在内存中，如果数据库重启或者宕机，表数据就会丢失。非常适合存储一些临时表，默认的是哈希索引，不是B+树索引，varchar()默认是按照char()存储的，浪费内存。

不支持text和BLOB类型。varchar当成char类型处理。如果数据中有text和BLOB类型，数据库会把这些数字转换到磁盘上。

4.Archive

只支持INSERT和SELECT操作，一般用于日志处理，使用压缩算法将数据进行压缩后存储，压缩比例一般是1:10，主要提供插入和压缩功能。

二、索引

关于MySQL索引的好处，如果把正确合理设计并且使用索引的MySQL比作是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。其多个数据表都会对经常被查询的字段添加索引

MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？（一般数据库默认都会为主键生成索引）。

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL索引的类型

1. 普通索引

这是最基本的索引，它没有任何限制， MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

01	–直接创建索引
02	CREATE INDEX index_name ON table(column(length))

 

03	–修改表结构的方式添加索引
04	ALTER TABLE table_name ADD INDEX index_name ON (column(length))

 

05	–创建表的时候同时创建索引
06	CREATE TABLE `table` (

 

07		`id` int(11) NOT NULL AUTO_INCREMENT ,
08	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

 

09	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
10	`time` int(10) NULL DEFAULT NULL ,

 

11	PRIMARY KEY (`id`),
12	INDEX index_name (title(length))

 

13	)
14	–删除索引

 

15	DROP INDEX index_name ON table

2. 唯一索引

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

01	–创建唯一索引
02	CREATE UNIQUE INDEX indexName ON table(column(length))

 

03	–修改表结构
04	ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

 

05	–创建表的时候直接指定
06	CREATE TABLE `table` (

 

07		`id` int(11) NOT NULL AUTO_INCREMENT ,
08	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NO