[数据库萌新入门] 第五章 索引与SQL优化

 一、索引

什么是索引？

MySQL：官方定义索引(Index)是帮助MySQL高效获取数据的数据结构。

我们可以简单理解为:快速查找排好序的一种数据结构。

索引分类：

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• 组合索引

（查询效率从上到下递减）

主键索引：

如果表中没有定义主键, InnoDB会选择一个唯一的非空索引代替。

如果没有这样的索引, InnoDB会隐式定义一个主键来作为聚簇索引

唯一索引

普通索引

用表中的普通列构建的索引,没有任何限制（一般不会随意创建索引，只要创建索引就是有代价的）

全文索引

用大文本对象的列构建的索引

为什么对中文不支持呢?因为中文和英文有本质上的区别。英文是单词语句组成,而且都有空格,好分辨,中文是有字组成的句,而且有不同的意思,因此不一样。当然目前中文分词技术已经非常成熟了,因此的mysql8中支持了中文的全文索引。

组合索引（联合索引）

组合索引不能乱序使用，可以从最左开始用后续有缺省。

二、索引原理-索引与B+ Tree

稍微了解一下：

        MySQL中只有memory (内存)存储引擎支持哈希索引,哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式,因此哈希索引不支持范围查找和排序的功能正常情况下,如果不指定索引的类型,那么一般是指B+Tree索引(或者B+Tree索引) 。存储引擎以不同的方式使用B+Tree索引。性能也各有不同,但是InnoDB按照原数据格式进行存储。

关于红黑树：

        当数据存在内存中,红黑树效率非常高,但是文件系统和数据库都是存在硬盘上的,如果数据量大的话,不一定能一次性加载到内存。所以一棵树都无法一次性加载进内存,又如何谈查找。因此就出现了专为磁盘等存储设备而设计的一种平衡多路查找树,也就是B树与红黑树相比,在相同的的节点的情况下,一颗B/B+树的高度远远小于红黑树的高度。

关于B+树和B树：

在计算机中,所有与空间相关的东西都是按照块(block)进行存取和操作的每次读取都意味着一次I/O

假设计算机中每个块的大小为4K,行的大小为1k,索引的大小为0.06K.

如果需要寻址遍历的次数多,就意味着更多的I/O。

B+树的优势：

1.磁盘读写代价更低
       B树的数据和索引都在同一个节点上,那么每个块中包含的索引是少量的,如果想要取出比较深层的数据,意味着要读取更多的块,才能得到想要的索引和数据,那么就增加了I/O次数
       而B+树中每个块能存储的索引是B树的很多倍（索引的存储所需空间远小于data）,那么获取比较深层的数据,也只需要读取少量的块就可以,那么就减少了磁盘的I/O次数
2.随机I/O的次数更少
       随机I/O是指读写操作时间连续,但访问地址不连续,时长约为10ms
       顺序I/O是指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据,时长约为0.1ms
       在相同情况下,B树要进行更多的随机I/O,而B+树进行更多的顺序I/O,因此B+树,效率也更快
3.查询速度更稳定
       由于B+Tree非叶子节点不存储数据(data) ,因此所有的数据都要查询至叶子节点,而叶子节点的高度都是相同的,因此所有数据的查询速度都是一样的。

聚簇索引与非聚簇索引：

innodb：

索引又分为聚簇索引和非聚簇索引两种。
在索引的分类中,我们可以按照索引的键是否为主键来分为“主索引”和辅助索引”
使用主键键值建立的索引称为“主索引" ,其它的称为“辅助索引”。
主索引只能有一个,辅助索引可以有很多个。这个的原因就涉及到了聚簇索引和非聚簇索引。

辅助索引找到key后，再去主键索引里面根据key找到数据（这也是为什么非聚簇索引也叫做二级索引）。所以聚簇的意思就是数据和索引聚集到一起。

以上关于索引原理和聚簇与非聚簇索引都是以InnoDB表引擎为基础

MyISAM：

        我们知道InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的数据都包含了,而MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:也就是把索引信息单独存到一个文件中,这个文件称为索引文件。
        MyISAM会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的数据记录,而是主键值+行号的组合。也就是先通过索引找到对应的行号,再通过行号去找对应的记录!其它非主键索引也是一样的,这种情况我们称为回行。所以在MyISAM中所有的索引都是非聚簇索引,也叫二级索引。

InnoDB和MyISAM的区别：

三、慢查询与SQL优化

什么是慢查询：

        MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。

        默认情况下, MySQL数据库并不启动慢查询日志,需要手动来设置这个参数。如果不是调优需要的话,一般不建议启动该参数,开启慢查询日志会或多或少带来一定的性能影响。

慢查询配置：

Explain：

        一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。

        MySQL为我们提供了EXPLAIN语句来帮助我们查看某个语句的具体执行计划。

 

type：const 是最好的状态。all是全表扫描，const说明访问时间是个常量。

 

 

 

适当建立索引

1.创建并使用自增数字来建立主键索引
2.经常作为where条件的字段建立索引
3.添加索引的字段尽可能的保持唯—性
4.可考虑使用联合索引并进行索引覆盖
注：

1、联合索引的索引覆盖(多个字段组合成了一个联合索引,在查询时,所要的字段和查询条件中的索引是一致)

2、索引绝不是加的越多越好(1.索引会占空间. 2.索引会影响写入性能)

合理使用索引：

1、不要在列上用函数和进行运算，这将导致索引失效。

2、隐式转换可能导致索引失效

3、like语句的索引失效问题（通配符不放开头）

4、复合索引的使用（联合、组合）

1、多个单列索引并不是最佳选择
MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。因此，为多个列创建单列索引并不能提高MySQL的查询性能。

三个索引还很浪费存储空间，所以不如用复合索引。

2、复合索引的最左前缀原则

       查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开开始查找,则无法使用索引。

3、尽可能达成索引覆盖

       如果一个索引包含所有需要的查询的字段的值,直接根据索引的查询结果返回数据,而无需读表,能够极大的提高性能。因此,可以定义一一个让索引包含的额外的列,即使这个列对于索引而言是无用的。

       就是如果select 有xxx 但是 where 不带 xxx（这种情况下索引是有效的，只要where中的符合条件）， 为了不用查询后回行找数据，我们可以把 xxx也放到组合索引里，虽然我们where条件不会用到xxx。

总结SQL语句的优化：