《高性能mysql》读书笔记

最新推荐文章于 2022-10-08 18:21:53 发布

charming丶

最新推荐文章于 2022-10-08 18:21:53 发布

阅读量138

点赞数

本文链接：https://blog.csdn.net/m0_46125280/article/details/103854801

版权

【推荐】2019 Java 开发者跳槽指南.pdf(吐血整理) >>>

1.锁有两种类型：读锁和写锁。读锁是共享的，或者说是相互不阻塞的，多个客户在同一时刻可以同时读取同一资源，而互不干扰；写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。另外，写锁比读锁有更高的优先级，也即在同时有读锁和写锁的情况写，写锁可能会插入到队列前面执行。

2.事务的特性：原子性、一致性、隔离性、持久性。

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性；
一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
持久性：一旦事务被提交，则其所做的修改会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

3.对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。

4.事务的隔离级别：READ_UNCOMMITTED、READ_COMMITTED、REPEATABLE_READ、SERIALIZABLE。

READ_UNCOMMITTED：在此级别就算没提交，事务中的修改对其他事务也是可见的；
READ_COMMITTED：此级别满足了前面事务的简单定义，即一个事务开始时，其所做的修改只对当前事务可见，但是其未解决重复读的问题，即当前事务在重复读取某条数据期间，如果另一事务对该数据进行了修改，那么当前事务就会读取到不一样的数据；
REPEATABLE_READ：此级别解决了事务重复读的问题，但是却没有解决幻读的问题，即如果当前事务读取的是一定范围内的数据，而领一事务此时插入了一条数据，那么当前事务两次读取的数据还是不一致；
SERIALIZABLE：可串行化是事务读取的最高级别，其强制要求事务在读取的时候串行化的执行，其安全性最高，但却产生了锁的竞争等一系列问题。

5.死锁是指两个或多个事务在同一资源上相互占用，并且相互请求对方所占用的资源，从而导致恶性循环的现象。

6.为了解决死锁的问题，数据库一般实现了各种死锁检测和死锁超时的机制，越是复杂的数据库系统越能检测到死锁的循环依赖，并且返回一个错误。另外一种处理方式是，当获取锁超时时就放弃获取该锁。这里InnoDB实现的方式是当检测到获取锁超时时，其会检测锁定行数最少的事务，并对其进行回滚。

7.死锁产生的原因一般有两种：一种是由于事务获取的数据之间有冲突，这种情况很难避免；另一种是由于数据库的实现方式产生的。

8.对磁盘中数据的修改操作每次都要写两次磁盘，如果对于每次修改操作都持久化到磁盘，那么就需要在多个地方随机移动磁头，而事务日志是指存储引擎在修改表数据时只需要修改其内存拷贝，再把修改行为持久化到硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。并且事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头。

9.MySQL默认采用自动提交的模式，也就是说如果不是显示的提交一个事务，那么每一个查询都将当做一个事务执行。在当前链接中可以通过设置AUTOCOMMIT变量设置是否采用自动提交，设置方式为SET AUTOCOMMIT=1，设置了之后所有的查询都将当做一个事务执行，直到执行COMMIT提交或者ROLLBACK回滚，此时该事务结束，开始执行下一个事务。另外需要注意的是，对于一些非事务的表，如MyISAM或内存表，其是非事务类型的表，也就是说对于这类表没有COMMIT和ROLLBACK的概念，所有的查询都处于AUTOCOMMIT的模式。

10.MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器实现的，和存储引擎无关。

11.LOCK TABLES和事务之间相互影响的话，情况会变得非常复杂，在某些MySQL版本中甚至产生无法预料的结果。因此，这里建议，除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显示地执行LOCK TABLES，不管使用的是什么存储引擎。

12.MySQL和其他多种类型数据库基于并发考虑，一般都会同时实现多版本并发控制（MVCC）。MVCC是行集锁的一个变种，其在很多情况下避免了加锁操作，因而开销更低，并且在大多数情况下实现了非阻塞的读操作，写操作也只锁定必要的行。MVCC的实现是通过保存数据在某个时间节点的快照来实现的。也就是说，不管需要多长时间，每个事务看到的数据都是一致的。而根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

13.InnoDB的MVCC，是通过在每行记录后面保存两隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（删除时间）。当然，存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号进行比较，以下是REPEATABLE_READ隔离级别下，MVCC是如何操作的：

SELECT：

InnoDB会根据以下两个条件检查每行记录：

a.InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于当前事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。

b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保，事务读取到的行，在事务开始之前未被删除。

INSERT：

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE：

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE：

InnoDB为插入一行新记录，保存当前系统版本号作为当前行版本号，同时保存当前系统版本号到原来的行作为删除标识。

保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且能保证读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的检查工作以及额外的维护工作。

14.InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE_READ，并且通过间隙锁（next-key locking）策略防止幻读的出现。

15.InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

16.基准测试的作用：

①验证基于系统的一些假设，确认这些假设是否符合实际情况；

②重现系统中的某些异常行为，以解决这些异常；

③测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题；

④模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展瓶颈；

⑤规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件，需要多大容量的网络，以及其他的相关资源；

⑥测试应用适应可变环境的能力。例如，通过基准测试，可以发现系统在随机的并发峰值下的性能表现，或者是不同配置的服务器之间的性能表现。基准测试也可以测试系统对不同数据分布的处理能力。

17.针对整个系统做集成测试而不是单组价测试的原因：

①测试整个应用系统，包括web服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不仅仅是MySql本身的性能，而是应用整体的性能；

②MySql并非总是应用的瓶颈，通过整体的测试可以揭示这一点；

③只有对应用做整体测试，才能发现各部分之间的缓存带来的影响；

④整体应用的集成式测试更能揭示应用的整体表现，而单组件的测试很难做到这一点。

18.基于如下情况，可以只测试MySql：

①需要比较不同的schema或查询的性能；

②针对应用中某个具体问题的测试；

