Mysql索引

阅读目录

• 索引简单使用
• 什么是索引？
• 1、索引原理
• 2、索引的应用

回到顶部

索引简单使用

--添加唯一索引
CREATE UNIQUE INDEX AUTH_NAME ON SHOP_AUTH(CODE);
--添加普通索引
CREATE INDEX AUTH_NAME ON SHOP_AUTH(NAME);
--添加全文索引
CREATE FULLTEXT INDEX AUTH_AUTH ON SHOP_AUTH(CONTENT);
--添加组合索引
CREATE INDEX AUTH_AUTH ON AUTH_AUTH(ID,NAME);
--删除索引
DROP INDEX<索引名>ON<表名>

注意

• 组合索引只会在当索引内字段同时出现的时候，才会走索引
• 全文索引 可以看 什么是全文索引

回到顶部

什么是索引？

索引的简单介绍：
MySQL索引的底层数据结构是B+树 B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构

** 例: **
在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

索引的优点如下：

通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

可以给所有的 MySQL 列类型设置索引。

可以大大加快数据的查询速度，这是使用索引最主要的原因。

在实现数据的参考完整性方面可以加速表与表之间的连接。

在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

注：
索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

回到顶部

1、索引原理

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是用B+Tree作为索引结构
（主键，unique 都会默认的添加索引）

回到顶部

2、索引的应用

2.1、创建索引

如果未使用索引，我们查询 工资大于 1500的会执行全表扫描

什么时候需要给字段添加索引：
-表中该字段中的数据量庞大
-经常被检索，经常出现在where子句中的字段
-经常被DML操作的字段不建议添加索引

索引等同于一本书的目录
主键会自动添加索引，所以尽量根据主键查询效率较高。

如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：

1、create unique index 索引名 on 表名(列名); 
create unique index u_ename on emp(ename);
2、alter table 表名 add unique index 索引名 (列名); 
create index test_index on emp (sal);

2.2、查看索引

show index from emp;

2.3、使用索引

注意一定不可以用select * … 可以看到type!=all了，说明使用了索引
explain select sal from emp where sal > 1500;
条件中的sal使用了索引

如下图：假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出7行，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。

2.4、删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引， 
mysql> ALTER TABLE EMP DROP INDEX test_index;

删除后就不再使用索引了，查询会执行全表扫描。