For investors
股价:
5.36 美元 %For investors
股价:
5.36 美元 %认真做教育 专心促就业
什么是MySQL?如何正确使用MySQL索引
1、概述
索引是存储引擎用于疾速查找记载的一种数据结构,经由过程正当的应用数据库索引能够大大进步体系的拜访机能,接下来紧张先容在MySql数据库中索引范例,和若何创立出加倍正当且高效的索引技能。
注:这里紧张针对的是InnoDB存储引擎的B+Tree索引数据结构
2、索引的长处
1、大大加重了服务器必要扫描的数据量,从而进步了数据的检索速率
2、赞助服务器防止排序和暂时表
3、能够将随机I/O变成次序I/O
3、索引的创立
3.1、主键索引
ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');
3.2、独一索引
ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');
3.3、通俗索引
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');
3.4、全文索引
ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');
3.5、组合索引
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);
【深圳PHP培训】4、B+Tree的索引规矩
创立一个测试的用户表
DROP TABLE IF EXISTS user_test;CREATE TABLE user_test( id int AUTO_INCREMENT PRIMARY KEY,
user_name varchar(30) NOT NULL,
sex bit(1) NOT NULL DEFAULT b'1',
city varchar(50) NOT NULL,
age int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创立一个组合索引: ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age);
4.1、索引有用的查问
4.1.1、全值婚配
全值婚配指的是和索引中的一切列停止婚配,如:如下面创立的索引为例,在where前提后可同时查问(user_name,city,age)为前提的数据。
注:与where后查问前提的次序有关,这里是很多同窗容易误解的一个处所
SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';
4.1.2、婚配最左前缀
婚配最左前缀是指优先婚配最左索引列,如:下面创立的索引可用于查问前提为:(user_name )、(user_name, city)、(user_name , city , age)
注:满意最左前缀查问前提的次序与索引列的次序有关,如:(city, user_name)、(age, city, user_name)
4.1.3、婚配列前缀
指婚配列值的开首部门,如:查问用户名以feinik开首的一切用户
SELECT * FROM user_test WHERE user_name LIKE 'feinik%';
4.1.4、婚配规模值
如:查问用户名以feinik开首的一切用户,这里应用了索引的第一列
SELECT * FROM user_test WHERE user_name LIKE 'feinik%';
4.2、索引的限定
1、where查问前提中不包括索引列中的最左索引列,则无奈应用到索引查问,如:
SELECT * FROM user_test WHERE city = '广州';
或
SELECT * FROM user_test WHERE age= 26;
或
SELECT * FROM user_test WHERE city = '广州' AND age = '26';
2、纵然where的查问前提是最左索引列,也无奈应用索引查问用户名以feinik开头的用户
SELECT * FROM user_test WHERE user_name like '%feinik';
3、假如where查问前提中有某个列的规模查问,则其左边的一切列都无奈应用索引优化查问,如:
SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;
【深圳PHP培训】5、高效的索引战略
5.1、索引列不克不及是表达式的一部门,也不克不及作为函数的参数,不然无奈应用索引查问。
SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');
5.2、前缀索引
有时刻必要索引很长的字符列,这会增长索引的存储空间和低落索引的效力,一种战略是能够应用哈希索引,另有一种便是能够应用前缀索引,前缀索引是抉择字符列的前n个字符作为索引,如许能够大大勤俭索引空间,从而进步索引效力。
5.2.1、前缀索引的抉择性
前缀索引要抉择充足长的前缀以包管高的抉择性,同时又不克不及过长,咱们能够经由过程如下办法来计算出适合的前缀索引的抉择长度值:
(1)
SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列
注:经由过程以上办法来计算出前缀索引的抉择性比值,比值越高阐明索引的效力也就越高效。
(2)
SELECTCOUNT(DISTINCT LEFT(index_column,1))/COUNT(*),COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)
...FROM table_name;
注:经由过程以上语句慢慢找到最接近于(1)中的前缀索引的抉择性比值,那末就能够够应用对应的字符截取长度来做前缀索引了
5.2.2、前缀索引的创立
ALTER TABLE table_name ADD INDEX index_name (index_column(length));
5.2.3、应用前缀索引的留意点
前缀索引是一种能使索引更小,更快的有用办法,然则MySql无奈应用前缀索引做ORDER BY 和 GROUP BY和应用前缀索引做笼罩扫描。
5.3、抉择适合的索引列次序
在组合索引的创立中索引列的次序异常紧张,准确的索引次序依赖于应用该索引的查问办法,对付组合索引的索引次序能够经由过程履历轨则来赞助咱们实现:将抉择性最高的列放到索引最前线,该轨则与前缀索引的抉择性办法同等,但其实不是说一切的组合索引的次序都应用该轨则就能够肯定,还必要依据详细的查问场景来肯定详细的索引次序。
5.4 凑集索引与非凑集索引
1、凑集索引
凑集索引决议数据在物理磁盘上的物理排序,一个表只能有一个凑集索引,假如界说了主键,那末InnoDB会经由过程主键来凑集数据,假如没有界说主键,InnoDB会抉择一个独一的非空索引取代,假如没有独一的非空索引,InnoDB会隐式界说一个主键来作为凑集索引。
凑集索引能够很大水平的进步拜访速率,由于凑集索引将索引和行数据保存在了同一个B-Tree中,以是找到了索引也就响应的找到了对应的行数据,但在应用凑集索引的时刻需留意防止随机的凑集索引(一般指主键值不连续,且散布规模不均匀),如应用UUID来作为凑集索引机能会很差,由于UUID值的不连续会招致增长很多的索引碎片和随机I/O,终极招致查问的机能急剧下降。
2、非凑集索引
与凑集索引分歧的长短凑集索引其实不决议数据在磁盘上的物理排序,且在B-Tree中包括索引但不包括行数据,行数据只是经由过程保存在B-Tree中的索引对应的指针来指向行数据,如:下面在(user_name,city, age)上树立的索引就长短凑集索引。
5.5、笼罩索引
假如一个索引(如:组合索引)中包括一切要查问的字段的值,那末就称之为笼罩索引,如:
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
由于要查问的字段(user_name, city, age)都包括在组合索引的索引列中,以是就应用了笼罩索引查问,检查能否应用了笼罩索引能够经由过程履行筹划中的Extra中的值为Using index则证实应用了笼罩索引,笼罩索引能够极大的进步拜访机能。
5.6、若何应用索引来排序
在排序操纵中假如能应用到索引来排序,那末能够极大的进步排序的速率,要应用索引来排序必要满意如下两点便可。
1、ORDER BY子句后的列次序要与组合索引的列次序同等,且一切排序列的排序偏向(正序/倒序)需同等
2、所查问的字段值必要包括在索引列中,及满意笼罩索引
经由过程例子来详细阐发
在user_test表上创立一个组合索引
ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);
能够应用到索引排序的案例
1、SELECT user_name, city, age FROM user_test ORDER BY user_name;
2、SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
3、SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
4、SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;
注:第4点比拟特别一点,假如where查问前提为索引列的第一列,且为常量前提,那末也能够应用到索引
无奈应用索引排序的案例
1、sex不在索引列中
SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;
2、排序列的偏向分歧等
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;
3、所要查问的字段列sex没有包括在索引列中
SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;
4、where查问前提后的user_name为规模查问,以是无奈应用到索引的其余列
SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;
5、多表衔接查问时,只要当ORDER BY后的排序字段都是第一个表中的索引列(必要满意以上索引排序的两个规矩)时,方可应用索引排序。如:再创立一个用户的扩大表user_test_ext,并树立uid的索引。
DROP TABLE IF EXISTS user_test_ext;CREATE TABLE user_test_ext( id int AUTO_INCREMENT PRIMARY KEY,
uid int NOT NULL,
u_password VARCHAR(64) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);
走索引排序
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;
不走索引排序
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;
想知道更多关于IT行业的信息吗?想远远不如行动,行动起来,一起加入达内,一起进入IT行业,跟着达内的脚步,一起走进如今的互联网信息时代,带给你不一样的色彩生活——【深圳IT培训】