mysql索引知识与实践

Michael_lcf

已于 2024-03-27 14:35:21 修改

阅读量267

点赞数 1

分类专栏： mysql 文章标签： mysql mysql索引

于 2020-02-05 16:56:45 首次发布

^_^ ^_^ ^_^

本文链接：https://blog.csdn.net/Michael_lcf/article/details/104185009

版权

mysql 专栏收录该内容

28 篇文章 2 订阅

订阅专栏

mysql索引知识与实践

1、索引的三个特征
2、索引的数据结构
4、索引分类
5、索引语法
6、索引设计原则
7、根据mysql执行线程优化sql语句

优化 SQL 的最高境界是回归需求，只有正确的理解了需求，才能写出高效的SQL，因此改写 SQL 往往是优化 SQL 的重要手段。
如果不能读懂需求，写出复杂繁琐绕了九曲回环十八弯的 SQL 语句，即使通过分析执行计划来调优，往往还是会陷入到束手无策之中。

1、索引的三个特征

1，索引高度很低
2，索引有序
3，索引存储索引列的值

索引是帮助mysql高效获取数据的排好序的数据结构。

2、索引的数据结构

二叉树
红黑树
Hash表
BTree
B+Tree

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

4、索引分类

1）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2）唯一索引：索引列的值必须唯一，但允许有空值
3）复合索引：即一个索引包含多个列

5、索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

5.1、创建索引语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

eg.

create index idx_city_name on city(city_name);

5.2、查看索引语法

show index from table_name;

eg.

show index from city\G;

5.3、删除索引

drop index index_name on table_name;

drop index idx_city_name on city;

5.4、alter修改索引

-- 1). 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table tb_name add primary key(column_list);

-- 2). 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter table tb_name add unique index_name(column_list);

-- 3). 添加普通索引， 索引值可以出现多次。
alter table tb_name add index index_name(column_list);

-- 4). 该语句指定了索引为FULLTEXT， 用于全文索引
alter table tb_name add fulltext index_name(column_list);

5.5、创建复合索引

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

--就相当于
--	对name 创建索引 ;
--	对name , email 创建了索引 ;
--	对name , email, status 创建了索引 ;

6、索引设计原则

对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

7、根据mysql执行线程优化sql语句

show full processlist 显示正在运行的线程，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别有问题的语句等。

root帐号，能看到所有用户的当前连接。普通帐号，则只能看到自己占用的连接。
SHOW PROCESSLIST 只能列出当前100条，Info字段值是截断显示。
SHOW FULL PROCESSLIST 全部列出线程以及Info完整信息。

select * from information_schema.processlist where db='xxxx';

show processlist

show full processlist

kill id

在这里插入图片描述

① Id ：用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看。
② User ：显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句。
③ Host ：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户。
④ db ：显示这个进程目前连接的是哪个数据库。
⑤ Command ：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等。
⑥ Time ：显示这个状态持续的时间，单位是秒。
⑦ State ：显示使用当前连接的sql语句的状态【重要】。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成。
⑧ Info ：显示这个sql语句，是判断问题语句的一个重要依据。