我的MySQL

主要来自

    《高性能MySQL（第3版）》

    《MySQL管理之道：性能调优、高可用与监控（第2版）》

    《MySQL 8 Cookbook:Over 150 recipes for high-performance database querying and administration》

    以及MySQL的官方文档

相关名称解释：

    schema：database and table structure

    shared lock，共享锁，S锁：允许其他事务读取被锁定的对象，其他事务也可以获取另一个此对象的共享锁，但是不允许写操作。

    exclusive lock，排它锁，X锁：

    intention lock，意向锁：作用在表上（是表级锁），用于指明事务要获取行锁的类型。不同的事务可以在同一个表上获取不同的意向锁，但是第一个获取了表IX锁的事务，会阻止其他想要获取S锁和X锁的。相反，如果第一个获取了表IS锁的事务，仅阻止其他想要获取X锁的事务。

来自《高性能MySQL（第3版）》

1、MySQL的存储引擎

    可是使用语句查看table的存储信息：show table status like ‘tablename’;

    InnoDB引擎：被设计用来处理大量的短期（short-lived）事务，当然在非事务性存储也很流行。

    MyISAM：设计简单，对整个表加锁，不支持行锁。

    其他引擎有Archive、Blackhole、CVS、Federated、Memory等。

    大多数情况下选择InnoDB，除非要用到InnoDB不具备的特性。例如，不在乎并发与扩展，也不在乎崩溃后数据丢失，却对InnoDB的空间占用比较敏感，可选择MyISAM。

2、选择存储引擎的几个需要考虑的因素。

    事务：比如无需事务、且主要使用select和insert的日志型应用，则可使用MyISAM。

    备份：

    崩溃恢复：

    其他特性：如地理空间搜索、聚簇索引等等

3、关于InnoDB的几个特点

    table是基于聚簇索引建立的，主键查询效率非常高，但是有二级索引问题。

    采用MVCC支持高并发，并且实现了4个事务隔离级别，默认是Repeatable Read。

4、死锁与解决方法

    在资源上的相互占用导致死锁。发生死锁时，只有部分或完全回滚其中一个事务，才能打破死锁。

数据库提供了死锁检查和死锁超时。InnoDB目前处理死锁的方式是，将持有最少行级排它锁的事务进行回滚。

5、关于事务日志

    存储引擎只在内存中修改数据，并不立即把数据持久化到磁盘，而是把事务日志持久化到磁盘。事务日志采用追加方式，并且磁盘是小区域的顺序IO，执行效率高。内存中的数据在后台择机刷新到磁盘。如果此时宕机，重启后引擎会恢复这部分数据。

6、事务中混合使用存储引擎

    事务是由存储引擎实现的，所以如果事务中涉及不同的存储引擎，那这个事务是不可靠的。比如混合使用InnoDB和MyISAM，在回滚时非事务型无法撤销。

7、InnoDB的显示和隐式锁

    显示锁：Select … Lock in share mode；Select…for update。MySQL也支持Lock table和Unlock

table语句。

8、InnoDB的两阶段锁定协议

    在事务执行过程中，可以随时执行锁定。只有执行Commit或Rollback时才会释放锁，并且所有锁是同时释放。

9、多版本并发控制：MVCC

    可以认为MVCC是行锁的变种，它的实现是通过保存数据在某个时间点的快照实现的。与其相关的事务隔离级别是Repeatable Read。

10、MySQL基准测试的指标都有那些

    吞吐量：单位时间内事务处理数，主要是针对在线事务处理（OLTP）的吞吐量，常用单位是TPS：每秒事务数，或TPM：每分钟事务数。

    响应时间或延迟：测试任务所需的整体时间，通常计算平均相应时间，最小相应时间、最大相应时间及百分比。常用百分比响应时间来描述最大相应时间，如95%的相应时间是5毫秒，表示95%的任务可以在5毫秒内完成。

    并发性：需要关注在并发增加时，吞吐量是否下降，响应时间是否变长。

    可扩展性：简单说就是给系统增加一倍资源，是否可同样增加一倍的吞吐量。

11、一些测试工具

    集成式测试工具：

        ab：一个Apache Http服务器的基准测试工具。

        http_load：类似ab

        JMeter

    MySQL测试工具：

        MySQLslap

        Sql-bench

        Super smack

        Database Test Suite

        Sysbench

