共计 5639 个字符,预计需要花费 15 分钟才能阅读完成。
7、索引
概念:索引 index 是帮忙 MYSQL 高效获取数据的数据结构。索引是数据结构(树) MYSQL 里的索引是 B + 树
索引相似于书的目录
SQL 优化的起因:性能低,执行工夫长,等待时间长,sql 语句欠佳(连贯查问)、索引生效、服务器参数设置不周
SQL 编写过程:
select .. from .. join ... on ..where ..group by ... having...order by...limit..
解析过程
先解析 from.... on .. join...where ..group by ...having ..select ..order by limit
索引的底层原理 B + 树
三层 B + 树,一个关键字对应一个指针,对应一个指数
B+ 树的数据全副寄存在叶节点中,
B+ 树中查问任意的数据次数:n 次(B+ 树的高度)
7.1、索引分类
-
单列索引
- 主键索引(PRIMARY KEY)
惟一的标识,主键不可反复
- 惟一索引 (UNIQUE KEY/index)
避免出现反复的列,能够反复,多个列都能够标识位 惟一索引
人们创立惟一索引的目标往往不是为了进步访问速度,而只是为了 == 防止数据呈现反复 ==。
- 主键索引(PRIMARY KEY)
-
惯例索引 (KEY/INDEX)
- 默认的,index。key 关键字来设置
-
全文索引 (FullText)
- 疾速定位数据
-
复合索引
- 多个列形成的索引(相当于二级目录:z :zhao) 比方 先找 name 列 再找 age,(name,age)不肯定要都查问,只有反复的状况下才须要,复合索引能够有多个列
创立索引
- CREATE 索引类型 索引名 on 表名(字段)
--- 创立复合索引
-----【形式一】create index dept_index on tb(dept,name)
--tb(dept,name) 会主动生成合乎索引
----【形式二】alter table 表名 索引类型 索引名(字段)
alter table tablename add unique index_name(name)
删除索引
drop index 索引名 on 表名
show index from 表名 \G
7.2、索引应用准则
索引的弊病
- 索引不是越多越好,不要对常常变动的数据加索引
- 小数据量的数据不要加索引
- 很少应用的字段也不倡议加索引
- 索引尽管能够进步查问的效率,然而会升高增删改的效率
索引的长处
- 进步查问效率(升高 IO 使用率)
- 升高 CPU 使用率
7.3、SQL 性能问题
- 剖析 SQL 的执行打算:explain,能够模仿 SQL 优化器执行 SQL 语句
- MySQL 查问优化其会烦扰咱们的优化【mysql 有一个查问优化器】
查问执行打算
explain + sql 语句
mysql> explain select * from linelock \G
*************************** 1. row ***************************
id: 编号
select_type: 查问类型
table: 表名
partitions: NULL
type: 类型
possible_keys: 预测用到的索引,可能用到索引的列都会列出来
key: 理论应用的索引
key_len: 用于解决查问的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查问不 肯定都能应用到所有的列,具体应用到了多少个列的索引,这里就会计算进去,没有应用到的列,这里 不会计算进去
ref: 表之间的援用
rows: 执行打算中估算的扫描行数,不是准确值
filtered: 100.00 这个字段示意存储引擎返回的数据在 server 层过滤后,剩下多少满足查问的记录数量的比 例,留神是百分比,不是具体记录数。Extra: 额定的信息
1 row in set, 1 warning (0.00 sec)
----type:索引类型、类型
---system>const>eq_ref>ref>range>index>all,
--- 对 type 进行优化的前提:有索引
----system、const 只是现实状况,理论能达到 ref>range
ref: 非位移性索引,对于每个索引建的查问,返回匹配的所有行(0, 多)
range:检索指定范畴的行,where 前面是一个范畴查问(between , > ,< =)
index: 查问全副索引中的数据
all: 查问全副表中的数据
system/const:后果只有一条数据
eq_ref: 后果多条;然而每条数据是惟一的;ref:后果多条;然而每条数据是 0 或多条
SQL
create table course (-> cid int(3),
-> cname varchar(20),
-> tid int(3)
-> );
create table teacher(-> tid int(3),
-> tname varchar(20),
-> tcid int(3)
-> );
create table teacherCard(-> tcid int(3),
-> tcdesc varchar(200)
-> );
--- 数据插入
insert into course values(1,'java',1),(2,'html',1),(3,'sql',2),(4,'jin',3);
insert into teacher values(1,'tz',1),(2,'tw',2),(3,'tl',3);
insert into teacherCard values(1,'tzdesc'),(2,'twdesc'),(3,'tldesc');
explain select t.* from teacher t,course c,teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);
------id 值雷同的状况下
---- 数据小的表优先计算,联立表查问时
----id 值不同的时候
----id 值越大越优先查问
select_type:
PRIMARY: 蕴含子查问 SQL 中的 主查问(最外层)SUBQUERY: 蕴含子查问 SQL 中的子查问(非最外层)
simple:简略查问(不蕴含子查问、union)derived:衍生查问(应用到了长期表)------a、在 from 子查问中只有一张表
------b、在 from 子查问中,如果有 table1 union table2,则 table1 就是 derived
---ref:留神与 type 中的 ref 值辨别
-- 作用:出名以后表所参照的字段
--- 在 utf- 8 中一个【字符】占【三个字节】---- 如果索引字段能够为 Nul,则会应用一个字节用于标识
{{
----etra 字段
----【using filesort】:性能耗费大;须要额定的一次排序(查问),个别呈现在 order by 中
--- 对于单索引,如果排序和查找的是同一个字段,则不会呈现 using filesort;如果不一样则会呈现
--- 对于合乎索引:不能跨列(最佳左前缀)-----【using temporary】:性能损耗较大,用到了长期表,个别呈现在 group by 中
---- 解决办法:查什么用什么分组
---【using index】:性能晋升,索引笼罩
--- 示意:不读取源文件,只从索引字段开始查问,即不须要回表查问
--- 只有应用到的列,全副在索引中,那么就会有 using index
---using index 会对 possible_key 和 key 造成影响
--1、如果没有 where,则索引只呈现在 key 中
--2、如果有 where,则索引呈现在 key 和 possible_keys 中
---【using where】(须要会表查问)-- 假如 age 是索引列,name 不是
-- 但查问语句中有 select age,name from。。where age=。。此语句必须回原表查 name
---【impossible where】:where 子句永远为 false
}}
7.4、优化案例
单表优化、两表优化、多表优化
7.4.1、单表优化
create table book(bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);
insert into book values(1,'java',1,1,2),(2,'fc',2,1,2),(3,'gf',3,2,1),(4,'magh',4,2,3);
1、问题:查问 authorid= 1 且 typeid 为 2 或 3 的 bid
select bid from book where typeid in(2,3) and authorid =1 ;
explain select bid from book where typeid in(2,3) and authorid =1 ;
优化:加索引
alter table book add index idx_bta(bid,typeid,authorid);
依据 sql 理论解析的程序,调整索引的程序
一旦进行索引降级优化,先前的索引应该删除,避免烦扰
drop index idx_bta on book
索引降级
alter table book add index idx_tab(typeid,authorid,bid);
--- 尽管能够回表查问 bid,然而能够将 bid 放在索引中,进步查找效率
索引再次优化
------ 思路:因为范畴查问 in 有时会实现,因而替换索引的程序,将 typeid
alter index idx_tab on book;
alter table book add index idx_atb(authorid,typeid,bid);
explain select bid from book where authorid =1 and typeid in(2,3) order by typeid desc;
小结:
- 索引不能跨列使(最佳左前缀),放弃索引的定义和应用的程序一致性
- 索引须要逐渐优化
- 将含 in 的范畴查问放到最初,避免索引生效
- using where 须要回原表查问,using index 不须要回原表查问
7.5、防止索引生效的准则
- 复合索引,不要跨列或者无序应用(最佳左前缀)
-
尽量应用全索引匹配
- 建了 (a,b,c) 三个索引,那么查问的时候尽量全用上
-
不要在索引上进行任何操作(计算、函数、类型转换),否则索引生效
- 例如:select。。。where A.x =。。。;假如 A.x 是索引,那么不要进行
- select。。。where A.x*3 =。。。会索引生效
- 对于复合索引(a,b,c) a 生效了,b,c 均生效
- 合乎索引不能应用不等于 (!= <>) 或 is null (is not null),否则本身以及右侧所有全副生效
SQL优化是一种概率层面的优化。至于是否理论应用了咱们的优化,须要通过 explain 进行揣测
体验概率状况:起因是服务层两头有一层sql 优化器,可能会影响咱们的优化
-
like 尽量以“常量”结尾,不要以’%‘结尾,否则索引生效
select * from use where name ='%x%';---name 索引生效 -- 如果必须应用 '%x%' 进行含糊查问 -- 则应用索引笼罩,能够解救一部分 select tname from ta where tname like '%x%';
-
尽量不要应用类型转换(显示、隐式),不然会索引生效
- 尽量不要应用 or,否则索引生效
7.5.2、一些其余的优化办法(刨除索引)
- exist 和 in
如果主查问的数据集大,则应用 In
如果子查问的数据集大,则应用 exist
exist 语法:
select tname from teacher where exist(sleect * from teacher)
--- 将主查问的后果,放到子查问中进行条件测验【是否有数据】,如果合乎校验,则保留数据
-
order by 优化
- using filesort 有两种算法:双路排序、单路排序(依据 IO 的次数)
- MySQL4.1 之前默认应用双路排序;双路:扫描两次磁盘(1:从磁盘读取排序字段,进行排序(buffer 中执行)2:扫描其余字段)
- MySQL4.1 之后默认应用单路排序:只读取一次全副的字段,在 buffer 中进行排序。然而单路排序会有肯定的隐患(不肯定的真的是一次。)如果数据很大,能够思考调大 buffer 容量的大小:set max_length_for_sort_data
- 如果 max_length_for_sort_data 太低,mysql 会主动切换到双路。
- 防止应用 select * …..
7.6、SQL 排序 - 慢查问
mysql 提供的一种日志记录,用于记录 MySQL 中相应工夫超过阈值的 SQL 语句(long_query_time, 默认 10s)
慢查问日志默认敞开,开发调优是能够开启,如果最终部署时候要进行敞开。
查看是否开启了慢查问日志
show variables like '%slow_query_log%';
开启慢查问日志
-
长期开启
set global slow_query_log =1; ---- 在内存中开启
- 永恒开启
/etc/my.cnf 中追加
- 慢查问阈值:
show variables like '%long_query_time%'
-
长期设置阈值:
set globale long_query_time=5 --- 要重新启动 mysql 失效
-
查问超过阈值的 SQL:
show global status like '%slow_queries%'; -- 慢查问的 sql 被记录在了日志中,能够通过日志查看具体的慢 SQL -- 也能够通过 mysql 工具 查看【mysqldumpslow】-- 通过 mysqldumpslow --help 来查看
-
慢 sql 工具查看
-- 获取返回记录组最多的 3 个 SQL mysqldumpslow -s r -t 3 日志文件的门路
7.7、全局日志查问
全局查问日志:记录开启后的全副 sql 语句
show variables like '%general_log';
set global general_log=1; -- 开启全局日志
set global log_output='table';
-- 开启全局日志后,所有的记录都会被存储在 mysql.general_log 中