Mysql中的索引

女爷i 2022-11-15 03:43 142阅读 0赞

### Mysql中的索引 ###

**1、什么是索引？**

Mysql官方给出的索引的定义：索引（Index）是帮助mysql高效获取数据的一种数据结构。

我们可以简单理解为：已经排好序的快速查找数据结构。

数据本身之外，数据库还维护着一个满足特点查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等。

--------------------

2、索引的优点和缺点：

优点：提高数据检索的效率，降低数据库的IO成本。降低数据排序的成本，降低CPU消耗。

缺点：占用空间，降低更新表的速度。

（1）实际上索引也是一张表，该表保存了主键与索引字段，并指向实际表的记录，所以索引也是要占用空间的。

（2）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert，update和delete。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新操作所带来的键值变化后的索引信息。

（3）索引只是提高查找效率的一个因素，如果mysql有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

--------------------

3、索引的分类：

（1）单值索引：即一个索引只包含单个列，一个表可以有多个单值索引。  
（2）唯一索引：索引列的值必须唯一，但允许有空值。例如主键，unique修饰的字段。  
（3）复合索引：即一个索引包含多个列。

--------------------

4、索引的基本语法：

（1）索引的创建

create \[unique\] index indexName on myTable(columnName(length));

alter myTable add \[unique\] index \[indexName\] on (columnName(length));

加了unique就表示唯一索引。  
索引命名习惯：index\_tableName\_column

例如：index\_user\_username

（2）索引的删除

drop index \[indexName\] on myTable;

（3）索引的查看：

show index from tableName;

使用alter命令，有4种方式来添加数据表的索引：  
alter table tbl\_user add primary key (id);

改语句添加一个主键，主键属于唯一索引，且不能为空。

alter table tbl\_user add unique index\_user\_username (username);  
创建唯一索引，除了null值外，null值可能会出现多次。

alter table tbl\_user add index index\_user\_username (username);  
创建普通索引，索引只可能出现多次。

alter table tbl\_user add index fulltext index\_user\_username (username);  
指定索引为fulltext，用于全文索引。

mysql索引结构：BTree索引，Hash索引，full-text全文索引，R-Tree索引。

--------------------

**5、哪些情况需要创建索引？**

（1）主键自动建立唯一索引；  
（2）频繁作为查询条件的字段应该创建索引；  
（3）查询中与其他表关联的字段，外键关系建立索引；  
（4）单键索引，组合索引选择用哪个？（在高并发下倾向创建组合索引）  
（5）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；  
（6）查询中统计或者分组字段

--------------------

**6、哪些情况不需要建立索引？**

（1）频繁更新的字段不适合创建索引，因为更新表记录还会更新索引；  
（2）Where条件里用不到的字段不要创建索引；  
（3）某个字段的值重复太多，例如一个字段的值只有true和false，这种字段建立索引没有太大意义；

--------------------

7、什么是索引覆盖？

就是select的数据列只用从索引中就能取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖。

--------------------

8、索引优化：

（1）全值匹配；  
（2）最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列；  
（3）不在索引列上做任何操作，包括计算，函数，自动手动类型转换；  
（4）存储引擎不能使用索引中范围条件右边的列；  
（5）尽量使用覆盖索引，查询列和索引列一致，减少select \*  
（6）Mysql在使用不等于（!=）将导致索引失效，进行全表扫描  
（7）is null, is not null也无法使用索引;  
（8）like左模糊（like ‘%abc’）将导致索引失效，进行全表扫描

（9）字符串不加单引号索引失效，即varchar类型你写错了，会被项目经理骂死。  
（10）少用or，会导致索引失效

问题：如何解决like ‘%abc%’索引失效问题？  
答：使用覆盖索引，尽量让查询的字段被索引覆盖。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BpcGl6aGVuXw_size_16_color_FFFFFF_t_70]  
Where c=5 and b=4 and a=3；Y，mysql会优化sql语句，使用到abc  
Where a=3 and c>5 and b=4；Y，优化后使用到abc，c使用了部分  
Where a=3 and c=5 order by b；Y，使用到a，b的作用是排序而不是查找  
Where a=3 order by b；Y，使用到a，b作用是排序  
Where a=3 order by c；Y，使用到a，但是中间断了，产生filesort  
Where a=3 order by b,c；Y，使用到a，bc作用是排序  
Where a=3 order by c,b；Y，使用到a，产生filesort  
Where a=3 and b=4 order by c,b；Y，使用到a，无filesort，因为b是常量  
Where a=3 and b=4 order by b,c；Y，使用到ab  
Where a=3 and b=4 and c=5 order by b,c；Y，使用到ab  
Where a=3 and c=5 group by b；Y，使用到a  
Where c=5 group by c,b；N，产生filesort, 也Using memory

--------------------

9、索引优化的一般建议：

对于单键索引，尽量选择针对当前查询过滤性更好的索引；  
对于组合索引，当前查询中过滤性最好的字段在索引顺序中，位置越靠前越好；  
对于组合索引，索引值尽量包含where子句中更多的字段；  
尽可能通过分析统计信息和调整查询的写法来选择合适的索引；

左连接，索引加右表。

小表驱动大表：  
例如：select \* from A where id in (select id from B);  
等价于：select \* from A where exists (select 1 from B where A.id=B.id);  
当B表的数据集小于A表的数据集时，in优于exists；  
当B表的数据集较多时，exists优于in；

为排序使用索引：  
Mysql两种排序方式：文件排序（using filesort）或扫描有序索引文件（using index）。

--------------------

10、假设：index a\_b\_c(a, b, c);

Order by能使用索引最左前缀：

*  order by a
 *  order by a,b
 *  order by a,b,c
 *  order by a desc, b desc, c desc（同升，同降序都行）  
    如果where使用索引的最左前缀定义为常量，则order by能使用索引：
 *  where a=’zs’ order by b,c
 *  where a=’zs’ and b=1 order by c
 *  where a=’zs’ and b>1 order by b,c  
    索引失效的情况，即没有使用索引来排序的：
 *  order by a asc, b desc, c desc // 排序不一致
 *  where b=1 order by b,c // 丢失a索引，带头大哥没了
 *  where a=’zs’ order by c // 丢失b索引，中间兄弟断了
 *  where a=’zs’ order by a,d // d不是索引的部分，需要重排序
 *  where a in(…) order by b,c // a是范围，范围之后全失效

Group by关键字优化与group by一致，能在where中限定的条件就不要放在having中限定。

--------------------

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BpcGl6aGVuXw_size_16_color_FFFFFF_t_70]: /images/20221022/b5ed18901d8c4910915e546b175d8401.png