你会使用 mysql索引吗

Java MySQL
1277

索引

  1. 索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。
  2. 对于索引，会保存在额外的文件中。
  3. 索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

索引选取类型

1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

2、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的处理比较复杂。

3、尽量避免NULL：应该指定列为NOT NULL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

什么场景不适合创建索引

  1. 对于那些在查询中很少使用或者参考的列不应该创建索引。既然这些列很少使用到，那么有索引或者无索引，并不能提高查询速度。反而由于增加了索引降低了系统的维护速度和增大了空间需求。
  2. 对于那些数据种类稀疏的列也不应该增加索引。比如性别，一般只有两种类型男(0)或者女(1),即使加上索引在查询过程中也不能过滤很多数据，和全表扫描差别不大，但是却会增加索引维护的成本。
  3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么很少。
  4. 当修改频率远远大于查询频率时，不应该创建索 引。这是因为，修改和查询是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
  5. 不会出现在where条件中的字段不该建立索引。

什么样的字段适合创建索引

  1. 表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
  2. 数据量超过300的表应该有索引；
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
  4. 经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
  5. 经常用到排序的列上，因为索引已经排序。
  6. 经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

索引优缺点

优点

  1. 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
  2. 索引的优点是可以提高检索数据的速度

缺点

  1. 索引的缺点是创建和维护索引需要耗费时间
  2. 索引可以提高查询速度，会减慢写入速度
  3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

索引分类

普通索引

仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。常见的创建索引的方式如下：

CREATE INDEX index_name on user_info(name);

创建表的时候同事创建索引

create table healerjean (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

注意

对于创建索引时如果是blob 和 text 类型，必须指定length。

create index idx_extra on in1(message(200));

alter table employee add index emp_name (name);

删除索引

drop index_name on healerjean;

alter TABLE users  drop index  name_index;

查看索引

show index from healerjean;

唯一索引

与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

CREATE UNIQUE INDEX  mail  on user_info(name);

全文索引

全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

组合索引

将几个列作为一条索引进行检索，使用最左匹配原则。

正确使用索引

规则1

对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用

1、首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:

mysql> create index ind_sales2_companyid_moneys on sales2(company_id,moneys);
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

2、然后按 company_id 进行表查询,具体如下:

3、可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。

4、但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:

mysql> explain select * from sales2 where moneys = 1;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)

规则2

对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用：

  1. 可以发现第一个例子没有使用索引,而第二例子就能够使用索引,
  2. 区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。
mysql> explain select * from company2 where name like '%3';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)

 
mysql> explain select * from company2 where name like '3%';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set (0.00 sec)

规则3

如果列名，记得是列的名字，是索引,使用 column_name is null 将使用索引。

mysql> explain select * from company2 where name is null;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

规则4

如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’

存在索引，但是不使用

规则1

如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如，如果列key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

规则2

如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

规则3

用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:，必须or前后都有索引才能被使用，而且必须是单独索引。

mysql> show index from sales;
*************************** 1. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_year
Seq_in_index: 1
Column_name: year
210Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)

规则4

如果列是字符型，传入的是数字，那么不上‘’不会使用索引

mysql> explain select * from company2 where name = 294;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
 
 
mysql> explain select * from company2 where name = '294';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

联合索引和单列索引

参考连接

联合索引

解释 ：可以通过key_len的长度来判断联合索引使用到了那些

CREATE TABLE `d001_index` (
    `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(128) DEFAULT NULL,
    `age` bigint(20) DEFAULT '0',
    `country` varchar(50) DEFAULT NULL,
    `a` int(11) DEFAULT '0',
    `b` int(11) DEFAULT '0',
    `c` int(11) DEFAULT '0',
    `d` int(11) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `idx_a_b_c_d` (`a`,`b`,`c`,`d`),
    KEY `idx_age` (`age`),
    KEY `idx_name` (`name`)
)

INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (1, 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (2, 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (3, 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (4, 'k', 2, 'b', 3, 5, 6, 8);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'k', 2, 'b', 3, 5, 6, 8);

查询条件为a :用到了索引a (长度为5)

explain  SELECT * from d001_index WHERE a = 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d5const1100NULL

查询条件为b：未用到索引

explain  SELECT * from d001_index WHERE b = 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

查询条件为c：未用到索引 (d同理)

explain  SELECT * from d001_index WHERE c = 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

查询条件为 b 、 c ：未用到索引

explain  SELECT * from d001_index WHERE b = 1 and c = 2 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

查询条件为 a 、 b：用到了联合索引 a 、b (长度为10)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d10const,const1100NULL

查询条件为 a、c ：用到了联合索引a (长度为5)

explain  SELECT * from d001_index WHERE a = 1 and c = 3 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d5const125Using index condition

查询条件为 a 、b、c、d：用到了联合索引a b c d (长度为20)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 and c = 3  and d = 4 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d20const,const,const,const1100NULL

查询条件为 a or b ：未用到索引

explain  SELECT * from d001_index WHERE a = 1 or b = 2;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLidx_a_b_c_dNULLNULLNULL450Using where

单列索引

查询条件为 name：使用到了索引 name（长度为512 = 4 * 128 + 2）

explain  SELECT * from d001_index WHERE name = 'zhangyj';

查询条件为 name 、 age ：只使用了第一个 name索引（长度为512 = 4 * 128 + 2）

explain  SELECT * from d001_index WHERE name = 'zhangyj' AND age=18;

参考原文：

https://blog.csdn.net/u012954706/article/details/81241049

参考索引优化：

https://www.cnblogs.com/Cheney222/articles/5876382.html