mysql索引优化的一些方法

lunarYoung
72 阅读14分钟

一、索引的基础

1、索引定义与优缺点

什么是索引？在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，可以快速查找表中数据。大白话解释：类似书的目录

2、索引的好处：

  • 提高数据的检索速度，降低数据库IO成本
  • 降低数据排序的成本，降低cpu消耗

3、索引的缺点：

  • 占用存储空间
  • 降低更新表的速度

4、索引的类型

4.1、从物理存储角度划分：

  • 聚簇索引：在一个数据table中，它的数据文件和索引文件是同一个文件。即在查询过程中，找到了索引，便找到了数据文件。innodb里按照主键来排序存储数据。特点：这样在查找行数据的时候非常有效
  • 非主键索引（辅助索引、二级索引）：不会把索引指向的内容像聚集索引一样直接放到索引的后面，而是维护单独的索引表（只维护索引，不维护索引指向的数据），为数据检索提供方便。

4.2、从逻辑角度划分

  • 普通索引： 基础的索引，没有任何约束，主要用于提高查询效率。
  • 唯一索引： 在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。
  • 主键索引： 在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
  • 全文索引：引用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

注意：在一张数据表中只能有一个主键索引，这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

 

4.3、从数据结构角度划分：B+树、B树、哈希表、有序数组、n叉数

二、索引的结构

1、mysql 使用到的B+树

特点：
B+ 树非叶子节点上是不存储数据的，仅存储键值
B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。
叶子节点的双向链表链接

检索逻辑
单值查询：查15
范围检索：[15,60]

 

2、问题三连

为什么索引使用B+树？不是哈希表、二叉查找树、平衡二叉树、红黑数？
为什么用自增主键？
一棵3层的B+数，可以存储多少条数据？

 

哈希索引： 只适合等值查询，不适合范围查询、模糊查询、排序、最左匹配。

有序数组：
有序数组在等值查询和范围查询场景中的性能就都非常优秀，如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

二叉查找树： 每个节点的左儿子小于父节点，父节点又小于右儿子。高效的数据检索方式，时间复杂度为 O(log2n)。可能会特殊化为一个链表，相当于全表扫描。查询时间严重不稳定

平衡二叉树： 通过旋转解决了平衡问题，但是旋转操作效率比较到

红黑树： 是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性

B-Tree： 叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

B+树： 在B树的基础上，将非叶节点改造为不存储数据纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。

4、一棵3层的B+数，可以存储多少条数据？

答： InnoDB 的页默认16k，假设一行记录的数据大小为1k，那么单个叶子节点可以保存的记录数=16k/1k=16;
假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B = 1170，
高度为2的B+数，能存放117016=18720的数据
高度为3的B+数，能存放11701170*16=21902400的数据

 

四、B+树的特点：

具体特点：

1.文件很大，不可能全部存储在内存中，故要存储到磁盘上
2.索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数（为什么使用B-/+Tree，还跟磁盘存取原理有关。）
3.局部性原理与磁盘预读，预读的长度一般为页（page）的整倍数，（在许多操作系统中，页得大小通常为4k）
4.数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，(由于节点中有两个数组，所以地址连续)。

 

局部性原理： 当一个数据被用到时，其附近的数据也通常会马上被使用。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

磁盘预读：
根据局部性原理，为了减少磁盘IO，磁盘往往不是按需进行读取，而是每次都会进行预读，顺序往后读取一定长度的数据到内存中（不用寻道，顺序旋转即可）

 

B+树特点总结：

查询时间稳定、检索高效
支持范围查找
最左匹配、模糊匹配
索引排序

 

五、最左前缀原则与联合索引

最左优先，以最左边的为起点任何连续的索引都能匹配上。首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推

注意：

遇到范围查询(>、<、between、like)就会停止匹配；
直接使用第二字段进行条件判断用不到索引了

 

联合索引： 多个字段组成一个索引，注意，索引项的顺序是按照索引定义的排列顺序来排序的；

注意： 在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

 

实验：

  • 全值匹配
  • 匹配左边的列
  • 匹配列前缀
  • 匹配范围
  • 精确匹配某一列并范围匹配另一列
  • 排序

 

第2部分：sql优化

