SQL优化
SQL优化
SQL优化技术
- 对表的设计合理化（符合3NF）
- 添加适当的索引（index）[普通索引,主键索引,唯一索引,全文索引]
- 分表技术(水平分割,垂直分割)
- 读写(update/delete/add)分离
- 存储过程(模块化编程)
- 对MySQL配置优化(配置最大并发数 my.ini , 调整缓存大小)
- MySQL服务器硬件升级
- 定时清除不需要的数据,定时进行碎片整理
一、什么样的表才符合3NF
-
1NF：对属性的原子性约束，即表的列具有原子性，不可再分解。
-
2NF：表中的记录是唯一的，实体属性完全依赖于主键，每张表只描述一件事情。
-
3NF：表中不能有冗余数据，任何非主属性不依赖于其他非主属性，从表的外键必须使用主表的主键。
-
反三范式：某些业务场景下，为了提高效率，可能会适当降低范式标准，增加字段，允许冗余。
二、索引
1.索引
-
添加索引
-
主键索引：当一张表把某个列设为主键的时候,该列就是主键索引
-
普通索引：一般先创建表，再创建普通索引
create index 索引名 on 表(列)alter table 表名 add index 索引名 （列）
-
全文索引：全文索引主要针对文件，文本的检索，比如文章，全文索引只针对myisam引擎有效
-
create table test (
xxx VARCHAR(20),
yyy TEXT,
FULLTEXT(xxx,yyy)
)engine=myisam charset utf-8;
-
使用全文索引:
select * from test where match(xxx,yyy) against('aaa'); -
fulltext索引只针对myisam生效;只针对英文生效->sphinx处理中文;停止词概念,因为在一个文本中,创建索引是一个无穷大的数,对一些常用词和字符,就不会创建,这些词叫停止词
-
-
唯一索引：当表的某列被指定为unique约束时，这列就是一个唯一索引
- create unique index 索引名 on 表名（列）
-
查询索引
- desc 表名 【缺点是：不能显示索引名】
- show indexes from 表名 \G
- show keys from 表名
-
删除索引
- alter table 表名 drop 索引名
- 删除主键 alter table 表名 drop primary key
-
修改索引
- 先删除，再创建
-
为什么创建索引，速度会变快
二叉树算法(BTREE)--->构建索引文件--->中间点为根节点--->再取中间--->小的放在左子树,大的右子树--->...
检索次数log 2N
没有创建索引时,dbms必须一条条检索,就算已经检索到指定记录,也要检索完所有记录才会停止
-
索引的代价
- 磁盘占用
- 对DML语句的效率影响：要维护索引，增删改会影响索引
-
哪些列适合添加索引
-
较为频繁作为查询条件的字段应创建索引
-
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
-
更新非常频繁的字段不适合创建索引
-
不会出现在where子句中的字段不该创建索引
总结：满足以下条件的字段，才应该创建索引：
- 在where子句中常使用
- 该字段内容不是确定的几个值（sex）
- 字段不会频繁变化
-
-
索引的使用
- 查询要使用索引最重要的是查询条件中需要使用索引
- 下列情况可能会使用索引
- 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
- 对于like的查询，查询如果是
%aaa不会使用索引，aaa%会使用索引,如果必须使用变化值,则考虑使用全文索引
- 下列情况不会使用索引
- 如果条件中有or，即使条件中带索引也不会使用索引,换言之,就是所有使用字段都必须建立索引
- 对于多列索引，不是使用的第一部分则不会使用索引
- like查询以%开头
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
- 如果MySQL估计使用全表扫描要比使用索引快，则不使用索引
查看索引使用情况:
show status like 'Handler_read%'handler_read_key:值越高越好,越高表示使用索引查询到的次数handler_read_rng_next:这个值越高,说明查询低效
三、慢查询
show status命令
-
了解各种SQL的执行频率
-
show status like 'xxx'（uptime，com_insert,com_update,com_select,connection)
show [session|global] status like 'xxx' 默认是session会话
-
show status like 'slow_queries' 显示慢查询
- 默认情况下,10s才是一个慢查询 set long_query_time = 1; 设置慢查询时间
-
-
定位执行效率较低的SQL语句(select)
- 为存储过程的正常执行,调整命令结束符 delimiter $$;
-
通过explain分析低效率的SQL语句执行情况
-
确定问题采取优化措施
四、常用SQL优化
- 大批量插入数据
- 对于MyISAM:
- 先禁用索引 alter table 表名 disable keys;
- 再启用索引 alter table 表名 enable keys;
- 对于Innodb:
- set unique_checks=0,关闭唯一性校验
- set autocommit=0,关闭自动提交
- 对于MyISAM:
SQL语句技巧
-
在使用group by分组查询时,默认分组后,还会排序,可能会降低速度
- 可以使用 order by null 禁止排序提速
-
有些情况可以使用join 代替子查询
- 使用join,不会在内存中建立临时表
-
如果要在含有or的查询语句中利用索引,则or之间的每个条件都必须使用到索引,如果没有,则考虑增加索引
选择合适的存储引擎
MyISAM:表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam引擎,比如论坛的发帖表,回复表,优势是访问速度快
- 如果存储引擎是MyISAM:必须定时进行碎片整理:
optimize table 表名
InnoDB:提供了具有提交/回滚和崩溃恢复能力的事务安全,但是效率差一些且会占用更多的磁盘你空间,例如订单表,账号表
Memory:数据频繁变化,不需要入库,同时又频繁的查询和修改,考虑使用memory
MyISAM和InnoDB区别:
- 事务安全
- 查询和添加速度
- 支持全文索引
- 锁机制
- 外键:MyISAM不支持外键,InnoDB支持外键
表分割技术
- 水平分割
例如：在开发addUser时，在添加用户时，各个用户的id确认，可以通过使用一个辅助表UUID帮助生成一个编号
分表规则：login0，login1，login2 根据用户id%3的值来判断这个用户在哪个表中
- 垂直分割
某些表的某些字段，这些字段在查询时并不是经常关心的，但是数据量很大，建议把这些字段单独放到另外一张表，从而提高效率
选择适当的字段类型，特别是主键
- 选择字段的原则是保小不保大，能用占用字节小的就不用占用大字段，比如主键，建议使用自增类型，这样节省空间，涉及到几个表join时，效果更明显
文件、图片等大文件用文件系统存储
- 数据库只存储路径。图片和文件放在文件系统，甚至单独放在一台服务器
数据库参数配置
- 最重要的参数就是内存innodb引擎
- innodb_additional_mem_pool_size=xxx
- innodb——buffer_pool_size=xxx
- 对于myisam，需要调整key_buffer_size
- 调整参数也要看状态，show status语句可以看到当前状态，决定调整哪些参数
- 在my.ini修改端口，默认存储引擎和最大连接数
读写分离
- 如果数据库压力很大，一台机器支撑不了，可以用MySQL复制实现多台机器同步，将数据库的压力分散
浙公网安备 33010602011771号