Mysql的索引

Mysql的索引
1、索引介绍

提高数据库性能，索引是最物美价廉，不加内存，不改程序，不用调sql，只要执行个正确的‘create index’，查询速度就可能提高百倍千倍，诱惑力极大。但没免费的晚餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。以空间换时间。

索引（Index）是帮助DBMS（数据库）高效获取数据的数据结构，索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。如同：字典的目录。
比如：根据id查找数据，如果没有使用索引，就会从第一行记录开始扫表，直到找到想要的ID为止，当数据量比较大，恰好想要的数据在最后一条，那么查询速度就会非常慢：如： where id = 9

使用索引：Id列使用索引后，Mysql会为id列维护一个索引结构，数据检索的时候就从索引中进行检索，能大大提高检索速度。举例：

2、索引分类

mysql中索引的分类：
- 普通索引Normal：允许重复的值出现，可以在任何字段上面添加
- 唯一索引Unique：除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null
- 主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值
- 全文索引：用来对表中的文本域(char，varchar，text)进行索引，全文索引针对MyISAM有用InnoDB不支持全文索引，所以一般不用,默认只支持英文. -使用ES,Lucene代替就ok
3、索引的方式

索引方式分为：BTREE 和 Hash

Hash方式底层使用的是Hash表算法，时间复杂度是n(1) ,一次IO就能查询到结果，打算Hash的场次是无序的，有如下缺点：
- Hash结构的，InnoDB,MyIsam都不支持Hash
- 只能进行等值查询( = , in)，不能使用范围查询（ > ，< ，Between ）
- 列的重复值过多会出现大量Hash冲突问题
- InnoDB不支持Hash方式， Memory存储引擎可以用到
  Mysql常用引擎允许的索引类型
- FullText全文索引算法，myisam,只能能在char vachar text
- hash通过Hash表算法，如同一个Map,通过一个key直接就能找到value
- B+tree算法
4、索引的原理

 4.1、InnoDB索引结构
- InnoDB索引物理结构
  MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1，默认存储到“C:\ProgramData\MySQL\MySQL Server 5.5\data”目录下
  表结构文件：xxx.frm
  
  索引数据文件：ibdata1
  
  InnoDB使用了b+tree作为索引结构，在InnoDB中索引和数据在同一个文件ibdata1，所以数据会存储在索引结构中。
- InnoDB索引B+Tree
  下图是InnoDB的B+树索引结构，它的优势体现在：
- B+Tree属于多路树，每次查询都要走到叶子节点，查询效率稳定
- 非叶子节点不存储完整数据，而是存储键值 KEY ，和子树节点的应用，可以存储更多的KEY，充分利用每个节点的存储空间 16KB，减少了节点数，树高变矮，IO次数变少，性能更高。
- 叶子节点存储完整数据，叶子节点是有序的，每个叶子节点指向下一个节点的应用，形成一个链表，适合范围查询。
- InnoDB主键索引和辅助索引
对于主键默认会创建主键索引，其他列创建的索引就叫辅助索引，也叫二级索引，辅助索引的叶子节点存储的是主键索引的键值，这就意味着辅助索引需要查询两个B+Tree.
这里有2个概念：
- 回表：辅助索引扫描完之后还会扫描主键索引，这叫回表
- 覆盖索引：如果Select name 查询的列正好包含在辅助索引的节点的键值中，它就不需要在扫描主键索引了，这个叫覆盖索引。所以不要写Select *
  问：如果表么有主键怎么办？
  1.如果没有主键，Mysql会选择第一个不包含null的唯一索引作为主键索引，
  2.如果不满足条件一，那么会选择一个隐藏的行RowID作为主键索引
查看隐藏rowId：Select _rowid from t_user
- 为什么用B+Tree，为什么不使用其他数据结构，比如数组？链表？AVL-Tree，B-Tree 而要使用B+Tree：
- 有序数组：在查询的时候性能很高，可以二分查找，但是修改删除数据的时候会移动数组下标，性能比较差，这种结构只适合静态数据
- 链表：链表的删除，修改性能高，打算查询性能极差
- 综合上面的优势，有没有支持二分查找的链表结构呢？有：就是二叉查找树。
- 二叉查找树：它的查询性能和树高有关系，二叉树树高越高，查询越慢，而且在子树极端不平衡的情况下二叉树可能会变成链表，性能比较差。
- AVL-Tree 平衡二叉树结构：平衡二叉查找树，如果每个节点放一个键值，数据地址，子树的引用，InnoDB节点默认最大存储为16KB，那AVL-Tree平衡二叉树的节点存储数据是远远达不到16K的，浪费了大量的存储空间。如果数据量大，就意味着树高非常高，查找一个KEY就需要遍历很多的节点，时间复杂度大，I/O次数非常高,所以查询是比较慢的。
- B-Tree : B-Tree：多路平衡查找树：多叉树意味着每个节点可以存储更多的KEY，多叉树可以解决每个节点空间浪费问题，也可以解决树高问题从而达到较少IO次数提高效率。
  
  但是Btree性能已经不错了，但是还不够优化，InnoDB没有使用BTree，而是使用一个更优化的树：B+树
