MYSQL索引操作
我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
笔者微信：zhanghe15069028807
1、索引概述
什么是索引呢？索引是一种数据库的优化手段之一，索引就相当于书的目录一样，方便我们能快速定位到某个章节，不用我们一页页的从头翻找。
如果一本书只有5页，是否需要索引呢？不需要，想找干什么内容一下子就能找到；但是如果一本书有500页，就必须需要索引了，不然我们想找某方面的内容也太耗费时间了。
2、索引的分类
普通索引INDEX:最基本的索引，没有任何限制。
唯一索引UNIQUE：与普通索引类型，不同的是索引列的值必须唯一，但允许有空值。
全文索引FULLTEXT：只能在MYISAM表里面用，针对较大的数据量，全文索引耗时比较长。
主键索引PRIMARY KEY：是一种特殊的唯一索引，不允许有空值
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分区类似。
