月度归档:2016年09月

mysql关系型数据库设计和优化

一. 表的设计满足3NF

1. 最高范式为6NF,只能在满足1NF的情况才能满足2NF,以此类推;

2.  1NF是表的属性(列)具有原子性,表的列不能再分,且不能有重复的列,只要是关系型数据库就满足1NF;

3.  2NF 是指表中不能有完全重复的记录(行),通过设置主键规避2NF;

4.  3NF 是如果列的内容可以通过已有的列或表推导出来,就不要使用单独的列来存储。

5. 通常情况下,表的设计必须遵循3NF要求,但有时候根据业务的不同可能需要违反3NF的规则,查询效率可能更高,称反3NF;

6. 数据表中含有多个非定长和定长的列时可以把表拆分为2,把定长的数据和非定长的数据分开存放;

7.设计表是字段不能为NULL,可以使用其他的值代替;

8.使用联合索引提高查询效率,

           ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引:

           ALTER TABLE table_name ADD INDEX index_name (column_list)

           ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

二. 创建适当的索引

  1. 设置索引sql语句 ‘alter table 表名 add primary key (‘字段名’)’;
  2. 创建完索引后数据库文件中会产生后缀为‘.MYI’结尾的文件,即数据表的索引文件,单个(字段)索引文件大小大约占用表数据的1/5,如下,分别是表结构文件、表数据文件、表索引文件:4
  3. 索引在大表中(1G以上的表数据)使用过多,占用空间较大,在中小型项目中可以使用适当的索引来提高系统的查询时间。

三. 优化数据库

1.先构建海量表,定位慢查询。

构建海量表,且数据不一样,需要创建存储过程完成创建或通过PHP写入随机数据,如下图,构建简单的个人资料表;

1

 

2. 在mysql服务正常开启时,默认不会纪录慢查询,启动慢查询方式如下:

2.1  在mysql命 令行下使用“show variables”查看mysql数据库的变量设置,确定如下慢查询日志已经开启,或可在my.ini中可添加配置:

log_slow_queries=ON   #开启慢查询
long_query_time=1       #设置慢查询记录时间
slow_query_log=ON     #开启日志记录
log_queries_not_using_indexes=ON  #索引字段不记录
slow_query_log_file = h:\wamp\logs\mysql_slow_query.log   #慢查询日志写入文件

2.2 查看纪录慢查询的查询时间最大值“show variables like ‘long_query_time’,设置纪录慢查询的时间命令 set long_query_time=1(配置文件中更改也可.配置后重启数据库服务器);

3

2.3 开启慢查询日志记录和慢查询,设置log_slow_queries=1和slow_query_log=1即可,修改系统变量使用命令:set GLOBAL log_sloe_queries=1(建议在配置文中修改,步骤1);

2.4 配置完成后,mysql就可以记录查询长设置时间的查询了,可以定位到项目中执行较慢的语句并针对优化,至于慢查询的时间设置根据业务需求自行配置.

mysql 5.7 修改密码和配置权限

一 、修改密码

  1. 命令:mysql -uroot -p

会提示你输入密码,就是上面我们看到的默认密码,输入后正常情况下会有welcome等提示

到这里我们就可以开始去修改我们的默认密码了,接着输入:

  1. 命令:use mysql

然后会提示你:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

不要急,我们接着输入命令:

  1. 命令:alter user ‘root’@’localhost’ identified by ‘mysql’;

然后会提示你Query OK !.

下面就正式进行修改我们的密码,这里有一个地方需要注意,5.7.9版本user表里面已经没有password这个字段了,可以看一下

但是这里有一个加密的 authentication_string 字段,这就是新版本的密码了,既然找到了我们就不客气了,改密码:

  1. update user set authentication_string=“123456” where user=“root”;

注意:这里很多人都会以为设置好了,就直接敲命令:flush privileges;然后退出重启登录了,结果会发现设置的密码不管用,楼主也是在这边吃了暗亏,停了有段时间不得其解,百度过后发现需要再设置以下:

  1. SET PASSWORD = PASSWORD(‘yourPwd’);

