Mysql运维管理-创建索引知识及创建索引的多种方法实战9

摘要:
为表的字段创建索引就像一本书的目录。如果在字段上创建索引,则当将索引用作条件时,可以加快数据查询的速度。1创建主键索引以查询数据库的内容。按主键查询是最快的。每个表只能有一个主键,但可以有多个普通索引列。主键列要求所有内容必须唯一,而索引列不要求内容唯一。

为表的字段创建索引

索引就像书的目录一样,如果在字段上建立索引,那么以索引为条件时可以加快查询数据的速度。

1 创建主键索引

查询数据库的内容,按主键查询是最快的,每个表只能有一个主键,但是可以有多个普通索引列,主键列要求所有内容必须唯一,而索引列不要求内容唯一。
我们无论建立主键索引还是普通索引,都要在表的对应列上创建,可以对单列创建索引也可以对多列创建索引
建立主键索方法:

1.在创建表时,可以增加建立主键索引语句

system@ceshi 04:0932->create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name(name)
-> );

提示:

a.AUTO_INCREMENT 自增

b.Primary key(id) 主键

c.KEY index_name(name) name 字段普通索引

操作演示

system@ceshi 04:2146->drop table student;
Query OK, 0 rows affected (0.00 sec)
system@ceshi 04:2333->create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name(name)
-> );
Query OK, 0 rows affected (0.00 sec)
system@ceshi 04:2609->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(4)  | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | MUL | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

提示:PRI为主键的标识,MUL为普通索引的标识。

2.建立表之后通过alter命令增加主键索引(不推荐这种做法)

a.主键列不能重复创建,必须先删除上面的配置

system@ceshi 04:3716->alter table student drop primary key;

b.建表时忘记加主键了利用alter命令增加id列为自增主键列

system@ceshi 04:5948->alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
system@ceshi 05:0324->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | MUL | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

提示:只有int类型且为primary key才可以使用auto_increment。

9.9.7.2 创建普通索引

1.在建表时,可以增加建立普通索引列的语句如下:

system@ceshi 04:0932->create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name(name)
-> );

提示:

a.KEY index_name(name)name 字段普通索引

操作演示:

system@ceshi 04:2146->drop table student;
Query OK, 0 rows affected (0.00 sec)
system@ceshi 04:2333->create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name(name)
-> );
Query OK, 0 rows affected (0.00 sec)
system@ceshi 04:2609->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(4)  | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | MUL | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

2.建表后利用alter增加普通索引

删除建表时创建的index_name索引

system@ceshi 05:0333->alter table student drop index index_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
system@ceshi 06:0419->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

在name列上添加索引,索引名为index_name

system@ceshi 06:0431->alter table student add index index_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

system@ceshi 06:0456->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | MUL | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3 对字段的前n个字符创建普通索引

当遇到表中比较大的列时,列内容的前n个字符在所有内容中已经接近唯一时,这时可以对列的前n个字符建立索引而无需对整个列建立索引,这样可以节省创建索引占用的系统空间,以及降低和更新维护消耗的系统资源。

对字段的前n个字符创建索引的语法:

Create index index_name on test(name(8)); 条件列前n个字符创建索引

实战操作:

system@ceshi 06:0838->create index index_dept on student(dept(8));
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

system@ceshi 06:3650->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | MUL | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | MUL | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
system@ceshi 06:3847->show index from studentG
*************************** 1. row ***************************
   Table: student
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
*************************** 2. row ***************************
   Table: student
  Non_unique: 1
Key_name: index_name
Seq_in_index: 1
 Column_name: name
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
*************************** 3. row ***************************
   Table: student
  Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
 Column_name: dept
   Collation: A
 Cardinality: NULL
Sub_part: 8
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment: 
3 rows in set (0.00 sec)

4 为表的多个字段创建联合索引

如果查询的条件是多列时,我们可以为多个查询的列创建联合索引,甚至可以为多列的前n个字符创建联合索引,实战演示如下:

为多个列创建索引

