MySQL的一些见解1

一、数据库存储引擎

数据库存储引擎是数据库底层软件组织,数据库管理系统DBMS使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。存储引擎主要有: 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5. Federated

1.1、InnoDB(B+树)
InnoDB底层存储结构为B+树,B树的每个节点对应innodb的一个page, page大小是固定的,一般设为16k。其中非叶子节点只有键值,叶子节点包含完成数据

 适用场景:
        1)经常更新的表,适合处理多重并发的更新请求。
        2)支持事务。
        3)可以从灾难中恢复(通过 bin-log 日志等)。
        4)外键约束。只有他支持外键。
        5)支持自动增加列属性 auto_increment。

1.2、TokuDB(Fractal Tree节点带数据)
TokuDB底层存储结构为Fractal Tree,Fractal Tree的结构与B+树有些类似,在Fractal Tree中,每个child指针除了需要指向一个 child 节点外,还会带有一个Message Buffer ,这个Message Buffer是一个FIFO的队列,用来缓存更新操作。

        例如,一次插入操作只需要落在某节点的Message Buffer就可以马上返回了,并不需要搜索到叶子节点。这些缓存的更新会在查询时或后台异步合并应用到对应的节点中。

 TokuDB在线添加索引,不影响读写操作, 非常快的写入性能, Fractal-tree在事务实现上有优势。他主要适用于访问频率不高的数据或历史数据归档

1.3、MyIASM
MyIASM是 MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT插入或UPDATE更新数据时即写操作需要锁定整个表,效率便会低一些。

MyISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。在设计之初就预想数据组织成有固定长度的记录,按顺序存储的。 MyISAM 是一种静态索引结构。

缺点是它不支持事务处理。

1.4、Memory
Memory也叫HEAP堆内存:使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH 索引。但是一旦服务关闭,表中的数据就会丢失掉。Memory同时支持散列索引和B树索引,B树索引可以使用部分查询和通配查询,也可以使用<,>和>=等操作符方便数据挖掘,散列索引相等的比较快但是对于范围的比较慢很多

二、数据库引擎有哪些

查看mysql提供的所有存储引擎 show engines;

mysql常用引擎包括:MYISAM、Innodb、Memory、MERGE

        MyISAM:全表锁,拥有较高的执行速度,不支持事务,不支持外键,并发性能差,占用空间相对
较小,对事务完整性没有要求,以select、insert为主的应用基本上可以使用这引擎

        Innodb:行级锁,提供了具有提交、回滚和崩溃回复能力的事务安全,支持自动增长列,支持外键约束,并发能力强,占用空间是MyISAM的2.5倍,处理效率相对会差一些

        Memory:全表锁,存储在内容中,速度快,但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失,默认使用HASH索引,检索效率非常高,但不适用于精确查找,主要用于那些内容变化不频繁的代码表

        MERGE:是一组MYISAM表的组合

三、InnoDB与MyISAM的区别

InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,
这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;

InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MyISAM会失败;

InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但
是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,
因为主键太大,其他索引也都会很大。而MyISAM是非聚集索引,数据文件是分离的,索引保存的
是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM
用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;

Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高

四、索引

索引Index是帮助MySQL高效获取数据的数据结构。常见的查询算法:顺序查找、二分查找、二叉排序树查找、哈希散列法、分块查找、平衡多路搜索树B树。索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速获取信息。

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库

mysql有4种不同的索引:主键索引PRIMARY、唯一索引UNIQUE、普通索引INDEX和全文索引FULLTEXT

注意:索引并非是越多越好,创建索引也需要耗费资源,一是增加了数据库的存储空间,二是在插入和删除时要花费较多的时间维护索引
        索引加快数据库的检索速度
        索引降低了插入、删除、修改等维护任务的速度
        唯一索引可以确保每一行数据的唯一性
        通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能
        索引需要占物理和数据空间

五、常见索引原则

1、选择唯一性索引,唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
2、为经常需要排序、分组和联合操作的字段建立索引。
3、为常用作为查询条件的字段建立索引。
4、限制索引的数目:越多的索引,会使更新表变得很浪费时间。尽量使用数据量少的索引
5、如果索引的值很长,那么查询的速度会受到影响。尽量使用前缀来索引
6、如果索引字段的值很长,最好使用值的前缀来索引。
7、删除不再使用或者很少使用的索引
8、最左前缀匹配原则,非常重要的原则。
9、尽量选择区分度高的列作为索引区分度的公式是表示字段不重复的比例
10、索引列不能参与计算,保持列干净:带函数的查询不参与索引。
11、尽量的扩展索引,不要新建索引