mysql索引

 索引

为什么要有索引？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，

因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

概念

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

 

好处：可以帮助你提高查询效率，数据量越大越明显，查询范围速度慢
缺点: 新增和删除数据时，效率较低

查看该表有没有索引的sql语句:

show index from 表名

结果:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| class |          0 | PRIMARY  |            1 | cid         | A         |           4 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

 

主键自动添加索引,索引一般是存在硬盘中的,但是memory存储引擎是把索引存到了内存中.索引是表的一部分.

对于索引的一些误解？

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

 

索引分类

　　索引按数据结构分为：B+树索引、hash索引、fullText索引等

　　索引按物理角度分为：聚簇索引和非聚簇索引

　　索引按逻辑角度分为：以下几种

1. 主键索引 .   primary key  加速查找+不为空且唯一的约束

　　  2.唯一索引  unique  除了索引功能外还有字段不为空约束功能

但是要注意以下两点

    1.唯一索引所在的列不能为空字符串
    2.唯一的索引所在的列可以为null.

#创建语法
create  unique index   索引名称  on 表名(列名)

　　3. 普通索引   加速索引

添加索引
create index 索引名  on  表名(字段)
删除索引:
dorp index 索引名 on 表名

　　4.联合索引 加速查找， 主要是用在多条件查找。 比如   select * from ee  where name ='tom' and age=13

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

 

　　5.全文索引. mysql的全文索引类型为Fulltext索引,仅myisam存储引擎支持. 比如我们想要在一篇文章中搜索成都两字,我们就可以建立全文索引.

create table book (
id int primary key auto_increment,
name varchar(100) not null,
body text ,
fulltext(body,name))engine=myisam default charset ='utf8';

查询事例:

 

 

在mysql数据库中,对表中的数据进行查找的几种方法

1. 全表扫描
2. 通过索引进行检索.

索引的两大数据结构

1.hash 是以key-value 的形式进行索引存储 ：查询单条快，范围查询慢

2.b+树 (平衡树一种变形)是以树方式进行索引存储。（innodb默认存储索引类型）,注意并没有用二叉树,因为二叉树存储的数据少,层多,查询io高

　　①B+ 树非叶子节点上是不存储数据的，仅存储键值，每一层的节点能索引到的数据范围更加的广,树的层数就越少,查询io就越少而 B 树节点中不仅存储键值，也会存储数据。

　　 ②B+ 树中各个叶子之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。支持范围查找

 

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

b+树的特点

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.联合索引的最左匹配原则：当b+树的数据项是复合的数据结构即建立了联合索引时

1.查询的时候必须带着最左边的索引才能都提高查询效率
2.查询条件中不能有范围

 

比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候即

select * from t1 where name = 张三 and age = 20 and sex = F，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。 

 详细博客见https://blog.csdn.net/u013164931/article/details/82386555

 什么情况下对该字段加索引?

　　1.该字段数据量庞大(百万级以上)

　　2.该字段很少dml(增删改)操作(因为索引需要不断维护,不断更新)

　　3.该字段经常用在where条件后

 

如何查询我们能使用索引查询?

1. 区别度低的字段不要加索引，加索引没效果

2.查找时范围大小影响查询速度.

1.范围越大查询越慢
2.范围越小查询越快

 

3.建立联合索引时把需要范围查找的字段放到后面 

select count(*)  from s1  where name='xiao' and age=10 and  id >1000

我们来给上面的sql语句建立索引：create index ee on s1(name,age,id) 这样建立的效率就高。这里用了最左匹配原则

4.当建立了联合索引后，查的时候where的条件中一定要包含联合索引中的最左边的那个字段否则查询效率会降低。应这样查询   

select * from ee where 
　　　　　　　　　　　　1.name
　　　　　　　　　　　　2. name age 
　　　　　　　　　　　　3. name  age  id 
#除此之外的所有情况都不会加快查询效率  这就是最左匹配原则 

5. 建立的索引字段不能参与计算,索引失效。

6.查询的时候数据类型要和建立索引的数据类型一样。

7. 不要使用not in，<>,！=这样不会走索引，

8.使用like模糊查询时，like '%张%' 和like "%张会导致索引不生效，like '张%' 索引能够被使用，查找以张开头的查询语句会使用索引.

9.对于 查询条件中有or 和and的来说

　　1.如果or的两边有一边没有建立单独索引，则不会提升效率  ，注意or的两边必须是建立单独索引才能提高效率

　　2.and两边有一边建立了索引,都会加快查询速度

#第一种情况 ,查询加了索引的字段
像这样select * from class  就不会用到索引

select  索引字段 from class  这样就可以使用到索引

#第二种情况:  where 条件后边的字段加了索引

select * from class where  class_no >10;

#第三种情况:如果表中存在几个字段组成的联合索引则查找记录时,这个联合索引的最左前缀匹配字段
例如若为某表创建了3个字段(c1,c2,c3)组成的联合索引,那么当你select c1或c2,或c3, 或(c1,c2)或(c1,c2,c3)都会加速查询,然而 (c2,c3)就不会被使用到索引,其实 (c1,c2)只用到了c1索引
#第四种情况多表做join操作时使用索引(前提join的字段在这些表中都建立了索引)
#第五种情况 若某字段已经建立索引,那么min或max该字段时候都会用到索引
#第六种情况 对建立索引字段sort或group时会使用到索引

 10.正则表达式不走索引

B+树

 

 

 

优秀博客 https://blog.csdn.net/weixin_42360237/article/details/112125202