索引
建立索引的对象是条件查询时的条件字段 where cloumnName1 = 1001; 那么就对 cloumnName1 建立索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。 在数据之外,数据库系统还维护这满足特定查找算法的数据结构,这些数据结构以某种引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
Mysql使用了B+Tree的数据结构保存数据。定义哪个字段的索引,哪个索引就会是使用默认的B+Tree数据结构保存该字段的结构
默认是B+Tree
分析
一般的查找会通过全表扫描,从头扫到尾。但是有了索引后,比如索引是使用二叉树管理的,那么根据条件的就可以不用全表扫描。所以总会比全表扫描的好吧
优缺点
优势 | 劣势 |
---|---|
提高数据检索的效率 | 索引列也是要占用空间的 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时却也降低更新表的速度(需要对索引维护),如对表进行INSERT、UPDATE、DELETE时,效率低 |
数据结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree空间索引 | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text全文索引 | 是一种通过建立倒排索引快速匹配文档的方式,类似于Lucene,Solr,ES |
-
为什么不使用二叉树,因为二叉树顺序插入是,会形成一个链表,查询性能大大降低,大数据量情况下,层级较深,检索速度慢
-
红黑树:大数据量情况下,层级较深,检索速度慢
-
B-Tree (多路平衡查找树)
以一棵最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key,5个指针)。新增数据达到节点最大度数数据量时,取中间值向上分裂
B+Tree
以一棵最大度数(max-degree)为4(4阶)的b+tree为例:
- 所有数据都会放在叶子节点
- 叶子节点形成一个单向链表
- 非叶子节点仅仅起到索引的作用,不存放实际数据
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
索引是存放的数据是表建立了索引字段列的值
- 相对于二叉树,层级更少,搜索效率高
- 对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加数的高度,导致性能降低
- 相对于Hash索引,B+Tree支持范围匹配即排序操作
Hash
- Hash索引只能用于对等比较(=,in),不支持范围查询(betwwen, >, <, …)
- 无法利用索引完成排序操作
- 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+Tree索引
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决
存储引擎支持: 在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引(不能为空) | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY KEY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
InnoDB的索引分类
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表没有主键,或没有合适的唯一索引,则InooDB会自动生成一个rowid作为隐藏的聚集索引
聚集索引的叶子节点保存(挂)的是行的数据,而叶子节点本身保存的就是该Id
二级索引
二级索引的叶子节点保存(挂)的是对应的主键值,而叶子节点本身保存的就是该字段索引的字段值
select * from user where name = ‘Arm';
二级索引查询时,会在二级索引的B+Tree结构中查询出对应的值获得主键值,然后拿ID到聚集索引中查找对应ID对应的行数据。这个过程称为回表查询,所以当然回表查询会比直接聚集索引查询慢(直接查找聚集索引查询会快,或直接需要的结果再二级索引中获取得到的话,不用回表查询,效率也会比回表查询好)
思考
InnoDB主键作用的B+Tree高度为多高?
假设:
一行数据大小为1k,一页中可以存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使为bigint,占用字节为8
那么高度为2时
n * 8 + (n + 1) * 6 = 16 * 1024, 算出n约为1170
那么就有1171个节点,而一个节点可以有16行数据,所以 1171 * 16 = 18736 条数据
当高度为3时
1171 * 1171 * 16 = 21939856 条数据
索引语句
-
查看表中的所有索引
SHOW INDEX FROM xxtable; +---------+------------+----------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------+------------+----------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+---------+------------+ | 表名 | 0 | 索引名称 |联合索引的顺序 | 字段名 |排序方式 ASC DESC| 4 |截取部分长度| NULL | | 索引结构 | | | YES | NULL | +---------+------------+----------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+---------+------------+ | account | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | YES | NULL | +---------+------------+----------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+---------+------------+
-
创建普通索引
CREATE [UNIQUE | FULLTEXT] INDEX indexName ON table_name (column_name1, column_name2, ...);
-
修改表中索引
ALTER table tableName ADD INDEX indexName(columnName);
-
创建表时添加索引
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
-
删除索引
DROP INDEX [indexName] ON mytable;
优化
优化一般指的是查询优化,怎么建立索引来优化
SQL执行频率
SHOW [SESSION | GLOBAL] STATUS LIKE 'COM_______'; # 7个_ 模糊查询
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 41 |
| Com_delete | 32 | # 删除的次数
| Com_import | 0 |
| Com_insert | 36 | # 插入的次数
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 416 | # 查询的次数
| Com_signal | 0 |
| Com_update | 31 | # 更新的次数
| Com_xa_end | 0 |
+---------------+-------+
-- 可以根据这个频率的数据来分析以什么操作为主,就可以根据响应的操作来优化
通过这个命令查询到SQL执行频率,按照结果分析来判断是否需要优化
SQL分析
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
查看开关语句
SHOW VARIABLES LIKE 'slow_query_log';
Mysql的慢查询日志默认没有开启,需要在Mysql的配置文件(my.ini)中配置
# 开启Mysql慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
慢查询日志文件 localhost-slow.log,根据记录来对sql进行优化
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前Mysql是否支持profile操作
SELECT @@have_profiling; # 查看是否支持profiling
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling
SELECT @@profiling; # 查询profiling开关
SET profiling = 1; # 设置profiling开 0 关 1 开
SHOW profiles; # 查看每一条SQL的基本耗时情况
+----------+------------+-----------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------+
| id | 耗时 | 执行语句 |
| 1 | 0.00026050 | select * from account |
+----------+------------+-----------------------+
SHOW profile FOR QUERY query_id; # 查看指定query_id的SQL语句各个阶段的耗时情况
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000048 | # 开始执行
| Executing hook on transaction | 0.000003 | # 事务
| starting | 0.000005 | # 开始
| checking permissions | 0.000004 | # 检查权限
| Opening tables | 0.000026 | # 打开表
| init | 0.000004 | # 初始化
| System lock | 0.000006 | # 系统锁
| optimizing | 0.000003 | # 优化操作
| statistics | 0.000010 | # 统计的操作
| preparing | 0.000009 | # 准备操作
| executing | 0.000030 | # 执行语句的操作
| end | 0.000003 | # 结束操作
| query end | 0.000002 |
| waiting for handler commit | 0.000007 | # 提交操作
| closing tables | 0.000005 | # 关闭表
| freeing items | 0.000069 |
| cleaning up | 0.000029 |
+--------------------------------+----------+
SHOW profile cpu FOR QUERY query_id; # 查看指定query_id的SQL语句CPU的使用情况
explain执行计划
EXPLAIN 或者 DESC 命令获取Mysql如何执行 SELECT 语句的信息,包括在SELECT语句执行过程中如何连接和连接的顺序
# 直接在SELECT语句之前加上关键字EXPLAIN或DESC
EXPLAIN SELECT * FROM tablename;
-- 注重 type 和 extra.主要对这两个判断进行优化
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | account | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
结果说明
-
id(执行顺序)
SELECT 查询的序列号,表示查询中执行SELECT子句或者是操作表的顺序(id相同,执行顺序从上到下,id不同,值越大,越先执行)
单表一般看不出来
-
select_type
表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(子查询、即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
-
type(性能指标)
表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
- null: 查询时不包含任何表时才可能出现
- system: 查询系统表时才会出现
- const: 访问时以主键、唯一索引为条件时
- eq_ref、ref: 非唯一主键时、唯一索引
- index: 用了索引,但是会扫描索引树(回表)
- all: 全表扫描,尽量不要出现all
-
possible_key
显示可能应用在这张表上的索引,一个或多个
-
key
实际使用的索引,如果为null,则没有使用索引
-
key_len
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
-
rows
MySQL认为必须要执行查询的行数,在InnoDB引擎表中,是一个估计值,可能并不总是准确的
-
filtered
表示返回结果的行数占需读取行数的百分比,filtered的值越大越好
-
Extra
额外信息
索引使用原则
最左前缀法则(针对联合索引)
如果索引了多列(联合索引),要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,索引将部分失效(后面的字段索引失效)
# 表索引
SHOW INDEX FROM XXTableName;
# 索引列中字段的顺序 AA BB CC # 索引存在多列(联合索引AA_BB_CC)需要按 AA BB CC 的顺序来(全部存在时,可不用按顺序)
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| 表名 | 0 | 索引名称 |联合索引的顺序 | 字段名 | A | 4 | | 索引结构 | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 1 | AA | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 2 | BB | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 3 | CC | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
# 查询
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA' AND BB = 'BB' AND CC = 'CC'; #索引生效
EXPLAIN SELECT * FROM XXTableName WHERE BB = 'BB' AND CC = 'CC' AND AA = 'AA'; #索引生效,跟字段的顺序无关
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA' AND BB = 'BB'; #索引生效
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA'; #索引生效
EXPLAIN SELECT * FROM XXTableName WHERE BB = 'BB' AND CC = 'CC'; #索引不生效-不满足最左前缀法则
EXPLAIN SELECT * FROM XXTableName WHERE CC = 'CC'; #索引不生效-不满足最左前缀法则
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA' AND CC = 'CC'; # 仅AA字段索引生效,CC字段不生效
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的索引失效
# 表索引
SHOW INDEX FROM XXTableName;
# 索引列中字段的顺序 AA BB CC # 索引存在多列(联合索引AA_BB_CC)需要按 AA BB CC 的顺序来(全部存在时,可不用按顺序)
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| 表名 | 0 | 索引名称 |联合索引的顺序 | 字段名 | A | 4 | | 索引结构 | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 1 | AA | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 2 | BB | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
| account | 0 | AA_BB_CC | 3 | CC | A | 4 | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+------+------------+---------+---------------+---------+------------+
# 查询
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA' AND BB > 'BB' AND CC = 'CC'; # CC不走索引查询
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA' AND BB >= 'BB' AND CC = 'CC'; # 可以使用添加 = 来解决
索引列运算
不要在索引列上运算,否则索引将失效,例如函数运算
EXPLAIN SELECT * FROM XXTableName WHERE substring(clo, 1, 2) = 'xx'; # clo字段所带的索引将失效
字符串不加引号
字符串类型使用是,不加引号的话,索引也不生效
EXPLAIN SELECT * FROM XXTableName WHERE AA = 'AA'; # 索引正常
EXPLAIN SELECT * FROM XXTableName WHERE AA = AA; # AA字段所带的索引将失效(没有使用引号)
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引失效
EXPLAIN SELECT * FROM XXTableName WHERE AA LIKE 'XX%'; # 索引生效
EXPLAIN SELECT * FROM XXTableName WHERE AA LIKE '%XX'; # 索引失效
or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。只有两侧都有索引的时候才会生效
数据分布影响
如果Mysql评估使用索引比全表更慢,则不使用索引。比如全表查询,使用了索引查询但不走索引(查询结果占了全表的一半以上 ,就不走索引)
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL 语句中加入一些人为的提示来达到优化操作的目的。
当字段有多个索引时,MySQL会自动取一个索引来使用,但是我们也可以使用下面这些来控制使用哪个索引,不使用哪个索引来查询
-
use index 建议使用哪个索引(使用 use ,mysql还会进行评估)
EXPLAIN SELECT * FROM XXTableName USE INDEX(indexName) WHERE XXX = 'XXX';
-
ignore index 忽略哪个索引
EXPLAIN SELECT * FROM XXTableName IGNORE INDEX(indexName) WHERE XXX = 'XXX';
-
force index 强制使用哪个索引(使用 force , 不让mysql进行选择)
EXPLAIN SELECT * FROM XXTableName FORCE INDEX(indexName) WHERE XXX = 'XXX';
=上面说的是WHERE后面的条件规则=========
=下面说的是SELECT后面的条件规则=========
覆盖索引
查询结果返回尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到,就不用再到聚集索引树中查找了[回表查询]),减少使用SELECT *
# 查询返回的值中全部带有了索引的字段,效率会高,在二级索引表中就能全部找到,不会回表查询
# 但是查询返回的值中有个别没有在索引中有的字段,效率就会变低,因为它需要去聚集索引树中再次查找,形成了回表查询
Extra 提示:
NULL: 可能回表查询
using index condition: 查询使用了索引,但需要回表查询数据
using where; using index: 查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
前缀索引
当字段类型为**字符串(varchar, text等)**时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
语法:
CREATE INDEX idx_xxxindexName ON tableName(字段(n)); # n = 前面几个字符,前缀的长度
前缀的长度(求 n):
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的
SELECT COUNT(DISTINCT substring(字段, 1, n)) / COUNT(*) FROM XXXtableName; # 得出来的结果就是前缀的长度,可以调n来看结果接近1的
单例索引与联合索引
单列索引:即一个索引只包含单个列
联合索引:即一个索引包含多个列
索引的命名规则:idx_tableName_clomName1_clomName2
在业务场景中,如果存在多个查询条件,考虑对于查询字段建立索引时,建议建立联合索引,而非单例索引
多条件联合查询时,Mysql优化器会评估那个字段的索引效率更高,会选择该索引完成本次查询(一条语句只用一个索引)
索引设计原则
- 针对与数据量(上万)较大,且查询比较频繁的表建立索引
- 针对于常作为查询条件(WHERE)、排序(ORDER BY)、分组(GROUP BY)操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
- 如果是字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
- 要控制索引的数量,索引不是多多益善,索引越多,维护索引结果的代价就越大,会影响增删改的效率
- 如果索引列不能存储NULL值,请在建立表示使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询
索引总结
-
索引概述
索引是高效获取数据的数据结构
-
索引结构
B+Tree、Hash
-
索引分类
- 主键索引、唯一索引、常规索引、全文索引
- InnoDB中的:聚集索引(默认主键是聚集索引,如果不存在则选择第一个唯一索引,都不存在,则自动创建row_id作为聚集索引)、二级索引
-
索引语法
CREATE [UNIQUE] INDEX XXIndexName ON XXTableName(XXColm1, XXColm2); SHOW INDEX FROM XXTableName; DROP INDEX XXIndexName ON XXTableName;
-
SQL性能分析
执行频次、慢查询日志、profile、explain
-
索引使用原则
- 联合索引
- 索引失效
- SQL提示
- 覆盖索引
- 前缀索引
- 单列/联合索引
-
索引设计原则