当前位置：首页 > 编程日记 > 正文

两个主键怎么设置tsql_索引该怎么创建？

编程日记 2024-08-28 19:10:00

1.2、索引 B+Tree 结构的特性：

①、B+Tree 只有叶子节点会存储真实的数据，非叶子节点只会存储索引字段值；

②、B+Tree的叶子节点之间使用双向链表链接，所以更加适合范围查询和排序；

2、索引的类型：

在平时创建的索引中，可以将索引大体分为两类：

①、聚簇索引(主键索引) ②、非聚簇索引(二级索引)

二级索引根据索引中的字段个数可以分为：

①、单字段索引 ②、联合索引 / 复合索引(多个字段组成的索引)

3、不同类型索引在磁盘中的B+Tree的存储结构：

3.1、聚簇索引：(主键索引)

聚簇索引：当表中创建了主键，默认就会生成主键索引；

聚簇索引的B+Tree索引结构中，非叶子节点中存储的是 ID主键值 ，存储在叶子节点中的真实数据是具体的 行记录 ；结构图如下：

3.2、单字段索引：(二级索引)

单字段索引：手动创建的索引，由 一个字段 组成的索引；

单字段索引的B+Tree索引结构中，非叶子节点中存储的是 索引字段值 ，存储在叶子节点中的数据部分是 主键值 。结构图如下：

3.3、联合索引：(二级索引)

联合索引：手动创建的索引，由 多个字段 组成的索引；

联合索引的B+Tree索引结构中，非叶子节点中存储的是 多个索引字段值 ，存储在叶子节点中的数据部分是 主键值 。结构图如下：

4、回表：

什么是回表？

回表是发生在 二级索引 中的；在使用二级索引查询数据时，如果 select 投影列 中拥有索引字段和主键之外的字段时，此时就需要回表；使用在二级索引树中查询到的主键值去主键索引树中查询具体的行记录，然后在具体行记录中取得最终的 select 投影列数据。

4.1、举例说明：

数据表：t_user

字段：id(主键)、name、age、address、sex

索引：index( name, age ) 测试的这个索引是联合索引，单字段索引也是一样的原理

Sql语句：select name, age, address, sex from t_user where name=’黎明’ and age=18

上面的sql语句执行时，就会发生回表；具体执行步骤如下：

①、执行时，首先会使用到联合索引 index( name, age )，在此索引树中查询时，最终在叶子节点中查询到数据，发现此时最终只能得到 name、age、id 三个字段的数据，发现 address、age 这两个字段的数据没有得到，所以只能再根据查询到主键值去主键索引树中查询；

②、拿着 id主键值去主键索引树中查询具体的行记录，然后在行记录中取出select投影列需要的字段的数据，最后返回。

4.2、扩展：

根据上面举的例子可以知道什么时候会发生回表，但是发生回表就代表着查询效率会比较低下的，因为需要走两边索引树(二级索引树 + 主键索引树)；所以一般情况下需要避免回表的发生；怎么避免发生呢？这又涉及到了 覆盖索引 这个知识点。下面就来介绍下 覆盖索引的知识 。

5、覆盖索引：

覆盖索引：大白话就是将select 的投影列字段全部放入到索引中；

5.1、举例说明：

数据表：t_user

字段：id(主键)、name、age、address、sex

索引：index( name, age )

Sql语句：select name, age, address, sex from t_user where name=’黎明’ and age=18

这个sql执行时，会发生回表；

发生回表的原因通过上面的解释应该也清楚了些，所以如果将索引 index( name, age ) 改为 * index( name, age, address, sex ) * 后，在执行上面的sql 就不会发生回表了，并且提高了查询效率，这就是覆盖索引。

但是，索引的创建及维护也是需要耗费代价的，并且这种代价也是随着索引中索引字段的个数增加而增加的，所以覆盖索引需要根据实际情况使用。

6、最左前缀原则：

说到索引的最左前缀原则，就必须说下 索引长度 这个概念了；

索引长度指的是索引字段列的前缀长度索引；

例如：name字段的长度设置为100，但是在以name设置索引时设置的索引长度为20；意思就是创建的索引中存储的索引key键值就是name字段值的前20长度的内容。

