contact us
联系我们POSTTIME：2024-04-07 作者：佚名 点击量：
目录
在数据库操作中，经常需要查找特定的数据，以一条“select * from zyftest where id=10000”为例，数据库必须从第一条记录来时遍历，直到找到id为10000的数据，这样的效率显然非常低。所以，MySQL允许建立索引来加快数据表的查询和排序。
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库的索引是对数据库表中一列或多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度。MySQL中的索引可以大致分为以下几类：主键索引、唯一索引、普通索引、全文索引、组合索引、空间索引。
索引的优缺点主要体现在：
MySQL中，索引是在存储引擎层实现的，不同的存储引擎支持的索引类型不同，对索引的组织实现方式也不同。我们平时最常使用的是B+树索引，B+树是为磁盘或其他存取设备设计的一种平衡查找树，所有记录节点按照键值大小顺序存放在同一层的叶节点上，各叶节点通过指针进行链接，先来看一个B+树的结构图
通过图可以看到其基本特征如下：
可以看到相比一般二叉树，B+树的单个节点能存储更多信息，减少了磁盘 IO 的次数，从而提升了查找速度，而且叶节点形成有序链表，非常适合进行范围查询。
MySQL常用两种引擎InnDB和MyISAM对B+树的索引组织形式稍有不同。
InnDB的主键索引叶节点上直接存储了行记录，行记录按物理顺序存储，也叫做聚簇索引，普通索引叶节点上存储的是主键索引值，称之为辅助索引。因此如果使用普通索引查询会走两遍索引：先通过辅助索引找到主键索引值，再到主键索引中检索获取记录行，这个过程叫回表。
但是MyISAM中，普通索引和主键索引一样，叶节点存储的都是记录的物理地址，只会走一次索引。
索引是数据库中一种常用的优化技术，它可以加快数据的查找速度，提高数据库的查询效率。在 MySQL 中，可以通过以下几种方式来创建和管理索引。
可以通过 CREATE INDEX 命令创建索引，语法如下：
其中，UNIQUE 表示创建唯一索引，FULLTEXT 表示创建全文索引，SPATIAL 表示创建空间索引，index_name 是索引的名称，table_name 是要创建索引的表名，(column1, column2, ...) 是要创建索引的列名。
可以通过 SHOW INDEX 命令查看表的索引信息，语法如下：
该命令将列出表的所有索引，包括索引的名称、列名、索引类型、是否唯一等信息。
可以通过 DROP INDEX 命令删除索引，语法如下：
其中，index_name 是要删除的索引的名称，table_name 是要删除索引的表名。
可以通过索引来优化查询语句的执行效率。MySQL 中，可以使用 EXPLAIN 命令来查看查询语句的执行计划，进而优化查询。如果查询语句没有使用索引，可以考虑添加索引或者修改查询语句的条件，使其能够利用索引来加快查询速度。
需要注意的是，虽然索引可以加快查询速度，但是过多的索引也会影响数据库的性能，因为索引需要占用存储空间，并且在修改表数据时也会增加操作的复杂度。因此，在创建索引时需要根据实际情况进行选择和权衡，避免过度使用索引。这个后面会细分析。
索引的优化是非常必要的，因为索引可以极大地提高数据库的查询效率，特别是对于大量数据的表。在建立索引时，需要权衡利弊。一般来说，对于经常被查询、查询效率需要提高的列，可以建立索引；而对于不经常被查询的列，或者存储空间比较紧张的情况下，可以考虑不建立索引。同时，可以考虑对于一些查询频繁但数据更新较少的列建立索引，并定期进行索引维护来保证查询效率。因此，正确的创建和使用索引是实现高性能查询的基础。
建议使用int/bitint类型自增id作为主键，避免使用uuid等无序数据作为主键。有序主键能保证顺序io提升性能，无序主键是随机io，会导致聚簇索引的插入变成完成随机和频繁页分裂。
在多列的B+树索引中，索引会按照最左列进行排序，其次是第二列，因此索引的顺序对于查询是至关重要的，将选择性更高的字段放到索引的前面，可以更快地过滤出需要的行。
假设有一个学生表（students），包含了学生的ID、姓名、年龄等字段。为了加快查询效率，我们希望建立一个联合索引，包含了年龄、姓名两个字段。使用以下语句来创建该联合索引：
其中，age_name_idx是索引的名称，students是表名，age和name是需要建立索引的字段名。
建立该联合索引后，就可以使用类似如下的 SQL 查询语句，来查询符合年龄、性别条件的学生数据，并使用该索引进行优化：
在查询数据时，MySQL 就会自动使用该联合索引，提高查询效率。
可以预先计算下哪个列的选择性更高
根据计算结果，选择值更大的列作为索引列的第一项。
假设我们有一个订单表（orders），包含了订单号、下单时间、用户ID、订单总金额等字段。为了提高查询效率，我们希望建立一个覆盖索引，包含了订单号、下单时间、订单总金额三个字段。
可以使用以下语句来创建该覆盖索引：
其中，orders_idx是索引的名称，orders是表名，order_no、create_time和total_amount是需要建立索引的字段名。
当我们需要查询订单号为某个值的订单数据时，可以使用以下 SQL 查询语句，来查询符合条件的订单数据，并使用该覆盖索引进行优化：
在查询数据时，MySQL 就会使用该覆盖索引进行优化，直接从索引中获取到需要的数据，避免了对数据表的全表扫描，提高了查询效率。这种索引被称为覆盖索引，可以帮助我们避免回表操作。
覆盖索引可以极大地提高性能，因为只需要扫描索引，这种方式能带来很多好处：
索引条目一般远小于数据行大小，只读取索引，极大减少数据访问量，而且索引更容易全部放入内存，对IO密集型应用性能提升很大
索引按照列顺序存储，范围查询会比随机从磁盘读取每一行数据的IO要少得多
InnoDB的辅助索引覆盖查询，可以避免对主键索引的二次查询
前缀索引是指对于一个列的值，只取其前几个字符建立索引。使用前缀索引的好处是可以大大减小索引的大小，提高查询效率。
举个例子，我们有一个用户表（user），包含了用户ID、用户名、邮箱等字段。假设我们需要对用户名进行索引，但是用户名过长，建立完整的索引可能会占用较多的空间，影响索引效率。这时，可以使用前缀索引来优化索引。可以使用以下 SQL 语句来创建该前缀索引：
其中，username_prefix_idx是索引的名称，user是表名，username是需要建立索引的字段名，(10)表示该索引只对用户名的前10个字符进行建立。
需要注意的是，对于使用前缀索引的字段，查询时也需要使用该前缀才能使用索引优化。比如，以下 SQL 查询语句可以使用该前缀索引进行优化：
而以下 SQL 查询语句无法使用该前缀索引进行优化：
因为 %abc% 包含了用户名的后缀，无法使用前缀索引进行优化。
遇到前缀区分度不够好的情况下，比如我们国家的身份证号有18位，其中前6位是地址码，所以同一个县的人身份证号前6位一般是相同的。如果维护的是一个县的公民信息系统，对身份证号做长度为6的前缀索引区分度会很低，但索引长度选取越占用磁盘空间越大，相同数据页能放下的索引值就越少，搜索效率也就越低。
有两种方法能在达到相同的查询效率的同时占用更小的空间：
第一种方式是使用倒序存储。我们可以将身份证号倒过来存储，每次查询的时候这么写
由于身份证号后6位没有地址码这样的重复逻辑，所以能够提供足够的区分度。
第二种方式是使用hash字段。我们可以在表上再创建一个整数字段，用来保存身份证的校验码，同时在这个字段上创建索引
每次插入新记录的时候，都用crc32()这个函数得到身份证校验码填到这个字段。由于校验码可能存在冲突，所以查询语句where部分要判断id_card的值是否相同
这样，索引的长度就变成了4个字节，比原来小了很多。
在MySQL中，如果我们使用ORDER BY对查询结果进行排序，如果数据量较大，可能会导致性能下降，因为MySQL会在内存或磁盘上对所有查询结果进行排序。为了避免这种情况，我们可以利用索引扫描来进行排序。具体来说，我们可以利用覆盖索引或者索引合并的方式来实现索引扫描排序。
利用覆盖索引进行排序
我们可以建立一个包含ORDER BY字段和需要查询的字段的索引，这样MySQL可以使用索引扫描来满足ORDER BY操作，而不必再去扫描表中其他的行。
假设对上面students表需要按照age字段进行排序，可以这样建立索引：
这样，我们在进行查询时，就可以利用age_index索引来排序了：
利用索引合并进行排序
当我们需要对多个字段进行排序时，我们可以建立多个单列索引，MySQL会自动选择最优的索引组合来进行排序。这个过程被称为索引合并。
例如，假设我们需要按照name和age字段进行排序，我们可以这样建立索引：
这样，在进行查询时，MySQL会自动选择最优的索引组合来满足ORDER BY操作：
需要注意的是，索引合并会增加查询的开销，因为MySQL需要扫描多个索引，将结果进行合并。因此，在建立索引时需要根据实际情况进行权衡，选择最优的索引策略。
在数据库中，创建过多的索引会导致查询性能下降、插入/更新/删除操作变慢等问题，而创建冗余索引则是其中一种常见的问题。冗余索引指的是已经存在一条索引可以满足查询条件，但是又创建了另一条重复的索引。这种索引不仅浪费存储空间，还会使得数据库维护索引的代价更大，影响数据库性能。下面是一个创建了students冗余索引的例子：
上述例子中，虽然已经在name和age字段上都创建了单独的索引，但还创建了一个覆盖了这两个字段的联合索引idx_name_age。如果查询条件只涉及name或age字段中的一个，那么使用单独的索引即可，而无需使用idx_name_age索引。
避免创建冗余索引的方法包括：
需要注意的是，索引的设计并不是一成不变的，需要根据具体的业务需求和数据特征不断进行调整和优化。
正确使用索引可以避免因过多的无效索引造成的额外的存储空间和内存消耗，避免在大数据量和高并发的情况下出现慢查询和数据库性能下降的问题，同时也可以提高系统的安全性，减少数据损失的风险。因此，在数据库的设计和使用中，正确使用索引是非常重要的一步。
对于联合索引，MySQL会一直向右匹配，直到遇到范围查询（< 、>、between、like等）就停止匹配。例如表有联合索引（a，b，c），只有a、ab、abc类型的查询会走这个索引，特别要注意对这种联合索引的使用
在索引字段上进行数学运算或函数运算会导致MySQL无法使用该索引，从而导致查询变慢。这是因为数学运算或函数运算会对字段进行计算，使得MySQL无法通过直接比较索引来确定查询结果。
上面两个查询分别对索引列使用了数学运算和函数运算，通过explain查看执行计划，可以发现他们都是走的全表扫描。
常见的隐式类型转换大坑
操作日志oplogid这个字段上有索引，但是explain的结果却显示这条语句会全表扫描。原因在于oplogid的字符类型是varchar(32)，比较值却是整型，故需要做类型转换。在MySQL中字符串和数字进行比较的话是将字符串转换成数字，对于优化器来说，上面的查询语句相当于
也就是说，它对索引字段做了函数运算，所以会出现索引失效。
常见的隐式字符编码转换大坑
两个用tradeid关联的表查询
Tradelog用tradeid关联tradedetail时，理应会走Tradedetail的tradeid索引快速定位到等值的行，实际上却走了全表扫描。如果仔细检查表结构定义的话，可以发现Tradelog字符集是utf8，Tradedetail的字符集是utf8mb4，由于utf8mb4是utf8的超集，当两个类型的字符串在做比较时，MySQL会先把utf8字符集的字符串转换成utf8mb4再做比较。所以，它也属于对索引字段做函数操作，索引会失效。
一般情况下不鼓励使用like，如果要使用的话避免以通配符%和_开头，即like '%xxx%'，它不会走索引，而like 'xxx%'能走索引。若要提高效率，可以考虑使用全文索引。上面已经说过了。
负向查询指的是在查询中使用不等于（<>）或不包含（NOT IN、NOT EXISTS等）的条件，即查询不满足某些条件的记录。负向查询通常会导致数据库执行全表扫描，影响查询性能。
下面是一个简单的例子，假设我们有一个 users 表，其中包含了用户的姓名、年龄、性别、地址等信息，现在需要查询不是女性的用户信息：
这个查询会扫描整个 users 表，并且无法利用 gender 字段上的索引，从而导致查询效率低下。为了避免负向查询，我们可以改写查询语句，如下所示：
这个查询只需要扫描 gender 等于 male 的记录，可以充分利用 gender 字段上的索引，因此查询效率更高。
查询时尽量不要使用select *，而是只查出需要的字段，因为select * 无法利用覆盖索引优化，还会为服务器带来额外的IO、内存和cpu的消耗
在写密集的场景，表范式化设计对性能的提升也是明显的。当数据较好范式化时，修改的数据更少，而且范式化的表通常要小，可以有更多的数据缓存在内存中，所以执行操作会更快。缺点则是查询时需要更多的关联。
一般而言，选择默认的Innodb就足够了，如果要追求更好的性能，可以根据使用场景结合存储引擎的特点来选择使用最合适的存储引擎：
数据库设计的分库分表是为了解决大数据量、高并发的情况下数据库性能问题的一种解决方案。一般来说，采用分库分表可以有效地提升数据库的性能和可扩展性，但是需要考虑如下问题：
针对以上问题，一些分库分表策略建议：
在MySQL中，使用COUNT(*)进行计数时，如果查询的表中有主键或非空唯一索引，则MySQL可以直接使用该索引进行计数，因此性能与使用COUNT(column)相当。而如果查询的表没有主键或非空唯一索引，则MySQL会执行全表扫描来计算行数，此时性能会比使用COUNT(column)差。因此，在查询性能方面，使用COUNT(*)和COUNT(column)并没有绝对的优劣之分，需要根据具体情况来选择使用哪种方式。
MySQL会对in列表的值排序，搜索时通过二分查找来判断是否在列表中。所以in的时间复杂度是O(logn)，而or的时间复杂度是O(n)，in的效率更高。如果or有大量数据，建议使用in。
在进行分页查询时，LIMIT是常用的关键字，但是当数据量较大时，使用LIMIT会有一定的性能问题。为了优化LIMIT分页，可以考虑以下两种方案：
使用游标分页
使用游标分页的原理是，在每次查询时，只查询指定数量的数据，然后再记录下最后一条数据的位置，作为下一次查询的起始位置，以此类推。这种方式的好处是，不需要将所有的数据都查询出来，减少了查询的数据量，可以有效提高查询效率。但是，使用游标分页的缺点是，需要在程序中维护游标，增加了程序的复杂度。
使用联合查询分页
使用联合查询分页的原理是，先查询出指定数量的主键，然后再使用主键去查询对应的数据，以此来达到分页的效果。这种方式的好处是，只需要查询主键，可以大大减少查询的数据量，提高查询效率。但是，使用联合查询分页的缺点是，需要进行两次查询，增加了查询的时间。
总的来说，在进行分页查询时，要根据具体情况选择合适的优化方案，以达到较好的查询效果。
假设有一张名为students表，有10000条记录，每次查询需要分页展示10条数据，那么可以使用如下的SQL语句进行分页查询：
这里的LIMIT语句中，第一个参数指定了查询结果的起始行数，第二个参数指定了查询结果的行数。
但是，如果数据库中有大量数据，这样的查询会非常慢。因此，可以通过优化来提高查询效率。
首先，为了避免全表扫描，应该在students表上创建一个主键索引：
接着，可以将查询语句进行优化，将起始行数作为查询条件，这样就可以直接命中索引，提高查询效率：
这里的查询语句中，WHERE子句中的id > x条件就是根据上一页最后一条数据的id值作为查询条件，查询下一页数据。这样就可以避免全表扫描，提高查询效率。
Union语句用于将两个或多个查询结果合并为一个结果集，但是在使用Union语句时也需要注意性能问题。以下是一些优化Union语句的技巧：
总之，Union语句可以帮助我们将多个查询结果合并为一个结果集，但是在使用Union语句时需要注意一些性能问题，尽量避免影响查询效率的操作。
假设有两张表，一张是 table1，有字段 id 和 name，另一张是 table2，有字段 id 和 age。现在要将两张表中的记录合并，并按 id 排序。一种常见的写法是使用 UNION：
这里第二个 SELECT 语句中使用了 NULL，是为了让 table1 和 table2 中的记录在合并后拥有同样的字段数。但是这样会导致 MySQL 在执行排序时使用文件排序算法，从而降低查询效率。
一个优化方法是使用 UNION ALL，并使用 IFNULL 函数为 table2 的 age 字段设置默认值：
这样可以避免使用文件排序算法，提高查询效率。同时，为了减少查询的数据量，可以使用 LIMIT 进行分页查询。例如：
这样可以查询出第 11~20 条记录。
优化 JOIN 语句是数据库优化的一个重要方向之一，可以有效提高查询性能。以下是一些优化 JOIN 语句的方法：
下面是一个使用JOIN语句进行查询的示例，对其进行优化：
在优化后的示例中，使用了子查询将需要JOIN的表的关键字段和名称提前查询并保存到临时表中，避免了在JOIN语句中进行大量的子查询操作，从而提高了查询性能。
1.传智播客教育科技股份有限公司-高教产品研发部，《MYSQL数据库入门》，清华大学出版社，2018.
2.Mysql使用索引的正确方法及索引原理详解_Mysql_脚本之家
3.深入理解MySQL索引原理和实现——为什么索引可以加速查询？_为什么查询语句会加快查询速度_tongdanping的博客-CSDN博客
5.https://www.cnblogs.com/realshijing/p/8419732.html
6.《高性能MySQL》
7.《MySQL技术内幕：InnodDB存储引擎》
8.极客时间《MySQL实战45讲
9.蔡泽胤, 《MySQL核心原理与性能优化》
