大型网站优化之mysql优化二

一、索引补充

1、前缀索引

前缀 前边的一部分

列:班级里的同学

张三

李四

王五

赵六

前缀索引:可以通过数据的前一部分区别出数据的唯一性,把这部分增加索引。比把字段所有的内容增加索引,更加节省空间,速度更快。

取出掉重复的,通过前几位确定唯一性。

①确定建立索引前缀长度

语法:distinct()

语法:substr(str,start,length)

②建立前缀索引

语法:alter table 表名称 add key 索引名称(字段(前几位))

in条件索引使用

in条件 枚举 同时查询出多条(不连续的)

如果某个字段,经常使用到in条件进行查询。可以选择给此字段增加索引。in条件可以使用到索引。

全文索引

文本类型 char varchar text

通过分词,然后给分词建立索引。

解决like查询(左固定),使用不到索引的问题。

3.1 增加全文索引

①建立一个测试表articles,并写入数据

②增加索引

③测试使用

3.2 复合全文索引

①增加复合全文索引

②测试使用

3.3 使用全文索引查询数据

match against是查询全文索引的语法。

全文索引里没有给查询的关键词建立索引。mysql本身不会把经常使用的一些词汇建立索引。

在实际业务开发环境,经常使用到的单词,反而没有建立索引。不适用于使用需求。会选择一些第三方软件配合mysql实现全文索引技术和搜索引擎技术。

php + sphinx 分词技术 全文索引技术 搜索引擎技术

xunsearch

java + lucene solr

3.4 全文索引注意

①只支持文本类型 char varchar text

②mysql5.6以下 myisam支持

③分词不适合实际使用,不支持中文

④选择sphinx等第三方软件代替mysql的全文索引

4、分页优化

分页原理:集合sql语法 limit(skip,length);

在大数据量下的表,进行分页limit语法操作,出现分页skip太大,使用不到索引的情况。

优化方案:

①进行数据的卡位操作

注意在进行where条件限制范围的时候,注意参数条件的起始值。

正常来说,数据表的主键id是自增和顺序的,所以skip跳过值和主键id是一致的。

以上列子,是因为id不是从1开始,所有要加上起始值。如果在数据表中,有删除的数据,主键id不连续,那么查询出来的结果,也就不一致。

分页优化,就是要把查询的limit范围,先通过where字段条件缩小,就可以使用索引了。

因为如果mysql认为查询索引之后,获取到的数据还是范围很大,就不会使用索引。

5、索引结构的类型(了解)

①聚集型索引

聚合 聚簇

数据文件和索引文件是在一起的

innodb .ibd

②非聚集型索引 非聚簇型索引

数据文件和索引文件是分离的

myisam .MYD .MYI

5.1 Myisam索引数据结构

主键索引

主键索引索引查询过程:

查询关键字=>主键索引=>物理地址 =>真实数据

非主键索引

非主键索引索引查询过程:

查询关键字=>非主键索引=>物理地址=>真实数据

5.2 Innodb索引数据结构

主键索引

主键查询数据过程:

关键字=>主键索引=>真实数据

非主键索引

非主键查询数据过程:

关键字=>非主键索引=>主键索引=>真实数据

Tip:如果innodb没有主键索引,非主键索引如何使用呢?

记录一个数据的唯一值。如果没有主键,会生成一个rowid(唯一)。非主键索引就记录这个rowid。

https://segmentfault.com/q/1010000003856705

https://www.cnblogs.com/softidea/archive/2016/08/29/5816497.html

二、缓存设置

mysql中提供了缓存机制,可以通过对应的sql语句,把数据结果保存起来,之后直接返回缓存数据的结果即可。提高了查询速度。

1、具体使用

①查看并开启缓存

mysql > show variables like 'query_cache%';

在/etc/my.cnf 配置文件里修改

shell > vim /etc/my.cnf

shell > service mysqld restart

选做可以设置缓存的空间大小,单位B 字节

mysql > set global query_cache_size=64*1024*1024;

②测试查看使用

测试使用emp表,因为之前已经把字段基本都建立索引,如果想要测试效果,可以选择把某个字段删除索引,进行查看。

2、缓存失效

当数据表结构发生改变时,缓存会失效。

数据库操作:

写:insert update delete 会是数据表结构发生改变

读:select

3、使用不到缓存的情况

当sql语句中存在时间值或者随机数时,就会使用不到缓存

now() rand()

4、生成多个缓存

由于sql语句大小写、空格等,会被认为是多条不同的sql语句,就会生成多个缓存

针对以上情况,在书写sql语句时,一定按照约定好的格式和规范。

5、不使用缓存

使用sql_no_cache语法,不使用到缓存

6、缓存其他操作

①查看缓存使用的状态

mysql > show status like 'Qcache%';

如果缓存命中率低。可能缓存空间设置太小。数据表更新频繁。

②清空缓存

mysql > reset query cache;

三、分表设计

在一般业务环境使用中,使用索引、缓存、内存缓存优化。能够满足大部分业务使用需要的速度。

随着业务发展,数据表中的数据量增加太快。百万级以上,千万级别亿级别时,单个数据表已经不能支撑业务需求了。数据表表活性大大降低,性能成指数下降,可以选择分区表的方式。把数据分配到多个数据表中,以提高数据的读写速度。

还有一种称呼叫做,分片 shareding