③为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的“周期循环”，来检测出某些调整后的结果。

19.Thread.isAlive()方法只有在当前线程在运行的时候才会返回true（也即调用start()方法运行之后，new了一个Thread当前线程是不会处于活跃状态的）。

20.优化一个任务有两个方向：执行时间和等待时间。如果是优化任务的执行时间，则应该定位不同的子任务花费的时间，然后优化去掉一些子任务，降低一些子任务的执行频率或者提升一些子任务的执行效率；而优化任务的等待时间则要复杂一些，因为等待有可能是其他系统间接影响导致，也可能是争用磁盘或CPU资源导致。

21.整数数据类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。这几种类型分别使用8，16，24，32，64位存储空间，并且可以使用UNSIGNED修饰，使用UNSIGNED修饰之后其不能存储负值，使用的存储空间不变，并且正数值的存储范围扩大了一倍。

22.数据的存储类型决定了该数据在内存中占用的空间大小，但是在计算中是使用64位的BIGINT整数，即使在32位的操作系统中也是如此。

23.可以在整数类型后指定宽度，如INT(11)，这种方式指定的是该数据在命令行中查询出来时显示的位数，而内存中使用的位数是不受影响的。

24.实数类型：FLOAT，DOUBLE和DECIMAL。前两种称为浮点类型，对于前两种MySQL使用浮点数来存储，因而可能丢失一定的精度，而DECIMAL则使用字符串进行存储，其不会丢失精度，因而常用于金额的计算。FLOAT占用的存储空间是4个字节，DOUBLE占用8个字节，而DECIMAL则可以动态的指定，如DECIMAL(18,9)表示小数点前有9位数字，小数点后有9位数字，各占4个字节，加上小数点一个字节，总共9个字节，因而DECIMAL相对于浮点数占用空间更大。另外，浮点数只是数据库的存储方式，在计算时还是转换为double类型进行计算。

25.如果确认浮点数的存储精度，那么可以将浮点数乘以相应的精度，将其转换为整数，然后使用BIGINT来存储浮点数，这样可以避免浮点数精度丢失的问题，并且也可以提高计算效率。

26.varchar的特点：

varchar存储的是可变长度的字符串，并且其内部还会维护1~2个字节，用于存储实际占用空间的长度；
如果对varchar类型数据进行更新，因为新的行比原来的要长，因而需要做额外的工作来扩容，并且如果在当前页没有更多的空间来存储，这种情况下InnoDB会将当前页进行分裂以存储数据，这将导致产生片段数据，读取时将造成磁盘的随机读写；
varchar虽说可以根据实际占用空间来使用硬盘空间，但是在内存中进行计算时，其还是使用声明的最大长度来存储数据计算的。

27.char类型是定长的，其适合存储很短的或者值都接近同一长度的字符串，对于经常变更的数据，char也比varchar好，因为char不容易产生碎片。需要注意的是，保存char数据时，会将数据最后的空格默认给去掉。

28.BLOB和TEXT类型

BLOB中有TINYBLOB、SMALLBLOB，BLOB、MEDIUMBLOB、LONGBLOB，而TEXT中则同样有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，这里SMALLBLOB和BLOB是同义词，SMALLTEXT和TEXT是同义词；
当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域进行存储，表中字段只需要存储一个指针；
BLOB存储的是二进制数据，其没有排序规则，TEXT存储的则是字符集，其有排序规则，并且其只会根据前max_sort_length长度的字符进行排序；

29.如果数据在查询时使用到了BLOB或者TEXT字段，并且隐式的使用到了临时表，那么MySQL将使用MyIsam内存临时表，这将造成很大的磁盘开销。解决办法有两个，一是不使用BLOB和TEXT类型，二是在创建BLOB和TEXT字段的时候另外添加一个字段使用substring(column, length)保存BLOB和TEXT类型前面部分数据，在进行一些排序操作时使用新加的字段来避免使用MyIsam内存临时表。这种处理方式也适用于一些存储值比较长的字段，比如varchar(1000)，对于存有1000万条数据的varchar(1000)数据，假设每个字符占3个字节，临时表的大小将达到30G。

30.创建枚举类型字段的方式：

create table enum_test (
  e enum ('fish', 'apple', 'dog') not null
);

插入枚举值的方式：

insert into enum_test (e) values ('apple'), ('dog'), ('fish');

说明：

mysql对于枚举类型的处理是将其映射为一个整数值存储；
如果以枚举字段排序，那么排序是基于底层映射的枚举进行的，而不是枚举字符串；

31.把值都转换为enum之后，关联会变得很快，但是当varchar列和enum列进行关联时则慢很多。但是将列都转换为enum之后由于都映射为了整数，因而可以减少表占用的内存，这样可以减少磁盘的I/O。

32.Mysql有两种存储时间的类型：datetime和timestamp。其中datetime与时区无关，其将时间封装到格式为YYYYMMDDHHMMSS的整数中；而timestamp则保存了从1970年1月1日午夜以来的秒数。需要注意的是Mysql对时间的处理都是以秒为单位的，如果需要处理毫秒级别的数据，可以使用BIGINT或者DOUBLE类型。

33.对于BIT类型，MyISAM会将BIT列数据打包存储在单个字节中，比如九个BIT位可以保存在两个字节中；而InnoDB则不会打包BIT位数据，对于每一个BIT位，其都通过一个足够存储的最小整数类型来存放，所以不能节省空间。

34.MySQL底层使用字符串保存BIT类型数据，因而如果直接查询BIT类型的数据时，获得的将是该二进制值转换为整数后的ascii码对应的字符串，这是比较令人费解的，因而对于大部分应用，应该避免使用这种类型。

35.一种保存TRUE/FALSE的方式是使用长度为0的字符串char(0)来表示TRUE，使用NULL值表示FALSE。

36.当某个字段被用作关联列的时候，要确保该字段在所有的关联表中都使用相同的类型，类型之间要精确匹配，包括像unsigned这样的属性。这样做的目的主要有一个：

