第六章·DBA-MySQL索引管理及执行计算

索引介绍

1.什么是索引：

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。

2.索引类型介绍

1)BTREE:B+树索引
2)HASH：HASH索引
3)FULLTEXT：全文索引
4)RTREE：R树索引

Btree索引

select * from tb1 where id=15; 精确查询：3次IO
select * from tb1 where id>10 and id<30; 范围查询：6次

B+tree索引

select * from tb1 where id=15; 精确查询：3次
select * from tb1 where id>10 and id<30; 范围查询：4次

# B+tree和Btree比
1.优化了范围查询
2.在叶子节点上添加了相邻的指针

*Btree**

3.索引管理

索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages<---索引（属性）<----查数据。

3.1:索引的分类

主键索引
- 联合索引
普通索引
- 前缀索引
- 联合索引
唯一索引
- 前缀索引
- 联合索引

3.2:添加索引

普通索引

# 创建:
alter table stu add index idx_name(name);

# 查看索引
show index from 表名;
show index from stu;
desc查看索引
PRI：主键索引
UNI：唯一键索引
MUL：普通索引

# 删除索引
alter table stu drop index idx_name;
alter table stu drop key idx_name;

# 添加主键索引
mysql[zls]> create table test(id int,name varchar(10) not null);
mysql[zls]> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql[zls]> alter table test add primary key(id);

# 删除主键索引
alter table test drop primary key;

# 添加唯一键索引
alter table test add unique key uni_name(name);

mysql[zls]> alter table stu add unique key uni_name(name);
ERROR 1062 (23000): Duplicate entry 'wzj' for key 'uni_name'
添加唯一键要求：该字段的数据不能有重复的

# 判断是否可以在name字段上创建唯一键
1.先统计该字段总共有多少行
mysql[zls]> select count(name) from test;
+-------------+
| count(name) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
2.再统计，去重后，该字段有多少行
mysql[zls]> select count(distinct(name)) from test;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 3 |
+-----------------------+
如果两个数值一样，则可以创建唯一键索引，如果两个数值不一样，则无法创建唯一键索引（有重复值）

# 删除唯一键索引
alter table test drop index 索引名;
alter table test drop index uni_name;

前缀索引

给某一字段数据内容特别长的列，创建前缀索引

# 普通前缀索引的创建
mysql[world]> alter table city add index idx_district(district(4));

# 唯一索引前缀索引创建
alter table student2 add unique key uni_name(name(3));

1.避免对大列（数据长的列）建索引
2.如果有，就使用前缀索引

联合索引

将多个字段，做成一个索引

联合索引的查询顺序要和创建时的顺序一致，才可以提高效率

使用场景：婚恋网站
gender,salary,age

## 普通联合索引创建
alter table student2 add index idx_all(gender,age,name);

