【大汇总】mysql优化&索引

村口阿花
94 阅读9分钟

内容会持续更新补充...

一些概念

最佳左前缀法则 多列索引，查询从索引的最左前列开始且不跳过索引中的列

索引下推 参考：www.modb.pro/db/523853 新版本的MySQL（5.6以上）中引入了索引下推的机制：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 索引下推的主要功能就是改善这一点，在联合索引中，先通过姓名和年龄过滤掉不用回表的记录，然后再回表查询索引，减少回表次数

主要应用在联合索引中不符合左匹配原则的情况

索引失效的情况以及解决方法

  1. 左模糊查询（like查询以%开头） 解决：用搜索引擎解决

  2. or语句前后没有同时使用索引

  3. 组合索引中不是使用第一列索引

  4. 在索引列上使用函数、运算

  5. 在索引列上使用”IS NULL“或”IS NOT NULL“

  6. 在索引字段上使用负向条件，比如：not ，<> ，!= 等

  7. 字段类型需要转换。比如：列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则索引失效。

  8. 表数据少

索引优化

1. like语句的前导模糊查询不能使用索引
select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决

2. union、in、or 都能够命中索引，建议使用 in

  • union能够命中索引，并且MySQL 耗费的 CPU 最少

  • in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in

  • or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or

    补充： 有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区： ①要求where子句使用的所有字段，都必须建立索引； ②如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引； ③确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。

3. 负向条件查询不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等 解决：可以优化为in查询

4. 联合索引最左前缀原则
  • 如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。
  • 登录业务需求，SQL语句如下：
select uid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

  • 建立联合索引的时候，区分度最高的字段在最左边。
  • 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列。
  • 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。
  • 下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引：
select uid, login_time from user where passwd=? andlogin_name=?

但还是建议 where 后的顺序和联合索引一致，养成好习惯。假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

5、不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效
  • 范围条件有：<、<=、>、>=、between等。
  • 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
  • 假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。
6、不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描
7、强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作

8、更新十分频繁、数据区分度不高的列不宜建立索引
  • 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
  • 性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
  • 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。
9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用
  • 覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。
  • 被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。
10、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null约束以及默认值

11、is null, is not null无法使用索引
12、如果有order by、group by的场景，请注意利用索引的有序性
* order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。
例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

* <b>如果索引中有范围查找，那么索引有序性无法利用，如WHERE a>10 ORDER BY b;，索引(a,b)无法排序

mysql配置调优

segmentfault.com/a/119000003…

max_connections

MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户(不是使用浏览器的用户，而且与MYSQL连接的服务器！)均算作一个连接，max_connections的默认值为100。

MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1；这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准

ysql too many connections 解决方法

www.jianshu.com/p/fc40067c4… help.aliyun.com/document_de…

想值设置计算方法

max_used_connections / max_connections * 100% （理想值≈ 85%）

MySQL中的连接、实例、会话

www.cnblogs.com/yasmi/artic…

back_log

MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。 back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。 默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数

不同索引在搜索上的区别

唯一索引 从索引树根节点开始按层往下查找,找到对应的数据页,然后二分法查找节点,找到第一个匹配的数据后,直接返回.

普通索引 普通索引,从索引树根节点开始按层往下查找,找到对应的数据页,然后二分法查找节点,找到第一个匹配的数据后,继续往后查找,直到不满足条件.然后返回

InnoDB引擎使用drop命令删除索引是否会释放磁盘空间

在InnoDB存储引擎中，使用DROP命令删除索引通常不会立即释放磁盘空间。当你执行DROP INDEX命令时，它会从数据库的数据字典中删除索引的定义，但实际数据文件并不会立即收缩以释放磁盘空间。InnoDB引擎通常会将已删除的数据标记为"未使用"，而不是立即将其从磁盘上删除，以提高性能和避免频繁的磁盘操作。

InnoDB会在后续的操作中（例如INSERT、UPDATE或DELETE）重用这些未使用的磁盘空间，从而降低了碎片化的风险。当需要更多磁盘空间时，InnoDB会将这些未使用的空间重新分配给新的数据。这种机制有助于提高性能，因为避免了频繁的磁盘扩展和收缩操作。

如果你希望立即释放磁盘空间，可以考虑使用InnoDB的OPTIMIZE TABLE命令，或者重建表来去除未使用的空间。这将重新组织表，删除未使用的磁盘空间，并可能提高性能。但请注意，这些操作可能会导致表锁定和较长的处理时间，因此需要谨慎使用，特别是对于大型表。

总之，InnoDB引擎在删除索引时不会立即释放磁盘空间，而是在后续操作中重用这些空间。如果你需要立即回收空间，可以考虑执行适当的表维护操作

avatar
文章
阅读
粉丝