一、建表规范

  1. 主键尽可能小
  2. create_time，update_time两个字段，且建议有索引（对于后期表数据量会很大需要归档的表一定要建立时间字段的索引，比如create_time）精确到秒或者毫秒的时间采用 TIMESTAMP，自动更新时间。
  3. 字段不要null， 要有缺省值
  4. java BigDecimal 对应数据库的decimal
  5. 不使用浮点类型（FLOAT、DOUBLE）
  6. 数量在5000万以内
  7. 区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型。例如取值范围为0-80时, 使用TINYINT UNSIGNED。对某一列只有一两个值，例如要么是0，要么是1，可以用tinyint（只占用一个字节）

二、建立索引的注意事项

1、最左前缀匹配。
合理的扩展索引，而不是每次都新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
合理的联合索引顺序
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3、尽量选择区分度高的列作为索引, 区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4、将where中用的比较频繁的字段建立索引 
5、每张表都设置一个ID做为其主键，而且最好的是一个bigint型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。
6、尽量选择较小的列 
7、数据类型为Text，Blob等大对象不能建立索引，也不适合建立索引,另外字段太长的字段不适合建立索引。例如超长字符串。会使索引树过大，mysql可能无法将其放入内存，访问索引会带来过多的磁盘I/O。效率低下

三、sql语句优化

  1. select子句中避免使用‘*’ 
  2. 当只需要一行数据的时候使用limit 1 
  3. 保证表单数据不超过千万量级，适时分割表 
  4. 针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况
  5. 一次查询，只能使用一个索引，所以利用好最左前缀原则
  6. limit分页需要排序
  7. 减少交互次数

四、不走索引的场景：

  1. 有or必全有索引; 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
  2. 复合索引未用左列字段;
  3. 最左前缀： 遇到范围查找，则停止向右匹配
  4. like以%开头;
  5. not in 、in慎用，可以使用union all
  6. 需要类型转换; MySQL会根据需要自动将数字转换为字符串，将字符串转换数字。如果字段 phone 是char类型, 那么 WHERE phone = 18612345678 同样可能会导致索引失效, 应该改写成 WHERE phone = '18612345678'
  7. where中索引列使用函数、进行计算。比如WHERE a + 1 = 2 这种语句也会导致索引 a 失效, 此时应该改写 SQL 语句为: WHERE a = 1
    比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29');
  8. 如果mysql优化器觉得全表扫描更快时（区分度太低、数据少等）;
  9. 索引列存在NULL且查询条件是 is not null, 若索引没有覆盖查询字段和查询条件时, 导致全表扫描.
  10. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  11. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

五、explain

dev.mysql.com/doc/refman/…

blog.csdn.net/asd05137730…
常用列：type、type、possible_keys、key、key_length

1、列type

性能从差到好：ALL、index、range、 ref、eq_ref、const、system、NULL
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 ,逐行做全表扫描.,运气不好扫描到最后一行. (说明语句写的很失败)
index: Full Index Scan，index与ALL区别为index类型只遍历索引树,相当于data_all index 扫描所有的索引节点,相当于index_all
range:只检索给定范围的行，使用一个索引来选择行,能根据索引做范围的扫描
ref: 不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

2、列rows

mysql估算的需要扫描的行数（不是精确值）。
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

 

3、列extra

性能从好到坏:using index>using where > using temporary | using filesort
Extra中包含的值：
using index： 只用到索引,可以避免回表，性能很高。
using where： 使用到where来过滤数据， 不是所有的where clause都要显示using where. 如以=方式访问索引。
using temporary： 用到临时表去处理当前的查询。
using filesort： 用到额外的排序，此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。(当使用order by v1,而没用到索引时,就会使用额外的排序)。
range checked for eache record(index map:N)： 没有好的索引可以使用。
Using index for group-by：表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

Using index condition：代表查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

 

六、limit偏移量较大的分页优化

一组实验数据：

sql语句用时
select * from student limit 10, 20  0.016秒
select * from student limit 400000, 20 3.229秒
select * from student limit 866613, 2037.44秒

现象：
1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

 

解决方法：

方法一、利用主键索引 id>10000000
方法二、《高性能mysql》：利用表的覆盖索引来加速分页查询
SELECT id,name FROM student INNER JOIN (SELECT id FROM student LIMIT 1000000,10) stu ON student.id = stu.id;
方法三、根据业务场景优化比如使用 id between and

avatar
文章
阅读
粉丝