MySQL——如何截取合适的左前缀索引长度
1、 理想的索引
①:查询频繁 :②区分度高 ③:长度小 ④: 尽量能覆盖常用查询字段.
注意: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
2、针对列中的值,从左往右截取部分,来建索引
①: 截的越短, 重复度越高,区分度越小, 索引效果越不好
②: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
惯用手法: 截取不同长度,并测试其区分度,
mysql> select count(distinct left(word,6))/count(*) from dict;
+---------------------------------------+
| count(distinct left(word,6))/count(*) |
+---------------------------------------+
| 0.9992 |
+---------------------------------------+
1 row in set (0.30 sec)
捕获.PNG
对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
3、对于左前缀不易区分的列 ,建立索引的技巧
如 url列
列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
① 把列内容倒过来存储,并建立索引
Moc.udiab.www//:ptth
Ti.euxiz.www//://ptth
这样左前缀区分度大,
② 伪hash索引效果
同时存 url_hash列
典型问题
捕获11.PNG
答：问题出在两个方面：①建表时使用的innodb存储引擎2②varbinary(3000),因为innodb，只查询主键id时，是聚簇索引，造成大量的页分裂，所以速度慢。解决：改为myisam存储引擎
笔记（可忽略）
优化上不要脱离实际业务逻辑，独立索引没有实际意义，同时建立多个独立索引，仅有一个索引有效。应该建立复合索引
mysql 命令：
1、清除缓存：reset query cache；
2、在某列上增加索引：alter table goods add index price(shop_price);
3、使profiling生效：set profiling = 1
4、查看语句执行效率：show profiles；
5、查询语句执行效果（例如：查看索引使用情况）：explain + 查询语句
6、删除索引：alter table goods drop index price；
7、添加复合索引：alter table goods add index cat_price(cat_id,shop_price);
疑问：用复合索引一定会快吗？
myisam 都是指向磁盘的，轻量级的
innodb 直接在主键索引上存储行数据
如何建立高性能索引：
首先分析这是什么表，是聚簇索引还是非聚簇索引，能否索引覆盖，是否有常用的几个列共同出现常用查询，则建立联合查询，索引的长度建立多少
