mysql 索引总结_mysql索引总结

最新推荐文章于 2024-02-08 17:36:10 发布

一叶羽卿

最新推荐文章于 2024-02-08 17:36:10 发布

阅读量76

点赞数

文章标签： mysql 索引总结

本文链接：https://blog.csdn.net/weixin_30696571/article/details/113937620

版权

http://www.cnblogs.com/frankielf0921/p/5930743.html

mysql中每一个表都有一个聚簇索引clusted index，该所索引是默认创建的，除此之外的表上的每一个非聚簇索引都是二级索引，又叫辅助索引(secondary indexes)。

以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引，如果您的表上定义有主键，该主键索引是聚集索引，如果你不定义为你的表的主键是，mysql取第一个唯一索引unique而且只含非空列(NOT NULL)作为主键，InnoDB使用它作为聚集索引，如果没有这样的列，InnoDB就自己产生这样的ID值，它有留个字节，而且是隐藏的，使其作为聚簇索引。

根据数据的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引

唯一所以是不允许其中任何两行具有相同的索引值的索引

主键索引数据库表经常有一列或列组合、其值唯一标识表中的每一行，该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。

聚集索引在聚集索引中，表中行的物理顺序与键值的逻辑顺序相同，一个表只能包含一个聚集索引

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一甚至6000倍，因此为了提升效率，要尽量减少磁盘I/O，为了达到这个目的，磁盘往往不是严格按需读取，而是每次读都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据存放入内存。这样做的理论依据是计算机科学中最著名的局部性原理，即当一个数据被用到时，其附近的数据也通常会被使用。程序运行期间所需的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需要很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率

预读的长度一般为页page的整数倍。页是计算机管理存储器的逻辑快，硬件以及操作系统往往将主存很磁盘存储区分割为连续大小相等的块，每个存储块称为一页(在许多操作系统中，页的大小通常为4K)，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，因此系统会向磁盘发送出读盘信号，磁盘会找到数据的起始位置并向后连续的一页或者几页内存中，然后异常返回，程序继续运行。

B-/+ Tree 索引的性能分析

上文说过，一般使用磁盘I/O次数评价索引结构的优劣，先从B-TREE分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙的利用了磁盘与读写原理，将一个节点的大小设置为等于一个页，这样每个节点只需要一次I/O就可以完全载入，为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存)，渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小(通常不超过3)。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

3.数据库结构优化

1)范式优化：比如消除冗余(节省空间。。) 2)反范式优化：比如适当加冗余等(减少join) 3)拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4)拆分其实又分垂直拆分和水平拆分：案例：简单购物系统暂设涉及如下表： 1.产品表(数据量10w，稳定) 2.订单表(数据量200w，且有增长趋势) 3.用户表 (数据量100w，且有增长趋势) 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万垂直拆分：解决问题：表与表之间的io竞争不解决问题：单表中数据量增长出现的压力方案：把产品表和用户表放到一个server上订单表单独放到一个server上水平拆分：解决问题：单表中数据量增长出现的压力不解决问题：表与表之间的io争夺

方案：用户表通过性别拆分为男用户表和女用户表订单表通过已完成和完成中拆分为已完成订单和未完成订单产品表未完成订单放一个server上已完成订单表盒男用户表放一个server上女用户表放一个server上(女的爱购物哈哈)

b+索引原理

在B+索引树中，非叶子节点由索引元素和指向子节点的指针组成，他们的作用就是找到叶子节点，因为只有叶子节点中有最终要找到数据信息。从图中可以看出节点中指针的数量比索引元素数量多一个，在叶子节点中，因为没有子节点，多出的那个指针指向下一个叶子节点，这样把所有的叶子节点串联起来，这对于范围搜索很有用。在实际应用中一个节点的大小是固定的通常等于磁盘一个页的大小，这样存取一个节点只要一次磁盘io，一般节点可存上百个元素，所以索引几百万数据b+树高不会超过3。

搜索类似于二叉查找树，从根节点开始自顶向下遍历，直到叶子节点，在节点内部典型的使用二分查找来确定位置。

innodb存储的数据本身就是主键b+树索引，因为索引内存存储这真实的数据。

innodb支持事务，外键，mvcc，行锁这些更先进的引擎技术。

主键primary key 必须唯一，不同的是不能有null。而且一个表只能有一个主键，有很多人认为主键是唯一索引的一种，其实是不准确的。主键也可以是组合索引，只要组合的每条结果是唯一的，这在某些场景非常实用，比如一个多对多关系中的枢纽表就非常适合使用复合主键。下图就是一个典型的用户权限功能的实现，用户和角色、角色和权限都是多对多的关系，主要枢纽表来记录他们之间的对应关系，而这些关系都是唯一的，所以这种枢纽表用复合主键非常合适。

选择区分度高的列作为索引

区分度：count(distinct col)／count(*)，区分度是一个介于0和1的小数，越接近1区分度越高，

mysql创建索引的基本原则：

http://www.toutiao.com/a6427416564824064257/?tt_from=weixin&utm_campaign=client_share&app=news_article&utm_source=weixin&iid=10861926112&utm_medium=toutiao_ios&wxshare_count=1

1、不要在低基数列创建索引。浪费索引存储空间，并且不会提供查询的效率。

2、尽量不要在经常被修改的子段上创建索引，会增加插入的成本，以及提高死锁发生的概率。

3、删除冗余索引，没有用到的索引必须全部删除，避免不必要的空间浪费。本示例中url索引是无用的。

4、不要创建太多的索引，因为插入数据时，索引也需要插入。索引太多会导致插入性能下降

5、不要在非null列创建索引，如果值为null是，建议替换成1或者-1等常量

6、如果查询的是多条件，不要为每个条件创建索引，而是创建复合索引，因为mysql只用使用1个索引

7、创建复合索引，注意是左匹配原则，将两考虑重用性。比如创建复合索引index(a，b，c)，相当于同时创建index(a)，index(a、b)，index(a，b，c)。

8、创建复合索引，需要注意把分度最大的放在最前面

9、主键最好使用自增型，保证数据连续性(mysql innodb主键默认采用b+tree，索引和数据放在同一个tree中，)不要使用uuid、hash、md5等

10、少使用外键，会导致两张表数据变更时相互影响。尽量通过业务实现。

11、不要使用前匹配的like查询，会导致索引失效，可以使用后匹配like“xxxx%”

12、在字符串上创建索引、尽量使用前缀索引。前缀技术根据具体业务，在匹配度和存储量(索引的存储量)之间做一个平衡。