Mysql进阶索引篇03——2个新特性，11+7条设计原则教你创建索引

# 创建数据库
CREATE DATABASE dbtest2;
# 使用数据库
USE dbtest2;
# 创建数据表.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)
CREATE TABLE dept( 
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |DESC]

CREATE TABLE book(
	book_id INT ,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	INDEX(year_publication)
);

 SHOW CREATE TABLE book\G

SHOW INDEX FROM book;

 EXPLAIN SELECT * FROM book WHERE book_name = "mysql";

 EXPLAIN SELECT * FROM book WHERE year_publication = 1998;

CREATE TABLE book1(
	book_id INT ,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	INDEX(year_publication),
	UNIQUE INDEX uq_comments(comment)
);

CREATE TABLE student (
	id INT(10) UNSIGNED  AUTO_INCREMENT ,
	student_no VARCHAR(200),
	student_name VARCHAR(200),
	PRIMARY KEY(id)
);

ALTER TABLE student DROP PRIMARY KEY;

DROP TABLE student;

CREATE TABLE student (
	id INT(10),
	student_no VARCHAR(200),
	student_name VARCHAR(200),
	PRIMARY KEY(id)
);

ALTER TABLE student DROP PRIMARY KEY;

CREATE TABLE test3(
	id INT(11) NOT NULL,
	name CHAR(30) NOT NULL,
	age INT(11) NOT NULL,
	info VARCHAR(255),
	INDEX multi_idx(id,name,age)
);

CREATE TABLE test4(
	id INT NOT NULL,
	name CHAR(30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR(255),
	FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

CREATE TABLE test4(
	id INT NOT NULL,
	name CHAR(30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR(255),
	FULLTEXT INDEX futxt_idx_info(info(50))
) ENGINE=MyISAM;

CREATE TABLE articles (
	 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	 title VARCHAR (200),
	 body TEXT,
	 FULLTEXT index (title, body)
) ENGINE = INNODB ;

 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('hello');

CREATE TABLE test5(
	geo GEOMETRY NOT NULL,
	SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY][index_name] (col_name[length],...) [ASC | DESC]

CREATE TABLE book2(
	book_id INT ,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR
);
ALTER TABLE book2 ADD INDEX idx_cmt(comment);

CREATE UNIQUE INDEX uk_bkname_idx ON book2(book_name);

mysql> SHOW INDEX FROM book2;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| book2 |          0 | uk_bkname_idx |            1 | book_name   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| book2 |          1 | idx_cmt       |            1 | comment     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE book2 DROP INDEX idx_cmt;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book2;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| book2 |          0 | uk_bkname_idx |            1 | book_name   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

DROP INDEX index_name ON table_name;

mysql> SHOW INDEX FROM test3;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 |          1 | multi_idx |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test3 |          1 | multi_idx |            2 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test3 |          1 | multi_idx |            3 | age         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
ALTER TABLE DROP COLUMN name;

mysql> SHOW INDEX FROM test3;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 |          1 | multi_idx |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test3 |          1 | multi_idx |            2 | age         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

mysql> SHOW CREATE TABLE ts1\G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

mysql> SHOW CREATE TABLE ts1\G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

DELIMITER //
	CREATE PROCEDURE ts_insert()
	BEGIN
		DECLARE i INT DEFAULT 1;
		WHILE i <800
		DO
		insert into ts1 select rand()*80000,rand()*80000;
		SET i = i + 1;
		END WHILE;
		commit;
	END //
DELIMITE ;

#调用
CALL ts_insert();

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b LIMIT 5;

CREATE TABLE book3(
	book_id INT ,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	INDEX idx_cmt(comment) INVISIBLE
);


mysql> SHOW INDEX FROM book3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| book3 |          1 | idx_cmt  |            1 | comment     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql>  EXPLAIN SELECT * FROM book3 WHERE COMMENT="XXX";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.01 sec)

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

mysql> Alter table book3 alter index idx_cmt invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  EXPLAIN SELECT * FROM book3 WHERE COMMENT="XXX";
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | book3 | NULL       | ref  | idx_cmt       | idx_cmt | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

CREATE DATABASE atguigudb1;

USE atguigudb1;

#1.创建学生表和课程表
CREATE TABLE `student_info` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`student_id` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`course_id` INT NOT NULL ,
	`class_id` INT(11) DEFAULT NULL,
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`course_id` INT NOT NULL ,
	`course_name` VARCHAR(40) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	   SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))  ;
RETURN i; 
END //
DELIMITER ;

This function has none of DETERMINISTIC......

mysql> SELECT @@log_bin_trust_function_creators;
+-----------------------------------+
| @@log_bin_trust_function_creators |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.62 sec)

set global log_bin_trust_function_creators=1;   # 不加global只是当前窗口有效。

#windows下：my.ini[mysqld]加上：
log_bin_trust_function_creators=1 