12、关于“性能优化”的一个前置知识：任务所需的时间

    性能就是完成任务的所需时间，即响应时间。而非“每秒查询次数”“cpu利用率”“可扩展性”之类。

    执行任务包括两个时间：等待时间和执行时间。当谈到优化任务执行时间时，需要从这两个完全不同的角度去考虑。

    比如任务执行时间优化，优化去掉一些子任务、提升子任务时间等。

13、关于选择优化的数据类型

    更小的通常更好：占用空间少，磁盘、内存、cpu缓存的空间。

    简单的更好：使用内建类型存储日期或时间；使用整数存储IP地址。

    尽量避免NULL：对NuLL很难优化，且进行统计和比较都会复杂。也有例外，Null对于InnoDB列的稀疏数据有很好的空间利用率）

14、关于varchar（5）和varchar（200）

    如果存储hello，那更短的列会有优势吗？事实证明其优势很大，更长的列占用更多的内存，MySQL内部会分配固定大小的内存块来保存内部值。

15、关于Alter table

    【略】

16、MySQL索引有哪些类型？

    B-tree索引：大多数引擎都支持此类型索引，但是又有所区别，NDB使用T-Tree，InnoDB使用B+Tree。

    Hash索引：MySQL中只有Memory引擎支持Hash索引。扩展，InnoDB支持“自适应Hash索引“（在此不讨论）。

    R-tree空间数据索引：MyISAM支持空间索引，可用作地理数据存储。

    全文索引：查找的是文本中的关键字，而不是比较索引的值。

    第三方存储引擎使用的自定义索引。

17、索引的优点

    1、减少服务器扫描的数据量。

    2、帮助服务器避免排序和临时表。

    3、将随机IO变为顺序IO。

18、高性能索引策略

    从以下方面考虑索引的使用策略：

    独立的列：索引列不能是表达式的一部分，也不能是函数的参数。

    前缀索引和索引选择性：平衡一下前缀索引和索引选择性两者的关系。

    多列索引：这里涉及一个“索引合并“的概念，

    选择合适的索引列顺序：对B-tree来说，最左顺序使用索引原则。但是，把选择性最好的列放置到最左是通用原则吗？在某些特殊场合下，避免随机IO和排序可能更重要一些。

    聚簇索引：这不是索引类型，而是数据存储方式。在InnoDB中，聚簇索引在同一结构中保存了B-tree索引和数据行。

    覆盖索引：如果索引中已经包含所需数据，则称为覆盖索引。

    使用索引扫描做排序：

    压缩（前缀压缩）索引：MyISAM使用前缀压缩来减少索引的大小。

    冗余和重复索引：需要消除

    未使用的索引：排查并消除。通过分析索引的使用频率（可能需要打开某开关）

    索引和锁：虽然InnoDB基于索引的行锁开销很小，但是依然要尽可能减少被锁定的行数。被锁定的行越多，锁争用会减少并发性。

19、关于InnoDB中的聚簇索引

    InnoDB通过主键聚集数据，如果没有主键，InnoDB会选择一个唯一的非空索引替代，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

    聚簇索引的几个优点：

        相关的数据保存在一起，减少IO次数

        数据访问更快，从聚簇索引中获取数据通常比非聚簇索引快。

        使用覆盖索引扫描的查询可直接使用页节点中的主键值，避免二次索引数据。

    聚簇索引的缺点：

        聚簇索引最大限度的提高IO密集型应用性能，但是如果都是基于内存的访问，顺序就不重要了。

        插入速度严重依赖插入顺序。

        更新聚簇索引代价较高。

        插入新行（或主键被更新）时，如果需要移动行，则可能面临“页分裂”问题。

        可能导致全表扫描变慢，尤其是行比较稀疏，或页分裂导致存储不连续的时候。

        二级索引占用空间可能更大，因为在二级索引包含行的主键列。

        二级索引访问需要两次索引查找。

20、一些啥玩意，看看吧！

    关联子查询：？（尤其是In（select ））改造。

    Union的局限性：union导致生成临时表。

    索引合并：？

    等值传递：？例如非常大的In（）列表。

    并行执行：？5.5版本不支持并行执行，8版本呢？需要考察。

    哈希关联：？MySQL不支持哈希关联，所有关联都是嵌套循环关联。MariaDB实现了真正的哈希关联。

    松散索引扫描：？MySQL不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。

    最大值和最小值优化：使用索引的有序特性，放弃分组函数，使用limit 1来替代。

    查询优化器提示（hint）：最好不使用这个，因为数据库是不断升级的，可能导致一些系统级优化变得无效，并且代码维护起来也比较繁琐。

