mysql表设计注意事项 mysql使用注意事项

转载

footballboy 2024-01-02 11:53:12

文章标签 mysql表设计注意事项 mysql 服务器字段 文章分类 MySQL 数据库

自己小结下，多多向高手学习

1、在写统计方面的sql时，有时可将大的sql拆分。

统计的列表页面分页，先查主表数据，再执行简单的sql去查其他数据。数据量大时，效果很明显。

2.事务尽量细粒度，不用臃肿。不影响数据的情况下，尽量将业分成多个小的事务。

3.不要使用select * 无用的字段不需要，浪费io,有时表变化用*可能会出麻烦。可减少服务器硬盘临时表生成，特别是有大字段、text/blog时。

4.查询同字段多个值时尽量不要用or.改为用in的形式，数量大时，or会慢很多，in的数量最好不要超过10000.

select id,name from user where id=1or id =2

select id,name from user where id in(1,2)

5.查询不同条件时有时可用uniox

select id from user where id =1 or name ='hello';//及时id和name都有创建索引，查询执行计划，可能此查询走的是全部扫描

select id feom user where id=1 union

select id from user where name ='hello'

6.尽量少用负向查询

NOT 、!=、<>、 not in 、not exists、 not in、not like

7.count时null字段不会计算，查询时注意null的操作，查询是否为空is null

8.分页时

selectid,name from usre limit 100000,20 偏移量越大，越慢。

如果时按id查询的可以改为

selectid,name from usre where id >=1 limit20 //第一页

selectid,name from usre where id >=21 limit20//第2页

selectid,name from usre where id >=41 limit20//第3页

不过只使用于能按照id排序和分页的。而且id必须连续。一般项目都按条件查询，此建议不实用。

id不连续时：

selectid,name from usre where id >=(selectid,nameselect id from user where limit 100000,1 10000)

9.写where条件时，字段的类型不要用错。a为varchar类型 a>888 和a>'888' 效率不一样。而且索引的类型也不一样

10.自动尽量不要用null的。null建立索引占的空间更大，复合索引是无效的。不好优化。

11.尽量不要使用计算的函数。

12.使用子表查询时，子表的数据不能过大，量小时实用

select a.id,a,name from a join (select b.id id,b.name from b where b.id<1000000 ) as b1

on a.id =b1.id 表b有索引时会很快，子查询生成的临时表b1，是没有索引只说的。数据量大时反而会慢。

1. 逻辑查询处理
在SQL语言中，最先被执行的是FROM操作，最后执行的是LIMIT操作。每个操作都会产生一张虚拟表，该虚拟表作为一个处理的输入。
这些虚拟表对用户是透明的，只有最后一步生成的虚拟表才会返回给用户。
                逻辑查询处理步骤序号
                (8)SELECT(9)DISTINCT<select_list>
                (1)FROM<left_table>
                (3)<join_type>JOIN<right_table>
                (2)            ON<join_condition>
                (4)WHERE<where_condition>
                (5)GROUP BY<group_by_list>
                (6)WITH{CUBE|ROLLUP}
                (7)HAVING<having_condition>
                (10)ORDER BY<order_by_list>
                (11)LIMIT<limit_number>

以下具体分析查询处理的各个阶段：
1） FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡尔积<Cartesian product>，产生虚拟表VT1。
2） ON：对虚拟表VT1应用ON筛选，只有那些符号<join_condition>的行才被插入虚拟表VT2中。
3） JOIN：如果指定了OUTER JOIN（如LEFT OUTER JION、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3.
                    如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）——步骤3），直到处理完所有表为止。
4） WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入虚拟表VT4中。
5） GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5。
6） CUBE|ROLLUP：对虚拟表VT5进行CUBE或ROLLUP操作，产生虚拟表VT6。
7） HAVING：对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才被插入虚拟表VT7中。
8） SELECT：第二次执行select操作，选择指定的列，插入到虚拟表VT8中。
9） DISTINCT：去除重复数据，产生虚拟表VT9
10）ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10
11）LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询的用户。

******************************多实例安装***********************************

