mysql 添加索引_mysql 增加索引

一、数据库索引

1、索引（index）的概念

（1）是一个排序的数据结构，帮助mysql快速查询、更新数据库表中的数据
（2）无须对整个表进行扫描，通过物理地址就可以找到所需数据
（3）为了提高表的搜索效率而对某些字段的值建立的数据结构；并不是所有字段都需要创建索引

2、索引的作用

（1）优点
①当数据量很大或查询涉及多个表时，使用索引，可以大大地提高数据查询速度，降低数据库的IO成本；
②通过索引对数据进行排序，可以降低数据库的排序成本，降低CPU的消耗；
③通过创建唯一性索引保证数据表数据的唯一性；
④可以加快表与表之间的连接
（2）缺点
①增加了数据库的存储空间
②降低了表的更新速度，插入和修改数据时花费更多的时间

3、索引的分类

（1）从物理存储的角度来划分：
①聚簇索引：索引顺序按照数据的物理位置，检索多行数据速度更快
②非聚簇索引：索引顺序不是按照数据的物理位置，检索单行数据的速度更快
（2）从逻辑的角度来划分
普通索引、唯一索引、主键索引、组合索引和全文索引

4、索引的添加与使用

（1）添加索引：通过某一个字段或某些字段添加
（2）索引使用：索引创建完成后无需调用，当数据库进行数据查询的时候，会自动使用索引

二、索引的分类

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

（1）最基本的索引类型，不要求索引列的值唯一性
（2）创建普通索引的方式

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

示例

