目录
  • 慢查询日志:里面会记录那些比较慢的日志,可以使用pt-query-digest工具进行分析

  • explain语句:可以分析单条语句的查询效率

  • show profile、show status、show processlist等语句:查询语句执行慢的各种情况以及消息情况或其它

使用show profile:set profiling=1;开启,服务器上执行的所有语句会检测消耗的时间,存到临时表中
show profile for query 临时表ID:可以查询每条profile临时表中记录花费的时间

使用show status:show status会返回一些计数器,show global status查看服务器级别的所有计数
使用show processlist:观察是否有大量线程处于不正常的状态或者特征
使用explain:分析单条SQL语句

访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
确认MySQL服务器是否在分析大量不必要的数据行

是否在扫描额外的记录
使用explain来进行分析,如果发现查询需要扫描大量的数据但只返回少数的行,可以通过如下技巧去优化:

使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果
改变数据库和表的结构,修改数据表范式
重写SQL语句,让优化器可以以更优的方式执行查询

避免使用如下SQL语句
1、查询不需要的记录:使用limit解决
2、多表关联返回全部列:指定A.id,A.name,B.age
3、总是取出全部列:SELECT*会让优化器无法完成索引覆盖扫描的优化
4、重复查询相同的数据,可以缓存数据,下次直接读取缓存

切分查询:将一个大的查询分为多个小的相同的查询:一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器开销
分解关联查询:可以将一条关联语句分解成多条SQL来执行

分解关联查询
可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分

查询效率会有大幅提升
较少冗余记录的查询

内部查询快,和客户端交互慢:MySQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多
多个简单查询有必要:使用尽可能少的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的

优化count(*)查询
count()中的会忽略所有的列,直接统计所有列数,因此不要使用count(列名)
MyISAM中,没有任何WHERE条件的count(*)非常快;当有WHERE条件,MyISAM的count统计不一定比其他表引擎快

可以使用explain查询近似值,用近似值替代count(*)
增加汇总表
使用缓存

优化关联查询
确定ON或者USING子句的列上有索引
确保GROUP BY和ORDER BY中只有一个表中的列,这样MySQL才有可能使用索引

优化子查询
尽可能使用关联查询来替代

优化GROUP BY和DISTINCT
这两种查询均可使用索引来优化,是最有效的优化方法
关联查询中,使用标识列进行分组的效率会更高
如果不需要ORDER BY,进行GROUP BY时使用ORDER BY NULL,MySQL不会再进行文件排序
WITH ROLLUP超级聚合,可以挪到应用程序处理

优化LIMIT分页
LIMIT偏移量大的时候,查询效率较低,可以记录上次查询的最大ID,下次查询时直接根据该ID来查询

优化UNION查询
UNION ALL的效率高于UNION

分区的话对用户是透明的

对用户而言,分区表是一个独立的逻辑表,但是底层MySQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件
比如age10-25的放到第一个分区:创建表时使用partition by子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要查询所需数据在的分区即可
较粗的粒度分在不同的表中:分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便

1.表大或热点数据:表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据
2.分区独立操作:分区表的数据更易维护,可以对独立的分区进行独立的操作,可以备份和恢复独立的分区
3.不同机器:分区表的数据可以分布在不同的机器上,从而高效使用资源
4.避免瓶颈:可以使用分区表来避免某些特殊的瓶颈

1、1024分区:一个表最多只能有1024个分区
2、不同版本不同限制:5.1版本中,分区表表达式必须是整数,5.5可以使用列分区
3、包含主键和唯一索引列:分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来
4、修改表结构:需要对现有表的结构进行修改

1、1024分区:一个表最多只能有1024个分区
2、不同版本不同限制:5.1版本中,分区表表达式必须是整数,5.5可以使用列分区
3、包含主键和唯一索引列:分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来
4、无法使用外键约束:分区表中无法使用外键约束
5、修改表结构:需要对现有表的结构进行修改
6.所有分区都必须使用相同的存储引擎
7.分区函数中可以使用的函数和表达式会有一些限制
8.某些存储引擎不支持分区
9.对于MyISAM的分区表,不能使用load index into cache
10.对于MyISAM表,使用分区表时需要打开更多的文件描述符

分库分表的话需要程序员做点事情,主从数据库同步的话借助的是二进制日志

水平分割:表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度

给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作把不同行的数据弄到一起,也可以通过 Redis 维护索引来解决这个问题。

垂直分表:把主键和一些列放在一个表,然后把主键和另外的列放在另一个表。包含主键id:分的不同的表都是用主键id相连,所以都包含主键id。需要join是把不同列的数据弄到一起

应用层逻辑算法:有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差
开发成本:对于应用层来说,逻辑算法无疑增加开发成本

主从复制原理:

1、二进制日志:在主库上把数据更改记录到二进制日志
2、中继日志:从库将主库的日志复制到自己的中继日志
3、读中继日志:从库读取中继日志中的事件,将其重放到从库数据中

解决的问题:

数据分布:随意停止或开始复制,并在不同地理位置分布数据备份
负载均衡:降低单个服务器的压力
高可用和故障切换:帮助应用程序避免单点失败
升级测试:可以使用更高版本的MySQL作为从库

SQL查询的安全方案?

1.使用预处理语句防SQL注入,预处理防sql注入:PHP端尽量使用PDO对数据库进行相关操作,PDO拥有对预处理语句很好的支持的方法,MySQLi也有,但是可扩展性不如PDO,效率略高于PDO,
2.写入数据库的数据要进行特殊字符的转义
3.查询错误信息不要返回给用户,将错误记录到日志

安全设置:

1.定期做数据备份
2.不给查询用户root权限,合理分配权限
3.关闭远程访问数据库权限
4.修改root口令,不用默认口令,使用较复杂的口令
5.删除多余的用户
6.改变root用户的名称
7.限制一般用户浏览其他库
8.限制用户对数据文件的访问权限

无限级分类的实现:

id  title   pid     path
1   服装    0       0-1
2   上衣    1       0-1-2
3   长袖    2       0-1-2-3

path 路径里面存父亲 id 到自己 id 的整个路径
排序的话可以直接根据 path 来 order by