mysql索引和事务详解

目录

一，mysql索引介绍

1，索引的概念

2，索引创建的基本原则

3，索引的创建和分类

1，普通索引

2，唯一索引

3，主键索引

4，组合索引（单列索引与多列索引)

5、全文索引（FULLTEXT）

4，查看索引

5，删除索引

二，mysql事务介绍

1，事务的概念

2，事务的ACID特点

3，mysql事务的隔离级别

4，事务控制语句

5，使用set设置控制事务

---

一，mysql索引介绍

1，索引的概念

数据库的索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址

使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询度。

索引是表中一列或者若干列值排序的方法。

建立索引的目的是加快对表中记录的查找或排序

索引需要额外的磁盘空间

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率。

当表很大或查询涉及到多个表时，可以成千上万倍的提高查询速度。

可以降低数据库的IO成本，并且还可以降低数据库的排序成本。

通过创建唯一性索引保证数据表数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时，可以大大减少分组和排序的时间。

索引的副作用

索引需要占用额外的磁盘空间。
对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
而 InnoDB 引擎的表数据文件本身就是索引文件。
在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

2，索引创建的基本原则

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。
●表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位

●记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。

●经常与其他表进行连接的表，在连接字段上应该建立索引。

●唯一性太差的字段不适合建立索引。

●更新太频繁地字段不适合创建索引。

●经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。

●索引应该建在选择性高的字段上。

●索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

3，索引的创建和分类

环境创建一个数据表

[root@localhost ~]# mysql -uroot -p123123   #登录数据库

(root@localhost) [(none)]> show databases;  #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| hellodb            |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)


(root@localhost) [(none)]> creat database test;  #创建库

(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| hellodb            |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)


(root@localhost) [(none)]> use test;         #进入库
Database changed


(root@localhost) [test]> create table info (id int,name char(40),cardid varchar(50),address varchar(50),remark text);     #创建表

(root@localhost) [test]> desc info;               #查看表
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | char(40)    | YES  |     | NULL    |       |
| cardid  | varchar(50) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

添加数据

(root@localhost) [test]> insert into info values(1,'zhangsi',123123,'shangghai','vip');
Query OK, 1 row affected (0.00 sec)    #继续添加数据

(root@localhost) [test]> select *from info;         #查看表
+------+----------+--------+----------+--------+
| id   | name     | cardid | address  | remark |
+------+----------+--------+----------+--------+
|    1 | zhangsan | 123456 | beijing  | vip    |
|    2 | zhangsi  | 123123 | shanghai | vip    |
|    3 | liwu     | 123321 | shanghai | vvip   |
|    4 | liliu    | 654321 | hangzhou | vip    |
|    5 | liqi     | 123654 | shenzhen | vvip   |
+------+----------+--------+----------+--------+
5 rows in set (0.00 sec)

1，普通索引

最基本的索引类型，没有唯一性之类的限制。

●直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。
如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

例：直接创建索引

(root@localhost) [test]> create index name_index on info(name); #添加普通索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost) [test]> show create table info; 查看表
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE `info` (
  `id` int(11) DEFAULT NULL,
  `name` char(40) DEFAULT NULL,
  `cardid` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `remark` text,
  KEY `name_index` (`name`)      #添加成功
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> explain select name from info;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | name_index | 41      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)      #查看name时type是索引查找key是name_index

修改表方式创建索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

(root@localhost) [test]> alter table info add index id_index(id);  #修改表创建索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost) [test]> explain select id from info;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | id_index | 5       | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)    #查找ID时是索引查找索引是id_index

创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

(root@localhost) [test]> create table info1 (id int,name char(20)not null,
cardid varrchar(40)not null,index id_index(id));    #创建表时指定索引id_index
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [test]> show create table info1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info1 | CREATE TABLE `info1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) NOT NULL,
  `cardid` varchar(40) NOT NULL,
  KEY `id_index` (`id`)                    #创建成功
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> explain select id from info1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info1 | NULL       | index | NULL          | id_index | 5       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)    查看时是索引查找id_index

2，唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

例：

直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

(root@localhost) [test]> select *from info;
+------+----------+--------+----------+--------+
| id   | name     | cardid | address  | remark |
+------+----------+--------+----------+--------+
|    1 | zhangsan | 123456 | beijing  | vip    |
|    2 | zhangsi  | 123123 | shanghai | vip    |
|    3 | liwu     | 123321 | shanghai | vvip   |
|    4 | liliu    | 654321 | hangzhou | vip    |
|    5 | liqi     | 123654 | shenzhen | vvip   |
|    3 | liwu     | 123321 | shanghai | vvip   |
+------+----------+--------+----------+--------+
(root@localhost) [test]> create unique index name_index on info(name);
ERROR 1062 (23000): Duplicate entry 'liwu' for key 'name_index' #唯一键每个数据需不同

(root@localhost) [test]> create unique index name_index on info(name);  #创建唯一键索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost) [test]> show create table info;
+-------+-----------------------------------------------------------------------------------------------