Java笔记之MySQL

1.MySQL的逻辑架构层（由上到下）：第一层是连接池组件，管理着所有的客户端连接和认证；第二层是管理服务和工具组件，例如备份、恢复、安全管理、集群管理和性能调优工具；第三层是SQL接口，负责之心SQL命令；第四层是查询分析器，负责解析SQL语句，并生成执行计划；第五层是优化器，对执行计划进行优化；第六层是缓存，查询缓存和查询结果都会被缓存起来，以便重用；第七层是插件式存储引擎，如InnoDB、MyISAM等，负责数据的存储和提取；最下面是物理文件，存储数据和日志。
2.每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每个新建的连接创建或者销毁线程。
3.MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序、以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。
4.优化器并不关系表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体的开销信息，以及表数据的统计信息等。
5.对于select语句，在解析查询前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
6.无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。MySQL提供两个层面的并发控制：服务器层，存储引擎层。
7.共享锁（读锁）和排他锁（写锁）。相比读锁，写锁具有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。
8.所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。常见的有表锁和行级锁。
9.MySQL的每种存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时会失去另外一些应用场景的良好支持。
10.表锁：是MySQL中最基本的锁策略，并且是开销最小的策略。它会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读/写操作。但读锁是不相互阻塞的。尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。（例如，服务器会为诸如alter table之类的语句使用表锁，而忽略存储引擎的锁机制。）
11.行级锁：可以最大程度地支持并发处理，同时也带来了最大的锁开销。行级锁只在存储引擎层实现（如InnoDB、XtraDB等），而MySQL服务器层没有实现。
12.事务的特性：原子性，一致性，隔离性，持久性。一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。
13.事务的隔离级别：未提交读，提交读，可重复读，可串行化。
14.未提交读（read uncommitted）：事务中的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，这也被成为“脏读”。
15.提交读（read committed）：大多数数据库系统的默认隔离级别（MySQL不是）。一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做“不可重读”，因为两次执行同样的查询，得到的结果可能不一样。
16.可重复读（repeatable read）：是MySQL的默认隔离级别。解决了“脏读”的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上，还是无法解决“幻读”的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。（InnoDB和XtraDB存储引擎通过多版本并发控制（Multiversion Concurrency Control，MVCC）解决了幻读的问题。）
17.可串行化（serializable）：是最高的隔离级别。通过强制事务串行执行，避免了幻读问题。可串行化级别，会在读取的每一行数据上都加锁，所以可能导致大量的超市和锁争用的问题。
18.死锁：是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一资源时，也可能会产生死锁。
19.为了解决锁死，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。死锁产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由存储引擎的实现方式导致的（锁的行为和顺序是和存储引擎相关的，以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会）。
20.事务日志：使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随即I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。这也是目前大多数存储引擎所采用的，通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。
21.如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。
22.MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的有XtraDB和PBXT。
23.MySQL默认采用自动提交（autocommit）模式。也就是说，如果不是显示地开始一个事务，则每个查询动作都被当作一个事务执行提交操作。在当前连接中，可以通过设置autocommit变量来启用或禁用自动提交模式。通过show variables like ‘autocommit’来查看当前自动提交模式的状态，通过set autommit =1来开启。1或者ON表示启用，0或者OFF表示禁用。当禁用时，所有的查询都是在一个事务中，知道显示地执行commit提交或者rollback回滚，该事务才结束，同时又开始另一个新事务。
24.有一些命令，在执行前会强制执行commit提交当前的活动事务。例如，在数据定义语言（DDL）中，如果是会导致大量数据改变的操作，比如alter table，执行前会强制执行commit。另外，lock tables 等其他语句也是如此。
25.MySQL可以通过执行set transaction isolation level命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：set session transaction isolation level read committed。
26.MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
27.如果在事务中混合使用了事务型的表和非事务型的表（例如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题。但如果该事务需要回滚，非事务型的表上的就无法撤销，这回导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果很难确定。所以，为每张表选择合适的存储引擎非常重要。
28.在非事务型的表上执行事务相关操作的时候，MySQL通常不会发出提醒，也不会报错。有时候只有回滚的时候才会发出一个警告：“某些非事务型的表上的变更不能被回滚”。但大多数情况下，对非事务型表的操作都不会有提示。
29.隐士锁定和显示锁定：InnoDB采用的是两阶段锁定协议。在事务执行的过程中，随时都可以执行锁定，锁只有在执行commit或rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。以上属于隐士锁定，InnoDB会根据隔离级别在需要的时候自动加锁。另外，InnoDB也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范，例如select … lock in share mode，select … for update。
30.MySQL也支持lock tables和unlock tables语句，这是在服务器层实现的，和存储引擎无关，它们有自己的作用，但不能代替事务处理。建议：除了事务中禁用了自动提交，可以使用lock tables之外，其他任何时候都不要显示地执行lock tables，不管使用的是什么存储引擎。
31.多版本并发控制（MVCC）：可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然各个DBMS的实现机制，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
32.MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，它们一般都同时实现了MVCC。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。
33.InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
34.在MySQL的默认事务隔离级别下（即repeatable read），MVCC的具体操作如下：对于select操作，InnoDB会根据以下两个条件检查每行记录：首先InnoDB只查找版本早于当前事务版本的数据行（即行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的；其次，行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。只有符合上述两个条件，才能返回作为查询结果。对于insert操作，InnoDB为新插入的每一行保存当前系统版本号为行版本号。对于delete操作：InnoDB为删除的每一行保存当前系统版本号作为行删除标识。对于update操作：InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作简单，性能好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的检查工作，以及一些额外的维护工作。
35.MVCC只在可重读（repeatable read）和读提交（read comitted）两个隔离级别工作，其他两个隔离级别和MVCC不兼容，因为未提交读（read uncommited）总是读取最新的数据行，而不是符合当前事务版本的数据行。而可串行化（serializable）则会对所有读取的行都加锁。
36.在文件系统中，MySQL将每个数据库（或者称为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的“.frm”文件保存表的定义。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。
37.InnoDB是MySQL的默认事务引擎，也是最重要和使用最广泛的，被设计用来处理大量的短期事务。短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和奔溃自动恢复特性，使得它在非事务型存储的需求中年也很流行。
38.InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一些列的数据文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。
39.InnoDB采用MVCC来支持高并发，并实现了四个标准的隔离级别，默认级别是可重复读（repeatable read），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙行锁定，以防止幻影行的插入。
40.InnoDB表是基于聚簇索引（聚集索引）建立的。InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，如果表上的索引较多的话，主键应当尽可能的小。InnoDB的存储格式是平台独立的，也就是说，可以将数据和索引文件从Intel平台复制到PowerPC等其他平台上。
41.InnoDB支持真正的热备份，Oracle提供的MySQL Enterprise Backup、Percona提供的开源的XtraBackup都可以，但是MySQL的其他存储引擎不支持。
42.MyISAM存储引擎提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且奔溃后无法安全恢复。尽管如此，但对于只读的数据，或者表比较小，可以忍受修复操作开，则依然继续使用MyISAM。
43.MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以“.MYD”和“.MYI”为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。要改变MyISAM表指针的长度，可以通过修改表的MAX_ROW和AVG_ROW_LENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能会需要很长的时间才能完成。
44.MyISAM的特性：加锁与并发，修复，索引特性，延迟更新索引键等。
45.加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（并发插入）。
46.修复：对于MyISAM表，MySQL可以手工或自动执行检查和修复操作（不等同于事务恢复以及系统奔溃恢复级别的意义修复）。可以通过执行check table mytable检查表的错误，如果有错误，可以通过执行repair table mytable进行修复。但是，执行表的修复可能导致一些数据丢失，而且，修复操作是非常慢的。另外，如果MySQL服务已经关闭，也可以通过myisamchk命令行工具进行检查和修复操作。
47.索引特性：对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
48.延迟更新索引键：创建MyISAM表时，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提写入性能，但是在数据库或者主机奔溃时会造成索引损坏，需要执行修复操作。该键可以在全局设置，也可以为单个表设置。
49.MyISAM压缩表：可以使用myisampack对MyISAM表进行压缩。压缩表可以极大地减少磁盘空间占用，因此可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。压缩表中的记录是独立压缩的，所以读取但行的时候并不需要去解压整个表。
50.Memory引擎：Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不想要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。Memory表支持Hash索引，因此查找操作非常快。Memory表是表级锁，因此并发写入的性能低。另外，Memory表还不支持BLOB或TEXT类型的列，并且每行长度是固定的，所以即使指定了varchar列，实际存储也会转换成char，这可能会导致部分内存的浪费。
51.MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的就是Memory表。如果中间结果大大超出了Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM表。
52.如果数据量增长达到10tb以上的级别，可能需要建立数据仓库。Infobright是MySQL数据仓库最成功的解决方案。
53.修改表的引擎：alter table mytable ENGINE=InnoDB。转换表的存储引擎，将会失去和原引擎新相关的所有特性。
54.可以使用mysqldump工具将数据导出到文件，mysqldump -uroot -proot --all-databases >/filedir/dump.sql，或者mysqldump -uroot -proot --databases db1 db2 >/firedir/dump.sql。需要注意的是，mysqldump默认会自动在create table语句前加上drop table语句。所以导出sql文件后，记得修改文件中的相关表名。
55.MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，通常考虑以下原则：更小的通常好，简单就好，尽量避免null。
56.MySQL数据类型：整数类型、实数类型、字符串类型、日期和时间类型、位数据类型bit等。
57.整数类型：tinyint、smallint、mediumint、int、bigint，分别使用8、16、24、32、64位存储空间。整数类型有可选的unsigned属性，表示不允许负值。有符号数和无符号数使用相同的存储空间，并具有相同的性能。
58.实数类型：decimal，float，double。
59.字符串类型：varchar，char。varchar类型用于存储可变长字符串，需要使用1或2个额外字节记录字符串的长度。char类型是定长的。
60.BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
61.时间和日期类型：datetime能保存大范围的值，从1001年到9999年，精度为秒。timestamp保存了从1970年1月1日午夜以来的秒数，它和unix时间戳相同。
62.MySQL的alter table 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表（但是像修改或者删除列的默认值无需创建新表，因为列的默认值实际上存储在表的.frm文件中）。一般而言，大部分alter table操作将导致MySQL服务中断。
63.加快alter table操作的速度：只修改.frm文件，快速创建MyISAM索引。
64.移除（不是增加）一个列的auto_increment属性，以及增加、移除或更改enum和set常量等操作，可能不需要重建表。
65.为了高效地载入数据到MyISAM表中，常用的技巧是先禁用索引、载入数据，然后重新启用索引（因为构建索引的工作被延迟到数据完全载入以后，此时已经可以通过排序来构建索引了）。但是，此举对惟一性索引无效。MyISAM会在内存中构造惟一索引，并且为载入的每一行数据检查惟一性，一旦索引的大小超过了有效内存大小，载入操作救护变得越来越慢。禁用/启用索引语句：alter table mytable disable keys，alter table mytalbe enable keys。disable keys 只对非惟一索引有效。
66.只修改表的.frm文件：为表创建一个新的.frm文件，然后用它替换掉原表的.frm文件。具体操作如下：创建一张具有相同结构的空表，并进行所需要的修改；执行flush tables with read lock（这将会关闭所有正在使用的表，并且禁止任何表被打开）；交换.frm文件；执行unlock tables来释放共享锁。
67.快速创建MyISAM索引的步骤如下：用需要的表结构创建一张空表，但是不包括索引；载入数据到表中以构建.MYD文件；按照需要的结构创建另外一张空表，这次要包含索引（此举会创建需要的.frm和.MYI文件）；获取读锁并刷新表；重命名第二张表的.frm和.MYI文件；释放读锁；使用repair table来重建表的索引（该操作会通过排序来构建所有索引，包括惟一索引）。
68.在MySQL中，索引可以包含一个或多个列的值。如果包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。另外，创建一个包含两个列的索引，和创建两个只包含一个列的索引是大不相同的。在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的标准，即不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
69.索引的类型：B-Tree索引，哈希索引，空间数据索引，全文索引等。
70.索引的优点：索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随即I/O变为顺序I/O。
71.判断一个索引是否适合查询的“三星系统”：索引将相关的记录放到一起则获得一行；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。
72.B-Tree索引：使用B-Tree数据结构来存储数据，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
73.存储引擎以不同的方式使用B-Tree索引，性能也各有不同。例如MyISAM使用 前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
74.B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了执行子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下下限。叶子节点比较特表，它们的指针指向的是被索引的数据，而不是其他的节点页。树的深度和表的大小直接相关。
75.B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。索引对多个值进行排序的依据是定义索引时列的顺序。
76.B-Tree索引适用的查询类型：全键值（全值匹配），键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。全值匹配指的是和索引中的所有列进行匹配。
77.使用B-Tree索引的限制：如果不是按照索引的最左列开始查找，则无法适用索引；不能跳过索引中的；如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查询。
78.哈希索引（hash index）：基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code）。哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
79.在MySQL中，只有Memory引擎显示支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非惟一哈希索引的，这在数据库世界里面是与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
80.对于哈希索引而言，因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。
81.哈希索引的限制：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值；哈希索引只支持等值比较查询，包括=、in()、<=>（与<>不同），不支持任何范围查询；访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列却又相同的哈希值），当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐步进行比较，直到找到所有符合条件的行；如果哈希冲突很多的话，一些索引维护操作代价也会很高。
82.InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的很频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，该行为属于InnoDB内部完全自动行为，用户无法控制或配置，但可以选择关闭。
83.空间数据索引（R-Tree）：MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数（如mbrcontains()）等来维护数据。
84.全文索引：是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于match against操作，而不是普通的where条件操作。
85.高性能的索引策略：独立的列，前缀索引和索引选择性，多列索引，选择合适的索引列顺序，聚簇索引（聚集索引），覆盖索引，使用索引扫描来做排序，压缩（前缀压缩）索引，冗余和重复索引，为使用的索引，索引和锁等。
86.通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中，“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
87.查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。例如，有些查询会请求超过实际需要的数据，然后这些多于的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。典型场景有：查询不需要的记录、多表关联时返回全部列、总是取出全部列、重复查询相同的数据等。
88.在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描来过多的数据。对于MySQL，最简单的衡量查询开销的三个指标为：响应时间，扫描的行数，返回的行数。
89.响应时间：包括服务时间和排队时间。服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能等I/O操作完成，也可能是等待行锁等等。
90.扫描的行数和返回的行数：分析查询时，查看该查询扫描的行数是非常有用的。这在一定程度上能够说明该查询找到需要的数据的效率高低。理想情况下，扫描的行数和返回的行数应该是相同的。
91.扫描的行数和访问的类型：在explain语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、惟一索引查询、常数引用等。以上类型的访问，速度是从慢到快，扫描的行数也是从多到少。
92.重构查询的方式：一个复杂查询还是多个简单查询，切分查询，分解关联查询。
93.explain中的列：id，select_type，table，type，possible_keys，key，key_len，ref，rows，filtered，Extra。
94.id列：标识select所属的行。如果在语句中没有子查询或联合，那么只会有唯一的select，于是每一行在这个列中都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置。
95.select_type列：这一列显示了对应行是简单还是复杂select。
96.table列：这一列现实了对应行正在访问哪个表。
97.type列：访问类型。all-全表扫描；index-按索引次序进行的全表扫描；range-范围扫描，即一个有限制的索引扫描；ref-索引访问，返回所有匹配某个单值的行；eq_ref：最多返回一条符合条件的索引查找；const,system-MySQL对查询的某部分进行优化并将其转换成一个常量；NULL-意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
98.possible_keys列：查询可以使用哪些索引，或者说哪一个索引能有助于高效地行查找。
99.key列：MySQL决定采用哪个索引来优化对该表的访问，或者说优化采用哪一个索引可以最小化查询成本。
100.key_len列：在索引里使用的字节数。
101.ref列：之前的表在key列记录的索引中查找值所用的列或常量。
102.rows列：为了找到所需要的行而要读取的行数。
103.filtered列：在使用explain extended时出现。显示的是针对表里符合某个条件（where子句或连接条件）的记录数的百分比所做的一个悲观估算。
104.Extra列：不适合在其他列显示的额外信息。Using index-表示MySQL将使用覆盖索引，以避免访问表；Using where-意味着MySQL服务器将在存储引擎检索后再进行过滤；Using temporary-意味着MySQL对查询结果排序时会使用一个临时表；Using filesort-意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行；Range checked for each record(index map:N)-意味着没有好用的索引，新的索引将在连接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的。