PASSWORD 后面写你刚刚设置过的新密码;

这次我们可以flush了:

  1. flush privileges
  1. exit  ;
这样我们就成功设置我们的新密码了;

好了,我们来验证一下,我是继续在开出的cmd命令窗口输入:

  1. mysql -uroot -p

然后输入密码  (你的新密码),正常情况下就OK了。

二、用户与权限

  1. 创建用户使用命令: CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;    //username-用户名 host-可访问主机 %|localhost password-密码
  2. 授权命令: GRANT privileges ON databasename.tablename TO ‘username’@’host’ ;  //privileges–权限选项【insert,select,update,delete,drop,ALL】databasename.tablename-数据库名称和表名称 ‘如 *.*为所有权限’
  3. 收回权限 :REVOKE privilege ON databasename.tablename FROM ‘username’@’host’;
  4. 删除用户:DROP USER ‘username’@’host’;

composer–优秀的开源项目集合

包管理Package Management

Libraries for package and dependency management.

Package Management Related

Libraries related to package management.

  • Satis – A static Composer repository generator.
  • Composition – A library to check your Composer environment at runtime.
  • Poser – A Composer class aliasing library.
  • Version – A parsing and comparison library for semantic versioning.
  • NameSpacer – A library to convert from underscores to namespaces.
  • Patch Installer – A library to install patches using Composer.

框架

Web 开发框架.

  • Symfony2 – A framework comprised of individual components.
  • Knp RAD Bundle – A Rapid Application Development (RAD) bundle for Symfony 2.
  • Zend Framework 2 – Another framework comprised of individual components.
  • Laravel 4 – A simple PHP framework.
  • Lithium – Another framework of components.
  • Aura PHP – A framework of independent components.
  • Phalcon – A framework implemented as a C extension.

框架组件

Web 开发框架的独立组件.

微框架Micro Frameworks

微框架和路由器routers.

内容管理系统Content Management Systems

现代内容管理系统Modern content management systems.

  • Bolt – A simple CMS built with Silex and Twig.

模板Templating

库和工具,模板和词法。

  • Twig – A comprehensive templating language.
  • Twig Cache Extension – A template fragment cache library for Twig.
  • Mustache – A PHP implementation of the Mustache template language.
  • Phly Mustache – Another PHP implementation of the Mustache template language.
  • MtHaml – A PHP implementation of the HAML template language.
  • Plates – A native PHP templating library.
  • Lex – A lightweight template parser.

静态网站生成器Static Site Generators

工具为预先处理的内容来生成网页。

  • Sculpin – A tool that converts Markdown and Twig into static HTML.
  • Phrozn – Another tool that converts Textile, Markdown and Twig into HTML.

HTTP

Libraries for working with HTTP and scraping websites.

  • Guzzle – A HTTP client.
  • Buzz – Another HTTP client.
  • Requests – A simple HTTP library.
  • Goutte – A simple web scraper.
  • PHP VCR – A library for recording and replaying HTTP requests.

URL

Libraries for parsing URLs.

Email

用于收发邮件的库.

Files

用于文件操作和MIME类型探测.

  • Gaufrette – A filesystem abstraction layer.
  • Flysystem – Another filesystem abstraction layer.
  • Canal – A library to determine internet media types.
  • Apache MIME Types – A library that parses Apache MIME types.
  • Ferret – A MIME detection library.
  • Hoa Mime – Another MIME detection library.
  • Lurker – A resource tracking library.
  • PHP File Locator – A library for locating files in large projects.
  • PHP FFmpeg – A wrapper for the FFmpeg video library.

Streams

Libraries for working with streams.

  • Streamer – A simple object-orientated stream wrapper library.

Dependency Injection

实现了依赖注入设计模式库。Libraries that implement the dependency injection design pattern.

  • Pimple – A tiny dependency injection container.
  • Auryn – Another dependency injection container.
  • Orno Di – Another flexible dependency injection container.
  • PHP DI – A dependency injection implementation using annotations.
  • Acclimate – A common interface to dependency injection containers and service locators.

