精华内容
下载资源
问答
  • mysql索引要注意了
    2021-02-07 02:46:09

    昨天看到现场有一张表索引建的不对,调整了一下,并发性提高了很多。

    CREATE TABLE `test` (

    `version_id` varchar(40) COLLATE utf8_bin NOT NULL,

    `type` varchar(20) COLLATE utf8_bin DEFAULT NULL,

    `version_num` int(11) NOT NULL,

    `tenant_id` varchar(40) COLLATE utf8_bin NOT NULL,

    PRIMARY KEY (`version_id`),

    KEY `ind_pvr_tenant_id` (`tenant_id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    select * from test;

    mysql> select * from test;

    +-------------------+-----------------+-------------+-------------------+

    | version_id        | type            | version_num | tenant_id         |

    +-------------------+-----------------+-------------+-------------------+

    | 000145d60f732601  | contact         |       11667 | 0001456d8d47ee01  |

    | 000147f2c61da46ef | contact         |           2 | 000147f2c422716ea |

    | 000149523343d105  | contact         |       11667 | 0001456d8d47ee01  |

    | 0001495242eba85b  | group           |        6238 | 0001456d8d47ee01  |

    | 00014a9bf5157c01  | wb_msg          |           1 | 0001456d8d47ee01  |

    | 00014a9bf515a602  | wb_msg_type     |           7 | 0001456d8d47ee01  |

    | 00014d539a816e26  | home_panel      |           3 | 0001456d8d47ee01  |

    | 00014d539a817b27  | home_item       |          10 | 0001456d8d47ee01  |

    | 00014f45536b2625c | collect_message |           2 | 0001456d8d47ee01  |

    +-------------------+-----------------+-------------+-------------------+

    session1:

    mysql> begin;

    Query OK, 0 rows affected (0.00 sec)

    mysql> update test set version_num = 11668 where type='contact' and tenant_id='0001456d8d47ee01';

    Query OK, 2 rows affected (0.00 sec)

    Rows matched: 2  Changed: 2  Warnings: 0

    session2:

    mysql> begin;

    Query OK, 0 rows affected (0.00 sec)

    mysql> update test set version_num = 2 where type='wb_msg' and tenant_id='0001456d8d47ee01';

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

    --mysql加锁是按照索引来加的,如果只按tenant_id加索引,tenant_id=0001456d8d47ee01则锁了很多记录,影响并发。

    DROP INDEX ind_pvr_tenant_id;

    CREATE INDEX ind_pvr_tenant_id_TYPE ON test(tenant_id,TYPE);

    session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set version_num = 11668 where type='contact' and tenant_id='0001456d8d47ee01'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2  Changed: 2  Warnings: 0 session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set version_num = 2 where type='wb_msg' and tenant_id='0001456d8d47ee01'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

    更多相关内容
  • 主键索引主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。在MySQL中,InnoDB数据表的主键...

    主键索引

    主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

    在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则:

    采用一个没有业务用途的自增属性列作为主键;

    主键字段值总是不更新,只有新增或者删除两种操作;

    不选择会动态更新的类型,比如当前时间戳等。

    这么做的好处有几点:

    新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了;可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”;

    业务数据有变更时,不修改主键值,物理存储位置发生变化的概率降低了,innodb page中产生碎片的概率也降低了。

    MyISAM表因为是堆组织表,主键类型设计方面就可以这么讲究了。

    辅助索引,就是我们常规所指的索引,原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引,非唯一索引。

    唯一索引其实应该叫做唯一性约束,它的作用是避免一列或多列值存在重复,是一种约束性索引。

    在MyISAM引擎中,唯一索引除了key值允许存在NULL外,其余的和主键索引没有本质性区别。也就是说,在MyISAM引擎中,不允许存在NULL值的唯一索引,本质上和主键索引是一回事。

    而在InnoDB引擎中,主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引,而唯一索引和普通辅助索引间除了唯一性约束外,在存储上没本质区别。

    从查询性能上来说,在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的,在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程。InnoDB表基于普通索引的查找代价更高,因为每次检索到结果后,还需要至少再多检索一次才能确认是否还有更多符合条件的结果,主键索引和唯一索引就不需要这么做了。

    经过测试,对100万行数据的MyISAM做随机检索(整数类型),主键和唯一索引的效率基本一样,普通索引的检索效率则慢了30%以上。换成InnoDB表的话,唯一索引比主键索引效率约慢9%,普通索引比主键索引约慢了50%以上。

    聚集索引在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)。

    也有人把聚集索引称为聚簇索引。

    当然了,聚集索引的概念不是MySQL里特有的,其他数据库系统也同样有。

    简言之,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序,而非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。

    我们先来看看两种存储形式的不同之处:

    简单说,IOT表里数据物理存储顺序和主键索引的顺序一致,所以如果新增数据是离散的,会导致数据块趋于离散,而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。

    IOT表相比HOT表的优势是:

    范围查询效率更高;

    数据频繁更新(聚集索引本身不更新)时,更不容易产生碎片;

    特别适合有一小部分热点数据频繁读写的场景;

    通过主键访问数据时快速可达;

    IOT表的不足则有:

    数据变化如果是离散为主的话,那么效率会比HOT表差;

    HOT表的不足有:

    索引回表读开销很大;

    大部分数据读取时随机的,无法保证被顺序读取,开销大;

    每张InnoDB表只能创建一个聚集索引,聚集索引可以由一列或多列组成。

    上面说过,InnoDB是聚集索引组织表,它的聚集索引选择规则是这样的:

    首先选择显式定义的主键索引做为聚集索引;

    如果没有,则选择第一个不允许NULL的唯一索引;

    还是没有的话,就采用InnoDB引擎内置的ROWID作为聚集索引;

    c26d9da1b40ddb8edfe6aa3abd6a72c8.png

    可以看到,在这个索引结构的叶子节点中,节点key值是主键的值,而节点的value则存储其余列数据,以及额外的ROWID、rollback pointer、trx id等信息。

    结合这个图,以及上面所述,我们可以知道:在InnoDB表中,其聚集索引相当于整张表,而整张表也是聚集索引。主键必然是聚集索引,而聚集索引则未必是主键。

    MyISAM是堆组织表,它没有聚集索引的概念。

    两者的比较

    下面是一个简单的比较表

    d3c580fb6cfd9da9091a40adaac36561.png

    展开全文
  • 一. 简介1....2.含义索引是一个单独的,存储在磁盘上的数据结构,它们包含对数据表里所有记录的引用指针,使用索引用于快速找出在某个或多个列中有某一特定值的行,对相关列使用索引是降低查询操...

    一. 简介

    1.背景

    索引用于快速找出在某个列中有某一特定值的行,不使用索引,数据库必须从第一条记录开始读完整个表,直到找出相关的行。表越大,查询数据所花费的时间越多, 如果表中查询的列有一个索引,数据库能快速到达一个位置去搜索数据。

    2.含义

    索引是一个单独的,存储在磁盘上的数据结构,它们包含对数据表里所有记录的引用指针,使用索引用于快速找出在某个或多个列中有某一特定值的行,对相关列使用索引是降低查询操作时间的最佳途径。索引可以是由表或视图中的一列或多列生成的键。

    例如:数据库中有20000条记录,现在要执行这样一个查询:SELECT * FROM tableName WHERE num=10000。如果没有索引,必须遍历整个表,直到num=10000的这一行为止;如果在num列上创建索引,SQL Server不需要任何扫描,直接在索引里面找10000,就可以得知这一行的位置。可见,索引的建立可以加快数据的查询速度。

    3.索引的优点

    (1).通过创建唯一索引,可以保证数据库表的每一行数据的唯一性.(eg:表主键)

    (2).建索引最主要的目的:大大加快了数据的查询速度.

    (3).实现数据的参照完整性,可以加速表和表之间的连接.

    (4).在使用分组和排序子句进行查询时,也可以显著减少查询中分组和排序的时间

    4.索引的弊端

    (1).创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加.

    (2).索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到做大文件尺寸。

    (3).对表中的数据进行增加,删除和修改的时候,索引也要动态地维护,这样就就降低了数据的维护速度。

    二. 索引分类

    SQL Server中的索引有两种:聚集索引和非聚集索引,它们的区别是在物理数据的存储方式上。

    1.聚集索引

    聚集索引基于数据行的键值,在表内排序和存储这些数据行。

    (1).每张表只能有一个聚集索引,因为数据行本身只能按一个顺序存储.

    (2).表中的物理顺序和索引中行的物理顺序是相同的,创建任何非聚集索引之前要先创建聚集索引,这是因为非聚集索引改变了表中行的物理顺序.

    (3).关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护.

    (4).在索引的创建过程中,SQL Server临时使用当前数据库的磁盘空间,所以要保证有足够的空间创建索引

    2.非聚集索引

    非聚集索引具有完全独立于数据行的结构,使用非聚集索引不用将物理数据页中的数据按列排序,非聚集索引包含索引键值和指向表数据存储位置的行定位器。可以对表或索引视图创建多个非聚集索引。通常,设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。查询优化器在搜索数据值时,先搜索非聚集索引以找到数据值在表中的位置,然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择,因为索引中包含搜索的数据值在表中的精确位置的项。

    考虑使用非聚集索引的情况:

    (1).使用JOIN或者GROUP BY子句,应为连接和分组操作中所涉及的列创建多个非聚集索引,为任何外键创建一个聚集索引.

    (2).包含大量唯一值的字段.

    (3).不返回大型结果集的查询,创建筛选索引以覆盖从大型表中返回定义完善的的行子集的查询.

    (4).经常包含在查询的搜索条件(如返回完全匹配的WHERE子句)中的列.

    3.其它类别索引

    除了聚集索引和非聚集索引之外,还有一些根据其它依据划分的索引:

    (1).唯一索引:确保索引键不包含重复的值,因此,表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。这种唯一性和主键约束是关联的,在某种程度上,主键约束等于唯一性的聚集索引。

    (2).包含列索引:一种非聚集索引,它扩展后不仅包含键列,还包含非键列。

    (3).索引视图:在视图上添加索引后能提高视图的查询效率。视图的索引将具体化视图,并将结果集永久存储在唯一的聚集索引中,而且存储方式相同,创建聚集索引后,可以为视图添加非聚集索引。

    (4).全文索引:一种特殊类型的基于标记的功能索引,由SQL Server全文引擎生成和维护,用于帮助在字符串数据中搜索复杂的词,这种索引的结构与数据库引擎使用的聚集索引或非聚集索引的B树结构是不同的。

    (5).空间索引:一种针对geometry数据类型的列上建立的索引,这样可以更高效的列中的空间对象执行某些操作,空间索引可以减少需要应用开销相对较大的空间操作的对象数。

    (6).筛选索引:一种经过优化的的非聚集索引,尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的的部分进行索引,与全表索引相比,设计良好的筛选索引可以提高查询性能,减少索引维护开销并可降低索引存储开销

    (7).XML索引:是与XML数据关联的索引形式,是XML二进制大对象(BLOB)的已拆分持久表示形式,XML索引可以分为主索引和辅助索引。

    三. 设计原则

    索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要,设计索引时,应该考虑以下原则:

    (1).索引并非越多越好,一个表中如果有大量的索引,不仅占用大量的磁盘空间,而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时,索引也会进行调整和更新。

    (2).避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

    (3).数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

    (4).在条件表达式中经常用到的、不同值较多的列上建立索引。如果在不同值较少的列上不要建立索引,比如在学生表的【性别】字段上只有【男】或【女】两个不同值,因此就无需建立索引,如果建立索引,不但不会提高查询效率,反而会严重降低更新速度。

    (5).当唯一性是某种数据本身的特征时,指定唯一索引,使用唯一索引能够确保定义的列的数据完整性,提高查询速度.

    (6).在频繁进行排序或分组(即进行GROUP BY 或ORDER BY操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

    四. 索引的管理

    1.常见指令

    (1).查看表的索引:exec sp_helpindex 'tableName';

    (2).查看索引的统计信息:DBCC SHOW_STATISTICS ('数据库名.dbo.表名',索引名);

    (3).修改索引名称:exec sp_rename '表名.旧索引名' ,'新索引名', index;

    例如:将teacher表中的索引名称idx_nameAndgender更改为multi_index

    答案:exec sp_rename 'teacher.idx_nameAndgender' ,'multi_index', index

    (4).删除索引:DROP INDEX 表名.索引名

    例如:删除表teacher中的索引 multi_index

    答案:DROP INDEX teacher.multi_index

    注:DROP INDEX 命令不能删除由 CREATE TABLE或者ALTER TABLE命令创建的主键(PRIMARY KEY)或者唯一性(UNIQUE)约束索引,也不能删除系统表中的索引.

    (5).创建索引

    CREATE [UNIQUE] [CLUSTERED|NONCLUSTERRED]

    INDEX 索引名 ON {table名|view名}(column名 [ASC|DESC] [,...n])

    PS:以上是索引的基本创建格式,下面补充创建索引的其它参数配置:

    8f900a89c6347c561fdf2122f13be562.png

    961ddebeb323a10fe0623af514929fc1.png

    1  CREATEINDEX命令既可以创建一个改变表的物理顺序的聚集索引,也可以创建提高查询性能的非聚集索引。语法如下:2

    3   CREATE [UNIQUE] [CLUSTERED | NONCLUSTERRED]

    4

    5   INDEX index_name ON {table | view }(column [ASC | DESC] [,...n])6

    7   [INCLUDE ( column_name [ ,...n])]8

    9   [with10

    11 (12

    13 PAD_INDEX={ON | OFF}14

    15 | FILLFACTOR =fillfactor16

    17 | SORT_IN_TEMPDB={ ON | OFF}18

    19 | IGNORE_DUP_KEY ={ ON | OFF}20

    21 | STATISRICS_NORECOMPUTE ={ ON | OFF}22

    23 | DROP_EXISTING = { ON | OFF}24

    25 | ONLINE ={ ON | OFF}26

    27 | ALLOW_ROW_LOCKS ={ ON | OFF}28

    29 | ALLOW_PAGE_LOCKS={ ON | OFF}30

    31 | MAXDOP =max_degree_of_parallelism32

    33 ) [,...n]]34

    35 UNIQUE:表示在表或视图上创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。36

    37 CLUSTERED:表示创建聚集索引,在创建任何非聚集索引之前创建聚集索引,创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED,则创建非聚集索引。38

    39 NONCLUSTERED:表示创建一个非聚集索引,非聚集索引数据行的物理顺序独立于与索引排序,每个表最多可包含999个非聚集索引,NONCLUSTERED是CREATE INDEX的默认值。40

    41 index_name:指定索引的名称,索引名称在表或视图中必须唯一,但在数据库中可不必唯一。42

    43 ON{table | view}:指定索引所属的表或视图。44

    45 Column:指定索引基于一列或者多列。指定两个或者多个列名,可为指定的列的组合值创建组合索引,{table | view}后的括号中,按排序优先级列出组合索引要包括的列。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。46

    47 [ASC | DESC]:指定索引列的升序或降序排序方向,默认为ASC。48

    49 INCLUDE(column[,...n]):指定要添加到非聚集索引的叶级别的非键列。50

    51 PAD_INDEX:表示指定索引填充,默认值为OFF。ON值表示fillfactor指定的可用空间百分比应用于索引的中间级页。52

    53 FILLFACTOR=fillfactor:制定一个百分比,表示在索引创建或重新生成过程中数据库引擎应使每个索引页的叶级别达到的填充程度。fillfactor必须为介于1至100直接的整数值,默认值为0;54

    55 SORT_IN_TESTDB:指定是否在tempdb中存储临时排序结果,默认值为OFF,ON值表示在tempdb中存储用于生成索引的中间排序结果,OFF表示中间排序结果与索引存储在同一数据库中。56

    57 IGNORE_DUP_KEY:指定唯一聚集索引或唯一非聚集索引执行多行插入操作时,出现重复键值的错误响应。默认值为OFF。ON表示发出一跳警告消息,但只有违反了唯一索引的行才会失败,OFF表示发出错误消息,并回滚整个INSERT事物。58

    59 STATISRICS_NORECOMPUTE:指定是否重新计算分发统计信息,默认值为OFF。ON表示不会自动重新计算过时的统计信息,OFF表示启用统计信息自动更新功能。60

    61 DROP_EXISTING:指定删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。ON表示删除并重新生成现有索引。指定的索引名称必须与当前现有的索引相同,但可以修改索引定义,例如:可以指定不同的列,排序顺序,分区方案或索引选项。OFF表示如果指定的索引名已存在,则会显示一条错误。62

    63 ONLINE={ON | OFF}:指定在索引操作期间,基础表和关联的索引是否用于查询和数据修改操纵,默认值为OFF。64

    65 ALLOW_ROW_LOCKS:指定是否允许行锁,默认值为ON,ON表示在访问索引时允许行所。数据库引擎确定何时使用行锁。OFF表示未使用行锁。66

    67 ALLOW_PAGE_LOCKS:指定是否允许页锁,默认值为ON,ON表示在访问索引时允许页锁。数据库引擎确定何时使用页锁。OFF表示未使用页锁。68

    69 MAXDOP:指定在索引操作期间,覆盖【最大并行度】配置选项,使用MAXDOP可以限制在执行并行计划的过程中使用的处理器数量,最大数量为64.

    View Code

    例1:在teacher表中的t_phone列上,创建一个名称为Idx_phone的唯一聚集索引,降序排列,填充因子为30%

    CREATE UNIQUE CLOSTERED INDEXIdx_phoneON teacher (t_phone DESC)WITH  FILLFACTOR=30;

    例2:在teacher表中的t_name和t_gender列上,创建一个名称为Idx_nameAndgender的-唯一非聚集组合索引,升序排列,填充因子为10%

    CREATE UNIQUE NONCLUSTERED INDEXIdx_nameAndgenderONteacher(t_name,t_gender)WITH FILLFACTOR=10;

    五. EFCore使用索引

    1.映射EF实体

    (1).映射生成:【Scaffold-DbContext "Server=localhost;Database=EFDB01;User ID=sa;Password=123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables T_UserInfor,T_RoleInfor -Context ypfContext -UseDatabaseNames -DataAnnotations】

    (2).覆盖:【 Scaffold-DbContext "Server=localhost;Database=EFDB01;User ID=sa;Password=123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables T_UserInfor,T_RoleInfor -Context ypfContext -UseDatabaseNames -DataAnnotations -Force】

    以上指令会自动映射数据库中实体的索引.

    2.相关索引的映射语句

    (1).单字段索引: entity.HasIndex(e => e.userName).HasName("Index_userName");

    (2).多字段索引: entity.HasIndex(e => new { e.userName, e.addTime }).HasName("Index_userAge_addTime").IsUnique();

    (3).单子段唯一索引:entity.HasIndex(e => e.userName).HasName("Index_userName").IsUnique();

    PS:SQL中对应的生成索引的语句如下:

    --给userName添加非聚集索引

    CREATE NONCLUSTERED INDEXIndex_userNameONT_UserInfor(userName)--给userAge和addTime添加联合唯一的非聚集索引

    CREATE UNIQUE NONCLUSTERED INDEXIndex_userAge_addTimeONT_UserInfor(userName,addTime)WITH FILLFACTOR=10;

    31659fe757f1f680962636195c565b92.png

    索引的命名习惯:Index_字段名, Index_字段1名_字段2名

    六. 测试索引对查询性能的影响

    后续补充

    !

    作       者 : Yaopengfei(姚鹏飞)

    声     明1 : 如有错误,欢迎讨论,请勿谩骂^_^。

    声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址,否则保留追究法律责任的权利。

    展开全文
  • Mysql索引

    2021-02-06 20:27:02
    3、经常与其他表进行连接的表,在连接字段上应该建立索引;4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;5、索引应该建在选择性高的字段上;6、索引应该建在小字段上,对于大的文本字段甚至...

    常用规则

    1、表的主键、外键必须有索引;

    2、数据量超过300的表应该有索引;

    3、经常与其他表进行连接的表,在连接字段上应该建立索引;

    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;

    5、索引应该建在选择性高的字段上;

    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

    以上是一些普遍的建立索引时的判断依据。索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。

    总的来说,小型表肯定不建索引,或者数据库记录在亿条数据级以上,还是建议使用非关系型数据库。 还有些特殊字段的数据库,比如BLOB,CLOB字段肯定也不适合建索引。

    注意事项:

    性能调整方面

    再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。 9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。 9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。 最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。

    设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    比如有一条语句是这样的:

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。

    select * from users where YEAR(adddate)

    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id

    正确选择复合索引中的主列字段,一般是选择性较好的字段;

    复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否 极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

    如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

    如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

    如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引; 8、频繁进行数据操作的表,不要建立太多的索引; 9、删除无用的索引,避免对执行计划造成负面影响;

    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。 另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。

    对千万级MySQL数据库建立索引的事项及提高性能的手段

    首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。 其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。

    首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。 其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

    MySql在建立索引优化时需要注意的问题

    创建索引

    复合索引

    select * from users where area=’beijing’ and age=22;

    索引不会包含有NULL值的列

    使用短索引

    排序的索引问题

    like语句操作

    不要在列上进行运算

    不使用NOT IN和操作

    展开全文
  • mysql索引结构

    2019-12-25 14:22:49
    自上上篇写了手动搭建Redis集群和MySQL主从同步(非Docker)和上篇写了动手实现MySQL读写分离and故障转移之后,索性这次把数据库中最核心的也是最难搞懂的内容,也就是索引,分享给大家。 这篇博客我会谈谈对于索引...
  • 2、索引优化分析 1、性能下降SQL慢、执行时间长...mysql官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质:索引是数据结构。 你可以简单理解为“排好序的快速查询数据结构”
  • MySQL 索引结构

    2021-03-22 20:42:05
    文章目录从一个简单的表开始页的引入排序对性能的影响基于单页模式存储的查询流程上述页模式可能带来的问题页目录的...MySQL的其它知识点聚簇索引和非聚簇索引联合索引的最左前缀匹配原则为什么InnoDB只有一个聚簇索引...
  • 只有唯一索引才真正提高速度,一般的索引只能提高30%左右讨论MySQL选择索引时单列单列索引和多列索引使用,以及多列索引的最左前缀原则。1. 单列索引在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。...
  • mySql性能优化,索引优化看这篇就够了
  • 于是考虑使用Mysql数据库,ORM使用EF.于是先踩下坑顺便记录一下,有需要的tx可以参考下.当你考虑使用EF连接Mysql的时候肯定是已经在网上搜了一堆教程.网上教程基本都是使用控制台做演示.跟着一步步来姿势没错的话可能...
  •   .Net EntityFramework4.7.2 应用程序台项目,使用 EF 框架 配置 MySQL数据库,并自动生成 MySQL 数据库。 二、配置 首先引入包 MySql.Data.Entity(我使用的版本是 6.10.9.0)、EntityFramework 1-1 创建实体 /// ...
  • 文章目录索引入门什么是索引MySQL中的索引索引的分类基础语法执行语法什么是执行语法执行计划的作用执行计划的语法执行计划详解 索引入门 什么是索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取...
  • 一、mysql安装linux版本rpm安装 查看是否安装了mysl rpm -qa | grep -i mysql 一定要下载指定的64位,因为电脑是64位的否则会安装失败 https://www.jb51.net/article/129294.htm 报错解决办法 安装mysql服务端 rpm -...
  • MySQL索引结构解析

    2020-02-19 23:45:45
    自上上篇写了手动搭建Redis集群和MySQL主从同步(非Docker)和上篇写了动手实现MySQL读写分离and故障转移之后,索性这次把数据库中最核心的也是最难搞懂的内容,也就是索引,分享给大家。 这篇博客我会谈谈对于索引...
  • IdentityServer4中,使用efcore生成PersistedGrant数据库时报错,错误信息如下:fail: Microsoft.EntityFrameworkCore.Database.Command[20102]Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text...
  • MySQL索引性能测试

    2019-09-24 17:50:26
    MySQL索引性能测试 blog文档结构图: 很长一段时间没学习MySQL了,一直致力于oracle的研究,最近得空了就再...
  • MySQL数据库索引解析

    2018-05-12 13:36:15
    MySQL索引建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引MySQL是一辆兰博基尼的话,那么没有设计和使用索引MySQL就是一个人力三轮车。 考虑如下...
  • 详解MySQL索引

    2022-04-17 15:44:58
    如果不了解索引如何使用,索引的分类,建立索引在查询条件下是否会失效,或者不清楚如何使用MySQL内置的explain关键字来评判sql查询性能的优劣,可以阅读本文。本文结合案例对上述内容做了讲解。
  • mysql 索引的使用之中文全文索引

    千次阅读 2018-10-21 22:30:05
    这里讨论的是 mysql 5.7 InnoDB 全文索引,在mysql 5.6 之前的版本中,myisam 支持全文索引而InnoDB 是不支持的,而且 mysql 全文索引对中文的支持也不太友好,所以一般采用其它方案去替代。一般的替代方案是 ...
  • MySQL介绍、索引、BTree介绍
  • MySQL 优化,索引

    2020-05-04 10:28:09
    一、MyISAM 和 InnoDB 1. 对比: 2. MyISAM 适用于读操作多于写操作,写锁后其他线程不能做任何操作,会造成阻塞。不支持事务。...Mysql 默认InnoDB 存储引擎,部分支持事务,支持Btree和自适应Hash...
  • Mysql索引基础: 索引基本概念  在Mysql中也称为键(key),是存储引擎用于快速找到记录的一种数据结构。类似书的目录。  索引可以包含一个或多个列的值,如果包含多个列的值,那么列的顺序也非常重要,...
  • mysql的全文索引

    2019-11-20 11:30:08
    mysql的全文索引那点事
  • MySQL Mysql下载地址 安装步骤 从windows传过来的软件放在linux的opt中用rpm -qa|grep -i mysql查询是否安装过mysql解压安装包:rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpmi:install v:/var/log h:hash ...
  • MySQL-索引 1、索引概述 MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)...
  • 索引概述 正常的业务SELECT居多,而INSERT UPDATE DELETE偏少,所以使用索引, 用空间换时间;查询变快,增删改变慢。 可以选择给单个字段加索引,也可以联合几个字段加索引 主键和外键默认都有索引,没有主键会...
  • 【判断题】判断零件键槽的尺寸标注是否正确。【图片】【单选题】【图片】【单选题】读零件图,回答问题:1. C3表示倒角为( )度?2. C2表示倒角锥台高度为( )?...DATA SEGMENTDAT1 DB 78H,'AB'DAT2 DW 'EF', $...
  • 自上上篇写了手动搭建Redis集群和MySQL主从同步(非Docker)和上篇写了动手实现MySQL读写分离and故障转移之后,索性这次把数据库中最核心的也是最难搞懂的内容,也就是索引,分享给大家。这篇博客我会谈谈对于索引结构...
  • MySQL索引详细介绍

    2019-12-31 10:20:21
    为什么要建立索引? 索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL...
  • mysql手册03_索引

    万次阅读 2020-08-14 19:45:14
    mysql手册03_索引 索引是帮助MySQL进行高效率数据查询的数据结构 索引的优势和劣势: 优势:提高数据检索和排序的效率 劣势:索引占用空间,更新表时,MySQL不仅要更新数据,还要更新索引信息。 BTREE结构: 以5叉...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,266
精华内容 2,106
关键字:

ef mysql 建立索引

mysql 订阅