2.MySQL索引创建方法及其优化

索引优化分析

性能下降，SQL慢、执行时间长、等待时间长

• 数据过多——分库分表
• 关联太多的表，太多join——SQL优化
• 没有充分利用到索引 ——索引建立
• 服务器调优及各个参数设置——调整my.cnf

其中优化手段“建立索引”最快捷也最常用。

预热—常见通用的join查询

其中：union关键字在使用时，两个结果的字段相等、字段数相等、字段顺序一致。

union all和union的区别在于去重，union会去重。

select a.*，b.* from t_emp a 
left join t_dept b on a.deptid = b.id
where b.id is null
union
select a.*，b.* from t_dept b 
left join t_emp a on a.deptid = b.id
where a.id is null;
-- 查询a表独有和b表独有的数据

mysql 单表瓶颈500w

索引简介

索引(Index)是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构

可将索引理解为"排好序的快速查找数据结构"。

数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这些数据结构就是索引。

索引的存放位置：一般来说，索引本身也很大，不可能全部存储在内存中，因此，索引往往以索引文件的形式存储在磁盘中。

索引的优势：

• 提高数据检索的效率，降低数据库的IO成本；
• 通过索引对数据进行排序，降低数据排序的成本，减少CPU的消耗。

索引的劣势：

• 在提高查询速率的同时，降低了表更新的速度。因在更新表时，MySQL不仅保存表数据，还需保存索引文件每次添加的索引列字段
• 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也需占用空间。

索引的结构：

B_tree 平衡树

• Btree
  1. 非叶子节点包含
     1. 索引指向的数据
     2. 向下的指针
     3. 指向数据的指针
• B+tree
  • 非叶子节点不包含索引指向的数据

MySQL选择B+tree作为索引，相对有限的内存中，B+tree的占用空间要比Btree小三分之一，相对发生IO的次数更少，时间更短。

时间复杂度

同一问题可用不同的算法解决，而一个算法的质量优劣将影响到算法及程序的执行效率。算法分析的目的在于选择合适的算法和改进算法。

O(n) 时间复杂度 算法随着数量级n的增加在时间维度上的复杂程度。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

“聚簇”表示数据行和相邻的间值聚簇存储在一起。

只有主键索引是聚簇索引，即按照顺序排列好的索引。

除了主键索引，其余建立的索引都是非聚簇索引，特点：非聚簇索引要对所有索引进行查找。

mysql索引分类

单值索引

​ 即一个索引只包含单个列，一个表可以有多个单列索引

​

• 随表一起创建索引

create table 
customer(
    id int(10) unsigned auto_increment,
    customer_no varchar(200),
    customer_name varchar(200),
    primary key(id),
    key(customer_name) 
    );
-- key(customer_name) 随着表的创建，同时创建索引

• 单独创建单值索引
  CREATE INDEX idx_customer_name ON customer(customer_name)
• 删除索引
  DROP INDEX idx_customer_name ON customer;

唯一索引

​ 索引列的值必须必须唯一，但允许有空值
CREATE UNIQUE INDEX [indexName] ON table_name(column)

主键索引

​ 设定为主键后数据库会自动建立索引，Innodb 为聚簇索引

复合索引

​ 即一个索引包含多个列。针对多个字段创建一个索引

​ create index idx_age_deptid_name on t_emp(age,deptid,name)

​ 通过符合索引查询的结果为复合索引内的n条数据，即复合索引包含几条字段就返回几条字段的查询结果。

基本语法

• 创建 create [unique] index [indexName] on table_name(column)

  • 创建单值索引

    给表t_emp表中的name字段创建索引：

    create index idx_name on t_emp(name)

  • 唯一索引的创建

    在mysql中，随着主键的创立会自动生成索引，所以对于表中其他字段数据集唯一的字段可以设置唯一索引

    为员工表中员工编号字段创建唯一索引

    create unique index idx_empno in temp(emp_no)

  • 复合索引

• 删除 drop index [indexName] on mytable;

• 查看 show index from table_name\G

• 使用alter命令（一般情况使用create就足够了）

  -- 四种方式来添加数据表的索引
  
  alter table tbl_name add primary key(column_list);
  -- 该语句添加一个主键，意味着索引值必须唯一且不为空
  
  alter table tbl_name add unique index_name(column_list);
  -- 这条语句创建索引的值必须是唯一的（除了Null外，Null可能会出现多次
  
  alter table tbl_name add index index_name(column_list);
  -- 添加普通索引，索引值可出现多次
  
  alter table tbl_name add fulltext index_name(column_list);
  -- 该语句指定是索引为fulltext，用于全文索引
  

什么情况下需要创建索引

• 主键自动创建唯一索引；；
• 频繁作为查询条件的字段应该创建索引；
• 查询中与其它表关联的字段，外键关系建立索引；
• 单键/组合索引的选择问题，组合索引的性价比更高；
• 查询中排序的字段，排序字段若通过索引去访问将大大提升排序速度；
• 查询中统计或分组字段 ，
  • group by 相较于order by 更伤性能，因为group by中包含order by先排序后分组。

那些情况不许创建索引

• 表记录太少
• 经常增删改查的表或字段
• where条件里用不到的字段不需要创建索引
• 过滤性不好的不适合创建索引

衡量是否因该建索引，怎么建立索引的“尺子”

Explain 就是mysql提供给使用者的“尺子”

一、Explain是什么（查看执行计划）

执行计划：在MySQL逻辑架构中optimizer(优化器)在不改变查询结果的情况下调整sql执行顺序，生成执行计划。

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

二、Explain能干什么？

• 表的读取顺序

• 哪些索引可以使用

• 数据读取操作的操作类型

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被物理查询

三、Explain怎么用

EXPLAIN sql查询语句

Explain sql语句 返回对sql语句的分析情况。

执行计划包含的信息（关键的）：

1. id

   • select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
   • 三种id情况：
     • id号相同：执行顺序由上到下（所显示分析情况表）
     • id不相同：如果是子查询，id的序号会递增，id越大优先级越高，越先被执行；
     • id列中id既有相同又有不同时：先按id不同，再按id相同进行优先级划分
   • 关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好。（相同号码代表一趟查询）

2. type

   显示查询使用了何种类型。

   • 从最好到最差：

     system>const>eq_ref>ref>range>index>ALL

     • all：全表扫描、效率极低（当type字段中出现此关键词，就可对其进行索引创建）
     • index：覆盖索引，出现index 时sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组；（需要进行优化）
     • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在你的where语句中出现了between、<、>、in等的查询语句，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一个点，不会涉及到扫描全部索引