【数据库学习简短总结篇】索引设计、缓冲设置、数据访问

今天看了一些资料和博客，对一些基础知识点片段进行归纳总结如下：

【1】通常可以在以下列上建立索引，利用B树和B+树实现索引：

【1.1】主键的列
【1.2】经常需要根据范围进行搜索的列
【1.3】经常需要排序的列
【1.4】经常使用在where子句上面的列
【1.5】经常需要搜索的列

---

【2】设计缓冲区：

【2.1】每个表都表示为磁盘上的一个文件，先打开，后读取。为了加快从文件中读取数据的过程，在mysql中对打开文件进行缓存，通过 /etc/mysqld.conf 下的table_cache 进行指定，设置 table_cache =5000 调整表的缓存。
【2.2】mysqld在接收连接时会根据需要生成线程。在一个连接变化的繁忙服务器上，对线程进行缓存便于以后使用时加快最初的连接。在my.cnf 中设置thread_cache = 40 来增大线程缓存。
【2.3】关键字缓冲区保存了 MyISAM 表的索引块，理想情况下，这些块的请求来自于内存。Key_reads 代表命中磁盘的请求个数，Key_read_requests是总数。命中磁盘的读请求数除以读请求总数就是不中比率。如果每1,000 个请求中命中磁盘的数目超过 1 个，就增大关键字缓冲区。刷新key_buffer =384M 将缓冲区设置为 384MB。
【2.4】数据在进一步进行处理(例如 GROUPBY字句)之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。通过tmp_table_size和 max_heap_table_size控制临时表的最值大小，在 my.cnf 中对这两个值都进行设置
【2.5】当 MySQL必须要进行排序时，就会从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，数据就保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 很大，就刷新sort_buffer_size = 4M 将排序缓冲区设置为 4MB。
【2.6】MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，从而提高查询的速度，使查询性能得到优化

---

【3】内外存交换（代更新）

---

【4】数据访问方法

【4.1】const
常量访问，当通过表的"主键"或"非空唯一索引"进行"常量等值匹配"访问表中数据时，访问类型就是const。此访问类型只会执行一次，查询最多只会查询出1条数据，在复杂的SQL中会被优先执行并直接替换为常量，然后再执行SQL的其他部分，以提升效率。
【4.2】eq_ref
等值引用，当表之间通过等值连接(连接条件是=)访问时，且使用"主键"或"非空唯一索引"访问被驱动表时，访问类型即是eq_ref，这是效率最高的连接访问方式
【4.3】ref
普通引用，当表之间同过等值(连接条件是=或<=>)访问时，使用普通索引（未使用"主键"或"非空唯一索引"）访问被驱动表时，访问类型即是ref，这是效率比较高的连接访问方式
【4.4】ref_or_null
这种访问方式和ref类似，只是MySQL在判断连接值时会额外做一步判断连接条件上的值是否包含null。这种连接类型大部分是在处理子查询的场景中使用：
【4.5】fulltext
全文索引访问，全文索引是一种特殊的索引类型，只能建立在char，varchar或text类型的数据上，用来在大量的文本中匹配某种模式
【4.6】index_merge
全文索引访问，全文索引是一种特殊的索引类型，只能建立在char，varchar或text类型的数据上，用来在大量的文本中匹配某种模式
【4.7】unique_subquery
唯一子查询，在某些子查询场景替换eq_ref访问方法。
【4.8】index_subquery
在某些场景下替换in子查询，在查询中使用非唯一索引。
【4.9】range
索引范围扫描，根据搜索条件扫描一个范围区间的索引
【4.10】index
索引全扫描。在索引覆盖和全表扫描时会使用此访问方法：
【4.11】all
是效率最低的访问方法，但当表中数据较少时，全表扫扫描性能也可能优于索引，因为索引大部分是二次访问，随机读取，全表扫描是顺序读取

---