MySQL索引

1. 索引

为了提高mysql数据库查询效率的一种数据结构。

好处：1、适用于大量的数据，类似于书的页码，可以快速的找到数据。

2、利用索引可以优化排序，降低cpu的消耗。

劣处：1、索引也是一张表，也会占用一定的磁盘空间。

2、因为所以也是一张表，对于插入，修改、修改的操作效率低，不仅要更新主表，还要修改索引表。

2. 索引的设计原则

对查询频次较高，且数据量比较大的表建立索引。

使用唯一索引，区分度越高，使用索引的效率越高。

最左前缀原则：将n个字段组合成一个复合索引，如果where字句中的字段是复合索引的字段，那么这条sql可以被复合索引提高查询效率。

在查询时应该把最可能被搜索到的字段放在最左侧，这样可以更好地利用索引，提高查询效率

3. 聚簇索引

聚簇索引是由表的一个列或多个列进行排序结果建立的一个索引，他和非聚簇索引不同，聚簇索引不仅存储索引值，而且还存储整行数据内容，因此，叶子节点存储的不在是指针而是整行数据，聚簇索引效率比非聚簇索引的效率要高，还支持范围查询。

优点：

1、聚簇索引的叶子节点存储的是整行数据，索引只要进行一次二分查找，就可以找到目标数据行，加快了查询速度。

2、减少了io操作，因为叶子节点存储的整行数据，减少了磁盘的查询次数。

缺点：

1、占用更多的磁盘空间，由于聚簇索引存储的是完整的行，他可能会占用更多的空间。

2、插入、更新、删除的效率会降低，因为它的索引是数据自己本身，进行插入、修改、删除操作，会导致物理位置更改，整棵树进行移动和排序操作

总结：聚簇索引适合用于频繁查询的表，对于增、删、该会有一些性能的降低。更据情况进行使用。

4.非聚簇索引

非聚簇索引是对表的一个或多个列进行排序，将排序结果放到一个b+树中，和聚簇索引不同，非聚簇索引只保存索引值和指向数据行的指针，并不包含整条数据。查询的时先使用索引值，再使用指针找到对应的数据行。一个表可以有多个非聚簇索引。

好处：

1、占用的空间少，因为非聚簇索引指存储索引值和指针，并不存储整行数据。

2、插入、删除、修改的速度快，因为非聚簇索引只存储指针和索引，对于更新的操作，只需要对索引结构进行修改，而不需要对整个表进行排序和移动。

坏处：

1、查询速度降低，有需要先查询索引，在使用指针查数据行，增加io的次数，索引查询较慢

2、不支持范围查询，因为只存储了索引和指针，只能查找单个值、

总结：非聚簇索引适合于随机插入数据的表以及需要经常进行更新、删除等操作的表，但对于需要频繁进行查询操作的表，聚簇索引比非聚簇索引更加适用。

5.索引的优化方式：

1. 尽量避免创建全文索引，因为全文索引会占用更多的空间和时间，并且可能会导致查询性能下降。
2. 在设计表时，使用合适的字段来创建索引，这些字段应该是经常被查询的。
3. 避免在一个表中创建过多的索引，因为这会导致索引的效率降低，从而导致查询性能下降。
4. 避免在查询时使用通配符（如%），因为通配符会对索引的效率造成影响。
5. 避免在一个表中重复创建索引，因为这会使得索引的效率降低。
6. 在删除数据时，尽量不要删除索引，因为这会破坏索引的数据结构，从而导致索引无法正常工作。

6.联合索引的规范

1、尽量避免在一个表中创建过多的联合索引，因为这会导致索引的数量过多，从而降低查询的性能。

2、最左前缀原则：在使用联合索引时，查询条件应从左到右匹配索引中的列。换句话说，如果查询条件没有包含联合索引的最左边的列，那么索引将不会被有效地使用。因此，需要确保在查询中经常使用的列位于联合索引的最左侧。

3、选择性高的列放前面：在创建联合索引时，将具有较高选择性（列值唯一性较高）的列放在索引的前面。这可以帮助 MySQL 更快地缩小查询范围。

4、避免过长的索引列：索引列越长，索引的存储和维护成本就越高。在创建联合索引时，尽量选择较短的列，或者使用前缀索引来减少索引的长度。

5、减少索引中的列数：尽量减少联合索引中的列数。每增加一个列，索引的大小和维护成本都会增加。只将对查询性能产生显著影响的列包含在索引中。

6、考虑查询的排序和分组需求：在创建联合索引时，考虑查询中的排序和分组需求，将这些列包含在索引中。这样，MySQL 可以使用索引来完成排序和分组操作，从而提高查询性能。

7.SQL优化

1、使用LIKE关键字的查询语句

避坑：%字符不能在第一个位置

例如：

WHERE city LIKE '%城市'

是因为最左边是需要匹配任意字符，从而进行全表扫描，导致索引失效，所以要避免%在开头的情况。

2、使用多列索引查询语句

避坑：查询条件中至少需包含组合索引的第一个字段

例如：组合索引（a，b，c）

-- 优化前 WHERE b=2 AND c=3 -- 优化后 WHERE a=1 AND b=2 AND c=3 WHERE a=1 AND c=3 AND b=2 WHERE a=1 AND c=3

是因为查询条件中没有包含组合索引的第一个索引，不管如何，你的查询条件中至少需要索引a

3、使用OR关键字的查询语句

避坑：OR前后需使用索引字段

例如：表里有索引字段a，索引字段b，普通字段c

-- 优化前 WHERE b=2 OR c=3 -- 优化后 WHERE a=2 OR b=3

是因为查询条件中OR的前后，不管如何，你的查询条件中至少需要索引a，否则将导致索引失效；

其次我们可以利用UNION ALL来代替OR

SELECT 字段1,... FROM table WHERE a=2 UNION ALL SELECT 字段1,... FROM table WHERE b=

4、使用子查询的时候

避坑：改子查询为连接查询

例如：

-- 优化前 SELECT col1,... FROM table1 WHERE col2 IN (SELECT col2 FROM table2) a -- 优化后 SELECT col1,... FROM table1 as t1 INNER JOIN table2 as t2 ON t1.id = t2.id

效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

5、在WHERE条件中使用<>或者!=和NOT IN符号

避坑：尽量减少使用不等号来作为过滤条件

使用不等号会索引失效，从而导致全表扫描

6、在索引字段上进行计算

避坑：尽量避免在索引上进行计算

例如索引字段col1

SELECT * FROM table WHERE col1*2 > 100 -- 或者 SELECT * FROM table WHERE INSTR(col1,'好')>0

例如上面这样，我们在WHERE条件的中的运算符左侧进行运算，这样数据库引擎是不允许这样做的，从而导致使用不了索引

7、避免使用select *

1. 消耗资源：SELECT * 查询返回了所有的列，包括了表中可能没有用的列和不需要的列，而且返回的数据量也更多，这不仅会增加网络传输的带宽，而且还会占用更多的存储空间。
2. 维护性差：当数据库中的视图、函数或存储过程查询了一个表并使用SELECT *时，如果该表的结构发生了变化，可能会导致查询结果不一致的问题，从而给维护带来困难。
3. 可读性差：SELECT * 查询使得SQL查询变得复杂和难以阅读，因为该查询返回的数据都是表中的所有列，而且不易于理解每一列的含义和作用。