SQL索引[1]

一、索引定义

索引是一种数据结构,创建在表上的。是对数据库表中的一列或者是多列的值进行排序的一种结果,使用索引可以提高查询速度,避免全表扫描
优势:提高查询效率,避免全表扫描
缺点:索引并非越多越好,过多的索引会导致CPU的使用率居高不小

二、索引分类

  • 普通索引:没有任何限制条件,可以给任何类型的字段创建普通索引
  • 唯一性索引:使用unique修饰的字段,值是不能重复,主键索引隶属于唯一性索引
  • 主键索引:使用primary key修饰的字段会自动创建索引
  • 单列索引:在一个字段上创建索引
  • 多列索引:在表中多个字段上创建索引
  • 全文索引:使用fulltext参数设置全文索引,只支持char\carchar和text类型的字段上,常用于数据量交大的字符串类型上,可以提高查询速度,只有在MyISAM存储引擎支持
  • 聚集性索引:在InNoDB引擎下建立的索引,将索引及数据存放在一起
    非聚集性索引:在MyISAM引擎下建立的索引,将索引和数据分开存储在不同的文件中

三、索引SQL

1、在创建表时创建索引

create table 表名(
属性名 属性类型 [完整性约束],
...,
属性名 属性类型 [完整性约束]
[unqiue|fulltext|spatial] index|key  [别名] 属性名 (asc,desc);
## 说明:
## unqiue|fulltext|spatial都是可选参数
## unqiue表示是唯一性索引
## fulltext表示索引为全文索引
## spatial表示索引位空间索引
## index|key参数值需要创建索引的属性的名称,属性必须是已经存在的,如果不存在,需要先创建
## 别名:可以给自己所创建的属性起一个别名,可以是任意的字段
## asc|desc是可选参数,asc:表示升序排列 ,desc:表示降序排列

示例:
创建普通索引:

create table index1 (
id int ,
name varchar(20),
sex  varchar(2),
index (name) 
);

创建唯一性索引

create table index2(
id int ,
name varchar(20),
sex  varchar(2),
unique index idx_id (id) 
);

2、在已经存在的表上创建索引
通过create语法创建索引

create [unqiue|fulltext|spatial] index 索引名 on 表名 (属性[asc|desc])

示例SQL:
在这里插入图片描述
通过alter语法创建

alter  table 表名 add  [unqiue|fulltext|spatial] 
index 索引名(属性名 [asc|desc])

3、删除索引

drop index 索引名 on 表名;

四、索引执行过程

查询student为例演示索引执行过程:
在这里插入图片描述
该表中只有主键索引
查询student表中名称为"tu"开头的同学的信息

select * from student where Sname like 'tu%';

需用explain查看SQL语句的执行计划,
使用示例,在查询的select前面添加上explain关键字

mysql> explain select * from student where Sname like 'tu%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
        Extra: Using where
1 row in set (0.00 sec)
## table属性:表示查询的表  
## possible_keys属性:可能用到的key,显示null表示没有用到索引
## key属性:真实执行过程中使用到的索引
## rows属性:表名当前查询操作影响数据的行数
## Extra属性:备注信息,显示SQL执行过程中使用到的关键信息

从上面的执行计划可知:在student表中查询”tu“开头的学生信息,是做了整个表查询操作,把表中所有的行( rows: 13)全部扫描一遍,才找到需要的”tu“开头的学生信息,假如当前表有5百万行数据,要查找”tu“开头的学生信息,要搜索的是5百万行数据,这样效率太低了

解决方案:添加索引

添加索引之后,再分析执行计划

mysql> explain select * from student where Sname like 'tu%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: range
possible_keys: idx_name,idx_name_age
          key: idx_name
      key_len: 27
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.01 sec)

在添加索引之后,在student表中查询”tu“开头的学生信息,发现在执行过程中会有索引选择idx_name,idx_name_age,真正执行过程使用了idx_name,整个数据查询影响的数据行数是2行数,可以发现,使用索引对SQL语句查询效率提升是很大的,所以,记住explain关键字,可以查看SQL执行计划,帮分析SQL语句能否正确使用索引

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值