凡度网络北京网站建设公司,wordpress导航样式,东莞专业网站设计,网站建设合理性文章目录一、B TreeB Tree相比于红黑树的优点1. B树有更低的树高2. B树更符合磁盘访问原理二、MySQL索引2.1 B Tree索引2.2 哈希索引2.3 全文索引2.4 空间数据索引三、索引的优点以及什么时候需要使用索引什么时候需要使用索引四、索引实战建立普通索引建立唯一索引建立主键索引…
文章目录一、B TreeB Tree相比于红黑树的优点1. B树有更低的树高2. B树更符合磁盘访问原理二、MySQL索引2.1 B Tree索引2.2 哈希索引2.3 全文索引2.4 空间数据索引三、索引的优点以及什么时候需要使用索引什么时候需要使用索引四、索引实战建立普通索引建立唯一索引建立主键索引建立联合索引建立全文索引哪些字段适合创建索引五、聚簇索引和非聚簇索引在Innodb中聚簇索引默认就是主键索引。如果表中没有定义主键那么该表的第一个唯一非空索引被作为聚集索引。如果没有主键也没有合适的唯一索引那么innodb内部会生成一个隐藏的主键作为聚集索引这个隐藏的主键是一个6个字节的列改列的值会随着数据的插入自增。六、最左匹配原则七、索引失效多条件字段中、NOT、in、not exists查询条件中使用OR 或者 like在索引列上做(计算 / 函数 / 类型转换)索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引索引列数据类型不匹配一、B Tree
B Tree指的是Balance Tree(平衡树),其是一颗查找树并且所有叶子结点位于同一层。
B Tree是改进版本的B Tree他不但具有B Tree的平衡性并且通过顺序访问指针来提高区间查询的性能。
在B Tree中一个节点的key从左到右非递减排列如果某一个指针的左右相邻key分别是key i 和 key i 1,且不为null则该指针指向的节点的所有key大于等于key i且小于等于key i 1;
如下图所示: B Tree相比于红黑树的优点
红黑树等平衡树也可以用来实现索引但是文件系统和数据库系统普遍采用B Tree作为索引结构,这是因为使用B Tree访问磁盘数据有更高的性能。原因如下
1. B树有更低的树高
平衡树的树高 O(h)O(logdN)其中 d 为每个节点的出度。红黑树的出度为 2而 B Tree 的出度一般都非常大所以红黑树的树高 h 很明显比 B Tree 大非常多。
2. B树更符合磁盘访问原理
之前提到了B树相比红黑树有着更低的树高。 由于B 树相对于红黑树有更低的树高磁盘寻道的次数与树高成正比在同一个磁盘块上进行访问只需要很短的磁盘旋转时间所以 B 树更适合磁盘数据的读取。
二、MySQL索引
mysql索引就相当于是书的目录。索引的类型有以下几种
2.1 B Tree索引
是大多数 MySQL 存储引擎的默认索引类型。 因为不再需要进行全表扫描只需要对树进行搜索即可所以查找速度快很多。 因为 B Tree 的有序性所以除了用于查找还可以用于排序和分组。
2.2 哈希索引
哈希索引能以 O(1) 时间进行查找但是失去了有序性 无法用于排序与分组 只支持精确查找无法用于部分查找和范围查找。
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”当某个索引值被使用的非常频繁时会在 BTree 索引之上再创建一个哈希索引这样就让 BTree 索引具有哈希索引的一些优点比如快速的哈希查找。
2.3 全文索引
查找条件使用 MATCH AGAINST而不是普通的 WHERE。 全文索引使用倒排索引实现它记录着关键词到其所在文档的映射。 InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
2.4 空间数据索引
MyISAM 存储引擎支持空间数据索引R-Tree可以用于地理数据存储。空间数据索引会从所有维度来索引数据可以有效地使用任意维度来进行组合查询。
三、索引的优点以及什么时候需要使用索引
索引的优点如下 1.大大减少了服务器需要扫描的数据行数。 2.帮助服务器避免进行排序和分组以及避免创建临时表BTree 索引是有序的可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建不需要排序和分组也就不需要创建临时表。 3.将随机 I/O 变为顺序 I/OBTree 索引是有序的会将相邻的数据都存储在一起
什么时候需要使用索引
对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效对于中到大型的表索引就非常有效但是对于特大型的表建立和维护索引的代价将会随之增长。这种情况下需要用到一种技术可以直接区分出需要查询的一组数据而不是一条记录一条记录地匹配例如可以使用分区技术
四、索引实战
建立普通索引
ALTER TABLE table_name ADD INDEX index_name (column);建立唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column);建立主键索引
ALTER TABLE table_name ADD PRIMARY INDEX index_name (column);建立联合索引
ALTER TABLE table_name ADD INDEX index_name (column1,COLUMN2,COLUMN3);建立全文索引
全文索引主要用来匹配字符串文本中的关键字当需要字符串中是否包含关键字的时候我们一般用like如果是以%开头的时候则无法用到普通索引这个时候我们就可以使用到全文索引了
ALTER TABLE table_name ADD FULLTEXT (column);哪些字段适合创建索引
频繁查询的字段在where和on条件中频繁出现的字段区分度高的字段。区分度可以通过下列方式计算:
select count(distinct birthday)/count(*),count(distinct gender)/count(*)
from user;有序的字段适合创建。这样有序的字段在插入数据库的过程中仍然能够保持B Tree的索引结构不需要频繁更新索引文件性能更佳。应该优先使用联合索引如果只在age字段创建索引会先匹配到age18的三条数据再逐个遍历效果会更差。同时在使用联合索引时区分度高的字段应该放在前面。
五、聚簇索引和非聚簇索引
聚集索引clustered index和非聚集索引secondary index也称辅助索引或普通索引。这两种索引是按存储方式进行区分的。聚集索引clustered也称聚簇索引这种索引中数据库表行中数据的物理顺序与键值的逻辑索引顺序相同。一个表的物理顺序只有一种情况因此对应的聚集索引只能有一个。如果某索引不是聚集索引则表中的行物理顺序与索引顺序不匹配与非聚集索引相比聚集索引有着更快的检索速度。 详情可以参考下图:
在Innodb中聚簇索引默认就是主键索引。
如果表中没有定义主键那么该表的第一个唯一非空索引被作为聚集索引。
如果没有主键也没有合适的唯一索引那么innodb内部会生成一个隐藏的主键作为聚集索引这个隐藏的主键是一个6个字节的列改列的值会随着数据的插入自增。
六、最左匹配原则
在MySQL建立联合索引时会遵守最左前缀匹配原则即最左优先在检索数据时从联合索引的最左边开始匹配。
由于构建一棵B树只能根据一个值来确定索引关系所以数据库依赖联合索引最左的字段来构建。举例创建一个a,b的联合索引那么它的索引树就是下图的样子。 可以看到a的值是有顺序的112233而b的值是没有顺序的121412。但是我们又可发现a在等值的情况下b值又是按顺序排列的但是这种顺序是相对的。 七、索引失效
多条件字段中 单字段有索引WHERE条件使用多字段含带索引的字段例如 SELECT * FROM student WHERE name ‘张三’ AND addr 北京市’语句如果name有索引而addr没索引那么SQL语句不会使用索引。 多字段索引违反最佳左前缀原则。例如student表如果建立了(name,addr,age)这样的索引WHERE后的第一个查询条件一定要是name索引才会生效。
、NOT、in、not exists
当查询条件为等值或范围查询时索引可以根据查询条件去找对应的条目。否则索引定位困难结合我们查字典的例子去理解执行计划此时可能更倾向于全表扫描这类的查询条件有、NOT、in、not exists
查询条件中使用OR 或者 like
如果条件中有or即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR)SQL语句中使用后置通配符会走索引例如查询姓张的学生SELECT * FROM student WHERE name LIKE ‘张%’而前置通配符(SELECT * FROM student WHERE name LIKE ‘%东’)会导致索引失效而进行全表扫描。
在索引列上做(计算 / 函数 / 类型转换)
以下几种例子会导致索引失效 :
在索引列上使用函数例如select * from student where upper(name)‘ZHANGFEI’在索引列上计算例如select * from student where age-117;在索引列上使用mysql的内置函数, SELECT * FROM student WHERE create_time
索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引
索引列数据类型不匹配
如果age字段有索引且类型为字符串并且在查询的时候让age等于一个整数值则会索引失效例如SELECT * FROM student WHERE age18会导致索引失效
