mysql 添加索引

大家好，欢迎来到IT知识分享网。

一、数据库索引

1、索引（index）的概念

2、索引的作用

3、索引的分类

4、索引的添加与使用

二、索引的分类

1、普通索引（可以有多个，对应MUL）

直接创建 create index 索引名称 on 表名(字段名称(字段长度)); #字段长度可添加 修改表的方式创建 alter table 表名 add index 索引名称 (字段名称(字段长度)); 创建表时创建 create table 表名（字段1,字段2,字段3,primary key(字段名称),index 索引名称(字段名称(字段长度)); 

示例

[root@localhost ~]# mysql -uroot -p #登录mysql mysql> create database auth; #创建auth库 mysql> use auth; #进入auth库 mysql> create table auth.users(id int(10) not null, 创建users表，建立id字段，数据类型int，不为空 -> name char(20) not null, -> age varchar(10) default '', -> primary key(id)); mysql> desc users; #查看表结构，存在3个字段 mysql> insert into users values('1','zhangsan','20'); mysql> insert into users values('2','lisi','23'); mysql> insert into users values('3','wangwu','26'); #字段下分别添加内容 mysql> select * from users; #查看表中数据 直接创建方法 mysql> create index index_age on auth.users(age); #在age上创建索引在users表的age字段，index_age：索引名称 修改表的方式创建 mysql> alter table users add index index_name(name); #在users表里的name字段创建索引，索引名称为index_name mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | NO | PRI | NULL | | #主键索引对应PRL | name | char(20) | NO | MUL | NULL | | #普通索引对应MUL | age | varchar(10) | YES | MUL | | | +-------+-------------+------+-----+---------+-------+ mysql> show index from users; 或者mysql> show keys from users; #查看users表的索引 +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-表名--+索引值得唯一性+--索引名称--+-索引列的序列号-+-对应的字段 --+-----------+-------------+----------+--------+------+--索引的类型-+---------+---------------+ | users | 唯一 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | users |不是唯一 1 | index_age | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | | | users | 1 | index_name | 1 | name | A | 3 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

2、唯一索引（可以有多个，对应UNIQUE）