21、关于优化Limit语句

    如limit 10000,20，前面10000条记录被抛弃，只返回后20条，这样的代价很高。可以从以下方面考虑：

        尽量的使用索引覆盖和延迟关联。

        转化为已知位置的查询，通过where的某些字段的条件。

        依据某字段（如注解，某索引）的顺序特性，将前面的查询结果作为后续分页查询的where条件。

        多获取1条数据（21条），如果存在第21条，则可以进行“下一页”如果不存在第21条，则无需再检索下一页。

        应用程序缓存。

22、关于优化Union

    MySQL（老版本这样，新版本呢？）总是通过创建并填充临时表的方式执行union，并且默认使用distinct去重，这个代价也很大。此时的优化是使用union all，避免唯一性检查。

    临时表是避免不了的（吗？，看看新版本）

23、大数据量的扩展性，一般有下面两个策略

    全量扫描数据、不要任何索引：考虑顺利IO和索引开销。

    索引数据，并分离热点

24、MySQL中重要的缓存

    以下缓存相对比较重要：

        InnoDB缓冲池

        InnoDB日志文件和MyISAM数据的操作系统缓存

        MyISAM健缓存

        查询缓存

        无法手工分配的缓存，如二进制日志和表定义文件的操作系统缓存

        其他缓存相对占用空间较小

25、MySQL的查询缓存检查

    【内容部分来自《深入分布式缓存从原来到实践》】

    相关的命令：

        show variables like‘query_cache%’

        show status like ‘Qcache%’

    Query Cache命中率= Qcache_hits/(Qcache_hits +Qcache_inserts)

26、每个连接需要多少内存

【TODO】

27、线程缓存（thread_cache_size）

    设置“线程池大小”的概念，新连接进入时可以从线程缓存中取一个线程，并分配给这个新连接。

    Thread_cache_size：可缓存的线程数。可以通过show status

    like ‘threads_created’查看以下状态。下面还有描述。

28、表缓存（table_cache_size）

    类似线程缓存，可以理解为对表结构（.frm文件）的缓存。这个参数对InnoDB的重要性非常小，InnoDB不依赖这个信息做很多的事情。

    InnoDB有自己的表缓存：Data Dictionary。

    可以设置的大一些，没啥影响。

29、InnoDB事务日志

    关于日志文件大小，有这样两个参数：

        Innodb_log_file_size:默认5M

        Innodb_log_files_in_groups:默认2

    默认总共10M，在实际生产环境中过小，可以调高到几百兆或GB。尤其是8版本中，已经进行优化，log文件设置的大一些没毛病！

    日志缓冲区参数：

        innodb_log_buffer_size:默认1M，相对有点儿小。10几M或几十M都正常。此参数对日志IO有影响。

    日志刷新到log文件（即持久化）参数：

    innodb_flush_log_at_trx_commit:

        0：每秒持久化到文件，并且刷新。与commit无关。

        1：每次commit，这个是默认值，比较安全

        2：每秒持久化，但并不刷新。与0的区别是MySQL宕机也不会丢失数据。但是如果服务器挂了，会导致一些事务丢失。

30、]InnoDB如何打开和刷新数据文件和日志

【TODO，很重要，回头看，略】

31、InnoDB表空间

【TODO、看看新版本的定义】

32、InnoDB的双写缓冲

    【TODO，再看看】

    很多文件系统做了同样的事情，没必要让InnoDB再做一遍，所以可禁用：innodb_doublewrite = 0。

32、InnoDB并发配置：innodb_thread_concurrency

    限制一次性可以有多少线程进入内核，0表示不限制。

    可参考一下公式：并发值=cpu数量*磁盘数量*2

34、MySQL的二进制日志参数

    sync_binlog参数：

        0：表示MySQL不刷新，交由操作系统自己决定什么时候持久化。

        >0：表示每多少次二进制日志写操作进行一次持久化，1是个很安全的设置。

        机制与事务日志类似，但是整体开销比事务日志要昂贵很多。设置为1，对性能损害可能很大。要慎重。

