MySQL优化
1.索引的创建与优化
(1)七类索引
1.唯一索引:索引值唯一,但允许为null,由关键字Unique定义,一个表中可以有多个。
2.主键索引:不允许为空,索引值唯一,一个表中只能有一个。
3.单列索引:以表中其中一列为索引。
4.复合索引,以表中多列为索引。
5.全文索引,使用FullText设置,适用于数据量大的字符串列。
6.空间索引
(2)通过索引优化查询
(3)优化子查询
通过连接查询(连接查询的where条件使用索引)效率大于双层select循环
explain select * from zut.student join zut.stu_college colleg on college_id=colleg.id
where colleg.name='计算机学院';
(4)优化慢查询
show variables like 'long%';默认值是10秒,查询大于10s的sql语句会被记录下来。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xlgq8ONT-1639208083072)(C:\Users\57281\AppData\Roaming\Typora\typora-user-images\image-20210420183125410.png)]
show variables like 'slow%';
默认存储的位置:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IYEDdpHj-1639208083074)(C:\Users\57281\AppData\Roaming\Typora\typora-user-images\image-20210420183343316.png)]
1.对慢sql语句进行分析优化,改变投影、选择链接的位置,增大查询效率
2.使用排序-合并算法进行表关联的优化,两个表按照要进行关联的字段排好序,则两个表只需要遍历一次就可以实现关联操作。
3.对查询条件的列建立索引,提高索引命中率加快查询速。
4.对其中一个表建立冗余字段,实现单表查询
5.更改mysql配置:增大内存、增大最大链接数、提高查询缓存。
6。使用redis做缓存,减轻mysql查询压力.
7.分解关联查询,对每一个表进行单表查询,将查询结果在应用程序中进行关联。
8.查询时考虑使用覆盖索引,即select item_id from t_item 只查询辅助索引就可拿到数据,而不用去查询聚簇索引了。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0fdGQcwI-1639208083075)(file:///F:\qq下载\572812703\Image\C2C\Image2\257TY_IA6U90U_EQ%V}]1C6.png)]
2、sql语句是否走索引
1.对于复合索引来说,只有查询条件where使用了第一列索引,且不能是
2.使用like语句做通配符时,匹配的第一个字符不能时%,不然无法走索引。对于索引(a,b,c)
select * from user where username like '%xxx'; NO
xxx%:查询username字段中以xxx开头的记录。
select * from user where username like 'xxx%'; Yes
where a between '' and '' and b=''; NO
where a='' and b between '' and '' yes
联合索引
最左列匹配:联合索引是按照最做列进行排序的,对于其他列来说是乱序的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AaG4weS8-1639208083075)(C:\Users\57281\AppData\Roaming\Typora\typora-user-images\image-20210822134606492.png)]
联合索引(col1, col2,col3)也是一棵B+Tree,其非叶子节点存储的是第一个关键字的索引,而叶节点存储的则是三个关键字col1、col2、col3和三个关键字的数据。且按照col1、col2、col3的顺序进行排序。
3.MyISAM和InnoDb索引实现原理
MyISAM主键索引和辅助索引都采用B+树的原理,即叶子节点存数据,其他节点存索引和地址。
InnoDb索引:
辅助索引的叶子节点存储的不是数据,而是主键索引的引用。
辅助索引:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6LzWNujK-1639208083076)(C:\Users\57281\AppData\Roaming\Typora\typora-user-images\image-20210822131257030.png)]
主键索引:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BorAwbSM-1639208083076)(C:\Users\57281\AppData\Roaming\Typora\typora-user-images\image-20210822131526466.png)]
InnoDB索引实现:
虽然InnoDB也使用B+Tree作为索引结构
第一个重大区别是InnoDB的数据文件本身就是索引文件。数据是按主键索引的位置存储的。
所以主键不要采用过长的字段,会占空间。
主键应该具有单调性,防止主键索引B+树频繁重建。
结构
第一个重大区别是InnoDB的数据文件本身就是索引文件。数据是按主键索引的位置存储的。
所以主键不要采用过长的字段,会占空间。
主键应该具有单调性,防止主键索引B+树频繁重建。