mysql 索引,事务和性能优化

喜新厌旧_大舅哥

已于 2023-04-13 17:54:06 修改

阅读量214

点赞数

分类专栏：数据库文章标签： mysql 性能优化数据库

于 2023-04-13 17:50:03 首次发布

本文链接：https://blog.csdn.net/qq_43876886/article/details/130126091

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一索引

1.表结构设计

三范式：a每列不可分割 b每列都与主键相关 c要满足所有的字段不能出现对非主键的依赖
反三范式：a提高利用效率可重复列

2.设计列类型

数值类型

数据库字段类型的合理设计

类型	大小(bytes)	范围(有符号)	范围(无符号)	用途
TINYINT	1	(-128, 127)	(0, 255)	小整数值
SMALLINT	2	(-32768, 32767)	(0, 65535)	大整数值
MEDIUMINT	3	(-8388608, 8388 607)	(0, 16777215)	大整数值
INT或INTEGER	4	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
BIGINT	8	(-9233372036854775808, 9223372036854775807)	(0, 18446744073 709551615)	极大整数值
FLOAT	4	(-3.402823466E+38, 1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38)	0, (1.175494351E-38, 3.402823466E+38)	单精度浮点数值
DOUBLE	8	(1.7976931348623157E+308, 2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308)	0, (2.2250738585072014E-308, 1.7976931348623157E+308)	双精度浮点数值

如果整型数据没有负数，如id号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
建议使用TINYINT代替ENUM、BITENUM、SET。
建议使用整型类型来运算和存储实数，一种方法是实数乘以相应的倍数后再操作；另外一种方法是使用两个字段来分别存储整数位和小数位。
DECIMAL最适合保存准确度要求高并且用于计算的数据，比如价格、金额等，但是在使用DECIMAL类型的时候注意长度设置。
避免使用整数的显示宽度，也就是说不要用INT(5)类似的方法指定字段显示宽度，直接用INT。

2.字符串类型

类型	大小(bytes)	用途
CHAR	0-255	定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
VARCHAR	0-65535	变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
TINYBLOB	0-255	不超过 255 个字符的二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65535	二进制形式的长文本数据
TEXT	0-65535	长文本数据
MEDIUMBLOB	0-16777215	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215	中等长度文本数据
LONGBLOB	0-4294967295	二进制形式的极大文本数据
LONGTEXT	0-4294967295	极大文本数据

当字符串短，并且所有值都固定一个长度或者接近一个长度时使用CHAR，当然要是如果没有完全可以使用整型来存储；字符串长度相差较大时使用VARCHAR。
CHAR和VARCHAR适合长度不超过255个字符唱的的任意字母和数字组合，例如人名、电话号码、编码等。用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题，同时可能影响到计算的准确性和完整性。
VARCHAR(255)在建立索引时会占用比较多的存储空间，在不要求保证数据完全精确的境况下可以使用前缀索引。例如idx_name_age_position(name(20), age, position)，取前20个字符作为索引，但是这种情况下因为是不完全字段，所以order by name asc或者group by name 排序过程无法使用索引排序。当然需要保证数据的精确性和查找速度，最优的方案就是使用全文搜索引擎ES了。
尽量不用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，使用主键id来关联。
BLOB和TEXT都不能有默认值。BLOB系列存储二进制字符串，与字符集无关；TEXT系列存储非二进制字符串，与字符集相关。

3.日期类型

类型	大小(bytes)	范围	格式	用途
DATE	3	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’ 到 ‘838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00 到 2038-01-19 03:14:07 (格林尼时间)	YYYYMMDD HHMMSS	混合日期和时间值，时间戳
YEAR	1	1901 到 2155	YYYY	年份值

