MySQL优化

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+树频繁重建。