35、Binlog的格式，需要谨慎设置

    有三种格式：Statement、Row、Mixed

        Statement：记录sql语句。

        Row：记录数据的实际变更

        Mixed：默认是Statement，但在以下情况下是Row格式：

            NDB引擎的DML操作

            使用了UUID函数

            自增字段更新

            包含了Insert Delayed语句

            使用了用户定义函数（UDF）

            使用了临时表

    *Repeatable-Read隔离级别下，建议设置Row格式。Read-Committed隔离级别下Mixed和Row效果一样，都是Row格式。

    *Row相关的另一个参数binlog_row_image = minimal，让binlog只记录影响后的行，从而降低binlog增长量。

36、innodb的最重要的两个配置参数

        innodb_buffer_pool_size

        innodb_log_file_size：注意：在版本8，这个文件要稍微大一些，没啥影响。

37、关于max_connections配置

    如果不能执行查询，那多打开一个连接是没有好处的。所以遇到“太多的连接错误”是一种快速且代价小的失败方式。

    可观察“max_used_connections”和”max_used_connectios_time”来看一下历史统计信息。

    *需要警惕一下应用停机/重启时连接释放和重连问题，如果未释放干净，可能导致重连超限。

38、关于thread_cache_size配置

    跟线程数量的波动有关，不用设置太大，因为保持太大空闲线程也没啥卵用。一个相关的状态变量是“slow_launch_threads”，如果这个值比较大，则说明有些情况导致延迟了连接分配新线程。

39、关于expire_logs_days配置

    二进制日志过期时间。别手工rm删除日志文件，导致MySQL很迷惑自己的处理能力：）。

40、关于max_allowed_packet

    允许服务器接收与发送的最大数据包，默认值太小，但是设置的太大也有危险。如果太小，在复制时可能出现问题，备库不能接收主库发过来的复制数据。

41、关于优化排序的配置

    two-pass和single-pass排序算法

    max_length_for_sort_data：如果查询中所需要的列和order by列总大小（按字段定义，非实际存储数据的大小），则使用two-pass算法排序。否则使用single-pass算法。

    max_sort_length：当对blob和text排序时，只使用前缀，忽略剩余的值。此变量指定前缀大小。

42、关于排序缓存（sort buffer）和读缓存（read  buffer）需要设置吗？

    默认值就很好，无需配置

。。。。。。。

 来自《MySQL管理之道：性能调优、高可用与监控（第2版）》

46、MySQL有哪些形式的锁

    有以下三种级别的锁：

    表级锁：开销小、加锁快、不出现死锁；粒度大，冲突概率高，并发度低。MyISAM引擎属于这种类型。

    行级锁：

    页面锁：

47、Per_thread_buffers优化

    有这样的几个相关参数：

        read_buffer_size：表的顺序扫描（如全表扫描）时，会临时缓冲到这个区域，再返回给上层调用者。

        read_rnd_buffer_size：与上面的顺序读取不同，这个是随机读取的缓冲区。

        sort_buffer_size：order by或group by字段没用到索引，可用此参数增加每个线程的分配缓冲区来提高性能，同时出现“user filesort”，遇到这样的提示则需要优化。

        thread_stack：线程堆栈大小

        join_buffer_size：join中的关联字段没索引，会使用这个区域提高性能，也同时出现“using join buffer”，遇到这样的提示就需要优化了。

        binlog_cache_size：如果没有大事务，就不用太大。

        max_connections：最大连接数

    per_thread_buffers的内存计算公式是：sum（上面的buffer size）* max_connections。

48、Global_buffers优化

    有这样的几个相关参数：

        innodb_buffer_pool_size：默认才128M，太小太小，参考整个内存的70%-80%

        innodb_additional_mem_pool_size：数据字典缓存。10几M就够。

        innodb_log_buffer_size：日志缓冲区大小，与innodb_flush_log_trx_commit参数联合使用。

        key_buffer_size：MyISAM引擎参数

        query_cache_size：缓存select语句和结果集大小的参数。与query_cache_type联合使用，如果频繁的写操作，则最好关闭这个缓存，避免缓存过于频繁的刷新。

49、关于初始化参数中的：innodb_page_size

    配置InnoDB数据页大小，默认16K。书中的案例：相比16k而言，8k的cpu压力高一些，select吞吐量也高。