- B+Tree : 子节点存储数据，而且是有些了，相比BTree来说有如下优势：
  - 查询效率稳定：B+Tree属于多路树，每次查询都要走到叶子节点，查询效率稳定
  - 磁盘读写能力强：非叶子节点不存储完整数据，而是存储键值 KEY ，和子树节点的指针，这样每个节点就可以存储更多的KEY，充分利用每个节点的存储空间 16KB，减少了节点数，树高变矮，IO次数变少，性能更高。
  - 排序能力强：叶子节点存储完整数据，叶子节点是有序的，每个叶子节点指向下一个节点的应用，形成一个链表，适合范围查询，和对排序支持友好
4.2、Myisam索引结构
- MyISAM主键索引
myisam的数据和索引是分开的，所以树的节点指向的是数据的地址。数据存储在 course.MYD文件，索引存储在course.MYI文件中，coursr.frm是表结构定义文件，所以Myiasm的索引不存储数据，而是存储数据的磁盘地址。

下面是MyIsam索引结构，由于数据和索引是分开存放的，所以叶子节点存储的是数据的磁盘地址，而不是数据。
- MyISAM辅助索引
  
  注意：MyIsam的辅助索引的叶子节点没有指向主键索引的键值，而是直接指向的数据的磁盘地址
5、索引的操作

 5.1、普通索引

索引分类：普通索引/唯一索引/主键索引/全文索引，普通索引:允许重复的值出现，一般来说，普通索引的创建，是先创建表，然后在创建普通索引
- 创建表时创建索引：
```
create index 索引名 on 表 (列1,列名2,...);
1
```
- 修改表添加索引
```
alter table 表名add index 索引名(列1,列名2,..);
1
```
案例：
```
create table aaa(id int unsigned,name varchar(32));
create index nameIndex on aaa(name);
alter table aaa add index index1(name);
1
2
3
```
5.2、唯一索引

除了不能有重复的记录外，其它和普通索引一样，当表的某列被指定为unique约束时，这列就是一个唯一索引
- 建表时创建索引例如：
```
create table bbb(id int primary key auto_increment , name varchar(32) unique);
1
```
这时, name 列就是一个唯一索引，也可以在创建表后，再去创建唯一索引
```
create unique index 索引名 on 表名 (列1,列2,..);
alter table 表名add unique index 索引名 (列1,列2,..);
1
2
```
- 为表添加索引例如：
```
create table ccc(id int primary key auto_increment, name varchar(32));
1
```
注意：unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.主键字段，不能为NULL,也不能重复.

5.3、主键索引：

是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值
- 创建表时指定主键例如：
```
create table ddd(id int unsigned primary key auto_increment ,name varchar(32) not null  defaul ‘’);
1
```
这时id 列就是主键索引. 如果你创建表时，没有指定主键，
- 在创建表后，再添加主键。
```
alter table 表名 add primary key (列名);
1
```
举例:
```
create table eee(id int , name varchar(32) not null default ‘’);
alter table eee add primary key (id);
1
2
```
5.4、全文索引

用来对表中的文本域(char，varchar，text)进行索引，全文索引针对MyISAM有用
- 创建表时定义：
```
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;
1
2
3
4
5
6
```
- 为表添加索引例如：
```
 create fulltext index 索引名 on 表名(列1,列2);
 alter table 表名add fulltext index 索引名 (列1,列2);
1
2
```
比如:
```
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     )engine=myisam charset utf8;

Alter table article add fulltext index title_body_fulltext_index(title,body);
1
2
3
4
5
6
7
```
5.5、查询索引
```
show index from 表名
1
```
5.6、删除索引
```
alter table 表名 drop index 索引名; 
1
```
5.7、删除主键索引
```
alter table 表名 drop primary key 删除主键。      [主键定义为auto_increment时不能删除]
1
```
5.8、修改索引

