pg库指定字段添加索引数据库字段添加索引

转载

mob64ca14193248 2024-06-13 21:09:44

索引类型（3个）

1 联合索引（复合索引）

（1）联合索引特征，相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。
（2）原理：比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，类似查字典
（3）联合索引的特点就是：
第一个字段一定是有序的
当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的

2.最左前缀原理

（1）我们再来详细介绍一下联合索引的查询。还是上面例子，我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：
以下的查询方式都可以用到索引

select * from table where a=1；
    select * from table where a=1 and b=2；
    select * from table where a=1 and b=2 and c=3；

上面三个查询按照（a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。
如果查询语句是：

select * from table where a=1 and c=3； 那么只会用到索引a。

如果查询语句是：

select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是用户到索引的。

（2）如果用到了最左前缀，但是顺序颠倒会用到索引吗？
比如：

select * from table where b=2 and a=1；
    select * from table where b=2 and a=1 and c=3；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

3.前缀索引

（1）前缀索引：用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
（2）使用前缀索引场景：
字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。
（3）前缀索引优缺点
优点：MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。
坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

4.索引优化策略（11个）

最左前缀匹配原则。
主键外键一定要建索引
对 where,on,group by,order by 中出现的列使用索引
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time= unix_timestamp(’2014-05-29’);
为较长的字符串使用前缀索引
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
对于like查询，”%”不要放在前面。

SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 
        SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引

11.查询where条件数据类型不匹配也无法使用索引
字符串与数字比较不使用索引;

CREATE TABLEa(achar(10)); 
        EXPLAIN SELECT * FROMaWHEREa="1" – 走索引 
        EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引

正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
12.谓词下推
参考数据库索引底层原理及优化

5.不使用索引的情况(6个)

全表扫描比使用索引快的时候。MySQL内部优化器会对SQL语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。
索引列进行函数计算的时候。
模糊匹配，%在前面的时候。
查询where条件数据类型不匹配也无法使用索引 。
如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字。
正则表达式不使用索引。

要尽量避免这些不走索引的sql：
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算 
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算，原理与上面相同 
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 
-- 正则表达式不使用索引，这应该很好理解，所以这就是为什么在SQL中很难看到regexp关键字的原因 
-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引，同样也是使用了函数运算 
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字 
-- MySQL内部优化器会对SQL语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引

6.SQL怎么优化join ？

作者：meta_pher
链接：https://www.nowcoder.com/discuss/384789?type=post&order=time&pos=&page=1&subType=2
来源：牛客网

我大概说了left join和inner join 以及用primary key join
她问我聊不了解hivesql的map join 我并没有听说过
之后是一道中等难度的sql题比较顺利
再之后就闲聊了让我对比了下爱奇艺和腾讯视频

7 explain

添加链接描述

8 密集索引和稀疏索引的区别

区别
密集索引文件中的每个搜索码值都对应一个索引值
稀疏索引文件只为索引码的某些值建立索引项
密集索引的定义：
叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引
稀疏索引：
叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键
mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引
innodb存储引擎：有且只有一个密集索引。密集索引的选取规则如下：
若主键被定义，则主键作为密集索引
如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引
若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
非主键索引存储相关键位和其对应的主键值，包含两次查找

9.exist

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。
优化建议可参考：https://database.51cto.com/art/201901/589633.htm###

10.MySql执行顺序及执行计划

10.1 mySql的执行顺序

mysql执行sql的顺序从 From 开始，以下是执行的顺序流程
1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1
2、JOIN table2 所以先是确定表，再确定关联条件
3、ON table1.column = table2.columu 确定表的绑定条件由Temp1产生中间表Temp2
4、WHERE 对中间表Temp2产生的结果进行过滤产生中间表Temp3
5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4 (开始使用select中的别名，后面的语句中都可以使用)
6、HAVING 对分组后的记录进行聚合产生中间表Temp5
7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6
8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7
9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8
10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

10.2 mySql的执行计划

1、什么是执行计划

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

2、执行计划的内容

pg库指定字段添加索引数据库字段添加索引_主键