避免隐式的类型转换导致的错误

37.在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。这样做的目的有两个：

节省内存空间；
更合适的类型能够提升查询效率；

38.使用随机字符串作为关联键的缺点：

插入值会随机的分部在索引的任何位置，因而插入语句会比较慢；
由于逻辑上相邻的行会分部在磁盘和内存的不同地方，造成磁头的随机读，因而查询语句会比较慢；
造成缓存的效果变差，随机值会造成缓存的局部性原理失效。如果所有的数据都一样“热”，那么缓存任何一部分特定数据导内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

39.特殊数据类型：

低于秒级精度的时间戳在映射到数据库时需特殊处理，因为datetime和timestamp都是以秒为基本单位；
IPv4地址实际是一个32位无符号整数，应该使用无符号整数类型存储，而不是字符串，MySQL提供了INET_ATON()和INET_NTOA()函数来将ip地址与字符串进行转换。

40.太多的列将会导致CPU运行占用非常高，查询效率低下。这是因为MySQL在从存储引擎层读取数据之后将编码过的列转换为行数据结构的操作代价非常的高，尤其是可变长度的数据结构，因而查询导致需要转码过多的数据。解决办法是将表按字段进行拆分，将“热”字段与“冷”字段分别放在不同的表中。

41.MySQL规定每个关联操作最多只能有61张表，但实际上就算少于61张表，关联查询效率也会非常低，单个查询最好在12个表以内进行。

42.Mysql会在索引中存储null值。

43.范式的优点：

范式化的更新操作通常比反范式化要快；
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需修改更少的数据；
范式化的表通常更小，可以更好地放在内存里，所以执行操作会很快；
很少有多余的数据意味着检索列表数据时更少需要distinct或者group by语句。

44.范式的缺点：

查询时通常需要关联。

45.反范式的优点：

减少了表的关联操作，这将减少磁盘随机I/O的次数；
能够将本应分表的字段放在一个表中，并为其创建索引，提高查询速度。

46.对于反范式缓存的字段，其与真实字段数据的同步可以使用触发器进行。

47.对于需要实时查询的数据，可以通过分段的策略来进行查询，比如查询用户过去一天内发送消息的总数，可以将这个查询操作分为两部分：过去整个小时时间段内发送消息的数目和不足一小时的时间段发送消息的数目。而整小时时间段发送消息的数目可以通过定时任务同步更新到一张缓存表中，每次查询同时从缓存表和消息表中分别查询即可。

48.在项目中经常需要使用一种计数器，比如统计某个链接的点击次数，这里的问题在于每次点击调用接口的时候都会对计数器加一个全局的互斥锁，这样效率非常低，可以用如下办法处理该问题，即为该计数器声明一个类型，并且提前创建100条该类型的数据，点击时通过如下SQL语句更新点击次数：

UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

并且通过下面的sql语句获取总的点击次数：

SELECT SUM(cnt) FROM hit_counter;

如果需要每隔一段时间开始一个计数器，比如记录每天的点击次数，那么可以为计数表声明一个date和slot字段，建表语句如下：

create table daily_hit_counter (
  day date not null,
  slot tinyint unsigned not null,
  `count` int unsigned not null,
  primary key(day, slot)
) engine=InnoDB;

由于这里为day和slot声明了唯一索引，因而更新点击次数的时候可以通过如下SQL语句进行：

insert into daily_hit_counter (day, slot, `count`) values (current_date, rand() * 100, 1) on duplicate key update `count` = `count` + 1;

49.大部分alter table操作都将导致MySQL的服务中断，这是因为在执行alter table操作的时候MySQL会使用新的schema来创建一个表，然后将原表的数据复制到新表，并且删除原表。有两种方式处理alter table过程中锁表的问题：

在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换；
使用另外一个名称创建一个“影子表”，然后通过重命名和删除表操作来交换两张表；

对于列的默认值修改，可以通过两种方式来避免上述交换表的方式：

修改表的.frm文件中的默认值
通过alter column来更改默认值，该命令直接修改.frm文件而不设计表数据，如
```
alter table sakila.film alter column rental_duration set default 5;
```

50.MySQL使用索引的原则：

全值匹配：即按顺序和索引中的所有列进行匹配；
匹配最左前缀：即联合索引为A，B，C为顺序的，那么包含最左边列的子列集合都会使用到索引；
匹配列前缀：可以匹配某一列的值的开头部分，如like 'hellen%'；
匹配范围值：可以查询某一范围段的值；
精确匹配某一列并范围匹配另外一列：在匹配到某一列的值之后可以范围性的匹配联合索引下一列的值；
只访问索引的查询：查询只需要访问索引，而无需访问数据行。

51.因为索引树是有序的，因而在可以使用到索引树的情况下也可以按照其进行排序。

52.B树索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引；
不能跳过索引中的列进行查找；
如果查询中有某个列的范围查询，那么其右边的列都无法使用索引优化查找，这里如果使用了范围的列的值范围有限，可以通过多个等于条件代替，那么也是可以使用索引的。

53.hash索引是基于hash表实现，其多索引列的每个值计算一个hash码，该hash码比较小，然后将hash码存于索引中，并且将hash码与相应的数据行关联起来，需要注意的是在hash索引中存储的顺序是按照hash码有序排序的。

54.hash索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
哈希索引数据并不是按照索引的值的顺序存储的，所以无法用于排序；
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
哈希索引只支持等值比较查询，包括=、IN()、<=>，也不支持任何范围查询；
访问哈希索引的数据非常快，除非有很多哈希冲突，当有哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，知道找到所有符合条件的行；
如果哈希冲突很多的话，一些索引维护操作的代价会很高。

55.InnoDB有一种功能称为“自适应哈希索引”，当InnoDB发现某些索引值被使用得非常频繁时，其会在B-Tree索引的基础上为这些“热值”创建的一个哈希索引，比如根据某个值作为条件进行查询的时候其首先会计算该查询值的hash值，并且通过该hash值找到对应的列。注意这是一个完全自动的、内部的行为，用户无法控制或者配置。

