[TOC] # 索引 ## **什么是索引?** 索引是一种数据结构，可以帮助我们快速的进行数据查找。 ## **MySQL 索引有哪几种** 常见的有普通索引（Index）、主键索引（Primary Key）、唯一索引（Unique）、全文索引（Fulltext） ## **索引是个什么样的数据结构** 索引的数据结构和具体的存储引擎有关，在MySQL中使用较多的索引有Hash索引,B+树索引。InnoDB 存储引擎的默认索引实现为：B+树索引。 ## **Hash索引和B+树索引的区别** Hash 索引底层及 Hash表，查找时，调用一次 hash 函数就可以获取相应的键值，之后进行**回表**查询获取实际数据。 B+Tree 底层实现是**多路平衡查找树**，对于每次查询都是从根节点出发，查找到叶子节点就可以获得所查键值，然后根据查询判断是否需要回表查询数据。 Hash 索引经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。 B+Tree 的所有节点遵循 **左节点小于父节点，右节点大于父节点**，支持范围操作。 * Hash索引进行等值查询更快（一般情况下），无法进行范围查询 * Hash索引不支持使用索引进行排序 * Hash索引不支持模糊查询及多列索引的**最左前缀匹配** * Hash索引每次查询都要**回表**，而B+树在符合某些条件(**聚簇索引**,**覆盖索引**等)的时候可以只通过索引完成查询。 * Hash索引不稳定，性能不可预测，当某个键值在大量重复的时候，发生 **Hash碰撞**，此时效率极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低. ## **什么是聚簇索引、非聚簇索引** **聚簇索引**：索引的叶节点指向数据。**非聚簇索引**：索引的叶节点指向数据的引用。 InnoDB 中，主键索引就是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键则隐式的生成一个键来建立聚簇索引。 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。 ## **非聚簇索引一定会回表查询吗** 不一定。 如果查询语句中要求的字段全部命中了索引，则不需要**回表查询**。 ## **索引使用原则** * 列独立 保证索引包含的字段独立在查询语句中，不能是在表达式中 * 左前缀 like:：匹配模式左边不能以通配符开始，才能使用索引 * 复合索引有左到右失效 最左原则，要同时考虑列查询的频率和列的区分度。 * 不滥用索引，多余索引会降低读写性能 **即使满足了上述原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大** ## **索引哪些情况会失效** * 查询条件包含 `or`。 * 隐式类型转换。例：`age` 字段类型为 **int**，`where age = '1'` 就会触发隐式类型转换。 * `like` 通配符在左边。`%a` * 联合索引条件不符合最左前缀原则 * 对索引字段进行函数运算 * 对索引列运算 * 索引字段上使用 `!=`、`<>`、`not in` 时 * 索引字段上使用 `is null`，`is not null` * 相 `join`的两个表字符编码不同，会导致笛卡尔积的循环计算 * MySQL 认为使用全表扫描比使用索引快时。 * 查询的数据超过总数据行数30% ## **索引不适合哪些场景** * 数据量少的不适合 * 更新比较频繁的不合适 * 离散型低的字段不合适 # 锁 ## **都有哪些锁** **共享锁**（S锁、读锁）：： 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即多个客户可以同时读取同一个资源，但不允许其他客户修改。 **排他锁**（X锁、写锁）： 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读锁和写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。 锁的粒度取决于具体的存储引擎,InnoDB实现了**行级锁,页级锁,表级锁**。 **读读不阻塞，读写阻塞，写写阻塞** # 表结构设计 ## **为什么要尽量设定一个主键?** 主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的 ID 列作为主键，设定了主键之后，在后续的删改查的时候可能更加快速，以及确保操作数据范围安全。 ## **主键使用自增ID还是UUID** 使用自增ID,不要使用UUID 因为在 InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的 B+Tree 叶子节点上存储了主键索引以及全部的数据（按照顺序） 如果主键索引的自增ID，那么只需要不断向后排列即可，如果是UUID，由于到老的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后产生很多的内存碎片，造成插入性能下降。 ## **字段为什么要求定义为not null** null 值会占用更多的字节,且会在程序中造成很多与预期不符的情况. ## **如果要存储用户的密码散列,应该使用什么字段进行存储** 密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率. ## **varchar和char有什么区别.** `char`是一个定长字段,假如申请了`char(10)`的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间. 在检索效率上来讲,`char `> `varchar`,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char. ## **MySQL的binlog有有几种录入格式?分别有什么区别** * Statement：每一条会修改数据的sql都会记录到master的bin-log中。 * Row：记录每一行数据被修改的形式。 * Mixed：根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。 ## ****关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?**** 在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们. 慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大? * 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写. * 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引. * 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表. ## **横向分表和纵向分表** **横向分表**是按行分表，假设我们有一张用户表，主键是自增 id 且同时是用户的 id，数据量较大，有1亿多条，那么此时放在一张表里查询效率就不理想，我们可以根据主键 ID 分表，无论是按尾号分，或者按 id 的区间分都可以。假设按照尾号0-99分为 100 个表，那么每张表中的数据就仅有100W。 **纵向分表**是按列分表，假设我们现有一张文章表，包含字段`id-摘要-内容`。而系统中的展示形式是刷新出一个列表，列表中仅包含标题和摘要，当用户点击谋篇文章详情时才无需要正文内容，此时如果数据量大，将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度，可以将上面表分为两张`id-摘要`，`id-内容` ，当用户点击详情时按照id 取出内容，而增加的存储量只是很小的主键字段.代价很小. ## **三大范式** * 每个列都不可以在拆分 * 非主键列完全依赖于主键，而不能是依赖于主键的一部分 * 非主键列只能依赖于主键，不依赖于其他主键。