#宽索引

MySQL 索引设计概要

Published at April 30, 2019 ·  1 min read

如何正确高效的设计索引,由于 hdd 的特性,随即访问数据是非常耗时的,对于数据库,无论是读取一行还是多行数据,都会将该行或多行所在的页面全部加载进来,然后再读取对应的数据记录。在 mysql 中,页的大小一般为 16 kb,也可能是 8kb,32 kb 或者其他值。数据库等待一个页从磁盘读取到缓存池所需要的成本巨大,需要消耗 10ms 左右的时间,如果顺序读取则会非常快,达到 40MB/s。如果一个页面的大小为 4kb,那么 1s 的时间就可以读取 10000 个页,读取一个页面所花费的平均时间就是 0.1 ms,相比随即读取的 10ms 已经降低了两个数量级,甚至比内存中读取数据还要快。数据库索引设计与优化中,分为宽索引与窄索引,每一个在索引中匹配到的记录行最终都需要执行另外的随即读取从聚集索引中获得剩余的数据,如果结果集非常大,那么就会导致随机读取的次数过多进而影响性能。 MySQL 索引设计概要...


Tags

abcs accept acid activemq affinity algorithm allocation android array async aws b+tree b-tree backoff benchmark best-practices bfs big-o bigquery bind bitcount blog break broker bubble buffer cache cap cert cgroups channel citus class classmethod cluster concurrency config consumer container cookie cors crawler cronjob csrf ctr data-science data-structure database datadog dataflow datascience decorator defer dfs distributed django dns docker double-shipping drf ecosia elastic-search enumerate epoll extra fabric2 facebook-pixel financial-report flask flutter forward-proxy freelance frontend frp garbage-collector gc gcp generator get gil git golang goroutine graphql ha handbook haproxy hash hash-slot hashring hashtable hpa http http-auth http-proxy http_proxy https index init innodb instagram intergration interview ios javascript jinja2 jobboard jwt k8s kafka kibana kqueue label lambda layer4 layer7 lean levels.io linked-list linux list listen loadbalancer logs long-tail lru marketing master matplotlib memory merge metaclass metaprogramming metrics metrics-server microservices mitm model mq myisam mysql namespace nat netflix network-extension nginx nodejs nomad nosql npm oodesign openssl optimization orm pandas parallelism paramiko parkinglot patroni permission pg pipeline pixelme post postgresql postresql prefetch_related prerender private-key process proxy proxycommand put pvm python queue rabbitmq rbac react-native reactjs rebase redis redis-cluster replication resource rest restfulapi retargeting retry revenue reverse-proxy rocketmq rsa saas scaleable search-engine security select seo serverless service session set shadosocks shadowsocks shard sharding shell shopify sigint signal sigterm slack slave slow-query sniper sns socket socks5 source-code spa sql sqlalchemy sqs ssh ssl ssl-pinning stack startup state stateful stateless staticmethod string struct swift switch syscall system-design systemctl tcp tcp-proxy thread tmpreaper token traefik trustkit tunning type typeform udp variable vc vpn vuejs web web-development where yarn zset 削峰 宽索引 异步 窄索引 解耦 跨域 跳板机


Archives

2019 (141)