56.当进行某些长字段值的查询时，我们可以模拟“自适应hash索引”的方式进行查询。比如表中存有大量的url，并且需要根据url进行查询，因为url长度都很长，并且根据url进行查询即使建有索引也会很慢，因而可以为url计算一个hash值，并且新增一个字段，将该hash值存储在该字段中，另外还需要为该字段创建一个索引，在进行url查询的时候可以根据url的hash值查询新增字段的值，由于建有索引，因而该查询是非常快的，查询如下：

select id from pseudohash where url_crc=CRC32("http://www.mysql.com") and url='http://www.mysql.com';

需要注意的是，在使用hash索引的时候还需要将真实值也带上，因为hash值可能会存在冲突的情况，带上真实值可以保证返回唯一的结果。对于url_crc字段的更新可以使用触发器完成，如创建如下触发器:

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW SET new.url_crc=crc32(new.url); DELIMITER ;
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW SET new.url_crc=crc32(new.url);DELIMITER ;

另外，如果表的数据量非常大，可以考虑自己实现一个简单的64位hash函数，该函数返回一个整数，注意不要使用SHA1()或MD5()作为哈希函数，因为这两个函数生成的哈希值本身就是非常长的字符串。

57.索引的优点：

索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O。

58.查询的“三星系统”：索引将相关的记录放到一起则获得异性；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。

59.只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效；对于中到大型的表，索引就非常有效；对于特大型的表，建立和使用索引的代价将随之增长，这种情况下，则需要一种技术可以直接区分查询需要的一组数据，而不是一条记录一条记录的匹配，例如可以使用分区技术。

60.高性能索引的策略：

①如果查询的列不是独立的，比如索引列在表达式或者是函数的参数等，这样是不会使用索引的，如：

select actor_id from sakila.actor where actor_id + 1 = 5;

②前缀索引和索引选择性，比如需要索引的是一个较长的字符串，那么有两种方式可以解决索引问题：一是通过为每个值生成一个hash值，存于一个新的字段中，并创建索引；另一种是只取该字符串的前缀部分，将其存于新字段中，并创建索引。

61.索引合并即表中有多个单列索引，在查询时同时使用到这几个单列索引，索引合并具体有以下几种情况：

or条件的联合；
and条件的相交；
组合前两种情况的联合及相交；

比如如下示例：

select film_id, actor_id from film_actor where actor_id=1 or film_id=1;

假设film_id和actor_id各自建立了一个索引，在MySQL5.0版本之前该查询不会使用到任何索引，但是可以通过如下方式改写：

select film_id, actor_id from film_actor where actor_id=1 union all film_id=1

使用union all之后MySQL会单独使用索引进行查询，查询完毕之后将结果集进行合并。在MySQL5.0之后的版本中，对于前面一条语句，其内部会自动使用索引合并策略，即使用各自使用单条索引查询之后进行合并。同理，对于and条件的相交和组合联合及相交的情况和这里讲的联合的情况类似。

62.索引合并策略部分时候是一种优化的结果，但是大部分时候都说明表上索引建得很糟糕：

当出现服务器对多个索引相交操作时（通常有多个and条件），通常说明需要一个包含所有相关列的多列索引，而不是多个独立的单列索引；
当服务器需要对多个索引做联合操作时（通常有多个or条件），通常需要耗费大量CPU和内存在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候；
优化器不会把结果集的合并计算到“查询成本”中，优化器只关心随机页面读取，并且这样做可能还会影响到查询的并发性。

63.计算前缀索引长度的方式：前缀索引选取的一个标准是索引的选择性较高（索引的选择性是指不重复的索引只和数据表的记录总数的比值）。在选择索引长度的时候可以通过如下SQL语句进行判断：

select 
count(distinct left(city,3))/count(*) as sel3, 
count(distinct left(city,4))/count(*) as sel4, 
count(distinct left(city,5))/count(*) as sel5, 
count(distinct left(city,6))/count(*) as sel6, 
count(distinct left(city,7))/count(*) as sel7 
from city_demo;

通过该SQL语句分别查询前缀分别为3，4，5，6，7列时其选择性的高低，另外，只看平均选择性是不够的，在选择性足够的基础上还需要查看数据分布是否均匀，其可以通过如下SQL语句查看：

select count(*) as cnt, left(city, 7) as pref from city_demo group by pref order by cnt desc limit 5;

最后，创建前缀索引的方式如下：

alter table city_demo add key (city(7));

64.对于索引列的选取，一般将选择性最高的列放到索引的最前列。

65.聚簇索引（InnoDB中）是指将索引和数据行存储到同一个结构中。InnoDB通过主键聚集数据。

66.如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

67.聚簇索引的优点：

可以把相关数据保存在一起，比如通过用户id来聚集数据，这样只需要从磁盘读取少数数据页就能获取用户的全部邮件；
数据访问更快，因为聚簇索引将索引和数据都保存在同一个B-Tree中，因而从聚簇索引中读取数据更快；
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

68.聚簇索引的缺点：

聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部保存在内存中，聚簇索引就没有什么优势；
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成之后最好使用OPTIMIZE TABLE命令重新组织一下表；
更新聚簇索引代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将一行数据插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，并且页分裂会导致占用更多的磁盘空间；
聚簇索引可能导致权标扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候；
二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列；
二级索引访问需要两次索引查找，而不是一次，这是因为二级索引中保存的“行指针”不是行的物理位置的指针，而是行的主键值。

69.使用随机聚簇索引的缺点：

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到内存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O；
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页；
由于频繁地页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片；

70.只有当索引的顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。

71.如果前导列为常量的时候，MySQL才可以在不需要前导列，并且只按照后续列的基础上排序。