[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      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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

（1）与普通索引的区别是索引列的所有值必须唯一，但可以有空值；
（2）创建唯一索引的方式
create unique index 索引名称 on 表名(字段名称(字段长度)); #unique：默认字段
alter table 表名 add 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   |
+----+------+---------+  #第二字段可以有重复，第三字段因为有唯一性不能重复
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

（1）是一种也输的唯一索引，指定为“PRIMARY KEY”
（2）一个表只能有一个主键，不允许有空值
（3）创建主键索引的方式
修改表的创建
alter table 表名 add primary key(字段名称(字段长度));
直接指定主键
create table 表名(字段1,字段2,primary key(指定字段名称));
示例

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主键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

（1）主键索引一定是唯一性索引，但唯一性索引不一定是主键索引
（2）一张表可以有多个唯一性索引，但只允许有提个主键索引
（3）主键列不能为空，但唯一索引可以为空
（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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

（1）当多条件查询的时候我们可以使用组合索引，查询效率会更高，因为当有多个单列索引时，MySQL只能用到其中那个它认为最有效率的单列索引
（2）支持最左原则，从左往右依次执行
（3）创建组合索引的方式
create table 表名(字段1,字段2,字段3,index 索引名称(字段1名称 字段2名称 字段3名称));
示例

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个索引
1
2
3
4
5

6、全文索引

（1）索引类型为FULLTEXT
（2）可以在char、varchar或者test类型的列上创建
create fulltext index 索引名称 on 表名(字段名称(字段长度));
alter table 表名 add fulltext index 索引名称(字段名称(字段长度));
create table 表名(字段1,字段2,字段3,fulltext index 索引名称(字段名称(字段长度));
示例

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  #数据类型为全文索引
1
2
3
4
5
6
7
8
9
10

7、创建索引的依据

（1）表的主键、外键都必须有索引
（2）记录数超过300行的表应该有索引
（3）经常与其他表进行连接的表，在连接字段上应该建立索引
（4）唯一性太差的字段不适合创建索引
（5）更新太频繁的字段不适合创建索引
（6）将常出现再where子句中的字段，特别是大表的字段，应该建立索引
（7）索引应该建立在选择性高的字段上
（8）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

8、查看索引的方法

show index from 表名;
show keys from 表名;

9、删除索引的方法

drop index 索引名称 on 表名; #删除普通索引
alter table 表名 drop index 索引名称; #删除普通索引
alter table 表名 drop primary key; #删除主键索引
示例

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)  #索引已删除
1
2
3
4

三、MySQL事务

1、事务的概念

（1）是一种机制、一个操作的组合，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库操作命令要么都执行，要么都不执行
（2）是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
（3）适用于多用户同时操作数据库系统的场景，如银行、保险公司及证券交易系统等待
（4）通过事务的整体性以保证数据的一致性

2、事务的ACID特点

（1）原子性（Atomicity）
①事务是一个完整的操作，事务的各元素是不可分割的
②事务中的所有元素必须作为一个整体提交或回滚
③如果事务中的任何元素失败，则整个事务将失败
（2）一致性（Consistency）
①当事务完成时，数据必须处于一致状态
②当事务开始前，数据库中存储的数据处于一致状态
③在正在执行的事务中，数据可能处于不一致的状态
④当事务成功完成时，数据必须再次回到已知的一致状态
（3）隔离性（Isolation）
①对数据进行修改的所有并发事务是彼此隔离的，表名事务必须是独立的，它不应以任何方式依赖于或影响其他事务
②修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
（4）持久性（Durability）
①值不管系统是否发生故障，事务处理的结果都是永久的
②一旦事务被提交，事务的效果会被永久地保留在数据库中

3、事务控制语句

（1）MySQL事务默认是自动提交，当SQL语句提交时事务遍自动提交
（2）事务控制语句
①begin：开启一个事务
②rollback：回滚一个事务，回滚到 begen之前地状态
③commit：提交一个事务
④savepoint identifier：在事务中创建一个保存点，一个事务可以有多个savepoint
⑤release savepoint identifier：删除一个事务的保存点（与保存的数据无影响）
⑥rollback to identifier：回滚到一个事务的保存点
⑦set transaction：用来设置事务的隔离级别

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

（1）事务处理命令控制事务
①begin：开始一个事务
②commit：提交一个事务
③rollback：回滚一个事务（位于begin和commit之间）
（2）使用set命令进行控制
①set autocommit=0：禁止自动提交（手动提交事务）
②set autocommit=1：开启自动提交

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行数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

如果后面执行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
3
4
5
6
7
8
9
10
11
12
13
14
15

四、存储引擎

1、存储引擎介绍

（1）MySQL中的数据用各种不同地技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套地功能在MySQL中称为存储引擎
（2）存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
（3）MySQL常用的存储方式
①MyISAM；②innoDB
（4）MySQL数据库中的组件，负责执行实际的数据I/O操作
（5）MySQL系统中，存储引擎处于文件系统之上，在数据保存到文件系统之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行储存

2、MyISAM的介绍

（1）MyISAM不支持事务，也不支持外键
（2）访问速度快
（3）对事务完整性没有要求
（4）MyISAM在磁盘上存储成三个文件
①表结构文件（.frm文件）
②数据文件的扩展名为.MYD（MYData）
③索引文件的扩展名是.MYI（MYIndex）
（5）表级锁定形式，数据在更新时锁定整个表
（6）数据库在读写过程中相互阻塞
①会在数据写入的过程中阻塞用户数据的读取
②也会在数据读取的过程中阻塞用户的数据写入
（7）数据单独写入或读取，速度较快且占用资源相对少
（8）MyIAM支持的存储格式
①静态表；②动态表；③压缩表

3、MyISAM适用的生产场景

（1）公司业务不需要事务的支持
（2）单方面读取或写入数据比较多的业务
（3）MyISAM存储引擎不适合数据读写都比较频繁的场景
（4）对数据业务一致性要求不是非常高的业务
（5）服务器硬件资源相对比较差

4、innoDB特点介绍

（1）支持事务
（2）行级锁定，提高数据库并发访问性能
（3）非常高效的缓存索引和数据
（4）支持外键约束，5.5版本后支持全文索引
（5）对硬件资源要求比较高的场合

5、innoDB适用生产场景分析

（1）业务需要事务的支持
（2）行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
（3）业务数据更新较为频繁的场景，如：论坛，微博等
（4）业务数据一致性要求较高，如：银行业务
（5）硬件设备内存较大，利用lnnodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

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

（1）需要考虑每个存储引擎提供的核心功能及应用场景
（2）支持的字段和数据类型
（3）锁定类型：不同的存储引擎支持不同级别的锁定
（4）索引的支持
（5）事务处理的支持

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 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8、修改存储引擎

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

mysql> alter table user_info engine=引擎;
1

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

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

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

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

（4）临时修改存储引擎

mysql> set default_storage_engine=引擎;
1