①.id sql执行计划的顺序或子查询表的执行顺序

id一样，按照顺序执行；id越大，执行的优先级就越高（如子查询）

②.select_type 表示查询中每个select子句的类型

pg库指定字段添加索引数据库字段添加索引_pg库指定字段添加索引_02

a**.SIMPLE**：查询中不包含子查询或者UNION

b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）

e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED

f.从UNION表获取结果的SELECT被标记为**：UNION RESULT**

③.type

MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：

pg库指定字段添加索引数据库字段添加索引_字段_03

由左至右，由最差到最好

ALL：全表扫描

index：index类型只遍历索引树
索引的存在形式是文件，按存储结构划分）：FULLTEXT，HASH，BTREE，RTREE。
对应存储引擎支持如下：
转载mySql索引：http://blog.csdn.net/jesseyoung/article/details/38037543
range：索引范围扫描
对索引字段进行范围查询，使用in则是使用rang范围查询；使用">" ,"<" 或者 “between” 都是可以使用索引的，但是要控制查询的时间范围，一般查询数据不要超过数据总数的 15%
ref：非唯一性索引
类似 select count(1) from age = ‘20’;
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
④.key
表示在执行语句用到的索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中

覆盖索引：查询数据只需要通过索引就可以查询出，如55万条数据，使用索引，立刻可以查询出 2000条数据，同时Extra字段是Using index

⑤.Extra

Using index : 使用覆盖索引的时候就会出现

using index condition：查找使用了索引，但是需要回表查询数据

Using where ：在查找使用索引的情况下，需要回表去查询所需的数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

Using temporary：需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：无法利用索引完成的排序操作称为“文件排序”；

很多场景都是索引是一个字段，order by 排序的字段与索引字段不一致，导致的Using fileSort;

此时可以给排序字段和where条件字段，添加为组合索引，同时保证索引查询的数据不超过总量的15%，避免fileSort

注：回表的含义是，先根据索引查询数据，然后在根据确定的数据id和查询条件再去查询具体的数据的过程

转载：添加链接描述

11 索引类型对比（按存储结构划分）

1 B-TREE索引

B-Tree索引加速了数据访问。从B-Tree根开始，借助中间节点页的上界和下界值，可以快速搜寻到叶子页层，最终找到含有需要找的值的叶子页(或者确定无法找到需要的数据)，找到对应的叶子页后可以通过相应的指针直接找到数据表中对应的数据行。这样存储引擎不会扫描整个表得到需要的数据。同时B-Tree索引通常意味着索引中数据保存时有序的，可以利用B-Tree索引来加速排序。

6.1.2 HASH索引

哈希索引建立在哈希表的基础上，它只对使用了索引的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值(可能对具有相同索引值的不同行计算出的哈希值不同)。索引中包含哈希码和对应指向数据行的指针。

hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree。hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。

6.1.3 FULLTEXT索引

FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的。

6.1.4 R-TREE索引

RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

Mysql目前主要有以下几种索引类型（按存储结构划分）：FULLTEXT，HASH，BTREE，RTREE。

对应存储引擎支持如下：

pg库指定字段添加索引数据库字段添加索引_主键_04

12 索引的优缺点

索引的优点
1）大大加快数据的检索速度，这也是创建索引的最主要的原因。
2）创建唯一性索引，保证数据库表中每一行数据的唯一性。
3）加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5）通过使用索引，可以在查询的过程中使用优化隐藏器。
索引的缺点
1） 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
3）建立索引会占用磁盘空间的索引文件，尤其大表上创建了多种组合索引，索引文件的会膨胀很快。

13.覆盖索引VS聚簇索引

1）聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
因为聚簇索引将相邻键值的数据行存在一起，所以能很快的查找到相关数据。
2）覆盖索引：一个索引包含所有要查询的字段的值。–不需要回表查询。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引都不存储索引列的值，所以覆盖索引必

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：ElementPlus删除 elementui表格编辑删除

下一篇：ingress 下载静态文件静态文件放到cdn

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