精华内容
下载资源
问答
  • mysql性能调优面试题

    2020-09-11 13:28:59
    第一方面:30种mysql优化sql语句查询的方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。  2.应尽量避免在 where 子句中使用 !=或<> 操作符,否则将引擎...

    第一方面:30种mysql优化sql语句查询的方法

           1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。

      2.应尽量避免在 where 子句中使用 !=或<> 
    操作符,否则将引擎放弃使用索引而进行全表扫描。

      3.应尽量避免在 where 子句中对字段进行 null 值 判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

      select id from t where num is null

      可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

      select id from t where num=0

      4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

      select id from t where num=10 or num=20

      可以这样查询:

      select id from t where num=10

      union all

      select id from t where num=20

      5.下面的查询也将导致全表扫描:
      select id from t where name like '%abc%'

      对于 like '..%' (不以 % 开头),可以应用 colunm上的index

      6.in 和 not in 也要慎用,否则会导致全表扫描,如:

      select id from t where num in(1,2,3)

      对于连续的数值,能用 between 就不要用 in 了:

      select id from t where num between 1 and 3

      7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

      select id from t where num=@num

      可以改为强制查询使用索引:

      select id from t with(index(索引名)) where num=@num

      8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

      select id from t where num/2=100

      应改为:

      select id from t where num=100*2

      9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

      select id from t where substring(name,1,3)='abc'--name以abc开头的id

      select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

      应改为:

      select id from t where name like 'abc%'

      select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

      10.不要在 where 子句中的“=”【左边】进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

      11.在使用索引字段作为条件时,如果该索引是【复合索引】,那么必须使用到该索引中的【第一个字段】作为条件时才能保证系统使用该索引,否则该索引将不会被使用。并且应【尽可能】的让字段顺序与索引顺序相一致。(字段顺序也可以不与索引顺序一致,但是一定要包含【第一个字段】。)

      12.不要写一些没有意义的查询,如需要生成一个空表结构:

      select col1,col2 into #t from t where 1=0

      这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

      create table #t(...)

     

      13.很多时候用 exists 代替 in 是一个好的选择:

      select num from a where num in(select num from b)

      用下面的语句替换:

      select num from a where exists(select 1 from b where num=a.num)

      14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

      15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

      16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

      17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

      18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

      19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

      20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

      21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

      22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

      23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

      24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

      25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

      26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

      27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

      28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

      29.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

      30.尽量避免大事务操作,提高系统并发能力。


    上面有几句写的有问题。

    第二方面:

    select Count (*)和Select Count(1)以及Select Count(column)区别

    一般情况下,Select Count (*)和Select Count(1)两着返回结果是一样的

        假如表沒有主键(Primary key), 那么count(1)比count(*)快,

        如果有主键的話,那主键作为count的条件时候count(主键)最快

        如果你的表只有一个字段的话那count(*)就是最快的

       count(*) 跟 count(1) 的结果一样,都包括对NULL的统计,而count(column) 是不包括NULL的统计


    第三方面:

    索引列上计算引起的索引失效及优化措施以及注意事项


    创建索引、优化查询以便达到更好的查询优化效果。但实际上,MySQL有时并不按我们设计的那样执行查询。MySQL是根据统计信息来生成执行计划的,这就涉及索引及索引的刷选率,表数据量,还有一些额外的因素。

    Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer
    determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

    简而言之,当MYSQL认为符合条件的记录在30%以上,它就不会再使用索引,因为mysql认为走索引的代价比不用索引代价大,所以优化器选择了自己认为代价最小的方式。事实也的确如此


    是MYSQL认为记录是30%以上,而不是实际MYSQL去查完再决定的。都查完了,还用什么索引啊?!

    MYSQL会先估算,然后决定是否使用索引。

    ----------------------------------------------------------------------------下----------------------------------------------------------

    1. 如何设计一个高并发的系统

    ① 数据库的优化,包括合理的事务隔离级别、SQL语句优化、索引的优化

    ② 使用缓存,尽量减少数据库 IO

    ③ 分布式数据库、分布式缓存

    ④ 服务器的负载均衡

    2. 锁的优化策略

    ① 读写分离

    ② 分段加锁

    ③ 减少锁持有的时间

    ④ 多个线程尽量以相同的顺序去获取资源

    等等,这些都不是绝对原则,都要根据情况,比如不能将锁的粒度过于细化,不然可能会出现线程的加锁和释放次数过多,反而效率不如一次加一把大锁。这部分跟面试官谈了很久

    3. 索引的底层实现原理和优化

    B+树,经过优化的B+树

    主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

    4. 什么情况下设置了索引但无法使用 

    ① 以“%”开头的LIKE语句,模糊匹配

    ② OR语句前后没有同时使用索引

    ③ 数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)

    5. SQL语句的优化 

    order by要怎么处理

    alter尽量将多次合并为一次

    insert和delete也需要合并

    等等

    6. 实践中如何优化MySQL

    我当时是按以下四条依次回答的,他们四条从效果上第一条影响最大,后面越来越小。

    ① SQL语句及索引的优化

    ② 数据库表结构的优化

    ③ 系统配置的优化

    ④ 硬件的优化

    8. sql注入的主要特点

    变种极多,攻击简单,危害极大

    9. sql注入的主要危害

    未经授权操作数据库的数据

    恶意纂改网页

    私自添加系统账号或者是数据库使用者账号

    网页挂木马

    11、优化数据库的方法

    MySQL数据库优化的八大方式(经典必看)点击获取

    · 选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如'省份'、'性别'最好适用ENUM

    · 使用连接(JOIN)来代替子查询

    · 适用联合(UNION)来代替手动创建的临时表

    · 事务处理

    · 锁定表、优化事务处理

    · 适用外键,优化锁定表

    · 建立索引

    · 优化查询语句

    19. 简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)(新浪网技术部)

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
    普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说,唯一索引可以保证数据记录的唯一性。
    主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字 PRIMARY KEY 来创建。
    索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。
    索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。

    20.数据库中的事务是什么?

    事务(transaction)是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。如果一个操作失败,则事务将回滚,该事务所有操作的影响都将取消。ACID 四大特性,原子性、隔离性、一致性、持久性。

    21.了解XSS攻击吗?如何防止?

    XSS是跨站脚本攻击,首先是利用跨站脚本漏洞以一个特权模式去执行攻击者构造的脚本,然后利用不安全的Activex控件执行恶意的行为。
    使用htmlspecialchars()函数对提交的内容进行过滤,使字符串里面的特殊符号实体化。

    22.SQL注入漏洞产生的原因?如何防止?

    SQL注入产生的原因:程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤,导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

    防止SQL注入的方式:

    开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

    执行sql语句时使用addslashes进行sql语句转换

    Sql语句书写尽量不要省略双引号和单引号。

    过滤掉sql语句中的一些关键词:update、insert、delete、select、 * 。

    提高数据库表和字段的命名技巧,对一些重要的字段根据程序的特点命名,取不易被猜到的。

    Php配置文件中设置register_globals为off,关闭全局变量注册

    控制错误信息,不要在浏览器上输出错误信息,将错误信息写到日志文件中。

    25、 对于关系型数据库而言,索引是相当重要的概念,请回答有关索引的几个问题:

    a)、索引的目的是什么?

    快速访问数据表中的特定信息,提高检索速度

    创建唯一性索引,保证数据库表中每一行数据的唯一性。

    加速表和表之间的连接

    使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间

    b)、索引对数据库系统的负面影响是什么?

    负面影响:
    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。

    c)、为数据表建立索引的原则有哪些?

    在最频繁使用的、用以缩小查询范围的字段上建立索引。

    在频繁使用的、需要排序的字段上建立索引

    d)、 什么情况下不宜建立索引?

    对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。

    对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等

    26、 简述在MySQL数据库中MyISAM和InnoDB的区别

    区别于其他数据库的最重要的特点就是其插件式的表存储引擎。切记:存储引擎是基于表的,而不是数据库。

    InnoDB与MyISAM的区别:

    InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎,已经停止开发)。

    特点:

    · 行锁设计、支持外键,支持事务,支持并发,锁粒度是支持mvcc得行级锁;

     MyISAM存储引擎: 是MySQL官方提供的存储引擎,主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
    特点:

    不支持事务,锁粒度是支持并发插入得表级锁,支持表所和全文索引。操作速度快,不能读写操作太频繁;

     27、 解释MySQL外连接、内连接与自连接的区别

    先说什么是交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。

    内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
    外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中
    的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。

    左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。

    28、 写出三种以上MySQL数据库存储引擎的名称(提示:不区分大小写)

    MyISAM、InnoDB、BDB(BerkeleyDB)、Merge、Memory(Heap)、Example、Federated、
    Archive、CSV、Blackhole、MaxDB 等等十几个引擎

    33、Myql中的事务回滚机制概述

    事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

    要同时修改数据库中两个不同表时,如果它们不是一个事务的话,当第一个表修改完,可能第二个表修改过程中出现了异常而没能修改,此时就只有第二个表依旧是未修改之前的状态,而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候,当第一个表修改完,第二表修改出现异常而没能修改,第一个表和第二个表都要回到未修改的状态,这就是所谓的事务回滚

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

    答:SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。

    数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等

    数据操纵:Select ,insert,update,delete,

    数据控制:grant,revoke

    数据查询:select

    3. 完整性约束包括哪些?

    答:数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

    分为以下四类:

    1) 实体完整性:规定表的每一行在表中是惟一的实体。

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

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

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

     

    与表有关的约束:包括列约束(NOT NULL(非空约束))和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

     

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

    答:事务:是一系列的数据库操作,是数据库应用的基本逻辑单位。

    事务特性:

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

    (2)一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

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

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

    或者这样理解:

    事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。

    5. 什么是锁?

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

     

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

    基本锁类型:锁包括行级锁和表级锁

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

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

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

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

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

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

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

    9. 如何通俗地理解三个范式?  

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

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

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

    范式化设计优缺点:

    优点:

    可以尽量得减少数据冗余,使得更新快,体积小

    缺点:对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化

    反范式化:

    优点:可以减少表得关联,可以更好得进行索引优化

    缺点:数据冗余以及数据异常,数据得修改需要更多的成本

    10. 什么是基本表?什么是视图?

      

    答:基本表是本身独立存在的表,在 SQL 中一个关系就对应一个表。  视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中,是一个虚表  

    11. 试述视图的优点?

    答:(1) 视图能够简化用户的操作  (2) 视图使用户能以多种角度看待同一数据; (3) 视图为数据库提供了一定程度的逻辑独立性; (4) 视图能够对机密数据提供安全保护。

    12. NULL是什么意思

    答:NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 NULL值进行比较,并在逻辑上希望获得一个答案。

    使用IS  NULL来进行NULL判断

    13. 主键、外键和索引的区别?

    主键、外键和索引的区别

    定义:

     主键--唯一标识一条记录,不能有重复的,不允许为空

     外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值

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

     

     

    作用:

     主键--用来保证数据完整性

     外键--用来和其他表建立联系用的

     索引--是提高查询排序的速度

    个数:

     主键--主键只能有一个

     外键--一个表可以有多个外键

     索引--一个表可以有多个唯一索引

     

    14. 你可以用什么来确保表格里的字段只接受特定范围里的值?

    答:Check限制,它在数据库表格里被定义,用来限制输入该列的值。

    触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。

    15. 说说对SQL语句优化有哪些方法?(选择几条)

    (1)Where子句中:where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

    (2)用EXISTS替代IN、用NOT EXISTS替代NOT IN。

    (3) 避免在索引列上使用计算

    (4)避免在索引列上使用IS NULL和IS NOT NULL

    (5)对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。  

    (6)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

    (7)应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

    16. SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?

    答:子查询:嵌套在其他查询中的查询称之。

    子查询又称内部,而包含子查询的语句称之外部查询(又称主查询)。

    所有的子查询可以分为两类,即相关子查询和非相关子查询

    (1)非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。

    (2)相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。

    故非相关子查询比相关子查询效率高

     

    17. char和varchar的区别?

    答:是一种固定长度的类型,varchar则是一种可变长度的类型,它们的区别是:  

    char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空格字符将被去掉)在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节).  

    varchar得适用场景:

    字符串列得最大长度比平均长度大很多 2.字符串很少被更新,容易产生存储碎片 3.使用多字节字符集存储字符串

    Char得场景:

        存储具有近似得长度(md5值,身份证,手机号),长度比较短小得字符串(因为varchar需要额外空间记录字符串长度),更适合经常更新得字符串,更新时不会出现页分裂得情况,避免出现存储碎片,获得更好的io性能

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

    答:简单的表达:

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

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

    19. 数据表类型有哪些

           答:MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。

           MyISAM:成熟、稳定、易于管理,快速读取。一些功能不支持(事务等),表级锁。

           InnoDB:支持事务、外键等特性、数据行锁定。空间占用大,不支持全文索引等。

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

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

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

    c. mysql库主从读写分离。

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

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

    f. 不经常改动的页面,生成静态页面。

    g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

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

    答:a. 确认服务器是否能支撑当前访问量。

    b. 优化数据库访问。

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

    d. 控制文件下载。

    e. 使用不同主机分流。

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

    4、如何进行SQL优化?(关于后边的解释同学们可以进行理解,到时根据自己的理解把大体意思说出来即可)

    答:

    (1)选择正确的存储引擎

    以 MySQL为例,包括有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。

    MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

    InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。但是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。

    (2)优化字段的数据类型

    记住一个原则,越小的列会越快。如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要留够足够的扩展空间。

    (3)为搜索字段添加索引

    索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么最好是为其建立索引,除非你要搜索的字段是大的文本字段,那应该建立全文索引。

    (4)避免使用Select *从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用*通配符,善用内置提供的字段排除定义也许能给带来更多的便利。

    (5)使用 ENUM 而不是 VARCHAR

    ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

    (6)尽可能的使用 NOT NULL

    除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。

    (7)固定长度的表会更快

    如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

    固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

    并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。

     

    22,为表中得字段选择合适得数据类型(物理设计)

     字段类型优先级: 整形>date,time>enum,char>varchar>blob,text

     优先考虑数字类型,其次是日期或者二进制类型,最后是字符串类型,同级别得数据类型,应该优先选择占用空间小的数据类型

    23:存储时期

    Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间,精确到秒,占用8个字节得存储空间,datatime类型与时区无关

    Timestamp:以时间戳格式存储,占用4个字节,范围小1970-1-1到2038-1-19,显示依赖于所指定得时区,默认在第一个列行的数据修改时可以自动得修改timestamp列得值

    Date:(生日)占用得字节数比使用字符串.datatime.int储存要少,使用date只需要3个字节,存储日期月份,还可以利用日期时间函数进行日期间得计算

    Time:存储时间部分得数据

    注意:不要使用字符串类型来存储日期时间数据(通常比字符串占用得储存空间小,在进行查找过滤可以利用日期得函数)

    使用int存储日期时间不如使用timestamp类型

    展开全文
  • php开发面试题---数据库SQL调优的几种方式 一、总结 一句话总结: 创建索引:尽量避免使用or或者like,或者sql中的正则 存储查询中间结果 可以加sphinx搜索技术 查询优化 主从数据库 数据库连接池 1、sql...

    php开发面试题---数据库SQL调优的几种方式

    一、总结

    一句话总结:

    创建索引:尽量避免使用or或者like,或者sql中的正则
    存储查询中间结果
    可以加sphinx搜索技术
    查询优化

    主从数据库

    数据库连接池

     

    1、sql调优的一些细节调优方法?

    用varchar/nvarchar 代替 char/nchar:变长字段存储空间小
    使用表的别名:减少解析的时间并减少哪些友列名歧义引起的语法错误
    用where字句替换HAVING字句:HAVING只会在检索出所有记录之后才对结果集进行过滤
    用varchar/nvarchar 代替 char/nchar
    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 
    
    使用表的别名
    当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
    
    用where字句替换HAVING字句
    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。

     

     

    2、sql调优的  查询语句  调优的方法?

    查询select语句优化:不要使用 select * from t
    更新Update语句优化:如果只更改1、2个字段,不要Update全部字段
    删除Delete语句优化语句:
    插入Insert语句优化:
    查询select语句优化:
    1.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段
    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,
    
    更新Update语句优化:
    如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志
    
    插入Insert语句优化:
    在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

     

     

     

    二、数据库SQL调优的几种方式(转)

    转自:数据库SQL调优的几种方式
    https://blog.csdn.net/u010520146/article/details/81161762

     

    在项目中,SQL的调优对项目的性能来讲至关重要,所有掌握常见的SQL调优方式是必不可少的,下面介绍几种常见的SQL的调优方式,供借鉴.

    一.创建索引

    1.要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
    2.(1)在经常需要进行检索的字段上创建索引,比如要按照表字段username进行检索,那么就应该在姓名字段上创建索引,如果经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。
    (2)创建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。
    (3)一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

    二.避免在索引上使用计算

    在where字句中,如果索引列是计算或者函数的一部分,DBMS的优化器将不会使用索引而使用全表查询,函数
    属于计算的一种,同时在in和exists中通常情况下使用EXISTS,因为in不走索引
    效率低:

     select * from user where salary*22>11000(salary是索引列)
    

    效率高:

     select * from user where salary>11000/22(salary是索引列)
    

    三.使用预编译查询

    程序中通常是根据用户的输入来动态执行SQL,这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞
    攻击,最重要数据库会对这些参数化SQL进行预编译,这样第一次执行的时候DBMS会为这个SQL语句进行查询优化
    并且执行预编译,这样以后再执行这个SQL的时候就直接使用预编译的结果,这样可以大大提高执行的速度。

    四.调整Where字句中的连接顺序

    DBMS一般采用自下而上的顺序解析where字句,根据这个原理表连接最好写在其他where条件之前,那些可以
    过滤掉最大数量记录。

    五.尽量将多条SQL语句压缩到一句SQL中

    每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程
    是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。

    六.用where字句替换HAVING字句

    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前
    刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数
    的过滤,除此之外,应该将条件写在where字句中。

    七.使用表的别名

    当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减
    少哪些友列名歧义引起的语法错误。

    八.用union all替换union

    当SQL语句需要union两个查询结果集合时,即使检索结果中不会有重复的记录,如果使用union这两个结果集
    同样会尝试进行合并,然后在输出最终结果前进行排序,因此如果可以判断检索结果中不会有重复的记录时候,应
    该用union all,这样效率就会因此得到提高。

    九.考虑使用“临时表”暂存中间结果

    简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
    但是也得避免频繁创建和删除临时表,以减少系统表资源的消耗。

    十.只在必要的情况下才使用事务begin translation

    SQL Server中一句SQL语句默认就是一个事务,在该语句执行完成后也是默认commit的。其实,这就是begin tran的一个最小化的形式,好比在每句语句开头隐含了一个begin tran,结束时隐含了一个commit。
    有些情况下,我们需要显式声明begin tran,比如做“插、删、改”操作需要同时修改几个表,要求要么几个表都修改成功,要么都不成功。begin tran 可以起到这样的作用,它可以把若干SQL语句套在一起执行,最后再一起commit。 好处是保证了数据的一致性,但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前,所有SQL语句锁住的资源都不能释放,直到commit掉。
    可见,如果Begin tran套住的SQL语句太多,那数据库的性能就糟糕了。在该大事务提交之前,必然会阻塞别的语句,造成block很多。
    Begin tran使用的原则是,在保证数据一致性的前提下,begin tran 套住的SQL语句越少越好!有些情况下可以采用触发器同步数据,不一定要用begin tran。

    十一.尽量避免使用游标

    尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

    十二.用varchar/nvarchar 代替 char/nchar

    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

    十三.查询select语句优化

    1.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段
    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,
    如:

         select id from t where num is null           
    

    可以在num上设置默认值0,确保表中num列没有null值,
    然后这样查询:

          select id from t where num=0
          select id from t where num=10 or num=20
    

    可以这样查询:

          select id from t where num=10
           union all
          select id from t where num=20
    

    4.不能前置百分

    select id from t where name like ‘%abc%’
    

    若要提高效率,可以考虑全文检索。

         select id from t where num in(1,2,3)
    

    对于连续的数值,能用 between 就不要用 in 了:

        select id from t where num between 1 and 3 
    

    6.如果查询的两个表大小相当,那么用in和exists差别不大。
    in:
    例如:表A(小表),表B(大表)

     select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;     
     select * from A where exists(select cc from B where cc=A.cc)   效率高,用到了B表上cc列的索引。   
    

    相反的

     select * from B where cc in (select cc from A)  效率高,用到了B表上cc列的索引;
     select * from B where exists(select cc from A where cc=B.cc)  效率低,用到了A表上cc列的索引。         
    

    十四.更新Update语句优化

    1.如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志

    十五. 删除Delete语句优化语句

    1.最高效的删除重复记录方法 ( 因为使用了ROWID)例子:

    DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
    

    十六.插入Insert语句优化

    1.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

     

    转载于:https://www.cnblogs.com/Renyi-Fan/p/10932118.html

    展开全文
  • Java中的面试题 [5] --- 数据库知识、MySQL、sql调优、sql语句设计 1、请画出权限模块的数据库设计图。 2、数据库优化?SQL优化? 3、根据数据模型画出物理模型并写出对应的SQL 。 4、数据库优化 ,子查询和左外...

    Java中的面试题 [5] --- 数据库知识、MySQL、sql调优、sql语句设计


    1、请画出权限模块的数据库设计图。

    2、数据库优化?SQL优化?    

    3、根据数据模型画出物理模型并写出对应的SQL  。

    4、数据库优化 ,子查询和左外那个效率高,内连接和左外连接。

    5、有做过哪些优化方案,数据库优化方案?

    6、如何建立索引,建立何种索引,如果现在要查询三个字段,有一个字段建立了索引。能否使用索引,讲解一下sql优化,及其替代方案。   

    7、MySQL的2个引擎的用法?

    8、如何用一条语句把一张表的数据插入到另一张数据库表中?

    9、如何用一条语句把一张表的数据插入到另一张不存在的数据库表中?  

    10、有如下A、B两个表,如何将A表的数据查询出来,结果如B所示?




    11、有一个employee表,其结构如下所示:

     empNo

     int

     empName           

     varchar2(20)  

     age

     int

     salary

     number(7,2)

    假设这个表的数据量很大,有几千万,请你用一条你觉得最有效率的SQL语句统计出如下四种类型的人数:

    • age>30 and salary>10000
    • age>30 and salary<10000
    • age<30 and salary>10000
    • age<30 and salary<10000

    12、




    展开全文
  • 小编整理了一些MySQL的资料跟面试题有兴趣可以点击获取!!! MySQL索引 MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。...

    本文有参考网上其他相关文章,本文最后有附参考的链接
    小编整理了一些MySQL的资料跟面试题有兴趣可以点击获取!!!

    MySQL索引

    MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引。

    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

    MySQL索引原理

    索引目的

    索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

    咱们去图书馆借书也是一样,如果你要借某一本书,一定是先找到对应的分类科目,再找到对应的编号,这是生活中活生生的例子,通用索引,可以加快查询速度,快速定位。

    索引原理

    所有索引原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。

    数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between)、模糊查询(like)、并集查询(or)、多值匹配(in【in本质上属于多个or】)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

    索引结构

    任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

    b+树的索引结构解释

    浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

    b+树的查找过程

    如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    b+树性质
    1. 通过上面的分析,我们知道间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

    2. 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

    MySQL 索引实现

    在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

    MyISAM索引实现

    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。 下图是MyISAM索引的原理图:

    这里设表一共有三列,假设我们以Col1为主键,则上图便是一个MyISAM表的主索引(Primary key)示意图。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

    InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

    第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:

    这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

    了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    如何建立合适的索引

    建立索引的原理

    一个最重要的原则是最左前缀原理,在提这个之前要先说下联合索引,MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列。另外,单列索引可以看成联合索引元素数为1的特例。

    索引匹配的最左原则具体是说,假如索引列分别为A,B,C,顺序也是A,B,C:

    - 那么查询的时候,如果查询【A】【A,B】 【A,B,C】,那么可以通过索引查询
    - 如果查询的时候,采用【A,C】,那么C这个虽然是索引,但是由于中间缺失了B,因此C这个索引是用不到的,只能用到A索引
    - 如果查询的时候,采用【B】 【B,C】 【C】,由于没有用到第一列索引,不是最左前缀,那么后面的索引也是用不到了
    - 如果查询的时候,采用范围查询,并且是最左前缀,也就是第一列索引,那么可以用到索引,但是范围后面的列无法用到索引
    

    因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好

    在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。如果从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

    InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下:

    这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

    如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,如下:

    此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    因此,只要可以,请尽量在InnoDB上采用自增字段做主键。

    建立索引的常用技巧

    1. 最左前缀匹配原则,非常重要的原则,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’);

    5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可,当然要考虑原有数据和线上使用情况

    MySQL优化

    配置优化

    配置优化指的MySQL 的 server端的配置,一般对于业务方而言,可以不用关注,毕竟会有专门的DBA来处理,但是对于原理的了解,我想,我们开发,是需要了解的

    基本配置

    • innodb_buffer_pool_size
      • 这是安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方:这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)。
    • innodb_log_file_size
      • 这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高了。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6,你可以一开始就把它这是成4G。
    • max_connections
      • 如果你经常看到‘Too many connections’错误,是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接,你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

    InnoDB配置

    • innodb_file_per_table
      • 这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里(innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的,比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是:有非常多的表(比如10k+)。MySQL 5.6中,这个属性默认值是ON,因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON,因为它只对新创建的表有影响。
    • innodb_flush_log_at_trx_commit
      • 默认值为1,表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的,比如在一个主节点上。但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠(reliable)因为提交的事务仅仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了,但在系统崩溃时可能丢失一些数据:只适用于备份节点。
    • innodb_flush_method
      • 这项配置决定了数据和日志写入硬盘的方式。一般来说,如果你有硬件RAID控制器,并且其独立缓存采用write-back机制,并有着电池断电保护,那么应该设置配置为O_DIRECT;否则,大多数情况下应将其设为fdatasync(默认值)。sysbench是一个可以帮助你决定这个选项的好工具。
    • innodb_log_buffer_size
      • 这项配置决定了为尚未执行的事务分配的缓存。其默认值(1MB)一般来说已经够用了,但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量,如果它不是0,增加innodb_log_buffer_size。

    其他设置

    • query_cache_size
      • query cache(查询缓存)是一个众所周知的瓶颈,甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用,设置query_cache_size = 0(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。如果你已经为你的应用启用了query cache并且还没有发现任何问题,query cache可能对你有用。这是如果你想停用它,那就得小心了。
    • log_bin
      • 如果你想让数据库服务器充当主节点的备份节点,那么开启二进制日志是必须的。如果这么做了之后,还别忘了设置server_id为一个唯一的值。就算只有一个服务器,如果你想做基于时间点的数据恢复,这(开启二进制日志)也是很有用的:从你最近的备份中恢复(全量备份),并应用二进制日志中的修改(增量备份)。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽,你可以用 PURGE BINARY LOGS 来清除旧文件,或者设置 expire_logs_days 来指定过多少天日志将被自动清除。记录二进制日志不是没有开销的,所以如果你在一个非主节点的复制节点上不需要它的话,那么建议关闭这个选项。
    • skip_name_resolve
      • 当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。

    SQL 调优

    一般要进行SQL调优,那么就说有慢查询的SQL,系统或者server可以开启慢查询日志,尤其是线上系统,一般都会开启慢查询日志,如果有慢查询,可以通过日志来过滤。但是知道了有需要优化的SQL后,下面要做的就是如何进行调优

    慢查询优化基本步骤

    1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
    2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
    3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
    4. order by limit 形式的sql语句让排序的表优先查
    5. 了解业务方使用场景
    6. 加索引时参照建索引的几大原则
    7. 观察结果,不符合预期继续从0分析

    常用调优手段

    执行计划explain

    在日常工作中,我们有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。

    使用explain 只需要在原有select 基础上加上explain关键字就可以了,如下:

    mysql> explain select * from servers;
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+
    |  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------+
    1 row in set (0.03 sec)
    
    

    简要解释下explain各个字段的含义

    1. id : 表示SQL执行的顺序的标识,SQL从大到小的执行
    2. select_type:表示查询中每个select子句的类型
    3. table:显示这一行的数据是关于哪张表的,有时不是真实的表名字
    4. type:表示MySQL在表中找到所需行的方式,又称“访问类型”。常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
    5. possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
    6. Key:key列显示MySQL实际决定使用的键(索引),如果没有选择索引,键是NULL。
    7. key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
    8. ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
    9. rows: 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,理论上行数越少,查询性能越好
    10. Extra:该列包含MySQL解决查询的详细信息

    EXPLAIN的特性

    • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
    • EXPLAIN不考虑各种Cache
    • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
    • 部分统计信息是估算的,并非精确值
    • EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。

    实战演练

    表结构和查询语句

    假如有如下表结构

    circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
      `circle_id` bigint(20) unsigned NOT NULL COMMENT '群组id',
      `from_id` bigint(20) unsigned NOT NULL COMMENT '发送用户id',
      `to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收用户id',
      `msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID',
      `type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '消息类型',
      PRIMARY KEY (`msg_id`,`to_id`),
      KEY `idx_from_circle` (`from_id`,`circle_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    
    

    通过执行计划explain分析如下查询语句

    mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063  and msg_id>=6273803462253938690  and from_id != 113487 order by msg_id asc limit 30;
    +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
    | id | select_type | table               | type  | possible_keys           | key     | key_len | ref  | rows   | Extra       |
    +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
    |  1 | SIMPLE      | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16      | NULL | 349780 | Using where |
    +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
    1 row in set (0.00 sec)
    
    
    mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063   and from_id != 113487 order by msg_id asc limit 30;
    +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
    | id | select_type | table               | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
    +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
    |  1 | SIMPLE      | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16      | NULL |   30 | Using where |
    +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    

    问题分析

    通过上面两个执行计划可以发现当没有msg_id >= xxx这个查询条件的时候,检索的rows要少很多,并且两者查询的时候都用到了索引,而且用到的还只是主键索引。那说明索引应该是不合理的,没有发挥最大作用。

    分析这个执行计划可以看到,当包含msg_id >= xxx 查询条件的时候,rows有34w多行,这种情况,说明检索太多,要么就是表里面确实有这么大,要么就是索引不合理没有用到索引,大都情况是没用合理用到索引。列中所用到的索引也是PRIMARY,那就可能是(msg_id,to_id)的其中一个,注意我们建立表的时候msg_id索引的顺序是在to_id前面的,因此MySQL查询一定会优先用msg_id索引,在使用了msg_id索引后,就已经检索出了34w行,并且由于msg_id的查询条件是大于等于,因此,再这个查询条件后,就不能再用到to_id的索引。

    然后再看key_len长度为16,结合 key为PRIMARY,那么可以分析得知,只有一个主键索引被用到。

    最后看看 type 值,是range,那么就说明这个查询要么是范围查询,要么就是多值匹配。

    请注意,from_id != xxx 这样的语句,是无法用到索引的。 只有from_id = xxx就可以用到所以,因此from id 的索引其实可以不用,建立索引的时候就要考虑清楚

    如何优化

    既然知道索引不合理,那么就要分析并调整索引。一般而言,我们既然要从单表里面查询,那么就需要能够知道大体,单表里面大致会有哪些数据,现在的量级大概是多少。

    然后开始下一步的分析,既然msgid是被设置为了主键,那一定是全局唯一的,所有,有多少数据量就至少会有多少条msgid;那么检索msg_id基本就是检索整个表了。我们要做的优化就是要尽量减少索引,减少查询的行数;那么就需要思考,通过查询哪些字段才能够减少行数?比如,一个张表里面,所属某个用户的数据,会不会比查询msgid的行数要少? 查询某个用户并且是属于某个圈子的,那会不会就更少了? 等等。。。

    然后根据实际情况分析,单表里面命中to_id 的行数应该是会小于命中msg_id的,因此要首先保证能够使用到to_id的索引,为此,可以设置主键的时候把msg_id和to_id的顺序交互一下;但是,由于已经是线上的表,已经有了大量数据,并且业务开始运行,这种情况下,修改主键会引发很多问题(当然修改索引是OK的),因此,不建议直接修改主键。那么,为了保证有效使用to_id的索引,就要新建一个联合索引;那么新建的联合索引的第一索引字段必然是to_id,针对此业务场景,最好能够再加上circle_id索引,这样可以快速索引;这样就得到了新的联合索引(to_id,circle_id)的索引,然后,因为要找msg_id,为此,在此基础上,再加上msg_id。最终得到的联合索引为(to_id,circle_id,msg_id);这样的话,就能够快速检索这样的查询语句了:where to_id = xxx and circle_id = xxx and msgId >= xxx

    当然,索引的建立,也不是说某个sql 语句需要啥索引,就建立某个联合索引,这样的话,索引太多的话,写的性能受影响(插入、删除、修改),然后存储空间也会相应增大;另外mysql在运行时也会消耗资源维护索引,所以,索引并不是越多越好,需要结合查询最频繁、最影响性能的sql来建立合适的索引。需要再说明的是,一个联合索引或者一组主键就是一个btree,多个索引就是多个btree

    总结

    首先我们需要深入理解索引的原理和实现,当理解了原理后,才能够更有助于我们建立合适的索引。然后我们建立索引的时候,不要想当然,要先想清楚业务逻辑,再建立对应的表结构和索引。 需要再次强调如下几点:

    1. 索引不是越多越好
    2. 区分主键和索引
    3. 理解索引结构原理
    4. 理解查询索引规则

    参考

    [美团-MySQL索引原理及慢查询优化]

    [MySQL索引背后的数据结构及算法原理]

    感谢参考文章的原作者

    整理了一些MySQL的资料跟面试题有兴趣可以点击获取!!!

    在这里插入图片描述

    展开全文
  • 六、Hive面试题(一) 七、Hive/Hadoop高频面试点集合(二) 基于Hadoop的数据仓库Hive基础知识 Hive是基于Hadoop的数据仓库工具,可对存储在HDFS上的文件中的数据集进行数据整理、特殊查询和分析处理,提供了...
  • 2.sql调优有哪些方法? 合理使用索引和分区表,观察执行计划,减少表连接。 3.如何进行数据库备份和还原? 备份:mysqldump 还原:MySQL,source 4.数据块坏了,快速修复有几种方法? 索引坏块损坏重建索引 物理上...
  • 数据库基础面试题汇总(关系型数据库mysql/mysql调优/底层B+ tree机制/sql执行计划详解/索引优化详解/sql语句优化) 文章目录数据库基础面试题汇总(关系型数据库mysql/mysql调优/底层B+ tree机制/sql执行计划详解/索引...
  • 说起 Hive 大家首先自然会想到的就是 SQL ,所以...SQL 语法相关的面试题。另外,光会写 SQL 还是不够的,这还只能算是基本技能,要知道 Hive 的调优才能拿到高阶工程师的入场资格。 本篇面试内容划重点:Hive 调...
  • 性能优化与调优近期面试总结

    千次阅读 2020-02-17 21:10:36
    前面已经整理发布了两篇近期面试的总结,今天顺便整理第三部分,性能优化与调优篇,后面还有其他主题的面试真题,敬请期待。 性能优化与调优篇 1.一条SQL操作比较慢请问有哪些原因 答:1.首先排查服务自身是否...

空空如也

空空如也

1 2 3 4 5
收藏数 96
精华内容 38
关键字:

sql调优面试题