mysql加索引优化 mysql索引如何优化

转载

轩辕 2023-09-04 14:02:46

文章标签 mysql加索引优化 mysql 数据库 sql 字段 文章分类 MySQL 数据库

一、索引设计原则

索引是优化数据查询效率的一种数据结构；

对查询频次高且数据量比较大的表建立索引；
索引字段的选择：最佳选列应从where子句中提取，也就是经常出现在条件语句中的字段；
唯一索引：区分度越高，使用索引的效率越高；
最左前缀法则：对N个列组合而成的组合索引，相当于创建了N个索引，当查询时where子句中使用了组成该索引的前几个字段，那么这条查询sql可以利用索引来提升查询效率；
create index idx_name_eamil_status on tb_seller(name,email,status)
where子句包含的索引字段是从最左侧的name字段开始且不跳过中间字段就会走这个复合索引

二、sql优化步骤

查看sql执行频率（show global status like ‘Com_%’ ）,可以查看到这个数据库的查询和更新次数；
定位低效率sql语句：慢查询日志、show processlist

三、索引的使用（避免索引）

全值匹配（对索引中所有列都指定具体值），在该情况下，索引生效，执行效率高
最左前缀法则：针对复合索引（包含最左列，不跳过中间列），违反最左前缀法则索引失效
范围查询遇到 ‘>’、‘<’ 时停止匹配
select * from table where name=‘xx’ and status> ‘1’ and address=‘北京’
字段name和status走了索引，而address没有走索引
对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
不要对索引列进行运算操作，否则索引失效
字符串不加单引号，索引失效
尽量使用覆盖索引，避免使用select *
覆盖索引就是包含的索引字段（不用回表查询）
用or分割开的条件，如果or之前的条件有索引，or之后的条件没有索引，那么整体都不会走索引
模糊查询：以%开头的like模糊查询，索引失效（只有当%位于最右边，索引生效）
对于实际开发中的 like name=‘%哈哈%’的模糊查询，解决办法：通过覆盖索引解决，也就是不要使用select *，查询索引字段
如果mysql评估发现全表扫描比索引更快，就不会走索引
is null和is not null有时会索引失效（当大部分都为null时，则不会走索引，is not null走索引）
in不一定走索引（当in后面的数据量太大时走全表扫描），not in不走索引，对于连续的数值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3；
很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
尽量使用复合索引，减少单列索引（多个单列索引，数据库会选择一个最优的单列索引来使用，并不会使用全部索引）
判断表是否为空，可以使用select exsits来代替，SELECT EXISTS(SELECT * FROM bridge_2021_01 WHERE sensor_id =‘371164’)，返回1代表不为空，返回0代表为空
更多索引优化策略

四、sql优化

insert优化：①批量插入 insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘June’)
②在事务中插入
start transaction;
insert into tb_test values (1,‘Tom’);
commit;
③有序插入，尽量按照主键的顺序插入
尽量使用多表联结查询来替换子查询
or优化：在or前后的字段都需要有索引，倘若有一方没有索引，则该语句整体不走索引；
可以使用union来替换or
select * from tb_test where id=1 union select * from tb_test where age=22

五、ACID底层实现原理

原子性（Atomicity):事务是一个不可分割的单位，是一个最小的操作单元；这个单元的操作要么全部成功，要么全部不成功。如果某一个SQL语句执行失败了，那么之前执行的SQL语句要执行回滚操作。实现原理：基于Undo log。Undo log会记录所有操作，一旦发生回滚，数据库就会按照Undo log做相反的操作，比如记录的是插入，那么数据库便会进行删除操作。
一致性（Consistency):事务执行之后，数据库的完整性约束没有被破坏，事务执行前后都是一个合法的数据状态。完整性体现在比如数据库的主键要唯一，字段类型大小要符合要求，外键的约束要符合要求。一致性是事务追求的最终目标。原子性、持久性、隔离性都是为了保证数据库最终的一致性。如果另外三个特性无法保证，那么一致性肯定也保证不了
隔离性（Isolation):写写操作：通过锁机制，保证当前只能有一个事务来操作某个数据。
持久性（Durability):实现原理：redo log。数据库除了要把数据写到Buffer中去，还会把内容记录到redo log里面，如果Mysql 宕机了，那么可以通过Redo log去恢复数据。Redo log是预写式日志，会把要进行的修改先写入到Redo log中，再更新到Buffer中。

六、并发事务处理带来的问题

丢失更新：后一个事务修改提交覆盖了前一个事务的修改操作
脏读：一个事务修改了某一数据但未提交，而另一个事务读取并使用了该数据
不可重复读：一个事务读操作前后数据不一致
幻读：一个事务按照相同的查询条件读取数据，发现其它事务插入了满足其条件的新数据

为了解决以上问题引入了事务隔离级别

隔离级别	丢失更新	脏读	不可重复读	幻读
读未提交	√（解决）	×（未解决）	×	×
读已提交	√	√	×	×
可重复读	√	√	√	×
串行化	√	√	√	√
mysql的默认事务隔离级别是：不可重复读

七、锁

共享锁（只能读）
排它锁（可以读写）
间隙锁：用范围条件查询数据时，对于键值在条件范围内但不存在的记录，叫做‘间隙’，innodb也会对这个间隙加锁，这种加锁机制就是’'间隙锁"
比如 select * from tb where id <10
其中id=1，2，3，4，6，9；
对于id=5，7，8的则为间隙