6.1、索引的最左前缀原则的两种情况：

①、索引字段的最左前缀原则；

②、联合索引的最左前缀原则；

6.2、索引字段的最左前缀原则：

根据索引长度应该就可以明白了索引字段的最左前缀原则了；但还是举例说明下：

经常说的导致索引失效的情况之一：模糊查询时将 % 放在了索引字段的前面；导致失效的最终原因就是不满足索引的最左前缀原则；

数据表：t_user

字段：id(主键)、name、age、address、sex

索引：index( name )

Sql语句：select name, age from t_user where name like ’ % 黎明 ’

上面这个 sql 语句执行时，并不会走 index(name) 索引查询，因为什么呢？不满足索引的最左前缀原则。

6.2、联合索引的最左前缀原则：

根据联合索引的B+Tree 存储结构可以知道其最左前缀原则是什么。

不太清楚也别着急，下面将会举例说明：

数据表：t_user

字段：id(主键)、name、age、address、sex

索引：index( name, age )

Sql语句：select id from t_user where name=’黎明’ and age=26

测试数据如图：

根据上面的测试数据及联合索引可以得到 B+Tree存储结构图：

上面sql执行时索引树的搜索步骤：

①、首先是加载索引树的根节点(磁盘块1)，然后匹配根节点中的第一个索引字段的值，查询name值是“黎明”的数据，得到指向其子节点地址的指针p1；

②、然后根据指针p1 找到子节点所在的磁盘块，然后将磁盘块加载到内存中，然后接着查询name值是“黎明”的数据，得到指向其子节点(叶子节点)地址的指针p3；

③、然后根据p3指针找到name值是“黎明”的所在叶子节点的磁盘块，然后将磁盘块中数据加载到内存中，然后再比较 age 为26的数据，最终找到name值是“黎明”并且age为26 的 id主键值，由于select 投影列只有 id 主键值，所以不需要回表，直接返回结果集了。

注意：在联合索引中，当索引中前一个索引字段值相同时，后面紧挨着的索引字段的值是有序的，所以索引天然适合进行排序，无需自己再进行排序了，提升了查询效率。

根据上面的索引树搜索过程，知道一开始是以索引中的第一个索引字段进行搜索的，最后在叶子节点中依次进行的索引字段值的匹配，这就是联合索引的最左前缀原则；

所以如果sql语句中的where 条件中没有使用到联合索引的第一个索引字段，则整个索引就失效了。

索引创建的准则：

1、考虑创建索引的表的读写情况：

对于 写操作比较多 的表，创建索引时，应该尽量保证联合索引尽可能的 窄(窄：索引字段个数尽可能少)， 如果索引字段比较多的话，写数据时的索引维护比较麻烦。

所以说，能创建单字段索引就不创建联合索引。

2、避免冗余索引：

合理创建索引，避免冗余索引；

联合索引index(a, b, c)相当于 index(a)、index(a，b)、index(a，b，c)这三个索引，所以如果有 index(a, b, c)索引了，那么 index(a)、index(a，b)这两个索引就不用存在了，属于重复索引了。

3、对多表关联查询使用的连接键字段创建索引：

针对多表关联查询时，需要将 连接键字段创建索引 ；例如：user 、role 两张表；

(一)、 select * from user a, role b where a.name = b.name 【普通连接查询】

需要在 role 表中创建 name 连接键的索引，user 不必创建 name 字段索引；

(二)、select * from user a left join role b on a.name = b.name 【左外连接查询】

需要在 role 表中创建 name 连接键的索引，user 不必创建 name 字段索引；

4、明确字段的区分度：

提前估算出表中每个字段存储的值的区分度，尽量在 区分度高的字段创建索引 ，区分度低的字段创建索引用处不大，并且还会多出索引维护的消耗。

区分度：字段值的不同的比例；例如性别字段只会有两种值，男或女，属于区分度低的字段。

5、保证索引的唯一性：

创建索引时，尽可能保证索引的唯一性，唯一性指的什么呢？

