mysql 索引 和mysql 的引擎
2017-10-14 10:11 晨曦曙光 阅读(333) 评论(0) 编辑 收藏 举报1.索引的特点
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
2.索引的优点
第一， 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二， 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三， 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四， 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五， 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
3.索引的缺点
也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。
第一， 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二， 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三， 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4.创建索引的准则（应该在哪些列上创建索引）
第一， 在经常需要搜索的列上，可以加快搜索的速度；
第二， 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
第三， 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
第四， 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
第五， 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
第六， 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
5.不应该在哪些列上创建索引
第一， 对于那些在查询中很少使用或者参考的列不应该创建索引
第二， 对于那些只有很少数据值的列也不应该增加索引
第三， 对于那些定义为text, image和bit数据类型的列不应该增加索引
第 四， 当修改性能远远大于检索性能时，不应该创建索引
6.通过代码比较有无索引的不同
例：创建一个student的数据表向里面插入1000000条数据
DELIMITER //
create procedure proc3()
begin
declare var int default 1;
while var <=1000000
do insert student values (null,'王尼玛',18);
set var = var +1;
end while;
end //
DELIMITER ;
call proc3();
//把第1276条数据的姓名改为‘张全蛋’；
update student set name ='张全蛋'where id = 1276;
7.创建索引
create index index_name on student(name);
//id和name字段都有索引之后，查询速度相当
8.创建主键索引的3种方式
方式-：
create table student (
id INT NOT NULL,
name VARCHAR(16)NOT NULL,
PRIMARY KEY (id)
);
方式二：
CREATE INDEX index_name ON student (name);
方式三：
ALTER student ADD PRIMARY KEY [indexName]ON (id);
9.普通索引
这是最基本的mysql 数据索引
它没有任何限制
它有以下几种创建方式：
9.1创建索引
create index index_name ON tableName(name[length]);
注意： 如果是CHAR ,VARCHAR 类型，length可以小于字段实际长度；如果是BLOB 和TEXT 类型，必须指定length相同；
9.2修改表结构体
ALTER tableName ADD INDEX [indexName]ON (name);
9.3创建表的时候直接指定
CREATE TABLE tableName(
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
INDEX [index_name](name[length])
);
10.唯一索引
他与前面的普通索引类是，不同就是：
mysql 数据库索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。
10.1创建索引
create UNIQUE index index_name ON tableName(name[length]);
10.2修改表结构体
ALTER tableName ADD UNIQUE [indexName]ON (name);
10.3创建表的时候直接指定
CREATE TABLE tableName(
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
UNIQUE[index_name](name[length])
);
11.全文索引（MyISAM 引擎下才能使用）需要先修改引擎
//修改mysql引擎
CREATE TABLE tableName(
id INT（11） NOT NULL,
name VARCHAR(16) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM;
例： 给name创建全文索引
CREATE FULLTEXT INDEX indexName ON tableName(name[length]);
12.删除索引的语法
方式一：
ALTER TABLE 表名 DROP INDEX 索引名;
方式二：
DROP INDEX [索引名]ON 表名;
13.查看数据表中的索引方法
show index from student;
14.组合索引
CREATE TABLE mytable(
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL,
age INT NOT NULL
);
-- 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表时，name 16，这里用 10。
这是因为一般情况下名字的长度不会超过10，
这样会加速索引查询速度，
还会减少索引文件的大小，
提高INSERT的更新速度。
如果分别在 name，city，age上建立单列索引，
让该表有3个单列索引，
查询时和上述的组合索引效率也会大不一样，
远远低于我们的组合索引。
虽然此时有了三个索引，
但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
15. mysql中的引擎有
ISAM 、 MyISAM、 HEAP（也称为MEMORY）、 BLACKHOLE（黑洞引擎）、 InnoDB
