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方式效率较低

order by满足两种情况，会使用index排序

• order by语句使用索引最左前列
• 使用where子句和order by子句条件列组合满足索引最左前列

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

group by：和order by一致

• group by实质是先排序后分组，遵照索引建的最佳左前缀
• where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

MySQL慢查询日志是MySQL提供的一种日志记录，他是来记录在MySQL中相应时间超过阈值的语句，具体指的是运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中（long_query_time默认是10s）

mysql> show variables '%slow_query_log%';

慢查询日志默认关闭

开启

mysql> set global slow_query_log = 1;

批量插入数据脚本

SQL编程文件，不做概述

show profile

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15此的运行结果

mysql> show variables like 'profiling';

打开服务

mysql> set profiling=on;

诊断SQL

mysql> show profile cpu, block io for query 上一步前面的问题的SQL数字号码

全局查询日志

不要在生成环境下使用该功能

# 开启
mysql> set global general_log=1;
# 建表
mysql> set global log_output='TABLE';
# 查看表
mysql> select * from mysql.general_log;

数据库锁理论

在数据库中，除最传统的计算资源（如CPU、RAM、I/O等）的争用之外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤为重要，也更加复杂。

表锁

表锁：偏向myisam引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低（偏读）

创建表锁

mysql> create table t1 {
	-> ...
	->	}engine myisam;

释放表锁

mysql> unlock tables;

查看表上加过的锁

mysql> show open tables;

手动增加表锁（在一个线程对资源加锁后该线程只能在解锁后才能对其他资源进行操作）

# 读锁，读锁可共享
mysql> lock table t1 read;
# 写锁
mysql> lock table t1 write;

综上，读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞

行锁

行锁：偏向innodb存储引擎，开销大，加锁慢，会出现死锁，锁定力度最小，发生锁的冲突的概率最小，并发度也最高

innodb和myisam区别是
支持事务、采用了行级锁

如何锁定一行？

mysql> begin;
mysql> select * from test where id=2 for update;
mysql> commit;

悲观锁和乐观锁

悲观锁

当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制【Pessimistic Concurrency Control，缩写“PCC”，又名“悲观锁”】

之所以叫做悲观锁，是因为这是一种对数据的修改持有悲观态度的并发控制方式。总是假设最坏的情况，每次读取数据的时候都默认其他线程会更改数据，因此需要进行加锁操作，当其他线程想要访问数据时，都需要阻塞挂起。悲观锁的实现：

传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁

悲观锁主要分为共享锁（读锁）和排他锁（写锁）

乐观锁

乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

具体实现：

• CAS实现
• 版本号控制

如何选择：
在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。

• 响应效率：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁。乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
• 冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率。冲突频率大，选择乐观锁会需要多次重试才能成功，代价比较大。
• 重试代价：如果重试代价大，建议采用悲观锁。悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
• 乐观锁如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户从新操作。悲观锁则会等待前一个更新完成。这也是区别。

随着互联网三高架构(高并发、高性能、高可用)的提出，悲观锁已经越来越少的被应用到生产环境中了，尤其是并发量比较大的业务场景。

主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。您看，像在mysql数据库中，支持单项、异步赋值。在赋值过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新。

复制的基本原理：slave会从master读取bin-log来进行数据同步

• master将改变记录到二进制日志bin-log中，这些记录过程叫做二进制日志事件， binary log events
• slave将master的binary log events拷贝到他的中继日志（relay log）
• slave重做中继日志中的事件，将改变应用到自己的数据库中，MySQL复制是异步的且串行化的

复制的基本原则：

• 每个slave只能有一个master
• 每个slave只能有一个唯一的服务器id
• 每个master可以有多个slave

复制的最大问题：延时

主从复制的作用

• 做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
• 架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
• 读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。