MySQL索引

1.提升查询效率

 

 类似字典，比如：年龄为20的都在第几页，把20和页码都存放在索引中

下一次查询时，不从第一页，一条一条的查询，而是根据索引信息快速的查询出数据。

索引本身也要耗费存储空间的，跟字典中的拼音查询、部首查询占字典的页数一样。

2.其他的增删改，效率降低

索引还会浪费增删改效率，除了查询效率高了以外，其他的增删改效率都低了。（数据页的数据改动也需要改动索引页相对应的数据）

3.MySQL索引类型

3.1 普通索引

 基本索引类型；允许在定义索引的列中插入重复值和空值。

3.2 唯一索引

索引列数据不重复；允许空值。

在创建unique唯一约束的时候，数据库会自动创建。

3.3 主键索引

主键列中的每个值是非空、唯一的；一个主键将自动创建主键索引。

创建primary key 主键的时候，数据库会自动创建。

3.4 复合索引

将多个列组合作为索引。

例：如果经常名字和年龄查询数据时，就可以建复合索引。（同时创建在多列上）

3.5 全文索引（全库）

支持值的全文查找；允许重复值和空值。

InnoDB不支持全文索引，（如果一个数据页的一个修改，就会修改索引页信息）

Myisam支持全文索引

3.6 空间索引（全磁盘空间）

对空间数据类型的列建立的索引。

4.索引（语法）

4.1 创建索引

//默认是普通索引
//唯一索引、全文索引、空间索引。可选
create [unique|fulltext|spatial] index index_name
on table_name (column_name[length]...);

 

示例：在student表的studentName列创建普通索引

#创建索引
create index i_studentname
on student(studentname);

#查所有数据时不使用索引
select * from student；

#使用，当索引项做条件时使用了索引
select * from student where studentname='郑秀妍';#使用索引i_studentname

#按照其他条件来查时，不使用索引
select * from student where password='123456';

 

 

4.2 删除索引

drop index index_name on table_name；

删除表时，该表的所有索引同时被删除

#删除索引
drop index i_studentname on student;

 

5.创建索引的原则

5.1 按照下列标准选择建立索引的列

频繁搜索的列：比如条件后边的studentname

经常用作查询选择的列：比如select 后边跟的字段，经常要查名字studentname，年龄age

经常排序、分组的列：经常放到order by 后边、group by 后边的列

经常用作连接的列（主键/外键）

5.2 请不要使用下面的列创建索引

仅包含几个不同值的列：比如性别列，只有男女取值，没必要建索引。

表中仅包含几行：表中数据量（记录）很少，直接查数据页就很快。

6.经验

查询时减少使用 * 返回全部列，不要返回不需要的列。（使用 * 时，就不使用索引了，查询效率低）

where子句中有多个条件表达式时，包含索引列的表达式应置于其他表达式之前。（有索引的列，可以先排序一部分数据，也是提高查询效率）

避免在order by 子句中使用表达式。（order by后边尽量使用单纯的列）

 7.查看某个表已有的索引

#查看所有已经创建的索引(表名)
show index from student;