MySQL创建索引并发_索引的创建与Explain的使用

索引是帮助mysql高效获取数据的数据结构，可以简单理解为，已经排好序的用于快速查找的数据结构。

排序和快速查找是关键。

索引会影响到order by排序。

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

索引的优势

通过索引可以降低数据的查询成本，提高查询性能，降低数据库IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的劣势

索引也会占用磁盘空间。

索引虽然提高了查询速度，但是更新表时，不仅要更新数据，也要更新索引表，效率会降低。

分类

单值索引

一个索引只包含单个列，一个表可以由多个单列索引

唯一索引

索引列的值必须唯一，但允许由空值。

复合索引

一个索引包含多个列

基本语法

create [unique] index (indexname) on tablename(columname(length));//创建索引

alter tablename add [unique] index (indexname) on tablename(columname(length));//添加索引

drop index indexname on tablename;//删除索引

show index from tablename\G;//查看索引 \g用来格式化显示的内容

索引结构

B+Tree索引结构

什么情况下需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其他表相关联的字段，外键关系建立索引。

4.频繁更新的字段不适合创建索引

5.where条件里用不到的字段不创建索引

6.单键/组合索引的选择问题，高并发下倾向创建组合索引

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8.查询中统计或者分组字段

什么情况不需要创建索引

1.表记录太少

2.经常增删改的表

3.数据重复且分布比较平均的表字段

性能分析

Mysql Query Optimizer

MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块，它主要的功能就是通过计算分析系统中收集的各种统计信息，为客户端请求的Query 给出他认为最优的执行计划，也就是他认为最优的数据检索方式。

mysql常见瓶颈

CPU,IO,服务器的硬件瓶颈。

explain

explain select * from employees;

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

1 SIMPLE employees null ALL null null null null 107 100.00 null

id:select查询的序列号，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下。

EXPLAIN SELECT

e.*

FROM

employees e,

departments d,

jobs j

WHERE

e.department_id = d.department_id

AND j.job_id = e.job_id;

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

1 SIMPLE j null index PRIMARY PRIMARY 22 null 19 100.00 Using index

1 SIMPLE e null ref dept_id_fk,job_id_fk job_id_fk 23 test.j.job_id 5 100.00 Using where

1 SIMPLE d null eq_ref PRIMARY PRIMARY 4 test.e.department_id 1 100.00 Using index

id相同，表的执行顺序如上，j，e，d。并不是按 from中e，d，j的顺序执行。

id不同：

如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM employees WHERE department_id =( SELECT department_id FROM departments WHERE department_id = 10 );

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

1 PRIMARY employees null ref dept_id_fk dept_id_fk 5 const 1 100.00 Using where

2 SUBQUERY departments null const PRIMARY PRIMARY 4 const 1 100.00 Using index

id相同不同同时存在。

同样id大的先执行，id相同的顺序执行。

explain 可以查看表的执行顺序

select_type

用来区别查询的类型

常见值：

SIMPLE : 简单查询，查询中不包含子查询或者UNION

PRIMARY：查询中若包含子查询，最外层的查询为PRIMARY

SUBQUERY:查询中包含了子查询，内层查询为SUBQUERY

DERIVIED：在FROM后跟的子查询定义被标记为DERIVED，mysql会把这个子查询查询到的结果放在临时表里。DERIVIED2就代表此表是序号为2的查询的衍生子查询。

UNION:若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

UNION:多个表UNION后的结果。

explain select * from employees e left join departments d on d.department_id=e.department_id

union select * from employees e right join departments d on d.department_id=e.department_id;

UNION示例

table

表名

type

ALL:全表扫描

index:和All一样都是读全表，但是index是从索引当中读，all是从硬盘中读。

range:只检索给定范围的行，使用一个索引来选择行。一般是在where使用了between and， in ，>等等的查询。

ref:

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，然而他可能会找到多个符合条件的行。

eq_ref:

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。

const,system:

system表只有一行记录，等于系统表，是const类型的特例，可以忽略不记。

const用于表示通过索引一次就找到了，用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量。

NULL;

