此篇是本人在准备java开发岗位时准备的一些关于mysql的优化和一些面试需要特别注意的地方,还有诸多面试知识点在主页,欢迎大家查看,互相交流学习~~ 第一部分链接 第二部分链接 这是第三部分后续还会更新集群、锁和其他高频面试问题
索引是加速数据库查询的关键。在设计表结构时,应该根据查询的需求添加合适的索引。常用的索引包括主键、唯一索引、普通索引、联合索引、前缀索引(vachar、text这种长的数据并且只需要前几个区分度就很高)等。
同时,要避免过多的索引,因为每个索引都需要占用存储空间,会影响写入性能。
优化查询语句是提高MySQL性能的重要手段。要尽可能使用索引,避免全表扫描。同时,要避免使用子查询,尽可能使用连接查询;避免在查询中使用“%”通配符;避免多余的字段等等。
合理的表结构可以提高查询效率和减少存储空间。应该避免使用大字段,如TEXT、BLOB等,因为这些字段会占用大量的存储空间。同时,应该避免冗余字段,避免更新和维护时的复杂性。
①单库不超过200张表
②单表不超过500w数据
③单表不超过40列
④单表索引不超过5个
使用缓存可以大大减轻MySQL数据库的压力,提高查询效率。常用的缓存技术包括Memcached和Redis等。
对于数据量较大的表,可以使用分区技术将表分成多个部分。这样可以提高查询效率,同时降低了单个表的存储空间和索引大小。
MySQL的参数配置会影响MySQL的性能。需要根据实际情况进行调整,包括缓冲区、连接数、线程数、查询缓存等等。
硬件设备也会影响MySQL的性能。要选择更快速的硬件设备,如更快的磁盘、更快的CPU和更多的内存等等。同时,要根据实际情况来决定使用RAID、SSD等技术。
#客户端连接服务端时,加上参数--local-infile
mysql --local-infile -u root-p
#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.loginto table tb_user fields terminated by ',' lines terminated by '\n';
主键顺序插入性能高于乱序插入
满足业务需求的情况下,尽量降低主键的长度。
插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
业务操作时,避免对主键的修改。
#没有创建索引时,根据age, phone进行排序
explain select id,age,phone from tb_user order by age , phone;
#创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
#创建索引后,根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age , phone;
#创建索引后,根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc ;
#根据age, phone进行降序一个升序,一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;
#创建索引
create index idx_user_age _phone_ad on tb_user(age asc ,phone desc);
#根据age, phone进行降序一个升序,一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
尽量使用覆盖索引(查询的字段在联合索引中可以直接查询到不需要进行回表查询)。
多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
在分组操作时,可以通过索引来提高效率。
分组操作时,索引的使用也是满足最左前缀法则的。
#执行分组操作,根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
#创建索引
Create index idx_user_pro_age_sta on tb_user(profession , age , status);
#执行分组操作,根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
#执行分组操作,根据profession字段分组
explain select profession , count(*) from tb_user group by profession,age;
一个常见又非常头疼的问题就是limit 2000000,10,此时需要MySQL排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = aid;
count的几种用法
count(主键) : InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
count(字段) : 没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。
count ( 1) : InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。
count (*) : InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累
按照效率排序的话,count(字段)此count(主键id)< count(1)≈count(*),所以尽量使用count()
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
如果update过程中where条件是没有索引的将会从行锁升级为表锁。
如果where条件是有索引的就会是正常的行锁。