在查看SQL执行计划时，执行计划中 possible_keys 中展示的就是优化器采样估算后可能会使用到的索引，只有这里展示一个索引，并且与实际使用的索引一致时，这才是唯一性；如果此处展示了多个索引名称的话，说明没有保证索引的唯一性。

为什么要保证唯一性呢？

因为如果在执行一个SQL时，优化器是通过采样分析判断发现有两个索引都可以时，那么就会面临抉择，到底最终使用哪个索引呢；如果只有一个索引符合的话，优化器直接选择即可。所以如果可以使用的索引存在多个的话，那么优化器还需要通过 额外的运算 得到最终使用的索引的，那么这就会降低查询效率的。

6、创建联合索引时，索引字段怎么排序：

在联合索引中，怎么对索引字段进行排序？大白话就是在联合索引中，哪些索引字段放在最前面？

①、where 条件中常使用的字段放到前面。

②、区分度高的字段放在前面，因为这样可以减少扫描 B+tree 中的叶块。

《高性能MySQL》一书中提到的一个经验法则：将选择性最高( 区分度高 )的列放到索引最前列。

7、SQL中，有等值查询和范围查询时，联合索引怎么创建？

当然是将 等值查询的字段 放在联合索引的前面， 范围查询 的放到联合索引的后面；

为什么这么设计呢？

因为依据索引的查询规则，在联合索引中，首先都是根据第一个索引字段查询符合要求的数据，然后再从符合要求的数据中筛选第二个索引字段符合的数据；如果第一个索引字段值不符合，则直接结束查询了，不会再去进行范围查询了；但是如果将范围查询字段放在联合索引的第一位的话，那么很大可能会查询到符合范围的数据，然后再从数据中筛选等值查询的字段值，如果等值查询的值不存在，那么前面范围查询所做的工作就白白浪费了；并且降低了查询效率。

8、单表索引数量建议：

建议：单张表中索引的数量不超过5个；单个索引中的索引字段个数不超过5个；

导致索引失效的SQL：

明明已经创建好了索引，但是SQL执行时通过执行计划发现没有走索引查询，为什么？

1、查看SQL的执行计划：

创建好索引后，一般会查看下SQL的执行计划，看看SQL执行时是否走了索引查询；

Mysql 数据库查看执行计划参考：使用explain分析SQL执行计划

Oracle 数据库查看执行计划参考： Oracle通过执行计划查看查询语句是否使用索引

2、导致SQL执行时没走索引的原因：

2.1、优化器采样估算后不走索引：

可能数据库的优化器在进行采样估算时，发现走全表扫描更加合适；例如：表中数据量很小时，全表扫描和索引查询可能一样快，所以此时可能会直接进行全部扫描查询了。

2.2、SQL语句问题导致索引失效：(简单写几个常用到的)

不满足索引的最左前缀原则；例如：like模糊查询时，%放在前面；联合索引；
使用了反向查询，例如：not in 、!= 、<> 等，导致索引失效；
在索引字段上进行了函数运算或者算数运算；
隐式转换导致索引失效：隐式类型转换、隐式字符集转换；其实底层都是使用函数进行的隐式转换，所以导致的索引失效；
对索引字段做判 null 操作，会导致索引失效；

https://www.dkcj.cn/info/16280.html

数据库连接池，实现及分析

在我们日常对数据库操作时存在一个问题，要为每次数据操作请求建立一个数据库连接。而每次建立连接都需要花费很多开销，如加载驱动类、注册驱动、获取连接，这样如果在短时间内连接多次，就会耗费多余的时间(加载驱动注册驱动)*n次&…

编程日记2024/08/28 19:00:00

BZOJ 1176: [Balkan2007]Mokia( CDQ分治 + 树状数组 )

考虑cdq分治, 对于[l, r)递归[l, m), [m, r); 然后计算[l, m)的操作对[m, r)中询问的影响就可以了. 具体就是差分答案排序离散化然后树状数组维护.操作数为M的话时间复杂度大概是O(M(logM)^2)-----------------------------------------------------------------------#include…

编程日记2024/08/28 18:50:00

iOS开发之绝对布局和相对布局(屏幕适配)

