常见问题
相关文章
- 学习MySQL优化原理
- MySQL索引-B+树（看完你就明白了） [每个叶子节点大小是页[磁盘页 4k]的整数倍，每个页子节点假设有4个内节点[磁盘页] 16k]
-
InnoDB 中一棵 B+ 树可以存放多少行数据？[约2000万,为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？]
一、索引
1.1 索引的本质
索引：是帮助MySQL高效获取数据的排好序的数据结构
索引的数据结构
- 二叉树
- 红黑树
- hash表
- B-Tree
1.2 覆盖索引
在之前《mysql索引初识》这篇文章中提到过，mysql的innodb引擎通过搜索树方式实现索引，索引类型分为主键索引和二级索引（非主键索引），主键索引树中，叶子结点保存着主键即对应行的全部数据；而二级索引树中，叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。假如我们现在有如下表结构
CREATE TABLE `user_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`age` int(11) unsigned Not NULL,
PRIMARY KEY (`id`),
key (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8上表中有 联合索引 index（id, username）
（1）执行语句(A) select id from user_table where username = 'lzs'时，因为username索引树的叶子结点上保存有username和id的值，所以通过username索引树查找到id后，我们就已经得到所需的数据了，这时候就不需要再去主键索引上继续查找了。——覆盖索引查找
（2）执行语句(B) select password from user_table where username = 'lzs'时，流程如下 ——回表查询
- username索引树上找到username=lzs对应的主键id
- 通过回表在主键索引树上找到满足条件的数据
由上面可知，当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。
例如上面的语句B是一个高频查询的语句，我们可以建立(username,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。当然，添加索引是有维护代价的，所以添加时也要权衡一下。
当然，索引不是建的越多越好，MySQL一张表最多可以建16个 ，最大索引长度256字节。
1.3 联合索引
mysql的b+树索引遵循“最左前缀”原则，继续以上面的例子来说明，为了提高语句B的执行速度，我们添加了一个联合索引（username,password）,特别注意这个联合索引的顺序，
如果我们颠倒下顺序改成（password,username),这样查询能使用这个索引吗？答案是不能的！
- 这是最左前缀的第一层含义：联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引。
现在，假设我们有一下三种查询情景：
- 查出用户名的第一个字是“张”开头的人的密码。即查询条件子句为"where username like '张%'"
- 查处用户名中含有“张”字的人的密码。即查询条件子句为"where username like '%张%'"
- 查出用户名以“张”字结尾的人的密码。即查询条件子句为"where username like '%张'"
以上三种情况下，只有第1种能够使用（username,password）联合索引来加快查询速度。
- 这就是最左前缀的第二层含义：索引可以用于查询条件字段为索引字段，根据字段值最左若干个字符进行的模糊查询。
维护索引需要代价，所以有时候我们可以利用“最左前缀”原则减少索引数量，上面的（username,password）索引，也可用于根据username查询age的情况。当然，使用这个索引去查询age的时候是需要进行回表的，当这个需求（根据username查询age）也是高频请求时，我们可以创建（username,password,age）联合索引，这样，我们需要维护的索引数量不变(联合索引也是一个索引)。
创建索引时，我们也要考虑空间代价，使用较少的空间来创建索引
假设我们现在不需要通过username查询password了，相反，经常需要通过username查询age或通过age查询username,这时候，删掉（username,password）索引后，我们需要创建新的索引，，我们有两种选择：
- （username,age）联合索引+age字段索引
- （age,username）联合索引+username单字段索引
一般来说，username字段比age字段大的多，所以，我们应选择第一种，索引占用空间较小。
1.4 索引下推
对于user_table表，我们现在有（username,age）联合索引，如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息
- 语句C如下："select * from user_table where username like '张%' and age > 10".
语句C有两种执行可能：
（1）根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。过程如下图。
（2）根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。过程如下图。
明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制
SET optimizer_switch = 'index_condition_pushdown=off';注意：
(1) innodb引擎的表，索引下推只能用于二级索引。
就像之前提到的，innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。2、索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。
假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率
select * from t where a > 2 and b > 10;
相关文章
二、数据库设计范式
数据库设计当中常用的三范式如下：
要求表中的每一列都是不可再细分的原子项。这是最低的范式要求，通常都能够被满足。
要求非主键列必须完全依赖于主键列，而不能存在部分依赖。示例如下：
| mechanism_id （组织机构代码） | employee_id （雇员编号） | ename （雇员名称） | mname （机构名称） |
|---|---|---|---|
| 28193182 | 10001 | heibaiying | XXXX公司 |
以上是一张全市在职人员统计表，主键为：机构编码 + 雇员编号。表中的雇员名称完全依赖于此联合主键，但机构名称却只依赖于机构编码，这就是部分依赖，因此违背了第二范式。此时常用的解决方式是建立一张组织机构与组织名称的字典表。
非主键列不能依赖于其他非主键列，如果其他非主键列又依赖于主键列，此时就出现了传递依赖。示例如下：
| employee_id （雇员编号） | ename （雇员名称） | dept_no (部门编号) | dname（部门名称） |
|---|---|---|---|
| 10001 | heibaiying | 06 | 开发部 |
以上是一张雇员表，雇员名称和所属的部门编号都依赖于主键 employee_id ，但部门名称却依赖于部门编号，此时就出现了非主键列依赖于其他非主键列，这就违背的第三范式。此时常用的解决方式是建立一张部门表用于维护部门相关的信息。
从上面的例子中我们也可以看出，想要完全遵循三范式设计，可能需要额外增加很多表来进行维护。所以在日常开发中，基于其他因素的综合考量，可能并不会完全遵循范式设计，甚至可能违反范式设计，这就是反范式设计。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
