当MySQL单表记录数过大时,增删改查性能都会急剧下降。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度。一般以整型值
为主的表在千万级以下,字符串
为主的表在五百万以下是没有太大问题的,而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量。
索引优势和劣势:
Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。
即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
FULLTEXT(全文)索引,仅可用于MyISAM和InnoDB
哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式。由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。
HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。
BTREE(B+TREE)索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。由于BTREE非叶子节点不存储数据(data),因此所有的数据都要查询至叶子节点,而叶子节点的高度都是相同的,因此所有数据的查询速度都是一样的。这是MySQL里默认和最常用的索引类型。
RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE,RTREE的优势在于范围查找。
主键自动建立唯一索引;经常作为查询条件在WHERE或者ORDER BY;语句中出现的列要建立索引;作为排序的列要建立索引;查询中与其他表关联的字段,外键关系建立索引高并发条件下倾向组合索引;用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引。
经常增删改的列不要建立索引;有大量重复的列不建立索引;表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。
在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的;在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了;LIKE操作中,’%aaa%'不会使用索引,也就是索引会失效,但是’aaa%'可以使用索引;在索引的列上使用表达式或者函数会使索引失效,例如:
select * from table where ceate_time > unix_timestamp(curdate());
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成当前时间由程序作为参数传入:
select * from table where ceate_time > 1524561911;
字段:
索引:
查询sql:
1.索引是干什么的?
索引用于快速找出在某个列中有一特定值的行。不使用索引,mysql必须从第一条记录开始读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,mysql能快速到达一个位置搜寻到数据文件的中间,没有必要查看所有数据。
大多数mysql的索引(primary key、index、unique、fulltext)在B树中存储,只是空间列类型的索引使用R树,并且memory表还支持hash索引。
2.索引好复杂,我该怎么理解索引,有没有一个更形象的例子?
索引就像是一本书的目录。
3.索引越多越好?
大多数情况下,索引能大幅提高查询效率。但是:数据变更(增删改)都需要维护索引,因此更多索引意味着更多维护成本;也意味着需要更多控件空间(一本书100页,却有50页目录?);过小的表,建索引可能会更慢。
4.索引的字段类型问题
text类型,也可建索引(需要指定长度);MyISAM存储引擎长度综合不能超过1000字节;用来筛选的值尽量保持和索引列同样的数据类型。
5.like能用到索引?
尽量减少like查询,但是也不是绝对不可用,'xxx%'是可以用到索引的。除了like,以下操作符也可以用到索引:
<,<=,=,>,>=,between,in
这些用不到索引:
<>,not in,!=
6.什么样的字段不适合建索引?
列的值唯一性太小(比如性别,类型),不适合建索引。(什么叫大小?一般来说,同值的数据超过表的15%,那就没有必要建索引了)更新非常频繁的数据不适合建索引。
7.一次查询能用多个索引?
不能
8.多列查询该如何建索引?
一次查询只能用到一个索引, a列建索引还是b列建索引?谁的区分度(同值的少)更高,建谁!当然,联合索引也是个不错的方案。
9.联合索引的问题
-- 命中col1、col2联合索引 select col1,col2 from test where col1 = 'xxx'; -- 不能命中col1、col2联合索引 select col1,col2 from test where col2 = 'xxx';
所以大多数情况下,有col1、col2索引了,就不用再去建col1索引了
10.哪些常见的情况不能用到索引?
like '%xxx' not in !=
对列进行函数运算,如:
where md5(password) = "xxx"
存了数值的字符串类型字段(如手机号),查询是记得不要丢掉值的引号,否则无法命中索引:
select * from test where mobile = 13800002222;
如果mobile字段是char或者varchar类型,则上面查询无法命中索引,应为:
select * from test where mobile = '13800002222';
11.NULL的问题
Null会导致索引形同虚设,所以在设计表结构应避免NULL的存在。
可用其他方式来表达,比如-1。