mysql索引简谈
mysql索引简谈
一、什么是索引
就好比我们在看一本书的时候,有目录的话,我们可以快速定位到想看的地方,而没有目录的话,我们只能一页一页地翻。索引就像目录,有了索引,数据库可以快速查询到目标内容,而不必查找整个数据库表,但是如果没有的话,数据库只能一行一行地遍历数据。
本文使用的案例表:学生表(t_student)
- CREATE TABLE `t_student` (
- `st_id` varchar(20) NOT NULL COMMENT '学号',
- `st_name` varchar(20) NOT NULL COMMENT '姓名',
- `st_sex` varchar(2) NOT NULL COMMENT '性别',
- `st_academy` varchar(20) NOT NULL COMMENT '学院',
- `st_major` varchar(20) NOT NULL COMMENT '专业',
- `st_class` varchar(20) NOT NULL COMMENT '班级',
- `st_grade` int(11) NOT NULL COMMENT '年级',
- `st_edu_len` int(11) NOT NULL COMMENT '学制',
- `st_is_at_school` varchar(4) default NULL COMMENT '是否在校',
- PRIMARY KEY (`st_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、索引种类
(1)单列索引,包括普通索引(index)、唯一索引(unique inex)、主键索引(primary key),一个单列索引只能包含一列属性
(2)组合索引,一个组合索引包括两个或两个以上的列。
(3)全文索引(fulltext index),检索出多列文本字段上(text类型)包含某些单词的索引
三、索引的创建
(1)单列索引
【1】普通索引,最基本的索引
第一种方式:语法为 create index 索引名 on 表名(要建立索引的列名)
create index i_st_class on t_student(st_class);
第二种方式:语法为 alter table 表名 add index 索引名(要建立索引的列名)
alter table t_student add index i_st_class(st_class);
【2】唯一索引,与普通索引类似,但唯一索引的每一个索引值只对应唯一的数据记录,这一点又与主键索引类似,但唯一索引允许null值
语法为:在创建普通索引的语句中的index前面加上unique即可(假设学生表中的姓名不重复)
create unique index i_st_name on t_student(st_name);
或
alter table t_student add unique index i_st_name(st_name);
【3】主键索引,在唯一索引的基础上不允许索引列有null值。主键索引一般用在与表中其他列无关或与业务无关的列上,一般是int,自增类型的列上。
不能使用create index语句创建主键索引,只能在建表时创建或alter语句中:
alter table t_student add primary key (st_id);
(2)组合索引
【1】一个组合索引包含多个列,一个组合索引对应的数据记录必须唯一,建立组合索引的语句如下:
create index i_name_major_class on t_student(st_name,st_major,st_class);
或
alter table t_student add index i_name_major_class(st_name,st_major,st_class);
如果我们建立了以上的组合索引,实际上包含了三个索引,分别是(name)、(name,major)、(name,major,class)
那么我们在查询的时候,如果要使用到组合索引,就必须遵循组合索引的“最左前缀“原则。
【2】什么是最左前缀原则?
用自己的话来说,就是从组合索引的最左列开始,where语句中必须包含此列,且可跳过中间列,到达目标列的匹配规则。
实际上就是上方所说的三种组合(name)、(name,major)、(name,major,class)
【3】哪些语句走组合索引,哪些语句不走呢?
走组合索引的情况:
- select * from t_student where st_name='123';
- select * from t_student where st_name='123' and st_major='123';
- select * from t_student where st_name='123' and st_class='123';
- select * from t_student where st_name='123' and st_major='123' and st_class='123';
可以使用explain语句来显示mysql对查询处理的过程
不走组合索引的情况:
- select * from t_student where st_major='123';
- select * from t_student where st_class='123';
- select * from t_student where st_major='123' and st_class='123';
即where条件中如果不带组合索引的最左列的话,肯定不走组合索引。
四、索引的删除
删除索引的格式为: alter table 表名 drop index 索引名
alter table t_student drop index i_name_major_class;
或为: drop index 索引名 on 表名
drop index i_name_majoe_class on t_student;
五、使用索引的优缺点
优点:
【1】可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
【2】建立索引可以大大提高检索的数据,提高查询性能,以及减少表的检索行数
【3】在分组和排序的子句中进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库中的记录会重新排序)
缺点:
【1】创建索引和维护索引也会耗费时间
【2】每一个索引还会占用一定的物理空间,索引建得多了,数据库文件也会变得庞大起来
【3】当对表的数据进行插入、删除、更新的操作,索引也要动态的维护,这样就会降低表的维护效率
六、使用索引需要注意的地方
【1】.在经常需要搜索的列上建立索引,可以加快查询的速度
【2】在主键列上建立主键索引,可以确保此列数据的唯一性
【3】如果你对st_name字段建立了一个索引,当查询时候的语句是 select * from t_student where st_name like '%123%' 或like '%123',那么这个索引将不会起到作用,而st_name like '123%' 才可以用到索引
【4】不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
七、不需要创建索引的情况
【1】查询中很少使用到的列,不应该创建索引,如果建立了索引,就会降低mysql的性能,也占用了存储空间
【2】当表的插入、删除、修改操作远远多与查询操作时,不应该创建索引,此时会占用数据库的存储空间,降低维护效率,因为索引只能提高查询效率
相关文章
- 打包一沓开源的 C/C++ 包管理工具送给你!
- 看看大神 Paul Graham 对如何学习编程的回答
- 喵星人教你 HTTP 状态码
- 抛却纷争,百度给开源世界带来了什么?
- 确认过眼神,看清 HTTP 协议
- 想做前端开发?推荐几个必备珍品组件库
- 高考完?入门级的开源项目带你开启编程之旅
- Flask 笔记
- MySSL HTTPS 评级 B 升 A+
- No module named MySQLdb
- SublimeText 修改文件扩展名的默认语法高亮
- chrome 错误 ERR_CACHE_READ_FAILURE
- 大数据的技术生态概述(转载)
- pyhive 连接 Hive 时错误
- Presto 常用配置及操作
- Hive 导入 parquet 格式数据
- uWSGI 踩坑记
- There was a problem confirming the ssl certificate: [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:590) - skipping
- 认证
- SQLite向MySQL的数据库迁移