先删除后添加=修改

 6、索引小技巧

根据索引列的多少分为复合索引(联合索引/组合索引)和普通索引
- 普通索引（单列索引）：该索引只在一个列上面创建
- 复合索引（多列索引）：该索引只在多个列上面创建
  对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引(向左匹配原则)。
```
alter table dept add index my_indx (dname,loc); 
1
```
dname 左边的列,loc就是右边的列
测试案例：
```
explain select * from dept where dname='aaa'       会使用到索引
1
```
测试案例：
```
explain select * from dept where loc='aaa'    就不会使用到索引
1
```
对于使用like的查询，查询如果是%aaa’不会使用到索引而‘aaa%’会使用到索引
```
explain select * from dept where dname like '%aaa'    不能使用索引
1
```
```
explain select * from dept where dname like 'aaa%'   使用索引.
1
```
所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用全文索引->lucene或Es

如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引. 全部能够单独使用才能使用
```
explain select * from dept where dname = 'aaa';	//如果使用索引
explain select * from dept where loc = 'aaa';		//如果不能使用索引
select * from dept where dname=’xxx’ or loc=’xx’;	//不能使用索引，它没办法从两个索引树种去检索
1
2
3
```
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
```
expain select * from dept where dname=’111’;
1
```
```
expain select * from dept where dname=111;（数值自动转字符串）
1
```
```
expain select * from dept where dname=qqq; 报错
1
```
也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.

7、注意事项

 7.1、索引的代价:
- 占用磁盘空间。
- 对dml操作有影响,因为要维护索引，变慢。
7.2、在哪些列上适合添加索引?
- 不会出现在WHERE子句中字段不该创建索引
- 较频繁的作为查询条件字段应该创建索引
  select * from emp where empno = 1
  - 唯一性太差(离散度)的字段不适合单独创建索引，即使频繁作为查询条件
    select * from emp where sex = '男’
- 更新非常频繁的字段不适合创建索引
  select * from emp where logincount = 1
7.3、索引失效因素
- 模糊查询 like “%keyword%” 不会使用到索引,like “keyword%”可以
- 列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来，否则索引失效
- 注意：not in 、not exist、!=、< >、like “%_”，以及in(select子句) 会导致索引失效
- 查询的条件列进行过运算或处理，不会走索引，因为不确定计算后的值是什么，
  如： where DATE_FORMART(start_time,’%y-%m-%d’) = “21-2-23” 不会走索引
- 查询null值如： where name is null 不会走索引，可以去null设定为 0 来代替。
- or会导致索引失效
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引，比如表里面只有一条记录
7.4、强制使用索引

select xx,oo from table force index(索引名) where xxx = xxxx;(强制使用主键)

8、思考：
1. 是不是索引越全越好
2. 性别字段适不适合建索引
3. 模糊匹配like“%abc”,“%abc%”,“abc%”谁能用到索引，谁不能用到，为什么
4. SQL写成： Select * 有什么问题
5. InnoDB的索引树和MyIsam的索引树有什么区别
相关阅读:
【21天算法学习】希尔排序
 Linux下C语言实现HTTP文件服务器和TCP协议实现网络数据传输
 Windows 10 - 适用于各种服务（Redis、MySQL）的文件迁移到其他目录后，导致的各种服务找不到的问题 - 注册服务 - 关闭服务 - 重启服务
 【月报】Aavegotchi 7 月更新请查收！
glog 日志库简介与测试【GO 常用的库】
【Effective C++ 笔记】（三）资源管理
 2、适配器模式
 一些好用的系统、组件记录
 【数据结构】二叉树二叉树的深度优先遍历和广度优先遍历完全二叉树和满二叉树的性质二叉树的节点个数以及叶子节点个数
 uni-app - 日期 · 时间选择器
原文地址：https://blog.csdn.net/baozi7263/article/details/133696163

1、索引介绍

2、索引分类

3、索引的方式

4、索引的原理

4.1、InnoDB索引结构

4.2、Myisam索引结构

5、索引的操作

5.1、普通索引

5.2、唯一索引

5.3、主键索引：

5.4、全文索引

5.5、查询索引

5.6、删除索引

5.7、删除主键索引

5.8、修改索引

6、索引小技巧

7、注意事项

7.1、索引的代价:

7.2、在哪些列上适合添加索引?

7.3、索引失效因素

7.4、强制使用索引

8、思考：