MySQL

MySql

三范式

第一范式:(确保每列保持原子性)所有字段值都是不可分解的原子值。

第二范式:(确保表中的每列都和主键相关)在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

第三范式:(确保每列都和主键列直接相关,而不是间接相关) 数据表中的每一列数据都和主键直接相关,而不能间接相关。

一张自增表里面总共有 7 条数据,删除了最后 2 条数据,重启 mysql 数据库,又插入了一条数据,此时 id 是几?

  • 数据库引擎如果是 MyISAM ,那 id 就是 8。

  • 数据库引擎如果是 InnoDB,那 id 就是 6。

  InnoDB 表只会把自增主键的最大 id 记录在内存中,所以重启之后会导致最大 id 丢失。

索引

有哪些索引类型

三个角度分析:

1.数据结构角度上可以分为B+tree 索引、hash 索引、flfltext索引 (InnoDB、MyISAM都支持)。

2.存储角度上可以分为聚集索引和非聚集索引。

3.逻辑角度上可以分为 primary key、normal key(普通键)、单列、复合和覆盖索引。

适合创建索引

  1).主键自动建立唯一索引

  2).频繁作为查询查询条件的字段应该创建索引

3).查询中与其它表关联的字段,外键关系建立索引

  6).单键/组合索引的选择问题(在高并发下倾向创建组合索引)

  7).查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

8).查询中统计或者分组字段

不适合创建索引

  1).表记录太少

  2).经常增删改的表(因为不仅要保存数据,还要保存一下索引文件)

  3).数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。

4).频繁更新的字段不适合创建索引

  5).where条件里用不到的字段不创建索引

注意:如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

聚集索引和非聚集索引

聚集索引:

1.聚集索引:该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

2.我们的汉语字典的正文本身就是一个聚集索引。

3.们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

4.聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行 的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。

每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

非聚集索引:

1.非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

2.目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

索引的实现

(1) BTree 索引是大多数 MySQL 存储引擎的默认索引类型,不是B+Tree。 (2) 哈希索引是memory引擎表的默认索引类型,memory也支持btree。 (3) 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行(即不能使用哈希索引来做覆盖索引扫描)。 (5) 空间索引不会要求where子句使用索引最左前缀可以全方位索引数据,可以高效使用任何数据组合查找 配合使用mercontains()函数使用。 (6) 索引将随机I/O变为顺序I/O

Hash索引或者B+树索引

1.MySQL中,只有HEAP/MEMORY引擎才显示支持Hash索引。

2.常用的InnoDB引擎中默认使用的是B+树索引,它会实时监控表上索引的使用情况,如果认为建立哈希索引可以提高查询效率,则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引(在InnoDB中默认开启自适应哈希索引),通过观察搜索模式,MySQL会利用index key的前缀建立哈希索引,如果一个表几乎大部分都在缓冲池中,那么建立一个哈希索引能够加快等值查询。 B+树索引和哈希索引的明显区别是:

如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;

如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索; ​ 同理,哈希索引没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);

哈希索引也不支持多列联合索引的最左匹配规则;

B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。

在大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。

为什么要为InnoDB表设置自增列做主键

解答思路:使用自增列做主键,写入顺序是自增的,和B+数叶子节点分裂顺序一致.InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致时,存取效率是最高的。

验证 mysql 的索引是否满足需求

需要根据查询需求来决定配置索引的类型,一旦确定索引类型之后,可以使用 explain 查看 SQL 执行计划,确认索引是否满足需求。

索引失效

1.最佳左前缀法则:如果索引了多列,要尊守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。  

2.索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。  

3.存储引擎不能使用索引中范围条件右边的列。  

如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引,只有username和age会生效,phone的索引没有用到。  

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列致))

如select age from user减少select *  

5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。  

6.is null, is not null 也无法使用索引。  

7.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。  

所以最好用右边like 'abc%'。如果两边都要用,可以用select age from user where username like '%abc%',其中age是索引列,假如index(a,b,c), where a=3 and b like 'abc%' and c=4,a能用,b能用,c不能用  

