FORCE INDEX

SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b=2;

使用FORCE INDEX可以指定优化器选择某个索引进行查询。

一、使用show variables 和show status
命令查看MySQL的服务器静态参数值和动态运行状态信息。

 

B+树索引的使用

八、innodb_lock_wait_timeout 出现死锁时等待指定时间后回滚。

1):union,不过这种查询速度比较慢

Cardinality

并不是所有在查询条件中出现的列都需要添加索引,对于什么时候添加B+树索引,一般的检验是,在访问表中很少一部分行是使用B+树索引才有意义。查看索引是否是高选择性的,可以通过SHOW
INDEX语句中的Cardinality列来观察。Cardinality是一个估计值,在实际中,Cardinality/n_rows_in_table应尽可能接近1,如果非常小,那么需要考虑是否还要建这个索引。

InnoDB中如何统计Cardinality值,索引的更新可能非常平凡,如果每次更新操作时就统计Cardinality值,那么对数据库会带来很大负担。InnoDB存储引擎中,Cardinality统计发生在INSERT和UPDATE操作中,不过并不会每次都去统计,它的策略是:

  • 表中的1/16的数据已发生变化。
  • stat_modified_counter > 2000000000。

stat_modified_counter是数据库发生INSERT和UPDATE操作的计数器,如果每次对表中的一行数据更新操作,表中的数据几乎不会有变化,那么第一种策略就无法试用。所以计数器用来统计操作的次数,如果满足条件,也会统计Cardinality值。

InnoDB存储引擎只对8个叶节点进行采样。采样的过程为:

  1. 取得B+树索引中叶节点的数量,即为A。
  2. 随机取B+树索引中的8个节点。统计每页中不同的记录的个数,即为P1,P2,…,P8。
  3. 根据采样的信息给出Cardinality值预估值:Cardinality =
    (P1+P2+…+P8)* A / 8。

注意:如果表足够小,表的叶节点小于或等于8个,这时即使随机采样,也总是采取到这些页,因此每次的Cardinality值都是一样的。

  默认为1,最安全。

有时候在sql遇到一次查询多张表的全部数据例如:创建一张虚拟表A
,表A中需要有表B和表C的全部数据(表B和表C并集,如图)

覆盖索引

InnoDB存储引擎支持覆盖索引,即从辅助索引就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减小大量的IO操作。

对于InnoDb存储引擎的辅助索引而言,由于其中包含了主键信息,因此其叶子节点放的数据为(primary
key1, primary key2,…, key1,
key2,…)。对于下面的可以仅用一次辅助联合索引来完成查询。

  • SELECT key2 FROM table key1 = xxx;
  • SELECT primary key1, key2 FROM table WHERE key1 = xxx;
  • SELECT primary key1, primary key2, key2 FROM table WHERE key1 = xxx;

简单的说,如果查询的列,正好是我们设置的主键或者键(建立聚合索引的键),如上图展示的,辅助索引的叶子节点的值就是所要查询的内容,就不必取到键值后再去聚合索引进行二次查找。可以想到如果想要更多的使用覆盖索引这一特性,需要将我们需要的列,都建立联合索引。

联合索引和覆盖索引有很大的区别:

覆盖索引是查询的列可以直接通过索引提取,比如只查询主键的列!或者查询联合索引的所有列或者左边开始的部分列(注意有顺序的)!

而联合索引并不一定只从索引中能获取到所有的数据,这个取决于你所查询的列。比如select
* from table where
××××××;的方式就不太可能是覆盖索引。因此如果你查询的列能用到联合索引,且你查询的列都能通过联合索引获取,比如你只查询联合索引所在的列或者左边开始的部分列,这就相当于覆盖索引了。通常为了让查询能用到覆盖索引,就将要查询的多列数据设置成联合索引。

四、table_cache 数据库用户打开表的缓存数量。可借助
open_tables(当前打开表的数量)来决策大小。

Select * From B FULL OUTER JOIN C

T树索引

对于MYSQL数据库的NDB
CLuster内存存储引擎,在使用它时可将其视为内存数据库。在内存数据库中,一般使用T树作为其索引的数据结构。T树是有平衡二叉树和B树发展而来。T树的好处是节点不存放数据,只存放指针,这样能减少内存的使用,这对内存数据库来说是很重要的。同时T树也是一棵平衡二叉树,以此保证查找的性能。

二、可以使用 mysqld –verbose –help|more 查看某个参数的定义。加上 grep
过滤。

图片 1

发表评论

电子邮件地址不会被公开。 必填项已用*标注