72.压缩（前缀压缩）索引的压缩方式如下：首先保存第一条数据的值，然后对比第二条数据与第一条数据前缀部分有多少位相同，将第二位存储为“位数,后缀”的形式，如第一个值为perform，第二个值为performance，那么perform将完整保存，而performance的前7位与第一个值相同，其将保存为“7,ance”的形式。

73.前缀索引的优缺点：

优点：由于索引都是有序的，因而前缀相同的概率较大，前缀索引将极大的节省索引空间；
缺点：前缀索引的每一个值都依赖于前面一个值，因而前缀索引将无法进行二分查找，并且对于按照正序排序效率尚可，但是如果按照倒序排序，效果则不是很好，另外，前缀索引查找某一行平均都要扫描半个索引块。

74.可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

75.重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，应该立即移除。

76.冗余索引指的是在B-Tree索引中，新创建的索引与某个联合索引的前缀部分完全相同的索引。

77.增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是新增索引后达到内存瓶颈的时候。

78.索引可以让查询锁定更少的行，这主要从两个方面带来好处：

虽然InnoDB的行锁效率很高，内存使用很少，但是锁定行的时候仍然会带来额外开销；
锁定超过需要的行会增加锁征用并减少并发性。

79.在设计索引时，尽可能将需要做范围查询的列放到索引的后面，以便优化器能够使用尽可能多的索引列。

80.当需要建的索引列中包含某一列的值是固定的，比如sex只有male和female两种，那么这种列可以放在索引的前面，当需要根据单个值查询时直接使用即可，如果不需要将该字段作为查询条件，那么可以通过sex IN ('male', 'female')这种方式来匹配最左前缀，并且避免该字段的过滤。

81.对于范围查询，比如>，<，>=，<=等，使用范围查询的字段是可以使用索引的，但是其后续字段是无法使用索引的；对于等值查询，等值查询的字段和其后续字段都是可以使用索引的。

82.当查询中存在多个范围查询时，比如需要同时查询年龄在18到25，并且最近7天登录的用户，查询语句类似如下：

WHERE sex IN ('male', 'female') 
  AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY) 
  AND age BETWEEN 18 AND 25;

这种情况MySQL是无法同时使用两个字段的索引的，解决这个问题的方法是考虑新增一个字段，然后将其中一个字段进行转换，比如新增一个active字段，由定时任务进行维护，用户登录时，将其设置为1，并且将过去7天未曾登录的用户的值设置为0。

82.优化排序的方式主要分为两种：

当仅仅只是需要排序的时候，使用索引将排序的字段覆盖即可，注意排序的字段排序方式需要一致；
当排序之后使用了limit只进行部分数据获取的时候就需要注意，因为如果查询的记录比较靠后，比如(limit 100000, 10)，那么MySQL会扫描前100010条记录，并将前100000条记录丢弃掉，这将耗费大量时间在记录扫描上（查询时获取了过多的列，因而会扫描每一条的数据）。解决这个问题的方式在于延迟关联，即首先使用覆盖索引查询需要查询的记录的id，然后通过id直接获取结果集即可，如：

SELECT <cols> FROM profiles INNER JOIN (
  SELECT <primary key cols> FROM profiles WHERE x.sex='male' ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>);

83.可以使用check table <table_name>来检查表是否损坏，如果存储引擎不支持check命令，也可以使用alter table来间接修复表。

84.三种类型的数据碎片

行碎片：这种碎片指的是数据行被存储为多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降；
行间碎片：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对注入全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益；
剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，从而造成浪费。

85.消除碎片化的方式：

执行optimize table命令来消除碎片化；
执行不做任何操作的alter table命令来重建表，如alter table <table> engine=<engine>。

86.优化低效查询的通用方式有以下两种：

确认应用程序是否在检索大量超过需要的数据，这通常意味着太多的行，但有时候也可能是访问了太多列；
确认MySQL服务器层是否在分析大量超过需要的数据行。

87.使用“select *”取出全部列会让优化器无法使用索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。

88.衡量MySQL查询开销的三个指标：

响应时间；
扫描的行数；
返回的行数；

89.MySQL应用where条件的方式有如下三种，其从好到坏一次为：

在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的；
使用索引覆盖扫描（在Extra列中出现了Using Index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无需再回表查询记录；
从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。这在MySQL服务器层完成，MySQL需要先从数据表读取记录然后过滤。

90.处理MySQL扫描了大量的行，但只需要返回少量数据的方案：

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需会标获取对应行就可以返回结果了；
该表库表结构，例如使用单独的汇总表；
重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

91.在其他条件相同的情况下，使用尽可能少的查询当然是好的，但有的时候，将一个大查询分解为多个小查询是很有必要的。

92.切分查询：有时候将一个大的操作“分而治之”，将大操作切分成小操作，每个操作功能完全一样，只完成一小部分，这样虽然延长了处理时间，但是降低了对行或表的锁定时长，比如在对日志表数据迁移的时候，当迁移完成时需要删除当前表的数据，如果使用一次delete全部删除，将导致日志表很长一段时间无法工作，如果分批完成，比如每次删除10000条，这将有助于系统更好的工作。

93.分解关联查询的优点：

让缓存效率更高。因为分解关联查询之后查询的都是单条记录，无论是对应用层还是数据库层查询时都可以直接从缓存中取该数据进而查询其余数据；
将查询分解后，执行单个查询可以减少锁的竞争；
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展；
查询本身效率可能会有所提升。因为拆分关联查询之后可以使用in()查询替代部分数据获取，这可以让MySQL按照ID顺序进行查询，其比随机的关联效率更高效；
可以减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能要重复访问一部分数据；
这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

94.客户端与服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器请求数据，这两个动作不能同时发生。另外，服务端向客户端发送的数据一般较多，这个发送过程是一个推送的过程，也就是说，无论客户端是否停止请求或断开连接，服务端都会将请求的数据完全推送出来。

95.可以通过show full processlist来查看服务器当前状态，其主要有以下几种：

