目录
索引
1. 索引的概念和优点（了解）
概念：
索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。
优点：
(1) 创建唯一性索引，保证数据库表中每一行数据的唯一性
(2) 大大加快数据的检索速度，这也是创建索引的最主要的原因
(3) 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
(4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
2. 索引的分类（必会）
(1) 普通索引：最基本的索引，它没有任何限制。
(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key 来约束。
(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
(5) 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。
3. 索引的底层实现原理（高薪常问）
1. 索引结构
B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.
Hash 索引: 只有 Memory 引擎支持, 使用场景简单.
S-Full-text(全文索引): 全文索引也是 MyISAM 的一个特殊索引类型, 主要用于全
2. BTree 结构
B+Tree 是在 BTree 基础上进行演变的, 所以我们先来看看 BTree, BTree 又叫多路平衡搜索树, 一颗 m 叉 BTree 特性如下:
(1) 树中每个节点最多包含 m 个孩子.
(2) 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil 函数指向上取整).
(3) 若根节点不是叶子节点, 则至少有两个孩子.
(4) 每个非叶子节点由 n 个 Key 和 n+1 个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.
3.Mysql 中的 B+Tree
B+Tree 为 BTree 的变种, B+Tree 与 BTree 的区别:
1. B+Tree 的叶子节点保存所有的 key 信息, 依 key 大小顺序排列.（出度d大，非叶子节点不包含表数据，树的高度小）
2. 查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）
3. 遍历效率高（从符合条件的某个叶子节点开始遍历即可）,B+Tree 叶子节点元素维护了一个单项(顺序)链表方便扫库，B树必须用中序遍历的方法按序扫库。
由于 B+Tree 只有叶子节点保存 key 信息, 查询任何 key 都要从 root 走的叶子. 所以B+Tree 查询效率更稳定。
Mysql 中的 B+Tree
MySql 索引数据结构对经典的 B+Tree 进行了优化, 在原 B+Tree 的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的 B+Tree, 提高区间访问的性能。
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能：如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。
联合索引在B+树上的存储结构及数据查找方式
联合索引存储结构：
对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。对于联合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，如，1 1 5 12 13…它是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树，上图中的1 1 4 ，1 1 5以及13 12 4, 13 16 1, 13 16 5就可以说明这种情况。
联合索引 bcd ,等同于建立了三个索引，b,bc,bcd, 在索引树中的样子如下图
联合索引具体查找步骤：
1. 存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址（此处实际上是存在一个指针的，指向的是下一个节点的磁盘位置）。
2. 进行一次磁盘IO，将此节点值加载后内存中，然后根据第一步一样进行判断，发现 数据都是匹配的，然后根据指针将此联合索引值所在的叶子节点也从磁盘中加载后内存，此时又发生了一次磁盘IO，最终根据叶子节点中索引值关联的 主键值 。
3. 根据主键值 回表 去主键索引树（聚簇索引）中查询具体的行记录。
联合索引的最左前缀原则：
最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。
首先我们创建的idx_t1_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引。
索引的第一列也就是b列可以说是从左到右单调递增的，但我们看c列和d列并没有这个特性，它们只能在b列值相等的情况下这个小范围内递增，如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。
由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引。
4. 如何避免索引失效（高薪常问）
(2) 不要在索引上使用运算, 否则索引也会失效.
(3) 字符串不加引号, 造成索引失效.
(4) 尽量使用覆盖索引, 避免 select *, 这样能提高查询效率.
(5) or 关键字连接 ----如果一定要用 or 查询, 可以考虑下 or 连接的条件列都加索引, 这样就不会失效了.
MySql 优化(定位慢查询)
1) 定位执行效率慢的 sql 语句。
命令:show status like 'Com____',通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
explain + sql语句查询sql执行过程, 通过执行计划，我们能得到以下信息：
A：哪些步骤花费的成本比较高
B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
C：这条sql语句是否走索引
show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化这条 sql.
慢查询日志(常用的工具)
慢查询日志记录了所有执行时间超过参数 long_query_time 的 sql 语句的日志 ,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下,有个 slow_query.log 文件。
优化索引设计
2.1 索引设计原则
对查询频次较高, 且数据量比较大的表, 建立索引.
索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
使用唯一索引, 区分度越高, 使用索引的效率越高.
索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率.
使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
如果 where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.
复合索引命名规则 index_表名_列名 1_列名 2_列明 3
比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)
2.2 避免索引失效
如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开始, 并且不能跳过索引中的列.
尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数.计算表达式等, 都会是索引失效.
查询 like，如果是 ‘%aaa’ 也会造成索引失效.
应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描.
3) Sql 语句调优
根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率.
多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.
排序字段上, 因为排序效率低, 添加索引能提高查询效率.
优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.
优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用 order by null 禁用.
select age, count(*) from emp group by age order by null
尽量避免子查询, 可以将子查询优化为 join 多表连接查询.
4) 合理的数据库设计(了解)
i. 第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
ii. 第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
iii. 第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。
注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：
A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。
C：分割表。
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
D: 字段设计
1. 表的字段尽可能用 NOT NULL
2. 字段长度固定的表查询会更快
3. 把数据库的大表按时间或一些标志分成小表
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
