Mysql索引

索引

名称解释

索引
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。
聚簇索引
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
非聚簇索引(辅助索引)
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据大部分情况下需要二次查找。辅助索引叶子节点存储的不再是行的记录数据，而是主键值或行的物理地址。通过辅助索引首先找到的是主键值或物理地址，再通过二次查找找到数据。
索引覆盖
①:如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。
因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。
不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。
②：查询语句的执行只用从索引中就能够取得，不必从数据表中读取，Innodb的主键查询就支持索引覆盖。

数据库默认隔离级别

mysql ---repeatable(可重复读),
oracle,sql server ---read commited(读已提交)。

MySQL默认锁

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

索引类型

主键索引
数据列不允许重复，不允许为Null，一个表只能有一个主键。
唯一索引
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
在sql server中，唯一索引字段不能出现多个null值；
在mysql 的innodb引擎中，是允许在唯一索引的字段中出现多个null值的。
普通索引
基本的索引类型，没有唯一性的限制，允许为NULL值。
全文索引
是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT(column),创建全文索引。在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

索引数据结构

简介

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。

B树索引

Mysql数据库中使用频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

可用于=,>,>=,<,<=,between和like这些比较操作符上，即只要它的查询条件是一个不以通配符开头的常量，如: like 'jack%'。

哈希索引

当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

只能用于对等(=)比较符,由于是一次定位数据,不像BTree索引需要从根节点到枝节点，后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

索引创建

创建原则

较频繁作为查询条件的字段才去创建索引,更新频繁字段不适合创建索引;
若是不能有效区分数据的列不适合做索引列(如性别，男女未知，多也就三种，区分度实在太低);
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间;
左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a=1 and b=2 and c>3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
定义有外键的数据列一定要建立索引。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
对于定义为text、image和bit的数据类型的列不要建立索引。

创建注意事项

非空字段：应该指定列为NOTNULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

索引的增删改

创建

创建表的时候创建

CREATE TABLE user_index2(
 id INT AUTO_INCREMENT PRIMARY KEY, -- 聚焦索引（主键索引),设置PRIMARY KEY时即创建
 first_name VARCHAR(16),
 last_name VARCHAR(16),
 id_card VARCHAR(18),
 information text,
 KEY name(first_name,last_name), -- KEY 普通索引
 FULLTEXT KEY(information), -- FULLTEXT 创建全文索引
 UNIQUE KEY(id_card) -- UNIQUE 唯一索引
);

注: mysql中主键创建最好不要与业务相关连，阿里巴巴开发手册中也有提到。

使用Alter table 命令增加索引
- 创建唯一索引
  ALTER TABLE table_name ADD UNIQUE(column);
- 创建唯一组合索引
  ALTER TABLE table_name ADD UNIQUE(column1,column2);
- 创建普通索引
  ALTER TABLE table_name ADD INDEX index_name(column);
- 创建普通组合索引
  ALTER TABLE table_name ADD INDEX index_name(column1,column2,column3);
- 创建主键索引
  ALTER TABLE table_name ADD PRIMARY KEY ( column );
- 创建全文索引
  ALTER TABLE table_name ADD FULLTEXT ( column );

查看索引

show index from user_index

注释
- Table 表的名称;
- Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1;
- Key_name 索引的名称;
- Seq_in_index 索引中的列序列号，从1开始;
- Column_name 列名称;
- Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序，B+树索引此值为A）或NULL（无分类);
- Cardinality 表中唯一值的估计个数;此列越接近行数越好，如果太小，要考虑去掉;同时优化器会根据这个值判断是否使用这个索引，这个值更新会有些延迟，手动触发更新使用analyze table命令，同时该值不是精确值，为数据采样值，系统自动触发条件为：数据调整1/16时或数据修改大于2 000 000 000次时;
- Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL;
- Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL;
- Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO;
- Index_type 索引类型;
- comment:注释;

删除索引

删除普通索引、唯一索引、全文索引

格式: alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引

格式:alter table 表名 drop primary key（因为主键只有一个）
如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引），需取消自增长再行删除

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

修改索引

mysql中没有真正意义上的修改索引，只有先删除之后在创建新的索引才可以达到修改的目的，原因是mysql在创建索引时会对字段建立关系长度等，只有删除之后创建新的索引才能创建新的关系保证索引的正确性；

即先行索引删除再创建索引

sql语句分析索引使用情况

explain select * from user_index2 where first_name=11;
explain和desc关键字会模拟优化器执行sql查询语句，从而知道MySQL如何处理sql语句。进而分析。

注释
- id 实际执行顺序 id 越大越先执行 id相同执行从上到下;
- select type 查询类型包含 simple（简单查询） primary（任何包含复杂子查询的最外层查询） subquery（select 或 where列表中包含了子查询） derived（在from列表中包含了子查询被标记为DERIVE的衍生，mysql递归执行这鞋子查询把结果放在临时表中） union（若第二个select语句出现在union之后则被标记为union，若union包含在from字句子查询中外层select标记为derived） unionresult（从union表中获取结果的select）等；
- table 操作的表；
- type 查询使用了那些类型system>const>eq_ref>ref>range>index>all
  - system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现。
  - const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MYSQL就能将该查询转换为一个常量。
  - eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
  - ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
  - range：只检索给定范围的行，使用一个索引赖选择行，key列显示使用了哪个索引，一般就是在where语句中出现between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描好，因为它只需要开始于索引的某一点，结束语于另一点。，不用扫描全部索引。
  - index：（Full Index Scan），Index和All区别为index类型只遍历索引树，这通常比ALL块，因为索引文件通常比数据文件小。
  - all：（Full Table Scan）将遍历全表以找到匹配的行。
- possible_keys 可能用到了索引
- key 实际用到的索引
- key_len 表示索引中使用的字节数可通过计算查询中使用的索引的长度。在不损失精度前提下，长度越短越好。是索引字段的最大可能长度，并非实际使用长度。
- ref 显示索引的那一列被使用了。如果可能的话，是一个常数。
- rows 根据表统计信息即索引选用情况，大直沽算出找到所需记录须读取的行数。
- Extra 不适合出现在其他列的十分重要的额外信息。可能出现以下信息
  - Using filesort mysql使用了外部的索引排序而不是按照表内索引顺序进行读取。mysql无法利用索引完成的排序操作称为 “文件排序” （不好）
  - Using temporary 使用了临时表保存中间结果。MySQL在对查询结果排序时使用了临时表。常见于order by和分组查询 group by。（不好）
  - Using Index 使用了索引覆盖，效率不错。如果同时出现using where 表明索引被用来执行键值的查找。

其他

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修
改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所
以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数
量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分
钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

MyISM使用的索引和Innodb的索引的区别

①:Innodb主键索引是聚簇索引;MyISM主键索引是非聚簇索引。
②:Innodb非聚簇索引使用的是B-Tree树，主键索引使用的是B+Tree。MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。
③:Innodb非聚簇索引存储的是主键值，MyISAM非聚簇索引存储的是数据的物理地址。

聚簇索引的优缺点

优点：
　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
　　2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
　　1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
　 2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

参考

MySQL删除数据：https://www.cnblogs.com/yhtboke/p/14229511.html；
聚簇索引和非聚簇索引:https://www.cnblogs.com/jiawen010/p/11805241.html。

posted @ 2020-02-21 23:44 晨度阅读(2922) 评论(0) 编辑收藏举报

刷新页面返回顶部

晨度

向前的普通人