自己做黑彩网站,外贸网站销售方式,内部oa管理系统,网站文章标题改了 百度快照多久能改数据库索引#xff1a; 索引#xff08;index#xff09;是帮助MySQL高效获取数据的数据结构#xff08;有效#xff09;#xff0c;在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构#xff0c;这些数据结构以某种方式引用#xff08;指向#x…数据库索引 索引index是帮助MySQL高效获取数据的数据结构有效在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。简而言之帮助MySQL高效的查询出数据的数据结构叫做索引。
索引的优势 索引类似于书籍的目录提高数据检索的效率减少数据库IO的成本
通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗
索引的劣势 实际上索引也是一张表存储在磁盘上该表保存了主键与索引字段并指向实体类的记录
虽然索引大大提高了查询的速度但是降低了增删改的速度对表进行update、insert、delete时需要对索引文件进行更新
从四点讲述索引功能 为什么要给表加上主键
为什么加索引后会使查询变快
为什么加索引后会使写入、修改、删除变慢
什么情况下要同时在两个字段上建索引
首先我们想要了解索引的原理我们需要清楚一种数据结构平衡树也就是b tree或者 b tree 当然 有的数据库也使用哈希桶作用索引的数据结构, 然而,主流的RDBMS关系型数据库都是把平衡树当做数据表默认的索引数据结构的
聚集索引与非聚集索引 我们平时建表的时候都会为表加上主键 在某些关系数据库中 如果建表时不指定主键数据库会拒绝建表的语句执行。 如果定义了主键InnoDB会自动使用主键来创建聚集索引。如果没有定义主键InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引InnoDB会隐式定义一个主键来作为聚集索引。
MyISAM:
BTree叶节点存放的是数据记录的地址在检索的时候先找到索引对应的数据记录的地址再根据地址读取相应的数据记录这种查找方式被称为“非聚集索引”。
InnoDB:
它的主键索引是聚集索引即主键和行记录放在同一个叶节点找到了主键也就找到了行记录而它的非主键索引或者说是辅助索引是非聚集索引跟MyISAM引擎的非聚集索引不同的是MyISAM叶节点保存的是地址而InnoDB是主键InnoDB非聚集索引的索引文件和数据文件分开存储索引文件的叶节点只保存主键在查找时要先找到叶节点中的主键再根据主键去主索引文件查找详细行记录因此在设计表的时候主键字段不宜过长。
适合建索引
1. 字段的数值有唯一性的限制 业务上具有唯一特性的字段即使是组合字段也必须建成唯一索引。来源Alibaba
说明不要以为唯一索引影响了 insert 速度这个速度损耗可以忽略但提高查找速度是明显的。
2. 频繁作为 WHERE 查询条件的字段 某个字段在SELECT语句的 WHERE 条件中经常被使用到那么就需要给这个字段创建索引了。尤其是在数据量大的情况下创建普通索引就可以大幅提升数据查询的效率。
比如学生表数据表含100万条数据我们肯定会经常用到学生的学号和姓名等。
3. 经常 GROUP BY 和 ORDER BY 的列 索引就是让数据按照某种顺序进行存储或检索因此当我们使用 GROUP BY 对数据进行分组查询或者使用ORDER BY对数据进行排序的时候就需要对分组或者排序的字段进行索引 。如果待排序的列有多个那么可以在这些列上建立组合索引 。
4. UPDATE、DELETE 的 WHERE 条件列 对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作如果对 WHERE 字段创建了索引就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录然后再对它进行更新或删除。
小提示如果进行更新的时候更新的字段是非索引字段提升的效率会更明显这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT 字段需要创建索引 有时候我们需要对某个字段进行去重使用 DISTINCT那么对这个字段创建索引也会提升查询效率。这是因为索引会对数据按照某种顺序进行排序所以在去重的时候也会快很多。
6. 多表 JOIN 连接操作时创建索引注意事项 首先 连接表的数量尽量不要超过 3 张 因为每增加一张表就相当于增加了一次嵌套的循环数量级增长会非常快严重影响查询的效率。其次 对 WHERE 条件创建索引 因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下没有 WHERE 条件过滤是非常可怕的。 最后 对用于连接的字段创建索引 并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型而不能一个为 int 另一个为 varchar 类型因为类型不一样时数据库是会做隐式类型转换的而做了类型转换的sql是不走索引的。
7. 使用列的类型小的创建索引 我们这里所的类型大小指的就是该类型表示的数据范围的大小。 我们在定义表结构的时候要显式的指定列的类型 以整数类型为例 有TINYINT 、INT BIGINT 等 它们占用的存储空间依次递增 能表示的整数范围当然也是依次递增。如果我们想要对某个整数列 建立索引的时在表示的整数范围允许的情况下 尽量让索引列使用较小的类型 比如我们能使INT 就不要使 BIGINT。这是因为
数据类型越小在查询时进行的交操作越快 数据类型越小索引占用的存储空间就越少 在一个数据页内就可以放下更多的记录 从而减少磁I/0 带 来的性能损耗也就意味着可以把更多的数据页缓存在内存中 从而加快读写效率。 这个建议对于表的主键来说更加适用 因为不仅是聚簇索引中会存储主键值 其他所有的二级索引的节点处都会存储一份记录的主键值 如果主诞使甲更小的数据类型 也就意味着节省更多的存储空间和更高效的。
8. 使用字符串前缀创建索引 拓展 Alibaba 《Java 开发手册》 【 强制 】在 varchar 字段上建立索引时必须指定索引长度没必要对全字段建立索引根据实际文本区分度决定索引长度。 说明索引的长度与区分度是一对矛盾体一般对字符串类型数据长度为 20 的索引区分度会 高达90% 以上 可以使用 count(distinct left( 列名 , 索引长度 ))/count(*) 的区分度来确定。 9. 区分度高(散列性高)的列适合作为索引 10. 使用最频繁的列放到联合索引的左侧 这样也可以较少的建立一些索引。同时由于最左前缀原则可以增加联合索引的使用率。
11. 在多个字段都要创建索引的情况下联合索引优于单值索引 二.7种不适合创建索引的常见情况 1. 在where中使用不到的字段不要设置索引 2. 数据量小的表最好不要使用索引 3. 有大量重复数据的列上不要建立索引 举例1要在 100 万行数据中查找其中的 50 万行比如性别为男的数据一旦创建了索引你需要先访问 50 万次索引然后再访问 50 万次数据表这样加起来的开销比不使用索引可能还要大。
结论当数据重复度大(比如 高于 10% 的时候)也不需要对这个字段使用索引。
4. 避免对经常更新的表创建过多的索引 5. 不建议用无序的值作为索引 例如身份证、UUID(在索引比较时需要转为ASCII并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
6. 删除不再使用或者很少使用的索引 7. 不要定义冗余或重复的索引 ① 冗余索引 举例建表语句如下 CREATE TABLE person_info( id INT UNSIGNED NOT NULL AUTO_INCREMENT , name VARCHAR ( 100 ) NOT NULL , birthday DATE NOT NULL , phone_number CHAR ( 11 ) NOT NULL , country varchar ( 100 ) NOT NULL , PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name( 10 ), birthday, phone_number), KEY idx_name (name( 10 )) ); 通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索再创建一 个专门针对 name 列的索引就算是一个 冗余索引 维护这个索引只会增加维护的成本并不会对搜索有什么好处。 ② 重复索引 另一种情况我们可能会对某个列 重复建立索引 比方说这样 CREATE TABLE repeat_index_demo ( col1 INT PRIMARY KEY , col2 INT , UNIQUE uk_idx_c1 (col1), INDEX idx_c1 (col1) ) 我们看到 col1 既是主键、又给它定义为一个唯一索引还给它定义了一个普通索引可是主键本身就会生成聚簇索引所以定义的唯一索引和普通索引是重复的这种情况要避免。 最后敲黑板“限制索引的数目 ”