Imagery

图片处理库Libraries for manipulating images.

Testing

单元测试相关库。Libraries for testing codebases and generating test data.

  • PHPUnit – A unit testing framework.
  • DBUnit – A database testing library for PHPUnit.
  • ParaTest – A parallel testing library for PHPUnit.
  • PHPSpec – A design by specification unit testing library.
  • Codeception – A full stack testing framework.
  • Atoum – A simple testing library.
  • Mockery – A mock object library for testing.
  • Phake – Another mock object library for testing.
  • Parody – Yet another mock object library for testing.
  • Sismo – A continuous testing server library.
  • Faker – A fake data generator library.
  • Samsui – Another fake data generator library.
  • Alice – An expressive fixture generation library.
  • Behat – A behaviour driven development (BDD) testing framework.
  • Pho – Another behaviour driven development testing framework.
  • Mink – Web acceptance testing.
  • HTTP Mock – A library for mocking HTTP requests in unit tests.
  • VFS Stream – A virtual filesystem stream wrapper for testing.
  • Locust – A modern load test library written in Python.
  • Travis CI – A continuous integration platform.
  • PHPCI – An open source continuous integration platform for PHP.

Documentation

生成项文档的库。Libraries for generating project documentation.

  • Sami – An API documentation generator.
  • APIGen – Another API documentation generator.
  • PHP Documentor 2 – A documentation generator.

Security

Libraries for generating secure random numbers, encrypting data and scanning for vulnerabilities.

  • HTML Purifier – A standards compliant HTML filter.
  • RandomLib – A library for generating random numbers and strings.
  • True Random – A library that generates random numbers using www.random.org.
  • SecurityMultiTool – A PHP security library.
  • Password Compat – A compatibility library for the new PHP 5.5 password functions.
  • PHPAss – A portable password hashing framework.
  • PHP Password Lib – A library for generating and validating passwords.
  • Password Policy – A password policy library for PHP and JavaScript.
  • Password Validator – A library for validating and upgrading password hashes.
  • PHPSecLib – A pure PHP secure communications library.
  • TCrypto – A simple encrypted key-value storage library.
  • PHP IDS – A structured PHP security layer.
  • PHP SSH – An experimental object orientated SSH wrapper library.
  • IniScan – A tool that scans PHP INI files for security.
  • SensioLabs Security Check – A web tool to check your Composer dependecies for security advisories.
  • Zed – An integrated penetration testing tool for web applications.

Code Analysis

库和工具来分析,解析和操作的代码库。Libraries and tools for analysing, parsing and manipulation codebases.

  • PHP Parser – A PHP parser written in PHP.
  • PHPPHP – A PHP VM implementation in PHP.
  • PHPSandbox – A PHP sandbox environment.
  • Dissect – A set of tools for lexical and syntactical analysis.
  • PHP Mess Detector – A library that scans code for bugs, sub-optimal code, unused parameters and more.
  • PHP Code Sniffer – A library that detects PHP, CSS and JS coding standard violations.
  • PHPCPD – A library that detects copied and pasted code.
  • PHP Analyser – A library for analysing PHP code to find bugs and errors.
  • PHP CS Fixer – A coding standard fixer library.
  • PHP Manipulator – A library for analysing and modifying PHP Source Code.
  • PHP Refactoring Browser – A command line utility for refactoring PHP code.
  • UBench – A simple micro benchmark library.
  • Athletic – An annotation based benchmark framework.
  • Mondrian – A code analysis tool using Graph Theory.
  • PHP Debug Bar – A debugging toolbar.
  • PHP Console – A web debugging console.
  • Barbushin PHP Console – Another web debugging console using Google Chrome.
  • PHPDBG – An interactive PHP debugger.
  • Scrutinizer – A web tool to scrutinise PHP code.

Build Tools

