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 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树更小。如果把所有同一内