MySQL学习day3

一、索引基础

1、索引是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

2、索引优化是对查询性能优化最有效的手段，能够轻松将查询性能提高几个数量级。

索引的优缺点

优点

• 提高数据检索的效率，降低数据库的IO成本（将随机I/O变为顺序I/O）
• 索引大大减少了服务器需要扫描的数据量
• 可以避免排序和临时表
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

简易的工作原理

存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应的行。样例如下：

mysql> select first_name from users where id=5;

如果在id上有索引，则MySQL将使用该索引找到id=5的行。

二、索引类型

在MySQL中，索引是存储在引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

B-Tree索引

1、简述：

 每个节点都保存指针和数据信息。

2、底层的存储引擎也可能使用不同的存储结构。例如，NDB集群存储引擎内部实际上使用了        T-Tree结构存储这种索引；InnoDB则使用B+Tree。

3、存储引擎以不同的方式使用B-Tree索引，性能也各不相同。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB使用源数据存储。再如，MyIASM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

4、B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。通过比较节点页的值和要查找的值可以选择指针进入下一节点，最终存储引擎要么找到数据，要么不存在。

5、B-Tree对索引是顺序组织存储的，所以很适合范围查找。

可以使用B-Tree索引查询的类型

B-Tree适用于全键值查找，键值范围或键前缀查找，具体如下：

CREATE TABLE People(
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    birth         date        not null,
    gender        enum('m','f') not null,
    key(last_name,first_name,birth)
);//last_name是姓，first_name是名

1、全值匹配

        全职匹配指的是和索引的所有列进行匹配，比如找，王  富贵 1990-01-01出生的人。

2、匹配最左前缀原则

        找姓王的人，即只用索引第一列。若找叫富贵的索引会失效。

3、匹配列前缀

        找叫王富_ 的人，即只匹配某一列的前部分，后边的索引会失效。

4、匹配范围值

        找姓王到姓李之间的人，后边的索引会失效。

5、只访问索引的查询

  select last_name,first_name from People; 

        这种情况叫索引覆盖，使用索引直接获取列信息，不用读取行。

Hash索引

1、hash索引是基于hash表实现，只有精准匹配hash索引所有列的查询才有效。哈希索引将所有的hash码存储在索引中，同时在hash表中存指向每个数据行的指针。如果多个列的hash值相同索引会以链表的形式存放多个记录。MySQL中只有Memory索引支持hash索引。

 2、例：以People表为例

SELECT last_name from People where first_name='Peter'

MySQL先计算‘Peter’ 的hash值，并使用该值寻找记录指针，并根据指针找到对应行，最后比较该行是否为‘Peter’，以确保就是要查找的行。

 3、因为索引自身只需要存储对应的hash值，所以hash结构十分紧凑，这使得hash索引查找的速度非常快。然而hash索引也有它的限制。

• hash索引只包含hash值和行指针，而不存储字段，所以不能避免读取行。
• hash索引数据并不是按照索引值顺序存储的，所以不能用于排序。
• hash索引不支持匹配查找，因为hash值是索引的的全部内容计算的。例如，索引列为（A,B）,如果查询只有A列，就不能用该索引。
• hash只支持等值比较查询，不支持范围查询。
• 访问hash索引非常快但是会有hash冲突。当出现hash冲突的时候，存储引擎必须遍历链表的所有行指针。
• 如果hash冲突非常多，索引维护代价会很高。

自适应hash索引

1、InnoDB特有的，当InnoDB引擎注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上建立一个hash索引，这会让B-Tree索引具备某些hash索引的特性，例如快速的hash查找。

空间数据索引（R-Tree）

1、MyISAM表支持空间索引，可以用来支持地理数据存储。和B-Tree不同，该类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以按照各种维度来组合查询。

全文索引

1、全文索引是一种特殊的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜素引擎的事情。

2、在相同列上同时创建全文索引和基于值的B-Tree索引不会引起冲突，全文索引适用于MATCH

和AHAINST操作，而不是普通的WHERE操作。

聚簇索引

1、聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的局促索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行存在索引的叶子页。

2、InnoDB会使用主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相聚甚远。

聚簇索引的优点

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用索引覆盖扫描的查询可以直接使用在页节点中主键值。

聚簇索引的缺点

• 聚簇索引最大程度的提高了I/O密集型应用的性能，如果数据都存在了内存中，就没有优势了。
• 插入速度严重依赖于顺序
• 更新聚簇索引列的改价很高
• 基于聚簇索引的表在插入新行，或者主键被改变时导致需要移动行的时候，可能面临页分裂的问题。当页已满时插入新行，存储引擎会将该页分裂成两个页存储该行。
• 聚簇索引可能导致全表扫描变慢，尤其行比较稀疏

其它索引

TokuDB等。

三、索引失效

以People表为例

1、未按照最左前缀匹配

SELECT * from People  WHERE first_name ='Peter' //第二列索引
Select * from People WHERE last_name ='Allen' and birth=2000-03-01 //跳过第二列，只会使用第一列索引
Select * from People where birth=2000-03-01 and first_name ='Peter' and last_name='Allen'//未按照顺序使用

2、字符串没加''

SELECT *from People where last_name=Allen

3、索引上做结算

SELECT * FROM People where datediff(now(),birth)>3000

4、范围后边的索引都失效

SELECT *FROM People where last_name>'Allen' and last_name ='Peter'

5、使用不等会失效

SELECT *FROM People where last_name<>'Allen' 

 6、使用null或not null可能会失效

但是如果允许字段为空，则

• IS NULL 不会导致索引失效
• IS NOT NULL 会导致索引失效

7、模糊查询放在具体值得左边

SELECT *FROM People where last_name like '%飞' 

8，使用or也可能会失效