索引及mysql性能的优化:
1、删除冗余索引,通过工具linux环境下可以查到冗余索引并且可以给出索引的建议
2、order by的时候对索引的影响,order by的字段尽量是索引字段,并且字段顺序和索引顺序保持一致,排序顺序和索引顺序保持一致
3、释放表空间 执行语句:optimize table t_content(表名),会锁表,必须在项目维护期间执行,否则会造成IO阻塞
索引重新统计 执行语句: analyze table t_content(表名)
4、not in ,<>, where子句跳过左侧索引列,直接查询右侧索引列(联合索引) 、索引选择性太差(查询结果太多或者重复),都会影响索引性能,尽量避免
5、查询数据库中所有表索引的状态:(把从未使用过的冗余索引可以删除; 可以看到哪个表从来未使用过索引,排查下问题; 查看使用时间长的索引是否可以进行优化)
SELECT
object_type,object_schema,object_name,index_name,
count_read,count_fetch, count_insert,
count_update,count_delete
FROM
performance_schema.table_io_waits_summary_by_index_usage
ORDER BY
sum_timer_wait desc;
6、分区表partiton关键字建立分区表(存在mysql/data目录下的几个文件),分区的列字段必须是数字类型的,分区表可以在物理层面把一张表分成多个表,指定范围查找(具体查询文档),但是有很多局限性,根据场景进行选择
7、分库分表中间件 Sharding Sphere(前身是Sharding JDBC),大的数据源拆成多个小的数据源然后部署在不同的服务器上,每台服务器上的数据是不同的
8、设置connections最大连接数
1.1先查看线上历史最大连接数:show status like 'Max_usede_connections%';比如查出最大连接数是4,上浮 20-50%
1.2如果是新服务器,先设置最大连接数设置的大一些,比如3000,等后期观察后最大连接数是4,再上浮20-50%,确定最终的连接数
9、对系统进行优化考虑的方面:
1.1 增加innodb缓存命中率,Buffer_Pool让查询更多命中缓存,在操作系统层面,增加文件系统缓存,减少文件IO次数
1.2 web容器增加过滤器,拦截垃圾重复数据
1.3 分析业务代码是否存在全表扫描或者索引选择性问题
1.4 增加redis组件的引入,但是会引入一系列redis相关的问题,根据系统架构综合考虑
10、架构是宏观设计的标准(不能只针对技术,并且也要考虑全局设计成本,也要在上下级关系进行平衡),框架是具体实现的规则
11、INNODB性能参数设置
12、查询缓存参数设置
13、centos7操作系统参数调优(系统的并发数可以设置到最大65535,默认是128)
14、服务器操作系统的选择(优先选linux操作系统)
15、mysql服务器硬件的选择
扩展知识:
1、B+tree索引和hash索引比较:
B+tree索引是范围搜索 ,默认排序的簇集索引,原理:底层是一个树结构,根据链表和指针进行关联
hash索引是精准搜索,默认无序的簇集索引,原理:生成一个hash值与具体的行对应
innodb默认只能显示创建B+tree索引,只有Memory可以显示创建hash索引
mysql会把很多sql根据查询优化器自动优化
慢sql日志,排查生产上的慢sql,需要在mysql安装目录下的,my.cnf文件中设置参数,并且把慢sql写入自己命名的慢sql文件中
2、增加冗余字段,就是反范式设计(体现:新增外键id)
反范式设计的优缺点以下几点:
1. 单表查询易于优化,易于管理
2. SQL语句简单,有利于程序开发,团队协作
3. 存在数据冗余,写操作时需要额外更新从表数据
4. 不合理的反范式设计会让表变得臃肿不堪