mysql[zls]> show create table student2;
CREATE TABLE `student2` (
    `id` int(11) NOT NULL,
    `name` varchar(10) NOT NULL,
    `age` tinyint(4) DEFAULT NULL,
    `gender` enum('0','1') DEFAULT NULL,
    `status` enum('0','1') DEFAULT '1',
    KEY `idx_all` (`gender`,`age`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql[zls]> desc student2;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | MUL | NULL | |
| status | enum('0','1') | YES | | 1 | |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql[zls]> show index from student2;

## 主键联合索引
alter table student2 add primary key (id,name);

mysql[zls]> show create table student2;
CREATE TABLE `student2` (
    `id` int(11) NOT NULL,
    `name` varchar(10) NOT NULL,
    `age` tinyint(4) DEFAULT NULL,
    `gender` enum('0','1') DEFAULT NULL,
    `status` enum('0','1') DEFAULT '1',
    PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 唯一键联合索引
alter table student2 add unique key uni_all(id,name);

mysql[zls]> show index from student2;

alter table student2 add unique key uni_all(id,name(3));

mysql[zls]> show create table student2;
CREATE TABLE `student2` (
    `id` int(11) NOT NULL,
    `name` varchar(10) NOT NULL,
    `age` tinyint(4) DEFAULT NULL,
    `gender` enum('0','1') DEFAULT NULL,
    `status` enum('0','1') DEFAULT '1',
    UNIQUE KEY `uni_all` (`id`,`name`(3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

### 索引删除
普通索引
唯一索引
alter table 表名 drop index 索引名;

主键索引
alter table 表名 drop primary key;
alter table 表名 drop primary;

联合索引
idx_all(id,name,age)

alter table 表名 drop index idx_all;

索引，无法直接修改，删除索引后，重新创建。

explain详解

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

explain命令应用

查询数据的方式

全表扫描
- 1）在explain语句结果中type为ALL
- 2）什么时候出现全表扫描?
  - 2.1 业务确实要获取所有数据
  - 2.2 不走索引导致的全表扫描
  - 2.2.1 没索引
  - 2.2.2 索引创建有问题
  - 2.2.3 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

索引扫描
1. 常见的索引扫描类型
  1. index
  2. range
  3. ref
  4. eq_ref
  5. const
  6. system
  7. null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：全索引扫描，创建索引的列，全部数据都查询出来了

mysql[zls]> explain select id from student2;

range：范围查询，一般来说，一条SQL语句，只要达到range级别，就OK

mysql[zls]> explain select * from student2 where id>2;

ref：唯一索引的前缀扫描或者非唯一索引扫描（精确查询）

mysql[world]> explain select * from city where countrycode='chn';

eq_ref：连表查询，传统连接，join on

explain select city.countrycode,country.name as 国家,city.name as 城市,city.population
as 城市人口数 from city join country on city.country.countrycode=country.code and
city.population<100;

const、system：主键精确查询

mysql[world]> explain select * from city where id=10;

null：不进行表的扫描，没有这样的数据所以效率最高

explain select * from city where id>100000000000000000000000000000000000000000000000;

优化例句

### 范围查询优化
mysql[world]> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql[world]> explain select * from city where countrycode in ('CHN','USA');

### 联合查询优化，or精确范围
explain select * from city where countrycode='CHN' union all select * from city where
countrycode='USA';

建立索引的原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

那么索引设计原则又是怎样的?

1、选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如: 学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。主键索引和唯一键索引，在查询中使用是效率最高的。

# 判断是否可以创建唯一键索引,去重后结果一致则可以创建唯一索引
select count(需要创建唯一键索引的字段) from 表;
select count(distinct(需要创建唯一键索引的字段)) from 表;

注意：如果重复值较多，可以考虑采用以下的联合索引

2、其次，如果无法创建唯一索引，重复值比较多，创建联合索引

例如: 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作

3、为经常作为查询条件的字段，创建普通索引

1.经常查询
2.列值的重复值少

4.尽量能使用前缀索引，就用前缀索引

1.减少创建索引的排序时间
2.增加查询的效率

5．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

不走索引的原因

在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。

1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）改写SQL语句

1.没有查询条件，或者查询条件没有建立索引

# 全表扫描
select * from table;
select  * from tab where 1=1;
1、换成有索引的列作为查询条件
2、建立索引

# 使用limit来优化
mysql[world]> explain select * from city where population>100 limit 60;

2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;

1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。

3.条件本身做运算

# 走索引
select * from test where id=10;

# 不走索引
错误的例子：select * from test where id-1=9;

4.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

mysql[lw]> create table test1(id int,name varchar(10),phone char(11));
mysql[lw]> alter table test1 add index idx_phone(phone);

mysql[lw]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(11) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+

## 走索引
mysql[lw]> explain select * from test1 where phone='133';

## 不走索引
mysql[lw]> explain select * from test1 where phone=133;

优化方案：
    1.查看字段的创建数据类型
    2.告诉开发，在查询的时候，一定要按照字段的数据类型来查询
    3.如果是字符串，就加引号
    4.如果是整形，就不加引号

5. <> ，not in 不走索引

使用不等于或者not in会影响到结果集
优化方案：

- 使用limit来优化
- 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit 、or或in尽量改成union

6.使用like模糊查询%在前面

mysql[world]> explain select * from city where countrycode like '%HN';
优化方案：
    - 尽量不要使用%在前面的SQL语句，尽量将%放在后面
    - 那就不要使用MySQL，使用搜索引擎式数据库：elasticsearch

7.使用联合索引尽量按顺序查数据

创建顺序 ：ABC
查询顺序：ABC AB AC BC

8.索引本身失效，统计数据不真实

索引有自我维护的能力。对于表内容变化比较频繁的情况下，有可能会出现索引失效。重建索引就可以解决**

第六章·DBA-MySQL索引管理及执行计算

第六章·DBA-MySQL索引管理及执行计算

索引介绍

explain详解

建立索引的原则

不走索引的原因

第五章·DBA-MySQL数据类型

02走进云计算-Docker自动构建镜像

admin_lw

Comments | NOTHING

取消回复

lw_blog