EXPLAIN SELECT

e.*

FROM

employees e,

departments d,

jobs j

WHERE

e.department_id = d.department_id

AND j.job_id = e.job_id;

示例

对于表d的查询来说 department_id是其主键，每个主键在表中只有一条记录与之匹配。

而对于表e来说，department_id不是主键，因此可能会查询出多个行有相同的department_id值。

对于表j来说，不知道为什么不是eq_ref而是index，可能跟下图mysql的选择有关。

EXPLAIN SELECT * FROM employees

WHERE department_id =( SELECT department_id FROM departments WHERE department_id = 10 );

示例

对于表d来说， department_id=10，已经是一个固定值，因此只需要查一次，返回一个数据。而eq_ref则是查多次，因为department_id的值不固定，每次都返回一个数据。

system>const>eq_res>ref>range>index>ALL;

possible_key

可能会涉及到的索引，但是不一定会被实际使用。

key

查询用到的索引，为null则有可能是索引失效，或者是本就没有索引。

key_len

表示查询中索引的字节数。长度越短越好。

key_len显示的值并不是索引得到实际使用长度，是根据表定义得到的，而不是表内的实际值检索得到的。

ref

哪一列使用了这个索引。

示例

由于表j先被加载，因此没有其他列使用j的索引，所以其j.job_id只能全盘扫面出值,其次是e表，

j.job_id = e.job_id; e表的job_id索引被j表使用。同理d表的主键索引也被e表使用。

rows

根据表统计信息和索引选用情况，大致推算出找到所需的记录需要读取的行数。越小越好。

Extra

额外信息

或者Using filesort，distinct等

加索引注意的点

左右连接索引加在从表中。

where中出现了>或

join语句的优化 //TODO

尽可能减少Join语句的NestedLoop循环次数，用小的结果集驱动大的结果集。

https://www.cnblogs.com/xqzt/p/4469673.html

优先优化NestedLoop的内层循环

保证Join语句中被驱动表上Join条件字段已经被索引。

最佳左前缀法则

如果索引为复合索引，要遵循最左前缀法则。也就是查询从索引的最左列开始并且不跳过索引中的列。

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

//索引加到了name，age，pos三列上，查询时如果不查name只查其余的两列或者一列，索引就会失效。

//而只要查询到了name，无论是只查name一列还是带上其他两列，索引都会被使用。

//但是并不一定是使用了全部的索引，也就是如果只查了name和Pos，Pos的索引也不会使用。

//要使用全部的索引，就必须查到所有的字段。带头大哥和中间兄弟都不能断。

不在索引列上做任何操作(操作，函数，类型转换)，否则失效

EXPLAIN select * from staffs where name='July';

EXPLAIN select * from staffs where left(name,4)='July';//索引失效

存储引擎不能使用索引中范围条件右边的列，范围右边全失效

尽量只访问索引的查询(索引列和查询列一致)，减少select*

使用！=会导致索引失效，8.0版本不会

is null,is not null无法使用索引

LIKE

//如果 索引中只包含name一列

EXPLAIN select * from staffs where name Like 'July%';//百分号在右边不会失效

EXPLAIN select * from staffs where name Like '%July%';//失效

EXPLAIN select * from staffs where name Like '%July';//失效

//如果索引是复合索引且name是复合索引的第一个字段

//那么只要select的列的范围小于符合索引列的范围或者select的列为主键，%在前面也不会影响索引。

字符串不加单引号索引会失效(可能会发生类型转换)

少用or，用or连接会导致索引失效

索引与Order By

Order by子句要尽量使用 Index方式进行排序，避免使用FileSort方式排序。

尽可能在索引列上完成排序操作，遵照索引最佳左前缀规则。

FileSort排序有两种算法：

双路排序：MySQL 4.1之前使用双路排序，扫描两次磁盘。

单路排序：扫描一次磁盘，总体而言好过双路。但是也有小问题。

提高Order By的速度：

使用Order By的时候尽量只查询需要的字段，不要用select*。

尝试提高sort_buffer_size和max_length_for_sort_data