1.1 MySQL逻辑架构
MySql服务器逻辑架构图
1、连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。
当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。
２、优化与执行
MySql会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户知道服务器时如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。（sql优化）
如：对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
1.2 并发控制
无论何时，只要有多个查询需要再同一时刻修改数据，都会产生并发控制的问题
1、读写锁
共享锁（share lock），也叫读锁（read lock）
读锁是共享的，相互不阻塞的。多个线程在同一时刻可以读取同一个资源，而相互不干扰
排他锁（exclusive lock），也叫写锁（write lock）
写锁是排他的，一个写锁会阻塞其他的读锁和写锁，只有这样才能确保在给定的时间里，只有一个线程能执行写入，并阻止其他线程读取正在写入的同一资源
在实际的数据库系统值，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySql会通过锁定防止其他用户读取同一数据，大多数时候，MySql锁的内部管理都是透明的。
1.2 锁粒度
一种提高共享资源并发性的方式就是让锁定资源对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是只对修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
加锁也需要消耗各种资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是读取数据，那么系统的性能可能会因此受到影响。
所谓的锁策略，就是在锁的开销和数据的安全性之间追求平衡，这种平衡当然也会影响到性能。
表锁（table lock）
表锁是MySql开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作（插入、修改、删除）前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。
写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面，而读锁不能插入到写锁的前面。
如服务器会为ALTER TABLE之类的语句使用表锁
行锁（row lock）
行锁可以最大程度的支持并发处理（同事也带来了最大的锁开销），行级锁只在存储引擎层实现。
1.3 事务
事务就是一组原子性的SQL语句。如果数据库引擎能够全部成功执行该组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所欲语句都不会执行（执行过的回滚）。也就是说事务内的语句，要么全部执行成功，要么全部执行失败。
事务的四大特性
原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元（可联想化学中的原子是最小单位，不
可分割），整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
一致性（consistency）：数据库总是从一个一致性状态转换成另外一个一致性状态。如转账扣钱后加钱失败，扣
钱也会回滚，事务中的修改不会保存到数据库中
隔离性（isolation）：通常来说，一个事务所作的修改在最终提交之前，对其他事务是不可见的。
持久性（durability）：一旦事务提交，则其所作的修改就会永久保存到数据库中。即使系统崩溃，修改的数据也不会丢失。
隔离级别
读未提交（READ UNCOMMITED）：事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未
提交的数据，这也被称为脏读（Dirty Read）。这个隔离级别会导致很多问题，一般不会使用。
读已提交（READ COMMITED），也叫不可重复读：只能读取到其他事务已经提交的数据，即一个事务从开始到
提交之前，所做的修改对其他事务都是不可见的。所以两次执行同样的查询，可能会得到不一样的结果
可重复读（REPEATABLE READ）：
该隔离级别保证了在同一个事务中多次读取同样的记录结果是一致的。解决了脏读的问题，但是无法解决幻读
的问题，所谓幻读，指的是当某个事务在读取某个范围内的记录时，会产生幻行，即两次读取的数据行不
一致。InnoDB使用多版本并发控制MVCC来解决幻读问题。
可重复读是MySql默认的隔离级别
可串行化（SERIALIZABLE）：最高的隔离级别，强制事务串行执行，避免了脏读和幻读的问题，但是会在读取
的每一行数据上都加锁，可能导致大量的超时和锁竞争的问题，性能很低，所以很少用此隔离级别
1.3.2 死锁
死锁是指两个或多个事务在同意资源上相互占用，并请求锁定对方占用的资源（相互请求锁定对方已经锁定占
用的资源），从而导致恶性循环等待的现象。
多个事务视图以不同的顺序锁定多个相同的资源时，可能导致死锁；多个事务同时锁定相同的资源时，也会产
生死锁。
如：两个事务以不同的顺序同时修改两行记录，两个事务分别在修改了第一行记录后，也锁定了该行记录，同
时尝试去修改第二条记录时，发现已被对方锁定。这时两个事务都等待对方释放锁，同时又持有对方需要的锁
，则陷入死循环，造成死锁。
为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。InnDB能检测到死锁的循环依赖，并立即返回一个错误。
InnDB目前处理的死锁方法是，将持有最少行级排他锁的事务进行回滚。
1.4 多版本并发控制
MySQL中的大多数事务存储引擎实现的都不是简单的行级锁，同时基于并发性能的考虑，它们一般都同时实现了多版本并发控制（Multiy Version Concurrent Control）MVCC。
MVCC同样实现了行级锁的功能，但是它在很多情况下避免了加锁的操作，因此开销更低。
MVCC是通过保存数据在某个时间点的快照来实现的。即根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。但是对每个事务来说，在它的执行期间，不管它执行多长时间，它看到的数据都是一致的。
存储引擎实现MVCC典型的有乐观并发控制（乐观锁）和悲观并发控制（悲观锁）
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建系统版本号，一个保存行的过期（或删除）版本号。
每开始一个新的事务，所有记录的系统版本号（应该是创建系统版本号）都会递增。事务开始时刻的系统版本号会作为当前事务的版本号（疑问：也就是说每行版本号都相同？）用来和查询到的每行记录的版本号进行比较（在事务执行期间，可能会有部分记录行）。下面是可重复读隔离级别下，InnoDB实现MVCC的具体操作：
SELECT：
1、InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号）
，这样可以确保事务读取的行，要么在事务开始前已经存在的，要么是事务自身插入或修改过的（这是执行插
入和修改系统版本号未变）。
2、行的删除版本号要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前是
存在的，未被删除。
只有满足这两个条件的记录，才能返回作为查询结果
INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号
DELETE：InnoDB为删除的每一行保存当前系统版本号作为删除版本号
UPDATE：删除原来的行插入当前系统版本号作为删除版本号，并插入一条新的记录，保存当前系统版本号作为行版本号
MVCC只在REPEATABLE READ和READ COMMITED中工作，因为READ UNCOMMITED总会读取最新的数据行，而SERIALIZABLE会对所有读取的行加锁。
Schema和数据类型优化
1、选择优化的数据类型
MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要，不管存储哪种类型的数据，需要遵循下面几个原则。
1、更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型（假如只需存0-200，tinyint unsigned更好。无符号的，没有负值。范围在0-255），更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
2、简单就好
简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。如：使用MySQL自建的类型而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址
3、尽量避免NULL
很多表都包含可为NULL（空值）的列，因为可设NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。如果查询中可以包含为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题，但是应该计划在列上建索引，就应该避免设计成可为NULL的列。（但是InnoDB使用单独的位bit存储NULL值，所以对稀疏数据（很多值为NULL，只有少数行的列有非NULL值）有很好的空间效率）
在为列选择数据类型时，第一步需要确定合适的打类型：数字、字符串、时间等。下一步是选择具体类型：很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型也有一些特殊的行为和属性。例如：DATETIME和TIMESTAMP都可以存储相同类型的数据：日期和时间，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化啊，具有特殊的自动更新能力，另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。
1、整数类型
有两种类型的数字；整数和实数。如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8,16,24,32,64位存储空间。他们可以存储的值的范围从-2^n-1到-2^n-1 -1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(28)是相同的。
2、实数类型
实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。
浮点（FLOAT，DOUBLE）和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL5.0及之后的版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字），例如；DECIMAL(18,9)（长18位小数点后精确9位）小数点两边将各存9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占用一个字节。
DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型，或者存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类I型那个，不指定精度。浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT中，这样可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
3、字符串
VARCHAR和CHAR是两种最主要的字符串类型。
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。但是如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或者等于255个字节，则只使用1个字节表示，否则使用2个字节。但是由于VARCHAR行是变长的，在UPDATE时可能使得行变得比原来更长，如果页内没有更多的空间存储，InnoDB需要分裂页来使行可以放进页内。
下面这些情况使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题。
CHAR 类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串，或者所有值都接近同一个长度；例如MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生 碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，如CHAR(1)用来存储Y/N值，但是VARCHAR(1)需要两个字节。
4、BLOB和TEXT类型
用来存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
BLOB没有排序规则或字符集，TEXT类型有字符集和排序规则。
5、日期和时间类型
MySQL能存储的最小时间粒度为秒，但是MySQL也可以使用微秒级的粒度进行临时运算。
MySQL提供两种相似的日期类型 DATETIME和TIMESTAMP
DATETIME能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值。
TIMESTAMP保存从1970年1月1日午夜以来的秒数，和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小的多：只能表示从1970年到2038年。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。
应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。
范式和反范式
在范式化的数据库中，每个事实数据会出现并且只出现一次。相反在范式的数据库中，信息是冗余的，可能会存储在多个地方。
创建高性能的索引
一、索引的类型
1、B-Tree索引（实际是B+Tree）：使用B-Tree数据结构来存储数据。
B-Tree通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。
B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，直到找到为止。
可以使用B-Tree索引的查询类型
全值匹配：查找条件和索引中的所有列进行匹配
匹配最左前缀：查询条件是索引的第一列
匹配列前缀：查询条件是索引第一列的开头的部分，模糊查询
匹配范围值：使用索引列作为查询范围的条件
精确匹配某一列并范围匹配另外一列：即查询条件一个是索引第一列全匹配，索引第二列开头部分前缀匹配
只访问索引的查询：查询的列和条件只需要根据索引即可查询到，不需要访问叶子结点
因为索引中的节点是有序的，索引除了按值查找之外，order by的列也可以使用索引（按顺序排序）。即如果B-Tree索引可以按照某种方式查找到值，也可以按照这种方式进行排序。
B-Tree索引的限制：
1、如果不是按照索引的最左列开始查找，则无法使用索引（最左原则）
2、不能跳过索引中的列，即索引是abc，ac作为条件不会使用索引（最左原则）
3、如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。
因此索引中列的顺序很重要，在优化性能的时候可能使用相同的列但顺序不同的索引来满足不同类型的范围查找。
2、哈希索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效。
3、空间数据索引（R-Tree）
4、全文索引
查找文本中的关键词，而不是直接比较索引中的值。
二、索引的优点
索引可以让服务器快速定位到表的指定位置，但并不是唯一作用。最常见的B-Tree索引，按照索引列顺序存储数据，索引还可以用来ORDER BY和GROUP BY操作。另外，索引中存储了存储了索引列的实际值，所以某些查询只使用索引就能够完成全部查询。总结有下面三个优点：
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机I/O变为顺序I/O
索引是否适合某个查询的三星系统：1、索引将相关的记录放到一起则获取一星 2、如果索引中的数据顺序和查找中的排列顺序一致则获得二星 3、如果索引中的列包含了查询中需要的全部列则获得三星。
三、高性能的索引策略（重要）
sql优化中的索引优化技巧，如何避免优化器放弃使用索引
1、独立的列
如果查询条件中的列不是独立（索引列不能是表达式的一部分也不能是函数的参数）的，则MySQL就不会使用该列的索引。
2、前缀索引和索引选择度
有时候需要索引很长的字符串，但会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，
从而提高查询效率。索引的选择度是指，索引列中的不重复的值占数据表记录总数（T）的比值（范围1/T - 1）之间，索引的选择度越高，则过滤掉的数据行越多，查询效率越高。唯一索引的选择度最
高为1，因为索引列中的每行值都不同，性能最好。
一般情况下某个列前缀的选择度也是足够高的，足以满足查询的性能，对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。
创建前缀索引要选择足够长的前缀以保证较高的选择度，使前缀索引的选择度接近于索引整个列的选择度，同时又不能太长（节省存储空间）。为了决定前缀的合适长度，需要逐渐尝试修改前缀长度直到这个前缀的选择度接近于这个完整列的选择度。
如何创建前缀索引：alter table tablename add key(cloumnname(len)) len为前缀长度
3、多列索引（组合索引、联合索引）
对于有多个查询条件的SQL语句，每个查询条件都创建一个单列索引的话
MySQL5.0之前的版本只会使用其中某一个单个索引（由于和全表扫描/只使用一个索引的速度比起来，去分析两个多个索引B-Tree更加耗费时间，所以绝大多数情况下数据库都是只用一个索引）
MySQL5.0之后的版本引入了一种叫 “索引合并（index merge）” 的策略，一定程度上可以使用表的多个单列索引来定位指定的行。（分别使用单个索引进行扫描，并将结果进行合并，根据条件的限制选择：1、OR条件的联合（union） 2、AND条件的相交（intersection） 3、组合前两种情况的联合及相交。在explain的Extra列可以看到）
索引合并
1、当出现对多个索引列做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的联合索引，而不是多个独立的单列索引。
2、当出现需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上，特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候
3、优化器不会把这些计算到 查询成本（cost）中，优化器只关心随机页面读取，会使得查询成本被低估，导致该执行计划还不如走全表扫描。
如果在explain中看到有索引合并，就需要考虑一下查询和表的结构，需要进行优化了。
因此，对于多个查询条件的sql语句，在多个列上建立独立的单列索引在大部分情况下并不能提高MySQL的查询性能，好的选择是建立联合索引来提高查询性能。
4、多列索引下选择合适的索引列顺序
B-Tree索引按顺序存储数据。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISINCT等字句的查询需求。
对于如何选择索引列的顺序的经验法则是：将选择度高的列放到索引的最前列（但不是绝对），当不考虑排序和分组时，将选择度高的列放在前面通常是友好的，这时候索引的作用只是用于优化where条件的查找。在这种情况下，这样设计的索引确实能够更快的过滤出需要的行，对于where子句中只使用了索引部分前缀的查询来说选择度也更高。然而，性能不只是依赖于所有索引列的选择度，也和查询条件的值的分布有关，也可能根据哪些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性更高。（即某一列的选择度不高，但是查询频率较高，其他sql查询也用这个列作为查询条件，那么将这列放在联合索引的最前列更好一些）
5、聚簇索引
聚簇索引不是一种单独的索引类型，而是一种数据存储方式。聚簇索引表示将索引和数据行存储在一个文件中。当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中。（因为无法把数据行存放在两个不同的地方，因此一个表只能有一个聚簇索引）
InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行（叶子页包含了行的全部数据，但节点页只包含了索引列）。
InnoDB默认通过主键聚集数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有，会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。
聚簇索引（聚集数据）的优点：
1、可以将相关数据保存在一起
2、数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
3、使用覆盖索引扫描的查询可以直接使用页结点中的主键值
聚簇索引（聚集数据）的缺点：
1、聚簇索引最大限度的提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了
2、插入速度严重依赖于插入顺序。按照主键的顺序插入是插入数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序插入数据，那么在插入完成后最好使用OPTIMIZE TABLE命令重新组织一下表（调整节点）。
3、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page spilt）的问题（增加节点）
当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面类容纳该行，页分裂会导致表占用更多的磁盘空间。
5、聚簇索引可能导致全表扫描很慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6、二级索引（非聚簇索引）可能比想象中的更要大，因为二级索引的叶子结点包含了该行的主键列
7、二级索引访问需要两次索引查找（二级索引叶子节点保存的不是执行行的物理位置的指针，而是行的主键值）
MyISAM和InnoDB数据分分布对比
MyISAM和InnoDB索引的数据结构都是B+Tree，但是MyISAM的索引是非聚集的，InnoDB的索引是聚集的
MyISAM按照数据插入顺序存储在磁盘上，在每一行上标注了行号，按顺序递增。索引中的每个叶子节点都包含行号。 将索引和关联数据存储在不同的文件中，根据索引找到叶子节点中的行号，在根据行号去取对应的数据，二级索引也是同样。
InnoDB，在InnoDB中，聚簇索引就是表，不需要向MyISAM那样独立存储数据行。聚簇索引的每一个叶子节点都包含了主键值、事物ID、用于事物和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
另外InnoDB还有一点和MyISAM不同，InnoDB的二级索引和聚簇索引很不相同，InnoDB的二级索引的叶子节点存储的不是“行”指针，而是主键值（二级索引的叶子节点包含了索引列值和主键值）。并以此作为指向数据行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当二级索引会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引的这个“指针”。
在InnoDB表中按主键顺序插入行
如果正在使用InnoDB表并且没什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键（聚簇索引），这种主键的数据应该和应用无关，最简单的方式是使用AUTO_INCREMENT自增列。这样可以保证数据行是按照顺序写入，对于根据主键做关联的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于IO密集型的应用。从性能的角度考虑， 使用UUID来作为聚集索引则会很糟糕，它使得聚簇索引的插入变得完全随机，使数据没有任何聚集特性。
而且使用UUID主键插入数据花费的时间变长，另外索引占用的空间也更大。这是因为主键的字段更长，还有页分裂和碎片导致的。下面来看一下使用自增主键和UUID主键的插入数据的过程：
自增主键：因为主键的值是顺序的，因此数据行被顺序插入到每一页（InnoDB一页默认16KB）中，每条新纪录总是插入在前一条记录的后面，当页被插满后（达到页的最大填充因子，默认是页大小的15/16，留出空间用于以后修改），继续顺序插入到新的页。
UUID主键：因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新行寻找合适的位置，可能插入到已有数据的中间的位置，导致需要强制移动之前的记录，这会导致：
1、写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘中读取目标页到内存中，这将导致大量的随机IO
2、因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个
3、由于频繁地页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
因此，使用InnoDB时应尽可能的按主键顺序插入数据，并且尽可能的使用单调增加的聚簇键的值来插入新行。
但是顺序的主键可能会造成更坏的结果：
1、对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用，可能导致间隙锁竞争
2、AUTO_INCREMENT锁机制
解决方案：更改innodb_autoinc_lock_mode配置。？？？待理解
6、覆盖索引
通常我们都会根据where后的查询条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。MySQL也可以使用索引来直接获取列的数据，而不需要读取数据行。如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为”覆盖索引“。因此覆盖索引能够极大提高性能。下面是覆盖索引的好处：
1、索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。
2、因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。
3、对于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
当一个查询使用覆盖索引时，在explain的Extra列可以看到“Using index”的信息。即便查询没有where条件，只是查询的列有索引覆盖的话也会使用覆盖索引。
不过索引覆盖查询也有很多陷阱可能会导致无法实现优化，MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了where条件中的字段，但不是整个查询涉及的字段。如果条件为假（false），MySQL5.5和更早的版本也总是会回表取数据行，尽管并不需要这一行且最终会被过滤掉。为什么会发生这种情况以及如何解决掉？如：explain select * from products where actor='carrey' and title like '%apollo%';
结果为：Extra：Using where
这里索引无法覆盖该查询，有两个原因：
1、没有任何索引能够覆盖查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上MySQL还有一个捷径可以利用：where条件中的列是有索引可以覆盖的，因此MySQL可以使用该索引找到对应的actor并检查title是否匹配，过滤之后再读取需要的数据行。
2、MySQL不能再索引中执行like操作。这是底层存储引擎API的限制，MySQL5.5和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于）。MySQL能在索引中做最左前缀匹配的LIKE（xxx%）比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE（%xxx）查询，存储引擎就无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。
也有办法解决上面的问题，需要重写查询并巧妙设计索引。先将索引拓展至三个数据列（actor，title，prod_id），然后修改查询sql为：
select * from products
join (select prod_id from products where actor='carrey' and title like '%apollo%') as t1
on t1.prod_id=products_id;
这种方式称为延迟关联（deferred join），因为延迟了对列的访问。在查询的第一阶段（JOIN表的查询）MySQL可以使用覆盖索引，在FROM子句中的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配需要的所有列值。虽然无法使用索引覆盖整个查询，但总比完全无法利用索引覆盖的好。
在大多数存储引擎中，覆盖索引只能覆盖哪些只访问索引中部分列的查询。不过，InnoDB的二级索引的叶子节点中都包含了主键值，这意味着InnoDB的二级索引可以有效的利用这些额外的主键列来覆盖查询。如下面的sql：
select actor_id,last_name from actor where last_name='xxx'; key:idx_actor_last_name Extra:Using where ,Using index
last_name 列有二级索引，虽然该索引列不包含主键actor_id，但也能够用于对actor_id做覆盖查询。只需查询last_name二级索引就能完成查询，而不用再查询聚集索引读取所有数据行。
7、使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：
1、通过排序操作 2、按索引顺序扫描
如果explain出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引的列不能覆盖查询所需要的所有的列，那么就不得不每扫描一条索引记录（拿到主键）就都要回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在IO密集型的工作负载时（高并发时）。
MySQL可以使用同一个索引既满足排序，又用于查找行。因此设计索引时应尽可能满足这两个任务，是最好的。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有的列排列方向都是倒序或者都是正序时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一张表时（MySQL优化器默认JOIN表为第一张表），才能够使用索引。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则MySQL都需要执行文件排序（filesort）操作，而无法使用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，如果where子句或者ORDER BY子句中对这些列指定了常量，就可以使用索引进行排序。如：表rental表有组联合索引idx_rental(rental_date,inventory_id,customer_id)，则sql：select xxx from rental where rental_date='2019-04-14' ORDER BY inventory_id,customer_id；可以使用索引idx_rental排序。即使ORDER BY子句不满足索引的最左前缀的要求，这是因为索引的第一列被指定为了一个常数。
疑问？需要where子句的列和order by列使用的是同一个索引吗？？？看书上的例子是的，有待验证。
8、压缩（前缀压缩）索引
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能。
9、冗（rong）余和重复索引
MySQL允许在相同的列上创建多个索引，但是MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个的进行考虑，这样会影响性能。
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引，应该避免创建重复索引，发现后应立即移除。
如：用户在主键列上创建了唯一索引，则创建了两个重复的索引。事实上，MySQL的唯一限制和主键限制都是通过索引来实现的。
冗余索引指如创建了索引(A,B),再创建索引A就是冗余索引，因为后一个索引是前一个索引的前缀索引。
冗余索引通常发生在为表添加新索引的时候。因此在添加新索引的时候要先考虑是扩展已有的索引还是直接创建新的索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。（索引太大导致单个节点存储的关键词变少，节点数变多）
由于表中的索引越多，插入的速度会越慢。一个索引对应一个B+树，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢（需要维护和更新索引树），特别是当新增索引后导致达到了内存瓶颈的时候。
10、未使用的索引
除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这些索引完全是累赘，发现立即删除。
11、索引和锁
索引可以让查询锁定更少的行。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销；其次，锁定超过需要的行会增加锁争用并减少并发性。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数（因为无需全表扫描），从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才会有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：因为InnoDB已经锁定了这些行，到适当时候才会释放。在MySQL 5.1之前的版本中，InnoDB只有在事务提交后才能释放锁。之后的版本，InnoDB可以在服务端过滤掉行后就释放锁。
即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话可能会更糟糕，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。
关于InnoDB、索引和锁有一些鲜为人知的细节：InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢得多。
实战：
1、有时为了复用索引需要额外添加多余的条件，然后包含该列的所有分布值，前提是该列的值分布有限，如性别
2、组合索引遇到有范围条件列，则之后的列则无法使用索引，可以使用IN来代替范围查询（前提是In包含的值不多），但不是所有的范围查询都可以转换。这里有一个基本原则是，尽可能将需要做范围查询的列放在索引的最后面，以便优化器可以使用尽可能多的列。
3、避免多个范围条件（什么是范围条件，explain很难区分MySQL是要查询范围值（>,<,between），还是查询列表值、多个等值查询(in)。这两种查询explain的type列都显示range。但是这两种查询的效率是不同的，对于范围查询，MySQL无法再使用范围列后面的其他索引列了，但是对于多个等值条件查询则没有这个限制）
4、优化排序，使用文件排序filesort对小数据集是很快的，但如果一个查询匹配的结果有上百万行会怎样？对于哪些选择性非常低的列，可以增加一些特殊的索引来做排序，如下面的sql查询可以创建 (sex,rating)索引；
select xxx from profiles where sex='M' order by rating LIMIT 10;
但是如果翻页到比较靠后时查询也可能缓慢，如：LIMTI 100000,10; 因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式，预先计算和缓存可能是解决这类查询的策略，另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这样可以减少MySQL扫描那些需要丢弃的行数。如：
select xxx from profiles INNER JOIN select <primary key col> FROM profiles where x.sex='M' ORDER BY rating LIMIT 100000,10) as x USING (<primary key col>);
维护索引和表
即使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护转缺的索引统计信息，减少碎片。
1、找到并修复损坏的表
表损坏（corruption）是很糟糕的事情。对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。
损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果遇到了古怪的问题，可以尝试运行CHECK TABLE来检查是否发生了表损坏。CHECK TABLE能找到大多数的表和索引的错误。可以使用REPAIR TABLE命令来修复损坏的表，但不是所有的存储引擎都支持。
2、更新索引统计信息
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息。以决定如何使用索引。
第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如MyISAM，但对于InnoDB返回的是估算值。
第二个API是info(),该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）
3、减少索引和数据的碎片
B-Tree索引可能会导致碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据涉及，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、覆盖扫描等操作来说，速度可能会降低很多倍；对于索引覆盖扫描这一点更加明显。
表的数据存储也可能导致碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。
行碎片：这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
对于MyISAM表，这三类碎片都可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片段中。
可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。新版本的InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。
总结
在选择索引和编写利用这些索引的查询中，有如下三个原则始终需要记住：
1、单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机IO要快很多，不过还是相对慢）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多的所需要的行。使用索引可以创建位置引用以提高效率。
2、按顺序访问范围数据是很快的，这有两个原因。第一，顺序IO不需要多次磁盘寻道，所以比随机IO要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按组进行聚合计算了。
3、索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问（单行访问是很慢的）。
总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。
如何判断一个系统创建的索引是合理的呢？一般来说，建议按照响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机IO访问数据，或者是有太多回表查询哪些不在索引中的列的操作
如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。如果不行，也可以看看是否可以重写该查询，将其转化成一个能够高效利用现有所有或者新创建多音的查询
查询性能优化
查询优化、索引优化和库表结构优化需要齐头并进，一个不落。
一、为什么查询速度会慢
查询真正重要的是响应时间，如果把查询看作是一个任务，那么它有很多子任务组成，优化查询实际上要优化其子任务，要么消除其中的一些子任务，要么减少子任务的执行次数，要么让子任务运行地更快。
首先需要对查询进行剖析，明确查询所执行的子任务。通常来看，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。
其中执行是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗的时间，根据存储引擎的不同，可能还会产生大量的上下文切换以及系统调用。
在每一个消耗大量时间的查询案例中，我们要从中寻找一些不必要的操作、某些操作被额外地重复了很多次、某些查询执行的太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。
二、慢查询基础：优化数据访问
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量的数据。大部分性能低下的查询都可以通过减少不必要的访问的数据量的方式进行优化。对于低效的查询，下面两个步骤来分析总是很有效“
1、确认应用程序是否在检索大量超过需要的数据。它以为着访问了太多的行，但是有时候也可能是访问了太多的列
2、确认MySQL服务器层是否在分析大量超过需要的数据行。
1、是否向数据库请求了不需要的数据
查询请求不是实际需要的数据应该丢掉。否则会给MySQL服务器带来额外的负担，并增加网络开销，也会增加应用服务器的CPU和内存资源。如：
（1）查询不需要的列：使用分页在查询后加上LIMIT
（2）多表关联时返回全部列：只取需要的列，别用SELECT *
（3）总是取出全部列：别用SELECT *：取出全部列会让优化器无法使用覆盖索引来优化查询，还会为服务器带来额外的IO、内存和CPU消耗。
（4）重复查询相同的数据：避免重复执行相同的查询，可通过在第一次查询后缓存查询结果，下次查询直接查询缓存即可。
2、MySQL是否在扫描额外的记录
在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据行。MySQL最简单的衡量开销的三个指标如下：
（1）响应时间
（2）扫描的行数
（3）返回的行数
这三个指标都会记录到慢日志中，所以检查慢日志是找出扫描行数过多的查询的好办法。
响应时间：
响应时间只是一个表面的值，是服务时间和排队时间之和。
服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间-可能是在等IO操作完成，也可能是等待行锁等等。
当看到一个查询的响应时间的时候，首先判断是不是一个合理的值。如何判断呢？可通过”快速上限估计“法来估算。即了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多个顺序和随机IO，再乘以再具体硬件下一次IO的消耗时间，最后把这些消耗都加起来，就可以获得一个大概的参考值来判断当前的响应时间是不是一个合理的值。
扫描的行数和返回的行数：
理想条件下扫描的行数和返回的行数应该是相同的，但实际中很少遇到。explain可以看到扫描的行数
扫描的行数和访问类型
评估查询开销的时候，需要考虑从表中找到一行数据的成本。MySQL有好几种方式查询并返回一行结果，有的需要扫描很多行，有的甚至不需要扫描。
EXPALIN的type列反应了访问类型，有很多种，从全表扫描（ALL）到索引扫描（index）、范围扫描（range）、唯一索引查询（eq_ref）、常数引用(const)等。
如何查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
1、使用索引覆盖扫描，把需要用的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了
2、改变库表结构。例如使用单独的汇总表
3、重写复杂查询，让MySQL优化器能够以更优化的方式执行这个查询
三、重构查询的方式