索引

索引是一种数据结构，数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

1、优点

  • 提高数据检索的效率，降低IO成本

  • 降低排序成本，降低CPU的消耗

    2、缺点

  • 索引占有空间

  • 降低更新表的速度，因为同时要维护索引
  • 如果MySQL有大数据量的表，需要花费时间优化和创建

索引的分类

1、单值索引：

一个索引值包含一列，一个表可有多个单列索引

2、唯一索引：

索引唯一，允许为空

3、复合索引

一个索引包含多列

基本语法

  • 创建
    • CREATE [UNIQUE] INDEX index_name ON table_name（column_name(length)）
    • ALTER table_name ADD [UNIQUE] INDEX index_name ON (column_name(length))
  • 删除
    • DROP INDEX [index_name] ON table_name
  • 查看
    • SHOW INDEX FROM table_name

      索引结构

      BTree索引、Hash索引、full-text全文索引、R-Tree索引

哪些情况需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该建立索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引：因为每次更新还要更新索引
  • where条件中用不到的字段不用创建索引

性能分析

MySQL常见瓶颈

  • CPU
  • IO
  • 硬件：top、free、iostat、vmstat 查看系统性能

    explain

    image.png：

    1、explain包含的字段如下

    image.png

    2、id ： 反应表的执行顺序

    1. explain
    2. select *
    3. from student s,
    4.    course c,
    5.    teacher t
    6. where s.course_id = c.course_id
    7. and c.teacher_id = t.teacher_id;
    image.png
    上图： 第一种情况id相同，从上往下执行
  1. explain
  2. select *
  3. from student
  4. where course_id  = (select course_id from course);

image.png
上图：id不同，数字越大的越先执行

  1. explain
  2. select *
  3. from student
  4. where course_id = (select c.course_id
  5.                    from course c,
  6.                         teacher t
  7.                    where t.teacher_id = c.teacher_id
  8.                      and t.teacher_name = 'rose');

image.png
上图：id相同为一组，数字越大越先执行，一组内从上至下执行

3、select_type

SIMPLE 简单的select查询，语句中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层被标记为PRIMARY
SUBQUERY 在select 或者 where列表中包含了子查询
DERIVED 在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果方在临时表里
UNION 若第二个select出现在UNION之后，被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT被标记为：DERIVED
UNION RESULT 从UNION表获取结果的select

4、type

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
一般来说级别要达到range级别

常用的type如下：

ALL 扫全表，但是从硬盘中查找
index full index scan，index于ALL的区别是index类型只遍历索引树，通常比ALL小，因为索引文件通常比数据文件小
range 只检索给定范围的行，如between ,<,>,in
ref 非唯一索引扫描，返回匹配某个单独值的所有行
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一扫描索引
const 表示通过索引一次就找到，const用于比较primary key或者unique索引
system 表只有一行记录，等于系统表，这是const类型的特例
null

5、possible_keys

显示可能应用在这边表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则将该索引被列出，但不一定被查询实际使用

6、key

  • 实际使用的索引，如果为NULL，则没有使用索引
  • 查询中若使用了覆盖索引，即要查询的字段符合某个索引的最左匹配原则

    7、key_len

    索引字段的最大可能长度，并非实际使用长度,即key_len是根据表定义计算而得，不是通过表内检索出的

    8、ref

    显示索引的哪一列被使用（test.student.name : test库，student表，name字段），如果是具体条件，可为const

    9、rows

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

    10、extra

    | using filesort | 无法通过索引来排序的操作，叫做文件排序，尽量不要出现 | | —- | —- | | using where | where条件 | | using index | 使用到了索引 | | using temporary | 使用了临时表保存临时数据，常见于order by和group by | | using join buffer | | | impossible where | where 子句的值总是false | | select tables optimized away | 在没有groupby子句情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段就完成优化。 | | distinct | 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作 |