数据库_Mysql：慢查询的优化方法，添加索引的3种方式

转自：https://blog.csdn.net/weixin_43851310/article/details/88931063

数据库_Mysql：慢查询的优化方法，添加索引的3种方式

问：数据库中最常见的慢查询优化方式是什么？

加索引

问：为什么加索引能优化慢查询？

因为索引其实是对某一列值预排序的数据结构，比如Mysql中的索引是用B+树实现的，而B+树的高度是2~4层，也就是说查找某一键值的行记录只需要2 ~4次IO。利用索引可以快速查找数据，所以能优化查询。

问：你知道哪些数据结构可以提高查询速度？

哈希表、完全平衡二叉树、B树、B+树等等。

索引（index）

优点：加快查询的速度
缺点：

1. 需要更多的存储空间
2. 数据操作语句(增、删、改)处理时间会更长，因为要对索引进行更新。

创建索引的指导原则

1. 用于频繁搜索的列（不用回表了）
2. 用于排序的字段（索引树本来就已经排好序了）
3. 做条件查询的列
4. 如果列中仅仅包含几个不同的值不要设置索引。比如性别（男和女）
5. 如果数据量不大就不用做索引，因为通过索引查找数据比全表扫描所花的时间更长。

查看索引

show create index from 表名;

创建索引

建表时添加，key 别名 (字段名) 或者 index 别名 (字段名)
建表后添加，create index 别名 on 表名(字段名); 或者 alter table 表名 add index (字段名);

1.建表时添加

1.1 使用key创建索引，key（字段名）

create table info1(
    name varchar(20),
	age int,
	key (age)
);

mysql> show create table info1\G
*************************** 1. row ***************************
       Table: info1
Create Table: CREATE TABLE `info1` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

1.2 使用key创建索引，给索引添加别名

mysql> create table info2(
	name varchar(20),
	age int,
	key `kname` (name)
);

mysql> show create table info2\G
*************************** 1. row ***************************
       Table: info2
Create Table: CREATE TABLE `info2` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `kname` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

1.3 使用index创建索引，index（字段名）

mysql> create table info3(
name varchar(20),
age int,
index `kname` (name)
);

mysql> show create table info3\G
*************************** 1. row ***************************
       Table: info3
Create Table: CREATE TABLE `info3` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `kname` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2 建表后添加

create index 别名 on 表名(字段名);

mysql> create table info4(
	name varchar(20),
	age int
);

mysql> create index `kname` on info4(name);

mysql> show create table info4\G
*************************** 1. row ***************************
       Table: info4
Create Table: CREATE TABLE `info4` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `kname` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3 修改表进行添加

alter table 表名 add index (字段);

mysql> create table info5(
	name varchar(20),	
	age int
);

mysql> alter table info5 add index (name);

mysql> show create table info5\G
*************************** 1. row ***************************
       Table: info5
Create Table: CREATE TABLE `info5` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

其他类型的索引

主键索引

create primary index `别名` on 表名(`字段名`);
例如：create primary index `pindex`on info1(`name`);

唯一键索引

一个表可以由多个唯一键索引
create unique index `别名` on 表名(`字段名`);
例如：create primary index `uindex`on info2(`name`);

全文索引

多列组合索引

mysql> create table info6(
	name varchar(20),
	age int
);

mysql> create index `index_two` on info6(name,age); 

mysql> show create table info6\G
*************************** 1. row ***************************
       Table: info6
Create Table: CREATE TABLE `info6` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `index_two` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)