烟台新闻网

MySQL索引管理及执行计划

索引简介

1.什么是指数?

1)索引就像一本书的目录,它可以让你更快地找到你想要的东西。

2)使获取的数据更具有目的性,从而提高数据库检索数据的性能。

2.索引类型介绍

1)BTREE: B +树索引

2)HASH:HASH指数

3)FULLTEXT:全文索引

4)RTREE:R树索引

图1. B +树索引

图2. B *树索引

3.索引管理索引基于表的列(字段)。

索引后面的列可以加快查询速度。

页数< - - index(属性)< - - 查找数据。

1.指数分类:

主键索引

普通指数***

唯一索引

2.添加索引:

#创建索引

更改表测试添加index_name(名称);

#创建索引

在test(name)上创建index_name;

#查看索引

描述表;

#ViewIndex

从表中显示索引;

#删除索引

更改表测试删除键index_name;

#Add主键索引(略)

#Add unique index

更改表学生添加唯一键uni_xxx(xxx);

#查看表格中的行数

从城市中选择计数(*);

#查看重复数据删除数据行的数量

从城市中选择计数(不同的名称);

3.前缀索引和联合索引

前缀索引

索引基于字段的前N个字符

更改表测试添加索引idx_name(name(10));

避免索引大列

如果有,请使用前缀索引

联合指数

为多个字段创建索引

例如:

哪里一个。女孩和b。高度和c。重量和d。好身体

指数(A,B,C)

功能:前缀有效性

a,ab,ac,abc,abcd可以是索引或部分索引

b bc bcd cd c d ba .不要拿索引

原则:首先将最常用的列作为条件查询

#创建人员表

创建表人(id int,name varchar(20),ageintint,money int,gender enum('m','f'));

#创建联合索引

更改表人们添加索引idx_gam(性别,年龄,金钱);

解释

解释命令用法

MySQL>解释选择名称,来自城市的国家代码,其中id=1;

解释命令应用程序

查询数据的方法

1.全表扫描1)何时在解释语句结果的ALL2类型中发生完全表扫描? 2.1业务确实需要获取所有数据2.2不走索引导全表扫描2.2.1无索引2.2.2索引创建问题2.2.3语句存在问题。

在生产中,使用全表扫描时MySQL的性能极差,因此MySQL尽可能避免全表扫描。

2.索引扫描

2.1通用索引扫描类型:

1)索引

2)范围

3)参考

4)eq_ref

5)const

6)系统

7)null

从上到下,从最差到最好,我们认为我们应该至少达到范围水平。

索引:完整索引扫描,索引与ALL不同,因为索引类型只遍历索引树。

范围:从某个点开始并返回与范围匹配的行的索引范围扫描。明显的索引范围扫描带有查询的between或where子句。

MySQL> alter table city add index idx_city(population);

MySQL>从人口>的城市解释select * ;

Ref:使用非唯一索引扫描或唯一索引的前缀扫描返回与单个值匹配的记录行。

MySQL> alter table city drop key idx_code;

MySQL>解释select * from city where countrycode='chn';

MySQL>解释select * from country where countrycode in('CHN','USA');

MySQL>解释select * from city where countrycode='CHN'union all select * from city where countrycode='USA';

Eq_ref:与ref类似,不同之处在于使用的索引是唯一的索引。对于每个索引键值,表中只有一个记录匹配。简单来说,主键或唯一键用作多表连接中的关联条件A.

加入B

在A. sid=B. sid

Const,system:当MySQL优化查询的一部分并将其转换为常量时,使用这些类型的访问。

如果主键放在where列表中,MySQL可以将查询转换为常量。

MySQL>解释select * from city where id=1000;

NULL:MySQL在优化期间分解语句,甚至不访问表或索引。例如,从索引列中选择最小值可以通过单独索引来完成。

MySQL>解释select * from city,其中id=;

额外

使用临时

使用filesort使用默认文件排序(如果使用索引则避免使用)

使用连接缓冲区

如果发生Use Filesort,请检查order by,group by,distinct,join condition列

上没有索引

MySQL>解释select * from city where countrycode='CHN'order by population;

当使用filesort出现在order by语句中时,尝试使排序值显示在where where

MySQL>从人口>的城市解释select *按人口排序3000万;

MySQL>从人口=按人口排序的城市中选择*;

较小的key_len:越好

前缀索引控制

较小的行:越好

3.指数制定原则(规范)

为了使索引更有效,在创建索引时,我们必须考虑创建索引的字段和要创建的索引类型。

索引设计的原理是什么?

1.选择唯一性指数

唯一索引的值是唯一的,可用于更快地确定记录。

例如,

学生表辅助号码是一个唯一的字段。为此字段建立唯一索引可以快速确定学生的信息。

如果使用名称,则可能存在同名的现象,这会降低查询速度。

主键索引和唯一键索引在查询中使用效率最高。

从world.city中选择count(*);

