mysql的优化策略有哪些

末蓝、 2024-04-18 09:06 117阅读 0赞

1.数据库层面

*  主从备份，读写分离
 *  垂直分库分表或者水平分库分表
 *  必要时可以编写存储过程
 *  搭建数据库集群服务，增加服务器物理内存
 *  给数据库增加缓存系统，把热数据缓存到内存中，如果内存缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。缓存实现有本地缓存和分布式缓存，本地缓存是将数据缓存到本地服务器内存中或者文件中，速度快。分布式可以缓存海量数据，扩展容易，主流的分布式缓存系统有memcached、[Redis][]，memcached性能稳定，数据缓存在内存中，速度很快，QPS可达8w左右。如果想数据持久化那就用redis，性能不低于memcached。
 *  根据读写情况选择使用合适的数据库存储引擎
 *  MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。
 *  另一个是InnoDB，支持事务处理（ACID），使用索引情况下是行级别锁，不使用索引情况也是表级别的锁

2.数据库索引层面

*  给经常查询的字段加索引
 *  注意前道模糊查询不会使用索引（‘李%’走索引，‘%李’-不走索引）
 *  不要在索引列撒花姑娘进行运算或使用函数，在列上进行运算或使用函数会导致索引失效，从而进行全表扫描
 *  小心隐式类型转换

（假如：id为varchar类型）
    select * from table where id=100  ---不走索引，全表扫描
    select * from table where id='100'  ---走索引
    为什么呢？
    select * from table where id=100
    等价于
    select * from table where CAST(id AS signed int)=100
    因为隐式类型转化在索引字段上做了函数操作，因此会全表扫描
    但是如果id是int类型的，那么下面语句是会走索引的
    那么select * from table where id='100'  ---走索引

*  联合索引最左前缀原则

mysql会一直向右匹配知道遇到范围查询就停止（>,<,between,like）

将区分度最高的索引字段放到最前面

例如：表table中A和B字段建立了联合索引
    select * from A='xxx' ---走索引
    select * from B='xxx' ---不走索引，全表扫描
    select * from A='xxx' and B='xxx' ---走索引
    select * from B='xxx' and A='xxx' ---不走索引，全表扫描
    联合索引的最左前缀原则是，先匹配第一个最前面的索引，不过能匹配到则接着匹配下一个索引，如果第一个索引匹配不到则进行全表扫描

*  经常更改或区分度不高的字段上不宜使用索引（例如：is\_delete等等，一般来说如果一个字段的数据重复率超过50%那么就没必要加索引）
 *  建立索引的列不为NULL，只要列中包含有NULL的值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的，因此数据库设计时，尽量不要让字段默认为NULL
 *  明知道只会返回一条数据记录时，可以加limit1，这样可以让mysql停止游标移动，提高查询效率
 *  建立覆盖索引

我们知道一个字段是非聚集索引时，当查询该字段时，会查询key为该字段的值的数据，但是它的value确实主键的值，                 而不是该条数据的物理地址，当拿到主键值之后会再次进行二次查询（也叫做回表）通过聚集索引查询key为主键值的索              引，这样查到的value值才是这条数据的物理存储地址

*  什么是覆盖索引呢？

覆盖索引可以避免二次回表查询，提高查询效率

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行                   了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

例如：有一个表table字段有username，userage,password
    当为username和passwoed建立索引之后查询语句为：
    select username，password from table where username='xxx'
    因为username和password建立了索引，所以索引中会包含这两个字段的值，当只对这两个字段查询时，那么就会直接在索引中取值而不会再去数据表中读取数据了，这就是覆盖索引

附加：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTQ1NzEz_size_16_color_FFFFFF_t_70][]

[Redis]: http://lib.csdn.net/base/redis
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTQ1NzEz_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7583b62dea7340189316d8c2c77afa6a.png