在IOS的UI设计中也有绝对定位和相对定位，和我们的web前端的绝对定位和相对定位有所不同但又有相似之处。下面会结合两个小demo来学习一下我们IOS开发中UI的绝对定位和相对定位。在前面的博客中所用到的UI事例用的全是绝对定位，用我们Storyboard拖拽出来的…

编程日记2024/08/28 18:40:00

设计模式5-抽象工厂模式

package DesignPattern;public class AbstractFactory {public static class Dough{}public static class Sauce{}public static class Veggies{}public static class Cheese{}public static class Pepperoni{}public static class Clams{}//披萨public static abstract class …

编程日记2024/08/28 18:30:00

wp打印输出日志

System.Diagnostics.Debug.WriteLine(String); 转载于:https://www.cnblogs.com/songtzu/archive/2012/07/26/2609678.html

编程日记2024/08/28 18:20:00

Element-ui表格选中回显

先瞄一下，是不是你要的效果然后，废话不多说，直接上代码啦 1 <template>2 <div class>3 <div class"projectData">4 <el-table :data"tableData2" ref"multipleTable" :show…

编程日记2024/08/28 18:10:00

iOS开发者帐号申请指南

如果你是一个开发团队，在你打算掏腰包购买iOS开发者授权之前，最好先问一下你的同事，是否已经有人获得了开发许可，因为一个开发许可一年内最多可以授权给111个设备来开发测试。如果你没有授权许可可以借用，或者你打算最…

编程日记2024/08/28 18:00:00

Redis的KEYS命令引起宕机事件

摘要： 使用 Redis 的开发者必看，吸取教训啊！ 原文：Redis 的 KEYS 命令引起 RDS 数据库雪崩，RDS 发生两次宕机，造成几百万的资金损失作者：陈浩翔Fundebug经授权转载，版权归原作者所有…

编程日记2024/08/28 17:50:00

GridView的编辑,更新,取消,删除等功能演示

GridView的编辑,更新,取消,删除等功能演示这是一个GridView应用的视频，内容很透彻的讲解了GridView的很多实用的技巧。下载地址：http://download.cnblogs.com/insus/ASPDOTNET/GridViewEditUpdateCancelDelete.rar posted on 2015-12-15 09:20 代码养家…

编程日记2024/08/28 17:40:00

mac 使用homebrew 安装mysql

1. 安装homebrew ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)" brew update 2.安装mysql brew install mysql 3.设置 MySQL 用户以及数据存放地址，下载的mysql的mysql_install_db文件中的路径有错误需要重新设置一下文件路径&…

编程日记2024/08/28 17:30:00

触控(Touch) 、布局(Layout)

1 使用触控实现一个简易的画板 1.1 问题触控（Touch）是一个UITouch类型的对象，当用户触摸了屏幕上的视图时自动被创建，通常使用触控实现绘图、涂鸦、手写等功能。本案例使用触控实现一个简易的画板，可以在画板上勾画出…

编程日记2024/08/28 17:20:00

fail-fast和fail-safe的介绍和区别

2019独角兽企业重金招聘Python工程师标准>>> fail-fast和fail-safe 前言前段时间公司招的实习生在使用迭代器遍历的时候,对集合内容进行了修改,从而抛出ConcurrentModificationException. 然后给他讲解之余也整理了这一篇文章. fail-fast ( 快速失败 ) 在使用迭代器…

编程日记2024/08/28 17:10:00

hdu 4311 Meeting point-1

http://acm.hdu.edu.cn/showproblem.php?pid4311 思维呀亲你想到就可以做出来想不到就做不出了什么都不说了上代码不知道为什么在hdu 上 long long 和 int 相乘就让我错 #include<iostream> #include<cstdio> #include<algorithm> #include<c…

编程日记2024/08/28 17:00:01

Spring Boot 整合Pagehelper（为什么PageHelper分页不生效）

引入包https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter/1.2.10 <dependency><groupId>com…

编程日记2024/08/28 16:50:00

关于javascript的keycode

javascript event对象的具体功能是 event对象只在事件发生的过程中才有效（比如鼠标点击，键盘按下等）。event对象用以表示事件的状态，例如触发event对象的元素（event.srcElement）、鼠标的位置（ev…

