了解Mysql优化吗？如何优化索引？
对索引使用左或者左右模糊匹配在MySQL中，LIKE 模糊查询可能会导致性能问题，特别是当使用通配符 % 开头时，因为这通常会导致全表扫描，也就是 like %xx 或者 like %xx% 这两种方式 (左或者左右模糊匹配的时候) 都会造成索引失效。
[*]前缀匹配：一般使用LIKE 'prefix%'的形式，这种情况MySQL能够利用索引
SELECT * FROM users WHERE username LIKE 'seven%';
[*]对于一定需要匹配后缀的情况（即LIKE '%suffix'），可以创建一个辅助列存储反转字符串，并基于此列进行前缀匹配。
ALTER TABLE users ADD reversed_username VARCHAR(255);
UPDATE users SET reversed_username = REVERSE(username);
CREATE INDEX idx_reversed_username ON users(reversed_username);计算(使用函数)
因为索引保存的是索引字段的原始值，而不是经过计算后的值，自然就没办法走索引了。
函数计算或者表达式计算都没办法走索引
//函数计算
select * from t_user where length(name)=6;
//表达式计算
select * from t_user where id + 1 = 10;不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。
但是在索引范围之外使用函数是不影响索引的： 即函数不在索引范围（即WHERE或ON条件）使用时，索引仍然有效。例如，在 SELECT 列表、ORDER BY、GROUP BY等地方使用函数通常不影响索引的使用。
类型转换
[*]如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，那么这条语句会走全表扫描。
[*]但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。
原因在于MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。也就是说，如果索引字段是整型类型，查询条件中的输入参数是字符串，会自动转换成整型，所以索引不会失效。而索引字段是字符串，而输入的是整型，由于是字符串转数字，而索引不是整型类型，所以索引失效了。
因此在使用sql语句时：数值类型禁止加引号，字符串类型必须加引号
联合索引非最左匹配
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
[*]where a=1；
[*]where a=1 and b=2 and c=3；
[*]where a=1 and b=2；
因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
[*]where b=2；
[*]where c=3；
[*]where b=2 and c=3；
对于where a = 1 and c = 0 这个语句，前面的a = 1是会走索引的，后面的c不走索引。
where条件的顺序影响索引使用吗
在MySQL中，WHERE条件的顺序确实可能影响索引的使用，尤其是在使用复合索引（多列索引）的情况下。但是，这种影响主要取决于MySQL查询优化器的工作方式，而不是直接由WHERE子句中条件的书写顺序决定的。
[*]MySQL的查询优化器通常会尝试重新排列WHERE条件以最优化索引的使用。
[*]对于复合索引，索引的使用遵循"最左前缀"原则。
[*]虽然WHERE条件的顺序通常不会影响索引的使用，但将索引列的条件放在前面可能会使查询计划更易读和理解。
不应使用 or
在 WHERE 子句中or，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。
in
尽量使用IN代替OR。但是IN包含的值不应过多，应少于1000个。
因为 IN 通常是走索引的，当IN后面的数据在数据表中超过30%的匹配时是全表的扫描，不会走索引
其实就是 Mysql优化器会根据当前表的情况选择最优解。 Mysql优化器认为走全表扫描 比 走索引+回表快 那就不会走索引
范围查询阻断，后续字段不能走索引
索引
KEY `idx_shopid_created_status` (`a`, `b`, `c`)SQL语句
select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10所谓的停止四配指的是：a可以用上联合索引，但是 b和c 却不行。因为 b 需要先经过范国查询，此时经过饰选得到 c 的数据是无序的。比如a为1和a为2数据中的 b和c是无序的，因此无法利用索引查询。
如果遇到如>=、=(select id from xxx order by id limit 500000, 1) order by id limit 10;先执行子查询 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到500000+1条数据，然后server层会抛弃前500000条，只保留最后一条数据的id。
但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。
在拿到了上面的id之后，假设这个id正好等于500000，那sql就变成了
select * from _t where a=1 order by b desc, c asc这样innodb再走一次主键索引，通过B+树快速定位到id=500000的行数据，时间复杂度是lg(n)，然后向后取10条数据。
方法二：根据id主键进行排序
记录上次查询的最大ID（或其他唯一标识符），并以此为起点进行下一次查询。这种方法需要有连续的、唯一的列（如自增ID）以用于分页。
select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1通过主键索引，每次定位到start_id的位置，然后往后遍历10个数据，这样不管数据多大，查询性能都较为稳定。
大表查询慢怎么优化？
某个表有近千万数据，查询比较慢，如何优化？
当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：
[*]合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
[*]索引优化，SQL优化。索引要符合最左匹配原则等
[*]建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
[*]利用缓存。利用Redis等缓存热点数据，提高查询效率
[*]限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
[*]读写分离。经典的数据库拆分方案，主库负责写，从库负责读
[*]通过分库分表的方式进行优化，主要有垂直拆分和水平拆分
[*]数据异构到es
[*]冷热数据分离。几个月之前不常用的数据放到冷库中，最新的数据比较新的数据放到热库中
[*]升级数据库类型，换一种能兼容MySQL的数据库（OceanBase、TiDB等）
什么时候索引失效反而提升效率
[*]小表查询：对于非常小的表，MySQL可能会选择全表扫描（忽略索引），因为全表扫描的开销可能比通过索引逐行查找还要低。在这种情况下，索引失效不会损害性能，反而简化了查询。
[*]读取大部分或所有行：当一个查询返回表中很大比例的行（如 30% 或更多）时，使用索引查找可能会耗时更多，因为数据库必须跳回主数据页以读取完整记录。全表扫描可能更有效，因为它可以逐行顺序读取数据。
[*]低选择性索引：如果索引列的选择性非常低，例如一个布尔型字段，许多行有相同的值，那么依赖索引可能会产生不必要的开销。全表扫描可以避免索引的搜索和回表开销。
[*]频繁更新的表：对于包含大量更新操作的表，索引的维护成本可能相对较高。尤其是在频繁更新索引列时，通过避免使用或减少复杂的索引可以减轻写操作的负担。
[*]复杂查询的优化选择：对于复杂的多表联接查询，优化器有时可以选择执行计划中不使用某个索引（或部分失效）以提高整体联接和计算效率。
[*]数据分布与优化器误判：在某些特定情况下，如果索引导致MySQL错误地估计数据分布或行数，手动禁用索引或提示优化器使用不同策略可能提升性能。
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]