[toc]
索引的几种类型:唯一索引、主键索引、聚集索引、普通索引、组合索引、全文索引
唯一索引:在创建唯一索引时要不能给具有相同的索引值。在MySQL中创建索引:
CREATE UNIQUE INDEX indexName ON mytable(username(length));
主键索引:在我们给一个字段设置主键的时候,它就会自动创建主键索引,用来确保每一个值都是唯一的。
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
聚集索引:我们在表中添加数据的顺序,与我们创建的索引键值相同,而且一个表中只能有一个聚集索引。
普通索引:它的结构主要以B+树和哈希索引为主,主要是对数据表中的数据进行精确查找。
CREATE INDEX indexName ON mytable(username(length));
ALTER TABLE mytable ADD INDEX username(length);
组合索引:一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有索引名称
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
全文索引:它的作用是搜索数据表中的字段是不是包含我们搜索的关键字,就像搜索引擎中的模糊查询。
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
使用索引的优点:
使用索引的缺点:
MySQL中,索引的底层实现构主要是:哈希索引和B+树索引。
哈希索引的底层数据结构就是哈希表,查询性能最快;
B树索引:B树实现
B+树索引:B+树实现
红黑树等数据结构也可以用来实现索引,但是文件系统以及数据库系统普遍采用B-Tree/B+Tree作为索引结构。
原因:索引本身也很大,因此索引往往是以索引文件的形式存储在磁盘上。所以,索引查找的过程就会产生磁盘的I/O操作,相比于内存存取,I/O存取消耗要高几个数量级,所以索引的优劣最重要的指标就是在查找过程中的磁盘I/O存取次数。
Hash索引是无序的,所以只适用于等值查询,而不能用于范围查询。大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。
聚集索引:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。
非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。
区别:
逻辑层优化:如分步查询,然后在代码层进行拼接;用临时表;改变sql语句的写法等等
实现层的优化:然后我们看看每条sql语句执行的执行计划,用explain命令查看。可以看到这些表的执行顺序,可能使用到的索引,实际使用到索引,索引的长度,可能要扫描的行数等等信息,然后对这些信息分析,进行索引的优化。
事务时逻辑上的一组操作,要么都执行,要么都不执行。
记忆:ACID
MyISAM是MySQL的默认数据库引擎(5.5版之前)。虽然性能极佳,而且提供了大量的特性,包括全文索引、压缩、空间函数等,但MyISAM不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。不过,5.5版本之后,MySQL引入了InnoDB(事务性数据库引擎),MySQL 5.5版本后默认的存储引擎为InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎,但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。(如果你不介意 MyISAM 崩溃恢复问题的话)。
两者的对比:
READ COMMITTED
和 REPEATABLE READ
两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读:MySQL-InnoDB-MVCC多版本并发控制 幻读:一次事务里,多次查询后,结果的个数不一致的情况叫做幻读。
如何解决:MVCC 和next-key(当前读)
next-key原理:将当前数据行与上一条数据和下一条数据之间的间隙锁定,保证此范围内读取数据是一致的。
next-key包含:记录锁、间隙锁。
记录锁:加在索引上的锁;间隙锁:加在索引之间的锁。
1、第一范式:一个单元格只存储一个值。
当关系模式R的所有属性都不能在分解zhi为更基本的数据单位时,称R是满足第一范式的,简记为1NF。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。
2、第二范式:满足所有的属性字段唯一依赖主键。
如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。
3、第三范式:消除传递依赖。
设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF。