其中查找到的original就是原本的选择性,sch3,sch4,sch5分别是取该列的前3,4,5个字符作为索引的时候的选择性.逐步增加这个数值,当选择性与原来相差不大的时候,就是一个比较合适的前缀索引的长度.(一般情况下是这样,但是也有例外,当数据极其不均匀时,这样的前缀索引会在某个特殊的case上表现很差劲).
找到合适的长度之后,就可以创建一个前缀索引了:alter table user add index sch_pre3(`school(3)`)
注意:前缀索引和覆盖索引是很难一起使用的,我今天早上刚试过,对索引的优化进行到这一步之后无功而返,具体的原因在下面介绍完覆盖索引之后解释.
联合索引
一般我们都是有对多个列进行索引的需求的,因为查询的需求多种多样.这个时候我们可以选择建立多个独立的索引或者建立一个联合索引.大多数时候都是联合索引更加合适一些.
假设我们要执行这个语句:select * from user where school_name = '卡塞尔' and age > 20,我们在school和age上分别建立两个独立的索引,那么我们预期这条查询语句会命中两个索引,但是使用explain命令查看会发现不一定.这是一个玄学的过程.个人没有研究清楚.
从理论上来讲,MySQL在5.0之后的版本里面对支持合并索引,也就是同时使用两个索引,但是MySQL的优化器不一定这样认为,他可能会认为,查询两次B+树的代价高于查询一次索引之后去数据表进行过滤,因此会选择只用一个索引.(我在自己的5张表上做了类似此case的测试,结果都是只使用了一个索引.)
创建联合索引的语法:alter table user add index school_age(`school`,`age`).
使用联合索引的时候,有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配,例如上面的school_age联合索引,当仅使用age作为查询条件的时候是不能使用的,也就是说select * from user where age =20是不能命中上面的联合索引的.
在不考虑任何查询的情况下,我们应该讲选择性高的列放在联合索引的前面,但是实际上我们更多的是通过查询来反推索引,以使某个固定的查询可以尽可能的命中索引以提高查询速度.毕竟我们建立索引的目的也是为了加快查询的速度.
因此联合索引的优化更多的是根据某个或者某些语句来优化的,不具备一个通用的法则。
最左前缀索引的原理
当数据列有序的时候,mysql可以使用索引,那么假设我们建立了school_age索引,示例数据如下：
在这份数据中,school字段是完全有序的,索引school可以使用索引.
而从全表来看,age字段不是有序的,因此无法直接使用索引,那么观察一下数据表,在什么时候age有序呢?在school进行定值匹配的时候,例如当school=b的时候,对于这三条数据而言,age是有序的,因此可以使用age索引.这就是最左前缀的原理.
此外,最左前缀索引只能使用一个范围查询,例如select * from user where school > a, select * from user where school = a and age > 12,都是可以命中索引的,但是select * from user where school > a and age > 12中,仅school可以命中索引,这也可以从上面得出结论.因为当school是范围匹配的时候,mysql无法确认age字段是否严格有序,比如 school的范围匹配命中了b,c的四条数据,那么age就不是有序的.无法使用后续的索引.
聚簇索引
聚簇索引不是一种索引类型,而是一种存储数据的方式.Innodb的聚簇索引是在同一个数据结构中保存了索引和数据.
因为数据真正的数据只能有一种排序方式,所以一个表上只能有一个聚簇索引.Innodb使用主键来进行聚簇索引,没有主键的话就会选择一个唯一的非空索引,如果还还没有,innodb会选择生成一个隐式的主键来进行聚簇索引.为什么innodb这么执着的需要搞一个聚簇索引呢,因为一个数据表中的数据总得有且只有一种排序方式来存储在磁盘上,因此这是必须的.
这也是innodb推荐我们使用自增主键的原因,因为自增主键自增且连续,在插入的时候只需要不断的在数据后面追加即可.设想一下使用UUID来作为主键,那么每一次的插入操作,都需要找到当前主键在已排序的主键中的位置,然后插入,并且要移动该主键后的数据,以使得数据和主键保持相同的顺序,这无疑是代价非常高的.
也是因为这个原因,在其他索引的叶子节点中,存储的”数据”其实不是该数据的真实物理地址,而是该数据的主键,查找到主键之后,再根据主键进行一次索引,拿到数据.
聚簇索引和非聚簇索引的区别可以用一个简单的例子来说明:
当我们拿到一本书的时候,目录就是主键,是一个聚簇索引,因为在目录中连续的内容,在正文中也是连续的,当我们想要查看迎着阳光盛大逃亡章节,只需要在目录中找到它对应的页面,比如459,然后去对应的页码查看正文即可.
而非聚簇索引呢,则类似于书后面的附录专有名词索引一样(二级普通索引),当你查找邦达列夫的时候,附录会告诉你,这个名词出现在了迎着阳光盛大逃亡一节,然后你需要去目录(主键索引)中再次查找到对应的页码.
覆盖索引
当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引.
设想有如下的查询语句:
select school_name,age from user where school_name = '金色莺尾花'
这个语句根据学校名称来查询数据行的学校名称和年龄,从上面的数据查询的步骤我们可以知道,当在索引中找到要求的值的时候,还需要根据主键去进行一次索引,以拿到全部的数据,然后从其中挑选出需要的列,返回.但是现在索引中已经包含了所有的需要返回的列,那么就不用进行回数据表查询的操作了,此外索引的大小一般是远远小于真正的数据大小的,覆盖索引可以极大的减少从磁盘加载数据的数量.
为什么前缀索引和覆盖索引无法一起使用?
因为前缀索引的目的是用前缀来代表真正的值,他们在选择性上几乎没有区别,但是MySQL仍然无法判断真正的数据是什么,比如阿里巴巴和阿里妈妈在前缀为2的时候是一样的,但是为了确保你查询阿里巴巴的时候不会出现阿里妈妈的内容,是需要回到数据表拿到数据再次进行一个精准匹配来进行过滤的.
因此,覆盖索引无法和列前缀索引一起使用,这是我用一个早晨的时间测试得出的结论.
删除掉冗余和重复的索引
有一些索引是从未在查询中使用过,却白白增加数据插入时开销的,对于这种索引我们应该及时的进行删除.
比如在主键上再建立一个普通索引,无疑是毫无作用的.
还比如在有联合索引school_age的情况下,再建立一个school的独立索引,因为索引的最左前缀匹配原则,school_age是完全可以命中对school的单独查询的,因此后者可以删掉.
如何查看索引的一些相关信息?
索引信息
在mysql中可以使用show index from table_name来查看某个表上的索引,它将会有如下的输出：
或者使用show create table table_name来查看建表语句,其中包含创建索引的语句.
索引大小
在5.0以后的版本中,我们可以通过查看information_schema.TABLES表中的数据来获取更加详细的数据.
该表各字段的含义如下表：
我们可以通过一些查询语句来获取详细的信息,比如:
// 查看当前MySQL服务器所有索引的大小(以MB为单位,默认是字节) SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES // 查看某一个库的所有大小 SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'XXX'; // 查看某一个表的索引大小 SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'yyyy' and table_name = "xxxxx"; // 汇总查看一个库中的数据大小及索引大小 SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'xxxxx';
对tables表的数据的所有查看方式都是可以的,其中还包含了一些表格本身的数据信息,但是因为和本文的主题不符合,这里就不举例子了.
注意:上面的表格是有缓存的,当更新数据库索引之后,最好执行`analyze table xxxx`,然后再进行查看.MySQL会在表格数据发生较大的变化时才更新此表(大小变化超过1/16或者插入20亿行).
索引碎片
在索引的创建删除过程中,不可避免的会产品索引碎片,当然还有数据碎片,我们可以通过执行optimize table xxx来重新整理索引及数据,对于不支持此命令的存储引擎来说,可以通过一条无意义的alter语句来触发整理,比如:将表的存储引擎更换为当前的引擎,alter table xxxx engine=innodb.
