MySQL
索引结构
Mysql的索引是在存储结构层实现的,不同的存储引擎有不同的结构,主要有
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分存储引擎都支持 |
Hash索引 | 底层数据结构是哈希表实现的,不支持范围查询 |
R-Tree空间索引 | 是MyISAM引擎的一个特殊索引类型,主要用于地理空间 |
Full-text全文索引 | 是通过建立倒排索引,快速匹配文档的方式。类似ES |
二叉树(准备知识)
缺点:顺序插入时,会形成一个链表,查询效率还是很低。大数据下层级深,检索速度慢。
B-Tree(多路平衡查找数)(准备知识)
特点:每个节点(页)的数据都是有序排列的,数据会存储在每个节点(页)中。
缺点:每个页的大小时16K,数据和指针都存在一个页中,导致每个页能存储的数据有限,如果数据大的话层级还是会比较深。
B+Tree
特点:所有数据都存在叶子节点中,非叶子节点用来存key和指针,这样每个页就能最大限度的存储内容,层级在3层时大概能存2000多万数据。大于3000W可能深度就是4了,效率会下降,这时候就要考虑分库分表了。
Hash索引(查询效率高)
Hash索引采用的是hash算法,将键值换算成hash值,映射到对应的槽上,然后存储到hash表中。
如果出现两个相同的hash值(哈希碰撞),通过链表来解决(这里在HashMap中引入红黑树,因为链表长了也会影响效率)
为什么InnoDB存储引擎选择使用B+Tree索引结构
InnoDB中对B+Tree进行了优化,在叶子节点之间增加了双向链表
索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中列值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位数据 | 可以有多个 | |
全文索引 | 查找的文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
InnoDB存储引擎中,根据索引的存储形式,又分为以下两种
分类 | 含义 | 特点 |
---|---|---|
聚集索引(clustered Index) | 将数据存储和索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存放多个 |
- 聚集索引选取规则:
- 如果存在主键,那就主键就是聚集索引
- 如果没有主键,那就选取唯一索引(UNIQUE)为聚集索引
- 如果没有主键,也没有唯一索引,InnoDB会自动生成一个rwoid作为隐藏的聚集索引
语法
-
创建索引
CREATE [UNIQUE|FULLTEXT|不加就是常规索引] INDEX index_name ON table_name(index_col_name,...);
-
查看索引
SHOW INDEX FROM table_name
-
删除索引
DROP INDEX index_name ON table_name;
性能分析工具
SQL执行频率
通过show [session|global] status 命令可以提供服务器状态信息。可以通过以下指令查看当前数据库INSERT、DELETE、UPDATE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE 'COM_______'; # 七个下划线
慢查询日志
慢查询日志记录了所有执行时间超过了指定时间(long_quey_time,单位:秒,默认:10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在my.cnf中配置如下信息:
# 开启MySQL慢查询日志开关
slow_query_log = 1
# 设置慢日志的时间为2S,sql语句执行超过2秒就会被视为慢查询,从而记录到日志
long_query_time = 2;
配置完毕后,重启MySQL服务。慢日志信息路径localhost-slow.log
profile
show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数可以看见当前MySQL是否支持profile操作
SELECT @@have_profiling;
默认情况下profile是关闭的,可以通过set语句在session/grobal级别开启profiling
SET profile = 1;
-
profile常用指令
# 查看每一条sql的耗时基本情况 show profiles; # 查看指定query_id的sql语句各个阶段的耗时情况 show profile for query query_id; # 查看指定query_di的sql语句CPU情况 show profile cpu query query_id;
explain 执行计划
字段 | 含义 | |
---|---|---|
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 。 | |
possible_key | 哪些可能是key | |
key | 实际使用的索引,如果为null,则没有使用索引 | |
key_len | 表示索引中使用的字节数,该值为索引最大可能长度,越短越好 | |
rows | MySQL认为必须要执行的行数,在innodb引擎中,是一个估计值,可能并不总是正确的 | |
filtered | 表示返回结果的行数占需读取行数的百分比,filtered的值越大越好。(比如只查询一行数据,MySQL也只读了一行就是100%) | |
Extra | 另外的 |
索引的使用
最左前缀法则
如果一个索引有多列(联合索引),要遵守最左前缀法则:
-
比如建立一个联合索引(a,b,c)
-
where后用and连接的话最左边的索引a必须要有,后面的bc才有可能走
# 走索引(abc都有与位置无关) select * from table_name where a=1 and b=2 and c=3 # 不走(a必须存在) select * from table_name where b=2 and c=3 # 索引部分失效,只走a索引(中间那个没了,其它两个存在且与位置无关) select * from table_name where a=1 and c=3
-
范围查询(联合索引下)
联合索引中出现范围查询(>,<),范围查询右侧的列索引失效
#索引失效(a不能范围查询,否则无效换>=也不行) select * from table_name where a > 1 and b =2 and c=3 #索引部分失效(只走ab,c失效) select * from table_name where a = 1 and b>2 and c=3
-
索引失效情况
索引列运算
# 失效(phone为索引的前提)
select * from table_name where substring(phone,10,2)
字符串不加引号
# 失效(managerid为字符串类型的数字)
EXPLAIN SELECT * FROM table_name WHERE age = 20 AND managerid = 1 AND job = '项目经理'
模糊查询
# 失效(%不能放在前面)
EXPLAIN select * from table_name like where name like "%张"
or的连接
两侧必须都有索引才会生效
SQL提示
sql提示,是优化数据库的重要手段,简单来说,就是在sql语句中加入一些认为的提示来达到优化操作的目的
use Index(建议MySQL使用这个索引)
select * from table_name use index(index_name)
ignore Index(不适用这个索引)
select * from table_name ingore index(index_name)
force(必须使用这个索引)
select * from table_name force index(index_name)
覆盖索引
select * 且条件为非主键索引时,很容易出现回表查询(在二级索引找不到的数据,只能通过二级索引叶子节点中的主键去聚集索引中找)
# 比如表user有字段a,b,c,d 建立的联合索引a,b,c
select * from user where a = 1,b=2 c=3
-- 那么此时只能通过联合索引去查找到abc,剩下的d需要回表到聚集索引中查找
面试题
- 建立username索引:虽然能提高效率,但是会有回表操作,得查两次
- 建立username,password联合索引:联合索引是二级索引的一种,叶子节点存的就是id所以只需要一次查询
前缀索引
当字段类型为字符串(varchar, text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘lO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
-
语法
create index index_name on table_name(col_name(取前几个))
-
查询流程
-
先通过前缀去二级索引查找获取聚集索引id
-
然后拿着索引id去聚集索引里查找整行数据
-
最后再进行一个完整的对比
-
text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘lO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
-
语法
create index index_name on table_name(col_name(取前几个))
-
查询流程
-
先通过前缀去二级索引查找获取聚集索引id
-
然后拿着索引id去聚集索引里查找整行数据
-
最后再进行一个完整的对比
-