mysql索引

B+树索引是最常见也是数据库中使用最为频繁的一种索引。文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。
在学习B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。
1.二叉查找树
二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
但是二叉树的查询效率太低低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。
2.平衡二叉树（AVLTree）
平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1
3.平衡多路查找树（B-Tree）
B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K。
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
红黑树（TreeMap）、时间复杂度是O(lgn)
平衡措施：变色和自旋
自平衡(不是绝对的平衡)的二叉查找树(BST)
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）新插入的节点默认为红色

为描述B树，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同
一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
   B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
   所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
   

4.B+Tree
在B+Tree中，**所有数据记录节点都是按照键值大小顺序

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以**对B+Tree进行两种查找运算：一种是对于主键的范围查找(依靠叶子节点的链指针)**和分页查找，另一种是从根节点开始，进行随机查找。为所有叶子结点增加了一个链指针

B+树的叶子节点都可以存哪些东西？
可能存储的是整行数据，也有可能是主键的值。B+树的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引（数据会根据索引中的顺序进行排列和组织的）。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

聚簇索引（主键索引）：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
非聚簇索引：叶子节点不存储数据、存储的是数据行地址(主键索引的ID)，也就是说根据索引查找到数据行的位置再去磁盘查找数据
优势：
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势：
1、维护索引很昂贵，特别是插入新行或者主键被更新导致要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键
3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键
值，会导致非叶子节点占用占用更多的物理空间
*为什么不推荐使用select ？
增加了不必要的网络开销。在查询巨量时，数据传输是十分耗时的。

辅助索引（在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找）与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。

为什么非主键索引结构叶子结点存储的是主键的值而不是数据？
两个方面：1.存储空间：如果存了数据会占用大量空间，所以选择时间换空间的思想，提高存储空间
2.数据一致性：如果把数据存储在不同的索引结构中，在做修改操作时成本会更高，
文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能，原因如下：
（一）B+ 树有更低的树高
平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2
（二）磁盘访问原理
MySQL中的数据一般是放在磁盘中的，读取数据的时候肯定会有访问磁盘的操作
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，（数据库索引使用节点大小恰好等于操作系统一页大小的B+树，使得结点读取效率更高，结点读取成本更低）使得一次 I/O 就能完全载入一个节点。如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。
（三）磁盘预读特性（一次预读4K）
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。
MySQL 索引
B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。（还有hash索引）
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
索引优化
1. 独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2. 多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3. 索引列的顺序
让选择性最强的索引列放在前面。
使用最频繁的列放到联合索引的左侧
索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

4. 前缀索引
对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
前缀长度的选取需要根据索引选择性来确定。
5. 覆盖索引
索引包含所有需要查询的字段的值。（既是主键又是需要查询的值）

具有以下优点：

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。
索引的优点
大大减少了服务器需要扫描的数据行数。

帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

创建索引原则：
最左前缀匹配原则，组合索引非常重要的原则，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的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

索引的使用条件
对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

对于中到大型的表，索引就非常有效；

但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。
查询性能优化
一.优化数据访问

1. 减少请求的数据量
   只返回必要的列：最好不要使用 SELECT * 语句。
   只返回必要的行：使用 LIMIT 语句来限制返回的数据。
   缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
   切分
   水平切分
   平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

主从复制
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

读写分离
主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

联合索引
通过第一个字段的值（部首）在第一级索引中找到对应的第二级索引位置（检字表页码），然后在第二级索引中根据第二个字段的值（笔画）找到符合条件的数据所在的位置（险字的真正页码）。
最左前缀匹配
联合索引中的字段，只有某个字段（笔画）左边的所有字段（部首）都被使用了，才能使用该字段上的索引。例如，有索引INDEX idx_i1(col_a, col_b)，如果查询条件为where col_b = 1，则无法使用索引idx_i1。
完整地表述一下最左前缀匹配原则的含义：对于一个联合索引，如果有一个SQL查询语句需要执行，则只有从索引最左边的第一个字段开始到SQL语句查询条件中不包含的字段（不含）或范围条件字段（含）为止的部分才会使用索引进行加速。

如何设计索引：
1.整理查询条件
整理需要用到的查询条件，也就是我们会在where子句、join连接条件中使用的字段。一般来说会整理程序中除了insert语句之外的所有SQL语句，按不同的表分别整理出每张表上的查询条件。也可以根据对业务的理解添加一些暂时还没有使用到的查询条件。
对索引的设计一般会逐表进行，所以按数据表收集查询条件可以方便后面步骤的执行。
2.分析字段的可选择性
字段的可选择性指的就是字段的值的区分度，例如一张表中保存了用户的手机号、性别、姓名、年龄这几个字段，可选择性从高到低排列就是：手机号 > 年龄 > 性别。
一般把可选择性高的字段放到前面，可选择性低的字段放在后面
3.考虑是否要加入排序字段
order by子句中的字段也可以利用索引来进行排序，这样就可以避免数据库进行排序操作的开销了。
4.合并查询条件
一般我们会根据最左匹配原则来合并查询条件，尽可能让不同的查询条件使用同一个索引。例如有两个查询条件where a = 1 and b = 1和where b = 1，那么我们就可以创建一个索引idx_eg(b, a)来同时服务两个查询条件。

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因
(1.1)、数据库在刷新脏页，例如 redo log 写满了需要从内存同步到磁盘。只能暂停其他操作，全身心来把数据同步到磁盘中去的
1.2内存不够用了释放内存
(2)、执行的时候，遇到锁，如表锁、行锁。 show processlist这个命令来查看当前的状态

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

2、这条 SQL 语句一直执行的很慢，则有如下原因。
(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、数据库选错了索引。

一条SQL语句在MySQL中如何执行
MySQL 基础架构

简单来说 MySQL 主要分为 Server 层和存储引擎层：
•Server 层：主要包括连接器、查询缓存、分析器(词法分析，语法分析)、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
•存储引擎： 主要负责数据的存储和读取

SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
•对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare（为什么 redo log 要引入 prepare 预提交状态，保证数据一致性）—》binlog（归档日志）—》redo log commit（重做日志）

primary key 、unique key 与index区别

key 是数据库的物理结构，它包含两层意义和作用，
一是约束（偏重于约束和规范数据库的结构完整性），
二是索引（辅助查询用的）。