MySQL索引篇

索引概述

索引（index）是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
索引是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度。 如果不使用索引，mysql必须从第一条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多

索引的优点是可以提高检索数据的速度
索引的缺点是创建和维护索引需要耗费时间，会减慢写入速度
索引可以用来改善性能，有时索引可能降低性能

索引的存储分类

BTree索引：最常见的索引类型，大部分存储引擎支持B树索引；
HASH索引：只有memory引擎支持，使用场景简单
Rtree索引（空间索引）：空间索引是myisam存储引擎的一种特殊索引类型，主要用于地理空间数据类型。
Fulltext索引（全文索引 ）：全文索引也是myisam的一种特殊索引类型，主要用于全文索引，InnoDB从mysql5.6版本提供全文索引的支持。

存储引擎

插件式存储引擎是mysql数据库最重要的特性之一，可以根据应用的需要选择如何存储和索引数据、是否使用事务等。用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，也可以自定义存储引擎。
存储引擎包括MyISAM、InnoDB、BDB、MEMORY等。MySQL5.5之前的默认存储引擎是MyISAM,5.5之后改为InnoDB。

1)查看当前数据库默认存储引擎类型
show variables like ‘table_type’;
2).查看当前数据库支持的存储引擎
show engines \G:
show variables like ‘have%’;
其中value显示的disabled表示支持该存储引擎，但是数据库启动的时候被禁用。

创建表的时候增加engine关键字设置新建表的存储引擎
create table ai(
id bigint(10)
)engine=myisam default charset=gbk;
修改
alter table ai engine=myisam;
查看表的存储引擎类型
show create table ai \G:

memory存储引擎
memory类型的表访问特别快，因为它的数据是放在内存中的，并且默认使用hash索引，但是一旦服务关闭，表中的数据就会失掉。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中。
在启动mysql服务的时候使用–init-file选项，把insert into ……select或load data infile这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。
create table tab_memory engine=memory select * from city group by city_id;
select count (*) from tab_memory;
show table status like ‘tab_memory’ \G；
给memory表创建索引（默认是hash索引，我们可以指定）
create index index_name using btree on table（name(20));
show index from table \G；
drop index index_name on table;

creaate index index_name using hash on table (name(20));
show index from table \G;
drop index index_name on table;
注意：前缀索引的限制应以字节为单位进行测量，而create table语句中的前缀长度解释为字符数。

索引分类

1. 普通索引：仅加速查询
2. 唯一索引：加速查询 + 列值唯一（可以有null）
3. 全文索引：对文本的内容进行分词，进行搜索
4. 主键索引：加速查询 + 列值唯一 +表中只有一个（不可以有null）
5. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
6. 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

创建普通索引：CREATE INDEX 索引名 ON 表名(列名);
创建唯一索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名);
创建主键索引：ALTER TABLE 表名 ADD PRIMARY KEY (id);
组合索引，创建方法基本一致 ALTER TABLE 表名 ADD INDEX 表名 (列名1,列名2,列名3);
查看索引：SHOW INDEX FROM 表名;
删除索引：DROP INDEX 索引名 ON 索引表名;

无论是否有索引，limit分页都是一个值得关注的问题