1：防SQL注入，prepare

• 使用预处理语句： delete from user where id = ?

2：写入数据库的数据要进行特殊字符的转义

3：查询错误信息不要返回给用户，将错误记录到日志

4：PDO

PHP端尽量使用PDO对数据库进行相关操作，并且对预处理语句很好的支持，MySQLi也有，但是可扩展性不如PDO，效率高于PDO，MySQL函数在新版本中已经趋向于淘汰，所以不建议用，而且它没有很好的支持预处理

5：其它安全设置

• 定期做数据备份
• 不给查询用户root权限，合理分配权限
• 关闭远程访问数据库权限
• 修改root口令，不用默认口令，使用较复杂的口令
• 删除多余的用户
• 改变root用户的名称
• 限制一般用户浏览其他库
• 限制用户对数据文件的访问权限

1：什么是分表?

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它

2：什么是分区?

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

3：mysql分表和分区有什么联系呢？

• 都能提高mysql的性高，在高并发状态下都有一个良好的表现。
• 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
• 分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
• 表分区相对于分表，操作方便，不需要创建子表。

我们知道对于大型的互联网应用，数据库单表的数据量可能达到千万甚至上亿级别，同时面临这高并发的压力。Master-Slave结构只能对数据库的读能力进行扩展，写操作还是集中在Master中，Master并不能无限制的挂接Slave库，如果需要对数据库的吞吐能力进行进一步的扩展，可以考虑采用分库分表的策略
参考

1：分区

• 分区表原理：

创建表时使用partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需查询所需数据在的分区即可。

• 分区目的：

是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便

• 适用场景

1：表非常大，热点数据和历史数据。2：分区表更易维护，可以对独立的分区进行独立的操作。3：分区表的数据可以分布在不同的机器上，从而高效使用资源。4：避免某些特殊的瓶颈。5：可以备份和回复独立的分区。

• 限制

1：一个表最多只能有1024分区。2：5.1版本中，分区表达式必须是整数，5.5可以使用列分区。3：分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来。4：分区表中无法使用外键约束。5：需要对现有表的结构进行修改。6：所有分区都必须使用相同的存储引擎。7：分区函数中可以使用的函数和表达式会有一些限制。8：某些存储引擎不支持分区。

2：分库分表

• 工作原理：通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分。
• 适用场景

1：单表记录条数达到百万或千万级别时。2：解决表锁的问题。

• 分表方式

水平分割：分割后可以降低查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度
使用场景：1：表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。2：需要把数据存放在不同介质上
水平分表缺点：1：给应用增加复杂度，通常查询时需要多个表明，查询所有数据都需UNION操作。2：在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

垂直分表：
工作原理：把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中
使用场景：1：一个表中的常用列和不常用列。2：可以使数据行变小，一个数据页能存储更多的数据，查询时减少I/O次数
缺点：管理冗余列，查询所有数据需要JOIN操作

3：MySQL的复制原理及负载均衡

• 主从复制工作原理：在主库上把更改记录到二进制日志binlog;从库将主库的日志复制到自己的中继日志;从库读取中继日志中的事件，将其重放到从库数据中
• 解决的问题：

1：数据分布：在不同的地理位置分布数据备份；负载均衡，降低单个服务器的压力；高可用和故障切换，帮助应用程序避免单点失败；升级测试，可以使用更高版本的MySQL作为从库测试

1：简述项目中优化SQL语句执行效率的方法，从哪些方面，sql语句性能如何分析

• 分析查询速度慢的原因
• 优化查询工程中的数据访问
• 优化长难的查询语句
• 优化特定类型查询语句

2：优化速度慢

• 避免访问太多数据
• 检查是否在检索大量的超过需要的数据
• 确认MySQL服务器是否在分析大量不必要的数据行

• 避免:

  查询不需要的数据，使用limit
  多表关联，指定字段
  避免select *
  重复的查相同的数据，可以缓存

• explain进行分析，添加索引
• 修改数据表的结构，修改数据表范式
• 重写SQL语句

3：优化长难(mysql扫描的很快，只是响应数据就很慢了)

• 切分查询

复杂查询可以在特殊的情况分解成多个简单查询

• 分解关联查询

关联分解成多条SQL来执行
提高缓存
执行单个查询可以减少所得竞争

4：优化特定类型

• count(*)会忽略所有的列，直接统计所有列数，因此不要使用count(列名)

  增加汇总表
  使用缓存

• 关联查询

  给on或字句的列上加索引
  确保group by 和order by 中只有一个列这样mysql才有可能使用索引
  少使用子查询

• 优化limit分页

  limit偏移量大的时候，查询效率较低
  可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
  加个条件where id > 上次查询的最大ID

1：MySQL索引创建原则

