MySQL索引设计

---

什么是索引?

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,
它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引设计原则

1. 搜索的索引列，不一定是所要选择的列。最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
2. 使用惟一索引。考虑某列中值的分布。对于惟一 值的列， 索引的效果最好，而具有多个重复值的列，其索引效果最差。
3. 使用短索引。如果对串列进行索引，应该指定一个前缀长度,只要有可能就应该这样做。例如，如果有一个CHAR(200)列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。
4. 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的n前个字符作为索引值)
5. 不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，在修改表的内容时，索引必须进行更新,有时可能需要重构,因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。
6. 考虑在列上进行的比较类型。索引可用于“<”、“<=”、“=”、“>=”、“>”和BETWEEN运算。在模式具有一个直接量前缀时，索引也用于LIKE运算。如果只将某个列用于其他类型的运算时(如STRCMP( ))，对其进行索引没有价值。

MySql有哪些索引?

数据结构角度

1. BTREE
2. HASH
3. FULLTEXT
4. R-Tree

物理存储角度

1. 聚集索引(clustered index)
2. 非聚集索引(non-clustered index)

逻辑角度

1. 普通索引:仅加速查询
2. 唯一索引:加速查询+列值唯一(可以有null)
3. 主键索引:加速查询+列值唯一(不可以有null) +表中只有一个
4. 组合索引:多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5. 全文索引:对文本的内容进行分词，进行搜索 .

视图View

概念
可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。现在用的少,了解即可.
使用： 1，创建视图 2，使用视图

create view 视图名 as  SQL语句;
select * from 视图名;
#视图：就是一个特殊的表，缓存上次的查询结果
#好处是提高了SQL的复用率，坏处是占内存无法被优化
 
#1.创建视图
CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的
#2.使用视图
SELECT * FROM emp_view

Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.
B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

那么可以看出他们有以下的不同:

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.
  因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似),天然支持范围.
• hash索引不支持使用索引进行排序,原理同上.
  hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测AAAA和AAAAB的索引没有相关性.
• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
  hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点，且树的高度较低.

因此,在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引那么就不必再进行回
表查询.
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

如何查询最后一行记录?

select * from table_ _name order by id desc limit 1;