数据库优化
关于数据库优化分可以以下几个方面来讲
先说几个小的注意点
当表中无主键时,首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键,如果不符合上述条件,InnoDB存储引擎自动创建一个6byte自增主键,且_rowid无法被查询到,如果使用int类型自增主键为4byte,所以在创建表时,必选显式地指定主键
我们平时使用的mysql的引擎时innodb,是mysql四种引擎中唯一支持事务的
一张表有且只有一个聚集索引就是主键
最左匹配原则
离散型、选择性最好的列放在最左边
mysql索引底层实现是B+tree
数据库相关参数的调优
1、可视情况开启缓存查询,开启缓存查询后,当表内数据和字段没有变化时,相同sql查询结果会从mysql缓存中读取,不会再次查询可提高效率
tips:即使sql相同但编码不同的查询语句,也会被mysql认为是两个查询,会重新查询
2、设置最大连接数,默认为100,最大为16384(超过的无效)
存储过程
可根据实际业务需求适应存储过程
sql语句
sql语句的优化最主要的就是避免全表扫描
1、避免select * 的存在,会导致全表扫描,同时也会增加io负担
2、在可以确定只存在一个查询结果或者判断库中有无符合条件数据时,使用limit 1来限定查询结果,limit 1会在查询到一条信息后停止查询返回结果,而不是继续往后查询下一条符合条件的数据,即使用select 1 from table where condition limit 1,来代替select count
3、like本身效率就比较低,所以应该尽量避免使用like,左like无法使用索引,右like可以
4、避免在where子句中对字段进行null判断,使用null会导致全表扫描
5、在照顾到实际业务需求的同时,在where、order by和join涉及的列上建立索引
6、使用UNION ALL 代替 UNION,如果结果集允许重复的话,UNION会去重效率较低
总结
SQL 优化的实质就是了解优化器的工作原理,尽可能的使用符合优化器工作原理的sql和索引
表的优化
字段的选择
为列选择合适的数据类型
1、在varchar和char中选择时,应尽量选择char,定长字段查询比可变长度字段快,简言之就是空间换时间
2、能用TINYINT就不用SMALLINT,能用SMALLINT就不用INT,磁盘和内存消耗越小越好
索引
索引是帮助mysql更加高效获取数据的数据结构,索引建立的应该遵循一下几个原则
1、where后面匹配的索引关键字列越多越好,扫描的数据越精确越少越好(通过索引筛选出的数据越少越好)
2、避免再次排序
3、尽可能的使用覆盖索引,减少回表操作
覆盖索引
当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中 (联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引,通过使用覆盖索引,可以减少搜索树的次数,这就是 覆盖索引,
同时在某些情况下会发生索引失效的问题,我们在查询时应避免这些情况的发生
- 对索引列运算及使用聚合函数
- or的左右需同时使用索引,如果只有or的左侧或右侧使用索引,也会导致索引失效
- like以%开头
- 索引列上使用!=、<>、not时会导致索引失效
- 数据类型发生隐式转换时,varchar类型列的数字不加单引号可能会导致数据类型转换
分库分表
暂时不太了解,先按下不表
才疏学浅斗胆在此抛砖引玉,还望各路大神口下留情