[TOC] ## 字段类型 ### 字段类型的选择 #### 选择合理范围内最小的 我们应该选择最小的数据范围,因为这样可以大大减少磁盘空间及磁盘I/0读写开销,减少内存占用,减少CPU的占用率。 #### 选择相对简单的数据类型 数字类型相对字符串类型要简单的多,尤其是在比较运算是,所以我们应该选择最简单的数据类型,比如说在保存时间时,因为PHP可以良好的处理LINUX时间戳所以我们可以将日期存为int(10)要方便、合适、快速的多。 #### 不要使用null 为什么这么说呢,因为MYSQL对NULL字段索引优化不佳,增加更多的计算难度,同时在保存不处理NULL类形时,也会做更多的工作,所以从效率上来说,不建议用过多的NULL。有些值他确实有可能没有值,怎么办呢?解决方法是数值弄用整数0,字符串用空来定义默认值即可。 #### 字符串类型的使用 字符串数据类型是一个万能数据类型,可以储存数值、字符串等。 保存数值类型最好不要用字符串数据类型,这样存储的空间显然是会更大,而且在排序时字符串的9是大于22的,其实如果进行运算时mysql会将字符串转换为数值类型,大大降低效果,而且这种转换是不会走原有的索引的。 如果明确数据在一个完整的集合中如男,女,那么可以使用set为enum数据类型,这种数据类型在运算及储存时以数值方式操作,所 以效率要比字符串更好,同时空间占用更少 ### 数值类型的选择 整数类型很多比如tinyint、int、smallint、bigint等,那么我们要根据自己需要存储的数据长度决定使用的类型,同时tinyint(10)t和 tinyint(100)在储存不计算上并无任何差别,区别只是显示层面上,但是我们也要选择适合合适的数据类型长度。可以通过指定zerofill 属性查看显示时区别。 浮点数float和double在储存空间及运行效率上要优于精度数值类型decimal,但float不double会有舍入错误而decimal则可以提供更加准确的小数级精确运算不会有错误产生计算更精确,适用于金融类型数据的存储。 数值数据类型要比字符串执行更快,区间小的数据类型占用空间更少,处理速度更快,如tinyint可比bigint要快的多选择数据类型时要考虑内容长度,比如是保存毫米单位还是米而选择不同的数值类型 ## 数据库表设计 ### 水平分表 #### 按时间分表 这种分表方式有一定的局限性,当数据有较强的实效性,如微博发送记录、微信消息记录等,这种数据很少有用户会查询几个月前的数据,如就可以按月分表。 #### 按区间范围分表 把数据分到不同的表格中 <table > <tbody><tr> <td>table_1</td> <td>user_id从1~100w</td> </tr> <tr> <td>table_2</td> <td>user_id从100w~200w</td> </tr> <tr> <td>table_3</td> <td>user_id从200w~300w</td> </tr> </tbody></table> ### 垂直分表 一张表的字段过多的时候,我们可以把表垂直进行拆分,把不经常用的字段或者不经常查询的字段把到另外一张表,当扩展表用。需要的时候再联表查询。 ## 查询 ### 减少IO 根据业务的需求来获取想要的数据,尽量少使用*号,不要返回用不到的任何字段 对一些不常更新的数据,可以采用缓存的方式,避免过多的查询 对频繁插入的数据,也可以缓存的方式,避免频繁的写入操作。如浏览量 在Join表的时候使用相当类型的例 ### 索引 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引就像一本书的目录一样,我们可以通过一本书的目录,快速的 找到需要的页面,但是我们也不能过多的创建目录页,原因是如果 某一篇文章删除或修改将发变所有页码的顺序,就需要重新创建目 录 索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。 创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。 上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。 ## explain EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何 使用索引来处理select语句以及连接表,可以帮助选择更好的索引和 写出更优化的查询语句。 **全表查询** ![](https://box.kancloud.cn/581669021aceed5b11d2dc8dcd2437ba_789x269.png) **根据某个有索引的字段进行查询** ![](https://box.kancloud.cn/6b8b857430f42a594656ffdf6424638d_798x268.png) ## 定位慢查询 当Mysql性能下降时,通过开启慢查询来获得哪条SQL语句造成的响应过 慢,进行分析处理。当然开启慢查询会带来CPU损耗和日志记录的IO开销, 所以我们要间断性的打开慢查询日志来查看Mysql运行状态。 查询能记录下所有执行超过long_query_time时间的SQL语句, 用于找 到执行慢的SQL, 方便我们对这些SQL进行优化. ### 是否开启慢查询 ~~~ show variables like "%slow%"; ~~~ ### 查询慢查询SQL状况 ~~~ show status like "%slow%"; ~~~ ### 慢查询时间 ~~~ show variables like "long_query_time" ~~~ ### 开启记录慢查询 修改mysql配置文件my.ini加入 ~~~ slow_query_log = on; slow_query_log_file = /data/f/mysql_slow_xbs.log long_query_time = 2 //测试执行一条慢查询 mysql>select sleep(3); ~~~