mysql> create table auth.kc31(id int(10) not null, #创建kc31表，存在3个字段 -> name char(20) not null, -> address varchar(30) default '', -> unique index index_address(address)); #unique：唯一性索引 mysql> use auth; mysql> desc kc31; #查看kc31表结构 | address | varchar(30) | YES | UNI | | | #address的UNI：唯一性索引 mysql> show keys from kc31; #查看Non_unique为0：代表唯一索引 mysql> insert into kc31 values('1','aaa','zhoukou'); #插入数据 mysql> insert into kc31 values('2','bbb','zhoukou'); #验证address字段的唯一性 ERROR 1062 (23000): Duplicate entry 'zhoukou' for key 'index_address' #address字段zhoukou的关键字具有唯一性，所以不能创建 mysql> insert into kc31 values('2','bbb','luoyang'); #将zhoukou改为luoyang可以创建成功 mysql> insert into kc31 values('3','bbb','luohe'); #name字段不具有唯一性，可以创建 mysql> select * from kc31; +----+------+---------+ | id | name | address | +----+------+---------+ | 1 | aaa | zhoukou | | 2 | bbb | luoyang | | 3 | bbb | luohe | +----+------+---------+ #第二字段可以有重复，第三字段因为有唯一性不能重复 

3、主键索引（只能有一个，关键字PRIMARY）

mysql> create table auth.test(id int(10) not null, #创建test表 -> class char(20) not null, -> primary key(id)); #直接指定主键为id字段 mysql> desc test; | id | int(10) | NO | PRI | NULL | | #PRI主键索引 mysql> show index from test\G; *************************** 1. row *************************** Table: test #表名 Non_unique: 0 #索引唯一值 Key_name: PRIMARY #索引名称，关键字；PRIMARY Column_name: id #id作为主键 mysql> alter table test drop primary key; #清空主键索引 mysql> show index from test #主键索引为空值 mysql> alter table test add primary key(class); #将test表的class添加为主键 mysql> show index from test #再查询表结构，存在class主键 

4、主键索引和唯一索引的区别

mysql> alter table test add primary key(id); #验证主键的唯一性 ERROR 1068 (42000): Multiple primary key defined #多余的主键没有被定义，不能同时存在2个主键 mysql> alter table test add unique index_id(id); #创建id字段唯一性索引，可以有多个唯一性索引 mysql> show keys from test\G; #查看test存在2个唯一性索引 *************************** 1. row *************************** Table: test Non_unique: 0 Key_name: PRIMARY #主键索引 Column_name: class *************************** 2. row *************************** Table: test Non_unique: 0 Key_name: index_id #唯一索引 Column_name: id 

5、组合索引（单列索引与多列索引）

mysql> create table auth.kgc(name char(10) not null, #创建kgc表 -> passwd char(50) not null, -> sex int(10) not null, -> index index_kgc(name,passwd,sex)); #在name,passwd,sex创建组合索引 mysql> show index from kgc\G; #查看存在3个索引 

6、全文索引

mysql> create table auth.content(id int(10) not null, -> title char(255) not null, -> time int(10) null, -> primary key(id), -> fulltext index_id(id)); ERROR 1283 (HY000): Column 'id' cannot be part of FULLTEXT index #id字段不是3种数据类型，不能在设置全文索引 mysql> create table auth.content(id int(10) not null, title char(255) not null, time int(10) null, primary key(id), fulltext index_id(title)); #替换为支持的数据类型即可 mysql> show index from content\G; *************************** 2. row *************************** Index_type: FULLTEXT #数据类型为全文索引 

7、创建索引的依据

8、查看索引的方法

9、删除索引的方法

mysql> drop index index_id on content; #删除content表下的index_id索引 mysql> alter table content drop primary key; #删除ontent表下的主键索引 mysql> show index from content; Empty set (0.00 sec) #索引已删除 

三、MySQL事务

1、事务的概念

2、事务的ACID特点

3、事务控制语句

4、手动对事务进行控制的方法

5、实验：手动控制事务

（1）验证手动控制事务不提交则不会保存

[root@localhost ~]# mysql -uroot -p #启动数据库 mysql> create database imployee_salary; #创建imployee_salary数据库 mysql> use imployee_salary; #进入imployee_salary数据库 mysql> create table IT_salary(员工ID char(10), -> 岗位类别 char(20), -> 姓名 char(10), -> 年龄 char(10), -> 学历 char(10), -> 薪资 char(10), -> primary key(员工ID)); mysql> desc it_salary; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | 员工ID | char(10) | NO | PRI | NULL | | | 岗位类别 | char(20) | YES | | NULL | | | 姓名 | char(10) | YES | | NULL | | | 年龄 | char(10) | YES | | NULL | | | 学历 | char(10) | YES | | NULL | | | 薪资 | char(10) | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ mysql> begin; #开启事务 mysql> insert into it_salary values('1','硬件工程师','小王','22','专科','8000'); mysql> insert into it_salary values('2','windows工程师','小张','23','本科','9000'); mysql> insert into it_salary values('4','Linux工程师','小杨','25','本科','12000'); mysql> select * from it_salary; +----------+------------------+--------+--------+--------+--------+ | 员工ID | 岗位类别 | 姓名 | 年龄 | 学历 | 薪资 | +----------+------------------+--------+--------+--------+--------+ | 1 | 硬件工程师 | 小王 | 22 | 专科 | 8000 | | 2 | windows工程师 | 小张 | 23 | 本科 | 9000 | | 4 | Linux工程师 | 小杨 | 25 | 本科 | 12000 | +----------+------------------+--------+--------+--------+--------+ mysql> exit #退出 [root@localhost ~]# mysql -uroot -p #重新登录数据库 mysql> show tables from imployee_salary; #能够查到it_salary数据表 mysql> select * from it_salary; #数据已经不存在 说明：此时数据库中并没有之前插入的数据，说明使用begin开启服务，执行数据操作语句后，必须使用commit提交事务，否则数据是不能自动提交的。 （2）rollback（回滚）的使用 mysql> use imployee_salary; mysql> begin; #开启事务 mysql> insert into it_salary values('1','硬件工程师','小王','22','专科','8000'); mysql> insert into it_salary values('2','windows工程师','小张','23','本科','9000'); mysql> select * from it_salary; #存在添加的两行数据 mysql> rollback; #回滚事务 mysql> select * from it_salary; #数据已经不存在 说明：使用begin开始事务，然后执行了插入语句后可以看到插入的数据，但是执行rollback回滚命令后，之前的数据已经没有了 （3）savepoint（定义回滚点）的使用 mysql> begin; #开启事务 mysql> insert into it_salary values('1','硬件工程师','小王','22','专科','8000'); #插入一个数据 mysql> savepoint s1; #定义s1回滚点 mysql> insert into it_salary values('2','windows工程师','小张','23','本科','9000'); #插入第二个数据 mysql> savepoint s2; #定义s2回滚点 mysql> select * from it_salary; #存在插入的2行数据 

如果后面执行rollback命令，这两条数据都将丢失。

mysql> rollback to savepoint s1; #回滚到s1 mysql> select * from it_salary; #只存在一条数据 +----------+-----------------+--------+--------+--------+--------+ | 员工ID | 岗位类别 | 姓名 | 年龄 | 学历 | 薪资 | +----------+-----------------+--------+--------+--------+--------+ | 1 | 硬件工程师 | 小王 | 22 | 专科 | 8000 | +----------+-----------------+--------+--------+--------+--------+ mysql> rollback to savepoint s2; #尝试回滚到s2 ERROR 1305 (42000): SAVEPOINT s2 does not exist #数据无法回滚 说明：回滚到s1，第一条插入的数据可以查询出来，但是无法回退到s2了 （4）使用set命令控制事务 mysql> set autocommit=0; #改为手动提交事务 mysql> insert into it_salary values('2','windows工程师','小张','23','本科','9000'); mysql> commit; #提交事务，数据永久保存 说明：若commit提交事务后，则数据无法进行回滚 

四、存储引擎

1、存储引擎介绍

2、MyISAM的介绍

3、MyISAM适用的生产场景

4、innoDB特点介绍

5、innoDB适用生产场景分析

6、企业选择存储引擎依据

7、查看存储引擎

（1）查看MySQL默认的存储引擎

mysql> show engines; #默认存在9款存储引擎 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ mysql> show variables like '%storage_engine%'; （2）查看表正在使用的存储引擎 mysql> show table status where name='表名'; mysql> show create table 表名; 

8、修改存储引擎

（1）alter table 修改 （准对以创建的表）

mysql> alter table user_info engine=引擎; 

（2）修改my.cnf文件，指定默认存储引擎并重新启动（针对新创建的表，旧表不影响存储引擎）

[root@localhost ~]# vim /etc/my.cnf default-storage-engine=INNODB [root@localhost ~]# systemctl restart mysqld 

（3）create table创建表时指定存储引擎

mysql> create table 表名(字段1) engine=引擎; 

（4）临时修改存储引擎

mysql> set default_storage_engine=引擎;