MySQL索引
索引
1、前言
-
MySQL中有四种索引
- 主键索引
- 常规索引
- 唯一索引
- 全文索引
-
索引目的
为了增加表的查询效率
经过索引的层层筛选 快速定位数据的位置 避免了全表扫描 数据量越大 效率越明显
2、主键索引
-
概述
主键索引是关系型数据库中最常见的索引类型 主要的作用是确定数据表中数据的确定的位置
-
设置
primary key
-
注意事项
- 最好为每张表去指定一个主键 但不是必须的
- 一个表中只能指定一个主键索引 而且主键的值不能为空 通常配合 auto_increment 来使用
- 字段类型一般设为 int类型
- 主键名称一般为id 也有叫表前缀_id
-
创建
第一种形式
CREATE TABLE `test3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
第二种形式
CREATE TABLE `test3` ( `id` int(10) unsigned NOT NULL PRIMARY KEY (`id`) AUTO_INCREMENT, `username` varchar(10) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
自增步长(了解)
mysql的默认步长是基于session 默认为1
-
查看步长[ˈvɛriəbəlz]
show session variables like 'auto_inc%';
-
设置步长 只针对当前的会话
set session auto_increment_increment = 2; # 将步长设置为2
-
设置全局步长
set global session auto_increment_increment = 2;
-
注意:
当数据删除以后 自增位置并不会改变
自增归位:
- truncate 表名; 清空表并将自增归位
- alter table test3 auto_increment=1;
-
3、常规索引
-
概述
常规索引是关系型数据库中查询最重要的技术 如果想要提升数据库的效率 那么索引优化是首先要考虑 因为它可以使数据库的性能得到很大的提升
-
缺点
- 多占用磁盘空间
- 会减慢插入 修改和删除的效率
-
创建索引
使用 index 或者 key 随同表一起创建
创建常规索引 使用index创建常规索引 索引名称为u_index
mysql> create table test4( -> id int unsigned primary key auto_increment, -> username varchar(10), -> index u_index(username) -> );
使用key创建常规索引不起名称
mysql> create table test5( -> username varchar(10), -> key (username) -> );
说明:如果创建索引的时候给定了索引名称 则索引名为你给定的名称 否则为字段名
-
建表以后添加索引 ( 常规或唯一索引 )
create index 索引名称 on 表名(字段名)
create index username on test5(username);
-
删除索引
drop index username on test5;
drop index 索引名称 on 表名
-
注意
在给mysql创建唯一或者常规索引的时候 最好单占一行
一个表中可以存在多个索引 但是要根据具体需求设置索引 前提是某个字段有大量查询的时候
4、唯一索引
-
概述
唯一索引与主键索引一样 都可以防止创建重复的值 但是不同之处在于 每个表中只能有一个主键索引 但是可以有多个唯一索引
-
定义
unique 创建唯一索引
-
注意
如果你在给表中插入数据出现了报错 但是SQL语句还没有问题 那你就要查看一下 当前字段是否存在唯一索引
-
创建
创建唯一索引并起名称
mysql> create table test6( -> id int unsigned primary key auto_increment, -> username varchar(10), -> unique u_username(username) -> );
创建唯一索引不起名称(默认名称为字段名)
mysql> create table test7( -> id int unsigned primary key auto_increment, -> username varchar(10), -> unique(username) -> );
5、常规索引和唯一索引的区别
这俩个索引在查询能力上是没有区别的 如果考虑对于更新性能的影响 建议使用普通索引
mysql更新
普通索引:
将数据页从磁盘读出到内存更新数据页
唯一索引:
将数据页从磁盘读出到内存 判断是否唯一 再更新数据页
6、查看索引执行效率
-
创建表
mysql> create table test8( -> id int unsigned primary key auto_increment, -> username varchar(10), -> age tinyint default 18 -> );
-
写入数据
insert into test8 values(null, 'lucky', 18);
insert into test8(username, age) select username,age from test8;
-
开启mysql运行时间监控
set profiling = 1; [ˈproʊfaɪlɪŋ]
-
开启查询
select * from test8 where name='zhangsan'
-
删除索引
drop index username on test8;
-
查看时间
show profiles;
本文来自博客园,作者:寻月隐君,转载请注明原文链接:https://www.cnblogs.com/QiaoPengjun/p/16011919.html