数据库—密集索引、稀疏索引、优化慢查询SQL、索引越多越好？

密集索引和稀疏索引的区别

密集它是一一对应

稀疏是一个对应多个值

 

 

 

InnoDB

采用主键 >第一个非唯一 >非空唯一 >生成隐藏作为密集索引(唯一)

 

shop_info_large 表是InnoDB

有两个文件 InnoDB索引和数据是存在一起的*.ibd

一个frm 表结构

ibd是数据内容

shop_info_small 表是myisam

MyISAM索引和数据是分开存储的

*MYI存储索引

*MYD存储数据

 

InnoDB如果不是索引主键，那么要在索引找到然后再去主键索引全部信息

 

 

SQL慢查询优化

如何定位并优化慢查询SQL

具体场景具体分析，只提出大致思路

根据慢日志定位查询SQL

使用explain等工具分析SQL

修改SQL或者尽量让SQL走索引

 

show variables like '%quer%';

查看那些日志，查看慢查询是否打

日志文件

打开慢查询

set global slow_query_log=on;

设置慢查询最大时间超过一秒就记录为慢查询

set global long_query_time=1;

 

show status like '%slow_queries%';查看慢SQL条数

 

慢SQL日志文档，在Linux系统中 使用vi或者vim进行查看

query_time 查询时间是6秒 老师讲的

 

Explain关键字段

type

extra

 

type

左边到右边 最优到最差 index 和all 都是全盘扫描 这个两种情况是最差的

 

 

extra

出现这2项 Using filesort 和Using temporary 意味着MySQL根本不能使用索引，效率会受到重要影响，应尽可能对此进行优化

 

 

create table `person_info_large`(

  `id` int(7) not null auto_increment,

  `account` varchar(10) default null,

  `name` varchar(20) default null,

   `area` varchar(20) default null,

   `title` varchar(20) default null,

   `motto` varchar(50) default null,

    PRIMARY KEY(`id`)

    unique key `account`(`account`)

    key `index_area_title`(`area`,`title`)

)

 

 

 

 

 

 

比如 这里索引使用的是name 但是 name 它不是索引键 所以相对慢一下

 但是 它的查询时间 相对比较长 这里就是用索引 进行优化 它会提升百分之30

使用了 account 它是唯一索引

这里可一个添加一个索引 然后给他进行优化

aler table person_info_large add index idx_name(name)

时间优化了百分之30，因为 name 不是索引

 

额外知识 查询 count多少行

explain select  count(id) from person_info_large;

它是按照最优化的索引 进行使用 唯一索引

可以强制 进行索引设置 但是效率肯定没有 最优化的SQL语句

force index(primary) 强制给他优先使用主键索引使用

explain select count(id) from person_info_large force index(primary);

 

 

最左匹配原则

explain select * from persopn_info_large where title ='AGEDFERA' and area="dadwea"

1.最左前缀匹配原则，非常重要的原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，

比如 a=3 and b=4 and c>5 and d=6 如果建立（a、b、c、d）顺序的索引，d是用不到索引的，如果建立(a,b,c,d)的索引则都可以用到，a,b,d的顺序可以任意调整

2.=和in可以乱序，不如 a=1 and b= and c=3 键立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

 

索引是建立的越多越好吗?

数据量小的表不需要建立索引，建立会增加额外的索引开销

数据变更需要维护索引，因此更多的索引以为着更多的维护成本

更多的索引意味着也需要更多的空间