索引
概念
索引是一个存在于磁盘中,对一列或者多列值进行排序的结构
用于提高查询速度,使用索引可以提高数据库中指定数据的查询速度,且索引是一个单独存在的内容,属于提高查询速度的最佳途径
索引算法
btree
索引通过存储引擎实现,不同的存储引擎可以使用不同的索引类型
在Memory和InnoDB上支持btree和hash索引
MyISAM支持btree
索引的优点
每个表无论何种存储引擎都至少支持创建16个索引以及索引长度最少256字节(没有上限)
1、提高查询速度
2、当唯一性是数据的特征时,可以创建唯一索引
3、实现数据完整性,加快表与表之间的联系(一旦给字段添加索引,字段值都将进入索引中,表与表之间添加索引后,查询速度会更快)
4、减少排序时间()
索引的缺点
1、创建索引和维护索引需要时间(更改索引字段的数据需要改变索引,需要时间,包括创建索引时已经存在数据,也需要时间
2、索引占用磁盘空间
3、对于表中的数据进行增删改时,索引需要动态维护,消耗时间(自动的,适用于不经常修改的表数据)
索引的类型
1、唯一索引和普通索引
普通索引在数据库中仅用于提高查询速度
唯一索引在提高查询速度的同时,不允许定义唯一索引的列写入重复值
2、单列索引和组合索引
单列索引只对一个字段进行添加,而组合索引给多个字段进行添加
3、全文索引(FULLTEXT)
在定义全文索引的列上支持值的全文查找
4、空间索引(SPATIAL)
在定义空间索引的列上不允许写入空值
创建索引的规则
1、索引不是创建的越多越好
2、数据量少的表不适合添加索引
3、避免对经常更新的数据添加索引
4、在不同值较多的字段上可以添加索引
5、当唯一性是数据本身特征时,添加唯一索引
6、在经常排序的列上添加索引
添加索引的语法
1、在创建表时创建索引
CREATE TABLE 表名 字段名 数据类型 [UNIQUE 唯一 | FULLTEXT 全文 | SPATIAL 空间]
INDEX|KEY[索引名] (定义索引的字段[length][ASC|DESC])
/*不指定索引类型默认为普通索引,只能添加值为字符串的字段
[length]表示索引查询时对该字段多长的字符串长度进行匹配,符合的全部输出*/
例:如下索引长度为5,则表示根据12345匹配,会将以下数据全部输出
123456
12345567
12345567468
例:升序降序
创建一个测试表,验证创建表时创建索引使用升序降序排列
mysql> CREATE TABLE test1 /*添加索引为id*/
-> (id INT,
-> KEY (id));
mysql> INSERT INTO test1 VALUES(9),(3),(4),(6),(1),(5);
mysql> SELECT * FROM test1; /*添加索引后,会自动将数据进行排序,默认为升序*/
+------+
| id |
+------+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 9 |
+------+
例:分析查询语句
创建一个测试表,分析查询语句
EXPLAIN SELECT_STATEMENT
mysql> CREATE TABLE book
-> (
-> bookid INT NOT NULL,
-> bookname VARCHAR(255) NOT NULL,
-> authors VARCHAR(255) NOT NULL,
-> info VARCHAR(255) NULL,
-> comment VARCHAR(255) NULL,
-> year_publication YEAR NOT NULL,
-> INDEX (year_publication));
mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1999\G
*************************** 1. row ***************************
id: 1 /*select语句标识符*/
select_type: SIMPLE /*查询类型*/
table: book /*查询的表名*/
type: ref /*连接类型*/
possible_keys: year_publication /*可能用到的索引*/
key: year_publication /*实际用到的索引*/
key_len: 1 /*索引长度*/
ref: const /*字段或者常量和索引一起被使用*/
rows: 1 /*得到正确结果之前扫描的条目数量*/
Extra: Using index condition /*额外信息*/
例:唯一索引
==唯一索引在创建唯一性约束条件时,也会创建==
mysql> CREATE TABLE index1(id INT UNIQUE,name VARCHAR(30),
UNIQUE INDEX index1_id(id));
mysql> INSERT INTO index1 VALUES(1,'a'),(5,'b'),(6,'c'),(10,'d'),(6,'e');
ERROR 1062 (23000): Duplicate entry '6' for key 'id'
/*报错表示id字段的6重复了,因为创建了唯一索引*/
mysql> INSERT INTO index1 VALUES(1,'a'),(5,'b'),(6,'c'),(10,'d');
mysql> EXPLAIN SELECT * FROM index1 WHERE id=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index1
type: const
possible_keys: id,index1_id /*id为唯一性约束条件的索引名,index1_id 为后来创建的唯一性索引*/
key: id /*创建时哪个在前,优先使用那个索引名*/
key_len: 5
ref: const
rows: 1
Extra: NULL
例:组合索引
==最左前缀原则:查询时需要带最左侧的字段进行判断才会使用组合索引否则组合索引不生效==
mysql> CREATE TABLE index2
-> (id INT NOT NULL,
-> name VARCHAR(30) NOT NULL,
-> age INT NOT NULL,
-> info VARCHAR(255),
-> INDEX cyj(id,name,age));
mysql> EXPLAIN SELECT * FROM index2 WHERE name='a' AND age=20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index2
type: ALL /*对于DBA来说type为ALL是最慢的查询速度,表示查询所有表*/
possible_keys: NULL /*不适用索引这几个为NULL*/
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
mysql> EXPLAIN SELECT * FROM index2 WHERE name='a' AND age=20 AND id=1\G
/*组合索引使用时,必须有创建索引时的第一个字段,才会用到该索引*/(就是最左前缀原则)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index2
type: ref
possible_keys: cyj
key: cyj
key_len: 100
ref: const,const,const
rows: 1
Extra: Using index condition
例:全文索引
只有MyISAM存储引擎可以使用,且必须建立在CHAR/VARCHAR和TEXT数据类型上,且不支持局部索引
能找到全部的
mysql> CREATE TABLE index3(id INT NOT NULL,
-> name VARCHAR(30) NOT NULL,
-> age INT NOT NULL,
-> info VARCHAR(255),
-> FULLTEXT INDEX full_idx(info(100))) ENGINE=MyISAM;
/*全文索引,并且有索引长度为100,存储引擎是重点,只有MyISAM支持全文索引*/
例:空间索引
空间索引SPATIAL:是表示地理位置的
对于空间数据的索引,只有MyISAM支持,且定义该索引的列不能为空
==空间索引是最常用的是redis数据库中==
空间索引的实际应用:美团外卖或者各种地图软件,需要用到地图的位置的应用,地图中的某个超市被看作某个点,使用坐标点进行查找
mysql> CREATE TABLE index4
-> (g GEOMETRY NOT NULL, /*GEOMETRY用于存放坐标点,就是空间数据*/
-> SPATIAL INDEX spa_idx(g)) ENGINE=MyISAM;
mysql> INSERT INTO index4 VALUES(POINT(0,0));
mysql> EXPLAIN SELECT * FROM index4 WHERE g=POINT(0,0)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index4
type: system
possible_keys: spa_idx
key: NULL /*并没有刚才创建的空间索引,由于5.5版本原因*/
key_len: NULL
ref: NULL
rows: 1
Extra: NULL
2、在已经存在的表上添加索引
(1)ALTER
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT |SPATIAL]
INDEX|KEY [索引名] (定义索引的字段(length)[ASC|DESC])
mysql> ALTER TABLE book ADD INDEX sy(authors,info);
mysql> SHOW INDEX FROM book\G /*出来几条就是几个索引*/
*************************** 1. row ***************************
Table: book
Non_unique: 1 /*是否是唯一索引,1代表不是*/
Key_name: year_publication
Seq_in_index: 1 /*在索引中的第几个字段*/
Column_name: year_publication
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: book
Non_unique: 1
Key_name: sy
Seq_in_index: 1
Column_name: authors
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: book
Non_unique: 1
Key_name: sy
Seq_in_index: 2
Column_name: info
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
(2)CREATE
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 定义索引的字段[length] [ASC|DESC])
3、查看索引的方式
SHOW CREATE TABLE 表名\G
SHOW INDEX FROM 表名\G
Non_unique:判断是否为唯一索引,0代表是,1代表不是
Seq_in_index: 1判断字段在索引中的位置,依次是1、2、3……(组合索引)
4、删除索引的方式
ALTER TABLE 表名 DROP INDEX|KEY 索引名;
DROP INDEX 索引名 ON 表名;
例:
mysql> ALTER TABLE index1 DROP INDEX id;
==如果将设置了索引的字段删除,将会删除索引==
==如果是组合索引,删除一个,组合索引中也会少一个字段,但索引依然可以生效,直到剩余一个字段,变为单列索引==
索引函数
RAND()随机数函数
介于0-1之间的随机小数
mysql> SELECT RAND(),RAND(),RAND();
+---------------------+--------------------+--------------------+
| RAND() | RAND() | RAND() |
+---------------------+--------------------+--------------------+
| 0.34604023336064094 | 0.5898257657790796 | 0.9110081595471204 |
+---------------------+--------------------+--------------------+
RAND(x) x种子值,获取有序的随机数列,如下所示,括号中给到的值一样,则两个获取的值也一样,如果不指定,每次使用都是不一样的
mysql> SELECT RAND(1),RAND(1),RAND();
+---------------------+---------------------+--------------------+
| RAND(1) | RAND(1) | RAND() |
+---------------------+---------------------+--------------------+
| 0.40540353712197724 | 0.40540353712197724 | 0.6581625423004502 |
+---------------------+---------------------+--------------------+
FLOOR取整函数
FLOOR(x) 取最接近x且不大于x的整数
mysql> SELECT FLOOR(10.52345);
+-----------------+
| FLOOR(10.52345) |
+-----------------+
| 10 |
+-----------------+
mysql> SELECT FLOOR(10.12345);
+-----------------+
| FLOOR(10.12345) |
+-----------------+
| 10 |
+-----------------+
/*综上述可以看出都取整数10,不管小数点后多少*/
mysql> SELECT FLOOR(-10.99999); /*如果x为-10.999,不管小数点多少,都是-11也不大于-10*/
+------------------+
| FLOOR(-10.99999) |
+------------------+
| -11 |
+------------------+
CEIL
CEIL(x) 取最接近x且不小于x的整数
mysql> SELECT CEIL(10.1);
+------------+
| CEIL(10.1) |
+------------+
| 11 |
+------------+
mysql> SELECT FLOOR(RAND()*10); /*值为0-9之间的数值*/
+------------------+
| FLOOR(RAND()*10) |
+------------------+
| 3 |
+------------------+
mysql> CREATE TABLE test_idx(
-> m INT PRIMARY KEY AUTO_INCREMENT,
-> x int,
-> y int,
-> z int);
mysql> delimiter // /*更改结束符*/
创建存储过程
mysql> DROP PROCEDURE IFEXISTS test_pro; /*如果test_por存在,则删除test_pro存储过程,并返回一个警告*/
-> CREATE PROCEDURE test_pro(IN num_limit INT , IN rand_limit INT)
/*创建test_pro存储过程,使用存储过程格式 CALL test_pro(num_limit,rand_limit)
num_limit和rand_limit都是整数类型,相当于两个字段*/
-> BEGIN /*开始写入*/
-> DECLARE i INT DEFAULT 1; /*声明i的变量,为整数类型,默认值为1*/
-> DECLARE a INT DEFAULT 1; /*声明a的变量,为整数类型,默认值为1*/
-> DECLARE b INT DEFAULT 1; /*声明b的变量,为整数类型,默认值为1*/
-> DECLARE c INT DEFAULT 1; /*声明c的变量,为整数类型,默认值为1*/
-> WHILE i <= num_limit Do /*while循环开始i的值小于等于num_limit的数值时,开始以下循环*/
-> SET a = FLOOR(RAND()*rand_limit); /*RAND()0-1之间的随机数*rand_limit这个值*/
-> SET b = FLOOR(RAND()*rand_limit); /*一次循环,知道i的值比num_limit大的时候跳出循环*/
-> SET c = FLOOR(RAND()*rand_limit);
-> INSERT INTO index1.test_idx VALUES(NULL,a,b,c); /*在表test_idx中插入数据,空,a,b,c*/
-> SET i = i + 1;
-> END WHILE ; /*结束一次循环*/
-> END //
mysql> call test_pro(50,10); /*这时表示num_limit为50,rand_limit为10*/
使用test_pro存储过程来键入值,以上就一长串就是存储过程,50简单来说代表一共写入50条数据
10代表数据值在10以内
mysql> explain select * from test_idx WHERE x =21\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_idx
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9991 /*扫描了9000多条*/
Extra: Using where
mysql> CREATE INDEX test_idx1 ON test_idx (x); /*y字段添加一条索引*/
mysql> explain select * from test_idx WHERE x =21\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_idx
type: ref
possible_keys: test_idx2
key: test_idx2
key_len: 5
ref: const
rows: 216 /*扫描了216条就找到了数据*/
Extra: NULL