Sleep，线程正在等待客户端发送新的请求；
Query，线程正在执行查询或者正在将结果发送给客户端；
Locked，在MySQL服务器层，该线程正在等待表锁；
Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划；
Copying to tmp table [on disk]，线程正在执行查询，并且将结果集都复制到一个临时表中，这种情况一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上；
The thread is，线程正在对结果集进行排序；
Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据；

96.在解析一个查询语句之前，如果已经打开了查询缓存，那么MySQL首先会在缓存中查看当前查询是否命中，如果命中了，并且当前用户有查询权限，那么MySQL会跳过其他阶段，而直接将当前结果返回。需要注意的是，MySQL查询缓存是否命中的方式是通过对一个大小写敏感的哈希查找实现的，并且其也会将注释计算在内。

96.导致MySQL选择错误的执行计划的原因：

统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大；
执行计划中的成本估算并不等同于实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的；
MySQL的最优可能和你想的最优不一样。MySQL是基于其成本模型选择的最优计划，有少量时候这并不是最快的执行方式；
MySQL从不考虑其他并发执行的查询，这可能影响到当前查询的速度；
MySQL也并不是任何时候都是基于成本的优化，而是基于机械固定的规则，如存在全文索引的match()子句，则在存在全文索引的时候就使用全文索引；
MySQL不会考虑不受其控制的操作的成本，例如存储过程或者用户自定义函数的成本；
优化器有的时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

97.MySQL能够处理的优化类型：

重新定义关联表的顺序。数据表的关联并不总是按照在查询中指定的顺序执行；
将外链接转化成内连接。诸多因素，如where条件、库表结果都可能会让外链接等价于一个内连接；
使用等价变换规则。MySQL可以通过一些等价变换来简化并规范表达式；
优化count()、min()和max()。索引和列是否为空通常可以帮助MySQL优化这类表达式；
预估并转化为常数表达式。当MySQL检测到一个表达式可以转化为常熟的时候，就会一直把该表达式作为常熟进行优化处理；
覆盖索引扫描。当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行；
子查询优化。MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问；
提前终止查询。如果发现已经满足查询需求的时候，MySQL总是能够立即终止查询，主要有三类：①使用limit子句的时候；②发现一个不成立的条件，这是MySQL会立刻返回一个空结果；③在执行过程中发现某些特殊的条件，比如检索“不同取值”或者判断存在性（distinct、not exist()或者left join）时；
等值传播。如果两个列的值通过等式传播，那么MySQL能够把其中一个列的where条件传递到另一列上；
列表IN()的比较。没有是里取会对IN()中的数据进行排序，然后通过二分查找的方式确定列表中的值是否满足条件，这是一个O(log n)的操作，当IN()列表中有大量取值的时候，MySQL的处理速度会更快；

98.MySQL进行关联查询的策略：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，知道找到所有表中匹配的行为止。

99.临时表是没有索引的。

100.MySQL将结果集返回客户端是一个增量、逐步返回的构成，比如关联操作中，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就开始向客户端返回结果集了。

101.如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在union的各个子句中分别使用这些子句。另外，从临时表中取出数据的顺序并不是一定的，如果想获得正确的顺序，还需要加上一个全局的order by和limit操作。

102.在5.0和更新的版本中，当where子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

103.对于一个IN()列表，如果MySQL优化器发现存在where、on或者using的子句，将这个列表的值和另一个表的某个列相关联，那么优化器会将IN()列表都复制应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。

104.MySQL无法利用多核特性来并行执行查询。

105.MySQL使用MIN()和MAX()函数时都是通过全表扫描来获取匹配的数据值的。

106.MySQL不允许在一张表上同时进行查询和更新，但是可以为这张表创建一张临时表，然后将原表和临时表进行关联来绕开此限制。

107.查询优化器的提示：

HIGH_PRIORITY和LOW_PRIORITY。HIGH_PRIORITY会将当前当前的SELECT语句放在等待表锁的语句的最前面，LOW_PRIORITY会使当前语句处于一直等待的状态，只要队列中还有需要访问同一个表的语句；
DELAYED。该语句会将使用该提示的语句立即返回给客户端，并且将该语句放入缓存，待表空闲时再执行该语句，其对日志系统或者是客户端不需要等待其结果的语句非常有效，但是其会导致LAST_INSERT_ID()函数无法正常工作；
STRAIGHT_JOIN。该提示可以放在SELECT关键字之后，也可以放在任何两个关联表的名字之间。放在SELECT之后会让查询表的关联顺序与语句中表出现的顺序一致，而放在两个关联表之间会让查询这两个表的关联按照其出现的顺序进行。该提示对于没能正确的选择关联顺序或者是关联顺序太多而无法快速选择时非常有效；
SQL_SMALL_RESULT和SQL_BIG_RESULT。这两个语句只对SELECT语句有效，SQL_SMALL_RESULT会告诉优化器查询结果集很小，可以将结果集放入索引临时表中，以避免排序操作，而SQL_BIG_RESULT会告诉优化器查询结果集很大，可以将结果集放入磁盘临时表中做排序操作；
SQL_BUFFER_RESULT。该提示告诉优化器将查询结果放入到一个临时表中，然后尽可能快地释放表锁；
SQL_CACHE和SQL_NO_CACHE。该提示告诉优化器是否将结果集缓存起来；
SQL_CALC_FOUND_ROWS。该提示告诉优化器对返回的结果集包含更多的信息，比如该提示让MySQL计算除去LIMIT之后查询的结果集的总行数，可以通过FOUND_ROW()获取这个值；
FOR UPDATE和LOCK IN SHARE MODE。这两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效，使用该提示会对符合查询条件的数据行加锁。需要注意的是，这两个提示会让某些优化无法正常使用，比如索引覆盖扫描，应该尽量避免使用这两个提示；
USE INDEX、IGNORE INDEX和FORCE INDEX。这几个提示会告诉优化器使用或者不使用哪些索引进行查询，并且在MySQL5.1和之后的版本，可以使用FOR ORDER BY和FOR GROUP BY来指定是否对排序和分组有效。