项目构建和自动化工具。Project build and automation tools.

  • Go – A simple PHP build tool.
  • Bob – A simple project automation tool.
  • Phake – A rake PHP clone library.
  • Box – A utility to build PHAR files.

Asset Management

Tools for managing, compressing and minifying website assets.

  • Assetic – An asset manager pipeline library.
  • Pipe – Another asset manager pipeline library.
  • Munee – An asset optimiser library.
  • JShrink – A JavaScript minifier library.

Geolocation

Libraries for geocoding addresses and working with latitudes and longitudes.

  • GeoCoder – A geocoding library.
  • GeoTools – A library of geo-related tools.
  • PHPGeo – A simple geo library.
  • GeoJSON – A GeoJSON implementation.

Date and Time

日期和时间处理库。Libraries for working with dates and times.

  • Carbon – A simple DateTime API extension.
  • ExpressiveDate – Another DateTime API extension.
  • CalendR – A calendar management library.

Event

Libraries that are event-driven or implement non-blocking event loops.

Logging

Libraries for generating and working with log files.

  • Monolog – A comprehensive logger.

E-commerce

Libraries and applications for taking payments and building online e-commerce stores.

  • OmniPay – A framework agnostic multi-gateway payment processing library.
  • Payum – A payment abstraction library.
  • Sylius – An open source e-commerce solution.
  • Thelia – Another open source e-commerce solution.
  • Money – A PHP implementation of Fowler’s money pattern.

PDF

PDF文件操作类库。Libraries and software for working with PDF files.

  • Snappy – A PDF and image generation library.
  • WKHTMLToPDF – A tool to convert HTML to PDF.

ORM and Datamapping

对象-关系映射库。Libraries that implement object-relational mapping or datamapping techniques.

  • Doctrine – A comprehensive DBAL and ORM.
  • Doctrine Migrations – A migration library for Doctrine.
  • Doctrine Extensions – A collection of Doctrine behavioural extensions.
  • Propel – A fast ORM.
  • Eloquent – The Laravel 4 ORM.
  • Baum – A nested set implementation for Eloquent.
  • Spot – A MySQL datamapper ORM.
  • RedBean – A lightweight, configuration-less ORM.
  • PHP ActiveRecord – A PHP Active Record implementation.
  • Paris and Idiorm – A minimalist database library.
  • Pomm – An Object Model Manager for PostgreSQL.
  • Migrations – A migration management library.
  • PHPMig – Another migration management library.
  • Phinx – Another database migration library.

NoSQL

Libraries for working with “NoSQL” backends.

  • MongoQB – A MongoDB query builder library.
  • Monga – A MongoDB abstraction library.
  • Predis – A feature complete Redis library.

Queue

Libraries for working with event and task queues.

  • Pheanstalk – A Beanstalkd client library.
  • PHP AMQP – A pure PHP AMQP library.
  • Thumper – A RabbitMQ pattern library.
  • Bernard – A multibackend abstraction library.

Search

Libraries and software for indexing and performing search queries on data.

Command Line

Libraries for building command line utilities.

  • Boris – A tiny PHP REPL.
  • PsySH – Another PHP REPL.
  • GetOpt – A command line opt parser.
  • OptParse – Another command line opt parser.
  • Commando – Another simple command line opt parser.
  • GetOptionKit – Another command line opt parser.
  • Cron Expression – A library to calculate cron run dates.
  • ShellWrap – A simple command line wrapper library.
  • Hoa Console – Another command line library.
  • Shunt – A library for running commands in parallel on multiple remote machines.

Authentication

Libraries for implementing authentications schemes.

  • Sentry – A framework agnostic authentication & authorisation library.
  • Sentry Social – A library for social network authentication.
  • OPAuth – A multi-provider authentication framework.
  • OAuth2 – An OAuth2 authentication server, resource server and client library.
  • PHP oAuthLib – Another OAuth library.
  • TwitterOAuth – A Twitter OAuth library.
  • TwitterSDK – A fully tested Twitter SDK.
  • Hawk – A Hawk HTTP authentication library.

Markup

