数据库优化

最新推荐文章于 2024-05-17 05:52:10 发布

点滴记录，是前进的脚步

最新推荐文章于 2024-05-17 05:52:10 发布

阅读量153

点赞数

本文链接：https://blog.csdn.net/u010287873/article/details/82862344

版权

https://www.cnblogs.com/luyucheng/p/6265594.html 慢查询开启

https://www.cnblogs.com/gomysql/p/3720123.html explain解释

创建视图可提一下，是编写sql的效率优化，但不是实际的数据库优化

没有where的查询，要显示的字段全部是索引且包含主键索引的话也会走索引

like模糊查询不管是否%开头，如果走索引，索引也像全表查询一样会遍历索引树的所有行。

explain select brand_id,id,decoration_year from product_hotels where brand_id like '%234534' 此语句未走like索引，所以模糊查询走不走索引全看优化器自己选择。

走索引时，那遍历索引树的所有行和遍历表的所有行效率一样吗？很简单，不如直接遍历表，因为索引还需最后指向表中的数据,那为什么之前对like的优化确实有效果？？

like时展示主键或自己(查询条件字段)走索引(like查询的字段加了索引)，展示普通索引不走索引

一：limit:

偏移量大时解决：

select * from product_hotels where yn=1 limit 30000,30 
0.640 sec.
select * from product_hotels a ,(select id from product_hotels where yn=1 limit 30000,30) b where a.id=b.id 
使用内连接后
0.016 sec.

模糊查询：

region_ids LIKE CONCAT('%', 6108061, ',%')改为

region_ids in (select region_ids from product_hotels where region_ids LIKE CONCAT('%', 6108061, ',%'))

二：索引

limit遇上order by：

https://www.cnblogs.com/wuwenshuai/p/7158389.html

给字段加索引：

https://www.cnblogs.com/lyhabc/p/3776739.html

联合索引怎么使用？

索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找，单独引用复合索引里非第一位置的索引列不会使用符合索引的，要单独给该列建立一个索引。

三：子查询嵌套查询改为join

select
    id, brand_name_cn, brand_name_en, brand_desc_cn, brand_desc_en, brand_image,weights,is_hot,
    yn, ts, create_time, create_pin, update_pin
    from  product_brands
    where id in (
      select brand_id from product_hotels where
        ( region_ids like CONCAT('%',6108061,',%'))
    )
    and is_hot = 1
    AND yn = 1
    order by weights desc
改为

SELECT
    *
FROM
    product_brands a
LEFT JOIN product_hotels b ON a.id = b.brand_id
WHERE
    a.is_hot = 1
AND a.yn = 1
AND b.region_ids LIKE CONCAT('%', 6108061, ',%')

(这里还有个改变是：位置，之前的sql没有走索引位置变了下使用了product_hotels的brand_id索引。)

仅是上面的优化还不够，还需给like使用的字段加索引然后拆开like为in (select...like..)

数据库优化：

1.加索引

2.对于limit m，n 偏移量m越大效率越差

https://zhidao.baidu.com/question/500138563528628404.html

https://segmentfault.com/p/1210000008951080/read

https://blog.csdn.net/su20145104009/article/details/78850151 两种解决方法

https://blog.csdn.net/tanliqing2010/article/details/81509539 mysql技术内幕图解

3.使用索引的注意事项

我们需要在什么情况下建立索引呢?一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
此时就需要对city和age建立索引，由于mytable表的 userame也出现在了JOIN子句中，也有对它建立索引的必要。

如下句会使用索引：
　　SELECT * FROM mytable WHERE username like'admin%'
而下句就不会使用：
　　 SELECT * FROM mytable WHEREt Name like'%admin'

使用索引时，有以下一些技巧和注意事项：
　　 ◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引，因此如果 where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
　　 select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
　　 select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作

优化方案：

一：开启慢查询

https://www.cnblogs.com/luyucheng/p/6265594.html

二：对执行慢的查询sql执行explain命令查看sql执行计划(更新sql怎么查看执行计划？)

explain命令

执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

explain 有两个变种：

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1 |
+-------+------+--------------------------------------------------------------------------------+
2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。 explain 中的列

接下来我们将展示 explain 中每个列的信息。

id列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

1）简单子查询

3）union查询

select_type列
select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。

1）simple：简单查询。查询不包含子查询和union

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

用这个例子来了解 primary、subquery 和 derived 类型

6）union result：从 union 临时表检索结果的 select

用这个例子来了解 union 和 union result 类型：

当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

_依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > indexsubquery > range > index > ALL

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　
时间类型　
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）
rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra列
这一列展示的是额外信息。常见的重要值如下：

distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了

mysql索引机制，对于 idx_a(a) 、idx_a_b_c（a,b,c） 2个索引来说，idx_a索引是没有意义的，可以直接删除。对于主键、唯一索引与普通索引建在共同的列上的情况，普通索引也没有保留的价值。删除无效的索引，
也会提高sql的性能,减小系统负载;
如果是分表的，请查看同一逻辑表下其他表是否有索引，一并处理，如果是弹性数据库，提交ddl操作。

DML（data manipulation language）数据操纵语言：
　　　　就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。
DDL（data definition language）数据库定义语言：
　　　　其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上
DCL（Data Control Language）数据库控制语言：
　　　　是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。这个比较少用到。
在公司呢一般情况下我们用到的是DDL、DML这两种。

点滴记录，是前进的脚步

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库优化

https://www.cnblogs.com/luyucheng/p/6265594.html 慢查询开启https://www.cnblogs.com/gomysql/p/3720123.html explain解释创建视图可提一下，是编写sql的效率优化，但不是实际的数据库优化没有where的查询，要显示的字段全部是索引且包含主键索引的话也会走索引like模糊查...
复制链接

扫一扫