MySQL索引

一、什么是索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

二、索引的优缺点

（1）优点
1.创建唯一性索引，保证数据库表中每一行数据的唯一性
2.大大加快数据的检索速度，这也是创建索引的最主要的原因
　3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
（2）缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
　2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
　3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

三、索引原理

（1）本质是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
（2） 磁盘IO与预读：当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO。
（3）InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K。

在MySQL中可通过如下命令查看页的大小

mysql> show variables like 'innodb_page_size';

四、索引的数据结构

1、平衡多路查找树（B-Tree）
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

（1）根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
（2）比较关键字29在区间（17,35），找到磁盘块1的指针P2。
（3）根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
（4）比较关键字29在区间（26,30），找到磁盘块3的指针P2。
（5）根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
（6）在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

2.B+Tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：
（1）非叶子节点只存储键值信息。
（2）所有叶子节点之间都有一个链指针。
（3）数据记录都存放在叶子节点中。

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

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2–4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

五、索引的分类

1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.组合索引
-primary key(id,name):组合主键索引
-unique(id,name):组合唯一索引
-index(id,name):组合普通索引
4.全文索引fulltext :对文本的内容进行分词，进行搜索
5.空间索引spatial :了解就好，几乎不用

六、索引相关技术名词

（1）回表
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

（2）索引覆盖
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
应用场景：可以避免回表
1.全表count查询优化
2.列查询回表优化
3.分页查询

（3）最左匹配
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合，所以在建立联合索引的时候查询最频繁的条件要放在左边
假设我们创建（a，b，c）这样的一个组合索引，那么相当于对a列进行排序，也就是我们创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引

语句	索引是否发挥作用
where a = 3	是，只使用了a
where a = 3 and b=5	是，使用了a,b
where a = 3 and b=5 and c=4	是，使用了a,b,c
where b = 3 or c=4	否
where a = 3 and c=4	是，只使用了a
where a = 3 and b>10 and c=4	是，使用了a,b
where a = 3 and b like “%xx%” and c=4	是，只使用了a

（4）索引下推

select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
总结

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

（5）聚簇索引和非聚簇索引
聚簇索引：不是单独的索引类型，而是一种数据存储方式，将索引与数据存储在同一个叶子节点中。
非聚簇索引：数据文件和非数据文件分开存放。

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。
一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。
聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。

聚簇索引优点：
1、把相关数据保存在一起，因为mysql数据库读取数据是按照页读取的，当读取某一个用户数据时，相邻的数据也会加载到内存中。根据用户读取一个id的数据时，相邻数据被读取的可能性会非常高，这种按页加载就减少了IO操作
2、数据访问更快
3、使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

聚簇索引缺点：
1、聚簇索引最大限度提高了IO密集型应用性能，但是当数据都在内存中时，聚簇索引优势就没有了
2、插入速度严重依赖插入顺序。如果不是按照顺序插入，可能导致数据的移动设置页分裂，从而影响性能
3、更新聚簇索引的代价非常高，因为会强制INNODB将每个给跟新的行移动到新的位置上去
4、聚簇索引插入新列或者更新聚簇索引的时候可能导致页分裂
5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续
6、二级索引需要的存储空间更大，因为二级索引中包含了主键列，同时二级索引需要两次查询才能查询到行数据
（6）覆盖索引

七、索引的优化

（1）尽量使用主键查询而不是其他索引查询，因为其他索引查询会触发回表查询。
（2）使用前缀索引
（3）使用索引扫描来排序
（4）union all,in,or 都能使用索引，推荐使用in
(5) 范围列可以用到索引。条件>,>=,<,<=,between，但是范围列后面的列无法用到索引，索引最多用于一个范围列。
（6）强制类型转换会全表扫描
（7）更新十分频繁，数据区分度不高的字段不宜建立索引。更新会变更B+Tree,会大大降低数据库性能。类似性别，数据区分度不大，不能有效过滤数据。区分度在80%以上的可以建立。可以用count(distinct(列名))/count(*)计算。
（8）创建索引列，不允许为null，可能会得到不符合预期的结果。
（9）当需要表连接时，最好不要超过三张表，因为需要join字段，数据类型必须一致。
（10）能使用limit尽量使用limit。

八、索引失效情况

1.有or，列必须全有索引，才生效；组合索引，or不生效。
2.复合索引未用左列字段;
3."%A","%A%"会全表扫描，不走索引，"A%"走;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;
8.order by
当order by 字段出现在where条件中时，才会利用索引而无需排序操作。其他情况，order by不会出现排序操作。
这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。
在order by子句中能否使用索引需要考虑两个问题就可以了。第一个就是select后面的字段列表是否可以直接从索引中获得全部字段，另外一个就是where条件是否可以使用索引来限定一个范围，有了这个查询范围即便是再次回表代价也是可以接受的。

九、 on和where的区别：

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’) 

（1）on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
（2）where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
（3）inner join on和where一样。
（4）left join,right join,full join。不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。
（5）单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。
（6） 在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。

十、数据库分库分表概念

原文链接：
https://blog.csdn.net/weixin_44062339/article/details/100491744

为什么大字段IO效率低：
第一是由于数据量本身大，需要更长的读取时间；
第二是跨页，页是数据库存储单位，很多查找及定位操作都是以页为单位，单页内的数据行越多数据库整体性能越好，而大字段占用空间大，单页内存储行数少，因此IO效率较低。
第三，数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

1、垂直分表
概念：将一个表按照字段分成多表，每个表存储其中一部分字段。
它带来的提升是：
（1）为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响
（2）充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。
2、垂直分库
垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
它带来的提升是：
（1）解决业务层面的耦合，业务清晰
（2）能对不同业务的数据进行分级管理、维护、监控、扩展等
（3）高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈。

垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。
3、水平分表
概念：是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
它带来的提升是：
（1）优化单一表数据量过大而产生的性能问题。
（2）避免IO争抢并减少锁表的几率。

库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。

4、水平分库
水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构

它带来的提升是：
（1）解决了单库大数据，高并发的性能瓶颈。
（2）提高了系统的稳定性及可用性。
（3）稳定性体现在IO冲突减少，锁定减少，可用性指某个库出问题，部分可用。

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平分库了，经过水平切分的优化，往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。

十一、表分区

一、表分区策略
1.识别大表
采用ANALYZE TABLE语句进行分析，然后查询数据字典获得相应的数据量。
2.大表如何分区
可根据月份，季度以及年份等进行分区；
3.分区的表空间规划
要对每个表空间的大小进行估计

二.创建表分区
a.创建范围分区的关键字是’RANGE’
1.范围分区

create table graderecord  
(  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by range(grade)  
(  
  partition bujige values less than(60), --不及格  
  partition jige values less than(85), --及格  
  partition youxiu values less than(maxvalue) --优秀  
); 

查询语句
select * from graderecord;  
select * from graderecord partition(bujige);  
select * from graderecord partition(jige);  
select * from graderecord partition(youxiu);  

2.创建散列分区
散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

create table graderecord  
(  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by hash(sno)  
(  
  partition p1,  
  partition p2,  
  partition p3  
);  

3.组合分区：
4.interval 分区

四.分区技术简介
优点：
1.减少维护工作量
2.增强数据的可用性
3.均衡I/O,提升性能
4.提高查询速度
5.分区对用户保持透明，用户感觉不到分区的存在。

参考内容如下：
来源：简书
原文链接：https://www.jianshu.com/p/d0d3de6832b9
来源：CSDN
原文链接：https://blog.csdn.net/u013235478/article/details/50625677