注:这里主要针对的是 InnoDB 存储引擎的 B+Tree 索引数据结构
ALTER TABLE 'table_name' ADDPRIMARY KEY 'index_name'('column');
ALTERTABLE'table_name'ADDUNIQUE'index_name'('column');
ALTERTABLE'table_name'ADDINDEX'index_name'('column');
ALTERTABLE'table_name'ADDFULLTEXT'index_name'('column');
ALTERTABLE'table_name'ADDINDEX'index_name'('column1', 'column2', ...);
DROPTABLEIFEXISTSuser_test;
CREATE TABLE user_test(id int AUTO_INCREMENT PRIMARY KEY, user_namev archar(30) NOTNULL,sex bit(1) NOTNULL DEFAULTb'1',city varchar(50) NOTNULL, ageintNOTNULL) ENGINE=InnoDBDEFAULTCHARSET=utf8;
ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age);
注:与where后查询条件的顺序无关,这里是很多同学容易误解的一个地方
SELECT* FROMuser_test WHERE user_name = 'feinik' AND age = 26 AND city ='广州';
注:满足最左前缀查询条件的顺序与索引列的顺序无关,如:(city, user_name)、(age, city, user_name)
SELECT* FROMuser_test WHEREuser_name LIKE'feinik%';
SELECT * FROM user_test WHERE user_name LIKE 'feinik%';
SELECT * FROM user_test WHERE city = '广州';或
SELECT* FROMuser_test WHEREage= 26;
SELECT* FROMuser_test WHEREcity ='广州'ANDage = '26';
SELECT* FROMuser_test WHEREuser_name like'%feinik';
SELECT* FROMuser_test WHEREuser_name ='feinik'ANDcity LIKE'广州%'ANDage = 26;
SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');
SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;
注:通过以上方式来计算出前缀索引的选择性比值,比值越高说明索引的效率也就越高效。
SELECTCOUNT(DISTINCTLEFT(index_column,1))/COUNT(*),COUNT(DISTINCTLEFT(index_column,2))/COUNT(*),COUNT(DISTINCTLEFT(index_column,3))/COUNT(*)...FROMtable_name;
注:通过以上语句逐步找到最接近于(1)中的前缀索引的选择性比值,那么就可以使用对应的字符截取长度来做前缀索引了
ALTERTABLE table_name ADDINDEX index_name (index_column(length));
SELECTuser_name, city, age FROMuser_test WHEREuser_name ='feinik'ANDage >25;因为要查询的字段(user_name, city, age)都包含在组合索引的索引列中,所以就使用了覆盖索引查询,查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引,覆盖索引可以极大的提高访问性能。
ALTERTABLEuser_test ADDINDEXindex_user(user_name , city , age);可以使用到索引排序的案例
SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;
注:第4点比较特殊一点,如果where查询条件为索引列的第一列,且为常量条件,那么也可以使用到索引
SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;
SELECTuser_name, city, age FROMuser_test ORDERBYuser_name ASC, city DESC;
SELECTuser_name, city, age, sex FROMuser_test ORDERBYuser_name;
SELECTuser_name, city, age FROMuser_test WHEREuser_name LIKE'feinik%'ORDERBYcity;5. 多表连接查询时,只有当ORDER BY后的排序字段都是第一个表中的索引列(需要满足以上索引排序的两个规则)时,方可使用索引排序。
DROPTABLE IFEXISTS user_test_ext;
CREATE TABLE user_test_ext( id int AUTO_INCREMENT PRIMARY KEY, uid int NOTNULL, u_password VARCHAR(64) NOTNULL) ENGINE=InnoDBDEFAULTCHARSET=utf8;
ALTERTABLEuser_test_ext ADDINDEXindex_user_ext(uid);
SELECT user_name, city, age FROMuser_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;
作者:FEINIK
本文为 @ 21CTO 创作并授权 21CTO 发布,未经许可,请勿转载。
内容授权事宜请您联系 webmaster@21cto.com或关注 21CTO 公众号。
该文观点仅代表作者本人,21CTO 平台仅提供信息存储空间服务。