目录
索引
explain
索引优化
慢查询日志
锁表
要了解mysql调优，首先要明白一条sql的执行顺序
select * from
table a
left join
table b on a.id = b.id
where a.x = 1
group by a.y
having count(a.z)>0
order by a.id
limit 1,1
对于一条语句，我们在开发时，首先会去看select列表，但是在加载表的过程中，是从table开始的。
真正的加载顺序如下：
- from table a
- join table b
- where 条件
- group by 条件
- having 条件
- select 列表
- order by 条件
- limit 限制
索引
- 简单来讲，索引就是一种数据结构，索引本身也是很大的，不能完全放在内存中，索引也是以文件形式存储的。
- 单值索引：一个索引只包含某一列，一个表可以有多个单值索引
- 复合索引：一个索引覆盖了多个列，复合索引的性能要优于单值索引
- 唯一索引：索引列的值必须是唯一的，但是可以允许出现空值
- 主键索引：在创建主键的时候，主键列会自动生成索引
哪些情况需要创建索引？
- 频繁作为查询条件的列
- 与外表关联的字段
- 需要排序的字段 即order by 后的字段
- 需要分组的字段 即 group by 后的字段 group by 实现原理，也是先要进行排序才可可以进行分组的
哪些情况不需要创建索引？
- 表的数据很少
- 频繁修改的字段，每次修改字段后需要同步修改索引，消耗性能
- 字段的数据大量重复， 比如性别、是否活跃 这种字段不适合创建索引
- 不能用作查询条件的字段也没必要创建索引
对索引进行操作
-- 索引名一般使用 idx_ 作为前缀，约定俗成的一种写法
alter table [tablename] add index [idx_name] on [tablecolumns]
create index index_name on tablename (column)
--查看索引
show index from tablename
--删除索引
drop index[indexname] on tablename
索引建好了，并且使用了索引，并不意味这sql 执行的性能就提高了。
explain
explain 可以帮助我们分析sql语句，找到查询效率低下的原因，包括索引是否失效等问题，从而改进sql语句，提高效率
执行以下语句，可以看到这么多字段。
explain
select * from t_school where grade_id=
(select grade_id from t_grade)
id列，表示的是表加载的顺序
id 相同，执行顺序由上而下，
id 不相同，id大的先执行，
id 既有相同的又有不同的，相同的id可以认为是同一组，由上而下执行。
所以上面的语句先加载 t_grade 再加载 t_school
select_type 列，表示的是表的select类型
| select_type | 说明 |
| simple | 简单的select查询，不包含子查询或者union操作 |
| primary | 标识最外层查询，也可以理解成最后加载的表 |
| subquery | 在select或者where中包含子查询 |
| derived | 在from列表中，包含的子查询被标记成derived（衍生）， |
| union | 第二个select出现在union则被标记成union，若union包含在from子查询中，外层的select将被标记成derived |
| union result | 从union列表获取结果的select |
table就是对应的表明。type 列表示连接类型
| 类型 | 说明 |
| system | 表只有一行记录（约等于系统表），一般不会出现，可以忽略不计 |
| const | 常量，表示通过索引，一次就找到了，用于primary key 或者 union 索引 |
| eq_ref | 唯一性索引扫描，表中只有一条记录与之匹配 |
| ref | 返回匹配某个值的所有行，与eq_ref 的区别是，可以返回多个值 |
| range | 表示检索某一范围内的所有数据，通常是在where中使用了 <、>、in、between 等关键字 |
| index | 出现index表示，使用了索引，遍历了index索引树 |
| all | 表示全表扫描 |
出现以上关键字的sql性能有高到低一次递减。
possible_keys、key、key_len
possible_keys：表示可能用到的索引，实际可能一个也没用到
key：表示实际用到的索引，
key_len：表示索引中用的字节数，在不影响性能的情况下，越短越好。表示的是最大字节长度，是根据表定义的字段类型的长度计算出来的，并非实际长度。
EXPLAIN select * from t_grade where grade_neme='grade1'
`grade_neme` varchar(10) DEFAULT NULL,
grade_neme定义的varchar 10 （DEFAULT NULL 标识非空，所以要再额外占一个字符位置）所以，key_len 长度为（10+1）*3=33 （mysql 5 版本以后，varchar10 代表的是10个字符长度，而在utf8编码中，一个字符占3个字节，GBK编码中，一个字符占2个字节，所以这里是33）
ref 表示哪些列被用于查找了，const表示查找条件是一个常量
如上图，查询条件是一个字符串，所以ref中是const，如下图，ref 是test.b.grade_id, 表示的是用到了test库下面的t_grade（别名是 b）的grade_id 这一列。
EXPLAIN select * from t_school a,t_grade b where a.grade_id= b.grade_id
rows 则是根据表的统计信息以及索引的情况，估算出查找到复合条件的记录需要读取的行数
extra 表示不包含在其他列中的，但是也是十分重要的信息
1. using filesort
出现这个类型说明mysql进行排序的时候，并没有按照表内的索引进行排序，
EXPLAIN select * from t_school a,t_grade b where a.grade_id= b.grade_id order by b.grade_id
其中，表b 的grade_id 没有创建索引，所以出现了filesort，除了没有索引之外，索引失效也会导致出现这种情况。
2. using temporary
通常使用了order by或者sort by 之后，会出现。 表示查询的过程中，使用到了临时表这个比出现filesort更危险。除了没有建立所索引之外，索引失效同样可以引起这个结果。
3. using where
使用了where
4. using index
使用了覆盖索引，如果同时出现了using where 则说明索引是用来查找索引键值的。
覆盖索引：查询的列表完全包含在索引列中。 比如一个复合索引为 id和name列， ide_id_name，那么当select id，name from XX where 的时候，就是走的覆盖索引。
5. using join buffer
使用了缓存连接
6. impossible where
where查询条件的值总是false，不能 获取到任何值，比如 where name ='jack' and name='rose'
7. distinct
使用到了distinct，匹配到第一条记录之后，就不再继续进行匹配。
索引优化
两表建立连接时，左连接，索引应当出现在右表中，右连接的时候，索引应当出现在左表
三个表建立连接的时候，永远使用小表驱动大表，保证join语句中，被驱动的表的join条件字段已经建立索引。
小表驱动大表
for(i=0;i<1000;i++){
for(j=0;j<5;j++){
}
}
类似我们写的嵌套循环，如果将小表放在里面，大表在外，那大表将查询1000次，反过来大表在内的话，大表只需要5次
索引失效的场景：
1. 如果创建了复合索引，那么最左边的字段在使用时不能丢失，否则汇引起索引时效，中间字段不能丢失，否则会导致只有部分字段使用索引
例如创建了复合索引 idx_id_name_age 在使用时，where name=XX and age=XX 就会导致索引时效
2. 不能对索引字段进行计算、使用函数、自动或者手动的进行类型转换，否则会时效。 age 是varchar 类型，第一条语句在查询时，mysql自动做了类型转换
3. 范围查询后的列索引都失效，还包括 ！=
例如创建了复合索引 idx_addr_age_name 在使用时，where addr=XX and age>XX and 就会导致索引时效
4. select 搜索列应当跟索引列保持一致
5. is null ，is not null 也会使索引失效
6. 通配符 % 开头也会导致索引失效，如果非要使用%作为开头的话，可以使用覆盖索引来解决这个问题
为t_user创建一个idx_userCode 索引
explain SELECT * from t_user where userCode like '%191'
explain SELECt userCode from t_user where userCode like '%191'
7. or 连接会使索引失效，可以使用union all 代替
慢查询日志
通过慢查询日志，可以看到哪些sql执行时间过长
--默认关闭的，使用完就关闭可以了，一直开启或多或少影响性能。
show variables like '%slow_quer_log%';
-- 开启码查询日志
set global slow_query_log=1;
--默认触发时间是10秒
show global variables like 'long_query_time%';
-- 修改时间 为2s
set global long_query_time=2;
-- 默认慢查询
select sleep(5);
开启慢查询、设置触发时间之后，一定要新建一个会话窗口进行测试，否则设置不生效，日志无法写入！
查看show variables like '%slow_quer_log%' 返回的路径下的慢查询日志，可以看到这条执行慢的sql被记录，拿出这条sql之后，我们就可以对这条sql进行优化。
/usr/sbin/mysqld, Version: 5.6.45 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200902 22:53:17
# User@Host: root[root] @ localhost [] Id: 6
# Query_time: 6.000484 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1599058397;
select sleep(6);
通过命令也可以看到有几个查询慢的sql show global status like '%Slow_queries%';
锁表
锁分为读锁和写锁
读锁是共享锁，即当前会话锁定了某个表以后，只能读取被锁定的表，无法读其他表，也无法修改表，其他会话也可以读被锁定的表，但是无法修改数据，会一直阻塞，知道当前会话释放锁，其他会话才会修改表。
--手动对表加读锁，只能读，不能修改。
lock table t_school read;
--可以查看哪些表被锁了，0 没锁，1表示锁表了
show open tables;
--解锁
unlock tables；
写锁 加了写锁，当前会话可以对当前的锁表进行读写操作，其他会话不可以。
mysql的存储引擎
MyIsam 是写优先的存储引擎，所以不适合做写为主的引擎，因为写锁之后，会造成大量的阻塞
InnoDB 相比 MyIsam， 提供事务，提供行锁。能更好的处理高并发，所以目前比较流行
--查看当前数据库提供了哪些存储引擎
show engines;
--查看当前数据库使用了什么引擎
show variables like '%storage_engine%';
行锁升级为表锁
设置了索引，索引失效，就会导致锁表
--如何锁定一行
select* from table where id=1 for update
间隙锁
表里有 id 1 3 4 5 6，如果一个会话操作的范围是 id>1 and id<6,另一个会话插入id=2的数据就会被阻塞，这就是间隙锁引起的
对范围进行检索的时候，无论范围内的某个键值存不存在，都会被锁定，这就是间隙锁
