Mysql索引
1、什么是索引
索引一种数据数据结构，既结构数据，大白话说：索引就是一种组织数据的方式 那么索引到底如何组织数据的呢？ 为表中的一条条记录创建建立索引就跟为书的一页页内容创建目录很类似，但不太一样 不一样的是，创建索引分为两步： 1、以索引字段为key与数据对应，例如 将id字段设置为索引， create index idx_id on t1(id); -- key为id 创建索引 key 记录1 <- 1 记录2 <- 2 记录3 <- 3 记录4 <- 4 记录5 <- 5 一个个key值所对应的就是一条条记录 2、以key为基础构建B+树
什么是B+树
""" B+树由二叉查找树,平衡二叉树,B树一步步发展而来 """
二叉查找树
""" 最初数据是一条一条存储在表中的，这种情况下我们想要搜索符合条件的数据只能做线性搜索，为了提高查询速度，给表建立了二叉查找树这种树形结构。 顶端的节点叫根节点，没有子节点的节点叫做叶节点，二叉查找树的每个节点存放的是一条记录的键值key和对应的一条完整记录。 二叉查找树的特点是左子节点的键值key比当前节点的key要小，右子节点的键值key要比当前节点的键值key大。 但是二叉树可能出现一种极端情况就是可能会出现二叉树只有右子节点，没有左子节点。 特殊情况如下图二所示 这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。
导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。 为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。 """
特殊情况
平衡二叉树
""" 为了解决二叉树的缺点，需要用到平衡二叉树。 平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。 下面是平衡二叉树和非平衡二叉树的对比： """
""" 由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。 平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。 平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。 """
B树
""" 因为内存的易失性。一般情况下，我们都会选择将表中的数据和索引存储在磁盘这种外围设备中。 但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。 如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。 那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！ """
""" 为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。 B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。 """
""" 从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。 """
B+树
B+树是对B树的进一步优化。
""" 如果每个节点不存放数据，只存放键值，每个节点下的子节点树就会更多，树会又矮又胖。 B+树就是在每个节点中放入了更多的键值key，非叶子节点不存放数据，只存放键值。 只有叶子节点才存放键值和整条记录，这使得B+树在做范围查找、分段查找、去重查找、分组查找异常简单。 """
B+树和B树有什么不同？
""" 1、B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。 2、因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。 而B树因为数据分散在各个节点，要实现这一点是很不容易的。 """
拓展
""" MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。 """
红黑树和平衡二叉树的区别
红黑树的本质
""" 1.节点是红色或黑色。 2.根节点是黑色。 3.每个叶子节点都是黑色的空节点（NIL节点）。 4 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点) 5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。 这些约束强制了红黑树的关键性质: 从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。结果是这个树大致上是平衡的。
因为操作比如插入、删除和查找某个值的最坏情况时间都要求与树的高度成比例，这个在高度上的理论上限允许红黑树在最坏情况下都是高效的，而不同于普通的二叉查找树。 """
平衡二叉树的本质
""" 它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。
但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多。 """
区别
""" 红黑树和平衡二叉树区别如下： 1、红黑树放弃了追求完全平衡，追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。 2、平衡二叉树追求绝对平衡，条件比较苛刻，实现起来比较麻烦，每次插入新节点之后需要旋转的次数不能预知。 平衡二叉树又被称为AVL树（有别于AVL算法），且具有以下性质：它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。构造与调整方法 平衡二叉树的常用算法有红黑树、AVL、Treap等。 最小二叉平衡树的节点的公式如下 F(n)=F(n-1)+F(n-2)+1 这个类似于一个递归的数列，可以参考Fibonacci数列，1是根节点，F(n-1)是左子树的节点数量，F(n-2)是右子树的节点数量。 """
特点总结
""" 1、节点内放的key+value：二叉树、平衡二叉树、B树 在叶子节点放value，其余节点只放key值：B+树 2、放等量数据的前提下，B+树的高度最低，查询速度最高 """
创建/删除索引的语法
""" 方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); 方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; 方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; 删除索引：DROP INDEX 索引名 ON 表名字; """
2、索引分类及区别
hash索引
""" 根据哈希表索引字段与哈希码建立联系。 哈希索引存储的就是哈希码。 查询单条快，范围查询慢。 """
B+树(btree索引)
""" 将键值与索引字段建立联系，以键值key为基础建立B+树 单条和范围查询都很快（我们就用它，因为innodb默认支持它） """
拓展
""" 不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引； """
3、B+数索引分类
在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。
聚集索引(聚簇索引)
""" 构建原理，只能有一个 典型特点：叶子节点放key：一整条完整记录 """
""" InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，
同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。
因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。 """
""" 聚集索引的好处之一： 它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，
由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录 聚集索引的好处之二： 范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可 """
非聚集索引(非聚簇索引、辅助索引)
""" 构建原理，可以有多个 典型特点：叶子节点放的是key:该记录对应的主键id """
""" 以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引(辅助索引)。 非聚集索引与聚集索引的区别在于： 非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。 """
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。
4、覆盖索引与回表操作
覆盖索引
""" 是指查找条件的语句命中了辅助索引的键值而且通过辅助索引获得了所需字段的全部信息。比如辅助索引的键值是name字段，主键是id，
而查询语句命中了建立辅助索引的字段且需要拿到id和name的信息，此时称为覆盖索引。 """
回表操作
""" 回表查询是指查询条件的语句命中了辅助索引但是并没有通过辅助索引获得所需的字段的全部信息，则需要通过叶子节点的主键回到聚集索引查询该字段对应的全部数据。 """
命中辅助索引是否一定需要回表？如果不，解释原因
""" 并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题 1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、 2、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，
而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录 3、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式 4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，
但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’) 5、索引下推技术的应用 6、最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 """
5、联合索引，索引的最左前缀匹配原则--->举例说明？？？
联合索引
""" 联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列，如下 mysql> create table t( -> a int, -> b int, -> primary key(a), -> key idx_a_b(a,b) -> ); Query OK, 0 rows affected (0.11 sec) """ 当使用联合索引时，必须包含联合索引的最左字段名才能命中联合索引。 联合索引还有一个好处：就是当前一个字段完全相同时，系统会将后面字段进行排序处理。
最左前缀匹配原则
""" 最左前缀匹配原则是指，建立联合索引之后，如果想要命中该索引，条件语句中的搜索字段必须包含联合索引中的最左字段。 比如对id、name、age建立联合索引 包含id、id/name、id/age、id/name/age字段的查语句都可以命中该索引。 """
6、索引下推技术
""" 索引下推（index condition pushdown ）简称ICP，在**Mysql5.6**的版本上推出，用于优化查询。 在不使用ICP的情况下，在使用**非主键索引（又叫普通索引或者二级索引）**进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，
只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。 """
7、举例说明，命中索引之后是否一定对加速有明显的提升--->等同于回答了如何正确使用索引！！！
""" 不一定,当索引出现以下情况时不能加速 """ 索引字段占用空间过多，索引字段是描述信息。 索引字段的区分度低，比如对性别、年龄字段建立索引。 索引字段参与运算，对主键id建立索引，条件为id是3的倍数。 索引放入函数，通过月薪计算年薪是否达到某个标准。 可以通过explain语句来查询执行某个SQL语句所有可能用到的方案，然后查询优化器会在对比之后选择成本最低的方案。
explaim命令简介
""" EXPLAIN为SELECT语句中使用的每个表返回一行信息 。它按照MySQL在处理语句时读取它们的顺序列出了输出中的表。
这意味着MySQL从第一个表中读取一行，然后在第二个表中然后在第三个表中找到匹配的行，依此类推。处理完所有表后，
MySQL将通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。 """
""" 执行计划：让mysql预估执行操作(一般正确) all < index < range < index_merge < ref_or_null < ref < eq_ref < const < system < NULL 慢： select * from userinfo3 where name='alex' explain select * from userinfo3 where name='alex' type: ALL(全表扫描) select * from userinfo3 limit 1; 快： select * from userinfo3 where email='alex' type: const(走索引) """
慢查询优化的基本步骤
""" 0、先运行看看是否真的很慢，注意设置SQL_NO_CACHE 1、where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 2、explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 3、order by limit 形式的sql语句让排序的表优先查 4、了解业务方使用场景 5、加索引时参照建索引的几大原则 6、观察结果，不符合预期继续从0分析 """
8、常识
""" 单表300w条记录-》硬盘空间200m uv：user view单日累计用户访问 2-5w pv：page view单日累计页面被点击的次数 20w-50w 最大并发（同时在线人数最大多少）：一天内，某一时刻的并发量 1000人同时在线 数据库（读多写少）： 累计2w的UV，平均每人往数据库中写入一条数据， 那么单日新增数据条数为2w条 2w条数据库-》占用空间大概2M 结论：以2-5w uv为例，单日数据库空间增长量从几M到几十M不等 """
9、如何正确看待索引
""" 开发人员最懂业务，任何一个软件都有其吸引用户的亮点 亮点背后对应的是热数据，这一点开发人员是最清楚的 开发人员最了解热数据对应的数据库字段有哪些，所以 应该在开发软件的过程中就提前为相应的字段加上索引，而不是 等软件上线之后，让DBA发现慢查询sql后再做处理，因为 1、一个软件慢会影响用户体验，但是慢的原因有很多，你不能立即确定是sql的问题 所以等到定位到sql的问题，可能已经过去了很久，问题已经被拖了很久了 2、因为大多数DBA都是管理型DBA而非开发型，所以即便是DBA从日志中看到了慢查询sql 也会因为其不懂业务而很难分析出慢的原因，最后这顶锅还是得扣到你开发的脑袋上 躲得过初一躲不过十五啊 """
