数据库基本的优化(三)

1.常见的关系型数据库管理系统有哪些

  • Oracle、SQL Server、Mysql、Sybase、DB2、Access

2.SQL语言包括哪几个部分?每个部分都有哪些操作关键字

  • 数据定义、数据操纵、数据控制、数据查询
    • 数据定义:Create Table,Alter Table,Drop Table,Create/Drop Index等

    • 数据操纵:Select,insert,update,detele

    • 数据控制:grant,revoke

    • 数据查询:select

3.完整性约束包括哪些

  • 数据完整性是指 数据的精确和可靠性。分以下四类:
    • 实体完整性:规定表打每一行在表中是唯一打实体。

    • 域完整性:是指表中打列表必须满足某种特定打数据类型约束,其中约束又包括取值范围、精度等规定。

    • 参照完整性:是指表中打主关键字和外关键字的数据应一致,保证了表之间的数据一致性,防止了数据丢失或无意义打数据在数据库中扩散。

    • 用户定义打完整性:不同打关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。

4.什么是事务?及其特性

  • 事务:事务是指作为单个逻辑工作单元执行的一系列操作,可以被看作一个单元的一系列SQL语句的集合。要么完全地执行,要么完全地不执行。

  • 事务特性:

    • 原子性:即不可分割性,事务要么全部执行,要么就全部不执行。

    • 一致性或可串性:事物的执行使得数据库从一种正确状态转换成另一种正确状态。

    • 隔离性:在事务正确提交之前,不允许把事务对数据的任何改变提供给任何其他事务。

    • 持久性:事务正确提交后,其结果将永久保存在数据库中,即使在食物提交后有了其他故障,事务的处理结果也会得到保存。

5.什么是锁

  • 数据库是一个多用户使用的共享资源。当多个用户并发的存取数据时,在数据库中就会产生多个事务同时取同一数据的清空。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

    • 当事务在对某个数据进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其它的事务不能对此数据对象进行更新操作。

    • 基本锁类型:锁包括行锁和表锁。

有哪些锁?
可以按照锁的粒度把数据库锁分为  行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎)

行级锁:
    行级锁时Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁力度最小,但加锁的开销也最大。行级锁分为 共享锁 和 排他锁。
    特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
    
表级锁:
    表级锁是MySql中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较小,被大部分MySql引擎支持。最常使用的MYISAM与INNODB都支持表级锁。表级锁分为 表共享读锁(共享锁)与 表独占写锁(排他锁)。
    特点:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突概率最高,并发度最低。
    
页级锁:
    页级锁是MySql中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,单冲突多,行级锁冲突少,但速度慢。所以取了折中的页级,一次锁定相邻的一组记录。
    特点:开销加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并法度一般。

什么是死锁?怎么解决?

死锁是指两个或多个事物在同一资源相互占用,并请求锁定对方多资源,从而导致恶性循环多现象。

常见多解决死锁多方法:

  • 1、如果不同程序会并发存取多个表,尽量约定以相同多顺序访问表,可以大大降低死锁机会。

  • 2、在同一事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。

  • 3、对于非常容易产生死锁多业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生多概率。

如果业务处理不好,可以用分布式事务锁或则使用乐观锁。

悲观锁:指对数据被意外修改持保守态度,假装被人会修改数据,在事务开始前执行锁定,执行完后再释放锁定。这对于长事务来说,可能会严重影响系统的并发处理能力。自带多数据库事务就是典型的悲观锁。

乐观锁:每次去拿数据多时候都认为别人不会修改,所以不会上锁,但是在提交更新多时候会判断以下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景,这样可以提高吞吐量。

乐观锁一般是加一个版本号字段,每次更新时候比较版本号;
或者是加一个字段记录当前时间戳字段,每次更新时对比更新之前取出的时间戳是否相等。

6.什么叫视图?游标是什么?

  • 视图:是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增、改、查操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比比多表查询。

  • 游标:是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不适用游标,但时需要逐条处理数据的时候,有标显得十分重要。

7.什么是存储过程?用什么来调用

  • 存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需要创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

8.如何通俗的理解三范式

  • 第一范式:是对属性的原子性约束,要求属性具有原子性,不可再分解;

  • 第二范式:是对记录的唯一性约束,要求记录有唯一标识,即实体的唯一性;

  • 第三范式:是对字段冗余性的约束,即任何字段不能有其他字段派生出来,它要求字段没有冗余。

9.索引的作用?和它的优缺点是什么

  • 索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。它很类似于现实生活中书的目录,不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的,创建索引允许制定单个列或者是多个列。
    • 缺点:减慢了数据录入的速度,同时也增加了数据库的尺寸大小。

