目录
索引是一种为了加快数据库查询(操作)速度而引入的一种手段,需要占用额外的磁盘空间,我们之前所学的主键,唯一键,外键都是自带了索引的。另外我们也可以自己手动为某个字段(列)设置索引。并且手动添加的可以删除索引,但是像主键这种自带索引的是无法删除索引的。一个表可以有多个索引。
首先我们要了解数据库查询(select)操作具体是怎样执行的:
从上面的执行过程来看,查询操作的时间复杂度是O(n),如果这个表的数据量很大,遍历起来就会很费时间。并且数据库的数据都是存储在硬盘上的,读硬盘操作这个过程本身开销就大。
引入索引虽然能够提高查询速度,但同时也是有代价的:
例如:show index from student;
例如:create index idx_student_name on student(name); //注意索引名的写法
其实创建索引操作,也是一个危险操作,特别是在你表已经创建好且表中数据量极大时,此时创建索引,会极容易把数据库服务器给卡住。一个有效的解决方法是:另外在弄一台机器,在这台机器上部署mysql服务器,并且创建一张同样的表,然后创建好索引,最后再将原服务器上的数据导入到新的mysql服务器上。导的过程中注意控制节奏。当所有数据都导入完毕,新的数据库就可以替代旧的数据库了。
例如:drop index?idx_student_name on student;?//注意索引名的写法
实际开发中,查询(读)的频率是高于修改(写)的频率的。所以引入索引还是非常有必要的。
索引是针对某一列,某个字段。只有对加索引的列进行条件查询的时候,查询速度才能被优化。即就是假如有一个学生表,字段有学生id,name等。只有学生id加了索引。那么where后条件查询是id的话才会优化查询速度。但若where后跟name则不会优化。
数据库索引用的是何种数据结构?
引入数据库的目的就是使查询操作更快,时间复杂度低。我们所知的在查询方面时间复杂度低的数据结构有哈希表,红黑树等。但是哈希表只能精确查询,并不能进行范围查询和模糊查询,因此并不适合作为索引的数据结构。而红黑树虽然既可以精确查询,又可以范围查询,还可以模糊匹配(但是%开头的这种模糊查询不支持),缺点是红黑树是一个二叉树,树的高度可能会很高,会导致IO操作频繁。
其实数据库索引操作使用的数据结构是 B+树 (即N叉搜索树)。
先说说B树:
本质上还是N叉搜索树,特点是每个节点里有多个key值,每个节点的子树个数由key的个数决定,若一个节点上保存有N个key就会划分出N+1个区间,N+1个子树。并且每个节点中key的个数也不是无限制的,达到一定的规模,就会触发节点的分裂。当删除元素达到一定的数目后,也会触发节点的合并。
N越大,树的高度就越低,读硬盘的次数就越少。由于每个节点中的所有key值都是存储在硬盘的一个区域中的,一次读硬盘操作就能读取出一个节点中的所有值。树的高度越低越好。
B+树是在B树的基础上做了一些改进,比如说范围查询更简单快速了。具体改进如下:
B+树的优点(相对于B树,哈希表,红黑树而言)
问题来了,非叶子节点存储到内存中,内存会不够吗?
假设排序的key值用int型,就是4个字节。假设表中有一亿条数据,就是一亿个key值,一亿个key值就是4亿字节,4亿字节约等于400MB,对于现在的内存空间来说是足够的。
字节单位转换:千字节对应KB、百万字节对应MB、十亿字节对应GB 。
另外mysql索引实现也并不只是B+树这一种,还有一种存储引擎,比如Innodb,就是mysql最常用的存储引擎。
事务是指逻辑上的一组操作,组成这组操作的各个单元,要么全部成功,要么全部失败。
假如做一件事,要么正确做完,要么"都不做",不能出现做一半的情况。都不做并不意味着都不做,在数据库中是指"回滚(rollback)"。
假如A给B转钱1000元,转钱是需要执行多条sql语句的,假如执行完第一句sql语句时,出现网络错误,或者是数据库挂掉了。A的账户会减少1000元,但是B的账户却没有多出1000元(因为第二条语sqi语句没有执行)。解决办法就是:使用事物来控制,保证以上两个sql语句要么全部执行成功,要全部执行失败。即就是事物的原子性。
使用日志的方式,记录事务中的关键操作(增删改查等操作),这种记录就是回滚的依据。
由于mysql数据库是一个客户端-服务器结构的程序。一个服务器经常会给多个客户端提供服务,很有可能会出现客户端1提交的事务执行了一半,客户端2提交的事务也来了。这时数据库就需要同时处理这两个事物,并发执行。如果事务之间穿行执行,即事物之间排队一个一个执行,效率就会非常低。所以事务并发执行是很有必要的。并发程度越高,执行的效率就越高。但是效率虽然高了,却也会出现一些"数据错误"的问题。比如:
1.脏读问题
例如事务A正在执行写数据操作的过程中,事务B读取这个数据。后续事务A又继续进行了修改(因为A还没执行完),这就会导致事务B读取到的数据不准确/过时/无效。也被称为脏数据。?
脏读解决方法:核心思路就是针对写操作加锁。写的时候不准读。写完才能读。并发性降低了,隔离性提高了,效率降低了,数据准确性提高了。??
上面虽然解决了脏读问题,但是只是约定了写的时候不能读,并没有约定读的时候不能写,就会造成不可重复度问题。
2.不可重复读问题
一个事物在内部多次读取到同一个数据却出现数据不同的情况。这是因为事务A在两次读取数据的期间,事务B对该数据进行了修改并提交了事务。
不可重复读解决方法:对读操作进行加锁。约定读的时候不能写。并发性降低了,隔离性进一步提高了,效率降低了,数据准确性也进一步提高了。??
3 .幻读问题
主要是结果集发生变化 。上面虽然解决了脏读和不可重复度问题。但是不可重复读约定的是事务A读a文件时,事务B不能写(修改)a文件,但是没规定事务B不能写b文件呀。此处说的问题不是说我读一个文件时,这个文件内容刷一下的变化了,而是我一次查询本来只有几个文件,结果一会又多出来或少了几个文件。主要指的是结果集的变化。即就是数据内容没变化,但是结果集变了。
幻读解决办法:引入串行化的方式,保证事务穿行执行,此时便完全没有并发了。此时隔离性是最高的,效率是最低的,数据是最准确的。?
综上所述,并发程度越高,隔离性越低,虽然效率高了,但带来的问题也越多。并发程度越低,隔离性越高,虽然效率低了,但是问题少。
对于注重效率还是注重数据正确不同的需求场景有不同的要求。mysql服务器也提供了"隔离级别",使我们针对不同的需求场景可以自行设置。具体是在mysql的配置文件中修改数据库的隔离级别,总共有四种隔离级别。?
隔离级别
就是在"数据正确"和"效率"之间做权衡。往往我们提升了效率,就会牺牲正确性;提升了正确性,就回牺牲效率。如果两个事物之间的影响越大,隔离性就越低;影响越小,隔离性就越高。
使用场景
充值、转账等一些与钱相关的场景,宁可不要效率,也要保证数据的准确性。
短视频、点赞、转发的数量等等这些场景下,对于准确性就不用要求太高。
四种隔离级别