做优化网站是什么意思,广州百度网站推广,wordpress ip 地址,做网站的动态图片深入浅出数据库索引原理参见:https://www.cnblogs.com/aspwebchh/p/6652855.html1.为什么给表加上主键#xff1f;1.平时创建表的时候#xff0c;都会给表加上主键。如果没有主键的表#xff0c;数据会一行行的排列在磁盘上#xff0c;查找一个数据需要一条条的进行对比。而…深入浅出数据库索引原理参见:https://www.cnblogs.com/aspwebchh/p/6652855.html1.为什么给表加上主键1.平时创建表的时候都会给表加上主键。如果没有主键的表数据会一行行的排列在磁盘上查找一个数据需要一条条的进行对比。而加上主键的表会变成树形B树/B树这样整个表就变成一个索引即聚合索引假如我们执行一个SQL语句select * from table where id 1256;首先根据索引定位到1256这个值所在的叶结点然后再通过叶结点取到id等于1256的数据行。2.为什么索引后会使查询变快假如一张表有一亿条数据 需要查找其中某一条数据按照常规逻辑 一条一条的去匹配的话 最坏的情况下需要匹配一亿次才能得到结果用大O标记法就是O(n)最坏时间复杂度这是无法接受的而且这一亿条数据显然不能一次性读入内存供程序使用 因此 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销以现在磁盘的IO能力和CPU的运算能力 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构一棵非常茂盛和节点非常多的树假设这棵树有10层那么只需要10次IO开销就能查找到所需要的数据 速度以指数级别提升用大O标记法就是O(log n)n是记录总树底数是树的分叉数结果就是树的层次数。3.为什么加索引后会使写入、修改、删除变慢事物都是有两面的 索引能让数据库查询数据的速度上升 而使写入数据的速度下降原因很简单的 因为平衡树这个结构必须一直维持在一个正确的状态 增删改数据都会改变平衡树各节点中的索引数据内容破坏树结构 因此在每次数据改变时 DBMS必须去重新梳理树索引的结构以确保它的正确这会带来不小的性能开销也就是为什么索引会给查询以外的操作带来副作用的原因。4.什么情况下要同时在两个字段上建索引4.1 非聚合索引非聚集索引和聚集索引一样 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段 假如给user表的name字段加上索引 那么索引就是由name字段中的值构成在数据改变时 DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 那么就会出现多个独立的索引结构每个索引非聚集索引互相之间不存在关联。 如下图 每次给字段建一个新索引 字段中的数据就会被复制一份出来 用于生成索引。 因此 给表添加索引会增加表的体积 占用磁盘存储空间。非聚集索引和聚集索引的区别在于 通过聚集索引可以查到需要查找的数据 而通过非聚集索引可以查到记录对应的主键值 再使用主键的值通过聚集索引查找到需要的数据如下图 4.2 覆盖索引非聚合索引都会利用主键通过聚合索引来定位到数据聚合索引主键是通往真实数据所在的唯一路径但是有一种例外是可以不使用聚合索引就能查询到所需要的数据这种办法被称为覆盖索引。先看下面这个SQL语句//建立索引create index index_birthday on user_info(birthday);//查询生日在1991年11月1日出生用户的用户名select user_name from user_info where birthday 1991-11-1这句SQL语句的执行过程如下首先通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值然后通过得到的主键ID值执行聚集索引查找找到主键ID值对就的真实数据数据行存储的位置最后 从得到的真实数据中取得user_name字段的值返回 也就是取得最终的结果我们把birthday字段上的索引改成双字段的覆盖索引create index index_birthday_and_user_name on user_info(birthday, user_name);这句SQL语句的执行过程就会变为通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容然而 叶节点中除了有user_name表主键ID的值以外 user_name字段的值也在里面 因此不需要通过主键ID值的查找数据行的真实所在 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式 可以省略不使用覆盖索引查找的后面两个步骤 大大的提高了查询性能如下图一、为什么要创建索引呢优点这是因为创建索引可以大大提高系统的性能。第一 通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。第二 可以大大加快数据的检索速度这也是创建索引的最主要的原因。第三 可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。第四 在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。第五 通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。二、建立方向索引的不利因素缺点也许会有人要问增加索引有如此多的优点为什么不对表中的每一个列创建一个索引呢这种想法固然有其合理性然而也有其片面性。虽然索引有许多优点但是为表中的每一个列都增加索引是非常不明智的。这是因为增加索引也有许多不利的一个方面。第一 创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加。第二 索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大。第三 当对表中的数据进行增加、删除和修改的时候索引也要动态的维护这样就降低了数据的维护速度。 三、创建方向索引的准则索引是建立在数据库表中的某些列的上面。因此在创建索引的时候应该仔细考虑在哪些列上可以创建索引在哪些列上不能创建索引。一般来说应该在这些列上创建索引。第一 在经常需要搜索的列上可以加快搜索的速度第二 在作为主键的列上强制该列的唯一性和组织表中数据的排列结构第三 在经常用在连接的列上这些列主要是一些外键可以加快连接的速度第四 在经常需要根据范围进行搜索的列上创建索引因为索引已经排序其指定的范围是连续的第五 在经常需要排序的列上创建索引因为索引已经排序这样查询可以利用索引的排序加快排序查询时间第六 在经常使用在WHERE子句中的列上面创建索引加快条件的判断速度。 同样对于有些列不应该创建索引。一般来说不应该创建索引的的这些列具有下列特点第一 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为既然这些列很少使用到因此有索引或者无索引并不能提高查询速度。相反由于增加了索引反而降低了系统的维护速度和增大了空间需求。第二 对于那些只有很少数据值的列也不应该增加索引。这是因为由于这些列的取值很少例如人事表的性别列在查询的结果中结果集的数据行占了表中数据行的很大比例即需要在表中搜索的数据行的比例很大。增加索引并不能明显加快检索速度。第三 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为这些列的数据量要么相当大要么取值很少。第 四 当修改性能远远大于检索性能时不应该创建索引。这是因为修改性能和检索性能是互相矛盾的。当增加索引时会提高检索性能但是会降低修改性能。当减少 索引时会提高修改性能降低检索性能。因此当修改性能远远大于检索性能时不应该创建索引。