Libraries for working with markup.

  • Decoda – A lightweight markup parser library.
  • PHP Markdown – A Markdown parser.
  • Dflydev Markdown – Another Markdown parser.
  • Parsedown – Another Markdown parser.
  • Ciconia – Another Markdown parser that supports Github flavoured Markdown.
  • HTML5 PHP – An HTML5 parser and serializer library.

Text and Numbers

Libraries for parsing and manipulating text and numbers.

  • ANSI to HTML5 – An ANSI to HTML5 convertor library.
  • Patchwork UTF-8 – A portable library for working with UTF-8 strings.
  • Hoa String – Another UTF-8 string library.
  • Stringy – A string manipulation library with multibyte support.
  • Numbers PHP – A library for working with numbers.
  • Math – A library for working with large numbers.
  • Color Jizz – A library for manipulating and converting colours.
  • UUID – A library for generating UUIDs.
  • Slugify – A library to convert strings to slugs.
  • Urlify – A PHP port of Django’s URLify.js.
  • Text – A text manipulation library.
  • PHP Units of Measure – A library for converting between units of measure.
  • PHP Conversion – Another library for converting between units of measure.
  • SQL Formatter – A library for formatting SQL statements.
  • Byte – A simple byte conversion library.
  • UA Parser – A library for parsing user agent strings.
  • LibPhoneNumber for PHP – A PHP implementation of Google’s phone number handling library.

Filtering and Validation

用于过滤和校验数据。Libraries for filtering and validating data.

  • Filterus – A simple PHP filtering library.
  • Respect Validate – A simple validation library.
  • Valitron – Another validation library.
  • Upload – A library for handling file uploads and validation.
  • DMS Filter – An annotation filtering library.
  • MetaYaml – A schema validation library that supports YAML, JSON and XML.

REST and API

Libraries and web tools for developing REST-ful APIs.

  • Apigility – An API builder built with Zend Framework 2.
  • Hateoas – A HATEOAS REST web service library.
  • HAL – A Hypertext Application Language (HAL) builder library.
  • Negotiation – A content negotiation library.

Caching

数据缓存库。Libraries for caching data.

  • Cache – A caching library (part of Doctrine).
  • Stash – Another library for caching.

Data Structure and Storage

Libraries that implement data structure or storage techniques.

  • Ardent – A library of data structures.
  • PHP Collections – A simple collections library.
  • Serializer – A library for serialising and de-serialising data.
  • PHP Object Storage – A library for object storage.
  • Fractal – A library for converting complex data structures to JSON output.

Notifications

Libraries for working with notification software.

  • Nod – A notification library (e.g., Growl).
  • Notificato – A library for handling push notifications.
  • Notification Pusher – A standalone library for device push notifications.
  • Notificator – A lightweight notification library.

Deployment

  • Pomander – A deployment tool for PHP applications.
  • Rocketeer – A fast and easy deployer for the PHP world.

Third Party APIs

Libraries for accessing third party APIs.

Miscellaneous