#linux下：/etc/my.cnf下my.cnf[mysqld]加上：
log_bin_trust_function_creators=1

#存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO course (course_id, course_name ) VALUES
	(rand_num(10000,10100),rand_string(6)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

CALL insert_course(100);

CALL insert_stu(1000000);

mysql> select count(*) from course;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from student_info;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.09 sec)

mysql> SHOW INDEX FROM student_info;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student_info |          0 | PRIMARY  |            1 | id          | A         |      960509 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.06 sec)

mysql> SELECT course_id,class_id,NAME,create_time,student_id
    -> FROM
    -> student_info
    -> WHERE student_id = 123110;
+-----------+----------+--------+---------------------+------------+
| course_id | class_id | NAME   | create_time         | student_id |
+-----------+----------+--------+---------------------+------------+
|     10058 |    10014 | SyNuJn | 2022-05-25 09:30:46 |     123110 |
|     10053 |    10007 | YYVLTl | 2022-05-25 09:31:15 |     123110 |
|     10053 |    10008 | XVIHkg | 2022-05-25 09:32:22 |     123110 |
+-----------+----------+--------+---------------------+------------+
3 rows in set (1.07 sec)

mysql> AlTER TABLE student_info
    -> ADD INDEX idx_sid(student_id);
Query OK, 0 rows affected (5.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT course_id,class_id,NAME,create_time,student_id
    -> FROM
    -> student_info
    -> WHERE student_id = 123110;
+-----------+----------+--------+---------------------+------------+
| course_id | class_id | NAME   | create_time         | student_id |
+-----------+----------+--------+---------------------+------------+
|     10058 |    10014 | SyNuJn | 2022-05-25 09:30:46 |     123110 |
|     10053 |    10007 | YYVLTl | 2022-05-25 09:31:15 |     123110 |
|     10053 |    10008 | XVIHkg | 2022-05-25 09:32:22 |     123110 |
+-----------+----------+--------+---------------------+------------+
3 rows in set (0.00 sec)

```mysql> SELECT student_id,COUNT(*) AS num

-> FROM student_info

-> GROUP BY student_id LIMIT 100;

删除索引，再来。慢的像蜗牛。

```javascript

-> FROM student_info

-> GROUP BY student_id LIMIT 100;

🏀 如果同时使用`GROUP BY`和`ORDER BY`，先看看不加索引的情况。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

出现了一个异常信息，这是因为我们使用的`sql_mode`是`only_full_group_by`.修改下再来，时间代价是6.61秒。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

再看看两个字段分别建立单列索引的情况,5.26s，快了一点点。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

分析下它的查询过程,原来我们只用了一个索引，由于我们是先`GROUP BY student_id`,后`ORDER BY create_time`，我们实际上只使用了索引`idx_sid` 。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

建立联合索引的情况,芜湖起飞。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

再来,交换字段顺序建立联合索引`idx_cre_time_sid`，下面查询真正使用的索引`key`是`idx_sid` ，当然，由于这里存在缓存，所以查询速度很快，实际上它应该比使用`idx_sid_cre_time`慢。读者自己测试可以关闭缓存，作者这里偷个懒了。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

### **3.2.4 UPDATE、DELETE 的 WHERE 条件列**

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

```javascript

-> WHERE NAME = '462eed7ac6e791292a79';

-> ADD INDEX idx_name(NAME);

-> WHERE NAME = '462eed7ac6e791292a79';

### 3.2.5 DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

比如，我们想要查询课程表中不同的 student\_id 都有哪些，如果我们没有对 student\_id 创建索引，执行SQL 语句：

```javascript

运行结果（600637 条记录，运行时间 0.683s ）：

如果我们对 student\_id 创建索引，再执行 SQL 语句：

```javascript

运行结果（600637 条记录，运行时间 0.010s ）：

你能看到 SQL 查询效率有了提升，同时显示出来的 student\_id 还是按照 递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

### 3.2.6 多表 JOIN 连接操作的WHERE条件

多表 JOIN 连接操作时， 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快(n ,n^2 , n^3…)，严重影响查询的效率。

多表 JOIN 连接操作时， 推荐对 `WHERE` 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 `WHERE` 条件过滤是非常可怕的。

>  🔊注意：对用于连接的字段在多张表中的 类型必须一致 。比如 course\_id 在`student_info` 表和 `course` 表中都为 `int(11)` 类型，而不能一个为 int 另一个为 `varchar` 类型。否则在查询时，虽然也会帮我们进行隐式的类型转换，转换时会使用函数，索引就失效了。索引失效情况在后续文章中还会给大家详细介绍，敬请期待。
>  

举个例子，如果我们只对`student_id`创建索引，执行 SQL 语句：

```javascript

运行结果（1 条数据，运行时间 0.189s ）：

这里我们对 `name` 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s。

### 3.2.7 优先使用类型小的列创建索引

这里的类型指的是我们在建表时显示给表中字段指定的类型，比如`TINYINT`,`INT`,`BIGINT`,能够存储整型数据的字节依次递增，类型依次变大。但是其在存储空间中占用的空间也会依次变多，另外也会影响到索引的使用。比如主键会建立聚簇索引，如果主键越短，目录页能够存储的目录项记录就会越多，B+树就会越扁平，查询时I/O次数就会越少，查询的速度就会越快。另外，即使是二级索引，其非叶子节点还存放主键。因此，这条建议对于表的主键特别适用。
 

![请在此添加图片描述](https://ask.qcloudimg.com/http-save/yehe-10127530/c8a852ec2019b59607e1636dcc1b7451.png?qc_blockWidth=793&qc_blockHeight=474)

### 3.2.8使用字符串前缀创建索引

假设一个字段是字符串，字符串很长，我们考虑使用字符串前缀创建索引，而不是整个字符串哟。这样不仅构建索引的B+树索引存储空间更大，存储的内容会更少点，使B+树更加矮胖，同时，在查询时对该字段进行比较效率也会更高点。

这里大家可能有个问题，如果说字符串截取了一段做索引，但是两个不同字符串截取的前缀相同，那么他们比较的结果不就相同了么？不用担心，如果相同，我们还在索引中存储了主键呀，我们再根据主键去回表，就可以知道到底哪个数据是我们需要的了。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

```javascript

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；
 截取得少了，重复内容太多，字段的散列度(选择性)会降低。
 怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

```javascript

通过不同长度去计算，与全表的选择性对比：

公式：

```javascript

例如：

```javascript

计算出来选择度越接近于1越好哟，因为这说明它重复的数值越少。

>  🎯 拓展：Alibaba《Java开发手册》
>  【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
>  说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(\*)的区分度来确定。
>  

引申另一个问题：索引列前缀对排序的影响
 比如我们索引就取了address字段的前12个字符，下面的查询就有点尴尬了。

```javascript

因为索引不包含完整的字符，因此无法对于address中前12个字符相同但是后面字符不同的数据进行索引前缀排序了，只能使用文件排序。

### 3.2.9 区分度高(散列性高)的列适合作为索引

使用如下公式可以计算列的区分度，一般区分度越高越好，当区分度达到33%就算是比较高效的索引了。

```javascript

数据相似性大的就不适合建立索引，如：男女性别

### 3.2.10 使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

### 3.2.11 在多个字段都要创建索引的情况下，联合索引优于单值索引

- 索引建立的多，维护的成本也高。
- 多个字段进行联合查询时，其实只使用到一个索引。比如。

```javascript

-> GROUP BY student_id

-> ORDER BY create_time DESC

-> LIMIT 100;

- 在建立联合索引的相关字段做查询时，联合索引都能生效，使用频率比较高。足够优化sql执行的速度了。

# 4 限制索引的数目

建议单张表的索引数目不要超过6个。

- 索引越多，占用的磁盘空间越大
- 索引会影响数据的增删改的性能
- 表中创建的索引过多，优化器在`possible_keys`中选择合适的`key` 时需要的成本也会更多。比如下面查询中`possible_keys`有两个，实际使用的`key`只有一个，这其实优化器判断的哟。

```javascript

# 5.不适合创建索引的7种情况

## 5.1 在where中使用不到的字段，不要设置索引

## 5.2 数据量小的表最好不要使用索引

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。不仅浪费存储空间。而且在查找时性能可能还会更慢，因为可能还需要回表操作，不如直接查找就行。

## 5.3 有大量重复数据的列上不要建立索引

如，男女性别，当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。

但也不是绝对的哟，比如一个学校有10万人，但是男生只有10人，如果想要筛选出这10个男生，那你就可以对性别这个字段建立索引哟。

## 5.4 避免对经常更新的表创建过多的索引

第一层含义：对于频繁更新的字段不需要创建索引。否则每次更新字段索引都需要对应维护。
 第二层含义：对于经常更新的表，不要创建过多的索引，也是因为维护成本。

## 5.5 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。所以我们通常也推荐使用自增的主键。

## 5.6 删除不再使用或者很少使用的索引

## 5.7 不要定义冗余或重复的索引

(1)冗余索引
 举例：建表语句如下

```javascript

 id INT UNSIGNED NOT NULL AUTO_INCREMENT,

 name VARCHAR(100) NOT NULL,

 birthday DATE NOT NULL,

 phone_number CHAR(11) NOT NULL,

 country varchar(100) NOT NULL,

  PRIMARY KEY (id),

  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),

  KEY idx_name (name(10))

通过idx\_name\_birthday\_phone\_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

(2)重复索引
 另一种情况，我们可能会对某个列 重复建立索引，比方说这样：

```javascript

 col1 INT PRIMARY KEY,

 col2 INT,

 UNIQUE uk_idx_c1 (col1),

 INDEX idx_c1 (col1)