数据库优化

一,Mysql数据库中一个表里有一千多万条数据,怎么快速的查出第900万条后的100条数据?
      select * from table limit 9000000,100;       查了100条数据用了7.063s。
      优化:
          1.使用InnoDB作为存储引擎,id作为自增主键,默认为主键索引。那我们现在用覆盖索引查询
             SELECT id FROM test LIMIT 9000000,100;
          1,id>=的(子查询)形式实现
    select * from test where id >= (select id from test limit 9000000,1)limit 0,100          4.262s;
           2,使用join的形式;
                 SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id      4.251s;  推荐
`mar_id`, `mer_id`, `member_id`, `update_time`, `pay_type`, `status`, `order_sn`
二、什么是mysql的最左侧原理_Mysql组合索引最左侧原理详解
       结论:你建立的组合索引(a,b,c,d) 无论怎么使用只要和a没有组合(最左面的那个),那么都没有使用上索引!!!!!


1、system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
2、const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
3、eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
4、ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
5、range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引
6、index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
7、ALL:Full Table Scan,遍历全表以找到匹配的行
索引不足之处
(1)索引提高了查询的速度,但是降低了INSERT、UPDATE、DELETE的速度,因为在插入、修改、删除数据时,还要同时操作一下索引文件;
 
(2)建立索引未见会占用一定的磁盘空间。
索引方式 HASH和 BTREE比较
  (1)HASH
    用于对等比较,如"="和" <=>"

  (2)BTREE
    BTREE索引看名字就知道索引以树形结构存储,通常用在像 "=,>,>=,<,<=、BETWEEN、Like"等操作符查询效率较高;

   通过比较发现,我们常用的是BTREE索引方式,当然Mysql默认就是BTREE方式




二,你用过mysql那些存储引擎,他们都有什么特点和区别?
      区别:

InnoDB支持事务,MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
InnoDB支持外键,而MyISAM不支持。
InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。
Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;5.7以后的InnoDB支持全文索引了。
InnoDB支持表、行级锁(默认),而MyISAM支持表级锁。;
InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有。
Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI。
Innodb:frm是表定义文件,ibd是数据文件。
Myisam:frm是表定义文件,myd是数据文件,myi是索引文件。

三,Mysql复杂查询语句的优化,你会怎么做?

1.使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈,就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的,是否有全表扫描等;
2.查询的条件尽量使用索引字段,如某一个表有多个条件,就尽量使用复合索引查询,复合索引使用要注意字段的先后顺序。
3.多表关联尽量用join,减少子查询的使用。表的关联字段如果能用主键就用主键,也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
4.尽量使用limit进行分页批量查询,不要一次全部获取。
5.绝对避免select *的使用,尽量select具体需要的字段,减少不必要字段的查询;
6.尽量将or 转换为 union all。
7.尽量避免使用is null或is not null。
8.要注意like的使用,前模糊和全模糊不会走索引。
9.Where后的查询字段尽量减少使用函数,因为函数会造成索引失效。
如:select id from t where substring(name,1,3)=’abc’ ,name以abc开头的id应改为:
select id from t where name like ‘abc%’
10.避免使用不等于(!=),因为它不会使用索引。
11.用exists代替in,not exists代替not in,效率会更好;
    .in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3
12.避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
13.千万不要 ORDER BY RAND()
posted @   minch  阅读(29)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
点击右上角即可分享
微信分享提示