Useful libraries or tools that don’t fit in the categories above.

  • Spork – A process forking library.
  • JSON Lint – A JSON lint utility.
  • JSONPCallbackValidator – A library for validating JSONP callbacks.
  • KnpMenu – A menu library.
  • Pagerfanta – A pagination library.
  • Ruler – A simple stateless production rules engine.
  • LiteCQRS – A CQRS (Command Query Responsibility Separation) library.
  • Sslurp – A library that makes dealing with SSL suck less.
  • PHP Option An option type library.
  • Metrics – A simple metrics API library.
  • Sabre VObject – A library for parsing VCard and iCalendar objects.
  • Annotations – An annotations library (part of Doctrine).
  • Whoops – A pretty error handling library.
  • Finite – A simple PHP finite state machine.
  • LadyBug – A dumper library.
  • Plum – A deployer library.
  • Procrastinator – A library for running time consuming tasks.
  • Compose – A function composition library.
  • SuperClosure – A library that allows Closures to be serialized.
  • Jumper – A remote service executor library.
  • Underscore – A PHP port of the Underscore JS library.
  • PHP PassBook – A PHP library for iOS PassBook.
  • PHP Expression – A PHP expression language.
  • RMT – A library for versioning and releasing software.
  • Wise – A configuration manager.
  • Opengraph – An opengraph library.
  • Essence – A library for extracting web media.
  • Embera – An Oembed consumer library.
  • Graphviz – A Graphviz library.
  • Monad PHP – A simple Monad library.
  • Flux – A regular expression building library.
  • Patchwork – A library for redefining userland functions.
  • Galapagos – Evolutionary language transformation.
  • Design Patterns PHP – A repository of software patterns implemented in PHP.
  • PHPCR – A PHP port of the Java Content Repository (JCR).
  • Functional PHP – A functional programming library.
  • ClassPreloader – A library for optimising autoloading.
  • Lib Country – A library for country and subdivision data.
  • Lib Accessor – A library for simplifying accessors.
  • PHPStack – A TCP/IP stack proof of concept written in PHP.
  • Nmap – A PHP wrapper around Nmap.
  • Code Mover – A library for moving code.
  • Iter – A library that provides iteration primatives using generators.
  • Lambda PHP – A Lambda calculus interpreter in PHP.
  • Country List – A list of all countries with names and ISO 3166-1 codes.
  • PHP-GPIO – A library for playing with the Raspberry PI’s GPIO pins.

Development Software

Software for creating a development environment.

  • HomeBrew – A package manager for OSX.
  • HomeBrew PHP – A PHP tap for HomeBrew.
  • PHP OSX – A PHP installer for OSX.
  • HipHop PHP – A Virtual Machine, Runtime and JIT for PHP by Facebook.
  • Vagrant – A portable development environment utility.
  • Ansible – A radically simple orchestration framework.
  • Puppet – A server automation framework and application.
  • Chef – A systems integration framework.
  • SaltStack – An infrastructure management tool.
  • PHP Brew – A PHP version manager and installer.
  • PHP Env – Another PHP version manager.
  • PHP Switch – Another version manager.
  • PHP Build – Another PHP version installer.
  • CodeKit – A general web development tool.
  • HTTPie – A command line alternative to cURL written in Python.
  • Backup – A server backup tool written in Ruby.

Web Tools

Web-based tools.

  • PuPHPet – A web tool for building PHP development virtual machines.
  • Protobox – Another web tool for building PHP development virtual machines.
  • 3V4L – An online PHP shell.
  • DBV – A database version control application.
  • PHP Queue – An application for managing queueing backends.
  • Composer as a Service – A tool for downloading Composer packages as a zip file.
  • MailCatcher – A web tool for capturing and viewing emails.

Resources

Various resources, such as books, websites and articles, for improving your PHP development skills and knowledge.

Websites

Useful web and PHP-related websites and newsletters.

Books

Fantastic books and e-books.

Web Reading

General web-development-related reading materials.

PHP Reading

PHP-releated reading materials.

PHP Internals Reading

Reading materials related to the PHP internals or performance.

windows 2008 服务器配置问题

前些天在配置服务器windowns 2008的phpstudy集成环境时,服务器环境安装好了,局域网无法访问的问题,当时也是各种排查,查了几个小时还是没找到问题。

当时的问题是这样的,使用数据库连接工具连不上服务器的数据库(已经配置了mysql的权限),使用局域网的机器ping服务器的IP不通,但是服务器ping局域网ip是可以的,然后就在服务器防火墙入站规则中的一个配置中修改一下参数,然后ping的问题解决了,试着连接还是返回连接不上!

后来查了各种资料,最后在总监的帮助下弄明白了要设置服务器防火墙的入站规则,就单独给mysql设置了端口3306的入站规则,终于连接上数据库了,但网站还是无法访问,一想是不是需要给apache单独配置入站规则?

那就尝试一下吧!按照mysql的协议,配置80端口的入站规则,测试,不行!后面把协议改成任何,测试通过,终于把问题搞定!