8.字符串不加单引号索引失效  

9.少用or,用它来连接时会索引失效 union all  

10.尽量避免子查询,而用join

sql优化& 全文扫描

其思路是尽量减少全文索引的过程,即尽量避免索引失效:

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

2.避免在 where 子句中对字段进行 null 值判断,导致引擎放弃使用索引而进行全表扫描,解决办法:可以在num上设置默认值0,然后这样查询=0

3.like前后‘%’,也会导致全文索引,下面的查询也将导致全表扫描:

  select id from t where name like '%abc%'

模糊查询:使用instr函数替换like, 用instr代替like,可以提高效率。

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

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

13.很多时候用 exists() 代替 in 是一个好的选择:(EXISTS 指定一个子查询,检测行的存在。语法:EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型为 Boolean,如果子查询包含行,则返回 TRUE。)

  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)

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

解决办法:在项目中,凡是设计到字符串类型的数据,一律采用varchar或者text类型

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

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

解决办法:查询时应该进行分页查询,用limit进行分页,如果数据很大的话,对数据库来说,避免不必要的损耗。

1、避免where 子句中使用!=或<>操作符

3、避免 where 子句null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,

4、避免 or 来连接,解决办法:union all(UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALLUNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来,无论资料值有无重复。)

5、避免前置百分号,若要提高效率,可以考虑全文检索。

6、慎用in 和 not in,否则会导致全表扫描。解决办法:对于连续的数值,能用 between 就不要用 in

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

MyISAM Innodb 索引

1.MyISAM索引实现:

1.MyISAM引擎使用B+树作为作为索引结构,叶子节点的data域存的是数据记录的地址。

2.索引文件和数据文件是分离的。所以也叫做 “非聚集”索引,之所以叫做“非聚集”索引,是因为他的索引文件只保存数据记录的地址,不包含完整的数据记录。

3.MyISAM引擎中查找流程是:按照b+树的查找规则进行的,如果指定的key存在,则取出器data域,然后在以data域的值为地址,读出相应数据记录。

2.InnoDB也使用B+树作为索引,但是与MyISAM的区别是:

1.就是他的数据文件本身就是索引文件,不像MyISAM索引那样那样 索引文件也数据文件是分离的。

因为:在innnodb中,表数据文件就是按照b+树的性质组织的一个索引文件,在棵b+树的叶节点的data域保存了完成的数据记录。这个索引的key就是数据表得主键,因此innodb表数据文件就是主索引。所以innnodb的索引也叫做“聚集”索引。

2.因为innodb的数据文件本身按主键聚集,所以要求innodb必须要有主键,如果没有显示的指定,那么Mysql会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这样的列,那么mysql会自动为innodb表生成一个隐藏的字段作为主键,这个字段的时间是6个字节,类型是长整形的。

3.第二个区别就是innodb的辅助索引data域存的是相应记录的主键,而不是地址。换句话说就是 innodb的辅助索引都用主键作为data域的值。辅助索引需要检索两边索引:首先检查辅助索引获得主键,再在主索引中利用查找到的key获得记录。

mysql为什么使用B+树

这个题可以理解为B+树在同类数据结构中的优点:

红黑树,B树,B+树:

红黑树的缺点:

在大规模数据存储的时候,红黑树会出现由于树的深度过大而造成磁盘IO读写过于频繁,导致效率低下的情况。

原因:

树的深度过大会造成磁盘IO频繁读写。要求只能通过某种较好的树结构减少树的结构尽量减少树的高度,B树可以有多个子女,从几十到上千,可以降低树的高度。

B树和B+树的区别:

1.B树所有叶子结点都出现在同一层,叶子结点不包含任何关键字信息。

2.B+树所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接,所有的非叶子结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)

B+相比B树在操作系统的文件索引和数据库索引优势:

B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。数据库中基于范围的查询是非常频繁,B树不支持这样的操作(效率太低)

1) B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值