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)
避免出现重复的列，可以重复，多个列都可以标识位 唯一索引
人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
-
-
常规索引 (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中
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
