Mysql数据库索引

索引的概念

1. 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。
2. 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
3. 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
4. 索引是表中一列或者若干列值排序的方法。
5. 建立索引的目的是加快对表中记录的查找或排序。（加快查询速度、对字段值进行排序）

索引的作用

优点

1. 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
2. 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
3. 可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。
4. 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
5. 可以加快表与表之间的连接。
6. 使用分组和排序时，可大大减少分组和排序的时间。
7. 建立索引在搜索和恢复数据库中的数据时能显著提高性能。

缺点

1、索引需要占用额外的磁盘空间。

• 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
• 而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）

2、在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

索引的分类和创建

table member(id int(10) ,name varchar(10) ,cardid int(18) ,phone int(11) ,address varchar(50),remark text);

 普通索引

create index 索引名 on 表名 (字段);       
alter table 表名 add index 索引名 (字段);

mysql> create index name_index on member(name);    #以name字段创建普通索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  | MUL | NULL    |       |
| cardid  | int(18)     | YES  |     | NULL    |       |
| phone   | int(11)     | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> create index cardid_index on member(cardid(4));  #指定cardid字段值的前4个字符做普通索引的值
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  | MUL | NULL    |       |
| cardid  | int(18)     | YES  | MUL | NULL    |       |
| phone   | int(11)     | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> show create table member;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                      |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| member | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" int(18) DEFAULT NULL,
  "phone" int(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "name_index" ("name"),
  KEY "cardid_index" ("cardid")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

唯一索引

create unique index 索引名 on 表名 (字段);    
alter table 表名 add unique 索引名 (字段);

 主键索引  

alter table 表名 add primary key (字段);

 组合索引

reate unique index 索引名 on 表名 (字段1, 字段2, 字段3);    
lter table 表名 add index 索引名 (字段1, 字段2, 字段3);

 全文索引

create fulltext index 索引名 on 表名 (字段);       
alter table 表名 add fulltext 索引名 (字段);

 

删除索引

除了删除主键索引，删除其他索引的方式是一样的。

删除主键索引（即删除主键）

 alter table 表名 drop primary key;  

 删除其他索引

 drop index 索引名 on 表名;     #直接删除索引
 ​alter table 表名 drop index 索引名;   #修改表的方式删除索引