前言
如果是小白的话,我觉得看到这篇文章一定会对博主赞赏有加,因为实在是太好了,没错,博主就是要让你喜欢上。
1、索引
索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。
2、索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。
1.1、索引选取类型
1、越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
2、简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。
3、尽量避免NULL:应该指定列为NOT nuLL,在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂
1.2、什么场景不适合创建索引
第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
第二,对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了,所以没有必要。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。
第五,不会出现在where条件中的字段不该建立索引。
1.3、什么样的字段适合创建索引
1、表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询
2、数据量超过300的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;经常连接查询,需要有索引
4、经常出现在Where子句中的字段,加快判断速度,特别是大表的字段,应该建立索引,建立索引,一般用在select ……where f1 and f2 ,我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5、经常用到排序的列上,因为索引已经排序。
6、经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的
2、索引优缺点
2.1、优点
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
2.2、缺点
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度,会减慢写入速度
索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
3、索引分类
1,普通索引:
仅加速查询 最基本的索引,没有任何限制,是我们大多数情况下使用到的索引。
CREATE INDEX index_name on user_info(name) ;
2,唯一索引:
与普通索引类型,不同的是:加速查询 + 列值唯一(可以有null)
CREATE UNIQUE INDEX mail on user_info(name) ;
3,全文索引:
全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表;作用于CHAR、VARCHAR、TEXT数据类型的列。
4,组合索引:
将几个列作为一条索引进行检索,使用最左匹配原则。
1、普通索引
1.1.1、创建表的时候同事创建索引
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 = '索引测试表';
1.1.2、在存在的表上创建索引
create index index_name on healerjean(name)
1.1.3、注意:
对于创建索引时如果是blob 和 text 类型,必须指定length。
create index ix_extra on in1(message(200));
alter table employee add index emp_name (name);
1.2、删除索引
drop index_name on healerjean;
alter TABLE users drop index name_index ;
1.3、查看索引
这个时候,我们会发现其实主键id也是一个索引
show index from healerjean;
2、主键索引 (我们一般都会提供主键的,默认主键就是索引)
3、正确使用索引
文章相当出色,请查看。主要是看explain 中出现的row有有多少行,行数越多,表示执行速度越慢
如果以错误的方式使用,则即使建立索引也会不奏效。
3.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 进行表查询,具体如下:
mysql> explain select * from sales2 where company_id = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
208key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
3、可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。
4、但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:
mysql> explain select * from sales2 where moneys = 1\G;
*************************** 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)
3.2、对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用:
1、 可以发现第一个例子没有使用索引,而第二例子就能够使用索引,
2、 区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。
mysql> explain select * from company2 where name like '%3'\G;
*************************** 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%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
209possible_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.3、如果列名,记得是列的名字,是索引,使用 column_name is null 将使用索引。
mysql> explain select * from company2 where name is null\G;
*************************** 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)
3.4、如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。
4、存在索引,但是不使用
4.1、如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如,如果列key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;
4.2、如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。
4.3、用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:,必须or前后都有索引才能被使用,而且必须是单独索引。
mysql> show index from sales\G;
*************************** 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.4、如果列是字符型,,传入的是数字,则不上‘’不会使用索引
mysql> explain select * from company2 where name = 294\G;
*************************** 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'\G;
*************************** 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)
5、联合索引和单列索引测试
5.1、联合索引
解释 :可以通过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);
5.1.1、查询条件为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
5.1.2、查询条件为b:未用到索引
explain SELECT * from d001_index WHERE b = 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where
5.1.3、查询条件为c:未用到索引 (d同理)
explain SELECT * from d001_index WHERE c = 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where
5.1.4、查询条件为 b 、 c :未用到索引
explain SELECT * from d001_index WHERE b = 1 and c = 2 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where
5.1.5、 查询条件为 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
5.1.6、查询条件为 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
5.1.7、查询条件为 a 、b、c、c:用到了联合索引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
5.1.8、查询条件为 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
5.1.8、精确查找联合索引总结
1、 顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上
2、多个单列索引在多条件查询时只会生效第一个索引!所以多条件联合查询时最好建联合索引!
当创建**(a,b,c)联合索引时,相当于创建了(a)单列索引**,(a,b)联合索引以及**(a,b,c)联合索引**
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到!
3、具体 使用 a b c 的顺序无关,mysql会自动优化,但是我们建议按照索引的顺序进行查询,而且尽量将筛选力度大的放到前面,其实这种也不要一定是准确的,其实真正有影响的是是否用到了索引
5.2、单列索引
5.2.1、查询条件为 name:使用到了索引 name(长度为512 = 4 * 128 + 2)
explain SELECT * from d001_index WHERE name = 'zhangyj' ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLconstidx_nameidx_name515const1100NULL
5.2.2、查询条件为 name 、 age :只使用了第一个 name索引(长度为512 = 4 * 128 + 2)
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLconstidx_name,idx_ageidx_name515const1100NULL
5.2.3、查询条件为 name or age :两个索引都用上了 type = INDEX_MERGE 合并索引
我这里的测试失败了,应该是由于我的数据表数据量比较小的原因
5.3、联合索引范围查询
解释:范围查询使用到第几列,则联合索引该列后面的字段的不能使用索引
注意:不要取极端值测试,因为mysql优化器会通过索引查找的数量造成一定的影响,即使使用了索引,但是索引却没能生效 ,比如下的 3 变成 1 会受到影响,因为我的数据量中a最小就是 1
explain SELECT * from d001_index WHERE a > 1 ; 没有使用索引,因为数据均匀分布在1 以上 (有1,但是和1比较了,所以也算在了里面)
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLidx_a_b_c_dNULLNULLNULL875Using where
explain SELECT * from d001_index WHERE a > 3 ; 使用到了索引
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d5NULL1100Using index condition
5.3.1、a > 3 使用了索引 a (长度为 5 )
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d5NULL1100Using index condition
5.3.2、a = 1 and b > 1 :使用了联合索引 a、b(长度为10)
explain SELECT * from d001_index WHERE a = 1 and b > 1 ;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d10NULL2100Using index condition
5.3.3、a = 5 AND b > 6 AND c = 7 :使用了联合索引 a、b(长度为10)
explain SELECT * from d001_index WHERE a = 5 AND b > 6 AND c = 7
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d10NULL112.5Using index condition
网络1 、可以用到所以的情况
A>5
A=5 AND B>6
A=5 AND B=6 AND C=7
网络2、下面条件将不能用上组合索引查询:
B>5 ——查询条件不包含组合索引首列字段
B=6 AND C=7 ——查询条件不包含组合索引首列字段
网络3、下面条件将能用上部分组合索引查询:
A>5 AND B=2 ——当范围查询使用第一列,查询条件仅仅能使用第一列 A
A=5 AND B>6 AND C=2 ——范围查询使用第二列,查询条件仅仅能使用前二列 A B
5.4、联合查询 范围排序情况
注意:表中的数据量和查询的数据量会造成影响,所以我这里都普遍使用了limit 1 ,博主测试有一些结果没有写,测试失败了,应该是由于数据量的原因,
5.4.1、order by a 使用到了联合索引 a b c d 按理说应该只用到a才对,这里博主有些疑惑
explain SELECT * from d001_index order by a limit 1;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLindexNULLidx_a_b_c_d20NULL1100NULL
5.4.4、order by b 未使用索引
explain SELECT * from d001_index order by b limit 1;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL8100Using filesort
网络1、组合索引排序的各种场景
ORDER BY A ——首列排序
A=5 ORDER BY B——第一列过滤后第二列排序
ORDER BY A DESC, B DESC——注意,此时两列以相同顺序排序
A>5 ORDER BY A——数据检索和排序都在第一列
网络2、不可以用到组合索引
ORDER BY B ——排序在索引的第二列
A>5 ORDER BY B ——范围查询在第一列,排序在第二列
A IN(1,2) ORDER BY B ——理由同上
ORDER BY A ASC, B DESC ——注意,此时两列以不同顺序排序
explain 很重要,请观察另一篇文章 很详细哦
如果满意,请打赏博主任意金额,感兴趣的在微信转账的时候,添加博主微信哦, 请下方留言吧。可与博主自由讨论哦
支付包
微信
微信公众号