50、关于“谨慎”使用分区表功能

    分区字段必须属于主键字段

    where后面的字段必须是分区字段，否则会在所有分区全部扫描一遍。    

51、简要说明一下MySQL的事务实现

    MySQL在进行事务处理的时候，采用日志先行的方式保证事务可快速并持久的运行，即写数据前，先写日志，过程如下：

        开始事务时记录该事务的LSN日志序列号

        执行事务时，往innodb_log_buffer里插入事务日志（redo log）

        当事务提交时，将日志缓存中的事务日志刷新到磁盘（依赖另一个参数）。

        除了事务日志，数据库还会记录一定量的撤销日志（undo log），用于处理回滚。

        事务提交后，首先刷新binlog，然后再刷新redo log。【请看下面的问题】

52、数据库操作过程，会出现以下问题。也是事务隔离级别需要对付的几个问题

    更新丢失，Lost Update

    脏读，Dirty Reads

    不可重复读，Non-repeatable Reads

    两次更新问题，Second lost updates problem

    幻读，Phantom Reads

53、数据库的四个事务隔离级别

    未授权读取，Read Uncommitted

    授权读取，Read Committed

    可重复读，Repeatable Read

    序列化，Serializable

54、关于“间隙锁”

    间隙锁主要是防止幻读，用在Repeatable Read隔离级别。指的是，当对数据进行条件查询、范围检索时，对其范围内的也许并不存在的值进行加锁。其对高并发、且范围更新的业务有较大影响。

其实Repeatable Read的隔离级别就比较高，对高并发还是有影响的。

55、系统性能评估工具

    vmstat、sar、iostat、netstat、free、ps、top、mpstat

    dstat、collectl

    淘宝的Tsar

56、一些常用的性能指标

    CPU：

        cpu使用率

        %us：应用程序（用户空间）所花费的CPU百分比

        %sy：系统（内核）所花费的CPU百分比

        %ws：I/O等待所需的CPU时间总和

        %id：CPU空闲百分比

        %ni：Nice时间，花费在执行re-nicing（改变进程执行顺序和优先级）的CPU百分比。

    内存指标：

        空闲内存、交换空间使用

        磁盘性能指标

        磁盘I/O等待

        队列平均长度

        平均等待时间

        每秒传输的数量

        每秒读写块的数量

        每秒读写字节的数量

57、高可用方案

    MMM、MHA

。。。。。。。。。。

来自《MySQL 8 Cookbook:Over 150 recipes for high-performance database querying and administration》

58、获取Database和Table的信息/Getting information about databases and tables

    这些信息来自于Information_schema，主要包括两类信息：

    静态表元数据，static table metadata：Table_Schema、Table_Name、Table_Type、Engine。

    动态表元数据，Dynamic table metadata：AUTO_INCREMENT,

    AVG_ROW_LENGTH, and DATA_FREE。收集动态信息是有些成本的。

59、关于Common table expressions（CTE）

【忽略】

60、关于Generated columns

【忽略】

61、关于Window functions

【忽略】

62、关于Repeatable Read的几点知识

    当一个事务开始时并执行了第一个读时，会创建一个读视图并保持打开，直到事务结束。为了提供前后一致的查询结果，InnoDB使用行版本和UNDO信息。称为MVCC-Multi-Version Concurrency Control。

63、Locking

【很重要的一章，其中参考了MySQL official doc】

    MySQL有两种类型的锁：

        内部锁Internal locking：MySQL内部用于管理多会话导致的表内容挣用。

        外部锁External locking：客户端会话可以直接获取表锁，阻止其他会话访问表。

    内部锁主要包括两种类型：

        行级锁Row-level locks：把访问的行锁定，允许多会话同时写访问，适用于多用户、高并发、OLTP应用程序。只有InnoDB支持行级锁。

        表级锁Table-level locks：MyISAM、MEMORY、MERGE

    table使用表级锁。适用于只读、读非常多、单用户场景。

64、External Locking

    使用Lock table和Unlock table来控制锁，分读锁和写锁。

        READ读锁：允许其他会话读访问，阻塞所有会话写访问，其他会话也可以同时获得读锁，读锁是共享锁（shared lock）。

        WRITE写锁：只允许当前会话读写，阻塞其他会话读/写访问，其他会话也不可以同时获取任何锁，写锁是排它锁（exclusive