MySQL能存储的最小时间粒度为秒。当然要是需要精确到毫秒级的话，当然也是有办法的，新加一列在另外一列保留毫秒值即可。
建议使用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串或者保存时间戳，这样的话可以通过MySQL的日期函数处理相关逻辑。
当不需要具体时间时，建议用DATE数据类型来保存日期，MySQL中默认的日期格式是YYYY-MM-DD。
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为(MySQL5.6以后)，MySQL会自动返回记录插入的当前确切时间。不过需要注意的是校准MySQL运行环境的时间和时区，比如Linux时间或者docker容器的时间和时区。
TIMESTAMP是UTC时间戳，与时区相关；DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，存的什么读出来就是什么。
一般的短期项目或者小公司项目小二建议使用TIMESTAMP，因为这种项目生命往往活不到2038年，DATETIME还更节约空间。但是如果是腾讯、阿里、京东一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

3.索引种类和设计原则

(a)主键索引(聚合索引)

InnoDB存储引擎使用B+树建立索引,非叶子结点存放主键，叶子结点存放主键对应一整条记录。每张表只有一个主键索引，默认存在。

(b)辅助索引(非聚合索引)

除开主键索引的都是辅助索引，也叫二级索引。非叶子结点存放索引列，叶子结点存放主键和对应索引列。然后回表(主键索引)查询结果列。
索引覆盖，索引列包含最终查询结果列，不需要回表。
联合索引,索引列包含二或三列最终查询结果列，可能不需要回表。
最左前缀原则，结果列字段已经在辅助索引左侧出现，而且不越过辅助索引左侧某个字段，就可以走辅助索引查询。
联合索引顺序，索引列里，字段长放辅助索引最左侧。辅助索引后面列单独建立索引，既保证了联合索引，而且节约索引存储空间。

(c)索引失效

辅助索引会在最左前缀原则上，向右一直匹配，直到遇到范围查询(>,<,between,like)停止匹配。后续列就只能走全表，无法准确筛选。

(d)索引下推

在MySQL5.5以及之前的版本中，在满足范围匹配name like '小%'之后，并不会继续判断后面个age字段，直接就回表了。而从MySQL5.6开始，InnoDB存储引擎在匹配到满足name like '小%'之后，无法继续使用最左前缀原则的字段（如本例的age）依旧在联合索引中，则会根据这些字段多做一些过滤，不满足条件的记录将不会回表查询，减少了二次搜索的次数。

(e)索引重建

数据频繁删除和增加会导致维护索引的B+树频繁发生页的分裂，导致页空间中出现浪费的空间，提高了索引的占用空间。
解决方案就是把旧索引删除掉，然后重建索引。重建过程中会把数据重新按照顺序紧凑的排放，从而节约了空间。

4.索引实战设计

假设用户表user，数据量很大，在不考虑分库分表情况下，怎么通过建立索引配合sql来优化？，通常我们编写的sql，里面包含 where ,group by ,order by limit 等条件

select * from user where name=xxx and age==1 order by xx desc limit 10,1

接下来按照增加条件字段方式，来分析怎么根据业务场景设计索引。

where province = xx and city = xx and sex = xx

设计一个辅助索引（province_city_sex）解决问题

province=xx and city=xx and sex in ('male','female') and age >= 18 and age <= 28

设计一个辅助索引(province_city_sex_age)，像范围(<,>,between,like)放索引顺序最后面，针对有多个范围的查询，在业务范围允许下固定范围，然后索引就可以存储范围标识了。范围查询变成了等值查询。
索引设计必须结合场景来，思路就是尽量用不超过3个复杂的联合索引抗住80%的查询流量，然后再用一两个辅助索引查询常用流量。

参考文章：MySQL性能优化之索引设计_mysql索引设计_如来神掌十八式的博客-CSDN博客

二事务

1.事务特性(a,c,i,d)

原子性（Atomicity）：指事务做为最小不可分割整体，包含一条sql或者多条sql执行。要么全部执行成功，要么全部回滚到执行前状态。
一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态(时间上变化状态必须一致)，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。
隔离性（Isolation）：事务开启后，需要的数据和执行的操作对于其他事务是隔离的和内部的，并发执行的各个事务之间不能互相干扰。所以必须有锁控制顺序
持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

2.事务分类

