需要优化的内容:项目数据库不合理:因为表设计不合理,没有设置索引(合理的索引),
SQL语句太慢并且重复,把频繁查询的字段用数据库查询,设置合理的存储引擎
优化方式:
1.解决SQL语句太慢并且重复:sql语句太慢可以先通过日志查询出来,并且进行优化
常见的优化方法:
单机:设计索引,水平和垂直分表,返数据库的三范式,选择合理的搜索引擎
多机:集群,分布式
分布式:Redis存储数据,ES全文搜索,页面静态化提供查询数据效率
查询sql流程:判断权限-分析SQL-优化SQL-执行SQL
一、查询慢查询步骤:
1.可以根据show globle status like '%com_select%'--查询全局的
(查询数据库的SQL扩展)
- 查看INNODB引擎的CRUD行数--show status like '%Innodb_rows%';
- 查询所有连接数--show status like 'connections'
- 查看服务器响应的最大使用连接数--show status like 'Max_used_connections'
2.查询出来后分析原因,用explan select*from xxx表(可通过Navicat看type这个位置,是否有索引,all代表查询表所有内容,没加索引)
3.解决方法:
1.加索引
2.改一下慢查询SQL的时间阈值(让这句SQL时间在1S内,一般企业会有这个需求,不设置通常是在2~10秒)
--SQL时间阈值修改需要使用慢查询日志:用SQL语句配置的是临时性时间阈值设置
在my.ini中修改的是永久修改慢SQL的时间阈值
二、mysql的存储引擎:myisam,innodb,memory
1.存储引擎的对比
myisam查询快,支持全文索引,非常优秀,表锁,但是不支持事务,不支持外键(数据库企业开发使用常用的就是需要事务的业务)
innodb对比查询更慢,不支持全文索引,行锁,支持事务,可外键(通常不设置)
2.存储引擎的使用场景
innodb:此表常用于事务,保存的数据都是比较重要。例如:订单数据,用户数据,账号信息
myisam:此表对事务要求不高,通常用于查询和添加,例如:发帖表,回复表
memory:此表用于存那些频繁变化的数据,查询和修改比较多的数据
特殊:创建表可以设置指定的存储引擎,也可改数据库默认引擎配置(my.ini中去改)
create table 表名(字段列表) engine 存储引擎名称;
CREATE TABLE t_temp(id BIGINT PRIMARY key,name VARCHAR(20)) ENGINE myisam;
三、MYSQL索引
在Navicat中给表字段添加索引就需要设置:索引类型+索引方式
1.常见的索引和分类
普通索引Normal:可以给任何字段设置,因为这个索引可以重复设置
唯一索引Unique:不允许重复,例如:用户名,密码等(索引值可以为null)
主键索引:创建表的时候设置了主键,就设置了主键索引(该索引唯一且为null)
全文索引FULLTEXT:不支持事务的myisam可用,我们用事务的innoDB,到时候可以用es(es的底层就是lucene),lucene(倒排索引文档)替换这个全文索引(功能雷同)
2.索引方式
b+树,hash
InoDB底层是b+树
myisam底层是b+树
Memory底层是Hash,BTREE
3.索引操作技巧
A.组合索引,将多个列字段(name,age)设置索引,采用向左匹配原则。
explain select * from dept where name='aaa' ,只访问左边的name,就可以使用这个组合索引
B.like匹配索引
explain select * from dept where dname like '%aaa' 不能使用索引
explain select * from dept where dname like 'aaa%' 可以使用索引
C.or匹配索引
需要or左右两边的字段都设置了索引,才能让包含or的SQL语句使用索引
D.字符串匹配索引
如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。
4.添加索引的技巧
1.出现在where后面的字段
2.经常查询的字段
3.字段要有唯一性,(就算该字段是经常查询查询的也不要设置索引)
4.经常修改的字段不要加索引
5.特殊:强制使用索引
select xx,oo from table force index(索引名) where xxx = xxxx;(强制使用主键)
6.索引失效的原因
1.不满足最左匹配原则
2.like使用不正确
3.字符串字段没有加引号
4.or左右两边有没有设置过索引的字段
5.创建表初期,表中内容不多,全表扫描的速度超过了索引速度会导致失效
6.字段的值为null-----where name is null 不会走索引
7.带计算的SQL语句不走索引,因为不清楚计算最后的值是多少
7.sql优化技巧
1.表数据量大可以分库分表
2.给时长查询的字段设置索引
3.根据表中字段需要可以选择合适的索引方法,(事务要求性不大,用myisam查询效率就更快)
4.注意不要让索引失效,like,最左查询原则,or
5.小表带动大表
一个SQL需要JOIN多张表来查询结果,查询较慢,你可以怎么优化
小表带动大表