lock）。

    使用Unlock table语句或会话结束，可释放锁。

    语法：

        Lock tables table_name [READ|WRITE]

        Unlock tables

        FLUSH TABLES WITH READ LOCK;       相当于冻结了整个数据库。

65、锁队列Locking queue

    1、只有共享锁可以同时作用在表上

    2、如果已有共享锁，随后有请求获取排它锁，那这个排它锁会被放入一个队列，直到共享锁被释放。

    3、只要锁队列中有锁请求，那么随后的锁请求都会被放入队列。

    4、

        4.1、InnoDB在进行读/写表的过程中，会获取元数据锁。

        4.2、如果此时第二个事务申请写锁（lock table write），那此请求会被放入锁队列。

        4.3、如果此时有第三个事务仅仅是想读数据，那也会别阻塞，也会被放入锁队列。

上述规则还是挺有意思的，总之，最好别使用lock table。因为随后的事务会被放入队列，而不是“直接错误”。超级危险。

66、InnoDB Locking

【很重要的一章】

67、二进制日志Binary Logging

    数据和结构变化都会记录到二进制日志中。二进制日志可用于：

        Replication复制：主从复制。

        Point-in-time recovery时间点恢复。

    日志格式（binlog_format）有三种：Row、Statement、Mixed

68、Backup and Restoring，备份与恢复

【忽略】

69、关于复制-Replication的一些拓补结构（topologies）

    主从复制master-slave replication

    链复制chain replication：masteràrelay masteràslave。

    主主复制master-master replication、

    多源复制multi-source replication：一个从服务可以接受来自多个主服务的数据。

70、关于延迟复制（delayed Replication）

    主要是防止严重的应用层问题（如drop table等）被快速传递到从库。

71、关于半同步复制（semi-synchronous replication）

    默认是异步复制的，Master不关心从机是否进行正常的复制，这可能有数据丢失风险。

    半同步复制是Master确保至少一台Slave已经收到了“writes”。

    全同步复制是Master保证所有Slave提交事务。

72、关于日志

    MySQL日志包括以下类型（注意：是MySQL的日志，不是存储引擎的）：

        错误日志，error log

        通用查询日志，general query log

        慢查询日志，slow query log、

        二进制日志，binary logs、

        中继日志，relay logs

        DDL logs

        InnoDB相关的日志：

            Redo Log

            Undo Log

73、关于性能调整，相关的概念有以下

    执行计划，explain plan

    基准查询和服务，Benchmarking queries and server

    增加索引，Adding indexes

    看不见的索引，Invisible index：如果要删除索引，可以先让它Invisible，观察一下删除索引的影响。

    降序索引，Descending index：相对Ascending index（升序索引），可定义降序索引。

    慢查询分析，Analyzing slow queries using pt-query-digest

    优化数据类型，Optimizing datatypes

    移除重复和冗余索引，Removing duplicate and redundant indexes

    检查索引的使用，Checking index usage

    控制查询优化器，Controlling the query optimizer

    使用索引暗示，Using index hints

    使用资源分组，Using resource groups

    使用performance_schema

    使用sys schema

74、关于Secondary indexes

    Primary key（clustered index）和Secondaryindexes

75、用put-query-digest分析慢查询

    不只是慢查询日志，这个工具还可以通过多种渠道收集查询：

        慢查询日志，slow query log

        通用查询日志，general query log

        过程列表，process list

        二进制日志，Binary log

        TCP dump

来自《MySQL Documentation——MySQL Reference Manual》

76、关于优化

    很多很多，包括从以下方面考虑优化，每个方面都是很大的课题

        SQL语句

        索引，Index

        数据库结构

        引擎特定的优化：InnoDB、MyISAM、Memory等引擎

        查询执行计划

        控制查询优化器

        缓冲与缓存（Buffering and Caching）

        锁优化

        MySQL Server优化

        性能测试，Measuring Performance(Benchmarking)    

        线程信息检查，Examining ThreadInformation

77、关于优化SQL Statements

    SQL语句，从使用目的上可分为两种类型：1、数据处理；2、系统监控。因此，SQL优化也从这两种目的上分别进行。

    SQL 语句的优化包括很多范围，大致如下：

        优化Select语句

        优化子查询、派生表、视图引用、公用表表达式

        优化Information Schema查询

        优化Performance Schema查询

        优化数据变更语句（insert、update、delete）

        优化数据库权限

        还有其他一些小地方【略】