隐式事务：如 update ,insert,delete操作会自动开启事务提交功能，数据库默认开启隐式事务
显式事务：查询数据库事务select @@autocommit，设置事务自动提交 set autocommit=0 禁用

3.事务开启步骤

a.开启事务 start transaction;

b.执行事务sql语句(update,delete,insert) update test set money=150 where name='张三'

c.提交事务 commit;

d.##如果事务执行出错回滚 rollback;

4.事务并发

同一时刻不可能只有一个事务执行，A事务在操作张三账户，B事务也在操作张三账户。对于同时发生事务，对事务进行隔离处理必不可少。

a.脏读 A事务读取 B事务更新数据，B事务没有提交。如B事务回滚 A事务读的数据临时和无效。

原因 A读取一次因为B事务回滚导致结果不可控

b.幻读 A 事务读取B事务更新字段，B事务插入新数据，A事务再读取发现数据多出几行。

原因 A读取两次因为B事务提交删除和插入操作

c.不可重复读 A事务读取B事务更新字段，B事务修改字段，A事务再读取发现内容不同。

原因 A读取两次因为B事务提交修改操作

为了避免出现事务并发问题，mysql数据提供四种事务隔离级别，用来隔离并运行各种事务，隔离性和一致性。

5.事务隔离级别种类

a.read uncommitted （读未提交数据）允许A事务读取B事务提交的变更。会发生脏读，幻读，不可重复读。

b.read committed（读已提交数据）只允许A事务读取B事务提交的变更,会发生不可重复读和幻读。

c.repeatable read (可重复读) 确保A事务多次读取B事务提交的变更值一致，会发生幻读。

d.serializable（串行化）确保A事务多次读取B事务提交的变更后行数一致，所有并发问题都可避免，但性能十分低下

隔离等级依次升高，越高数据一致性和隔离性越好，但是并发性能就下降。数据库默认是repeatable read (可重复读)。

数据库隔离分为全局隔离和当前回话隔离。

参考文章：MySQL事务(transaction) (有这篇就足够了..)_胡亦.的博客-CSDN博客

三数据库锁

1.乐观锁和悲观

a.乐观锁不是数据自带需要我们自己实现，修改数据时候不加锁而是在进行更新后再判断是否有冲突，相等就更新，不相等说明期间被人更新了就不更新。

1.select version from xx where id=xx;

2.update test set id=xx and version=xx;

b. 悲观锁数据库自带，每次操作都要获取锁才行，所以效率比较低。分为共享锁和排他锁

1.共享锁 select * from table where id=1 lock in share mode 称为 S锁只读不能写

2.排他锁 select * from table where id=1 for update 称为X 锁只能读写别人不能读写

2.表锁

参考文章：Mysql基础(十九)：锁_mysql的锁_CodingALife的博客-CSDN博客

四 sql优化

1.优化目标

a.减少磁盘IO（数据库层面索引）

添加适合业务的联合索引。
select查询的时候不使用select * ，新增的时候同理。避免<,>,between,like范围查询条件
尽量避免使用子查询（原因写在子查询优化）

b.减少cpu消耗

多表连接(inner join)。
聚合函数（max,min,sum）
排序函数和分组函数(order by ,group by )

c.减少网络宽带

sql 查询时候分页数据条数减少
减少插入和修改数据传输的次数

2.子查询优化

a.数据库不会对嵌套查询和子查询等临时数据建立索引，所以查询性能会有影响。查询结束后产生大量cpu消耗和IO消耗，和大量慢查询。

b.将子查询变为连表查询

原先：select * from test where id in (select test1_id from test2)

修改：select * from test t1 left join test2 t2 on t1.id = t2.test1_id

c.limit 优化

原先：select * from test limit 99990,10

修改：select * from test t1 inner join (select id from test order by create_time limit 99990,10) t2 on t1.id = t2.id

d.left join 优化为inner join

join_buffer（Bloack Nested-Loop）表示不能通过索引去做关联条件的匹配

参考文章：Mysql sql优化_Blurred me的博客-CSDN博客