system@ceshi 06:4019->create index ind_name_dept on student(name,dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

system@ceshi 06:4726->show index from studentG
*************************** 1. row ***************************
   Table: student
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
*************************** 2. row ***************************
   Table: student
  Non_unique: 1
Key_name: index_name
Seq_in_index: 1
 Column_name: name
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
*************************** 3. row ***************************
   Table: student
  Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
 Column_name: dept
   Collation: A
 Cardinality: NULL
Sub_part: 8
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment: 
*************************** 4. row ***************************
   Table: student
  Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 1
 Column_name: name
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
*************************** 5. row ***************************
   Table: student
  Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 2
 Column_name: dept
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment: 
5   rows in set (0.00 sec)

可以对多个列的前n个字符创建联合索引,实战演示如下:

system@ceshi 06:5144->drop index ind_name_dept on student; 删除索引的另一种方法。
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

system@ceshi 06:5158->create index ind_name_dept on student(name(8),dept(10));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

提示:

按条件列查询数据时,联合索引是有前缀生效特性的

Index(a,b,c)仅a,ab,abc三个查询条件可以走索引。b,bc,ac,c不能走索引

5 创建唯一索引(非主键索引)

Create unique index index_age on student(age);

唯一索引是用来约束表的内容的,不能重复。

system@ceshi 07:0828->create unique index uni_ind_name on student(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
system@ceshi 07:1010->desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | UNI | NULL||
| age   | tinyint(2)  | NO   | | 0   ||
| dept  | varchar(16) | YES  | MUL | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

6 索引表的创建及生效条件

问题1:既然索引可以加快查询速度,那么是不是就要给所有的列创建索引呢?

解答:因为索引你不但占用系统空间,更新数据库时还需要维护索引数据。因此索引是一把双刃剑,并不是越多越好,例如:数十到几百行的小表上无需创建索引,更新频繁,读取少的业务要少建立索引。

问题2:需要在哪些列上创建索引呢?

解答:Select user,host from mysql.user where host= ….索引一定要创建在where后的条件列上,而不是select后的选择数据的列上,另外我们要尽量选择在唯一值多的大表上建立索引。创建索引要和开发商量。

7 创建索引命令集合小结

1.创建索引相关命令集合

创建主键索引

alter table student change id id int primary key auto_increment;

删除主键索引

alter table student drop primary key

创建普通索引

alter table student add index index_dept(dept(8))

根据列的前n个字符创建索引

create index index_dept on student(dept(8))

根据多个列创建联合索引

create index ind_name_dept on student(name,dept);

根据多个列的前n个字符创建索引

create index ind_name_dept on student(name(8),dept(10));

删除普通索引

Alter table student drop index index_dept
drop index ind_name_dept on student

创建唯一索引

create unique index uni_ind_name on student(name);

基本索引:

a.要在表的列上创建索引。

b.索引会加快查询速度,但是会影响更新的速度,因为要维护索引。

c.索引不是越多越好,要在频繁查询的where后的条件列上创建索引。

d.小表或唯一值极少的列上不建索引,要在大表以及不同内容多的列上创建索引。

免责声明:文章转载自《Mysql运维管理-创建索引知识及创建索引的多种方法实战9》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇SSH常见错误让这家有12万名员工、1.7万种产品的钢铁厂平滑上云的黑科技是什么?下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

Ubuntu18.04完全卸载mysql5.7并安装mysql8.0的安装方法

Ubuntu18.04版本下,如果直接输入: sudo apt install mysql-server 命令,会默认安装mysql5.7版本,安装过程并没有提示输入密码,安装完成后也无法正常登录,这是因为mysql为Ubuntu18.04提供了新版本mysql8.0,所以此时就需要卸载5.7,重新安装8.0版本。 注意:一定要完全卸载并清楚数据,不然等会...

数据库系列(五)之 mysql的伸缩性

      这篇文章,主要讲述mysql的伸缩性。在国内mysql一直都是使用得最多的数据库,在国外也排名前三。mysql是一款开源的、性能较高的数据库。       伸缩性是指在软件设计中,软件(数据库、应用程序)通过特定的配置或升级,可以进行横向或纵向扩展,来达到软件适应越来越多用户访问的目的。数据库达到一定瓶颈,需要考虑伸缩性,这是大部分软件设计人...

mysql修改字段防止锁表

步骤1: 修改一个大表的字段,add column或者drop column,操作后表会锁住,此时查询ok,insert和update会一直等待锁。如图。 解决方案: 1、基于要操作的表创建一个临时表,执行要修改的操作,比如add column或者drop column 2、把表内容导出到文件(注意不要用intsert into table_copy s...

linux开启MySql远程连接功能

MySql默认只有本机(127.0.0.1)能够访问,要开启主要有两步骤:配置MySql绑定IP和建立允许远程访问的账户。 一、修改IP绑定 需要修改MySql的配置文件my.cnf 如果你使用Debian Linux,文件位置在: /etc/mysql/my.cnf 如果你使用Red Hat Linux/Fedora/Centos Linux,文件位置...

mysql db [Warning] IP address 'xxxx' could not be resolved: Name or service not known

mysql 使用版本:mysql 8.0 异常现象:每次访问,mysql server 的日志都会打印出如下内容,[Warning] IP address 'xxxx' could not be resolved: Name or service not known 异常分析:默认情况下,mysql 总会对访问者使用的 IP 或主机名进行 DNS 反向...

MySQL++:liunx 安装 MySQL

第一步: 1):下载mysql安装包:这里选择下载版本 5.6.33,通用版,linux下64位 http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz 第二步: 2):卸载老版本MySQL 查找并删除mysql有关的文件 find /...