MySQL高级及索引优化（更新中）

MySQL高级

MySQL基础

查看字符集（默认的server和client采用的是Latin，写中文会乱码）

mysql> show variables like '%character%';
mysql> show variables like '%char%';

存储引擎

对比项	myisam	innodb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会所著整个表，不适合高并发操作	行锁，适合高并发操作
缓存	只缓存索引，不缓存真实数据	缓存索引和真实数据，对内存要求高
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

mysql默认是innodb

SQL性能下降原因
性能下降SQL慢（执行时间长，等待时间长）：

• 查询语句写的烂
• 索引失败
• 关联查询太多join（设计缺陷等不得已的要求）
• 服务器调优及各个参数设置（缓冲，线程数等）

join(关联查询)

SQL执行顺序
手写：select distinct->from->on->where->group by->having->order by
机读：from->on->where->group by->having->select distinct->order by

join七种理论

左外连接

mysql> select * from t1 left join t2 on t1.id = t2.id;

右外连接

mysql> select * from t1 right join t2 on t1.id =t2.id;

内连接

mysql> select * from t1 inner join t2 on t1.id = t2.id;

完全外连接

mysql> select * from t1 left join t2 on t1.id = t2.id
	-> union
	-> select * from t1 right join t2 on t1.id = t2.id;

笛卡尔积

mysql> select * from t1 join t2;

左表独有

mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null;

右表独有

mysql> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

并集去交集

mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

索引

什么是索引

索引（Index）是帮助MySQL高效获取数据的数据结构，一般是B+树（多路搜索树）
简单理解为排好序的快速查找数据结构

优势：（B+树的特点）

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

劣势：

• 占用空间
• 提高了查询速度，降低了更新表的速度

索引分类：主键索引，唯一索引，全文索引，普通索引，组合索引

索引分类

MySQL索引结构：BTree索引，Hash索引，RTree索引，B+Tree索引

索引数据结构

为什么不用哈希表？
耗费存储空间
哈希表适合查找某个数据，但是在实践中通常要寻找某个范围

为什么不用二叉树或者红黑树
会因为树的深度过深而导致io次数过多，影响读取效率，时间复杂度高

为什么不用B树
磁盘块固定大小的时候，但是因为数据量过大或者数据过大而导致深度过深，io次数过多

B+树

• B+树每个节点可以包含更多的节点，这么做的原因有两个：第一是为了降低树的高度，第二是为将数据范围变为多个区间，数据检索更快
• 非叶子节点存储key，叶子节点存储key和数据
• 叶子节点两两指针相互连接，顺序查找性能更高

建立索引情况

什么时候要建立索引？

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引○r因为每次更新不单单是更新了记录还会更新索引
5. Where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

什么时候不适合建立索引

1. 表记录太少
2. 经常增删改的表（经常改索引）
3. 数据重复且平均的表字段

索引分析

MySQL常见瓶颈：

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

explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。

功能：

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

explain命令分析

mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

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

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type：查询类型，数据读取操作的操作类型

• simple：简单的select查询，查询中不包含子查询或者union

mysql> select * from student;

• primary：查询中若包含任何复杂的子部分，最外层查询则被标记

mysql> explain select stu from (select * from t1);

• subquery：在select或where列表中包含了子查询

mysql> explain select stu from (select * from t1);

• derived：在form列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

mysql> explain select stu from (select * from t1);

• union：若第二个select出现在unon之后，则被标记为union；若union包含在form子句的子查询中，外层select被标记为derived

mysql> explain select * from t1 a left join t2 b on t1.a=t2.b
    -> union
    -> select * from t1 a right join t2 b on t1.a=t2.b;

• union result：从union表获取的结果的select

mysql> explain select * from t1 a left join t2 b on t1.a=t2.b
    -> union
    -> select * from t1 a right join t2 b on t1.a=t2.b;

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

type：访问类型排列（从好到坏：system->const->eq_ref->ref->range->index->all）

一般来说到达range，最好到达ref

• system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
• const：表示通过索引一次就找到了，const用于比较主键索引（primary）和唯一索引（unique）
• eq_ref：唯一性索引扫描，对于每个索引键，表中只要一条记录与之匹配，常见于主键或唯一索引扫描

mysql> explain select * from t1, t2 where t1.id=t2.id;

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上是一种索引访问，返回所有匹配某个单独行，然而，他可能返回多个符合条件的行，所以他应该属于查找和扫描的混合体

mysq> explain select * from t1 where t1.name='ppap';

• range：只检索给定范围的行，使用一个索引来选择多行，key值显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询

mysql> explain select * from t1 where id between 1 and 5;
mysql> explain select * from t1 where id in (2,4,6);

• index：遍历索引树，而all是遍历数据，索引文件比数据文件小(all从硬盘中读的)

mysql> select id in t1;

• all：遍历全表以找到匹配的行

mysql> select * from t1;

possible_keys：是否使用了索引，一个或多个，存在则列出，但不一定被查询实际使用

key：在多个索引竞争的情况下，MySQL用到了哪个索引

若使用了覆盖索引，则只出现在key表中
覆盖索引：select查询的字段和获得的字段符合

mysql> select t1, t2 from student;

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好；key_len显示的值为索引字段的最大可能长度，并非实际使用长度。即key_len是根据表定义计算而得，而不是通过检索得到。

# key_len = 13
mysql> explain select * from t1 where a = 1;
# key_len = 26
mysql> explain select * from t1 where a = 1 and b = 2;

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

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

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

• Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

mysql> select * from t1 order by t1.id;

• Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by

• Using index：表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率高

• Using where：使用where过滤

• Using join buffer：使用了连接缓存

• impossible where：where子句的值总是false，不能用来获取任何元组

• select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于myisam存储引擎优化的count(*)操作

• 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引失效

查询截取分析

查询优化

小表驱动大表：用小的数据集驱动大的数据集

in：当B数据集小于A数据集时，使用in

mysq> select * from A where id in (select id from B);

exists：当A数据集小于B数据集时，使用exists

mysql> select * from A where exists (select i from B where B.id=A.id);

order by优化

MySQL支持两种方式的排序，filesort和index，index效率高，他指MySQL扫描索引本身完成排序，filesort方式效率较低

慢查询日志

批量插入数据脚本

show profile

全局查询日志

数据库锁理论

读锁

行锁