MySQL——存储引擎、索引

一、存储引擎<基于表>

1.MyISAM

底层实现：B+树；不支持外键，不支持事务，支持全文索引；非聚集索引；表锁；.myi(index);.myd(data)

2.InnoDB

底层实现：B+树；支持外键，支持事务，不支持全文索引；聚集索引；行锁；

3.MEMORY

“内存”存储；临时表；varchar当作char来处理；当遇到TEXT BOLB关键字不识别，交由MyISAM处理；

4.ARCHIVE

归档；适用于插入数据量大，且不经常查询（日志文件）；不支持索引；只支持insert,select

二、索引<基于字段>

查看索引命令：show index from table_name;

mysql > show index from table_name;
mysql > show keys from table_name;
/*
. Table　　表的名称;
· Non_unique    如果索引不能包括重复词，则为0。如果可以，则为1;
· Key_name    索引的名称;
· Seq_in_index     索引中的列序列号，从1开始;
· Column_name   列名称;
· Collation   列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）;
· Cardinality  索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大;
· Sub_part   如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL;
· Packed   指示关键字如何被压缩。如果没有被压缩，则为NULL;
· Null  如果列含有NULL，则含有YES。如果没有，则该列含有NO;
· Index_type    用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）;
· Comment
*/

由于支持索引的存储引擎只有MyISAM和InnoDB,索引针对这两个来分析索引；

1.MyISAM（数据和索引分离开来）

非聚集索引。由于底层实现是B+树，叶子节点存放的是真实数据的地址；

主索引：key不允许重复；

辅助索引：允许重复（叶子节点也是存储真实数据的地址）；

2.InnoDB（索引当作数据的一部分来处理）

聚集索引。底层实现：B+树，叶子节点存放的是真实数据；

主索引：key不允许重复；

辅助索引：叶子节点存放主索引的索引值，通过主索引值找到真实数据；

辅助索引查询过程：（1）从辅助索引找到具体的主索引索引值；（2）从主索引找到真实数据；

InnoDB简历索引的原则：（1）先看有没有主键；（2）再看有没有唯一键；（3）最后使用隐藏字段(6字节) ，此字段不可见（autoincreament 自增长类型）也就是插入一个数据它会自主进行++操作；

三、索引的分类(创建)

在执行create table语句时可以创建索引,或者可以用这两个语句创建索引：（1）create index（2）alter table

1、普通索引：key可以重复

2、主键索引：PRIMARY KEY

3、唯一索引：UNIQUE

4、全文索引（MyISAM支持）：FULLTEXT

5、组合索引（多个字段构成的索引：最左前缀原则）

/*
table_name是要增加索引的表名
column_list指出对哪些列进行索引
多列时各列之间用逗号分隔
索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称
alter table允许在单个语句中更改多个表，因此可以在同时创建多个索引。
 */
--1.添加一个普通索引
alter table table_name add index index_name (column_list);
--2.添加一个唯一索引
alter table table_name add UNIQUE (column_list);
--3.添加一个主键索引
alter table table_name add PRIMARY KEY(column_list);
--4.添加FULLTEXT(全文索引) 
alter table table_name add FULLTEXT (column_list);
--5.添加多列索引 
alter table table_name table index index_name (column_list1,column_list2,column_list3);

/*
create index可对表增加普通索引或UNIQUE索引
不能用create insex语句创建PRIMARY KEY索引
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义
索引名不可选
*/
--添加一个普通索引 
create index index_name on table_name (column_list)
--添加一个唯一索引
create UNIQUE index index_name on table_name (column_list)
 

删除索引：

可利用alter table或drop index语句来删除索引。类似于create index语句，drop index可以在alter table内部作为一条语句处理，语法如下。

--删除掉table_name中的索引index_name。
drop index index_name on talbe_name;
--等价于
alter table table_name drop index index_name;

--在删除PRIMARY KEY索引时使用
--因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
--如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
alter table table_name drop PRIMARY KEY

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

四、索引优化

那种情况应该建立索引

（1）表的主关键字：自动建立唯一索引

（2）表的字段唯一约束:ORACLE利用索引来保证数据的完整性

（3）直接条件查询的字段

（4）查询中与其它表关联的字段

（5）查询中排序的字段

（6）查询中统计或分组统计的字段

那种情况不应建立索引

（1）表记录太少

（2）经常插入、删除、修改的表

（3）数据重复且分布平均的表字段

（4）经常和主字段一块查询但主字段索引值比较多的表字段

五、索引应该注意的事项

（1）应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

（2）索引值不能为空：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

（3）在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

（4）创建索引：对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

（5）复合索引：比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（6）使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

（7）排序的索引问题：mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

（8）like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

（9）不要在列上进行运算

（10）不使用not in和操作：not in和操作都不会使用索引,将进行全表扫描。not in可以noy exists代替.