Mysql数据库索引作用及分类

1.索引的概念:
索引是一种特殊的文件，包含着对数据表中所有的记录的引用指针，数据库索引好比是一本书前面的目录，能加快数据库的查询速度，数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

2.索引的作用：
建立索引的目的是加快对表中记录的查找或排序，为表设置索引要付出代价；一是增加了数据库的存储空间，二是在插入和修改数据时要花费更多的时间（因为索引也会随之改变）。
（1），设置合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
（2）当表很大的时候，或者查询涉及到多个表时，使用索引可以使查询速度快上成千倍。
（3）可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
（4）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（5）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。
3.索引的分类
MySQL的索引分为以下几类：
（1）普通索引，这是最基本的索引类型，而且它没有唯一性之类的限制。
（2）唯一索引，这种索引和前面的‘普通索引’基本相同，但是有一个区别；索引列表的所有值只能出现一次，即必须唯一，当现有数据中存在重复的键值时，大多数数据库都不允许新创建的唯一索引与表一起保存，数据库还可以防止添加将再表中创建重复键值的新数据。
（3）主键索引，主键索引时一种唯一性索引，但它必须指定为‘PRIMARY KEY（字段）’在数据库中为表定义主键将自动创建主键索引，主键索引是唯一的特定类型，该索引要求主键中的每一个值都唯一。
（4）全文索引，索引类型为（FULLTEXT），全文索引可以在CHAR VARCHAR 或者TEXT类型的列上创建。
（5）单列索引与多列索引，索引可以是单列上创建索引，也可以是在多列上创建的索引，多列索引可以区分其中一列可能有相同的行。如果经常同时搜索两列或者多列排序时，索引也时很有帮助的。
4.创建索引的原则依据。
索引可以提升数据库查询的速度，因为索引会占用数据库的系统资源不是任何情况下都需要建立索引的，数据库查询会先查询索引查询。
（1）表的主键、外建必须有索引，主键具有唯一性，索引值也是唯一性，查询时可以快速定位到数据行，外键一般关联的是另一个表的主键，所有在多表查询时也可以快速定位。
（2）数据量过300行的表应该有索引，数据量较大的时候，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。
（3）经常与其他表进行表连接的表，在连接字段上应该建立索引。
（4）唯一性太差的字段不适合建立索引，如果索引字段的数据唯一性太差，是不适合创建索引。
（5）更新太频繁的字段不合适创建索引，在表中进行增加、删除、修改操作时，索引也有相应操作产生、字段更新得过于频繁，对于系统资源占用也会更多。
（6）经常出现在where（条件判断）字句中的字段，特别是大表的字段，应该建立索引。
（7）索引应该建立在选择性高的字段上，如果很少的字段拥有相同值，即有很多独特值，则选择性很高。
（8）索引应该建在小字段上，对于大的文本段甚至超长字段，不要建索引。

5.创建索引的方法；
（1）创建普通索引，命令格式是：
CREATE INDEX (索引名字)ON tablename(列的列表)

mysql> create index salary_index （索引名）on sys.IT_salary(name)（那个库的表里的字段）;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from IT_salary;查看索引方法

explain select * from IT_salary;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | IT_salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

各属性含义：
id: 查询的序列号
select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询
SIMPLE：查询中不包含子查询或者UNION
查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
table: 输出的行所引用的表
type: 访问类型

从左至右，性能由差到好

1.ALL: 扫描全表
2.index: 扫描全部索引树
3.range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
4.ref: 使用非唯一索引或非唯一索引前缀进行的查找
（eq_ref和const的区别：）
5.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
6.const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。
7.NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1
possible_keys: 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引
key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL
key_len: 使用到索引字段的长度
注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
1.
.Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
.Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。
Key_name对应的是索引名字，显示名字是salary_index的索引已经存在，NOnunique对应值是1，表示不是唯一性索引。

（2）创建唯一索引，命令格式：

CREATE UNIQUE INDEX(索引名字) ON tablename（表名）（列的列表，字段）；

该索引比普通索引多了一个关键字UNIQUE关键字。

mysql> create unique index salary （索引名字）on IT_salary（表）(id);//将这个表中的ID号设置为唯一索引名字为salary
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

它的NOn_unique值为0表示唯一索引’
（3）创建主机索引两种方式
1.1：一种是在创建表的同时创建主键，主键索引会自动创建，命令格式：

CREATE TABLE tablename（表名）([...],PRIMARY KEY(列的列表))；

1.2：已经创建了表，没有指定主键，然后修改表加入主键，主键索引会自动创建命令如下：

ALTER TABLE （表名） ADD PRIMARY KEY(列的类表，字段)；

（4）在mysql中使用全文索引（FULLTEXT index）,目前只有使用myiSAM类型表的时候有效（msISAM是默认的表类型）全文索引建立在TEXT、CHAR或者VARVHAR类型的字段或者字段组合上，创建表时指定或修改表时指定全文搜索，命令如下：

CREATE TABLE 表名(列名 TEXT,FULLLTEXT(列名)) enginde=MyISAM;

ALTER TABLE 表名 ADD FULLTEXT（列名）；

CREATE TABLE 表名(列名字1 TEXT,FULLTEXT(列名1)) engine=MyISAM;

(5)多列索引只需要在创建索引时指定多少列即可；

mysql> create index salary_age_index （索引名字）on IT_salary(age列1,type列2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

（6）不使用索引时，应该删除索引

1.DROP INDEX 索引名 ON 表名;删除索引
2.ALTER TABLE  表名 DROP INDEX l列;
3.ALTER TABLE 变名 DROP PRIMARY KEY;

第一条命令是直接删除索引，第二条命令是修改表时删除索引，第三条是删除主键索引。
1.第一种直接删除索引。

2.修改表时删除索引。

3.删除主键索引。

（7）查看索引方法：

SHOW INDEX FROM 表名；或者SHOW KEYS FROM 表名。