MySQL高级-索引优化分析

MySQL高级-索引优化分析

一.简介

1. 索引是什么

• 索引index 是帮助mysql高效获取数据的数据结构
• 本质:索引就是数据结构
• 面试回答: 排好序的快速查找数据结构

2. 优势

• 提高数据检索的效率,降低数据库的IO成本
• 降低数据排序的成本,降低了cpu的消耗

3. 劣势

• 索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的
• 索引大大提高了查询速度,同时降低了更新表的速度
• 索引只是其中一个因素,数据量大的话

4. 分类

• 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
• 唯一索引:索引列的值必须唯一,但是允许有空值
• 复合索引一个索引包含多个列

5. 结构

• BTree索引:Java主要使用索引
• Hash索引
• full-text全文索引
• R-Tree索引

5. 需要创建索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段,外键关系的应该建立索引
• 频繁更新的字段不适合创建索引
• where 条件中用不到的字段不创建索引
• 高并发下倾向创建组合索引
• 查询中排序的字段,排序字段若是通过索引去访问将大大提高排序速度
• 查询中统计或者分组的字段

6. 不要创建索引的情况

• 表的记录太少
• 经常性增删改查的表
• 数据重复且分布平均的表字段

二.性能分析

1. mysql常见瓶颈

• CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈,top,free,iostat和vmstat来查看系统的性能状态

2. Explain 分析

(1). Explain是什么

• 使用explain关键字可以模拟优化器执行SQL查询语句,从而知道mysql是如何处理SQL语句的,分查询语句或是表结构的性能瓶颈

(2). Explain能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间引用
• 每张表有多少行被优化器查询

(3).explain 执行计划包含的信息

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

(3).explain字段解释-id

select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

三种情况:

• id相同,执行顺序由上至下
• id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
• id相同不同,同时存在

(4).explain字段解释-select_type

查询类型,主要是用于区别普通查询,联合查询,子查询等的复杂查询

类型:

• SIMPLE 简单查询,不包含子查询等复杂
• PRIMARY 包含子查询,最外层查询被标记
• SUBQUERY 在select /where 包含了子查询
• DERIVED 在from列表中包含的子查询被标记为derived (衍生) 临时表
• UNION 若是第二个select出现在union之后,则被标记为union
• UNION RESULT 从union表获取结果的select

(5).explain字段解释-table

显示这一行的数据是关于哪一张表

(6).explain字段解释-type

访问类型排列

类型:

• system
• const
• eq_ref
• ref
• range
• index
• all

(7).explain字段解释-possible_keys

显示可能应用在这张表中的索引,一个或者多个

(8).explain字段解释-key

实际使用的索引

(9).explain字段解释-key_len

标识索引中使用的字节数,可通过该列计算查询中使用的索引长度.在不失精确性的情况下,长度越短越好

(10).explain字段解释-ref

显示索引的哪一列被使用了

(11).explain字段解释-rows

根据表统计信息及索引选用的情况,大致估算出找到所需记录所需要的读取的行数

(12).explain字段解释-Extra

包含不适合其他列中显示但十分重要的额外信息

三.性能优化

日后连载…