oracle 大量数据加索引_数据库相关知识

数据库是学计算机比不可少的一门课程。最长常见的数据库是MySQL和Oracle，现在大的互联网公司都是用的MySQL，像银行系统或者做安全的公司大都是用的Oracle。

现在我们就以下5点来逐步了解下MySQL吧

1、MySQL存储引擎有哪几种

2、MySQL事务

3、MySQL锁

4、MySQL分库分表

5、MySQL索引优化

1、MySQL存储引擎有哪几种

这里只列举2种，因为这两种常用，并且在面试中做比较，其他几种自行百度百科

一、InnoDB

这是MySQL 5.5或更高版本的默认存储引擎。它提供了事务安全(ACID兼容)表，支持外键引用完整性约束。它支持提交、回滚和紧急恢复功能来保护数据。它还支持行级锁定。

二、MyISAM

该存储引擎管理非事务性表，提供高速存储和检索，支持全文搜索。

MyISAM和InnoDB的区别

1. myisam不支持事务，innodb支持事务
2. myisam不支持外键，innodb支持外键
3. myisam只支持表锁，不支持高并发，innodb支持表锁和行锁（共享锁、排它锁、意向锁）
4. 大量select操作，最好选择myisam，执行大量insert update 最好选择innodb
5. myisam是非聚集索引，索引和数据分开，只缓存索引；innodb是聚集索引，索引和数据存在一个文件。

2、MySQL事务

首先要了解事务的概念：事务是一组SQL语句组成的逻辑单元，要不全部执行，要不全部不执行。

事务的特性：ACID

Atomicity 原子性：一组SQL要不全部执行，要不全不执行，重点在单个事务本身

Consistency：数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态

Isolation：并发执行的各个事务之间不能互相干扰。

Durability：事务一但提交，它对数据库的改变是永久性的。其他操作或故障不应该对其有影响。重点在单个事务本身

1. 数据库什么时候会有事务出现？

show variables like '%engine%'; 使用的存储引擎

select VERSION(); 数据库版本

show global VARIABLES like 'tx_isolation';使用的隔离机制

show global variables like 'autocommit';

针对增删改操作自动提交

set session autocommit=off;

关闭自动提交事务后缓存中的数据不会写入磁盘，必须使用commit/rollback提交事务

------------------下面讨论写操作对读操作的影响------------------------

事务并发的三大问题：脏读、不可重复读、幻读

（1）事务并发的三大问题脏读：

事务A读到了事务B未提交的数据（脏数据）

举例如下

（2）事务并发的三大问题 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。举例如下：

（3）事务并发的三大问题 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。举例如下：

事务并发的三大问题其实都是读一致性问题，必须由数据库提供一定的事务隔离机制来解决

数据库实现事务隔离的方式，基本上可分为以下两种。

· 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

· 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

3、锁机制

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。
锁机制的基本原理可以概括为：
事务在修改数据之前，需要先获得相应的锁
获得锁之后，事务便可以修改数据
该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

innodb的锁分为行锁（排它锁、共享锁）、表锁

表锁

表级锁是MySQL最大粒度的锁定机制。特点是：实现逻辑非常简单，带来的系统负面影响最小。获取锁和释放锁很快。可以避免死锁。

表锁分为表共享读锁和表共享写锁，读锁不会阻塞其他用户对同一表的读请求，会阻塞对同一表的写请求，写锁会阻塞其他用户对同一表的任意请求。

行锁

行锁顾名思义就是在一行上加锁，锁定粒度小，带来的负面影响最小。可能会死锁。行锁的类型很多，共享锁、排他锁、行锁使用不当会变成表锁。

4、索引

innodb是聚集索引，也就是索引数据存在同一个文件里面。当显示设置一个字段为主键时，有主键索引，没有显示设置时，innodb会默认给一个唯一标识数据记录的列为主键，如果不存在，自动为innodb生成一个隐含字段作为主键。

概念：索引是一种排好序的数据结构，这就是为什么索引可以提高查询速度。

索引的种类：

主键索引

唯一索引

复合索引

聚簇索引

非聚簇索引

全文索引

索引是可以提高查询速度的，大部分人想我只要给表加了索引那么查询效率肯定提高，事实不是这样的，索引列你选对了吗，虽然索引加上了，可是你的查询条件不对也会导致索引失效的。你可能会惊讶索引怎么还会失效的。带着这些问题，我们来探索下索引的那些事情，让你更直观的认识索引。

先来了解下 索引应该加在哪些字段上呢？

1、具有唯一标识的；

2、长度不能太长的，建在小字段上

3、索引应该建在查询高的字段上

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

5、频繁进行操作的表，不建议建立太多的索引

6、数据量大的表应该建立索引

聚簇索引指的是索引和数据都存在一张表上，并且由于innodb采用多叉平衡数，三层树结构就可以存储2000多万的索引，所以采用数的结构可以减少遍历的层数，快速查找对应的数据。

复合索引（A,B,C,D) 什么样的操作使用了索引，什么样的操作没有使用索引，索引的查询遵循什么原则？如何查看一条SQL语句用到哪种类型的索引？排序和group by会产生临时内存吗？如何优化查询语句，select * 合适吗？

select * from test where name='A'; 使用了索引A