mysql索引

Mysql索引

优缺点

1. 优点
   a. 降低IO成本
   b. 通过创建唯一索引,保证数据库表中每一行数据的唯一性
   c. 加快表与表之间的连接
   d. 减少查询中分组和排序的时间
2. 缺点
   a. 创建和维护需要耗费时间
   b. 索引需要占用磁盘空间
   c. 会降低更新表的速度

基础知识

1. 行记录索引结构
   
   描述: record_type: 表示记录的类型 ,0表示普通记录，2表示最小记录，3表示最大记录，1暂时没用过

2. 页记录索引结构
   

3. 多级索引形成过程图解(以聚簇索引为例,即主键索引)

   • 一级索引
     
   • 二级索引
     
   • 多级索引
     

4. 非聚簇索引(二级索引)
   二级索引和聚簇索引不同点是"二级索引叶子节点存储的是索引列和主键列",需要回表操作查询其他列数据

这种结构就是B+tree

常见的索引结构都不超过4层原因:

前提: 假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存

放 1000条目录项记录

说明: I . 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录;

​ II. 如果B+树有2层，最多能存放 1000×100=10,0000 条记录;

​ III. 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录;

​ IV. 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录(当数据量达到这么大规模时要考虑其他方案来进行表优化了)。

5. 索引类型

I. 按照功能逻辑分类
  主键索引、唯一索引、全文索引、普通索引
II. 按照物理实现方式分类
  聚簇和非聚簇
III. 按照作用字段个数
  单列索引和联合索引

6. 常用索引sql语句

I. 创建索引
a. create index idx_name on student(name); -- 创建索引
b. alter table student add index idx_name(name); -- 修改已创建的索引
c. create table student(...,idx_name(name)); -- 建表时直接创建索引
II. 删除索引
drop index idx_name on  student(name)

Mysql性能分析手段

1. 常用服务器性能查询参数

  I. connections     查询连接次数
 II. uptime          mysql服务器的上线时长
III. slow_queries    慢查询次数
 IV. innnodb_rows_%  curd分别对应总行数
  V. last_query_cost  运行成本

2. 定位慢sql(使用慢查询日志)

默认情况下mysql没有开启慢查询日志,使用set global slow_query_log = on;开启

3. 使用explain性能分析工具

   常见用法: explain + sql脚本

字段类别	字段名	字段解释	备注说明	举例
table	table	表名	每一行记录都应一个单表
id		同一个select语句只会对应一个唯一的id	1. id相同,可以认为是一组,从上到下按顺序执行 2. 所有组中,id值越大,优先级越高,越先执行 注: 每个id表示一趟独立的查询,一个sql的查询次数越小越好
select_type	simple	简单的查询
select_type	union/union result	联合结果集
select_type	dependent subquery	相关子查询
select_type	derived	包含派生表的查询
select_type	MATERIALIZED	当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层进行连接查询
partitions		无分区测试,忽略
type	system	当表中只有一条记录并且使用的存储引擎的统计数据是精确的,比如MYISAM,MEMORY;
type	const	当我们根据主键或者唯一二级索引与常数值进行等值匹配值		explain select * from s1 where key2 = 10003;
type	eq_ref	在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的		explain select * from s1 inner join s2 on s1.id = s2.id;
type	ref	当通过普通的二级索引与常量进行等值匹配查询某个表		explain select * from s1 where key1 = ‘a’;
type	ref_or_null	当对于普通二级索引进行等值查询匹配,该索引的值也可以为null		explain select * from s1 where key1 = ‘a’ or key1 is null;
type	index_merge	or条件使用多个索引		explain select * from s1 where key1 = ‘a’ or key3 = ‘a’;
type	unique_subquery	针对一些包含‘IN’子查询的查询语句中,如果查询优化器决定将’IN’子查询转换为’exists’子查询,而且子查询可以使用到主键进行等值匹配的话		explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = ‘a’;
type	range	使用索引获取范围区间的数据		explain select * from s1 where key1 in (‘a’, ‘b’, ‘c’);
type	index	可以使用索引覆盖(主要理解覆盖索引的概念),但需要扫描全部的索引记录时	要查的部分字段在索引里面
type	all	全表扫描
possible_keys/key		可能使用到的索引;真正使用到的索引
key_len		实际使用的索引长度(即:字节数) 帮忙检查是否充分利用上了索引,值越大越好 主要针对联合索引，有一定的参考意义
ref		当使用索引列等值查询时.与索引值进行等值匹配的对象信息
rows		预估需要读取的记录条数 值越小越好
filtered		某个表经过条件过滤后剩余记录条数的百分比(百分比越高越好)
extra		一些额外的信息 更准确的理解mysql到底将如何执行给定的查询语句	1. no tables used 2. Impossible(where条件永远为false) 3. Using where(使用全表扫描来执行某个表的查询,并且where条件中有关于该表的搜索条件时) 4. no match min/max(有max/min聚合函数,没有匹配列) 5. Using index(查询条件以及where条件只包含索引,不需要回表操作) 6. Using index condition(有些条件虽然出现了索引列,但却不能使用索引) 7. Using join buffer(被驱动表不能有效利用索引加快访问速度,Mysql一般会为其分配一块名为"join buffer"的内存块来加快查询速度) 8. Using where; Not exists(使用左外连接时,where条件包括null值的搜索条件,但那个列不允许存储NULL值的) 9. Using union(idx_key1,idx_key3) 10. Zero limit(limit 0条记录) 11. Using filesort(MYSQL把在内存中/磁盘上排序的方式统称为文件排序(fileSort)) 12. Using temporary(使用临时表)

总结:

sql调优结果值type类型从最好到最坏依次是:
system -> const -> eq_ref(join表操作) -> full_text -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> all
sql性能优化的目标: 至少达到range级别,要求是ref级别，最好是const级别。

其他知识点

1. 覆盖索引

查的字段比联合索引字段少，不用回表操作

2. 索引下推

(先过滤掉一部分数据，减少回表操作)先用上索引条件在二级索引中进行过滤筛选，然后再回表。