。

在数据十分大的时候#xff0c;索引可以大大加快查询的速度。

这是因为使用索引后可以不用扫描全表来定位某行的数据#xff0c;而是先通过索引表找到该行数…一.MySQL索引介绍

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

在数据十分大的时候索引可以大大加快查询的速度。

这是因为使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的作用类似于图书的中的页码快速找到所需的内容。

1.概述

当数据保存在磁盘类存储介质上时它是作为数据块存放。

这些数据块是被当作一个整体来访问的这样可以保证操作的原子性。

硬盘数据块存储结构类似于链表都包含数据部分以及一个指向下一个节点或数据块的指针不需要连续存储。

记录集只能在某个关键字段上进行排序所以如果需要在一个无序字段上进行搜索就要执行一个线性搜索LinearSearch的过程平均需要访问N/2的数据块N是表示所占据的数据块数日。

如果这个字段是一个非主键字段也就是说不包含唯一的访问入口那么需要在N个数据块上搜索整个表格空间

但是对于一个有序字段可以运用二分查找BinarySearch这样只需要访间log2N的数据块。

这就是为什么数据表使用索引后性能可以得到本质上提高的原因。

素引是对记录集的多个字段进行排序的方法。

在一张表中为一个字段创建一个索引将创建另外一个数据结构包含字段数值以及指向相关记录的指针然后对这个索引结构进行排序允许在该数据上进行二分法排序。

使用索引的副作用是需要额外的磁盘空间。

对于MyISAM引而言这些索引是被统一保存在一张表中的。

如果很多字段都建立了索引那么会占用大量的磁盘空间这个文件将很快到达底层文件系统所能够支持的大小限制

2.索引作用

设置了合适的索引之后数据库利用各种快速定位技术能够大大加快查询速度这是创建索引的最主要的原因。

当表很大或查询涉及到多个表时使用索引可以成千上万倍地提高查询速度。

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

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

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

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

​​​​​​​

3.索引分类

从物理存储的角度来划分索引分为聚族索引和非聚族索引两种聚族索引是按照数据存放的物理位置为顺序的而非聚族索引就不一样了聚索引能提高多行检索的速度而非聚族索引对于单行的检索更快

从逻辑的角度来划分索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

普通索引是最基本的索引它没有任何限制也是大多数情况下用到的索引。

mysql

users(user_name(20));备注index索引on后面跟要创建索引的表名表名括号内跟该表内标准型字段名以及它的字符长度。

修改表格式添加索引

唯一索引与普通索引类似不同的就是唯一索引的索引列的值必须唯一但允许有空值注意和主键不同。

如果是组合索引则列值的组合必须唯一。

唯一索引创建方法和普通索引类似。

创建唯一索引

主键索引是一种特殊的唯一索引一个表只能有一个主键不允许有空值。

一般是在建表的时候同时创建主键索引。

create

平时用的SQL查询语句一般都有比较多的限制条件所以为了进一步榨取MySQL的效率就要考虑建立组合索引。

在组合索引的创建中有两种场景即为单列索引和多列索引。

下面通过一个场景来具体说明单列索引和多列索引

在一个user用户表中有nameagesex三个字段分别分三次建立了INDEX普通索引。

那么在select

from

sex;数据查询语句中就会分别检索三条索引虽然扫描效率有所提升但却还未达到最优。

这个时候就需要使用到组合索引即多列索引

create

在MySQL中有一个知识点叫最左原则。

下面的select语句的where条件是依次从左往右执行的。

select

usernameagesex。

在查询中nameagesex的顺序必须如组合索引中一致排序否则索引将不会生效所以一般在建立索引时要先想好响应的查询业务尽量避免虽然有索引但是使用不上的问题。

5全文索引

对于较大的数据集将资料输入一个没有FULLTEXT索引的表中然后创建索引其速度比把资料输入现在FULLTEXT索引的速度更快。

不过切记对于大容量的数据表生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

CREATE

确定针对该表的操作是大量的查询操作还是大量的增删改操作尝试建立索引来帮助特定的查询。

检查自己的

sql

