主要内容;
- 1.索引基本概述
- 2.索引的种类
- 3.索引名词
- 4.最左前缀
- 5.正确使用索引
- 6.索引的注意事项
- 7.执行计划
1.索引基本概述
1.1 索引简介
- 索引（Index）是帮助MySQL高效获取数据的数据结构。(类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置吗，然后直接获取)
- 在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引
索引的作用:约束和加速查找
1.2 索引的优点
- 提高数据检索效率，降低数据库的IO成本。
- 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
- 大大加快数据的查询速度。
1.3 索引的缺点
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
- 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
- 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
1.4 索引的原理
- 索引原理
索引原理索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。 数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景
- 磁盘IO与预读
磁盘IO,预读
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考： 考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助
2. 索引的种类
- 普通索引 - 唯一索引 - 主键索引 - 联合索引（多列） - 联合主键索引 - 联合唯一索引 - 联合普通索引
无索引： 从前往后一条一条查询
有索引：创建索引的本质，就是创建额外的文件（某种格式存储，查询的时候，先去格外的文件找，定好位置，然后再去原始表中直接查询。但是创建索引越多，会对硬盘也是有损耗。
建立索引的目的：
a.额外的文件保存特殊的数据结构
b.查询快，但是插入更新删除依然慢
c.创建索引之后，必须命中索引才能有效
无索引和有索引的区别以及建立索引的目的
2.1 普通索引
create table user( id int primary key auto_increment, name varchar(20) not null, email char(64) not null, index ix_name(name) ); # 表已存在 create index ix_name on user(name); 删除 drop index ix_name on user;
#普通索引 create index 索引的名字 on 表名(列名)
#删除索引 drop index 索引的名字 on 表名
#查看索引 show index from 表名
2.2唯一索引
作用：约束(包含null)+加速查询
create table user( id int primary key auto_increment, name varchar(20) not null, email char(64) not null, unique index ix_name(name) ); # 表已存在 create unique index un_ix_name on user(name); 删除 drop index ix_name on user;
#唯一索引 create unique index 索引名 on 表名(列名)
#删除唯一索引 drop index 索引名 on 表名;
2.3主键索引
作用：约束（不包含null） + 加速查询
#主键索引 create table userinfo( id int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, unique index ix_name(name) ) or create table userinfo( id int not null auto_increment, name varchar(32) not null, email varchar(64) not null, primary key(nid), unique index ix_name(name) ) 创建表+主键索引
# 主键索引 alter table 表名 add primary key(列名);
#删除主键索引 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
2.4 组合索引
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where name = 'alex' and email = 'alex@qq.com'。
#联合普通索引 create index 索引名 on 表名(列名1,列名2);
3.索引名词
#覆盖索引：在索引文件中直接获取数据 例如： select name from userinfo where name = 'alex50000'; #索引合并：把多个单列索引合并成使用 例如： select * from userinfo where name = 'alex13131' and id = 13131;
4.最左前缀
最左前缀匹配： create index ix_name_email on user(name,email); select * from userinfo where name = 'alex'; select * from userinfo where name = 'alex' and email='alex@oldBody'; select * from userinfo where email='alex@oldBody'; 如果使用组合索引如上，name和email组合索引之后，查询 （1）name和email ---使用索引 （2）name ---使用索引 （3）email ---不适用索引 对于同时搜索n个条件时，组合索引的性能好于多个单列索引 ******组合索引的性能>索引合并的性能*********
5.正确使用索引
5.1 索引未命中
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题
- 范围问题,或者说条件不明确, 条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like
- 尽量使用区分度高 的列设置索引
- 索引列不能在条件中参与计算
- and / or
View Code
#1、and与or的逻辑 条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立 条件1 or 条件2:只要有一个条件成立则最终结果就成立 #2、and的工作原理 条件： a = 10 and b = 'xxx' and c > 3 and d =4 索引： 制作联合索引(d,a,b,c) 工作原理: 对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序 #3、or的工作原理 条件： a = 10 or b = 'xxx' or c > 3 or d =4 索引： 制作联合索引(d,a,b,c) 工作原理: 对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
- 最左前缀匹配原则,非常重要的原则，对于组合索引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的顺序可以任意调整。
- 其他情况:
- 使用函数 select * from tb1 where reverse(email) = 'egon'; - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where email = 999; #排序条件为索引，则select字段必须也是索引字段，否则无法命中 - order by select name from s1 order by email desc; 当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢 select email from s1 order by email desc; 特别的：如果对主键排序，则还是速度很快： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 命中索引 name -- 命中索引 email -- 未命中索引 - count(1)或count(列)代替count(*)在mysql中没有差别了 - create index xxxx on tb(title(19)) #text类型，必须制定长度
6.索引的注意事项
(1)避免使用select * (2)count(1)或count(列) 代替count(*) (3)创建表时尽量使用char代替varchar (4)表的字段顺序固定长度的字段优先 (5)组合索引代替多个单列索引（经常使用多个条件查询时） (6)尽量使用短索引 （create index ix_title on tb(title(16));特殊的数据类型 text类型） (7)使用连接（join）来代替子查询 (8)连表时注意条件类型需一致 (9)索引散列（重复少）不适用于建索引，例如：性别不合适
7.执行计划
select_type： 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 table： 正在访问的表名 type： 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL : 全表扫描，对于数据表从头到尾找一遍 select * from userinfo; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from userinfo where email = 'alex112@oldboy' select * from userinfo where email = 'alex112@oldboy' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX ：全索引扫描，对索引从头到尾找一遍 select nid from userinfo; RANGE： 对索引列进行范围查找 select * from userinfo where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE： 合并索引，使用多个单列索引搜索 select * from userinfo where name = 'alex' or nid in (11,22,33); REF： 根据索引查找一个或多个值 select * from userinfo where name = 'alex112'; EQ_REF： 连接时使用primary key 或 unique类型 select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id; CONST：常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select id from userinfo where id = 2 ; SYSTEM：系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select id from userinfo where id = 1) as A; possible_keys：可能使用的索引 key：真实使用的 key_len： MySQL中使用索引字节长度 rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra： 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的
8. 慢日志管理
慢日志 - 执行时间 > 10 - 未命中索引 - 日志文件路径 配置： - 内存 show variables like '%query%'; show variables like '%queries%'; set global 变量名 = 值 - 配置文件 mysqld --defaults-file='E:wupeiqimysql-5.7.16-winx64mysql-5.7.16-winx64my-default.ini' my.conf内容： slow_query_log = ON slow_query_log_file = D:/.... 注意：修改配置文件之后，需要重启服务
MySQL日志管理 ======================================================== 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息 二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作 查询日志: 记录查询的信息 慢查询日志: 记录执行时间超过指定时间的操作 中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放 通用日志： 审计哪个账号、在哪个时段、做了哪些事件 事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等 ======================================================== 一、bin-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir[filename]] # service mysqld restart 2. 暂停 //仅当前会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 3. 查看 查看全部： # mysqlbinlog mysql.000002 按时间： # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数： # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 4. 截断bin-log（产生新的bin-log文件） a. 重启mysql服务器 b. # mysql -uroot -p123 -e 'flush logs' 5. 删除bin-log文件 # mysql -uroot -p123 -e 'reset master' 二、查询日志 启用通用查询日志 # vim /etc/my.cnf [mysqld] log[=dir[filename]] # service mysqld restart 三、慢查询日志 启用慢查询日志 # vim /etc/my.cnf [mysqld] log-slow-queries[=dir[filename]] long_query_time=n # service mysqld restart MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3 单位为秒 查看慢查询日志 测试:BENCHMARK(count,expr) SELECT BENCHMARK(50000000,2*3);
博客参考:https://www.cnblogs.com/majj/p/9196025.html