1、索引介绍
1.1、什么是索引?
- 索引是数据结构,可以高效地获取数据
- 索引存储在文件系统中
- 索引的文件存储形式与存储引擎有关
- 索引文件结构
- hash
- 二叉树
- B树
- B+树 (MySql索引文件结构)
1.2、索引的优势和劣势
优势:
-
可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。
-
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
-
被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
-
如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多.
劣势:
- 索引会占据磁盘空间
- 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。
1.3、索引分类
-
主键索引
主键是一种唯一性索引,它必须指定为PRIMARY KEY,不能为空,每个表只能有一个主键
一个主键并非一定只有一个列,也可以是多个列组成的联合主键
MySql会自动为主键创建索引
-
唯一索引
索引列的所有值都只能出现一次,即必须唯一,值可以为空;一张表可以在不同的字段建多个唯一索引
(另:主键索引与唯一索引的区别:
1. 主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。 2. 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 3. 唯一性索引列允许空值,而主键列不允许为空值。 4. 主键索引在创建时,已经默认为非空值+ 唯一索引了。 5. 一个表最多只能创建一个主键索引,但可以创建多个唯一索引。 6. 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。 7. 主键可以被其他表引用为外键,而唯一索引不能。)
-
普通索引
基本的索引类型,值可以为空,没有唯一性的限制
-
全文索引(用的很少)
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建
-
组合索引
多列值组成一个索引,专门用于组合搜索
又称:复合索引、联合索引
-
空间索引
MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
-
前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。
1.3.2索引的CURD
创建索引
1:直接创建索引:
CREATE INDEX index_name ON table(column(length)); 创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); 全文索引
2:修改表结构的方式添加索引:
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
删除索引:
DROP INDEX index_name ON table;
查看索引:
show index from table_name;
更改索引
直接删除
1.4索引优化
1.4.1 Explain优化查询检测
EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。使用方法,在select语句前加上Explain就可以了;
EXPLAIN能干嘛
可以查看以下信息
-
id
:表的读取顺序select_type
:数据读取操作的操作类型
-
possible_keys
:哪些索引可以使用key
:哪些索引被实际使用
-
-
ref
:表之间的引用 -
rows
:每张表有多少行被优化器查询
-
1.4.2 常见名词
案例表
id(主键) name(普通索引) age 1 哈哈 6 2 呜呜 3 3 呼呼 8
(1)回表
如果创建的索引是其它字段(普通索引,不是主键),那么在叶子节点中存储的是该记录的主键(不是数据),然后再通过主键去 主键索引表 查找对应的记录
经过了两个表的查询:
- 普通索引表:查到主键
- 主键索引表:查询记录数据
-- name是普通索引,在普通索引表中存储的是主键id的值,即1 -- 要想得到记录的值,需经过二次查询 select * from table where name="呜呜"
(2)索引覆盖
-- 一次查询就可以得到结果,不需要回表 select id from table where name= "哈哈"
- 根据name的值去name的B+树检索对应的记录,能获取到id的属性值,索引的叶子结点中包含了查询的所有列,此时不需要回表,这个额过程叫做索引覆盖,会有using index的提示,推荐使用
- 在某些场景中,可以考虑将要查询的所有列都变成组合索引 ,此时会使用索引覆盖(不会回表),加快查询效率。
(3)最左匹配
索引可以简单如一个列 (a),也可以复杂如多个列 (a,b,c,d),即联合索引。
如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等),遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。
因此,列的排列顺序决定了可命中索引的列数。
例子:
如有索引 (a,b,c,d),查询条件 a=1 and b=2 and c>3 and d=4,则会在每个节点依次命中a、b、c,无法命中d。(c已经是范围查询了,d肯定是排不了序了)
(4)索引下推
- 把原来在server层进行的条件过滤下推到存储引擎层,索引下推是默认开启的
select * from user where name = "呼呼" and age = "8";
没有索引下推前
先根据 name 从存储引擎中拉取数据到 server 层,然后在 server 层中对 age 进行数据过滤
有索引下推后
根据 name 和 age,直接在存储引擎中做数据过滤,把结果返给 server 层
可以减少返给 server 层的数据量
(5)前缀索引
如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引;前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。
-- 这个值大于0.31就可以创建前缀索引,Distinct去重复
SELECT COUNT(DISTINCT(LEFT(title,10)))/COUNT(*) FROM Arctic;
-- 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度
ALTER TABLE user ADD INDEX `uname`(title(10));
1.5、优化小细节
-
当使用索引列进行查询的时候,尽量不要使用表达式,把计算逻辑放到业务层,减轻数据库运算压力
-
尽量使用主键查询,而不是其它索引,因为主键索引不会触发回表查询
-
如果 索引列 长度过长,可以使用前缀索引,否则会产生很大的索引文件,不便于操作
-
使用索引扫描来排序
参考:https://www.cnblogs.com/YC-L/p/14461561.html
-
mysql有两种方式可以生成有序的结果:通过 排序操作 或者 按索引顺序扫描
- 如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序
- 扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录
- 但如果索引不能覆盖查询所需的全部列,那么就不得不每扫描一条索引记录就得回表查询一次对应的行
- 这基本都是随机IO,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢
mysql可以使用同一个索引即满足排序,又用于查找行,如果可能的话,设计索引时应该尽可能地同时满足这两种任务
- 只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方式都一样时,mysql才能够使用索引来对结果进行排序
- 如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一张表时,才能使用索引做排序
- order by子句和查找型查询的限制是一样的,需要满足索引的最左前缀的要求
否则,mysql都需要执行顺序操作,而无法利用索引排序
-
-
union all,in,or 都可以使用索引
-
对于 索引列 最好使用 union all
因为复杂的查询【包含运算等】将使 or、in 放弃索引而全表扫描,除非确定 or、in 会使用索引
-
对于 非索引字段 用 or 或者 in,因为要全表扫描,而 union all 会成倍增加表扫描的次数
-
对于既有 索引字段【索引字段有效】又包含 非索引字段,使用三者都可以,推荐使用 or、in
参考:
博客园:https://www.cnblogs.com/maohuidong/p/10478356.html
-
-
范围列可以用到索引
- 范围条件是:<、<=、>、>=、between
- 范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列
-
强制类型转换会全表扫描
-- 数据库中的电话号码是 字符串形式(varchar),并建立了索引 -- 全表扫描,不会触发索引;因为以 数值 的形式查询,强制进行了类型转换 select * from user where phone = 10086; -- 触发了索引,不会全表扫描 select * from user where phone = "1008698";
-
更新频繁,数据区分度不高的字段,不宜建立索引
-
创建索引的列,不允许为null,可能会得到不符合预期的结果
-
当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致
-
能使用 limit 的时候,尽量使用 limit
-
单表索引建议控制在5个以内
-
组合索引中单索引字段在5个以内
-
索引的正确概念:
-
索引不是越多越好;索引也是要维护的,过多时会降低性能
-
在不了解系统的情况下,不要过早优化
2、存储引擎
常用的:MyIsam、InnoDB
2.1 InnoDB索引原理
互联网大多数应用是读多写少的,读写比例可达10:1;并且数据库在做查询时IO消耗较大,理论上,B+树的查找时间复杂度为log1.44(n),是大于logn的。从下图可以发现,MySQL是由一个个磁盘块组成的树形结构。每个磁盘块由数据项和指针组成。且所有的数据都是存放在叶子磁盘节点,非叶子磁盘节点不存放数据。
2.1.1查找过程
以磁盘块1为例,指针 P1 表示小于17的磁盘块,P2 表示在 17~35 之间的磁盘块,P3 则表示大于35的磁盘块。
比如要查找数据项99,首先将磁盘块1 load 到内存中,发生 1 次 IO。接着通过二分查找发现 99 大于 35,所以找到了 P3 指针。通过P3 指针发生第二次 IO 将磁盘块4加载到内存。再通过二分查找发现大于87,通过 P3 指针发生了第三次 IO 将磁盘块11 加载到内存。最后再通过一次二分查找找到了数据项99。
由此可见,如果一个几百万的数据查询只需要进行三次 IO 即可找到数据,那么整个效率将是非常高的。
观察树的结构,发现查询需要经历几次 IO 是由树的高度来决定的,而树的高度又由磁盘块、数据项(主键)的大小决定的。
磁盘块越大,数据项越小那么树的高度就越低。这也就是为什么索引字段要尽可能小的原因。2.2 InnoDB的内存架构主要分为三大块,缓冲池(Buffer Pool)、重做缓冲池(Redo Log Buffer)和额外内存池
2.2.1 缓冲池
InnoDB为了做数据的持久化,会将数据存储到磁盘上。但是面对大量的请求时,CPU的处理速度和磁盘的IO速度之间差距太大,为了提高整体的效率, InnoDB引入了缓冲池。
当有请求来查询数据时,如果缓存池中没有,就会去磁盘中查找,将匹配到的数据放入缓存池中。同样的,如果有请求来修改数据,MySQL并不会直接去修改磁盘,而是会修改已经在缓冲池的页中的数据,然后再将数据刷回磁盘,这就是缓冲池的作用,加速读,加速写,减少与磁盘的IO交互。
缓冲池说白了就是把磁盘中的数据丢到内存,那既然是内存就会存在没有内存空间可以分配的情况。所以缓冲池采用了LRU算法,在缓冲池中没有空闲的页时,来进行页的淘汰。但是采用这种算法会带来一个问题叫做缓冲池污染。
当你在进行批量扫描甚至全表扫描时,可能会将缓冲池中的热点页全部替换出去。这样以来可能会导致MySQL的性能断崖式下降。所以InnoDB对LRU做了一些优化,规避了这个问题。
MySQL采用日志先行,在真正写数据之前,会首先记录一个日志,叫Redo Log,会定期的使用CheckPoint技术将新的Redo Log刷入磁盘.
除了数据之外,里面还存储了索引页、Undo页、插入缓冲、自适应哈希索引、InnoDB锁信息和数据字典。下面选几个比较重要的来简单聊一聊。
①插入缓冲
插入缓冲针对的操作是更新或者插入,我们考虑最坏的情况,那就是需要更新的数据都不在缓冲池中。那么此时会有下面两种方案。
- 来一条数据就直接写入磁盘
- 等数据达到某个阈值(例如50条)才批量的写入磁盘
很明显,第二种方案要好一点,减少了与磁盘IO的交互。
2.2.2自适应哈希索引
自适应索引就跟JVM在运行过程中,会动态的把某些热点代码编译成Machine Code一样,InnoDB会监控对所有索引的查询,对热点访问的页建立哈希索引,以此来提升访问速度。
2.2.3重做日志缓冲
上面聊过,InnoDB中缓冲池中的页数据更新会先于磁盘数据更新的,InnoDB也会采用日志先行(Write Ahead Log)策略来刷新数据,什么意思呢?当事务开始时,会先记录Redo Log到Redo Log Buffer中,然后再更新缓冲池页数据。
Redo Log Buffer中的数据会按照一定的频率写到重做日志中去。被更改过的页就会被标记成脏页,InnoDB会根据CheckPoint机制来将脏页刷到磁盘。
补:日志
MySQL层面
InnoDB层面
-
1.MySQL日志
MySQL的日志可以分为错误日志、二进制文件、查询日志和满查询日志。
-
错误日志 很好理解,就是服务运行过程中发生的严重错误日志。当我们的数据库无法启动时,就可以来这里看看具体不能启动的原因是什么
-
二进制文件 它有另外一个名字你应该熟悉,叫Binlog,其记录了对数据库所有的更改。
-
查询日志 记录了来自客户端的所有语句
-
慢查询日志 这里记录了所有响应时间超过阈值的SQL语句,这个阈值我们可以自己设置,参数为
long_query_time
,其默认值为10s,且默认是关闭的状态,需要手动的打开。
2.InnoDB日志
InnoDB日志就只有两种,Redo Log和Undo Log,
-
Redo Log 重做日志,用于记录事务操作的变化,且记录的是修改之后的值。不管事务是否提交都会记录下来。例如在更新数据时,会先将更新的记录写到Redo Log中,再更新缓存中页中的数据。然后按照设置的更新策略,将内存中的数据刷回磁盘。
-
Undo Log 记录的是记录的事务开始之前的一个版本,可用于事务失败之后发生的回滚。
Redo Log记录的是具体某个数据页上的修改,只能在当前Server使用,而Binlog可以理解为可以给其他类型的存储引擎使用。这也是Binlog的一个重要作用,那就是主从复制,另外一个作用是数据恢复。
上面提到过,Binlog中记录了所有对数据库的修改,其记录日志有三种格式。分别是Statement、Row和MixedLevel。
- Statement 记录所有会修改数据的SQL,其只会记录SQL,并不需要记录下这个SQL影响的所有行,减少了日志量,提高了性能。但是由于只是记录执行语句,不能保证在Slave节点上能够正确执行,所以还需要额外的记录一些上下文信息
-
Row 只保存被修改的记录,与Statement只记录执行SQL来比较,Row会产生大量的日志。但是Row不用记录上下文信息了,只需要关注被改成啥样就行。
- MixedLevel 就是Statement和Row混合使用。
具体使用哪种日志,需要根据实际情况来决定。例如一条UPDATE语句更新了很多的数据,采用Statement会更加节省空间,但是相对的,Row会更加的可靠。
------------------------------MyISAM 和 InnoBD区别:----------------------
MyISAM InnoDB 主键 允许没有任何索引和主键的表存在,myisam的索引都是保存行的地址。 如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)innodb的数据是主索引的一部分,其他索引保存的是主索引的值。 事务处理上方面: MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持、不支持外键 InnoDB提供事务支持事务,外部键(foreign key)等高级数据库功能 DML操作 如果执行大量的SELECT,MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表 2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。 自动增长 myisam引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。 innodb引擎的自动增长必须是索引,如果是组合索引也必须是组合索引的第一列。 count()函数 myisam保存有表的总行数,如果select count(*) from table;会直接取出出该值 innodb没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre 条件后,myisam和innodb处理的方式都一样。 锁 表锁 提供行锁,另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表, 例如update table set num=1 where name like “%aaa%”
总结上表:
- 事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持,但查询的速度要比InnoDB更快
- 主键 InnoDB规定,如果没有设置主键,就自动的生成一个6字节的主键,而MyISAM允许没有任何索引和主键的存在,索引就是行的地址
- 外键 InnoDB支持外键,而MyISAM不支持
- 表锁 InnoDB支持行锁和表锁,而MyISAM只支持表锁
- 全文索引 InnoDB不支持全文索引,但是可以用插件来实现相应的功能,而MyISAM是本身就支持全本索引
- 行数 InnoDB获取行数时,需要扫全表。而MyISAM保存了当前表的总行数,直接读取即可。
MyISAM只适用于查询大于更新的场景,如果你的系统查询的情况占绝大多数(例如报表系统)就可以使用MyISAM来存储,除此之外,都建议使用InnoDB。
还可以去看看为什么MySQL要用B+树
and