mysql一些问题点整理

2020-06-15 本文已影响0人奔向学霸的路上

1. mybatis事务配置

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
</bean>
]<tx:annotation-driven transaction-manager="txManager"/>

2. mysql读写分离

可以通过sharing jdbc配置主从库，默认主库写，从库读

3. mysql索引

唯一索引、普通索引、组合索引、全文索引

全文索引主要用来查找文本中的关键字，目前可以在char，varchar，text列上使用。
在数据量比较大的时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext，然后再将数据写入的速度快很多。

4. mysql优化

分为索引优化、sql语句优化、表优化（可参考https://blog.csdn.net/c814276009/article/details/82656535）

4.1 索引优化:

  > 比较好的文章[https://www.toutiao.com/i6732776474308248072/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1591060999&app=news_article&utm_source=weixin&utm_medium=toutiao_android&use_new_style=1&req_id=202006020923190100180851470B51B35C&group_id=6732776474308248072](https://www.toutiao.com/i6732776474308248072/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1591060999&app=news_article&utm_source=weixin&utm_medium=toutiao_android&use_new_style=1&req_id=202006020923190100180851470B51B35C&group_id=6732776474308248072)

- 在where子句经常使用的列，最好设置索引，这样会加快查找速度
- 对于有多个列where或者order by子句的，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作

4.2 慢SQL优化

image.png

4.2.1 捕获低效SQL
slow_query_log（这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句）
4.2.1 优化方法（略过）
4.2.2 优化原则

查询时，尽量写全字段名，不使用*
大多时候连接效率高于子查询
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化
多表连接时，尽量小表驱动大表，即小表 join 大表
在千万级分页时使用limit

4.3 数据库表优化

表字段尽可能用not null
表字段长度固定查询更快
数据库大表按时间或者一定规律拆分为小表

水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

5.SQL注入

直接使用JDBC，如果代码中存在拼接SQL语句，那么很可能会产生注入

// concat sqlString 
sql = "SELECT * FROM users WHERE name ='"+ name + "'";Statement stmt = connection.createStatement();ResultSet rs = stmt.executeQuery(sql);

安全的写法是使用参数化查询，即SQL语句中使用参数绑定占位符。
在MyBatis中，使用XML文件或Annotation来进行配置和映射，将interfaces和Java POJOs映射到database records，MyBatis 使用 #{} 来进行参数值替换。

#{}：MyBatis 会自动生成 PreparedStatement ，使用参数绑定 ( ?) 的方式来设置值。
${}：MyBatis 会直接注入原始字符串，即相当于拼接字符串，因而会导致 SQL 注入。