编程日记2024/08/28 16:40:00

SQL-54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

题目描述查找排除当前最大、最小salary之后的员工的平均工资avg_salary。CREATE TABLE salaries ( emp_no int(11) NOT NULL,salary int(11) NOT NULL,from_date date NOT NULL,to_date date NOT NULL,PRIMARY KEY (emp_no,from_date));输出格式:avg_salary69462.5555555556SQ…

编程日记2024/08/28 16:30:00

JqGridView 1.0.0.0发布

前几个月，客户要求显示列表做到列锁定表头锁定列组合,但从Extjs到Jquery EasyUi，从Jquery Grid到Telerik等等组件，发现无一符合条件，要么只能用列锁定，要么只能用列组合，当两者结合就不行了。于是只好开始自…

编程日记2024/08/28 16:20:00

Struts2--ActionContext及CleanUP Filter

1. ActionContext ActionContext是被存放在当前线程中的，获取ActionContext也是从ThreadLocal中获取的。所以在执行拦截器、 action和result的过程中，由于他们都是在一个线程中按照顺序执行的，所以可以可以在任意时候在ThreadLocal中获取 Act…

编程日记2024/08/28 16:10:00

HTML5跳转页面并传值以及localStorage的用法

1、首先，你得在那个页面把数据存入localStorage中吧。这个是必须的！ localStorage.setItem("user",JSON.stringify(data.allUser)); 用localStorage的setItem方法，这个方法看名字都知道得差不多了吧。。。setItem把数据存入localSt…

编程日记2024/08/28 16:00:00

冒泡排序_python实现冒泡排序

冒泡排序是比较经典的面试题， 它重复地走访过要排序的元素列，依次比较两个相邻的元素，如果他们的顺序(如从大到小、首字母从A到Z)错误就把他们交换过来。走访元素的工作是重复地进行直到没有相邻元素需要交换，也就是说该元素列已经…

编程日记2024/08/28 15:50:00

30分钟内让你明白正则表达式是什么，并对它有一些基本的了解（二）

测试正则表达式如果你不觉得正则表达式很难读写的话，要么你是一个搞笑的天才，要么，你不是地球人。正则表达式的语法很令人头疼，即使对经常使用它的人来说也是如此。由于难于读写，容易出错，所以找一种工具对…

编程日记2024/08/28 15:40:00

(区间dp 或记忆化搜素 )Brackets -- POJ -- 2955

http://poj.org/problem?id2955 Description We give the following inductive definition of a “regular brackets” sequence: the empty sequence is a regular brackets sequence,if s is a regular brackets sequence, then (s) and [s] are regular brackets sequences…

编程日记2024/08/28 15:30:00

1.2、索引 B+Tree 结构的特性：

2、索引的类型：

3、不同类型索引在磁盘中的B+Tree的存储结构：

3.1、聚簇索引：(主键索引)

3.2、单字段索引：(二级索引)

3.3、联合索引：(二级索引)

4、回表：

4.1、举例说明：

4.2、扩展：

5、覆盖索引：

5.1、举例说明：

6、最左前缀原则：

6.1、索引的最左前缀原则的两种情况：

6.2、索引字段的最左前缀原则：

6.2、联合索引的最左前缀原则：

注意：在联合索引中，当索引中前一个索引字段值相同时，后面紧挨着的索引字段的值是有序的，所以索引天然适合进行排序，无需自己再进行排序了，提升了查询效率。

索引创建的准则：

1、考虑创建索引的表的读写情况：

2、避免冗余索引：

3、对多表关联查询使用的 连接键 字段创建索引：

4、明确字段的区分度：

5、保证索引的唯一性：

6、创建联合索引时，索引字段怎么排序：

7、SQL中，有等值查询和范围查询时，联合索引怎么创建？

8、单表索引数量建议：

导致索引失效的SQL：

1、查看SQL的执行计划：

2、导致SQL执行时没走索引的原因：

2.1、优化器采样估算后不走索引：

2.2、SQL语句问题导致索引失效：(简单写几个常用到的)

相关文章：

3、对多表关联查询使用的连接键字段创建索引：