108.count()函数可以统计某个列可用值（不包含NULL）的数量，也可以统计行数，如果在括号中指定了具体的列，那么返回的结果是该列中不包含NULL值的数据量，如果括号中是*或者某个肯定不为NULL的列，那么返回的将是总行数，并且使用COUNT(*)的时候并不是扫描所有的列以得到统计结果，而是直接获取总行数以返回结果。

109.在使用MyISAM存储引擎时，count函数有时候会比较快，这是因为如果查询不带WHERE条件，那么MyISAM可以直接返回总行数，否则，其和其他的存储引擎没有什么区别。

110.对于count(*)函数，如果WHERE条件后的条件会导致扫描很大一部分数据，那么可以考虑将WHERE条件反过来写，再使用总数（因为不带WHERE条件的统计非常快）减去该结果即可得到正确结果，如：

SELECT COUNT(*) FROM city WHERE id>5;

其可以改写为：

SELECT (SELECT COUNT(*) FROM city) - COUNT(*) FROM city WHERE id<=5;

111.如果在一条查询语句中需要统计多个不同条件的数据，可以将COUNT()语句改写为SUM()语句，比如需要通过一条语句查询不同颜色商品的数量，如果单纯使用COUNT()语句实现起来比较麻烦，那么可以使用如下SUM()语句：

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue, SUM(IF(color = 'red', 1, 0)) AS red FROM items;

对于COUNT()语句，由于其是不会统计NULL值的，那么我们也可以利用这一点改写上述查询语句：

SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;

112.在某些业务场景中并不要求完全精确的COUNT()值，此时可以使用近似值，而对于EXPLAIN语句，其在解析SQL语句时统计的时候本身就是一个近似值，并且由于其不会真正执行查询，因而成本很低。

113.优化关联查询：

确保ON或者USING子句中的列上有索引；
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程；
当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积。

114.对于使用子查询的时候，一定要思考是否该子查询可以改写为关联查询，因为即使是相同的执行计划，关联查询大多数时候都要比子查询快。

115.如果在关联查询中使用了分组（GROUP BY），并且是按照某个列进行分组，那么通常采用查找表的标识列（分组列）的效率比其他列更高。

116.在分组查询语句中直接使用非分组列通常都不是好的主意，因为这样的结果通常是不定的，或者优化器选择不同的优化策略时都会导致结果发生改变。

117.如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按需要的方向排序。

118.对于LIMIT offset, count翻页，其会扫描offset + count条数据，然后将前offset条数据舍弃掉，当offset比较大的时候，这将极大的影响查询效率，比如如下查询语句：

SELECT film_id, description FROM film ORDER BY title LIMIT 50, 5;

对于这种查询可以通过三种方式进行改进：

①由于title列建有索引，因而可以首先在title的使用覆盖索引获取第51到55行的数据id，然后通过IN()查询直接获取这几个id的数据：

SELECT film_id, description FROM film INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50, 5) AS lim ON film.film_id=lim.film_id;

②如何分页获取是连续的，那么可以获取到上次分页时的id数据，然后通过WHERE语句过滤掉该id之前或者之后的数据，这样就可以通过直接在获取的数据列表的初始或最后获取所需的数据，如：

SELECT film_id, description FROM film WHERE id > 14589 ORDER BY title LIMIT 5;

③对于分页数据较大，用户一般不会关心较后页面的数据，这里可以只是展示总的数据量，而用户可点击的翻页数可以限制在一定范围内，比如10000，如果用户确实要获取某些数据，其可以进行精确搜索来获取。

119.表分区的作用：

表非常大以至于无法放在内存中，或者只在表的最后部分有热点数据，其余的均为历史数据；
分区表的数据更容易维护；
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备；
可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等；
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

120.分区表的限制：

一个表最多只能有1024个分区；
在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式，在MySQL5.5中，某些时候可以直接使用列来进行分区；
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来；
分区表中无法使用外键约束。

121.分区表的操作原理：

SELECT查询：当查询一个分区表的时候分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应存储引擎接口访问各个分区的数据；
INSERT操作：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作；
DELETE操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作；
UPDATE操作：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

122.分区表达式中可以使用各种函数，但是其返回的值必须是一个确定的整数，且不能是一个常数。

123.如下是创建分区表的一个例子，该例子中将调用YEAR()函数的某一列作为参数传入，然后在分区函数中通过年份对数据进行分区：

CREATE TABLE sales(
  order_date DATETIME NOT NULL
)ENGINE =InnoDB PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p_2010 VALUES LESS THAN (2010),
  PARTITION p_2011 VALUES LESS THAN (2011),
  PARTITION p_2012 VALUES LESS THAN (2012),
  PARTITION p_catchall VALUES LESS THAN MAXVALUE
);

124.分区表的限制：

NULL值会使分区过滤无效。在分区函数进行计算的时候，所有非法值（如NULL）都会存储在一个分区里，如果该分区还存储有正常数据，那么其数据量会比其余的分区要大，并且在进行查询的时候，MySQL会检查该默认分区，然后再检查指定的分区，这是由于查询的时候将查询条件的非法值考虑进去了。解决这个问题的方式是建立一个默认的非法值分区，如果数据正常，那么该分区将没有数据，虽然查询会扫描两个分区，但由于该分区数据量较小，因而不会产生什么问题；
分区列和索引列不匹配。比如分区列为a，而索引列为b，那么进行索引查询的时候由于无法根据索引确定具体是哪个分区，因而还是需要对每个分区进行扫描。解决这个问题的方式就是将分区列作为索引列；
选择分区的成本可能很高。如果无法确定数据具体属于哪个分区，那么扫描数据的时候就需要对大部分的分区进行扫描，以查找符合条件的数据，因而建立分区时需要限制分区数量，一般100个左右的分区是没有问题的；
打开并锁定所有底层表的成本可能很高。在进行查询时，需要锁定住所有的分区表，这一点是在分区计算的时候进行的，因而无法通过确定分区来进行优化，处理这个问题的方式是尽量使用批量操作；
维护分区的成本可能很高。由于每个分区其实还是一个表，因而对于创建或删除分区其操作比较快，但是对于修改表结构等操作，那么其和一般的表操作类似，也就是说其会先创建一个新的分区，然后将旧分区的数据复制到新分区，最后删除旧分区；
所有分区必须使用相同的存储引擎；
分区函数中可以使用的函数表达式也有一些限制；
某些存储引擎不支持分区；
对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作；
对于MyISAM表，使用分区表时需要打开更多的文件描述符；

