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 ALL 和 UNION 不同之处在于 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树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。举个例子,假设磁盘中的一个盘块容纳16bytes,而一个关键字2bytes,一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候,B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
2) B+-tree的查询效率更加稳定
由于非叶子结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。<