mysql索引及sql优化

技术文档网 2021-04-16

1. 简述myisam和innodb的区别 特性 区别 用途

  innodb支持事务,外键和行级锁  适合执行大量的INSERTUPDATE 不保存表的具体行数
  myisam不支持                适合执行大量的SELECT         保存表的具体行数
  InnoDB的设计目标是处理大容量数据库系统
  我觉得使用InnoDB可以应对更为复杂的情况,特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询,从而提高整体性能

2. 简述left join righ join 和inner join的区别

  left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
  right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
  inner join(等值连接) 只返回两个表中联结字段相等的行

3. mysql 索引相关问题

mysql索引原理以及慢查询优化

  [mysql索引原理以及慢查询优化](**https://tech.meituan.com/mysql-index.html**)
  索引最重要的特性:索引的最左匹配特性
  当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,
比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,
最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了

  索引的几大基本原则
  1. 最左前缀匹配原则,非常重要的原则
  非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整
  2. =和in可以乱序
  比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
  3. 尽量选择区分度高的列作为索引
  区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
  4. 索引列不能参与计算
  保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  5. 5.尽量的扩展索引,不要新建索引
  比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

4. 查询优化神器 explain命令

  关于explain命令相信大家并不陌生,具体用法和字段含义可以参考官网[explain-output](https://dev.mysql.com/doc/refman/5.5/en/explain-output.html),这里需要强调rows是核心指标,
绝大部分rows小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows

5. 慢查询优化基本步骤

  0. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  1. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,
单表每个字段分别查询,看哪个字段的区分度最高
  2. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  3. order by limit 形式的sql语句让排序的表优先查
  4. 了解业务方使用场景
  5. 加索引时参照建索引的几大原则
  6. 观察结果,不符合预期继续从0分析

6. mysql优化

1. 不要查询不需要的列
2. 不要在多表关联返回全部的列
3. 不要select *
4. 不要重复查询,应当写入缓存
5. 尽量使用关联查询来替代子查询。
6. 尽量使用索引优化。如果不使用索引。mysql则使用临时表或者文件排序。如果不关心结果集的顺序,可以使用order by null 禁用文件排序。
7. 优化分页查询,最简单的就是利用覆盖索引扫描。而不是查询所有的列
8. 应尽量避免在 where 子句中使用 !=或<> 操作符,否则将引擎放弃使用索引而进行全表扫描。
9. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 whereorder by 涉及的列上建立索引
10. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select * from user where name is null
11. 尽量不要使用前缀%
select * from user where name like '%a'
12. 应尽量避免在 where 子句中对字段进行表达式操作
13. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
14. 很多时候用 exists 代替 in 是一个好的选择:

7. btree索引

B-TREE索引适合全键值、键值范围、前缀查找。
全值匹配,是匹配所有的列进行匹配、
匹配最左前缀。比如 a=1&b=2 那么会用到a的索引
匹配列前缀。 比如 abc abcd %abc
匹配范围 比如 in(3,5)

限制

如果不是左前缀开始查找,无法使用索引 比如 %aa
不能跳过索引的列。
需要中,含有某个列的范围查找,后面的所有字段都不会用到索引

8. 索引的优点

1、减少服务器扫描表的次数
2、避免排序和临时表
3、将随机io变成顺序io

9. 高性能索引策略

1、使用独立的列,而不是计算的列
where num+1 =10 //bad
where num = 9 //good
2、使用前缀索引
3、多列索引,应该保证左序优先
4、覆盖索引
5、选择合适的索引顺序
不考虑排序和分组的情况。在选择性最高的列上,放索引,
6、使用索引扫描来排序
mysql有两种方式生成有序的结果,一种是排序操作,一种是按索引顺序扫描,如果explain处理的type列的值是index。则说明mysql使用了索引
只有当索引的列顺序和order by子句的顺序一致的时候,并且所有的顺序都一致的时候。mysql才能使用索引进行排序。

10. 不能使用索引的情况

1.查询使用了两种排序方向
select * from user where login_time > '2018-01-01' order by id des ,username asc #
2.order by中含有了一个没有 索引的列
select * from user where name = '11' order by age desc; //age 没有索引
3.where 和 order by 无法形成最左前缀
索引列的第一列是范围条件
在索引列上有多个等于条件,这也是一种范围。不能使用索引

聚集索引和非聚集索引

区别:

   聚集索引:可以帮助把很大的范围,迅速减小范围。但是查找该记录,就要从这个小范围中Scan了。
   非聚集索引:把一个很大的范围,转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置,再去找你所需要的记录

索引与主键的区别:

   主键:主键是唯一的,用于快速定位一条记录。
   聚集索引:聚集索引也是唯一的。(因为聚集索引的划分依据是物理存储)。而聚集索引的主要是为了快速的缩小查找范围,即记录数目未定。
   主键和索引没有关系。他们的用途相近。如果聚集索引加上唯一性约束之后,他们的作用就一样了

mysql索引的分类

  1. 普通索引
  2. 唯一索引
  3. 主键索引
  4. 组合索引
  5. 全文索引

相关文章

  1. webpack

    一、安装webpack 安装webpack首先需要安装node.js、node.js自带了软件包管理工具npm 1、查看自己的node版本 node -v 2、全局安装webpack(这里先

  2. 查看mysql数据库及表编码格式

    1.查看数据库编码格式 mysql&gt; show variables like 'character_set_database'; 2.查看数据表的编码格式 mysql&gt; show cre

  3. mysql锁

    mysql锁 共享锁与排他锁 (mysql共享锁排它锁) mysql锁分表锁和行锁 行锁包括共享锁和排他锁 共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访

  4. mysql索引及sql优化

    1. 简述myisam和innodb的区别 特性 区别 用途 innodb支持事务,外键和行级锁 适合执行大量的INSERT或UPDATE 不保存表的具体行数 myisam不支持

  5. mysqldump备份成sql文件

    mysqldump备份成sql文件 ============== 假想环境: MySQL 安装位置:D:\MySQL 数据库名称为:myDatabase MySQL root 密码:12345

随机推荐

  1. webpack

    一、安装webpack 安装webpack首先需要安装node.js、node.js自带了软件包管理工具npm 1、查看自己的node版本 node -v 2、全局安装webpack(这里先

  2. 查看mysql数据库及表编码格式

    1.查看数据库编码格式 mysql&gt; show variables like 'character_set_database'; 2.查看数据表的编码格式 mysql&gt; show cre

  3. mysql锁

    mysql锁 共享锁与排他锁 (mysql共享锁排它锁) mysql锁分表锁和行锁 行锁包括共享锁和排他锁 共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访

  4. mysql索引及sql优化

    1. 简述myisam和innodb的区别 特性 区别 用途 innodb支持事务,外键和行级锁 适合执行大量的INSERT或UPDATE 不保存表的具体行数 myisam不支持

  5. mysqldump备份成sql文件

    mysqldump备份成sql文件 ============== 假想环境: MySQL 安装位置:D:\MySQL 数据库名称为:myDatabase MySQL root 密码:12345