数据库优化之(创建索引、分表、读写分离、缓存）

2019-04-19 11:06:26 浏览数 (1)

数据库优化之创建索引

索引（Index）是帮助DBMS高效获取数据的数据结构。 分类：普通索引/唯一索引/主键索引/全文索引。

普通索引: 允许重复的值出现； 唯一索引: 除了不能有重复的记录外，其它和普通索引一样(用户名、用户身份证、email、tel)； 主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引，唯一且没有null值； 全文索引:用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyISAM； explain select * from articles where match(title,body) against(‘database’)； 【会使用全文索引】

索引使用小技巧

创建索引的好处

–帮助用户提高查询速度

–利用索引的唯一性来控制记录的唯一性

–可以加速表与表之间的连接

–降低查询中分组和排序的时间

索引弊端 1.占用磁盘空间； 2.对SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作的语言有影响，索引维护，速度变慢； 3. 在数据处理时回需额外的回退空间；

实际数据修改测试:

一个表有字段A、B、C，同时进行插入10000行记录测试

在没有建索引时平均完成时间是2.9秒

在对A字段建索引后平均完成时间是6.7秒

在对A字段和B字段建索引后平均完成时间是10.3秒

在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

从以上测试结果可以明显看出索引对数据修改产生的影响… B*树索引

B树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B树索引。

使用场景： a: 肯定在where条件经常使用，如果不做查询就没有意义； b: 该字段的内容不是唯一的几个值(sex) ； c: 字段内容不是频繁变化.

具体技巧：

  1. 对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
代码语言：javascript复制
alter table dept add index my_ind (dname，loc)；   // dname 左边的列，loc就是右边的列
explain select * from dept where dname='aaa'      G 会使用到索引
explain select * from dept where loc='aaa'             G 就不会使用到索引
  1. 对于使用like的查询，查询如果是’�a’不会使用到索引，而’aaa%'会使用到索引。 explain select * from dept where dname like ‘�a’ G不能使用索引. explain select * from dept where dname like ‘aaa%’ G使用索引.

所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

  1. 如果条件中有or，有条件没有使用索引，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须单独使用时能使用索引.
  2. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。

数据库优化之分表

分表分为水平(按行)分表和垂直(按列)分表

根据经验，Mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度较少这些压力。按行数据进行分表。

如果一张表中某个字段值非常多(长文本、二进制等)，而且只有在很少的情况下会查询。这时候就可以把字段多个单独放到一个表，通过外键关联起来。 考试详情，一般我们只关注分数，不关注详情。

水平分表策略： 1.按时间分表 这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。 2.按区间范围分表 一般在有严格的自增id需求上，如按照user_id水平分表： table_1 user_id 从1~100w table_2 user_id 从101~200w table_3 user_id 从201~300w 3.hash分表***** 通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表

数据库优化之读写分离

一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多。一台服务器满足不要要求是就可以集群处理。Mysql的集群处理技术最常用的就是读写分离。

主从同步 数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的。能改变数据库数据的操作都往主数据库去写，而其他的数据库从主数据库上同步数据。

读写分离 使用负载均衡来实现写的操作都往主数据去，而读的操作往从服务器去。

数据库优化之缓存

在持久层(dao)和数据库(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不用访问数据库。而缓存是在操作内存级，访问速度快。

作用：减少数据库服务器压力，减少访问时间。

Java中常用的缓存有:

1、hibernate的二级缓存，该缓存不能完成分布式缓存。

2、可以使用redis(memcahe等)来作为中央缓存，对缓存的数据进行集中处理。

缓存 全文检索 数据库 sql

0 人点赞