mysql的引擎索引_mysql数据库索引和引擎

1. 数据库索引

1.1 索引作用

当我们在数据库表中查询数据时，若没有索引，会逐个遍历表格中的所有记录，表格中数据记录量大时很耗时。建立索引就像创建目录一样，直接通过索引找到数据存储位置，加快查找。例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。

如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。

但索引也不是越多越好，因为创建的索引也需要占用空间，而且需要维护索引，因此没必要为所有字段创建索引，对于经常需要查询，或数据记录很多的字段可以创建索引。

1.2 索引分类(index或key)

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换

MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

普通索引, INDEX：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引,UNIQUE：索引列中的值必须是唯一的，但是允许为空值，

主键索引， PRIMARY KEY：是一种特殊的唯一索引，不允许有空值。

组合索引: 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引 FULLTEXT :只有在MyISAM引擎上才能使用 (MySQL 5.6版本的InnoDB 开始支持全文索引)，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行.

空间索引 SPATIAL :只有在MyISAM引擎上才能使用(MySQL 5.7版本的InnoDB 开始支持)，空间索引是对空间数据类型(坐标，地理位置等)的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。创建空间索引的列，必须将其声明为NOT NULL

1.3 索引操作

1.3.1 创建索引(create index)：(创建索引后，通过SHOW CREATE TABLE tbname;能查看)

下面命令给titles表中的title列增加索引 (索引名称为idxtitle，未命名时默认为该字段名称)，有三种方式如下：

1，创建表格时指定：

CREATE TABLE titles(

title varchar(100),

publID INT,

INDEX idxtitle (title)

)；

2，创建索引： CREATE INDEX idxtitle ON titles(title)；

3，修改表设计，添加索引： ALTER TABLE titles ADD INDEX idxtitle (title)；

1.3.2增加不同的索引：

ALTER TABLE tablename ADD PRIMARY KEY (indexcols..);     不要信索引名称

ALTER TABLE  tablename ADD INDEX [indexname] (indexcols..);

ALTER TABLE tablename ADD UNIQUE [indexname] (indexcols..);

ALTER TABLE tablename ADD FULLTEXT [indexname] (indexcols..);

ALTER TABLE  tablename ADD INDEX [indexname] (indexcols1，indexcols2);   为indexcols1，indexcols2两列创建组合索引

1.3.3删除索引：

ALTER TABLE tablename DROP PRIMARY KEY ;

ALTER TABLE  tablename DROP INDEX [indexname] ;

1.4 索引性能测试：

先创建person表格，通过存储过程插入5000条数据

--创建表

CREATE TABLE person(id INT, name VARCHAR(16));--存储过程 (创建函数，delimiter $$：修改终止符号为$$)

delimiter $$

CREATE PROCEDURE autoinsert()

BEGIN

DECLARE i INT DEFAULT1;

WHILE(i<5000) DO

INSERT INTO person VALUES(i,‘zack‘);

SET i=i+1;

END WHILE;

END$$

delimiter ;--调用函数

CALL autoinsert();

创建person表

无index时查询：SELECT * FROM person WHERE id=4900;

添加index后查询：

ALTER TABLE person ADD INDEX index_id (id);

SELECT * FROM person WHERE id=4900;

可以看出有索引时查询时间变快。

2，mysql数据库引擎

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

数据库存储引擎，是mysql用来处理SQL语句的组件，对每一个数据表格，创建时都需要指定一个数据库引擎，mysql会使用该引擎来处理这个表格的相应操作，因此对于不同引擎的表格，SQL语句的处理结果和性能会不同。另外，对于一个数据库中的表格，每个表格都可以指定不同的数据库引擎。通过命令SHOW ENGINES;能查看mysql支持的数据库引擎。主要有InnoDB，MyISAM, Memory, CSV, Archve, Blackhole等，常用的就是InnoDB，MyISAM, Memory。其中通过Create table 命令默认设置的引擎为InnoDB。

2.1 设置数据表格引擎：

创建表格时指定：CREATE TABLE titles(id INT, name VARCHAR(16)) ENGINE = InnoDB;

创建后修改：ALTER TABLE titles ENGINE = InnoDB;

(SET default_storage_engine=NDBCLUSTER; 设置默认的引擎)

2.2 InnoDB特点

https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html

InnoDB支持事务操作，即commit，rollback和crash-recovery；

InnoDB支持行级锁，即可以给一行数据上锁；

InnoDB支持外键关系约束；

2.3 MyISAM特点

https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

MyISAM 适合读取操作较多的数据表，其读取速度较快；

MyISAM支持表级锁，可以给一张表上锁；

MyISAM支持全文索引；

MyISAM 支持Gometry，Point等表示空间位置的数据类型；

2.3 Memory：仅存在于内存中，多用于临时表格(hash index)

3.ODBC和JDBC

ODBC(open database connectivity): windows系统中数据库系统的一个驱动，基于ODBC的软件能够通过ODBC驱动来操控数据库中的数据。(如excel, access 能够通过ODBC连接MySQL 数据库，进行数据的增删改查

JDBC (java database connectivity)：unix和Linux系统上数据库系统的驱动。

参考博客：https://www.cnblogs.com/whgk/p/6179612.html

http://www.cnblogs.com/yuanchenqi/articles/6357507.html