mysql set 索引,MYSQL索引操作

1、索引概述

什么是索引呢？索引是一种数据库的优化手段之一，索引就相当于书的目录一样，方便我们能快速定位到某个章节，不用我们一页页的从头翻找。

如果一本书只有5页，是否需要索引呢？不需要，想找干什么内容一下子就能找到；但是如果一本书有500页，就必须需要索引了，不然我们想找某方面的内容也太耗费时间了。

2、索引的分类

3、索引环境

1、准备表MariaDB [bgx]> create table t5(id int,name varchar(50));

Query OK, 0 rows affected (0.00 sec)

MariaDB [bgx]> desc t5;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

2、使用存储过程批量插入数据1、创建存储过程

MariaDB [bgx]> delimiter $$ #修改结束符

MariaDB [bgx]> create procedure autoinsert()

BEGIN

declare i int default 1;

while (i<2000000)do

insert into bgx.t6 values(i,'bgx');

set i = i+1;

end while;

END $$

MariaDB [bgx]> delimiter ; #别忘记再修改回来

2、查看存储过程MariaDB [bgx]> show procedure status\G

MariaDB [bgx]> show create procedure autoinsert\G

*************************** 1. row ***************************

Procedure: autoinsert

sql_mode:

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`()

BEGIN

declare i int default 1;

while (i<20000)do

insert into bgx.t5 values(i,'bgx');

set i = i+1;

end while;

END

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

3、调用存储过程，执行

MariaDB [bgx]> call autoinsert();

4、创建索引的方法//创建普通索引示例

CREATE 'INDEX' index_name ON product(name);

//创建唯一索引索引

CREATE 'UNIQUE INDEX' index_name ON product(name);

//创建全文索引索引

CREATE 'FULLTEXT INDEX' index_name ON product(name);

//创建多列索引示例

CREATE 'INDEX' index_name ON product(name，id);

5、索引测试

1、未建立索引//未建立索引，花费时长

MariaDB [bgx]> select * from t6 where id=190000;

+--------+------+

| id | name |

+--------+------+

| 190000 | bgx |

+--------+------+

1 row in set (0.51 sec)

//explain是查询优化器，通过explain可以看到查询的过程

MariaDB [bgx]> explain select * from t6 where id=199999\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t6

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 2000287 #代表查询了2000287行才找到了id是199999的行。

Extra: Using where

1 row in set (0.00 sec)

2、建立索引MariaDB [bgx]> create index index_t6_id on bgx.t6(id);

#第一个index是指普通索引的意思

#index_t6_id是指索引的名字

#bgx.t6(id)是指对bgx的库的t6表里面的id字段做索引

//建立索引之后再查询，发现用时很少很少。

MariaDB [bgx]> select * from t6 where id=1999997;

+---------+------+

| id | name |

+---------+------+

| 1999997 | bgx |

+---------+------+

1 row in set (0.00 sec)

//用explain来看一下

MariaDB [bgx]> explain select * from t6 where id=1999997\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t6

type: ref

possible_keys: index_t6_id

key: index_t6_id

key_len: 5

ref: const

rows: 1 #只查找了一行就给找到了

Extra:

1 row in set (0.00 sec)

6、索引管理

1、查看索引MariaDB [bgx]> show create table t6 \G; #查看t6表的创建过程

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(11) DEFAULT NULL,

`name` varchar(50) DEFAULT NULL,

KEY `index_t6_id` (`id`) #表示有一个索引名为index_t6_id

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

2、删除索引MariaDB [bgx]> drop index index_t6_id on t6; #删除索引

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [bgx]> show create table t6 \G; #再次查看，发现关键字key没有了。

*************************** 1. row ***************************

Table: t6

Create Table: CREATE TABLE `t6` (

`id` int(11) DEFAULT NULL,

`name` varchar(50) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

7、技巧

当我们在生产环境当中对数据库进行索引优化时，要先开启慢查询日志，看超过3秒以上的语句，对没有做索引的、查询比较慢的表找出来，提交给开发人员。

当然3秒这个值并不是固定的，具体多少要看具体业务具体分析，与SWAP分区类似。