1、尽量避免使用select *
我们需要哪些属性就取哪些,避免全盘接收。比如我们比如我们只需要查询id和name两个属性,那么我们可以使用SELECT id,name FROM TEACHER,尽量避免使用select * ,虽然有时效果一样,但效率不一样。
2、union-all 代替union
如果业务上没有特殊说明,可以考虑用UNION-ALL 替换UNION,因为UNION-ALL不会过滤重复数据,所执行效率要快于UNION,并且UNION可以自动排序,而UNION-ALL不会,这些细节往往都影响了数据库的性能。
3、不使用NOT IN和like语句
NOT IN和like“%aaa%”操作都不会使用索引,将进行全表扫描。可取的方法是NOT IN可以NOT EXISTS代替 。
4. 为经常用来查询的字段添加索引
普通索引 添加INDEX ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
主键索引 添加PRIMARY KEY ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
唯一索引 添加UNIQUE ALTER TABLE `table_name` ADD UNIQUE ( `column` )
全文索引 添加FULLTEXT mysql> alter table table_name add fulltext (`column` )
5、 在查询语句select前面加上explain
EXPLAIN关键字的作用是让你可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构。从根本处找出可以优化的地方,EXPLAIN的查询结果也会告诉你,你的索引主键被如何利用的,你的数据表是如何被搜索和排序的,通过对这些信息的查看,你可以对自己的查询语句做相应的调整
explain select * from student where id=1;
注意:
1)、索引一般加在查询条件的关键字上,如果有多个查询条件关键字,还可以添加组合索引,写sql的时候需要注意,索引字段和sql字段需要保持一致,否则索引会无效。
2)、建立索引要选择区分度高的字段
3)、建立组合索引,可以持续提升sql运行效率,但是也不要盲目,同样的要注意区分度
6、利用查询缓存优化数据库查询
下面是配置和适用查询缓存的方法:
开启: 在my.ini中配置query_cache_type(0[off], 1[on], 2[demand]; 0不使用; 1默认使用缓存, 如果查询语句用SELECT SQL_NO_CACHE ...开头则不缓存; 2默认不使用缓存, 如果查询语句使用SELECT SQL_CACHE开头则使用缓存)
查看配置: show variables like '%cache%';
have_query_cache: 为YES表示缓存开启
query_cache_type: on表示默认使用缓存
query_cache_limit: 能缓存的单条查询结果容量最大值, 超过此值则不会缓存
query_cache_size: 查询缓存总共占内存空间大小, 一般设置为256M为一个不错的大小
查询缓存状态: show stats like 'Qcache%';
Qcache_free_blocks: 查询缓存中空闲的block数目
Qcache_free_memory: 空闲的内存总量
Qcache_cache_hits: 命中的次数
Qcache_cache_inserts: 向缓存中插入新的缓存结果的次数(也就是没有命中的次数)
Qcache_lowmem_prunes: 当 Query Cache内存容量不够,需要从中删除老的单条查询结果以给新的查询结果对象使用的次数
Qcache_not_cached: 没有被缓存的SQL数, 包括无法被缓存的SQL以及由于query_cache_type设置的不会被缓存的SQL
Qcache_queries_in_cache: 目前在内存中的SQL数量
Qcache_total_blocks: 内存中总的Block数量
当MySQL开启了缓存模式(query_cache_type=1)后,mysql会把查询语句和查询结果保存在一张hash表中,下一次用同样的sql语句查询时,mysql会先从这张hash表中获取数据,如果缓存没有命中,则解析sql语句,查询数据库。 当缓存的数据达到最大值(query_cache_size) 后,mysql会把老的数据删除掉,重新写入新的数据。
7、多使用limit
当我们知道查询表只会有一条结果。在这种情况下,我们可以加上LIMIT 1,这样可以增加性能。MySQL数据库引擎会在找到一条数据后停止搜索,而不是占据cpu继续往下查直到查询表中的最后一条数据为止。
8、分库分表分区
其实分库分表,也可以就是时下最流行的数据库的垂直拆分和水平拆分。
9、预处理
也就是说,如果对一些对实时性不强的数据也就是历史数据,大数据表历史数据且有表关联,通过常规sql难以优化,第二天,在去处理这些数据库,这种情况的解决方案就是预处理:将这些复杂表关联sql写成个定时任务在半夜执行,将执行的结果存入到一张结果表中,第二天直接查询结果表,如此,效率能得到十分明显提升
而一些实时性要求严格的数据,可以放在缓存里面,提升系统运行效率
10、读写分离
在数据库并发大的情况下,最好的做法就是进行横向扩展,增加机器,以提升抗并发能力,而且还兼有数据备份功能