MysQL索引与事务

最新推荐文章于 2022-12-17 00:11:36 发布

m0_67156403

最新推荐文章于 2022-12-17 00:11:36 发布

阅读量104

点赞数

文章标签： mysql 数据库全文检索

本文链接：https://blog.csdn.net/m0_67156403/article/details/125252188

版权

索引及分类
1）索引的概念
索引是一种特殊的文件，包含着对数据表中所有记录的引用指针
2）索引的作用

设置了合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度

当表大时，或者查询涉及多个表时。使用索引可使查询速度加快上千倍

可以降低数据库的IO成本，并且索引还可以降低数据库的排列成本

通过创建唯一索引。可以保证数据库表中每一行数据的唯一性

可以加快表与表中的连接

在使用分组个排序子句进行数据查询时。可以显著减少查询中分组和排序的时间
3）索引的分类

普通索引，这是最基本的索引类型，而且他没有唯一性之类的限制

唯一索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值只能出现一次，既必须唯一

主键索引，主键是一种唯一性索引，但他必须指定为“peimary key”

全文索引，索引类型为fulltext.全文索引可以在char,varchar或者text类型的列上创建

单列索引与多列索引，索引可以是单列上创建的索引，也可以实在多列上创建的索引
4)创建索引的原则依据

表的主键、外键必须有索引

数据量超过300行的表应该有索引

经常与其他表进行表连接的表

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

更新太频繁的字段不适合建立索引

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

索引应该建立咋选择性高的字段上

索引应建在小字段上
建立数据库及数据表
创建索引一共有三种方式

创建表直接定义

create index 索引名称 on 表名

alter table 表名 add index 索引名称

mysql> create database school；
#设置数据表
create table unfo ( id int(4) not null primary key auto_increment, name varchar(10) not null, address varchar(50) default 'nanjing',, age int(3) not null);
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);                                       
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

创建索引
第一种

#针对年龄创建索引
mysql> create index index_age on info (age);
#查看索引
mysql> show index from info;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+--

mysql> create unique index unique_name on info (name);
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------

第二种

mysql> alter table info add unique index index_name (name);
mysql> show index from info;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

第三种方法创建表时直接创建索引

mysql> create table user (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> index index_score (score)）;
    mysql> show index from user;
    ysql> show index from user;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_score |            1 | score       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------

将两张数据表关联起来查看

#建立两张表
mysql> select * from user;
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     1 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
mysql> select * from hob
    -> ;
+----+----------+
| id | hob_name |
+----+----------+
|  1 | 看书     |
|  2 | 运动     |
|  3 | 游戏     |
+----+----------+
3 rows in set (0.00 sec)
mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 游戏     |
+--------+----------+
//设置别名（方便操作）
mysql> select a.name,b.hob_name from user a inner join hob b on a.hobbby
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 游戏     |
+--------+----------+

创建视图

create view view_user as select a.name,b.hob_name from user a inner join hob b on a.hobby=b.id;
mysql> select * from view_user
    -> ;
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 游戏     |
+--------+----------+
#设置全文索引
mysql> create fulltext index full_addr on info (address);

事务的概念
事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，既这一组数据库命令要么都执行，要么都不执行。
事务ACID特性

原子性：事务是一个完整的操作，各元素是不可分的，即原子的。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败

一致性：当事务完成时，数据必须处于一致状态；在事务开始之前，数据库中储存的数据处于一致状态；在正在进行的事务中，数据可能处于不一致状态；当事务成功完成时，数据必须再次回到已知的一致状态。

隔离性：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性：指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会效果会被永久地保留在数据库中
事务的操作
MySQL操作事务
默认情况下MySQL的事务是自动提交的。之前我们用sql操作数据库时，一条语句执行后，系统会自动执行事务提交。当需要把一组语句作为一个事务提交时，需要手动对事物进行控制。手动控制事务有两种方法，一种是使用事务处理命令控制，另一种是使用set设置事务的处理方式

使用事务命令控制事务
begin:表示开始一个事务，后面会有多条数据库操作语句执行
commit：表示提交一个事务，对应前面的begin操作，他们之间的数据库操作语句一起完成
rollback：表示回滚一个事务，在begin和commit之间，如果某一个数据库操作语句出现错误，执行rollback回滚，数据库回到begin之前的状态，也就是操作语句都没有执行

创建数据库和表

mysql> create database user;
mysql> use user
mysql> create table info (
    -> id int(4) not null primary key auto_increment,   //auto_increment   自动增长
    -> name varchar(10) not null,
    -> address varchar(50) default 'nanjing',
    -> age int(3) not null);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into info (name,address,age) values ('zhangsan','baijing',20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (name,address,age) values ('lisi','shanghai',22);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (name,address,age) values ('wangwu','hangzhou)',30);
Query OK, 1 row affected (0.00 sec)

开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#设置一行数
mysql> insert into info (name,address,age) values ('tianqi','baijin',30);
Query OK, 1 row affected (0.01 sec)
#设置节点
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('css','baijin',31);
Query OK, 1 row affected (0.01 sec)
mysql> savepoint b;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | baijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | tianqi   | baijin   |  30 |
|  5 | css      | baijin   |  31 |
+----+----------+----------+-----+
5 rows in set (0.01 sec)
#利用rollback回滚到tianqi
mysql> rollback to a

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | baijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | tianqi   | baijin   |  30 |
+----+----------+----------+-----+
mysql> commit
    -> ;