MySQL

MySQL分页有什么优化

在数据库设计合理的情况下，可以使用适当的查询逻辑提升查询性能，如: 当数据库主键设置为连续增长序列时，

select * from table limit 2000000, 200
可以修改成
select *from table where id >= 2000000 limit 200

使用索引尽可能地覆盖查询，而不是查询所有列；然后根据需要做一个关联操作，再返回所需的列，

select id, desc from table limit 10000 limit 100
可以修改成
select id, desc from table inner join (select id from table limit 10000, 100) as t using(id)

悲观锁、乐观锁

悲观锁

• 概念: 阻止一个事务以影响其他用户的方式来修改数据，如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作
• 流程: 在对任意记录进行修改前，先尝试为该记录加锁。如果加锁失败，说明该记录正在被修改，那么当前操作可能要等待或抛错；如果加锁成功，那么对数据进行操作，事务完成后释放锁
• 优点: 安全
• 缺点: 性能开销大

乐观锁

• 流程: 假设多用户并发的事务在处理时不会彼此相互影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交事务更新之前，每个事务会先检查在该事务读物数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚
• 实现: 一般乐观锁并不会使用数据库提供的锁机制，一般的实现方式是记录数据版本，为数据增加一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当提交更新的时候，判断数据库表对应记录的当前版本与第一次取出来的版本标识进行比对，如果一致，则更新数据；否则认为是过期数据
• 优点: 效率高
• 缺点: 可能会遇到不可预期的问题

组合索引、最左原则

• 最左原则，即最左优先。在创建了col1_col2_col3的组合索引时，相当于创建了col1单列索引，col1_col2组合索引以及col1_col2_col3组合索引
• 在创建组合索引时，要根据业务需求，where子句中使用最频繁的一列放在索引的最左边
• 在mysql执行查询时，只能使用一个索引，如果我们在col1, col2, col3上分别建立索引，执行查询时，只能使用一个索引，mysql会选择最严格(获得结果集最少)的索引

MySQL的表锁、行锁

表锁

• 每次操作锁住整张表
• 开销小
• 加锁快
• 不会出现死锁
• 发生锁冲突的概率最高
• 并发度最低

行锁

• 每次操作锁住一行数据
• 开销大
• 加锁慢
• 锁粒度小
• 发生锁冲突的概率低
• 并发度高

用户自己加锁语句

• 共享锁(S) SELECT * FROM table WHERE ... LOCK IN SHARE MODE
• 排他锁(X) SELECT * FROM table WHERE ... FOR UPDATE
• 自己加的锁没有释放语句，锁会持续到事务结束为止

Mysql性能优化

• 使用查询缓存
  • set session query_cache_type=ON
  • 如果在缓存开启的情况下不想缓存查询结果，使用SELECT sql_no_cache * from table
  • 当表中insert、update、delete数据时，该表的缓存会立即清除
• EXPLAIN分析select 的结果，根据结果优化相应的索引，从而提升查询效率
• 当只有一行记录时，使用limit 1
• 为where后面的搜索字段添加索引
• 在join表的时候使用相同类型的列，并将其索引
• 不使用ORDER BY RAND()
• 避免使用SELECT *
• 为每张表设置一个id字段，作为主键，设置为unsigned int 类型
• 使用enum而不是varchar类型，事实上enum保存的是tinyint类型
• 建表时尽可能使用NOT NULL
• 使用Prepared Statements
• 设置固定长度的字段
• 垂直分割表，可选字段或者大内容的字段可以分离出去
• 拆分大的DELETE、INSERT语句
• 越小的列查询越快
• 选择正确的存储引擎，比如数据库没有insert、update、delete时，使用MYISAM
• 使用一个对象关系映射器，如: Hibernate、MyBatis
• 防止永久链接