从world.city中选择count(不同的国家/地区代码);

从world.city中选择count(不同的国家代码,人口);

注意:如果您有更多重复值,请考虑使用联合索引

2.索引通常需要排序,分组和联合操作的字段

例如。

当经常需要ORDER BY,GROUP BY,DISTINCT和UNION等字段时,排序操作可能会浪费时间。

如果对其进行索引,则可以有效地避免排序操作

3.索引通常用作查询条件的字段如果字段经常用作查询条件,则字段的查询速度将影响整个表的查询速度。因此,索引这些字段可以提高整个表的查询速度。 3.1频繁查询3.2列值的重复值很小

注意:如果列经常用作条件,则有很多重复值,可以创建联合索引

4.尝试使用前缀来索引

如果索引字段的值很长,则最好使用值的前缀对其进行索引。例如,TEXT和BLOG类型的字段用于全文搜索

这将浪费时间。如果您只检索字段前面的几个字符,这将加快检索速度。

-------------------------------------------------- ---------------------------我是一个华丽的分界线----------------- -------------------------------------------------- --------

5.限制索引数索引数不尽可能。每个索引都需要占用磁盘空间。索引越多,所需的磁盘空间就越多。修改表时,重构和更新索引很麻烦。索引越多,更新表所需的时间就越多。 6.当删除不再使用或很少使用的索引表中的数据,或者更改数据的使用时,可能不再需要某些原始索引。数据库管理员应定期识别这些索引并将其删除,以减少索引对更新操作的影响。

-------------------------------------------------- --------------------------是的,是的,是我--------------- ------------------------------------------------ - ------

专注于:

1.没有查询条件,或查询条件未编入索引

#全表扫描

从表中选择*;

从标签中选择*,其中1=1;

在业务数据库中,尤其是具有大量数据量的表中,不需要全表扫描。

1)查看用户非常痛苦。

2)对服务器的破坏性。

3)SQL被重写为以下语句:

#情况1

#全表扫描

从表中选择*;

#您需要在价格列上编制索引

按价格限制10从标签订单中选择*;

#情况2

#name列没有索引

从表中选择*,其中name='zhangsan';

1.将索引列替换为查询条件

2.索引名称列

2.查询结果集是原始表中的大部分数据,应该超过25%。

MySQL>从人口>的城市解释select *按人口排序3000;

1)如果业务许可,可以使用限制控制。

2)结合商业判断,有更好的方法吗?如果没有更好的重写解决方案,请尽量不要将这些数据存储在MySQL中并将其置于redis中。

3.索引本身无效,统计数据不正确。

索引具有自我维护的能力。

当表内容频繁更改时,可能会发生索引失败。

重建索引可以解决这个问题。

4。查询条件使用函数对索引列进行操作,包括(+、-、*等)。

#示例

错误示例:从ID-1=9的测试中选择“*”。

正确的例子:从id=10的测试中选择*

5。隐式转换导致索引失败。这应该认真对待。这也是开发中常见的错误。

mysql>;创建表测试(id int,name varchar(20),telnum varchar(10));

mysql>;插入测试值(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);

mysql>;explain select*from test其中telnum=120;

mysql>;更改表测试添加索引idx_tel(telnum);

mysql>;explain select*from test其中telnum=120;

MySQL>解释select * from test where telnum=120;

MySQL>解释select * from test where telnum='120';

6.不在不编入索引

MySQL> select * from tab where telnum'';

MySQL>解释select * from tab where telnum'';

个人>&lt ;五月或可能不会,与结果集相关,尝试添加与业务相结合的限制

或者或尽可能地结合

EXPLAIN SELECT * FROM teltab WHERE telnum IN('110','119');

#重写为

EXPLAIN SELECT * FROM teltab WHERE telnum='110'

UNION ALL

SELECT * FROM teltab WHERE telnum='119'

7.比如'%百分比第1号

#范围索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE'31%';

#Now Indexing

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE'%110';

%对于linux%class搜索要求,您可以使用弹性搜索 - - > ELK

8.单独引用联合索引中的非第一位索引列

CREATE TABLE T1(id INT,NAME VARCHAR(20),年龄INT,性别ENUM('m','f'),钱INT);

ALTER TABLE T1 ADD INDEX t1_idx(金钱,年龄,性别);

DESC t1

显示来自t1的索引

#步行指数情况测试

EXPLAIN SELECT NAME,年龄,性别,金钱来自T1 WHERE money=30 AND age=30 AND sex='m';

#部分行走指数

点击选择姓名,年龄,性别,金钱来自T1,其中金钱=30岁,年龄=30岁;

解析选择姓名,年龄,性别,金钱从T1 WHERE money=30 AND sex='m';

#Now Indexing

解析选择姓名,年龄,性别,金钱来自t1年龄=20

EXPLAIN SELECT NAME,年龄,性别,金钱来自t1年龄=30且性别='m';

http://www.whgcjx.com/bdsGK08J/e6fA.html