编译安装单机多实例 mysql 5.5.22
1、安装cmake 编译工具
tar zxfv cmake-2.8.7.tar.gz
cd cmake-2.8.7
./configure --prefix=/usr/local/cmake && make && make install
2、安装mysql
   tar zxfv mysql-5.5.22.tar.gz
   安装第一个实例mysql
   cd mysql-5.5.22

   /usr/local/cmake/bin/cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/3306 -DMYSQL_DATADIR=/data/3306 -DWITH_INNOBASE_STORAGE_ENGINE=on -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/data/3306/mysqld.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DSYSCONFDIR=/etc/mysql/3306 && make && make install


MySQL5.5版本中，编译的选项同样众多，DBA可以通过# cmake . -LH 查看支持的参数，或者浏览下列页面：http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html，查看编译时可指定参数的详细描述。
截略一些常用参数如下：
CMAKE_INSTALL_PREFIX：指定MySQL程序的安装目录，默认/usr/local/mysql
DEFAULT_CHARSET：指定服务器默认字符集，默认latin1
DEFAULT_COLLATION：指定服务器默认的校对规则，默认latin1_general_ci
ENABLED_LOCAL_INFILE：指定是否允许本地执行LOAD DATA INFILE，默认OFF
WITH_COMMENT：指定编译备注信息
WITH_xxx_STORAGE_ENGINE：指定静态编译到mysql的存储引擎，MyISAM，MERGE，MEMBER以及CSV四种引擎默认即被编译至服务器，不需要特别指定。
WITHOUT_xxx_STORAGE_ENGINE：指定不编译的存储引擎
SYSCONFDIR：初始化参数文件目录
MYSQL_DATADIR：数据文件目录
MYSQL_TCP_PORT：服务端口号，默认3306
MYSQL_UNIX_ADDR：socket文件路径，默认/tmp/mysql.sock


cd /usr/local/mysql/3306
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql --datadir=/data/3306 初始化
创建PID并授权
touch /data/3306/mysql.pid
chown mysql.mysql /data/3306/mysql.pid
vim /etc/mysql/3306/my.cnf

/usr/local/mysql/3306/bin/mysqld_safe --user=mysql &   启动mysql

安装第二实例mysql

   cd mysql-5.5.22

   /usr/local/cmake/bin/cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/3307 -DMYSQL_DATADIR=/data/3307 -DWITH_INNOBASE_STORAGE_ENGINE=on -DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/data/3307/mysqld.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DSYSCONFDIR=/etc/mysql/3307 && make && make install


MySQL5.5版本中，编译的选项同样众多，DBA可以通过# cmake . -LH 查看支持的参数，或者浏览下列页面：http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html，查看编译时可指定参数的详细描述。
截略一些常用参数如下：
CMAKE_INSTALL_PREFIX：指定MySQL程序的安装目录，默认/usr/local/mysql
DEFAULT_CHARSET：指定服务器默认字符集，默认latin1
DEFAULT_COLLATION：指定服务器默认的校对规则，默认latin1_general_ci
ENABLED_LOCAL_INFILE：指定是否允许本地执行LOAD DATA INFILE，默认OFF
WITH_COMMENT：指定编译备注信息
WITH_xxx_STORAGE_ENGINE：指定静态编译到mysql的存储引擎，MyISAM，MERGE，MEMBER以及CSV四种引擎默认即被编译至服务器，不需要特别指定。
WITHOUT_xxx_STORAGE_ENGINE：指定不编译的存储引擎
SYSCONFDIR：初始化参数文件目录
MYSQL_DATADIR：数据文件目录
MYSQL_TCP_PORT：服务端口号，默认3306
MYSQL_UNIX_ADDR：socket文件路径，默认/tmp/mysql.sock


cd /usr/local/mysql/3307
chown -R mysql .
chgrp -R mysql .
vim /etc/mysql/3307/my.cnf
scripts/mysql_install_db --user=mysql --datadir=/data/3307 --basedir=/usr/local/mysql/3307 --defaults-file=/etc/mysql/3307/my.cnf 初始化
创建PID并授权
touch /data/3307/mysql.pid
chown mysql.mysql /data/3307/mysql.pid

/usr/local/mysql/3307/bin/mysqld_safe --user=mysql &   启动mysql