1、分区表类型区别

分片

分区、分表类型:

①逻辑分区 分表 真实还是一个表 逻辑分为多个 使用的sql语句和单表相同 mysql本身语法

②物理分表 把数据分配到几个真实数据表中,sql语句需要确定操作哪个表 手动建立分表 业务代码实现操作哪个分表

2、四种格式的逻辑分表

逻辑分区分类:

①取余方式 key/hash 会根据数据表的算法分配多个分表中

②条件方式 range/list 数据满足某一个分表的条件,就被分配到分表

分表条件必须是主键或者是主键的一部分(联合主键)

2.1、key分表

语法:

create(

字段 类型

……

)

partition by key (字段) partitions 分区数目

①建立实现key方式分表

②写入查询数据查看

2.2、hash分表

语法:

create(

字段 类型

……

)

partition by hash(表达式(字段)) partitions 分区数目

②写入查询数据查看

2.3、range分表

语法:

create(

字段 类型

……

)

partition by range(字段/表达式) (

partition 名称1 values less than (常量),

partition 名称2 values less than (常量),

partition 名称3 values less than (常量),

);

②写入查询数据

2.4、list分表

语法:

create(

字段 类型

……

)

partition by list(字段/表达式) (

partition 名称1 values in (列表1),

partition 名称2 values in (列表2),

partition 名称3 values in (列表3),

);

②写入查询数据查看

3、分表管理

数据表已经建立好,或者分区数据表后期需要增加或者删除表。可以对相应的数据表进行管理操作。

3.1、key/hash分表管理

语法:

求余(key/hash)分区:

增加:alter table 表名 add partition partitions 5;

减少:alter table 表名 coalesce partition 12;

key/hash 方式,数据存储方式和业务联系(条件)不紧密

3.2、range/list分表管理

语法:

增加:

alter table 表名 add partition(

partition 名称 values less than (常量)

partition 名称 in (n,n,n)

);

减少:

alter table 表名 drop partition 分区名称;

range/list方式,删除分区数据会丢失,和业务联系紧密。

4、物理分表设计

物理分表的方式:

①水平分表

②垂直分表

4.1、水平分表

把数据表进行横向切割,数据是完整的一行(row)。根据某个字段的取值范围。

分为emp1,emp2表。

例如:

在数据表中有多个年份的数据。经常使用的数据是今年的。

根据年份分为:

emp_2015 emp_2016 emp_2017

通过emp的empno的id分表

前10000条数据 分为emp_1

10000条-20000条 分为emp_2

。。。。。。

4.2、垂直分表

垂直分表,将数据进行竖向切割。

例如:

user表

id username password sex hobby cardid

需求:如果进行用户登录操作,需要查询id username password

解决方案:把user表通过常用的字段和不太常用的字段,分割为两个表

user表

id username password

user_ext表

id sex hobby cardid user_id

以上分表可以提高登录速度。

根据业务需求,选择对应的分表方式。

四、慢查询日志设置

慢查询:如果执行的sql语句,返回数据时间,大于设置的设置(规定的时间),这个sql语句就被认为是一个慢查询。

①慢查询日志的开启、日志位置

mysql > show variables like 'slow_query%';

mysql > set global slow_query_log = 1;

②快慢时间临界点

mysql > show variables like 'long_query_time';

mysql > set long_query_time = 2;

③执行一条大于2s的sql

实际线上运营中,经常会有服务器运维人员,把慢查询的日志发送给开发维护人员,进行sql语句的优化。

其他优化

修复表

在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片,我们可以通过一个操作(不产生对数据实质影响的操作)来修改表,修复表语法操作。

①新建一个testnum表

②增加模拟数据

复制产生多条数据

③删除表数据

通过修复表语句,进行表的碎片修复。

语法:optimize table 表名;

修复表碎片和空洞操作,是为了减少遍历时间空间,提供查询速度。

修复表碎片需要时间,不要经常做此操作。可以周期性执行。

锁机制

lock

锁 占用(保护)的作用。

锁是一个标识,表示这个资源已经被占用,需等待。

锁的实际使用,就是为了能够让用户进行等待操作。阻塞用户的操作。

操作系统原理 进程 锁

语法:lock table 表名称 锁定方式

unlock tables

锁类型:

读锁 共享锁 S锁 锁定之后可读不可写

写锁 排他锁 独占锁 锁定,其他都不可操作

锁粒度:

表锁 锁定整个表 操作某一行数据锁定之后,其他数据也不能操作了

行锁 innodb 行锁定 锁定的是索引

语法:

begin;

执行语句;

commit;

实际小案列分析

如果并发读写过程中,不进行锁操作,排队操作,会发生什么?

现在的数据值

使用ab.exe进行模拟并发测试

-c 并发数

-n 总请求数

刚才的mysql操作可以实现并发数据丢失的问题,同时有另外问题,锁表其他操作也不能过进行了。实际开发中,可以使用文件锁的方式。通过文件的标识来进行确定是否具有操作权限和资格。

锁机制,就是为了防止用户进行并发操作时,出现数据不完整和不一致的情况。让操作一个一个执行,进行了阻塞排队操作。

实际应用:

例如:抢购出现了超卖现象,如何避免?

①把剩余的商品数量设置为unsigned(没有负数)

②在下订单抢购的时候,使用事务机制和锁机制实现