526互联

09-MySQL 索引管理及执行计划

发布时间 2023-12-27 14:45:59作者: EJW

一、mysql索引类型

  • BTREE:B+树索引 （常用）
  • HASH：HASH索引
  • FULLTEXT：全文索引
  • RTREE：R树索引

二、索引分类

  • 主键索引
  • 普通索引*** **
  • 唯一索引

三、索引基本管理


创建和删除：
alter table stu add index idx_name(stu_name); # 创建索引
alter table stu drop index idx_name; # 删除索引

或者

create index inx_name on stu(stu_name);
drop index inx_name on stu;

查询索引设置
desc stu;

四、MySQL中的约束索引

主键索引：
  • 只能有一个主键。
  • 索引:列的内容是唯一值,高中学号.
  • 建的时候至少要有一个主键索引，最好和业务无关

走主键索引的查询效率是最高的，我们尽量每个表有一个主键，并且将来查询的时候计量以主键为条件查询

  1. 建表时创建推荐
CREATE TABLE `test` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` CHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
  1. 建立表后增加
CREATE TABLE `test1` (
 `id` INT(4) NOT NULL,
 `name` CHAR(20) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8; # 简历一个test1表

ALTER TABLE test1 CHANGE id id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT; # 增加自增主键
普通索引
  • 加快查询速度，工作中优化数据库的关键。
  • 在合适的列上建立索引，让数据查询更高效。
create index index_name on test(name);
alter table test add index index_name(name);
  • 用了索引，查一堆内容。
  • 在where条件关键字后面的列建立索引才会加快查询速度.
select id,name from test where state=1 order by id group by name;
前缀索引：根据字段的前N个字符建立索引
create index index_name on test(name(8));
联合索引：多个字段建立一个索引

特点：前缀生效特性。

原则：把最常用来作为条件查询的列放在前面。

alter table stu add index minx(gender,age); # 查询时优先匹配gender
前缀加联合索引
create index index_name on test(name(8),sex(2));
唯一性索引
create unique index index_name on test(name);

五、企业sql优化思路

企业SQL优化思路：

1、把一个大的不使用索引的SQL语句按照功能进行拆分

2、长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。

3、对SQL语句功能的拆分和修改

4、减少“烂”SQL
由运维（DBA）和开发交流（确认），共同确定如何改，最终由DBA执行

5、制定开发流程

不适合走索引的场景：

1、唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列

  • 查看表的唯一值数量：
select count(distinct user) from mysql.user;  # user列不重复的有几行
select count(distinct user,host) from mysql.user;  #user，host不重复的有几行
SELECT COUNT(*) FROM mysql.user;  #一共有多少行

2、小表可以不建立索引，100条记录。

3、对于数据仓库，大量全表扫描的情况，建索引反而会慢

一、数据库索引的设计原则：

  • 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
    那么索引设计原则又是怎样的?

1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

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

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

3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。

select count(DISTINCT population ) from city;
select count(*) from city;

4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

------------------------以上的是重点关注的，以下是能保证则保证的--------------------

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

6．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文
检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

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

以下知识了解

---------------------------------------------二、开发规范---------------------------------------------------------------

不走索引的情况：
重点关注：

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

select * from tab; 全表扫描。
select * from tab where 1=1;

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

1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。

（1）select * from tab;

SQL改写成以下语句：
selec * from tab order by price limit 10 需要在price列上建立索引

（2）
select * from tab where name='zhangsan' name列没有索引

改：
1、换成有索引的列作为查询条件
2、将name列建立索引

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

查询的结果集，超过了总数行数30%，优化器觉得就没有必要走索引了。

假如：tab表 id，name id:1-100w ，id列有索引

select * from tab where id>500000;

如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

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

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

  1. 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
    例子：
    错误的例子：select * from test where id-1=9;
    正确的例子：select * from test where id=10;

5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给数据库,
这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333;
正确的例子： select * from test where tu_mdn='13333333333';

mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| telnum | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql>
  1. <> ，not in 不走索引

EXPLAIN SELECT * FROM teltab WHERE telnum   <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');
------------
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tab where telnum <> '1555555';

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

EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：

EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

  1. like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引

%linux%类的搜索需求，可以使用elasticsearch

%linux培训%

  1. 单独引用复合索引里非第一位置的索引列.
    列子：
    复合索引：
DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
走索引的情况测试：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';    ----->部分走索引
不走索引的：
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex='m';