子句中出现的字段建立索引尝试建立复合索引来进一步提高系统性能。

修改复合索引将消耗更长时间同时复合索引也占磁盘空间

对于小型的表建立索引可能会影响性能应该避免对具有较少值的字段进行索引避免选择大型数据类型的列作为索引。

​​​​​​​

索引查询是数据库中重要的记录查询方法要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑下面给出实际生产环境中的一些通用的原则

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

因为主键具有唯一性外键关联的是子表的主键查询时可以快速定位。

记录数超过300行的表应该有索引。

如果没有索引需要把表遍历一遍会严重影响数据库的性能。

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

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

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

经常出现在

where

子句中的字段特别是大表的字段应该建立索引。

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

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

5.查看索引

事务主要用于处理操作量大复杂度高的数据。

比如说在人员管理系统中要删除一个人员即需要删除人员的基本资料又需要删除和该人员相关的信息如信箱文章等等。

这样这些数据库操作语句就构成一个事务。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性保证成批的SQL语句要么全部执行要么全部不执行。

是一个不可分割的工作逻辑单元在数据库系统上执行并发操作时事务是最小的控制单元

适用于多用户同时操作的数据库系统的场景如银行、保险公司及证券交易系统等​​​​​​​

一般来说事务是必须满足4个条件原子性不可分割性一致性隔离性持久性。

原子性Atomicity

一个事务中的所有操作被视为一个单独的工作单元即不可分割。

这意味着事务中的所有操作要么全部成功完成要么全部失败回滚不会出现只完成部分操作的情况。

如果事务在执行过程中发生错误或被中断系统将撤销事务中已经执行的所有操作将数据库状态回滚到事务开始前的状态以确保数据的一致性和完整性。

一致性Consistency

在事务开始之前和事务结束以后数据库的完整性没有被破坏。

这意味着事务必须使数据库从一个一致性状态转换到另一个一致性状态。

换句话说事务的执行不会破坏数据的完整性和业务规则。

例如在一个转账操作中从一个账户扣除的金额必须等于在另一个账户中添加的金额以确保账户总金额的一致性。

隔离性Isolation

事务在并发执行时彼此之间是不可见的即一个事务的执行不能被其他事务所干扰。

数据库允许多个并发事务同时对其数据进行读写和修改但隔离性确保了这些操作之间的独立性。

一个事务对数据的修改在最终提交之前对其他事务是不可见的。

事务隔离分为不同级别包括读未提交Read

uncommitted、读提交Read

read和串行化Serializable。

这些级别提供了不同程度的并发控制和数据一致性保证。

持久性Durability

一旦事务被提交其对数据的修改就是永久性的即使系统发生故障也不会丢失。

为了确保持久性DBMS数据库管理系统通常会将事务的修改写入到磁盘上的稳定存储介质中并在系统恢复时重新应用这些修改以恢复数据的一致性状态。

begin

transaction用于开始一个新的事务。

commit提交事务。

将事务中的所有更改永久地保存到数据库中。

rollback回滚事务。

撤销事务中所做的所有更改并将数据库恢复到事务开始之前的状态。

savepoint在事务中设置一个保存点。

允许在后续的回滚操作中只撤销到指定的保存点。

rollback

savepoint将事务回滚到指定的保存点。

撤销从上一个保存点或事务开始到当前点之间的所有更改。

set

transaction设置事务的属性如隔离级别、访问模式等。

lock

table

table锁定和解锁表。

用于在事务中控制对表的并发访问。

set

autocommit设置自动提交模式。

当自动提交模式打开时每个单独的SQL语句都被视为一个单独的事务并在执行后自动提交值为1开启自动提交0关闭自动提交。

set

transaction

level设置事务的隔离级别。

不同的隔离级别提供了不同程度的并发控制和数据一致性保证

MySql事务处理主要有两种方法

BEGIN开始一个事务ROLLBACK事务回滚COMMIT事务确认

SETAUTOCOMMIT0禁止自动提交SETAUTOCOMMIT1开启自动提交

MySQL

kgc_transaction_test;mysqlbegin;

//开始事务