mysql 亿级数据单表新增索引方法

转载

云端行者 2024-09-12 12:15:34

文章标签 mysql 亿级数据单表新增索引方法数据库数据子节点 文章分类 MySQL 数据库

课前导读：

本章设计到MySQL数据库的索引和事务操作，索引操作设计的概念内容比较多，但是他涉及到数据库的内部运行效率和使用空间等多方面知识，相比比较重要，也需要进行相关学习。而事务操作更不用多说，是我们进行数据库操作的保障！这方面只是主要是便于面试准备！

课前导读：

一、索引

1、概念

2、作用

3、索引操作

4、索引的运行

5、索引的数据结构：B+树

二、事务

1、事务认识：

2、事务操作：

3、事务四大特性：

4、MySQL的隔离性

总结：

一、索引

1、概念
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
2、作用
数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系
索引所起的作用类似书籍目录，可用于快速定位、检索数据
索引对于提高数据库的性能有很大的帮助
对我们的MySQL操作来说索引最大的作用就是提高运行效率，但不是所有索引都能提高效率的
3、索引操作
(1)查看索引：show index from 表名;
例如：show index from adc;
但我们会想，明明没有创建索引为什么能查到索引呢，这时候我们就要看看这个表中的字段约束了。
可以看见id是primary key主键的，idnumber 是唯一的，这就是他们拥有索引的原因了，主键非空且唯一，需要索引进来判断他的唯一性，所以MySQL会自动帮我们创建索引，unique也是这样的。

(2)创建索引：create index 索引名字 on 表名(列名);
例如：create index name_index on adc(name);
可以看见和上面相比，我们创建了name的索引了，我们就可以通过name来进行查询了。
但是如果表中有很多数据(成千上万)的时候进行索引创建时，是一个危险操作，因为这样创建会吃掉很多磁盘io，会花掉很多时间，但创建的这段时间数据库是无法使用的，会带来很大的损失，所以我们最好在建表时进行索引的创建！

(3)删除索引：drop index 索引名字 on 表名;
例如：drop index name_index on adc;
这个删除操作和创建索引一样是个危险操作，会吃掉大量磁盘io。需要在建表时决定好是否需要这个索引！

4、索引的运行
咱们把索引创建好了之后，不需要手动使用，直接查询的时候就会自动的走索引，sql内部通过数据库的执行引擎来进行执行的，执行引擎会自动评估，哪种成本最低，速度最快~
5、索引的数据结构：B+树
索引的根本目的在于提高查找速度
1、B树的特点：每个数据在他的根节点上，比较次数虽然和二叉树相比没怎么减少(一个节点上可能需要比较多次)，但是读写硬盘的次数减少了。(B树的每个节点都是在硬盘上的),但是b树还不够，我们就引入了B+树
2、B+树介绍：B+树也是N叉搜索树，但在这之上又有一些新的特点，B+树就是为了索引这个场景，量身制作的数据结构。
特点：
(1)B+树也是一个N叉搜索树，每个节点上可能包含N个key，N个key划分出N个区间，最后一个key就相当于最大值。
(2)父元素的key会在子元素中重复出现，并且是以最大值的姿态出现的！
这样的重复出现，导致叶子节点就包含了所有数据的全集，非叶子节点中的所有值都会在叶子节点中体现出来！！！
(3)它会把叶子节点用类似链表的方式进行首尾相连~
这样通过类似链表的连接，就可以更有利于我们进行范围查询了！所有的查询，最后都是要落到叶子节点上的，无论查询哪个元素，中间比较的次数都差不多，查询操作比较均衡！
(4)由于所有的key都会在叶子节点中体现，因此非叶子节点，不必存表的真实记录(不必存数据行)，只需要把所有的数据行给放到叶子节点上即可，非叶子节点只需要存索引的值
(由于非叶子节点只存了简单id，没有存一整行，这就意味着非叶子节点，占用的空间是大大降低的，有可能在内存中可以放进去缓存，更进一步降低了硬盘的io，提高查询速度！！)

二、事务

1、事务认识：
事务简单理解就是可以把sql给打包到一起，变成一个整体进行操作。
例如转账操作需要双方进行操作，需要让转账人进行-金额，使接收人进行+金额，如果有一个失误，就会出现问题，所以我们需要把他进行一个整形进行操作，同时进行。要么一起执行，要么都不执行。如果事务中间执行失误了，就让一条都不执行，但它并不是没有执行，而是遇到了错误回复到执行之前的样子，看起来好像没执行一样。
2、事务操作：
回滚(rollback)：回滚就是把执行过的操作逆向恢复回去。
(数据库会把执行的每个操作记录下来，如果某个数据出错，就会把事务中前面的操作进行回滚，根据之前进行的操作，进行逆操作(前面是插入，现在就删除，前面是删除，现在就插入))。
开启事务：start transaction;
中间写sql语句
commit; （开启事务后，sql不会立即执行，而是攒着等commit一起执行,保证事务的原子性）
rollback 主动回滚事务。
3、事务四大特性：
(1)原子性
事务的初心
(2)一致性
事务执行前后，都得是合法的数据，不能出现失误情况。
(3)持久性
事务操作产生的修改都会写入到硬盘中，就算是关机了，数据一样存在硬盘中。
(4)隔离性
一个数据库服务器，同时执行多个事务的时候，事务之间的相互影响程度。
4、MySQL的隔离性
隔离性详解：mysql服务器如果要同时给多个用户提供服务，此时多个客户端之间，可能会同时发生事务，尤其是这多个事务同时操作同一个数据库的同一个表的时候，就可以有问题。
如果隔离性越高，就意味着事务之间的并发程度越低，执行效率越慢，但是数据的准确性越高。例如：算钱的时候，一定要数据准确
如果隔离性越低，就意味着事务之间的并发程度越高，执行效率越快，但是数据的准确性越低。例如：抖音、b站等的点赞数，需要执行速率快，但不需要太准确。
mysql提供了不同挡位(可以控制隔离性的高低、并发程度的高低、执行效率的高低、数据正确性的高低等)
不同程度遇到的不同问题：
1、脏读(dirty data):读脏数据，意思就是读到了有问题的数据，完全并发的情况下，没有任何限制就会出现脏读问题。
解决办法：降低并发现，提高隔离性，给写"加锁";
2、不可程度读：在一个事务操作中，连续两次读到的数据不一样，就是因为边读数据，边修改数据导致。
解决办法：进一步降低并发性，提高隔离性，给读"加锁";
3、幻读：在同一个事务中，两次读到的结果集不一样，在上面加锁的情况下，写不一样的内容，导致读到的情况不一样
解决问题：串行化，彻底舍弃并发性，实现完全隔离。
MySQL提供了四个隔离级别：
1、read uncommitted 不做任何限制，事务之间都是随意并发的，并发程度最高，隔离性最低，会出现脏读+不可重复读+幻读问题
2、read committed 给写加锁，并发程度降低，隔离性提高，解决了脏读问题，但会出现不可重复读+幻读问题
3、repeatable read 给写和读都加锁，并发程度又降低，隔离性又提高，解决了脏读+不可重复读问题，但会出现幻读问题。
4、serializable 严格串行化，并发程度最低，隔离性最高，解决了脏读+不可重复读+幻读问题，但执行速度时最慢的。
mysql就可以根据具体问题，来决定使用哪个隔离级别，通过mysql配置文件进行调整。