10.主键、外键和索引的区别

  • 定义:
    • 主键:惟一标识一条记录,不能有重复的,不允许为空。

    • 外键:表的外键是另一个表的主键,外键可以有重复的,可以是空值。

    • 索引:该字段没有重复值,但可以有一个空值。

  • 作用:
    • 主键:用来保证数据完整性。

    • 外键:用来和其他表建立联系用的。

    • 索引:是提高查询排序的速度。

  • 个数:
    • 主键:主键只能有一个。

    • 外键:一个表可以有多个外键。

    • 索引:一个表可以有多个唯一索引。

索引有哪几种类型?什么是最左前缀原则?索引算法有哪些?有什么区别?

  • 主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键。

  • 唯一索引(UNIQUE):数据列不允许重复,允许为NULL值,一个表允许有多个列创建唯一索引。

    创建唯一索引:
      
          ALTER TABLE table_name ADD UNIQUE (column);
          or
          ALTER TABLE table_name ADD UNIQUE (column1,column2);
  • 普通索引(INDEX):基本的索引类型,没有唯一性的限制,允许为NULL值。

    创建普通索引:
      
          ALTER TABLE table_name ADD INDEX index_name (column);# 创建普通索引
          or
          ALTER TABLE table_name ADD  INDEX index_name (column1,column2);# 创建组合索引
  • 全文索引(FULLTEXT):是目前搜索引擎使用的一种关键技术。

    创建全文索引:
      
      ALTER TABLE table_name ADD FULLTEXT (column);

最左前缀

  • 就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

  • 还有一个就是生效原则,例如:

    index(a,b,c)
      
      where a = 3 # 只使用了a
      
      where a = 3 and b = 5 # 使用了a,b
      
      where a = 3 and b = 5 and c = 6 # 使用了a,b,c
      
      where a = 3 or where c = 4 # 没有使用索引
      
      where a = 3 and c = 4 # 仅使用了a
      
      where a = 3 and b > 10 and c = 7 # 使用了a,b
      
      where a = 3 and b like 'xx%' and c = 7 # 使用了a,b

索引算法 – BTree、Hash

  • BTree:是最常用的mysql数据库索引算法,也是mysql默认的算法。因为它不仅仅可以被用在 =,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量

    例如:
      
      select * from member where nickname like 'jack%';
      
      如果以通配符开头,或者没有使用常量,则不会使用索引,例如:
      
      select * from member where nickname like '%jack';
  • Hash:Hash索引只能用于对等比较,例如=,<=> (相当于=) 操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。

索引设计的原则

  • 1、适合索引的列时出现在where字句中的列,或者连接字句中指定的列。

  • 2、基数较小的列,索引效果较差,没有必要在此列建立索引。

  • 3、使用短索引,如果对长字符串进行索引,应该指定一个前缀长度,这样能够节省大量索引空间。

  • 4、不要过度索引,索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间会越长。所以只保持需要的索引有利于查询即可。

11.Mysql的存储引擎,myisam 和 innodb 的区别

  • Myisam 是非事务的存储引擎;适合用于频繁查询的应用;表锁,不会出现死锁;适合小数据,小并发。

  • innodb 是支持事务的存储引擎;适合插入和更新比较多的应用;设计合理的话是行锁(最大区别就在于锁的级别上);适合大数据,大并发。

12.Mysql数据库作为发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化

  • 设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。

  • 选择合适的表字段数据类型和存储引擎,适当的添加索引。

  • mysql库主从读写分离。

  • 找规律分表,减少单表中的数据量提高查询速度。

  • 添加缓存机制,比如memcached,apc等。

  • 不经常改动等页面,生成静态页面。

  • 书写高效等SQL。

13.对于大流量等网站,采用什么样等方法来解决各页面访问量统计问题

  • 确认服务器是否支撑当前访问量。

  • 优化数据库访问。

  • 禁止外部访问链接(防盗链),比如图片盗链。

  • 控制文件下载。

  • 使用不同主机分流。

  • 使用浏览统计软件,了解访问量,有针对性等进行优化。

14.什么是脏读?幻读?不可重复读?什么是事务的隔离级别?mysql的默认隔离级别是什么?

  • 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据就是脏数据。

  • 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCEDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

  • 不可重复读:事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据操作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

  • 事务隔离是基于锁实现的。

    有哪些锁?
      可以按照锁的粒度把数据库锁分为  行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎)

参考文档:codewill

发表评论

电子邮件地址不会被公开。 必填项已用*标注