125.在进行分区计算时，MySQL能够将范围条件转化为离散的值列表，并根据列表中的每个值过滤分区。

126.MySQL只能在单纯的使用分区列本身的时候才能过滤分区，使用了一些函数，比如YEAR(order_date)，这种情况下是不能进行分区的。

127.在创建分区时可以使用表达式，但在查询时缺只能根据列来过滤分区。

128.若分区表是关联操作的第二张表，且关联条件是分区键，MySQL就只会在对应的分区里匹配行。

129.视图本身是一个虚拟表，不存放任何数据。

130.可更新视图是指可以通过更新这个视图来更新相关的表，只要指定了合适的条件，就可以更新、删除甚至向视图中插入数据。

131.如果视图中包含了GROUP BY、UNION、聚合函数，以及其他一些特殊情况，就不能被更新了。

132.更新视图的查询也可以是一个关联语句，但是有一个限制，被更新的列必须来自同一个表中，并且所有使用临时表算法实现的视图都无法被更新。

133.在定义视图时如果使用了CHECK OPTIOIN子句，这表示所有对视图的更新都必须符合视图本身的WHERE条件定义，也即其必须是通过该WHERE条件能够查询出来的值。

134.外键的优点在于能够保证表数据之间的一致性，其缺点在于在修改一个表的数据的时候会隐性的查询相关联的表，这在有的时候会导致一些隐性的表锁产生，比如向一个表中插入一条数据，那么相应的会查询并锁定父表的相关记录，这会导致额外的锁等待，甚至会导致一些死锁。

135.在使用外键的时候如果目的仅仅是保证数据的一致性，那么可以使用触发器来替代外键，并且外键的约束一般在应用程序中进行保证会更好。

136.存储过程的优点：

它在服务器内部执行，离数据最近，并且其可以减少语句执行的带宽和网络延迟；
这是一种代码复用，可以为业务提供统一的业务规则，保证某些行为总是一致的；
它能够简化代码的维护和版本的更新；
它能够帮助提升安全，比如提供更细粒度的权限控制；
服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗；
因为是在服务器端部署的，所以备份、维护都可以在服务器端完成；
它可以在应用开发和数据库开发人员之间更好的分工；

137.存储过程的缺点：

MySQL没有提供很好的开发和调试工具，所以编写MySQL的存储代码比其他的数据库要更难些；
较之应用程序代码，存储代码效率要稍微差些。这是因为存储代码中可供使用的函数非常少，对于较复杂的逻辑，其比较难处理；
因为存储程序部署在服务器内，因而会存在安全隐患。比如将一些非标准的加密功能放在存储程序中，那么如果数据库被攻破，数据也就被窃取了，如果这些加密功能放在应用程序中，那么攻击者必须要同时攻破应用程序和数据库才能获取数据；
存储程序会给服务器带来额外的压力，并且服务器的扩展性较应用程序差很多；
MySQL没有提供很好的存储程序资源消耗的优化策略，因而一个小的错误都可能把服务器拖死；
存储代码在MySQL中的实现有很多限制，如执行计划的缓存是连接级别的，游标的物化和临时表相同，在MySQL5.5版本之前，异常处理也非常困难等等；
调试MySQL的存储过程是一件很困难的事情；
它和基于语句的二进制日志复制合作得并不好。

138.存储代码是一种帮助应用隐藏复杂性，使得应用开发更简便的方法，不过它的性能可能更低，而且会给MySQL的复制等增加潜在的风险。

139.存储代码的限制：

优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况；
优化器无法评估存储函数的执行成本；
每个连接都有独立的存储过程的执行计划缓存，如果多个连接需要调用同一个存储过程，将会浪费缓存空间来反复缓存同样的执行计划；
存储程序和复制是一组诡异的组合。如果可以，最好不要复制对存储程序的调用，直接复制由存储程序改变的数据则会更好。

140.触发器可以让你在执行INSERT、UPDATE或者DELETE语句时，执行一些特定的操作。

141.触发器的限制：

对每一个表的每一个事件，最多只能定义一个触发器；
MySQL只支持“基于行的出发”----也就是说，触发器始终是针对一条记录的，而不是针对整个SQL语句的，如果变更的数据集非常大的话，效率会很低；
触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后，因为触发器，可能包含了很多看不见的工作；
触发器的问题也很难排查，如果某个性能问题和触发器有关，会很难分析和定位；
触发器可能导致死锁和锁等待，如果触发器失败，那么原来的SQL语句也会失败。

142.触发器并不一定能够保证原子性。对于MyISAM表，在执行触发语句的时候如果遇到问题，那么原语句是不会回滚的，而对于InnoDB，触发语句和原语句是在同一个事务中的，如果触发语句遇到问题，那么原语句也会回滚。

143.在使用触发器做数据约束校验时需要注意MVCC的作用。比如当插入一条数据时使用触发器检查写入数据的对应列在另一个表中是否存在时，需要使用SELECT FOR UPDATE，该语句会阻塞更新语句（INSERT、UPDATE和DELETE）直到当前事务提交，比如在触发器中检查某个数据值不存在，随后就会执行插入语句，但如果在这中间有另外的语句将数据修改为触发器所检查的数据，那么就会导致错误产生。

144.绑定变量的优点：