1、Order By 是怎么工作的
MySQL做排序是一个成本比较高的操作。MySQL会为每个线程分配一个 sort_buffer 内存用于排序，该内存大小为 sort_buffer_size。
全字段排序
排序流程：
1）、初始化 sort_buffer，确定放入需要查询的字段，例如查询年龄age、姓名name等；
2）、从 where 条件中的查询条件的索引例如age = 10 这个条件，age作为一个索引，从索引树拿到符合 age = 10 的主键id（二级索引存的是主键id，主键索引才是存的数据行）；
3）、到主键 id 索引取出需要查询的字段的值，例如age、name等，然后放入 sort_buffer中；
4）、从索引 age 中拿取下一个符合条件的主键 id ，重复3 4步骤直到没有符合条件的数据；
5）、对 sort_buffer 中的数据按照 order by 条件的字段进行排序，例如 name；
6）、取结果的分页的条数行返回给客户端；
最后排序的动作是取决于所需的内存和参数 sort_buffer_size。
如果排序所需的内存，小于 sort_buffer_size，排序将会在内存中完成；
如果排序所需的内存，大于 sort_buffer_size，排序会临时分配磁盘的临时文件来存放排序后的数据，外部排序一般使用归并算法。如果 sort_buffer_size 太小，生成的临时文件越多（此操作和磁盘做了交互），然后所有的临时文件合并成一个有序的大文件，将所有排序好的数据聚合。
rowid 排序
如果排序的字段太多，那么 sort_buffer 里面要放的字段就很多，能放的行数就很少，排序效果就很差。
max_length_for_sort_data 是 MySQL 专门控制用于排序的单行数据的总长度的一个参数，设置小一点的时候，单行数据总长度大于该参数值，就会走 rowid 排序。
当行的总长度被限制后，就只剩下索引字段和主键字段。主要步骤和全字段排序差不多，最后多了个步骤：
拿出来的例如50行，根据主键 id 回表去取出要查询的字段例如 age、name等给客户端。
意思先根据主键 id 去sort_buffer去排好序，然后再回表拿数据，再返回给客户端。
如果内存够，就要多利用内存，尽量少和磁盘做交互。所以当内存足够大的时候，还是选择全字段排序。对于InnoDB 表来说，rowid 排序会回表造成磁盘读（随机IO，大量的随机读）。如果只是内存表不是InnoDB表，回表只是根据位置直接去内存里面拿数据，不会过多的回表。
如果从 where 条件的索引上取出来的行是已经按照 order by 字段的字段排好序的，就不用排序了。可以建立联合索引来避免排序所带来的性能损耗（联合索引就是已经排好序的，只需要根据二级索引查主键 id，然后拿出需要查询的字段的值，然后取N条数据就可以，该数据已排好序）。
还可以做覆盖索引，将要查询的字段，全部设置为联合索引，这样不用再去查询主键 id 且回表。
但是索引的选择也要慎重考虑，建立联合索引、覆盖索引都要在业务与优化之间权衡。
扩展知识点
SQL查询语句：需要哪些字段就查哪些字段，不需要的不查询。 因为如果涉及到索引与排序的话，越多的字段，排序越慢。
where 条件可以和 order by 条件，连起来做联合索引。但是要考虑并不是索引越多越好。
设计字段长度，例如varchar（255），在255范围内任意大小都可以，因为内存是按实际大小来分配内存空间的，不是按预设值。但是也要注意255这个临界值，因为小于255需要1个字节记录长度，大于255需要2个字节记录长度。
有一个联合索引（name,age），如果查询条件是 where name in ("XX"，"OO") order by age limit 100，对于单个name内部的age是递增的。但是这个 name 条件是针对两个姓名，满足条件的 name 就不是有序递增的了。可以分开查询，然后用归并排序。
like 语句不排序(除了左前缀索引原则)，原因就是上一条扩展知识点。
如果查询等 sql 语句的条件字段没有加索引，就会走全表扫描（默认走主键索引/可能走其他索引的全索引扫描，挨着一条数据一条数据的匹配）。
explain sql 语句的字段Extra，Using index 是覆盖索引，Using index condition 是索引下推，Using filesort 就是需要排序。
2、如何正确随机显示消息
对于InnoDB表来说，执行全自动排序会减少磁盘访问，因为会被优先选择。
对于内存表，回表过程只是简单的根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，优化器会考虑用于排序的行越少越好，所以MySQL会选择 rowid 排序。
MySQL的表是用什么方法来定位"一行数据"：如果MySQL表有主键且唯一，定位就是根据这个主键 id 来定位，如果MySQL表没有主键，那么InnoDB引擎为生成一个长度为6字节的 rowid 作为主键。
order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
tmp_table_size 这个配置限制了内存临时表的大小，默认值 16M，这个临时表大小如果被超过，那么内存临时表就会转成磁盘临时表。
优先队列排序算法：对N个需要排序的行，先去需要的M行，构造成一个数组类型。然后取下一行，和数组比较。该条件值B大于数组的该值A，就去掉A那一行，保存该值B的那一行。重复直接取M个行。
limit 1000等，如果需要维护的1000行的数据值超过了设置的 sort_buffer_size 大小，就只能用归并排序算法。
尽量将业务逻辑写在业务代码中，让数据库只做"读写数据"的事情。
扩展知识点
MySQL的代码和业务代码要配合起来使用，底层的实现和业务功能平衡。
3、SQL语句逻辑相同，性能差异巨大的原因
如果对字段做了函数计算，可能会破坏索引值的有序性，因此优化器就决定放弃走树功能，用不上索引的快速定位功能，还是走了索引查询，但是会走全索引扫描（一行数据一行数据的扫描）。
数据类型转换就需要走全索引扫描（例如数据库是 varchar 类型，sql 语句传入的是int类型）。因为字符串转数字涉及到函数计算，所以走全索引扫描。
如果两个表的字符集不相同，两个表都有关键字段xxxId，根据这个 xxxId 查询两个表数据时，可能从一个表的 xxxId 字段值去查询另一个表有 xxxId 值的数据时，没有用上索引树。因为当两个不同字符集的字符串做比较时，MySQL的内部操作是先转换类型。
索引字段不能进行函数操作，索引字段的参数可以进行函数操作（不影响走索引树搜索）。
扩展知识点
关联查询时，用小表驱动大表（驱动是先查询，被驱动是次查询），每次在树搜索里面做一次查询都是 log(n)，对比的是100*log(10000)和10000*log(100)，所以肯定是小表驱动大表。
in or 条件不走索引树扫描。
MySQL中，字符串和数字做比较的话，是将字符串转换成数字。（where 字段=值，字段还是值是数字，字段是数字就不用做函数计算，可以用字段索引树搜索。值是数字，字段就要做函数计算，就不能走字段索引树搜索）
4、为什么只查1行的语句，也执行这么慢
查询长时间无返回
1）、表被锁住
select * from t where id = 1; 查询结果长时间不返回，很可能是表t被锁住了。
state 显示 waiting for table metadata lock 元数据锁锁住了表t，表示另外有一个线程正在表t上请求或者持有MDL写锁，把 select 语句阻塞。
通过查询 sys.schema_table_lock_waits 这张表，就可以直接查询出阻塞的process id，然后用kill命令杀掉线程。
2）、等 flush
一般MySQL对表做 flush 操作是 flush tables t with read lock； 或者 flush tables with read lock。但是这两个语句执行起来是很快的，除非它们被别的线程堵住了。所以 select 语句也被阻塞了。
3）、等行锁
另一个 sql 语句对某行数据持有写锁，select 语句持有读锁，会被阻塞。
查询慢
1）、where 条件字段上没有索引，所以走主键顺序扫描，扫描全表的数据行；
2）、如果查询的是一个修改了很多次的一行数据，该数据的 undo log 超级多，查询的话就会追溯到最老的 read view，所以查询时间会长一些。如果加 lock in share mode ，表示当前读，就会读最新的视图，速度就会很快。
扩展知识点
show processlist 查看当前sql语句处于什么状态，lock table user write；加表级写锁、unlock tables 释放表锁。
没有用到索引的 sql 语句加锁，也会给所有行加锁，不会加表锁。
RR隔离级别下，为保证 binlog 记录顺序，非索引更新会锁住全表的所有行，且事务结束前不会对不符合条件记录有逐步释放的过程。（最后事务结束才释放）
RC隔离级别下，对非索引字段操作，锁住表所有行，不符合条件的会及时释放行锁，不必等事务结束时释放。而直接用索引字段做条件更新，只会锁住索引查找到的行。锁住的行在 commit 的时候释放。
5、幻读
select * from t where c = 5 for update，查所有 d = 5 的行，而且使用的是当前读，并且加上写锁。
幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。（RR隔离级别下，幻读在"当前读"下才会出现。幻读仅指"新插入的行"）
行锁只能锁住行，但是新插入记录的动作，要更新的是记录之间的"间隙"，可以实现，所以产生了幻读。
如何解决幻读：InnoDB引擎引入间隙锁gap（Gap Lock），例如表T初始化有5条数据，就有6个间隙，所以有5个行锁，6个间隙锁。（不仅给行加锁，还给行的两边空隙也加锁）gap 不止二级索引有，主键索引也会存在。
间隙锁和行锁合称 next-key lock，前开后闭原则，例如（10,20]，（20,30]...
间隙锁如果已经加锁，插入该间隙的时候，线程就会阻塞。
间隙锁带来的问题是，同样的 sql 语句锁住了更大的范围，其实是影响了并发度的。
6、加锁规则
间隙锁在RR隔离级别下才有效。
1）、加锁的基本单位是 next-key lock （行锁 + 间隙锁），前开后闭区间。
2）、查找过程中访问到的对象才会加锁（where 条件使用索引A，只会给索引A加锁，索引B不加锁）。
3）、索引上的等值查询（等值查询，id = 1 这种条件），给唯一索引（条件是唯一索引，例如主键索引，条件是 id = 1 这种情况）加锁的时候，next-key lock 退化为行锁。
4）、索引上的等值查询，向右遍历且最后一个值不满足条件的时候，next-key lock 退化为间隙锁。
5）、唯一索引上的范围查询会访问到不满足条件的第一个值为止。（该值是数据库的数据）
注：主键索引也是唯一索引（每个主键唯一）
lock in share mode 只锁覆盖索引（覆盖索引优化，只锁条件的那个索引，不锁主键索引），但是如果查询结果多了个其他字段例如 city 不属于联合索引，那就会锁行。for update 系统认为接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
锁是加在索引上的（InnoDB是索引树）。如果不需要访问主键索引，主键索引就不会加任何锁（例如使用了覆盖索引，查询的主键 id 和条件 name 是联合索引，就不需要访问主键索引）。
删除数据的时候尽量加 limit，可以控制删除数据的条数，让操作更安全，也可以减少加锁的范围。因为有 limit，查询到不符合条件就结束查询，加锁范围就是符合条件的左开右闭区间。 不加 limit，就得符合第四条，查询到不符合条件的，退化为间隙锁，扩大了加锁范围。
7、短期、临时性MySQL提高性能的办法
集成MySQL的时候要配置一个参数 max_connections ，服务与MySQL建立的最大连接数。
max_connections 数量的计算是按连着数据库就占用一个计数。
1）、处理掉占着连接但是不工作的线程
使用 kill connection 主动踢掉不工作的线程。show processlist 的结果里踢掉显示为 sleep的线程。（操作是可能有损失的，例如还处于事务中的线程。可以通过 information_schema 库中的innodb_trx 表，trx_mysql_thread_id 就是还处于事务状态的线程，踢掉不在事务中的 sleep 线程）
2）、减少连接过程的消耗
可以跳过数据库做权限验证的阶段。重启数据库，并使用 -skip-grant-tables 参数启动，这样MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程。（不安全，尤其外围可访问。在MySQL 8.0 版本，如果启动了 -skip-grant-tables参数，MySQL默认把 -skip-networking 参数打开，标识数据库只能被本地的客户端连接）
除了暴涨的短连接，还有可能是查询或者更新语句导致的性能问题。
查询：新出现的慢查询导致，还有是QPS（每秒查询数）突增导致的。
出现慢查询的可能：
1）、索引没有设计好：紧急创建索引，执行 alter table 索引 语句。一般是有主备数据库，现在备用数据库执行，然后主备切换，再在之前的主数据库执行。
2）、SQL 语句没写好；
3）、MySQL选错了索引：应急方案是给 sql 语句加上 force index。
把慢查询日志（slow log）打开，把 long_query_time 设置为0，确保每个语句都会被记录入慢查询日志。
QPS突增问题：
某业务出现高峰，或者应用程序bug，可能会导致某语句 QPS 突然暴涨。
扩展知识点
sql 语句加上 order by desc ，扫描索引的时候就会从右往左扫描。
8、MySQL是怎么保证数据不丢失
binlog的写入机制
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache写到binlog 文件中。一个事务的 binlog 是不能被拆开的，无论事务多大，也是一次性写入。
每个线程一个 binlog cache ，参数 binlog_cache_size 控制单个线程的 binlog cache 的大小。如果超过了该 size ，就要暂存到磁盘。
事务提交，执行器把 binlog cache 的完整事务 write 到 binlog 文件中，并清空 binlog cache。
然后 binlog files 进行fsync 操作，才是将数据持久化到磁盘（fsync 才占磁盘的IOPS）。
write 和 fsync 的时机是由参数 sync_binlog 控制：sync_binlog=0，每次提交事务都只 write，不 fsync。 sync_binlog=1 的时候，每次提交事务都 fsync。 sync_binlog=N（N>1）的时候，每次提交事务都write ，但积累 N 个事务才 fsync。（实际业务场景设置为100~1000的某个数值，IO瓶颈时，设置为一个较大的值可以提升性能。但是主机异常重启，会丢失N个事务的 binlog 日志）
redo log 的写入机制
日志 write 到 redo log buffer 是很快的，write 到 page cache 也差不多，持久化到磁盘就慢多了。
InnoDB提供了 innodb_flush_log_at_trx_commit 参数设置：值为0时，每次事务提交时都只是把 redo log 留在redo log buffer中。值为1时，每次事务提交都把 redo log 持久化到磁盘。值为2时，每次事务提交都把 redo log 写到 page cache。
InnoDB 有个后台线程，每隔1秒就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache ，然后调用 fsync 持久化到磁盘。（事务执行过程的 redo log 也是写在 redo log buffer 中，这些 redo log 会被后台线程一起持久化到磁盘。所以一个没有提交的事务的 redo log 也可能已经持久化到磁盘的）
通常MySQL是"双1"配置，sync_binlog 和 innodb_flush_log_at_trx_commit 都设置为1。一个事务完整提交，需要等到两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog 阶段。
组提交机制
在并发更新场景下，第一个事务写完 redo log buffer 以后，这个 fsync 越晚调用，组员（LSN序号的 redo log）可能越多，节约IOPS的效果越好。
WAL机制： redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度快。组提交可以大幅度降低磁盘的IOPS消耗。
该文章只是笔记，记录的是我自己大概的总结。
如果想看完整的知识点，可以去 极客时间app 上找 MySQL实战45讲 --- 林晓斌 老师的课程。
链接：https://time.geekbang.org/column/139