网络资料散乱,查阅不便,所以做如下整理,内容出处均以标明,侵删!
一、数据库的分类
关系型数据库
- 关系型数据库通过外键关联来建立表与表之间的关系,另外,表和字段、数据和数据存在着关系
非关系型数据库
- 非关系数据库通常指数据以对象的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定
数据库类型 | 举例 | 优点 | 缺点 | 适用范围 |
---|---|---|---|---|
关系型数据库 | Mysql、Oracle | 1、数据之间有关系,进行数据的增删改查的时候是非常方便的 2、关系型数据库是有事务操作的,保证数据的完整性和一致性,即ACID 3、易于维护:丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率 4、支持SQL,可用于复杂的查询。 | 1、因为数据和数据是有关系的,底层是运行了大量的算法大量算法会降低系统的效率,会降低性能 2、面对海量数据的增删改查的时候会显的无能为力 3、海量数据对数据进行维护变得非常的无力 | 适合处理一般量级的数据,例如:银行转账等 |
非关系型数据库 | redis、MangDB | 1、海量数据的增删改查,维护和处理轻松 2、无需经过sql层的解析,读写性能很高 3、基于键值对,数据没有耦合性,容易扩展 4、存储数据的格式:nosql的存储格式是key,value形式、文档形式、图片形式等等,文档形式、图片形式等等,而关系型数据库则只支持基础类型 | 1、不提供sql支持,学习和使用成本较高; 2、无事务处理,附加功能bi和报表等支持也不好; | 适合处理海量数据,保证效率,不一定安全,例如:微博数据等 |
二、数据库的引擎分类
以下内容转自:https://www.cnblogs.com/domi22/p/8059403.html
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。 数据库的核心就是存储引擎 数 据 库 的 核 心 就 是 存 储 引 擎
MyISAM存储引擎
- MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事物。
MyISAM主要特性有:
1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节,这也可以通过编译来改变,对于键长度超过250字节的情况,一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中,这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库,将产生3个文件。文件的名字以表名字开始,扩展名之处文件类型:frm文件存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名时.MYI(MYIndex)
InnoDB存储引擎
- InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。
InnoDB主要特点:
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束,存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
MEMORY存储引擎
- MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。
MEMORY主要特性有:
1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享(就像其他任何非TEMPORARY表)
8、MEMORY表内存被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享
9、当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)
MERGE存储引擎
- Merge存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,merge表本身并没有数据,对merge类型的表可以进行查询,更新,删除操作,这些操作实际上是对内部的MyISAM表进行的。
三、索引优化数据库
mysql逻辑架构图
以下内容来自我同学的博客:https://blog.csdn.net/alan_gaohaodong/article/details/82525945
整理如下:
SQL优化
SQL优化,主要是优化索引
索引:相当于书的目录
索引:index是帮助MySQL高效获取数据的数据结构。索引是数据结构(树:B树、Hash树…)
- 索引的弊端:
- 1.索引本身很大,可以存放在内存/硬盘(通常为硬盘)
- 2.索引不是所有情况均适用:a.少量数据 b.频繁更新的字段 c.很少适用的字段
- 3.索引会降低增删改的效率
- 索引的优势:
- 提高查询效率(降低IO使用率)
- 降低CPU使用率(… order by age desc ,因为B树索引本身就是排好序的结构,在排序时即可直接使用)
为什么要优化sql
- 原因:性能低、执行时间长太长、等待时间太长、SQL语句欠佳(连接查询)、索引失效、服务器参数设置不合理(缓冲区、线程数)
sql编写过程:
select dinstinct .. from ..join .. on .. where .. group by .. having .. order by .. limit ..
sql解析过程:
from .. on .. join ..where .. group by .. having .. select dinstinct .. order by limit ..
- 索引分类
- 单值索引:单列,age;一个表可以有多个单只索引,name
- 主键索引:不能重复。Id 不能是null (如果不加索引,默认是主键索引)
- 唯一索引:不能重复。Id 可以是null
- 复合索引:多个列构成的所有(相当于二级索引: z: zhao)(name,age)(a,b,c,d,……)
创建索引:
创建的方式一:
create 索引类型 索引名 on 表(字段)
单值:
create index dept_index on tb(dept);
唯一:
create unique index name_index on tb(name);
复合索引:
create index dept_name_index on tb(dept,name);
创建的方式二:
alter table 表名 索引类型 索引名(字段)
单值:
alter table tb add index dept_index(dept);
唯一:
alter table tb add unique index name_index(name);
复合索引:
alter table tb add index dept_name_index(dept,name);
注意:如果一个字段是primary key ,则改字段默认就是 主键索引
删除索引:
- drop index 索引名 on 表名 ;
- drop index name_index on tb ;
查询索引:
- Show index from 表名;
创建索引以后,在查询时,就会有速度上的提升,由于机器原因,测试这项内容需要很大的数据量(上百万条)。网上有很多测试的实例,也可以直接去访问这里的第三点,比较具体的测试了数据优化的展示,测试数据量为300W。
优化的注意具体事项
若想利用索引达到预想的提高查询速度的效果,我们在添加索引时,必须遵循以下原则
1、最左前缀匹配原则,非常重要的原则,
create index ix_name_email on s1(name,email,)
- 最左前缀匹配:必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,
d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2、=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),
表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、
性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,
这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
4、索引列不能参与计算,保持列“干净”
比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,
但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
最左前缀示范
mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.39 sec)#sec是单位秒
mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀
Query OK, 0 rows affected (15.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.43 sec)
mysql> drop index idx on s1;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀
Query OK, 0 rows affected (15.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.03 sec)
最左前缀匹配
2 index(id,age,email,name)
3 #条件中一定要出现id(只要出现id就会提升速度)
4 id
5 id age
6 id email
7 id name
8
9 email #不行 如果单独这个开头就不能提升速度了
10 mysql> select count(*) from s1 where id=3000;
11 +----------+
12 | count(*) |
13 +----------+
14 | 1 |
15 +----------+
16 1 row in set (0.11 sec)
17
18 mysql> create index xxx on s1(id,name,age,email);
19 Query OK, 0 rows affected (6.44 sec)
20 Records: 0 Duplicates: 0 Warnings: 0
21
22 mysql> select count(*) from s1 where id=3000;
23 +----------+
24 | count(*) |
25 +----------+
26 | 1 |
27 +----------+
28 1 row in set (0.00 sec)
29
30 mysql> select count(*) from s1 where name='egon';
31 +----------+
32 | count(*) |
33 +----------+
34 | 299999 |
35 +----------+
36 1 row in set (0.16 sec)
37
38 mysql> select count(*) from s1 where email='egon3333@oldboy.com';
39 +----------+
40 | count(*) |
41 +----------+
42 | 1 |
43 +----------+
44 1 row in set (0.15 sec)
45
46 mysql> select count(*) from s1 where id=1000 and email='egon3333@oldboy.com';
47 +----------+
48 | count(*) |
49 +----------+
50 | 0 |
51 +----------+
52 1 row in set (0.00 sec)
53
54 mysql> select count(*) from s1 where email='egon3333@oldboy.com' and id=3000;
55 +----------+
56 | count(*) |
57 +----------+
58 | 0 |
59 +----------+
60 1 row in set (0.00 sec)
索引无法命中的情况需要注意:
- like '%xx'
select * from tb1 where email like '%cn';
- 使用函数
select * from tb1 where reverse(email) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or name = 'seven@live.com';
特别的:当or条件中有未建立索引的列才失效,以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
- 类型不一致
如果列是字符串类型,传入条件是必须用引号引起来,不然...
select * from tb1 where email = 999;
普通索引的不等于不会走索引
- !=
select * from tb1 where email != 'alex'
特别的:如果是主键,则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > 'alex'
特别的:如果是主键或索引是整数类型,则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
#排序条件为索引,则select字段必须也是索引字段,否则无法命中
- order by
select name from s1 order by email desc;
当根据索引排序时候,select查询的字段如果不是索引,则不走索引
select email from s1 order by email desc;
特别的:如果对主键排序,则还是走索引:
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为:(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
- count(1)或count(列)代替count(*)在mysql中没有差别了
- create index xxxx on tb(title(19)) #text类型,必须制定长度
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(经常使用多个条件查询时)
- 尽量使用短索引
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合