• 最适合索引的列是出现在where字句中的列，或连接子句中的列而不是出现在select关键字后的列（联查的on后）
• 索引列的基数越大，索引的效果越好
• 对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
• 根据情况创建复合索引，复合索引可以提高查询效率（类似章和节）
• 避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率

2：MySQL索引注意事项

• 复合索引遵循前缀原则(连续的左侧)（最左侧原则）

  key(a,b,c) a,b,c; a,b; a(这些才能生效)
  只创建一个索引，同时作用在这个组合上
  效果是缩短这个范围（章节段）

• like查询,%号不能在前，可以使用全文索引

  where name like "%wang";
  索引不生效

• column is null可以使用索引

• 如果MySQL估计使用索引比全表更慢，会放弃使用索引

  where id >1 and id < 100
  查找90条

• or前的条件有索引，后面的没有，索引都不会被用到（这也是为什么尽量少用or的原因）

  where id(它有索引) > 20 0r age(没有索引) < 30

• 列类型是字符串，查询时一定要给值加引号，否则索引失效

  name varchar(10)
  "100"
  where name = 100(这样能找到，但是不会用到索引)

例：
1.使用了!=运算符及not in, not exist等，认为产生的结果集很大，往往导致引擎不使用索引而使用全局扫描；
2.对索引使用了函数，如where substr(name,1,3)='mark'，会导致索引无效

1：索引的含义

• 类似书籍的目录

2：索引对性能的影响

• 大大减少服务器需要扫描的数据量
• 帮助服务器避免排序和生成临时表
• 将随机的I/O变顺序I/O
• 大大提高查询速度，降低写的速度，占用磁盘空间（索引也是一种数据）

3：使用场景

• 非常小表，不适用
• 中到大兴表，索引非常有效
• 特大型的表，建立和使用索引的代价将随之增长，可以使用分区技术来解决

4：索引的类型

• 普通索引：最基本的索引，没有任何约束限制
• 唯一索引：与普通索引类似，但是具有唯一性约束
• 主键索引：特殊的唯一索引，不允许有空值

5：主键和唯一索引的区别

• 一个表只能有一个主键索引，可以有多个唯一索引
• 主键索引一定是唯一索引，唯一索引不是主键索引
• 主键可以与外键构成完整性约束，防止数据不一致

6：组合索引

• 将多个列组合在一起创建索引，可以覆盖多个列
• 复合索引，在索引建立语句中同时包含多个字段名，最多16个字段

7：其它索引

• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据一致性，完整性和实现级联操作
• 全文索引：只能用于MyISAM,并且只能对英文进行全文检索

1：inodb优势

默认事物型，最重要最广泛，性能非常优秀
数据存储共享表空间
对主键查询的性能高于其他类型的存储引擎
内部优化较好，读取时自动在内存构建hash索引，插入时自动构建缓存区
通过一些机制和工具支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键

2：MyISAM:

5.1之前，是默认的存储引擎
拥有全文索引，压缩，空间函数
不支持事务和行级锁，不支持崩溃后的安全恢复
存储在两个文件，myd(数据)和myi(索引)
设计简单，某些场景性能很好）（*查）

1：整形

• TINYINT SMALLINT MEDIUMINT INT BIGINT

属性：unsigned表示无符号的意思,也就是非负数,只用于整型
长度：可以为整数类型指定宽度，对大多数应用是没有意义的，不会限制值的合法范围，只会影响显示字符的个数；如果没有达到这个宽度，会在前面zerofill
FLOAT DOUBLE DECIMAL
DECIMAL可存储比BIGINT还大的整数，可以用于存储精确的小数
FLOAT和DOUBLE类型支持使用标准的浮点进行近似计算（小数点后有取值范围）计算

2：字符串

• VARCHAR CHAR TEXT BLOB

varchar用于存储可变长度字符串，它比定长类型更节省空间
varchar使用1个或2个额外字节记录字符串的长度，列长度小于255字节，使用1个字节表示，否则用2个，会截取超过设定的长度
char定长,根据定义的长度分配足够的空间，会采用空格填充不够的空间
char适合存储很短的字符串或者所有值都接近同一个长度（密码），也会被截断超过定义的长度
对于经常变更的数据，char比varchar更好，char不容易产生碎片
对于非常短的列，char比varchar在存储空间上更有效率
尽量分配真正需要的长度空间，减少消耗的内存
blob/text少用，会产生临时表，消耗更大的内存

3：枚举

优势可以使用枚举代替常用的字符串类型
把不重复的集合存储成一个预定义的集合
非常紧凑，把列表压缩到一个或两个字节
内部存储的是整数
尽量避免使用数字作为枚举的常量，易混乱
排序按照内部存储的整数进行排序
使表大大减小

4：时间

尽量使用timestamp,比datetime空间效率高
用整数保存时间戳的格式通常不方便处理
如果存储微妙，可以使用bigint