mysql优化难_mysql优化注意点

通常和zerofill一起使用，比如int(3)，存入1，则自动填充为001。并不是存入的长度

char(n)，varchar(n)中的n是最大长度。

外键索引一般不用，可以通过业务逻辑来实现。全文索引一般也不用，直接上全文搜索引擎：sphinx、elasticsearch

表索引的创建原则：最适合素银的列是出现在where子句中的列，或连接子句中的列而不是出现在select关键字后的列

索引列的基数越大，索引的效果越好

对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间

根据情况创建符合索引，符合索引可以提高查询效率

避免创建过多索引，索引会二维占用磁盘空间，降低写操作效率

主键尽量选择较短的数据类型，可以有效减少索引的磁盘占用，调高查询效率

为什么复合/多列索引更高效呢？

比如key('章','节','段')，它不是分别创建若干个索引，而是创建一个索引，作用在这个索引的组合上。缩短查询范围：如果只查某章，则扫描整个某章；若查询某章下的某节，则把某章下的某节扫描一遍。

对于where name like "%wang%"类型的查询，如果前面有%，则name的索引会失效。这种情况最好用全文搜索引擎，如：sphinx、elasticsearch

索引的注意事项：符合索引遵循前缀原则

like查询，%不能放在前面，可以使用全文索引来实现类似功能

column is null 可以使用索引

如果mysql估计使用索引比全表扫描更慢，会放弃使用索引

如果or前的条件中的列有索引，后面的没有，索引都不会被用到

列类型是字符串，查询一定要给值加引号，否则索引失效

(比如字段类型是varchar，where name="100"索引有效，where name=100索引无效)

答案1：

update A,B set A.c1=B.c1,A.c2=B.c2 where A.id=B.id and B.age>50

答案2：

update A inner join B on A.id=B.id set A.c1=B.c1,A.c2=B.c2  where B.age>50

交叉连接会产生笛卡尔积，不用。

左外连接以左表为准，右外连接以右表为准，内连接(inner join)只以on后面的条件为准。

首先要找到查询速度慢的原因。然后优化查询过程中的数据访问，优化长难的查询语句，优化特定类型的查询语句。

查询速度慢的原因：

开启慢查询日志，不建议直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析。

使用show profile，mysql命令行set profiling=1开启，于是所有执行的语句都会检测消耗时间，存到临时表中。

通过show profile for query 临时表id 来获取查询语句在什么动作上话费多少时间。

show status会返回一些计数器，show global status查看服务器级别的所有计数。有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多。

show processlist用来观察是否有大量线程出于不正常的状态或者特征。

使用explain(用desc也一样)分析单条sql语句。

优化查询过程中的数据访问：

不要访问数据太多，只查寻需要的列和行，确认mysql服务器是否在分析大量不必要的数据行

使用limit，指定要返回的字段，少用select *

重复查询相同的数据，可以缓存数据，下次直接读取缓存

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

使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表结构，修改数据表范式。如适当冗余

重写sql语句，让优化器以更优的方式执行查询(索引优化，避免索引失效的情况)

优化长难的查询语句：

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

切分查询：比如一次删除/插入1k条，暂停一会再继续...

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

优化特定类型的查询语句：

优化count()查询，count(*)中的*会忽略所有的列，直接统计所有的列数，因此不要使用count(列名)

myisam中，没有任何where条件的count(*)非常快，当有where条件，myisam的count统计不一定比其他表引擎快

可以使用explain查询近似值，用近似值替代count(*)

增加汇总表(用一个表记录各增删改查后表中的数据条数)

缓存汇总信息

优化关联查询

确定on或using子句的列上有查询

确保group by和order by中只有一个表中的列，这样mysql才有可能使用索引

优化子查询

尽量用关联查询来替代

优化group by和distinct

均可用索引来优化，是最有效的优化方法

关联查询中，使用标识列(主键列、auto increment列)进行分组的效率会更高

如果不需要order by，进行group by时使用order by null，mysql不会再进行文件排序，否则可能会文件排序导致性能消耗

with rollup超级聚合，可以挪到应用程序处理

优化limit分页

limit偏移量大，查询效率就会低

可以记录上次查询的最大id，下次查询时直接根据id来查询

优化union查询

union all的效率高于union，可以在应用层将重复的筛选掉

分库分表：

水平分割：

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

使用场景：

表中的数据本身就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，有些不常用

需要把数据存放到多个介质上

缺点：

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需union操作

在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

垂直分割：

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

使用场景：

如果一个表中某些列常用，而另外一些列不常用

可以使数据行变小，一个数据页能存储更多数据，查询时减少io次数

缺点：

管理冗余列，查询所有数据需要join操作

分库分表(水平和垂直)的缺点：

有些表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性差

对应用层来说，逻辑算法无疑增加开发成本

主从复制原理：

在主库上把数据更改记录到二进制日志

从库将主库的日志复制到自己的中继日志

从库读取中继日志中的事件，将其重放到从库数据库中

主从复制解决的问题：

数据分布：随意停止或开始复制，并在不同地理位置分部数据备份

负载均衡：降低单个服务器的压力

高可用和故障切换：帮助应用程序避免单点失败

升级测试：可以使用更高版本的mysql作为从库

sql语句安全：

预处理，防止sql注入(通常用pdo方式)

写入数据对特殊字符转义

查询错误信息不要返回给客户，将错误记录到日志

定期做数据备份

不给查询用户root权限，合理分配权限

关闭远程访问数据权限

修改root口令，不用默认口令，使用较复杂口令

删除多余用户

改变root用户名称

限制一般用户浏览其他库

限制用户对数据文件的访问权限