78、关于Select优化

    看看标题，不详述了：

79、关于Where优化

    一句话，去掉没用的条件；优化条件。

    早期检查常数表达式

    如果没有分组函数和Group By，Having与where合并。

    构造简单的Where条件，以便获得快速Where评估。并尽可能多的过滤掉行。

    固定表格（no row或只有1行的表；主键匹配）优先加载。

    如果order和group都来自一个表，这个表最好能作为join的第一个

    Order和group不一样，或Order来自不同的表，那会导致创建临时表。

80、关于Limit 查询优化

【不详细说了，看原文吧，上面有类似内容】

    分为两部分：

        需要分页时，考虑如何优化limit。Limit作为优化对象。

        进行一些查询时（如order by），可以考虑用limit去优化它们。Limit作为优化手段。

81、重点看看InnoDB的大量数据加载问题：Bulk Data Loading for InnoDB Tables

        这跟Insert大量数据的优化有关，考虑一下操作：

            关闭自动提交，否则导致更频繁的flush log。

            如果有Secondary唯一索引，先关闭唯一性检查。

            如果有外键，先关闭外键检查。对大表来说，可节省超多的IO。

            使用多行插入Insert into yourtable values （,,），（,,），…。

            如果有自增列，可把innodb_autoinc_lock_mode设置为2

            按照主键的顺序插入

82、关于InnoDB的若干优化

    表格存储布局优化：考虑使用optimize table来重组表格；尽可能缩短主键长度；如果varchar太长考虑使用前缀索引；不固定长度的字符串使用varchar替代char；大表考虑使用Compressed行格式，以及考虑使用全表扫描等。

    事务管理优化：找到事务特性的性能开销与工作负载的平衡点。Autocommit设置对日志刷新和只读事务的影响；避免大数据操作后的回滚，会恶化性能；事务日志的刷新机制；

    只读事务优化：对只读事务避免设置事务ID而产生的开销。系统内有与优化机制。

    Redo日志优化：太小会导致不必要的写磁盘，最新版本下，大的Redo文件不会导致太长时间的恢复，已经优化了。如果有大事务，要把缓冲区设置的大一些。

    大量数据加载优化：尤其是批量insert场景下，进行特定的设置。

    查询优化：关键是对index的创建和使用。

    DDL操作优化：比如使用truncate table，如果有外键可先drop table再create。

    磁盘I/O优化：通常的缓存设置；日志刷新机制等；使用Linux native AIO等等；利用多个磁盘；

    配置优化：很多配置项优化

    多表的系统优化：【看原文吧】

83、InnoDB的锁

    共享锁和排它锁（Shared and Exclusive Locks）：InnoDB实现了标准的行级锁，包括有两种类型：共享锁（S锁）和排它锁（X锁）。

    意向锁（Intention Locks）：InnoDB支持多种粒度锁共存，行级锁和表级锁，比如使用Lock table write锁定表。为了提供多种粒度级别上进行实际锁定，InnoDB使用意向锁（Intention Lock）。意向锁是表级锁，用于指示事务稍后对于表中的行需要哪种类型的锁（共享或独占）。意向共享锁（IS）（Select for share）表示事务意图获取表的每行共享锁。意向排它锁（IX）（Select for update）表示事务意图获取表的每行排它锁。

    记录锁（Record Locks）：对索引记录进行加锁，即使一个表没有定义索引，InnoDB引擎也会创建一个隐藏的聚簇索引。

    间隙锁（Gap Locks）：索引记录之间的“间隙”进行加锁，或者是第一个记录之前，或者是最后一个记录之后的“间隙”。所谓间隙，有可能有1个值或多个值，也可能是空。

    下一主键锁（Next-Key Locks）：记录锁和间隙锁的组合。

    插入意向锁（Insert Intention Locks）：是间隙锁的一种，多个事务对同一间隙序列进行插入时需要等待。

    自增锁（AUTO-INC Locks）：表级锁，这个innodb_autoinc_lock_mode配置对自增机制有影响。

    空间索引的断言锁（Predicate Locks for Spatial Indexes）：【略】